On Sun, May 24, 2009 at 11:28:59PM -0700, Kelly Jones scratched on the wall: > 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?
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. > 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) > 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 -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
