On 28 September 2010 17:43, Marco <[email protected]> wrote: > Hello there sqlite-users, > I have a quick question regarding selection and deletion of large unsigned > integers. > > I create my table as following: > [1] CREATE TABLE t1 (instnum BIG UNSIGNED INTEGER PRIMARY KEY, ...) > > I then insert some row: > [2] INSERT INTO t1 (instnum, ...) VALUES (CAST (?1 AS BIG UNSIGNED > INTEGER), ...)
http://www.sqlite.org/datatype3.html "BIG UNSIGNED INTEGER" resolves to storage class INTEGER - always signed. > > This is just a test for 64bit unsigned, so I have the following rows: > 1 > 2 > 18446744073709551615 (0xFFFFFFFFFFFFFFFF) > 9223372036854775808 (0x8000000000000000) > 0 > 3 > 1234 > 23456 > 654321 > > When I try to DELETE using: > [3] DELETE FROM t1 WHERE instnum <= CAST (?1 AS BIG UNSIGNED INTEGER); > > I don't always get what I would expect, to me it looks like large numbers > (64bit with the MSB set to 1) are treated as negative. > > For example if ?1 in query [3] is set to 654321, all rows are being deleted > (while 18446744073709551615 and 9223372036854775808 should not be deleted) > > If ?1 is set to 18446744073709551615, the rows 18446744073709551615 and > 9223372036854775808 are deleted, but the other ones are not. > (while all of them should be gone) > > (I was previously using [2] and [3] without explicit cast, but the result is > the same). > > How can I solve this issue? subtract 9223372036854775808 when storing values into the db, add when retrieving. > > Thank you, > Marco Regards, Simon _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
