[Maria-developers] Rev 2792: MWL#90: Subqueries: Inside-out execution for non-semijoin materialized in file:///home/psergey/dev/maria-5.3-subqueries-r12/
At file:///home/psergey/dev/maria-5.3-subqueries-r12/ ------------------------------------------------------------ revno: 2792 revision-id: psergey@askmonty.org-20100406103533-0upq6g7sse9sjstz parent: psergey@askmonty.org-20100406091710-xamea3aolh3rdspu committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.3-subqueries-r12 timestamp: Tue 2010-04-06 14:35:33 +0400 message: MWL#90: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE - Make test results handle same-cost QEP races === modified file 'mysql-test/r/subselect_mat.result' --- a/mysql-test/r/subselect_mat.result 2010-04-05 20:16:45 +0000 +++ b/mysql-test/r/subselect_mat.result 2010-04-06 10:35:33 +0000 @@ -98,9 +98,9 @@ explain extended select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t1i ref it1i1,it1i3 it1i3 9 materialized subselect.b1 1 100.00 Using index -2 SUBQUERY t2i range it2i1,it2i3 it2i1 9 NULL 3 100.00 Using where; Using index for group-by +1 PRIMARY SUBQUERY#2 ALL distinct_key # NULL # 3 100.00 # +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`) select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); @@ -122,9 +122,9 @@ explain extended select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t1i ref it1i1,it1i2,it1i3 it1i3 18 materialized subselect.b1,materialized subselect.b2 1 100.00 Using index -2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by +1 PRIMARY SUBQUERY#2 ALL distinct_key # # # 3 100.00 # +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`)) select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); @@ -134,9 +134,9 @@ explain extended select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t1i ref it1i1,it1i2,it1i3 it1i3 18 materialized subselect.b1,materialized subselect.min(b2) 1 100.00 Using index -2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by +1 PRIMARY SUBQUERY#2 ALL distinct_key # # # 3 100.00 # +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`)) select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); @@ -307,13 +307,13 @@ (a1, a2) in (select c1, c2 from t3i 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 t1i ref it1i1,it1i2,it1i3 it1i3 18 materialized subselect.b1,materialized subselect.c2 1 100.00 Using where; Using index -3 SUBQUERY t3i index it3i1,it3i2,it3i3 it3i3 18 NULL 4 100.00 Using index -3 SUBQUERY SUBQUERY#4 eq_ref distinct_key distinct_key 18 test.t3i.c1,test.t3i.c2 1 100.00 -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 +1 PRIMARY SUBQUERY#2 ALL distinct_key # # # 5 0.00 # +1 PRIMARY SUBQUERY#3 eq_ref distinct_key # # # 1 100.00 # +1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # +3 SUBQUERY t3i index it3i1,it3i2,it3i3 # # # 4 100.00 # +3 SUBQUERY SUBQUERY#4 eq_ref distinct_key # # # 1 100.00 # +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`)) select * from t1i @@ -392,23 +392,23 @@ (a1, a2) in (select c1, c2 from t3i 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 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 -6 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; Using temporary; Using filesort -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 -7 UNION SUBQUERY#8 ALL distinct_key NULL NULL NULL 5 0.00 -7 UNION SUBQUERY#9 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00 -7 UNION t1i ref it1i1,it1i2,it1i3 it1i3 18 materialized subselect.b1,materialized subselect.c2 1 100.00 Using where; Using index -9 SUBQUERY t3i index it3i1,it3i2,it3i3 it3i3 18 NULL 4 100.00 Using index -9 SUBQUERY SUBQUERY#10 eq_ref distinct_key distinct_key 18 test.t3i.c1,test.t3i.c2 1 100.00 -10 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index -8 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index -NULL UNION RESULT <union1,7> ALL NULL NULL NULL NULL NULL NULL +1 PRIMARY SUBQUERY#2 ALL distinct_key # # # 5 0.00 # +1 PRIMARY SUBQUERY#5 eq_ref distinct_key # # # 1 100.00 # +1 PRIMARY t1 ALL NULL # # # 3 100.00 # +5 SUBQUERY t3 ALL NULL # # # 4 100.00 # +5 SUBQUERY SUBQUERY#6 eq_ref distinct_key # # # 1 100.00 # +6 SUBQUERY t2i index it2i2 # # # 5 100.00 # +2 SUBQUERY t2 ALL NULL # # # 5 100.00 # +4 SUBQUERY t3 ALL NULL # # # 4 100.00 # +3 SUBQUERY t3 ALL NULL # # # 4 100.00 # +7 UNION SUBQUERY#8 ALL distinct_key # # # 5 0.00 # +7 UNION SUBQUERY#9 eq_ref distinct_key # # # 1 100.00 # +7 UNION t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # +9 SUBQUERY t3i index it3i1,it3i2,it3i3 # # # 4 100.00 # +9 SUBQUERY SUBQUERY#10 eq_ref distinct_key # # # 1 100.00 # +10 SUBQUERY t2i index it2i2 # # # 5 100.00 # +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`))) (select * from t1 === modified file 'mysql-test/t/subselect_mat.test' --- a/mysql-test/t/subselect_mat.test 2010-04-05 20:16:45 +0000 +++ b/mysql-test/t/subselect_mat.test 2010-04-06 10:35:33 +0000 @@ -75,18 +75,21 @@ select * from t1i where a1 in (select b1 from t2i where b1 > '0'); select * from t1i where a1 in (select b1 from t2i where b1 > '0'); -explain extended -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); - -explain extended -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); - -explain extended -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); - +--replace_column 6 # 8 # 11 # +explain extended +select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); +select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); + +explain extended +select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); +select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); + +--replace_column 6 # 7 # 8 # 11 # +explain extended +select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); +select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); + +--replace_column 6 # 7 # 8 # 11 # explain extended select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); @@ -174,6 +177,7 @@ (a1, a2) in (select c1, c2 from t3 where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); +--replace_column 6 # 7 # 8 # 11 # explain extended select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and @@ -215,6 +219,7 @@ # multiple levels of nesting subqueries, unions +--replace_column 6 # 7 # 8 # 11 # explain extended (select * from t1 where (a1, a2) in (select b1, b2 from t2
participants (1)
-
Sergey Petrunya