SQLite

View Ticket
Login
Ticket Hash: 80ba201079ea608071d22a57856b940ea3ac53ce
Title: Bug involving subqueries and the OR optimization
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Code_Generator Resolution: Fixed
Last Modified: 2010-12-07 15:10:31
Version Found In: 3.7.3
Description:
SELECT statements can return incorrect results in certain cases where the following are true:
  • The query is a join,
  • The query takes advantage of the OR optimization,
  • At least one branch of the optimized OR expression in the WHERE clause involves a subquery.
  • At least one branch of the optimized OR expression in the WHERE clause refers to a column of a table other than the table to which the OR optimization applies.

For example:

  CREATE TABLE t1(a);
  INSERT INTO t1 VALUES('A');
  CREATE INDEX i1 ON t1(a);

  CREATE TABLE t2(b);
  INSERT INTO t2 VALUES('B');

  CREATE TABLE t3(c);
  INSERT INTO t3 VALUES('C');

  SELECT * FROM t1, t2 WHERE
     (a='A' AND b='X')
  OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'))

The bug causes the query above returns zero rows instead of the expected result - one row.

This bug was discovered in a real application and reported on the mailing list. Bisection reveals that it was introduced on 2009-08-13 by commit [86a06dd049]. It first appeared in release 3.6.18.