Unsigned int fields seem wrongly typed to System.Int32 by System.Data.SQLite

Question

Using System.Data.SQLite, I am creating a table with unsigned integer columns:

@"CREATE TABLE widgets (" +
    @"id unsigned integer(10) PRIMARY KEY, " +
    @"fkey unsigned integer(10), " + ...

and then insert values like

INSERT INTO widgets (id, fkey, ...) VALUES (4294967295, 3456, ...

However, looping over rows and columns of this table I discover that row["id"] has type System.Int32 (rather than UInt32) and, no surprise, 4294967295 is interpreted as -1. Actually, all unsigned int fields in the table (not only the primary key id are incorrectly typed as System.Int32)

Meanwhile the SQLite type specification says that integers are stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. 4294967295 = 0xFFFFFFFF fits in just four bytes. It also seems at odds with the SQLite's so-called "dynamic typing". When I insert bigger than 4294967295 positive values, the type remains System.Int32.

Is it a bug or a feature?

PS1:

my table contains 1 row with column id=2^32-1=4294967295 (or bigger), and I print types of all columns with

public void PrintDataTypes(DataTable dt) {
    foreach (DataRow row in dt.Rows) {
        foreach (DataColumn col in dt.Columns)
            Console.WriteLine("name={0}, type={1}", 
                               col.ToString(),
                               row[col].GetType().ToString());
        return; // after 1st row is done
    }
}

And I invariably get

name=id, type=System.Int32
name=fkey, type=System.Int32
...

See sqlite.org/datatype3.html - as far as I know SQLite ignores the "unsigned" keyword and the size specifier. To determine why you are seeing Int32 you may want to post the code you use to read from the table. — C.Evenhuis
Thanks! I added PS1 to the original post to explain how I get System.Int32 — user2770141
Citing the reference you suggested:The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer). — user2770141
In SQLite, all integer have the same type, signed 64 bits. (Smaller storage is just an optimization). Using Int32 looks like a bug in the DB driver. — CL.
Still missing is how the SQlite table becomes a DataTable. Could be a mis-configuration of the Adapater or the Table. Are they strongly typed, what is the SELECT query? — Henk Holterman

JamesWHurst JamesWHurst · Accepted Answer · 2014-04-13T06:03:33

You're not going to get an unsigned integer column in SqLite, even though you specified unsigned in your SQL. Yes, I personally consider that to be a glaring deficiency - it does not even output a warning upon seeing your "unsigned" specification within your SQL. Thus, your column is 64-bit signed, and your maximum value that you can store is indeed 2 to the 63rd power.

Unsigned int fields seem wrongly typed to System.Int32 by System.Data.SQLite

1 Answers