Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Create and populate tables t1 and t2 with the following commands:
CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
INSERT INTO t1 VALUES (11,0);
INSERT INTO t1 VALUES (12,5);
INSERT INTO t1 VALUES (15,0);
CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
INSERT INTO t2 VALUES (11,1);
INSERT INTO t2 VALUES (12,2);
INSERT INTO t2 VALUES (15,4);
The subquery
SELECT * FROM t1
WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
returns wrong results with the setting
set optimizer_switch='semijoin=on'; :
MariaDB [test]> set optimizer_switch='semijoin=on';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM t1
-> WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
-----+
| pk | i |
-----+
| 12 | 5 |
| 12 | 5 |
| 12 | 5 |
-----+
With the setting:
set optimizer_switch='semijoin=off';
the result is correct:
MariaDB [test]> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM t1
-> WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
-----+
| pk | i |
-----+
| 12 | 5 |
-----+
1 row in set (0.01 sec)
The bug is reproducible on both 5.3 tree and 5.3-subqueries-mwl90
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 752992