Inconsistencies occur when sqlite executes the following queries

(1) By xiangwei on 2023-05-19 08:44:08 [source]

I.PoC

CREATE TABLE v0 ( c1 INTEGER, c2 ); 
CREATE VIRTUAL TABLE v3 USING rtree ( c4, c5, c6 ); 
INSERT INTO v3 VALUES ( 'x' = 9223372036854775807, 9223372036854775807, 9223372036854775807 ), ( 127, 9223372036854775807, 9223372036854775807 ), ( 9223372036854775807, 0, 18446744073709551488 ); 
ALTER TABLE v3 RENAME TO v8; 
SELECT COUNT ( * ) FROM v8 AS a31 WHERE a31.c6 IN ( 4294967295, 127, 9223372036854775807 ); 
SELECT TOTAL ( ( CAST ( a31.c6 IN ( 4294967295, 127, 9223372036854775807 ) AS BOOL ) ) != 0 ) FROM v8 AS a31;

II.Results

SELECT COUNT ( * ) FROM v8 AS a31 WHERE a31.c6 IN ( 4294967295, 127, 9223372036854775807 );
--- result: 2

SELECT TOTAL ( ( CAST ( a31.c6 IN ( 4294967295, 127, 9223372036854775807 ) AS BOOL ) ) != 0 ) FROM v8 AS a31;
--- result: 0

III. Reproduce

SQLite version:         3.43.0 2023-05-17 00:26:50 (3.010bd47b9879aa563d04827497c3a0cd91c19f7d)
Build platform: 	Ubuntu 22.04.2 LTS
Build steps:            ./configure & make

(2) By Richard Hipp (drh) on 2023-05-19 11:58:48 in reply to 1 [link] [source]

Here is a simplification of your complaint:

CREATE VIRTUAL TABLE t1 USING rtree(id, x0, x1); 
INSERT INTO t1 VALUES(0, 1, 9223372036854775807);
SELECT count(*) FROM t1 WHERE x1=9223372036854775807;
SELECT sum(x1=9223372036854775807) AS 'total' FROM t1;

You are concerned that the first SELECT returns 0 whereas the second SELECT returns 1. This difference comes about because the RTREE index uses 32-bit floating point numbers (for storage space efficiency) whereas the main SQLite compute engine uses 64-bit floating point numbers. The x1=9223372036854775807 comparison occurs inside the RTREE virtual table on the first SELECT and is thus computed to 32-bits of accuracy whereas the comparison is computed inside the main SQLite compute engine on the second SELECT and thus is computed to 64-bits of accuracy. That is why they give different answers.

I'm not sure this counts as a bug, because you have violated an important rule of computer programming, which is that you should never do equality comparisons between floating-point numbers. Or if you really must do equality comparisons on floating-point numbers, you should not be surprised if the results are different from what would happen with an exact (infinite precision) calculation.

Nevertheless, I have made a change to RTREE such that equality comparisons on RTREE coordinates are duplicated in the main compute engine, which should hopefully give more consistent results for fuzzer-generated queries.