Inconsistencies occur when sqlite executes the following queries
(1) By xiangwei on 2023-05-19 08:44:08 [source]
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;
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
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.