← Back to team overview

maria-developers team mailing list archive

  1. maria-developers team
  2. Mailing list archive
  3. Message #02873

Rev 2792: MWL#90: Subqueries: Inside-out execution for non-semijoin materialized in file:///home/psergey/dev/maria-5.3-subqueries-r12/

  Thread Previous • Date Previous • • Thread Next 
At file:///home/psergey/dev/maria-5.3-subqueries-r12/

------------------------------------------------------------
revno: 2792
revision-id: psergey@xxxxxxxxxxxx-20100406103533-0upq6g7sse9sjstz
parent: psergey@xxxxxxxxxxxx-20100406091710-xamea3aolh3rdspu
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
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
  Thread Previous • Date Previous • • Thread Next