Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0.4, 5.3.12, 5.5.33a
-
None
-
None
Description
Test case:
SET optimizer_switch = 'semijoin=on'; |
|
# Any value > 2 will do |
SET join_cache_level = 3; |
|
CREATE TABLE t1 (pk1 INT PRIMARY KEY, i1 INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1,4),(2,5); |
|
CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (1,'v'),(7,'s'); |
|
CREATE TABLE t3 (pk3 INT PRIMARY KEY, i3 INT, c3 CHAR(1), INDEX(i3), INDEX(c3,i3)) ENGINE=MyISAM; |
INSERT INTO t3 VALUES (1,7,'g'),(2,4,'p'),(3,1,'q'); |
|
CREATE TABLE t4 (i4 INT) ENGINE=MyISAM; |
INSERT INTO t4 VALUES (1); |
|
SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) |
WHERE ( i2, pk1 ) IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2; |
Actual result:
pk1 i1 i2 c2 pk3 i3 c3
|
1 4 7 s NULL NULL NULL
|
Expected result:
pk1 i1 i2 c2 pk3 i3 c3
|
1 4 7 s NULL NULL NULL
|
1 4 1 v NULL NULL NULL
|
Reproducible on older versions down to 5.3.5 at least.
EXPLAIN (from current 5.5):
WHERE ( i2, pk1 ) IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t4 system NULL NULL NULL NULL 1 100.00 Using filesort
|
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 100.00
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 PRIMARY t3 hash_index i3 #hash#i3:i3 5:5 test.t2.i2 3 66.67 Start temporary; End temporary; Using join buffer (flat, BNLH join)
|
1 PRIMARY t3 hash_ALL c3 #hash#c3 2 test.t2.c2 3 33.33 Using where; Using join buffer (flat, BNLH join)
|
Warnings:
|
Note 1003 select 1 AS `pk1`,4 AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`c2` AS `c2`,`test`.`t3`.`pk3` AS `pk3`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`c3` AS `c3` from `test`.`t1` semi join (`test`.`t3`) join `test`.`t2` left join `test`.`t3` on(((`test`.`t3`.`c3` = `test`.`t2`.`c2`) and (`test`.`t2`.`c2` is not null))) where (`test`.`t3`.`i3` = `test`.`t2`.`i2`) order by 4,`test`.`t2`.`c2`
|