Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 10.0.2
-
Component/s: None
-
Labels:None
Description
The following test case
CREATE TABLE t1 (i INT, c1 CHAR(5), c2 CHAR(5), t1_field VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'test1','test2','f'), (2,'test3','test4','d'); CREATE TABLE t2 (t2_field VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES ('m'), ('b'); CREATE TABLE t3 (t3_field VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t3 VALUES ('b'),('c'); SELECT * FROM t1, t2 outer_t2 WHERE EXISTS ( SELECT 1 FROM t2, t3 WHERE t3_field = outer_t2.t2_field AND t2_field <= t1_field );
Produces 2 rows if exists_to_in=off and an empty result set if exists_to_in=on. Two rows is the correct result.
Expected result:
i c1 c2 t1_field t2_field -------------------------------------------------- 1 test1 test2 f b 2 test3 test4 d b
EXPLAIN with exists_to_in=on, otherwise default optimizer_switch:
EXPLAIN EXTENDED SELECT * FROM t1, t2 outer_t2 WHERE EXISTS ( SELECT 1 FROM t2, t3 WHERE t3_field = outer_t2.t2_field AND t2_field <= t1_field ) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY outer_t2 ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Start temporary; Using join buffer (flat, BNL join) 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: Note 1276 Field or reference 'test.outer_t2.t2_field' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.t1_field' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`t1_field` AS `t1_field`,`test`.`outer_t2`.`t2_field` AS `t2_field` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) join `test`.`t2` `outer_t2` where ((`test`.`t3`.`t3_field` = `test`.`outer_t2`.`t2_field`) and (`test`.`t2`.`t2_field` <= `test`.`t1`.`t1_field`))
revision-id: sanja@askmonty.org-20130207125211-tdiknbz6344njon4 revno: 3488 branch-nick: 10.0-base-exists2in
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
the problem is that weedout table was not cleaned or its cleanup (start) made on wrong join_tab.
(if change order of t2 records everything will work)
As was explained by Sergey Petrunia plan is incorrect because outer table t1 goes after weedout table end.
The problem is that subquery does not report t1 as used table (used_tables()).
transformed query (even if semi-join replaced with join return correct result, so transformation is correct:
select `test`.`t1`.`i` AS `i`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS
`c2`,`test`.`t1`.`t1_field` AS `t1_field`,`test`.`outer_t2`.`t2_field` AS
`t2_field` from `test`.`t1` join (`test`.`t2` join `test`.`t3`) join
`test`.`t2` `outer_t2` where ((`test`.`t3`.`t3_field` = `test`.`outer_t2`.`t2_field`) and (`test`.`t2`.`t2_field` <= `test`.`t1`.`t1_field`));
i c1 c2 t1_field t2_field
1 test1 test2 f b
2 test3 test4 d b