Overview
Artifact ID: | 4cebcddb11fdd466c1a02c253f1fafcdfa8621ff |
---|---|
Ticket: | 80ba201079ea608071d22a57856b940ea3ac53ce
Bug involving subqueries and the OR optimization |
User & Date: | dan 2010-12-06 16:01:30 |
Changes
- comment changed to:
SELECT statements can return incorrect results in certain cases where the following are true: <ul> <li> The query is a join, <li> The query takes advantage of the OR optimization, <li> At least one branch of the optimized OR expression in the WHERE clause involves a subquery. <li> 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. </ul> For example: <verbatim> 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')) </verbatim> 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.
- Untracked field detected: "Application_Fault"
- foundin changed to: "3.7.3"
- severity changed to: "Important"
- status changed to: "Open"
- subsystem changed to: "Code_Generator"
- title changed to: "Bug involving subqueries and the OR optimization"
- type changed to: "Code_Defect"