SQLite

View Ticket
Login
Plaintext
2011-09-15
18:54 • New ticket [002caede] LEFT JOIN with OR terms in WHERE clause causes assertion fault. (artifact: 80aac112 user: drh)
2009-12-16
22:11 • Fixed ticket [31338dca]: OR operator in WHERE clause gives wrong answer when indexed plus 2 other changes (artifact: 4fe3aebf user: drh)
22:10
Ensure WHERE clause terms involving tables on the right end of a join are not prematurely evaluated when tables on the left end of the join make use of the OR-clause optimization. Fix for ticket [31338dca7e]. (check-in: 2c2de252 user: drh tags: trunk)
16:52 • Ticket [31338dca] OR operator in WHERE clause gives wrong answer when indexed status still Open with 1 other change (artifact: c9fd070a user: drh)
16:40 • Ticket [31338dca]: 1 change (artifact: df44f515 user: drh)
15:18 • Ticket [31338dca]: 3 changes (artifact: eec39f02 user: drh)
15:10 • New ticket [31338dca]. (artifact: 654dbb46 user: drh)
Ticket Hash: 31338dca7e3ddc9e7397f881218a23a9d4b251cd
Title: OR operator in WHERE clause gives wrong answer when indexed
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2009-12-16 22:11:58
Version Found In: 3.6.21
Description:
The following code shows two identical queries, one before indices were created and the other after. The presence of indices should never change the result of a query, but in this case it does: 
CREATE TABLE t1(x);
CREATE TABLE t2(y);
INSERT INTO t1 VALUES(111);
INSERT INTO t1 VALUES(222);
INSERT INTO t2 VALUES(333);
INSERT INTO t2 VALUES(444);

SELECT 'noindex', * FROM t1, t2
 WHERE (x=111 AND y!=444) OR x=222;

CREATE INDEX t1x ON t1(x);

SELECT 'w/index', * FROM t1, t2
 WHERE (x=111 AND y!=444) OR x=222;

This is problem is observed in SQLite versions 3.6.18 through 3.6.21. Versions 3.6.17 and earlier appear to work.

drh added on 2009-12-16 15:18:45:
Problem introduced on 2009-08-13 by check-in [19f799b32f9d]

drh added on 2009-12-16 16:40:47:
Another interesting test case. This one gives an assertion fault. 

CREATE TABLE t1(v,w);
CREATE TABLE t2(x,y);
CREATE TABLE t3(z);
INSERT INTO t1 VALUES(111,222);
INSERT INTO t1 VALUES(333,444);
INSERT INTO t2 VALUES(222,333);
INSERT INTO t2 VALUES(444,555);
INSERT INTO t3 VALUES(888);
INSERT INTO t3 VALUES(999);

CREATE INDEX t1v ON t1(v);
CREATE INDEX t2x ON t2(x);

SELECT * FROM t1, t2, t3
 WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444);

drh added on 2009-12-16 22:11:58:
Fixed by check-in [2c2de252666662f5459904fc33a9f2956cbff23c]