Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.4, 5.3.12, 5.5.33a
-
None
-
None
Description
The test case is nearly identical to the one provided in the comment to MDEV-5059 (https://mariadb.atlassian.net/browse/MDEV-5059?focusedCommentId=35418&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-35418); the only difference is that table t3 lost the index, and the wrong result now affects the default join_cache_level=2.
Test case:
SET optimizer_switch = 'semijoin=on'; |
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY, c1 VARCHAR(1)) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1,'v'),(2,'v'),(3,'c'),(4,NULL),(5,'x'); |
|
|
CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES ('x'); |
|
|
CREATE TABLE t3 (c3 VARCHAR(1)) ENGINE=MyISAM; |
INSERT INTO t3 VALUES ('x'),('d'); |
|
|
SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1; |
Actual result:
pk c1 c2
|
4 NULL x
|
Expected result:
pk c1 c2
|
4 NULL x
|
3 c x
|
1 v x
|
2 v x
|
5 x x
|
revision-id: bar@mnogosearch.org-20131016141313-63jt6geakz1e6dg2
|
revno: 3708
|
branch-nick: 5.3
|
EXPLAIN EXTENDED
|
SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 Using filesort
|
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 100.00
|
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t1.pk 1 100.00
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; End temporary
|
Warnings:
|
Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1`,'x' AS `c2` from `test`.`t1` semi join (`test`.`t3`) join `test`.`t1` join `test`.`t2` where (`test`.`t1`.`pk` = `test`.`t1`.`pk`) order by 'x',`test`.`t1`.`c1`
|
