Dear SQLite people, Please bless me with your infinite wisdom. I'm using SQLite 3.25.2 on Windows, downloaded the latest precompiled binaries from the official page https://sqlite.org/download.htmlExecuting the following code
DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL UNIQUE);INSERT INTO TestReal values (9223372036854775807.0);INSERT INTO TestReal values (9223372036854775807.0 - 1);INSERT INTO TestReal values (9223372036854775807.0 - 2);INSERT INTO TestReal values (9223372036854775807.0 - 3); fails as expected, since 9223372036854775807.0 is 2^63, these numbers are way out of the range where all integers are exactly representable as doubles. I mean sqlite> select 9223372036854775807.0 = 9223372036854775807.0 - 1;1 sqlite> select 9223372036854775807.0 = 9223372036854775807.0 - 512;1 And column A is unique, so it makes perfect sense to print a 'UNIQUE constraint failed: TestReal.A' message. But there seems to be an unintended workaround DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL UNIQUE);INSERT INTO TestReal values (9223372036854775807);INSERT INTO TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values (9223372036854775807 - 2);INSERT INTO TestReal values (9223372036854775807 - 3); runs without any problems. The following queries confirm that the table now has exactly 4 values inserted, but only one distinct value, despite of having a unique constraint sqlite> SELECT * FROM TestReal;9.22337203685478e+189.22337203685478e+189.22337203685478e+189.22337203685478e+18 sqlite> SELECT DISTINCT(A) FROM TestReal;9.22337203685478e+18 sqlite> .schemaCREATE TABLE TestReal(A REAL UNIQUE); So my question is: is this a bug in SQLite? Or do I not understand correctly what 'unique' actually means? I posted an identical question on StackOverflow.com, because I'm still not 100% sure it is a bug. If so, sorry for wasting you time. Sincerely, Máté Szabó _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
