Details
Major
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
new test suite is:
CREATE TABLE t1 (i1 INT, c1 VARCHAR(1));
INSERT t1 VALUES (7,'v'),(3,'y');
CREATE TABLE t2 (c2 VARCHAR(1));
INSERT INTO t2 VALUES ('y'),('y');
CREATE TABLE t3 (c3 VARCHAR(1));
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 outer_t1.c1 IN ( SELECT c1 FROM t1, t3 WHERE c3 = c1);
set optimizer_switch='semijoin=off';
SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
WHERE outer_t1.c1 IN ( SELECT c1 FROM t1, t3 WHERE c3 = c1);
drop table t1,t2,t3;
Sergey,
It looks like this bug is also a legacy bug in multiple equalities:
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') WHERE outer_t1.c1 IN ( SELECT c1 FROM t1, t3 WHERE c3 = c1);
--------------------------------------------------------------------------------------------------------------------------------
| 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) |
--------------------------------------------------------------------------------------------------------------------------------
5 rows in set, 1 warning (0.00 sec)
MariaDB [test]> show warnings;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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`) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I will investigate the problem.
The fix for the bug was pushed into the 5.3 tree on 21-02-2013
transformed join (without semi-join) produces more or less expected results ...
select `test`.`outer_t1`.`i1` AS `i1`,`test`.`outer_t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` `outer_t1` 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`);
i1 c1 c2
3 y y
3 y y
3 y y
3 y y
7 v NULL
7 v NULL