SQLite3 support for 64-bit unsigned integers

[Kelly Jones]

I tried inserting 2^63-1 and the two integers after it into an SQLite3
db, but this happened:

SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE test (test INT);
sqlite> INSERT INTO test VALUES (9223372036854775807);
sqlite> INSERT INTO test VALUES (9223372036854775808);
sqlite> INSERT INTO test VALUES (9223372036854775809);
sqlite> .mode line
sqlite> SELECT * FROM test;
test = 9223372036854775807
test = 9.22337203685478e+18
test = 9.22337203685478e+18

sqlite> SELECT * FROM test WHERE test = '9223372036854775808';
test = 9.22337203685478e+18
test = 9.22337203685478e+18

Why the sudden switch to scientific notation and loss of precision?

Are 64-bit integers signed (ie -2^63 to 2^63-1)? Can I "unsign" them?

Since sqlite3 uses 64-bit ints for rowid, I figured they'd be unsigned.

Workarounds?

[John Machin]

Post by Kelly Jones
I tried inserting 2^63-1 and the two integers after it into an SQLite3
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE test (test INT);
sqlite> INSERT INTO test VALUES (9223372036854775807);
sqlite> INSERT INTO test VALUES (9223372036854775808);
sqlite> INSERT INTO test VALUES (9223372036854775809);
sqlite> .mode line
sqlite> SELECT * FROM test;
test = 9223372036854775807
test = 9.22337203685478e+18
test = 9.22337203685478e+18
sqlite> SELECT * FROM test WHERE test = '9223372036854775808';
test = 9.22337203685478e+18
test = 9.22337203685478e+18
Why the sudden switch to scientific notation and loss of precision?

See answer to next question.

Post by Kelly Jones
Are 64-bit integers signed (ie -2^63 to 2^63-1)?

SQLite's integers are 64-bit signed two's-complement. Don't bet the
ranch on -2^63.

Post by Kelly Jones
Can I "unsign" them?

No.

Post by Kelly Jones
Since sqlite3 uses 64-bit ints for rowid, I figured they'd be unsigned.

Since using rowids at the rate of 1 million per second would bump into
2^63 after about 292,000 years, and since SQLite has only one integer
type, to figure that it'd be signed would be a better betting proposition.

Post by Kelly Jones
Workarounds?

BLOBs, maybe, depending what you want 64-bit unsigned integers for.
What's the use case?

Cheers,
John

[Jay A. Kreibich]

Post by Kelly Jones
I tried inserting 2^63-1 and the two integers after it into an SQLite3
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE test (test INT);
sqlite> INSERT INTO test VALUES (9223372036854775807);
sqlite> INSERT INTO test VALUES (9223372036854775808);
sqlite> INSERT INTO test VALUES (9223372036854775809);
sqlite> .mode line
sqlite> SELECT * FROM test;
test = 9223372036854775807
test = 9.22337203685478e+18
test = 9.22337203685478e+18
sqlite> SELECT * FROM test WHERE test = '9223372036854775808';
test = 9.22337203685478e+18
test = 9.22337203685478e+18
Why the sudden switch to scientific notation and loss of precision?

For more details, see http://sqlite.org/datatype3.html

You're creating a column with an INTEGER affinity, but then
overflowed what an integer can represent, so SQLite found some other
representation. From the docs:

A column that uses INTEGER affinity behaves in the same way as
a column with NUMERIC affinity, except that if a real value
with no fractional component and a magnitude that is less than
or equal to the largest possible integer (or text value that
converts to such) is inserted it is converted to an integer
and stored using the INTEGER storage class.

It helps to remember that the default "type" of all numbers in SQL is
some type of real (in the formal mathematical sense, not the
programming sense of a floating-point number) number.

Post by Kelly Jones
Are 64-bit integers signed (ie -2^63 to 2^63-1)?

*All* integer values in SQLite are always signed.

(And yes, being two's complement, that's the correct range)

Post by Kelly Jones
Workarounds?

Since you haven't said what you're trying to do, it is difficult to
propose a workaround. If all you need is a unsigned long long, you
can either use an 8-byte BLOB (and a lot of casting) or you can just
store the value as a signed int and cast back and forth.

BLOBs are likely the safer choice, since the sort order will be
correct.

You could also break things up into two major/minor columns that each
hold 32-ish bit numbers (SQLite auto sizes integers to their minimum
representation of 1, 2, 3, 4, 6, or 8 bytes). Or 8 bit/48 bit, or
whatever.

-j

[Nuno Lucas]

Just a small clarification you probably forgot...

 Since you haven't said what you're trying to do, it is difficult to
 propose a workaround.  If all you need is a unsigned long long, you
 can either use an 8-byte BLOB (and a lot of casting) or you can just
 store the value as a signed int and cast back and forth.
 BLOBs are likely the safer choice, since the sort order will be
 correct.

This will be true if the BLOBs are stored as big-endian 64-bit
integers, not if they are stored as little-endian (the first byte
would then be the one less significant).


Regards,
~Nuno Lucas