Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Some queries with IN subqueries that contain left joins may return incomplete result set.
The following test case provides such a query:
CREATE TABLE t1 (a varchar(1024));
INSERT INTO t1 VALUES ('v'), ('we');
CREATE TABLE t2 (
a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int
);
INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6);
CREATE TABLE t3 (b int, c int);
INSERT INTO t3 VALUES (4,4);
set @tmp_optimizer_switch=@@optimizer_switch;
set optimizer_switch='semijoin=off';
set optimizer_switch='materialization=off';
et join_cache_level=2;
SELECT * FROM t1
WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
WHERE t2.c < 10 OR t3.c > 1);
set join_cache_level = default;
set optimizer_switch=@tmp_optimizer_switch;
DROP TABLE t1,t2,t3;
The last SELECT returns:
MariaDB [test]> SELECT * FROM t1
-> WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
-> WHERE t2.c < 10 OR t3.c > 1);
------
| a |
------
| v |
------
while the correct answer is:
MariaDB [test]> SELECT * FROM t1
-> WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
-> WHERE t2.c < 10 OR t3.c > 1);
------
| a |
------
| v |
| we |
------
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 922971
Re: Missing row in the result set of a query with IN subquery containing a left join
The above test case is a slightly modified version of the test case for bug #12546542 from mysql-5.6. code line.