Different comparison results in WHERE and SELECT clauses

(1) By Yu Liang (LY1598773890) on 2021-07-19 19:05:38 [source]

For query:

CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY );
INSERT INTO v0 VALUES ( 9223372036854775807 );

SELECT * FROM v0 WHERE v1 >= ( 9223372036854775807 + 1 );
/* Returns 9223372036854775807 */

SELECT v1 >= ( 9223372036854775807 + 1 ) FROM v0;
/* Returns 0 */

We observe different comparison results on v1 >= ( 9223372036854775807 + 1 ), while v1 being the ROWID of the table. We are uncertain about the exact data type SQLite are using for v1 and the numerical literal, in both WHERE and SELECT clauses, so we are uncertain about the reason of the different comparison results.

The different comparison results are also related to the PRIMARY KEY. As we remove v1 as PRIMARY KEY, the comparison results are consistent:

CREATE TABLE v0 ( v1 INTEGER );
INSERT INTO v0 VALUES ( 9223372036854775807 );

SELECT * FROM v0 WHERE v1 >= ( 9223372036854775807 + 1 );
/* Returns NULL */

SELECT v1 >= ( 9223372036854775807 + 1 ) FROM v0;
/* Returns 0 */

Looking forward to replies. And thank you for any explanation that help us to better understand this query.

(2) By Richard Hipp (drh) on 2021-07-19 20:54:17 in reply to 1 [link] [source]

Fixed with check-in f9c6426de3b413ff.

(4.1) By Yu Liang (LY1598773890) on 2021-07-19 22:04:51 edited from 4.0 in reply to 2 [link] [source]

Deleted

(3) By Keith Medcalf (kmedcalf) on 2021-07-19 22:00:41 in reply to 1 [link] [source]

This is the result of the operation of affinity calculations regarding rowid lookup.

(9223372036854775807 + 1) is a floating point result 9223372036854775000.0 which when converted back to an integer so that it can be used as the "integer primary key" to lookup the first candidate rows in the table. That value becomes 9223372036854774784

WHERE 9223372036854775807 >= 9223372036854774784

evaluates True. So the candidate is output.

(5.1) By Yu Liang (LY1598773890) on 2021-07-19 23:26:10 edited from 5.0 in reply to 3 [link] [source]

Thank you Keith for providing further explanation.

So in my understanding, in the original query, if column v1 is NOT a PRIMARY KEY, then the floating point result 9223372036854775000.0 will not be converted back to an integer for table lookup, thus the comparison 9223372036854775807 >= 9223372036854775000.0 is accurate and always evaluates True. The loss of precision is only happening when v1 is PRIMARY KEY and the 9223372036854775000.0 would converted back to INTEGER.

Also, thank you for the fix and the fix comment.