Different comparison results in WHERE and SELECT clauses

(1) By Yu Liang (LY1598773890) on 2021-07-19 19:05:38 [link] [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) By Yu Liang (LY1598773890) on 2021-07-19 22:00:53 in reply to 2 updated by 4.1 [link] [source]

Thank you for the fix and the fix comment. 

So according to the fix comment, in the comparison, column v1 is being interpreted as 64-bits INTEGER, and the numerical literal should be REAL. The patch fix the loss of the precision problem from the original query. This is different from our original understanding, as we originally believe that both v1 and the literal are being transformed into REAL and then compare.

(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) By Yu Liang (LY1598773890) on 2021-07-19 22:17:42 in reply to 3 updated by 5.1 [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 False. 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.

(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.