/ Ticket Change Details
Login
Overview

Artifact ID: 4cebcddb11fdd466c1a02c253f1fafcdfa8621ff
Ticket: 80ba201079ea608071d22a57856b940ea3ac53ce
Bug involving subqueries and the OR optimization
User & Date: dan 2010-12-06 16:01:30
Changes

  1. Change comment to:

    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.

  2. Change detected to "Application_Fault"
  3. Change foundin to "3.7.3"
  4. Change severity to "Important"
  5. Change status to "Open"
  6. Change subsystem to "Code_Generator"
  7. Change title to "Bug involving subqueries and the OR optimization"
  8. Change type to "Code_Defect"