SQLite Forum

Incorrect result may caused by Distinct
Login

Incorrect result may caused by Distinct

(1) By syang (ysy111) on 2024-03-07 09:32:30 [source]

description

Since c2 is 0.2,so c2/0.1 should be evaluated as true in sql2 where condition. Sql and sql2 should both return 0.2. However, sql2 return nothing.

--sql1
sqlite> SELECT c2 FROM v0 FULL OUTER JOIN vt0 ON ((UPPER( c3))<(NULL)) LEFT OUTER JOIN t1 ON 1;
0.2

--sql2
sqlite> SELECT c2 FROM v0 FULL OUTER JOIN vt0 ON ((UPPER( c3))<(NULL)) LEFT OUTER JOIN t1 ON 1 WHERE c2/0.1;
sqlite>

how to repeat

It can be reproduced in CLI(SQLite version 3.46.0 2024-03-06 20:49:05 ) . If you delete distinct key word,the bug can't be reproduced.

CREATE VIRTUAL TABLE vt0 USING fts5(c0, c1 UNINDEXED);
CREATE TABLE t1 (c2 float);
CREATE INDEX i0 ON t1(NULL);
INSERT INTO t1(c2) VALUES (0.2);
CREATE VIEW v0(c3) AS SELECT DISTINCT c2 FROM t1;

SELECT c2 FROM v0 FULL OUTER JOIN vt0 ON ((UPPER( c3))<(NULL)) LEFT OUTER JOIN t1 ON 1;
SELECT c2 FROM v0 FULL OUTER JOIN vt0 ON ((UPPER( c3))<(NULL)) LEFT OUTER JOIN t1 ON 1 WHERE c2/0.1;

(2) By Richard Hipp (drh) on 2024-03-07 12:43:36 in reply to 1 [link] [source]

Fixed by check-in 720ce06d93a9e4cc.

The problem stems from the degenerate index "i0". The query planner was being tricked into thinking that index was useful for something and was in fact trying to use it.

The problem originated in check-in 44200596aa943963 (2023-02-10) which attempted to resolve a performance regression associated with indexes on expressions as reported by forum post 0a539c76db3b9e29.

The problem only arises if the schema contains an index with columns that are constants. No sensible database contains such indexes, and so this is not a problem that real-world applications need to be concerned with.