From: Date: November 20 2008 4:34pm Subject: bzr commit into mysql-6.0-opt branch (timour:2701) Bug#36752 List-Archive: http://lists.mysql.com/commits/59405 X-Bug: 36752 Message-Id: <20081120153425.7CE12A1ACC@lapi> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #At file:///home/tkatchaounov/mysql/bzr/6.0-opt/ 2701 Timour Katchaounov 2008-11-20 [merge] BUG#36752: subquery materialization produces wrong results when comparing different types Manual merge with mysql-6.0-opt tree that fixes missing "drop table" statements in subselect_mat.test. modified: mysql-test/r/subselect.result mysql-test/r/subselect_mat.result mysql-test/r/subselect_no_mat.result mysql-test/r/subselect_no_opts.result mysql-test/r/subselect_no_semijoin.result mysql-test/t/subselect_mat.test sql/item_subselect.cc === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2008-10-20 09:16:47 +0000 +++ b/mysql-test/r/subselect.result 2008-11-19 15:27:23 +0000 @@ -4367,13 +4367,13 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key))) +Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`))))) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key))) +Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`))))) DROP TABLE t1; End of 5.0 tests. create table t_out (subcase char(3), === modified file 'mysql-test/r/subselect_mat.result' --- a/mysql-test/r/subselect_mat.result 2008-10-17 09:46:06 +0000 +++ b/mysql-test/r/subselect_mat.result 2008-11-20 15:34:15 +0000 @@ -41,7 +41,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), (`test`.`t1`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`))))) select * from t1 where a1 in (select b1 from t2 where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -52,7 +52,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), (`test`.`t1`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`))))) select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -63,7 +63,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); a1 a2 1 - 01 2 - 01 @@ -74,7 +74,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS `min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), (`test`.`t1`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS `min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`min(b2)`))))) select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -85,7 +85,7 @@ id select_type table type possible_keys 1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index 2 SUBQUERY t2i index it2i1,it2i3 it2i1 9 NULL 5 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`))))) select * from t1i where a1 in (select b1 from t2i where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -96,7 +96,7 @@ id select_type table type possible_keys 1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index 2 SUBQUERY t2i range it2i1,it2i3 it2i1 9 NULL 3 100.00 Using where; Using index for group-by Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1i`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`))))) select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -107,7 +107,7 @@ id select_type table type possible_keys 1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index 2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))) select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -118,7 +118,7 @@ id select_type table type possible_keys 1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index 2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), (`test`.`t1i`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))) select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); a1 a2 1 - 01 2 - 01 @@ -129,7 +129,7 @@ id select_type table type possible_keys 1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index 2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1i`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`min(b2)`))))) select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -140,7 +140,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2i range NULL it2i3 9 NULL 3 100.00 Using index for group-by Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1` ), (`test`.`t1`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`max(b2)`))))) select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); a1 a2 1 - 01 2 - 01 @@ -169,7 +169,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`min(b2)`))))) select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -209,7 +209,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); a1 a2 1 - 01 2 - 01 @@ -220,7 +220,7 @@ id select_type table type possible_keys 1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index 2 SUBQUERY t2i index NULL it2i3 18 NULL 5 100.00 Using index Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), (`test`.`t1i`.`a1` in on distinct_key))) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))) select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); a1 a2 1 - 01 2 - 01 @@ -275,7 +275,7 @@ id select_type table type possible_keys 4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), (`test`.`t1`.`a1` in on distinct_key))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key))) ), (`test`.`t1`.`a1` in on distinct_key)))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key wh ere ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`)))))) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and (a1, a2) in (select c1, c2 from t3 @@ -294,7 +294,7 @@ id select_type table type possible_keys 4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index 2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key))) and ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where ((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3i`.`c1` in on distinct_key))) ), (`test`.`t1i`.`a1` in on distinct_key)))) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))) and ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where ((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3i`.`c1` in on distinct_key where ((`test`.`t3i`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3i`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`c2`)))))) select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and (a1, a2) in (select c1, c2 from t3i @@ -317,7 +317,7 @@ id select_type table type possible_keys 4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), (`test`.`t2`.`b2` in on distinct_key))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key)))) ), (`test`.`t1`.`a1` in on distinct_key))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test` .`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key))) ), (`test`.`t1`.`a1` in on distinct_key)))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materi alized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`)))))) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 where c2 LIKE '%02') or @@ -342,7 +342,7 @@ id select_type table type possible_keys 3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (((`test`.`t2`.`b2`,(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key)))) and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where ( (`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),(`test`.`t3c`.`c1`,`test`.`t3c`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3c`.`c1` in on distinct_key))) ), (`test`.`t1`.`a1` in on distinct_key)))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (((`test`.`t2`.`b2`,(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c `.`c2` AS `c2` from `test`.`t3` `t3c` where ((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),(`test`.`t3c`.`c1`,`test`.`t3c`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3c`.`c1` in on distinct_key where ((`test`.`t3c`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3c`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`)))))) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 t3a where c1 = a1) or @@ -378,7 +378,7 @@ id select_type table type possible_keys 8 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), (`test`.`t2`.`b2` in on distinct_key))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key)))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on distinct_key))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key))) ), (`test`.`t1`.`a1` in on distinct_key))))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key))) and ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `t est`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where ((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3i`.`c1` in on distinct_key))) ), (`test`.`t1i`.`a1` in on distinct_key))))) +Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on dis tinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`))))))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` A S `a2` from `test`.`t1i` where (((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))) and ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where ((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3i`.`c1` in on distinct_key where ((`test`.`t3i`.`c1` = `materiali zed subselect`.`b1`) and (`test`.`t3i`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`c2`))))))) (select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 where c2 LIKE '%02') or @@ -407,7 +407,7 @@ id select_type table type possible_keys 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and ((`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and ((`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),

(`test`.`t3`.`c1` in on distinct_key))) ), (`test`.`t1`.`a1` in on distinct_key)))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and ((`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and ((`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),

(`test`.`t3`.`c1` in on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`)))))) select * from t1 where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and (a1, a2) in (select c1, c2 from t3 @@ -430,7 +430,7 @@ id select_type table type possible_keys 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and ((`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and ((`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (selec t `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key))) ), (`test`.`t3`.`c1` in on distinct_key)))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and ((`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and ((`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (selec t `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t3`.`c1` in on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`c1`) and (`test`.`t3`.`c2` = `materialized subselect`.`c2`)))))) select * from t1, t3 where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and (c1, c2) in (select c1, c2 from t3 @@ -476,7 +476,7 @@ id select_type table type possible_keys Warnings: Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (((`test`.`t2`.`b2`,(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key)))) and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where (((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`), (((`test`.`t3c`.`c1`) in t2i on it2i3 where (((`test`.`t2i`.`b2` > '0') or (`test`.`t2i`.`b2` = `test`.`t1`.`a2`)) and ((`test`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and ((`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and ((`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and ((`test`.`t1`.`a2`) = `test`.`t3c`.`c2`))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (((`test`.`t2`.`b2`,(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where (((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),(((`test`.`t3c`.`c1`) in t2i on it2i3 where (((`test`.`t2i`.`b2` > '0') or (`test`.`t2i`.`b2` = `test`.`t1`.`a2`)) and ((`test`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and ((`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and ((`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and ((`test`.`t1`.`a2`) = `test`.`t3c`.`c2`))))) explain extended select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -611,7 +611,7 @@ id select_type table type possible_keys 1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where (`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( (select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0') ), (`test`.`t1_16`.`a1` in on distinct_key))) +Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where (`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( (select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0') ), (`test`.`t1_16`.`a1` in on distinct_key where ((`test`.`t1_16`.`a1` = `materialized subselect`.`substring(b1,1,16)`))))) select left(a1,7), left(a2,7) from t1_16 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); @@ -640,7 +640,7 @@ id select_type table type possible_keys 1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where (`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2` ), (`test`.`t1_16`.`a1` in on distinct_key))) +Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where (`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2` ), (`test`.`t1_16`.`a1` in on distinct_key where ((`test`.`t1_16`.`a1` = `materialized subselect`.`group_concat(b1)`))))) select left(a1,7), left(a2,7) from t1_16 where a1 in (select group_concat(b1) from t2_16 group by b2); @@ -662,7 +662,7 @@ id select_type table type possible_keys 3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer 4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (concat(`test`.`t1`.`a1`,'x'),concat(`test`.`t1`.`a1`,'x') in ( (select left(`test`.`t1_16`.`a1`,8) AS `left(a1,8)` from `test`.`t1_16` where ((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`) in (select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from `test`.`t2_16` join `test`.`t2` where ((`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and (`test`.`t2`.`b1`,`test`.`t2`.`b1` in ( (select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0') ), (`test`.`t2`.`b1` in on distinct_key))) and ((`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and ((`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) ), (concat(`test`.`t1`.`a1`,'x') in on distinct_key))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (concat(`test`.`t1`.`a1`,'x'),concat(`test`.`t1`.`a1`,'x') in ( (select left(`test`.`t1_16`.`a1`,8) AS `left(a1,8)` from `test`.`t1_16` where ((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`) in (select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from `test`.`t2_16` join `test`.`t2` where ((`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and (`test`.`t2`.`b1`,`test`.`t2`.`b1` in ( (select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0') ), (`test`.`t2`.`b1` in on distinct_key where ((`test`.`t2`.`b1` = `materialized subselect`.`c1`))))) and ((`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and ((`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) ), (concat(`test`.`t1`.`a1`,'x') in on distinct_key where ((concat(`test`.`t1`.`a1`,'x') = `materialized subselect`.`left(a1,8)`))))) drop table t1_16, t2_16, t3_16; set @blob_len = 512; set @suffix_len = @blob_len - @prefix_len; @@ -724,7 +724,7 @@ id select_type table type possible_keys 1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where (`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( (select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0') ), (`test`.`t1_512`.`a1` in on distinct_key))) +Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where (`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( (select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0') ), (`test`.`t1_512`.`a1` in on distinct_key where ((`test`.`t1_512`.`a1` = `materialized subselect`.`substring(b1,1,512)`))))) select left(a1,7), left(a2,7) from t1_512 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); @@ -738,13 +738,11 @@ id select_type table type possible_keys 1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where (`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), (`test`.`t1_512`.`a1` in on distinct_key))) +Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where (`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), (`test`.`t1_512`.`a1` in on distinct_key where ((`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`))))) select left(a1,7), left(a2,7) from t1_512 where a1 in (select group_concat(b1) from t2_512 group by b2); left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x set @@group_concat_max_len = 256; explain extended select left(a1,7), left(a2,7) from t1_512 @@ -753,13 +751,11 @@ id select_type table type possible_keys 1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where (`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), (`test`.`t1_512`.`a1` in on distinct_key))) +Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where (`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), (`test`.`t1_512`.`a1` in on distinct_key where ((`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`))))) select left(a1,7), left(a2,7) from t1_512 where a1 in (select group_concat(b1) from t2_512 group by b2); left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x drop table t1_512, t2_512, t3_512; set @blob_len = 1024; set @suffix_len = @blob_len - @prefix_len; @@ -835,13 +831,11 @@ id select_type table type possible_keys 1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where (`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), (`test`.`t1_1024`.`a1` in on distinct_key))) +Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where (`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), (`test`.`t1_1024`.`a1` in on distinct_key where ((`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`))))) select left(a1,7), left(a2,7) from t1_1024 where a1 in (select group_concat(b1) from t2_1024 group by b2); left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x set @@group_concat_max_len = 256; explain extended select left(a1,7), left(a2,7) from t1_1024 @@ -850,13 +844,11 @@ id select_type table type possible_keys 1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where (`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), (`test`.`t1_1024`.`a1` in on distinct_key))) +Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where (`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), (`test`.`t1_1024`.`a1` in on distinct_key where ((`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`))))) select left(a1,7), left(a2,7) from t1_1024 where a1 in (select group_concat(b1) from t2_1024 group by b2); left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x drop table t1_1024, t2_1024, t3_1024; set @blob_len = 1025; set @suffix_len = @blob_len - @prefix_len; @@ -932,13 +924,11 @@ id select_type table type possible_keys 1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where (`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), (`test`.`t1_1025`.`a1` in on distinct_key))) +Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where (`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), (`test`.`t1_1025`.`a1` in on distinct_key where ((`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`))))) select left(a1,7), left(a2,7) from t1_1025 where a1 in (select group_concat(b1) from t2_1025 group by b2); left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x set @@group_concat_max_len = 256; explain extended select left(a1,7), left(a2,7) from t1_1025 @@ -947,13 +937,11 @@ id select_type table type possible_keys 1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where (`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), (`test`.`t1_1025`.`a1` in on distinct_key))) +Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where (`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), (`test`.`t1_1025`.`a1` in on distinct_key where ((`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`))))) select left(a1,7), left(a2,7) from t1_1025 where a1 in (select group_concat(b1) from t2_1025 group by b2); left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x drop table t1_1025, t2_1025, t3_1025; create table t1bit (a1 bit(3), a2 bit(3)); create table t2bit (b1 bit(3), b2 bit(3)); @@ -971,7 +959,7 @@ id select_type table type possible_keys 1 PRIMARY t1bit ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2bit ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` where ((`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`),(`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`) in ( (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2` from `test`.`t2bit` ), (`test`.`t1bit`.`a1` in on distinct_key))) +Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` where ((`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`),(`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`) in ( (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2` from `test`.`t2bit` ), (`test`.`t1bit`.`a1` in on distinct_key where ((`test`.`t1bit`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1bit`.`a2` = `materialized subselect`.`b2`))))) select bin(a1), bin(a2) from t1bit where (a1, a2) in (select b1, b2 from t2bit); @@ -1042,7 +1030,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`))))) select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1056,7 +1044,7 @@ id select_type table type possible_keys 1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`))))) select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1070,7 +1058,7 @@ id select_type table type possible_keys 1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index 2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`))))) select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1083,7 +1071,7 @@ id select_type table type possible_keys 1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index 2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`))))) select a from t1 group by a having a in (select c from t2 where d >= 20); a 2 @@ -1095,7 +1083,7 @@ id select_type table type possible_keys 1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index 2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), (`test`.`t1`.`a` in on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`))))) select a from t1 group by a having a in (select c from t2 where d >= 20); a 2 @@ -1175,3 +1163,20 @@ id select_type table type possible_keys select min(a1) from t1 where 7 in (select b1 from t2); min(a1) NULL +drop table t1,t2; +create table t1 (a char(2), b varchar(10)); +insert into t1 values ('a', 'aaa'); +insert into t1 values ('aa', 'aaaa'); +set @@optimizer_switch='no_semijoin'; +explain select a,b from t1 where b in (select a from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +select a,b from t1 where b in (select a from t1); +a b +prepare st1 from "select a,b from t1 where b in (select a from t1)"; +execute st1; +a b +execute st1; +a b +drop table t1; === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2008-08-27 16:48:24 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2008-11-19 15:27:23 +0000 @@ -4371,13 +4371,13 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key))) +Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`))))) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key))) +Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`))))) DROP TABLE t1; End of 5.0 tests. create table t_out (subcase char(3), === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2008-08-27 16:48:24 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2008-11-19 15:27:23 +0000 @@ -4371,13 +4371,13 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key))) +Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`))))) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key))) +Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`))))) DROP TABLE t1; End of 5.0 tests. create table t_out (subcase char(3), === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2008-08-27 16:48:24 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2008-11-19 15:27:23 +0000 @@ -1300,7 +1300,7 @@ id select_type table type possible_keys 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), (`test`.`t2`.`a` in on distinct_key))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), (`test`.`t2`.`a` in on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`))))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1310,7 +1310,7 @@ id select_type table type possible_keys 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), (`test`.`t2`.`a` in on distinct_key))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), (`test`.`t2`.`a` in on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`))))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1321,7 +1321,7 @@ id select_type table type possible_keys 2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), (`test`.`t2`.`a` in on distinct_key))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), (`test`.`t2`.`a` in on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`))))) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1343,7 +1343,7 @@ id select_type table type possible_keys 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), (`test`.`t2`.`a` in on distinct_key))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), (`test`.`t2`.`a` in on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`))))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1353,7 +1353,7 @@ id select_type table type possible_keys 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), (`test`.`t2`.`a` in on distinct_key))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), (`test`.`t2`.`a` in on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`))))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1364,7 +1364,7 @@ id select_type table type possible_keys 2 SUBQUERY t3 index a a 5 NULL 3 100.00 Using index 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), (`test`.`t2`.`a` in on distinct_key))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), (`test`.`t2`.`a` in on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`))))) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1380,7 +1380,7 @@ id select_type table type possible_keys 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), (`test`.`t2`.`a` in on distinct_key))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,`test`.`t2`.`a` in ( (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), (`test`.`t2`.`a` in on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`))))) drop table t0, t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -4371,13 +4371,13 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key))) +Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`))))) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key))) +Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`))))) DROP TABLE t1; End of 5.0 tests. create table t_out (subcase char(3), === modified file 'mysql-test/t/subselect_mat.test' --- a/mysql-test/t/subselect_mat.test 2008-10-17 09:46:06 +0000 +++ b/mysql-test/t/subselect_mat.test 2008-11-20 15:34:15 +0000 @@ -831,4 +831,19 @@ select min(a1) from t1 where 7 in (selec set @@optimizer_switch='no_materialization'; explain select min(a1) from t1 where 7 in (select b1 from t2); select min(a1) from t1 where 7 in (select b1 from t2); +drop table t1,t2; +# +# BUG#36752 "subquery materialization produces wrong results when comparing different types" +# +create table t1 (a char(2), b varchar(10)); +insert into t1 values ('a', 'aaa'); +insert into t1 values ('aa', 'aaaa'); + +set @@optimizer_switch='no_semijoin'; +explain select a,b from t1 where b in (select a from t1); +select a,b from t1 where b in (select a from t1); +prepare st1 from "select a,b from t1 where b in (select a from t1)"; +execute st1; +execute st1; +drop table t1; === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2008-11-19 12:55:52 +0000 +++ b/sql/item_subselect.cc 2008-11-20 15:34:15 +0000 @@ -3114,11 +3114,54 @@ bool subselect_hash_sj_engine::init_perm KEY_PART_INFO *cur_key_part= tmp_key->key_part; store_key **ref_key= tab->ref.key_copy; uchar *cur_ref_buff= tab->ref.key_buff; + + /* + Create an artificial condition to post-filter those rows matched by index + lookups that cannot be distinguished by the index lookup procedure, e.g. + because of truncation. Prepared statements execution requires that + fix_fields is called for every execution. In order to call fix_fields we + need to create a Name_resolution_context and a corresponding TABLE_LIST + for the temporary table for the subquery, so that all column references + to the materialized subquery table can be resolved correctly. + */ + DBUG_ASSERT(cond == NULL); + if (!(cond= new Item_cond_and)) + DBUG_RETURN(TRUE); + /* + Table reference for tmp_table that is used to resolve column references + (Item_fields) to columns in tmp_table. + */ + TABLE_LIST *tmp_table_ref; + if (!(tmp_table_ref= (TABLE_LIST*) thd->calloc(sizeof(TABLE_LIST)))) + DBUG_RETURN(TRUE); + + tmp_table_ref->init_one_table(NULL, 0, "materialized subselect", 22, + "materialized subselect", TL_READ); + tmp_table_ref->table= tmp_table; + + /* Name resolution context for all tmp_table columns created below. */ + Name_resolution_context *context= new Name_resolution_context; + context->init(); + context->first_name_resolution_table= + context->last_name_resolution_table= tmp_table_ref; for (uint i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) { - tab->ref.items[i]= item_in->left_expr->element_index(i); + Item_func_eq *eq_cond; /* New equi-join condition for the current column. */ + /* Item for the corresponding field from the materialized temp table. */ + Item_field *right_col_item; int null_count= test(cur_key_part->field->real_maybe_null()); + tab->ref.items[i]= item_in->left_expr->element_index(i); + + if (!(right_col_item= new Item_field(thd, context, cur_key_part->field)) || + !(eq_cond= new Item_func_eq(tab->ref.items[i], right_col_item)) || + ((Item_cond_and*)cond)->add(eq_cond)) + { + delete cond; + cond= NULL; + DBUG_RETURN(TRUE); + } + *ref_key= new store_key_item(thd, cur_key_part->field, /* TODO: the NULL byte is taken into account in @@ -3135,6 +3178,9 @@ bool subselect_hash_sj_engine::init_perm tab->ref.key_err= 1; tab->ref.key_parts= tmp_key_parts; + if (cond->fix_fields(thd, &cond)) + DBUG_RETURN(TRUE); + DBUG_RETURN(FALSE); } @@ -3154,6 +3200,12 @@ bool subselect_hash_sj_engine::init_runt the subquery if not yet created. */ materialize_engine->prepare(); + /* + Repeat name resolution for 'cond' since cond is not part of any + clause of the query, and it is not 'fixed' during JOIN::prepare. + */ + if (cond && !cond->fixed && cond->fix_fields(thd, &cond)) + return TRUE; /* Let our engine reuse this query plan for materialization. */ materialize_join= materialize_engine->join; materialize_join->change_result(result);