SQLite

View Ticket
Login
Ticket Hash: 123c9ba32130a6c9d43278229da12a076583005d
Title: Incorrect result when an index is used for an ordered join
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-12-13 18:02:03
Version Found In: 3.21.0
User Comments:
drh added on 2017-12-13 16:33:55:

The query in the SQL below gets a different answer after the index is created. The answer without the index is correct.

CREATE TABLE t1(a, b);  INSERT INTO t1 VALUES(1,2);
CREATE TABLE t2(x, y);  INSERT INTO t2 VALUES(1,3);
SELECT y FROM t1, t2 WHERE a=x AND b<=y ORDER BY b DESC;
CREATE INDEX t1ab ON t1(a,b);
SELECT y FROM t1, t2 WHERE a=x AND b<=y ORDER BY b DESC;

This problem appears to have been introduced by check-in [559733b09e] on 2016-05-20, 572 days ago. The problem first appeared in version 3.14.0. The problem was discovered in the wild and reported to the SQLite developers via private email.