Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.1, 5.5.29
-
None
-
None
Description
The following test case
|
set optimizer_switch='exists_to_in=on'; |
|
CREATE TABLE t1 (i1 INT, c1 VARCHAR(1)) ENGINE=MyISAM; |
INSERT t1 VALUES (7,'v'),(3,'y'); |
|
CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES ('y'),('y'); |
|
CREATE TABLE t3 (c3 VARCHAR(1)) ENGINE=MyISAM; |
INSERT INTO t3 VALUES |
('j'),('v'),('c'),('m'),('d'), |
('d'),('y'),('t'),('d'),('s'); |
|
SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') |
WHERE EXISTS ( SELECT 1 FROM t1, t3 WHERE c3 = c1 AND c1 = outer_t1.c1 ); |
|
Produces 2 rows when it's executed with exists_to_in=on:
i1 c1 c2
|
7 v NULL
|
3 y NULL
|
and 3 rows without exists_to_in:
i1 c1 c2
|
3 y y
|
3 y y
|
7 v NULL
|
The latter result is correct.
EXPLAIN with exists_to_in=on, otherwise default optimizer_switch:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 2 100.00
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00
|
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1276 Field or reference 'test.outer_t1.c1' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`outer_t1`.`i1` AS `i1`,`test`.`outer_t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` `outer_t1` semi join (`test`.`t1` join `test`.`t3`) left join `test`.`t2` on((((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and (`test`.`t2`.`c2` = `test`.`outer_t1`.`c1`)) or (`test`.`outer_t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t1`.`c1`)
|
revision-id: sanja@askmonty.org-20130213131832-b1sk3puofj29jubr
|
revno: 3502
|
branch-nick: 10.0-base-exists2in
|