CODEWITHSUNDEEP
sqlitefloating-point
szmate1618
szmate1618

Reputation: 1715

SQLite - float granularity breaking unique contraint?

I'm using SQLite 3.25.2 on Windows, downloaded the latest precompiled binaries from the official page https://sqlite.org/download.html

Executing 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+18
9.22337203685478e+18
9.22337203685478e+18
9.22337203685478e+18
sqlite> SELECT DISTINCT(A) FROM TestReal;
9.22337203685478e+18
sqlite> .schema
CREATE 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?

Upvotes: 3

Views: 109

Answers (1)

szmate1618
szmate1618

Reputation: 1715

With the release of SQLite 3.29.0 it's settled, this was never the intended behavior, but a bug. The error is still present in 3.28.0, but not in 3.29.0, in the newest versions both of my code snippets fail as expected.

This is the commit containing the fix: https://www.sqlite.org/src/info/9b0915272f4d4052

I confirmed it by manually applying this commit on 3.28.0 source code, and replacing MEM_IntReal with (MEM_Int | MEM_Real).

The error ticket was opened after the possible error was reported here on the official SQLite mailing list: https://www.mail-archive.com/[email protected]/msg115214.html

I also reported it, in fact a bit earlier but my report didn't garner that much attention: https://www.mail-archive.com/[email protected]/msg112655.html

Upvotes: 1

Related Questions