[Maria-developers] Rev 2793: Always refer to materialized table as "SUBSELECT#%d" where %d is select number in file:///home/psergey/dev/maria-5.3-subqueries-r12/

on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#4`.`c2`))))))) join `test`.`t1` where ((`SUBQUERY#5`.`c2` = `SUBQUERY#2`.`b2`) and (`test`.`t1`.`a2` = `SUBQUERY#2`.`b2`) and (`SUBQUERY#5`.`c1` = `SUBQUERY#2`.`b1`) and (`test`.`t1`.`a1` = `SUBQUERY#2`.`b1`)) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 where c2 LIKE '%02') or @@ -368,7 +368,7 @@ 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 <materialize> (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` `t3c` where ((`materialized subselect`.`b2` = `test`.`t3c`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3c`.`c1`))) join `test`.`t1` where ((`materialized subselect`.`c2` = `test`.`t1`.`a2`) and (`materialized subselect`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` fr om `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` `t3c` where ((`SUBQUERY#6`.`b2` = `test`.`t3c`.`c2`) and (`SUBQUERY#6`.`b1` = `test`.`t3c`.`c1`))) join `test`.`t1` where ((`SUBQUERY#5`.`c2` = `test`.`t1`.`a2`) and (`SUBQUERY#5`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` li ke '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#4`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 t3a where c1 = a1) or @@ -410,7 +410,7 @@ 8 SUBQUERY t2i index it2i1,it2i3 # # # 5 100.00 # NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL # Warnings: -Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup> (`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a1` = `materialized subselect`.`b1`))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`materialized subselect`.`b2` = `test`.`t3i`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i ` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`))) +Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#6`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#6`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#3`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary tabl e> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#4`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`SUBQUERY#5`.`c2` = `SUBQUERY#2`.`b2`) and (`test`.`t1`.`a2` = `SUBQUERY#2`.`b2`) and (`SUBQUERY#5`.`c1` = `SUBQUERY#2`.`b1`) and (`test`.`t1`.`a1` = `SUBQUERY#2`.`b1`))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`SUBQUERY#10`.`b2` = `test`.`t3i`.`c2`) and (`SUBQUERY#10`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`SUBQUERY#9`.`c2` = `SUBQUERY#8`.`b2`) and (`test`.`t1i`.`a2` = `SUBQUERY#8`.`b2`) and (`SUBQUERY#9`.`c1` = `SUBQUERY#8`.`b1`) and ( `test`.`t1i`.`a1` = `SUBQUERY#8`.`b1`))) (select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 where c2 LIKE '%02') or @@ -441,7 +441,7 @@ 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` where ((`materialized subselect`.`c2` = `test`.`t1`.`a2`) and (`materialized subselect`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`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 (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`t est`.`t1`.`a2`) = `test`.`t2`.`b2`))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#5`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#5`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` where ((`SUBQUERY#4`.`c2` = `test`.`t1`.`a2`) and (`SUBQUERY#4`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`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 (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) 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 @@ -466,7 +466,7 @@ 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT <union2,3> 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 <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c2` = `materialized subselect`.`c2`) and (`materialized subselect`.`c1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`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 (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#5`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#5`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c2` = `SUBQUERY#4`.`c2`) and (`SUBQUERY#4`.`c1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`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 ( <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) 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 @@ -512,7 +512,7 @@ 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 (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where (<i n_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3c`.`c1`) in t2i on it2i3 where (((`test`.`t2i`.`b2` > '0') or (`test`.`t2i`.`b2` = `test`.`t1`.`a2`)) and (<cache>(`test`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#4`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where (<in_optimizer> ((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3c`.`c1`) in t2i on it2i3 where (((`test`.`t2i`.`b2` > '0') or (`test`.`t2i`.`b2` = `test`.`t1`.`a2`)) and (<cache>(`test`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and (<cache>(`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 @@ -648,7 +648,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 20 test.t1_16.a1 1 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 <materialize> (select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0')) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `materialized subselect`.`substring(b1,1,16)`) +Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0')) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `SUBQUERY#2`.`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'); @@ -678,7 +678,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_16.a1 1 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 <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `SUBQUERY#2`.`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); @@ -701,7 +701,7 @@ 3 DEPENDENT SUBQUERY SUBQUERY#4 eq_ref distinct_key distinct_key 9 test.t2.b1 1 100.00 Using where 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 <in_optimizer>(concat(`test`.`t1`.`a1`,'x'),<exists>(select 1 AS `Not_used` from `test`.`t1_16` where (<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),<exists>(select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from <materialize> (select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0')) join `test`.`t2_16` join `test`.`t2` where ((`materialized subselect`.`c1` = `test`.`t2`.`b1`) and (`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) and (<cache>(concat(`test`.`t1`.`a1`,'x')) = left(`test`.`t1_16`.`a1`,8))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(concat(`test`.`t1`.`a1`,'x'),<exists>(select 1 AS `Not_used` from `test`.`t1_16` where (<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),<exists>(select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from <materialize> (select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0')) join `test`.`t2_16` join `test`.`t2` where ((`SUBQUERY#4`.`c1` = `test`.`t2`.`b1`) and (`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) and (<cache>(concat(`test`.`t1`.`a1`,'x')) = left(`test`.`t1_16`.`a1`,8))))) drop table t1_16, t2_16, t3_16; set @blob_len = 512; set @suffix_len = @blob_len - @prefix_len; @@ -764,7 +764,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 517 test.t1_512.a1 1 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 <materialize> (select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0')) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `materialized subselect`.`substring(b1,1,512)`) +Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0')) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `SUBQUERY#2`.`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'); @@ -779,7 +779,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 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 <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `SUBQUERY#2`.`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); @@ -793,7 +793,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 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 <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `SUBQUERY#2`.`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); @@ -874,7 +874,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 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 <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `SUBQUERY#2`.`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); @@ -888,7 +888,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 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 <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `SUBQUERY#2`.`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); @@ -969,7 +969,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 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 <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `SUBQUERY#2`.`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); @@ -983,7 +983,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 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 <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `SUBQUERY#2`.`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); @@ -1006,7 +1006,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 10 test.t1bit.a1,test.t1bit.a2 1 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 <materialize> (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2` from `test`.`t2bit`) join `test`.`t1bit` where ((`test`.`t1bit`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1bit`.`a2` = `materialized subselect`.`b2`)) +Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from <materialize> (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2` from `test`.`t2bit`) join `test`.`t1bit` where ((`test`.`t1bit`.`a1` = `SUBQUERY#2`.`b1`) and (`test`.`t1bit`.`a2` = `SUBQUERY#2`.`b2`)) select bin(a1), bin(a2) from t1bit where (a1, a2) in (select b1, b2 from t2bit); @@ -1078,7 +1078,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`materialized subselect`.`c` = `test`.`t1`.`a`) +Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`SUBQUERY#2`.`c` = `test`.`t1`.`a`) select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1093,7 +1093,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`materialized subselect`.`c` = `test`.`t1`.`a`) +Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`SUBQUERY#2`.`c` = `test`.`t1`.`a`) select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1108,7 +1108,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`materialized subselect`.`c` = `test`.`t1`.`a`) +Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`SUBQUERY#2`.`c` = `test`.`t1`.`a`) select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1121,7 +1121,7 @@ 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 <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `SUBQUERY#2`.`c`))))) select a from t1 group by a having a in (select c from t2 where d >= 20); a 2 @@ -1133,7 +1133,7 @@ 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 <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `SUBQUERY#2`.`c`))))) select a from t1 group by a having a in (select c from t2 where d >= 20); a 2 @@ -1253,7 +1253,7 @@ SET @@optimizer_switch='default,semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); @@ -1275,7 +1275,7 @@ EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);

At file:///home/psergey/dev/maria-5.3-subqueries-r12/ ------------------------------------------------------------ revno: 2793 revision-id: psergey@askmonty.org-20100406212909-r07zximlfpwmyxhm parent: psergey@askmonty.org-20100406103533-0upq6g7sse9sjstz committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.3-subqueries-r12 timestamp: Wed 2010-04-07 01:29:09 +0400 message: Always refer to materialized table as "SUBSELECT#%d" where %d is select number - for Item-based materialization, it was "materialized subselect" - for SJ-Materialization it was "subquery%d" === modified file 'mysql-test/r/group_min_max.result' --- a/mysql-test/r/group_min_max.result 2010-04-06 08:10:58 +0000 +++ b/mysql-test/r/group_min_max.result 2010-04-06 21:29:09 +0000 @@ -2256,7 +2256,7 @@ a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 8 -1 PRIMARY t1_outer ref a a 5 materialized subselect.max(b) 2 Using index +1 PRIMARY t1_outer ref a a 5 SUBQUERY#2.max(b) 2 Using index 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-04-05 20:16:45 +0000 +++ b/mysql-test/r/subselect.result 2010-04-06 21:29:09 +0000 @@ -4328,14 +4328,14 @@ 1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`SUBQUERY#2`.`1` = 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 1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00 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 <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`SUBQUERY#2`.`1` = 1) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. === modified file 'mysql-test/r/subselect3.result' --- a/mysql-test/r/subselect3.result 2010-04-05 20:16:45 +0000 +++ b/mysql-test/r/subselect3.result 2010-04-06 21:29:09 +0000 @@ -1017,7 +1017,7 @@ explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort 1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where @@ -1035,7 +1035,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY subselect3 eq_ref unique_key unique_key 5 func 1 +2 DEPENDENT SUBQUERY SUBQUERY#3 eq_ref unique_key unique_key 5 func 1 3 SUBQUERY Z ALL NULL NULL NULL NULL 2 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; subq @@ -1158,7 +1158,7 @@ insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1 PRIMARY t3 ref a a 5 test.t2.a 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where a in (select a from t2); @@ -1207,7 +1207,7 @@ explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); @@ -1241,14 +1241,14 @@ explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where create table t4 as select a as x, a as y from t1; explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where 2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where drop table t0,t1,t2,t3,t4; @@ -1274,14 +1274,14 @@ explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 10 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 10 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=default; @@ -1320,7 +1320,7 @@ explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 15 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 15 func 1 2 SUBQUERY X ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY Y ALL NULL NULL NULL NULL 6 Using join buffer 2 SUBQUERY Z ALL NULL NULL NULL NULL 6 Using join buffer @@ -1393,7 +1393,7 @@ WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1.00 1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer 2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 === modified file 'mysql-test/r/subselect3_jcl6.result' --- a/mysql-test/r/subselect3_jcl6.result 2010-04-05 20:16:45 +0000 +++ b/mysql-test/r/subselect3_jcl6.result 2010-04-06 21:29:09 +0000 @@ -1021,7 +1021,7 @@ explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort 1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where @@ -1040,7 +1040,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY subselect3 eq_ref unique_key unique_key 5 func 1 +2 DEPENDENT SUBQUERY SUBQUERY#3 eq_ref unique_key unique_key 5 func 1 3 SUBQUERY Z ALL NULL NULL NULL NULL 2 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; subq @@ -1163,7 +1163,7 @@ insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where a in (select a from t2); @@ -1212,7 +1212,7 @@ explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); @@ -1246,14 +1246,14 @@ explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where create table t4 as select a as x, a as y from t1; explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; Using join buffer 2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where drop table t0,t1,t2,t3,t4; @@ -1279,14 +1279,14 @@ explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 10 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 10 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=default; @@ -1325,7 +1325,7 @@ explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 15 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 15 func 1 2 SUBQUERY X ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY Y ALL NULL NULL NULL NULL 6 Using join buffer 2 SUBQUERY Z ALL NULL NULL NULL NULL 6 Using join buffer @@ -1398,7 +1398,7 @@ WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1.00 1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer 2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer === modified file 'mysql-test/r/subselect4.result' --- a/mysql-test/r/subselect4.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect4.result 2010-04-06 21:29:09 +0000 @@ -216,7 +216,7 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer PREPARE stmt FROM "EXPLAIN SELECT EMPNAME @@ -231,13 +231,13 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 -1 SIMPLE subselect2 eq_ref unique_key unique_key 3 func 1 +1 SIMPLE SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer DEALLOCATE PREPARE stmt; @@ -254,7 +254,7 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer PREPARE stmt FROM "EXPLAIN SELECT EMPNAME @@ -269,13 +269,13 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 -1 SIMPLE subselect2 eq_ref unique_key unique_key 3 func 1 +1 SIMPLE SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer DEALLOCATE PREPARE stmt; @@ -291,7 +291,7 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer PREPARE stmt FROM "EXPLAIN SELECT EMPNAME @@ -306,13 +306,13 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE subselect2 eq_ref unique_key unique_key 3 func 1 +1 SIMPLE SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer DEALLOCATE PREPARE stmt; === modified file 'mysql-test/r/subselect_mat.result' --- a/mysql-test/r/subselect_mat.result 2010-04-06 10:35:33 +0000 +++ b/mysql-test/r/subselect_mat.result 2010-04-06 21:29:09 +0000 @@ -42,7 +42,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 9 test.t1.a1 1 100.00 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 <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where (`materialized subselect`.`b1` = `test`.`t1`.`a1`) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`) select * from t1 where a1 in (select b1 from t2 where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -54,7 +54,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 9 test.t1.a1 1 100.00 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 <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where (`materialized subselect`.`b1` = `test`.`t1`.`a1`) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`) select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -66,7 +66,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 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 <materialize> (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`) join `test`.`t1` where ((`materialized subselect`.`b2` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (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`) join `test`.`t1` where ((`SUBQUERY#2`.`b2` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) 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 @@ -78,7 +78,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 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 <materialize> (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`) join `test`.`t1` where ((`materialized subselect`.`min(b2)` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (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`) join `test`.`t1` where ((`SUBQUERY#2`.`min(b2)` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) 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 @@ -90,7 +90,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 9 test.t1i.a1 1 100.00 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 <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where (`materialized subselect`.`b1` = `test`.`t1i`.`a1`) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where (`SUBQUERY#2`.`b1` = `test`.`t1i`.`a1`) select * from t1i where a1 in (select b1 from t2i where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -102,7 +102,7 @@ 1 PRIMARY t1i ref it1i1,it1i3 # 9 # 1 100.00 # 2 SUBQUERY t2i range it2i1,it2i3 # 9 # 3 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`test`.`t1i`.`a1` = `materialized subselect`.`b1`) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`test`.`t1i`.`a1` = `SUBQUERY#2`.`b1`) select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -114,7 +114,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1i.a1,test.t1i.a2 1 100.00 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 <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`materialized subselect`.`b2` = `test`.`t1i`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1i`.`a1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`SUBQUERY#2`.`b2` = `test`.`t1i`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1i`.`a1`)) select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -126,7 +126,7 @@ 1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 2 SUBQUERY t2i range it2i1,it2i3 # # # 3 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (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`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `materialized subselect`.`b2`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (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`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `SUBQUERY#2`.`b2`) and (`test`.`t1i`.`a1` = `SUBQUERY#2`.`b1`)) 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 @@ -138,7 +138,7 @@ 1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 2 SUBQUERY t2i range it2i1,it2i3 # # # 3 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (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`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `materialized subselect`.`min(b2)`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (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`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `SUBQUERY#2`.`min(b2)`) and (`test`.`t1i`.`a1` = `SUBQUERY#2`.`b1`)) 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 @@ -150,7 +150,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 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 <materialize> (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`materialized subselect`.`max(b2)` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`SUBQUERY#2`.`max(b2)` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); a1 a2 1 - 01 2 - 01 @@ -182,7 +182,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 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 <materialize> (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`) join `test`.`t1` where ((`materialized subselect`.`min(b2)` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (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`) join `test`.`t1` where ((`SUBQUERY#2`.`min(b2)` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) 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 @@ -223,7 +223,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 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 <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`materialized subselect`.`b2` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`SUBQUERY#2`.`b2` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); a1 a2 1 - 01 2 - 01 @@ -235,7 +235,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1i.a1,test.t1i.a2 1 100.00 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 <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`materialized subselect`.`b2` = `test`.`t1i`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1i`.`a1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`SUBQUERY#2`.`b2` = `test`.`t1i`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1i`.`a1`)) select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); a1 a2 1 - 01 2 - 01 @@ -286,14 +286,14 @@ where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 5 0.00 -1 PRIMARY SUBQUERY#3 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00 +1 PRIMARY SUBQUERY#3 eq_ref distinct_key distinct_key 18 SUBQUERY#2.b1,SUBQUERY#2.b2 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer 3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 3 SUBQUERY SUBQUERY#4 eq_ref distinct_key distinct_key 18 test.t3.c1,test.t3.c2 1 100.00 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 <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#4`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#4`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where ((`SUBQUERY#3`.`c2` = `SUBQUERY#2`.`b2`) and (`test`.`t1`.`a2` = `SUBQUERY#2`.`b2`) and (`SUBQUERY#3`.`c1` = `SUBQUERY#2`.`b1`) and (`test`.`t1`.`a1` = `SUBQUERY#2`.`b1`)) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and (a1, a2) in (select c1, c2 from t3 @@ -315,7 +315,7 @@ 4 SUBQUERY t2i index it2i2 # # # 5 100.00 # 2 SUBQUERY t2i index it2i1,it2i3 # # # 5 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`materialized subselect`.`b2` = `test`.`t3i`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`SUBQUERY#4`.`b2` = `test`.`t3i`.`c2`) and (`SUBQUERY#4`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`SUBQUERY#3`.`c2` = `SUBQUERY#2`.`b2`) and (`test`.`t1i`.`a2` = `SUBQUERY#2`.`b2`) and (`SUBQUERY#3`.`c1` = `SUBQUERY#2`.`b1`) and (`test`.`t1i`.`a1` = `SUBQUERY#2`.`b1`)) select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and (a1, a2) in (select c1, c2 from t3i @@ -332,7 +332,7 @@ where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 5 0.00 -1 PRIMARY SUBQUERY#5 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00 +1 PRIMARY SUBQUERY#5 eq_ref distinct_key distinct_key 18 SUBQUERY#2.b1,SUBQUERY#2.b2 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer 5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 5 SUBQUERY SUBQUERY#6 eq_ref distinct_key distinct_key 18 test.t3.c1,test.t3.c2 1 100.00 @@ -341,7 +341,7 @@ 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 <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>( `test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))))) join `test`.`t1` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#6`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#6`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#3`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table pk === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-04-05 20:16:45 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-04-06 21:29:09 +0000 @@ -1303,7 +1303,7 @@ 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer 2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`test`.`t2`.`a` = `materialized subselect`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`test`.`t2`.`a` = `SUBQUERY#2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1314,7 +1314,7 @@ 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer 2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`test`.`t2`.`a` = `materialized subselect`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`test`.`t2`.`a` = `SUBQUERY#2`.`a`) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1326,7 +1326,7 @@ 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 <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`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)); @@ -1349,7 +1349,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1360,7 +1360,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00 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 <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1372,7 +1372,7 @@ 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 <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`a`) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1389,7 +1389,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00 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 <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`a`) drop table t0, t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -2844,7 +2844,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 10 test.t1.one,test.t1.two 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N')) join `test`.`t1` where ((`materialized subselect`.`two` = `test`.`t1`.`two`) and (`materialized subselect`.`one` = `test`.`t1`.`one`)) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N')) join `test`.`t1` where ((`SUBQUERY#2`.`two` = `test`.`t1`.`two`) and (`SUBQUERY#2`.`one` = `test`.`t1`.`one`)) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 @@ -4343,14 +4343,14 @@ 1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`SUBQUERY#2`.`1` = 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 1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00 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 <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`SUBQUERY#2`.`1` = 1) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-04-06 21:29:09 +0000 @@ -848,7 +848,7 @@ EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); 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 -1 PRIMARY subselect2 eq_ref unique_key unique_key 13 func 1 1.00 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 13 func 1 1.00 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0)) @@ -1017,7 +1017,7 @@ WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 -1 PRIMARY subselect2 eq_ref unique_key unique_key 8 func 1 1.00 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 8 func 1 1.00 2 SUBQUERY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`)) === modified file 'mysql-test/r/subselect_sj2.result' --- a/mysql-test/r/subselect_sj2.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj2.result 2010-04-06 21:29:09 +0000 @@ -32,7 +32,7 @@ 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 3 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); @@ -74,7 +74,7 @@ from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; @@ -101,7 +101,7 @@ explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t2 index b b 5 NULL 10 Using index select * from t1; a b @@ -129,7 +129,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -163,7 +163,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -197,7 +197,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -231,7 +231,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -348,7 +348,7 @@ SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( @@ -692,7 +692,7 @@ explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) === modified file 'mysql-test/r/subselect_sj2_jcl6.result' --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-04-06 21:29:09 +0000 @@ -36,7 +36,7 @@ 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 3 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); @@ -78,7 +78,7 @@ from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer 2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; @@ -105,7 +105,7 @@ explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t2 index b b 5 NULL 10 Using index select * from t1; a b @@ -133,7 +133,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -167,7 +167,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -201,7 +201,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -235,7 +235,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -352,7 +352,7 @@ SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( @@ -698,7 +698,7 @@ explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-04-06 21:29:09 +0000 @@ -852,7 +852,7 @@ EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); 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 -1 PRIMARY subselect2 eq_ref unique_key unique_key 13 func 1 1.00 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 13 func 1 1.00 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0)) @@ -1021,7 +1021,7 @@ WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 -1 PRIMARY subselect2 eq_ref unique_key unique_key 8 func 1 1.00 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 8 func 1 1.00 2 SUBQUERY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`)) === modified file 'mysql-test/suite/pbxt/r/group_min_max.result' --- a/mysql-test/suite/pbxt/r/group_min_max.result 2010-04-06 08:10:58 +0000 +++ b/mysql-test/suite/pbxt/r/group_min_max.result 2010-04-06 21:29:09 +0000 @@ -2257,7 +2257,7 @@ a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 15 -1 PRIMARY t1_outer ref a a 5 materialized subselect.max(b) 1 Using index +1 PRIMARY t1_outer ref a a 5 SUBQUERY#2.max(b) 1 Using index 2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); === modified file 'mysql-test/suite/pbxt/r/subselect.result' --- a/mysql-test/suite/pbxt/r/subselect.result 2010-04-06 08:10:58 +0000 +++ b/mysql-test/suite/pbxt/r/subselect.result 2010-04-06 21:29:09 +0000 @@ -3433,7 +3433,7 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 9 -1 PRIMARY t1 ref a a 8 materialized subselect.a 1 Using where +1 PRIMARY t1 ref a a 8 SUBQUERY#2.a 1 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort DROP TABLE t1; create table t1( f1 int,f2 int); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-04-05 20:16:45 +0000 +++ b/sql/item_subselect.cc 2010-04-06 21:29:09 +0000 @@ -2116,7 +2116,8 @@ if (!(new_engine= new subselect_hash_sj_engine(thd, this, old_engine)) || - new_engine->init_permanent(unit->get_unit_column_types())) + new_engine->init_permanent(unit->get_unit_column_types(), + old_engine->get_identifier())) { Item_subselect::trans_res trans_res; /* @@ -3662,6 +3663,7 @@ reexecution. @param tmp_columns the items that produce the data for the temp table + @param subquery_id subquery's identifier (for temptable name) @details - Create a temporary table to store the result of the IN subquery. The @@ -3677,7 +3679,8 @@ @retval FALSE otherwise */ -bool subselect_hash_sj_engine::init_permanent(List<Item> *tmp_columns) +bool subselect_hash_sj_engine::init_permanent(List<Item> *tmp_columns, + uint subquery_id) { /* Options to create_tmp_table. */ ulonglong tmp_create_options= thd->options | TMP_TABLE_ALL_COLUMNS; @@ -3712,12 +3715,19 @@ DBUG_RETURN(TRUE); } */ + char buf[32]; + uint len= my_snprintf(buf, sizeof(buf), "SUBQUERY#%d", subquery_id); + char *name; + if (!(name= (char*)thd->alloc(len + 1))) + DBUG_RETURN(TRUE); + memcpy(name, buf, len+1); + if (!(result= new select_materialize_with_stats)) DBUG_RETURN(TRUE); if (((select_union*) result)->create_result_table( thd, tmp_columns, TRUE, tmp_create_options, - "materialized subselect", TRUE)) + name, TRUE)) DBUG_RETURN(TRUE); tmp_table= ((select_union*) result)->table; @@ -3798,7 +3808,7 @@ if (!(tmp_table_ref= (TABLE_LIST*) thd->alloc(sizeof(TABLE_LIST)))) DBUG_RETURN(TRUE); - tmp_table_ref->init_one_table("", "materialized subselect", TL_READ); + tmp_table_ref->init_one_table("", tmp_table->alias, TL_READ); tmp_table_ref->table= tmp_table; context= new Name_resolution_context; === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-04-05 20:16:45 +0000 +++ b/sql/item_subselect.h 2010-04-06 21:29:09 +0000 @@ -814,7 +814,7 @@ } ~subselect_hash_sj_engine(); - bool init_permanent(List<Item> *tmp_columns); + bool init_permanent(List<Item> *tmp_columns, uint subquery_id); bool init_runtime(); void cleanup(); int prepare() { return 0; } /* Override virtual function in base class. */ === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-04-05 20:16:45 +0000 +++ b/sql/sql_select.cc 2010-04-06 21:29:09 +0000 @@ -18004,7 +18004,7 @@ /* table */ int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1, - "subselect%d", + "SUBQUERY#%d", tab->emb_sj_nest->sj_subq_pred->get_identifier()); item_list.push_back(new Item_string(table_name_buffer, len, cs)); /* partitions */