[Maria-developers] Rev 2786: MWL#110: Make EXPLAIN always show materialization separately in file:///home/psergey/dev/maria-5.3-subqueries-r10/
At file:///home/psergey/dev/maria-5.3-subqueries-r10/ ------------------------------------------------------------ revno: 2786 revision-id: psergey@askmonty.org-20100329140435-bb21mofh3i85tt4q parent: psergey@askmonty.org-20100323145750-sr9oucry979i3p60 committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.3-subqueries-r10 timestamp: Mon 2010-03-29 18:04:35 +0400 message: MWL#110: Make EXPLAIN always show materialization separately - Add Item_in_subselect::get_identifier() that returns subquery's id - Change select_describe() to produce output in new format - Update test results (checked) === modified file 'mysql-test/r/subselect3.result' --- a/mysql-test/r/subselect3.result 2010-03-20 12:01:47 +0000 +++ b/mysql-test/r/subselect3.result 2010-03-29 14:04:35 +0000 @@ -1017,10 +1017,11 @@ 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 t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan -1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 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 +2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer 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; a b c @@ -1034,7 +1035,8 @@ 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 Z ALL NULL NULL NULL NULL 2 Materialize +2 DEPENDENT SUBQUERY subselect3 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 NULL @@ -1156,8 +1158,9 @@ 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 t2 ALL NULL NULL NULL NULL 2 Using where; Materialize; Scan +1 PRIMARY subselect2 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); a filler 1 filler @@ -1204,8 +1207,9 @@ 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 t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where @@ -1233,14 +1237,16 @@ 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 t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 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 t4 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 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; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -1264,13 +1270,15 @@ 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 t2 ALL NULL NULL NULL NULL 100 Materialize +1 PRIMARY subselect2 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 t2 ALL NULL NULL NULL NULL 100 Materialize +1 PRIMARY subselect2 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; drop table t0, t1, t2; @@ -1308,9 +1316,10 @@ 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 X ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using join buffer -1 PRIMARY Z ALL NULL NULL NULL NULL 6 End materialize; Using join buffer +1 PRIMARY subselect2 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 drop table t0,t1,t2; BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 @@ -1380,9 +1389,10 @@ WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start materialize; Scan -1 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 End materialize +1 PRIMARY subselect2 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 Warnings: Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2')) drop table t1,t2,t3; === modified file 'mysql-test/r/subselect3_jcl6.result' --- a/mysql-test/r/subselect3_jcl6.result 2010-03-11 21:43:31 +0000 +++ b/mysql-test/r/subselect3_jcl6.result 2010-03-29 14:04:35 +0000 @@ -1021,10 +1021,11 @@ 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 t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan -1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 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 +2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer 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; a b c @@ -1039,7 +1040,8 @@ 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 Z ALL NULL NULL NULL NULL 2 Materialize +2 DEPENDENT SUBQUERY subselect3 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 NULL @@ -1161,8 +1163,9 @@ 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 t2 ALL NULL NULL NULL NULL 2 Using where; Materialize; Scan +1 PRIMARY subselect2 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); a filler 1 filler @@ -1209,8 +1212,9 @@ 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 t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where @@ -1238,14 +1242,16 @@ 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 t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 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 t4 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 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; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -1269,13 +1275,15 @@ 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 t2 ALL NULL NULL NULL NULL 100 Materialize +1 PRIMARY subselect2 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 t2 ALL NULL NULL NULL NULL 100 Materialize +1 PRIMARY subselect2 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; drop table t0, t1, t2; @@ -1313,9 +1321,10 @@ 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 X ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using join buffer -1 PRIMARY Z ALL NULL NULL NULL NULL 6 End materialize; Using join buffer +1 PRIMARY subselect2 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 drop table t0,t1,t2; BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 @@ -1385,9 +1394,10 @@ WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start materialize; Scan -1 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 End materialize; Using join buffer +1 PRIMARY subselect2 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 Warnings: Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2')) drop table t1,t2,t3; === modified file 'mysql-test/r/subselect4.result' --- a/mysql-test/r/subselect4.result 2010-03-20 12:01:47 +0000 +++ b/mysql-test/r/subselect4.result 2010-03-29 14:04:35 +0000 @@ -216,8 +216,9 @@ 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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 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 FROM t1 WHERE EMPNUM IN @@ -230,13 +231,15 @@ 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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 SIMPLE subselect2 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; DROP INDEX t1_IDX ON t1; CREATE INDEX t1_IDX ON t1(EMPNUM); @@ -251,8 +254,9 @@ 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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 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 FROM t1 WHERE EMPNUM IN @@ -265,13 +269,15 @@ 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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 SIMPLE subselect2 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; DROP INDEX t1_IDX ON t1; EXPLAIN SELECT EMPNAME @@ -285,8 +291,9 @@ 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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 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 FROM t1 WHERE EMPNUM IN @@ -299,13 +306,15 @@ 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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 SIMPLE subselect2 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; SET SESSION optimizer_switch = @old_optimizer_switch; SET SESSION join_cache_level = @old_join_cache_level; === modified file 'mysql-test/r/subselect_mat.result' --- a/mysql-test/r/subselect_mat.result 2010-03-13 21:11:06 +0000 +++ b/mysql-test/r/subselect_mat.result 2010-03-29 14:04:35 +0000 @@ -1190,8 +1190,9 @@ 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 t2 ALL NULL NULL NULL NULL 2 Materialize; Scan +1 PRIMARY subselect2 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); COUNT(*) 2 @@ -1211,7 +1212,8 @@ 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 t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR; Materialize +1 PRIMARY subselect2 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); pk 2 === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-03-15 19:52:58 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-03-29 14:04:35 +0000 @@ -848,7 +848,8 @@ 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 t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR; Materialize +1 PRIMARY subselect2 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)) SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); @@ -1016,7 +1017,8 @@ 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 t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where; Materialize +1 PRIMARY subselect2 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`)) SELECT varchar_nokey === modified file 'mysql-test/r/subselect_sj2.result' --- a/mysql-test/r/subselect_sj2.result 2010-03-07 15:41:45 +0000 +++ b/mysql-test/r/subselect_sj2.result 2010-03-29 14:04:35 +0000 @@ -32,8 +32,9 @@ 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 t1 ALL NULL NULL NULL NULL 3 Materialize; Scan +1 PRIMARY subselect2 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); a b 1 1 @@ -73,8 +74,9 @@ 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 t0 ALL NULL NULL NULL NULL 10 Materialize; Scan +1 PRIMARY subselect2 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; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); @@ -99,7 +101,8 @@ 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 t2 index b b 5 NULL 10 Using index; Materialize +1 PRIMARY subselect2 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 1 1 @@ -126,8 +129,9 @@ 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 it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY subselect2 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 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -159,7 +163,8 @@ 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 it ALL NULL NULL NULL NULL 32 Materialize +1 PRIMARY subselect2 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) from t2 ot where a in (select a from t1 it); @@ -192,8 +197,9 @@ 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 it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY subselect2 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 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -225,7 +231,8 @@ 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 it ALL NULL NULL NULL NULL 52 Materialize +1 PRIMARY subselect2 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) from t2 ot where a in (select a from t1 it); @@ -341,7 +348,8 @@ 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 t2 ALL CountryCode NULL NULL NULL 545 Using where; Materialize +1 PRIMARY subselect2 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 ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); @@ -684,7 +692,8 @@ 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 t3 index a a 5 NULL 30000 Using index; Materialize +1 PRIMARY subselect2 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) 1000 === modified file 'mysql-test/r/subselect_sj2_jcl6.result' --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-03-07 15:41:45 +0000 +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-03-29 14:04:35 +0000 @@ -36,8 +36,9 @@ 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 t1 ALL NULL NULL NULL NULL 3 Materialize; Scan +1 PRIMARY subselect2 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); a b 1 1 @@ -77,8 +78,9 @@ 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 t0 ALL NULL NULL NULL NULL 10 Materialize; Scan +1 PRIMARY subselect2 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; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); @@ -103,7 +105,8 @@ 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 t2 index b b 5 NULL 10 Using index; Materialize +1 PRIMARY subselect2 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 1 1 @@ -130,8 +133,9 @@ 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 it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY subselect2 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 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -163,7 +167,8 @@ 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 it ALL NULL NULL NULL NULL 32 Materialize +1 PRIMARY subselect2 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) from t2 ot where a in (select a from t1 it); @@ -196,8 +201,9 @@ 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 it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY subselect2 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 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -229,7 +235,8 @@ 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 it ALL NULL NULL NULL NULL 52 Materialize +1 PRIMARY subselect2 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) from t2 ot where a in (select a from t1 it); @@ -345,7 +352,8 @@ 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 t2 ALL CountryCode NULL NULL NULL 545 Using where; Materialize +1 PRIMARY subselect2 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 ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); @@ -690,7 +698,8 @@ 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 t3 index a a 5 NULL 30000 Using index; Materialize +1 PRIMARY subselect2 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) 1000 === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-03-15 19:52:58 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-29 14:04:35 +0000 @@ -852,7 +852,8 @@ 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 t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR; Materialize +1 PRIMARY subselect2 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)) SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); @@ -1020,7 +1021,8 @@ 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 t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where; Materialize +1 PRIMARY subselect2 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`)) SELECT varchar_nokey === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item_subselect.cc 2010-03-29 14:04:35 +0000 @@ -926,6 +926,11 @@ DBUG_VOID_RETURN; } +int Item_in_subselect::get_identifier() +{ + return engine->get_identifier(); +} + Item_allany_subselect::Item_allany_subselect(Item * left_exp, chooser_compare_func_creator fc, st_select_lex *select_lex, @@ -2271,6 +2276,10 @@ select_lex->master_unit()->item= item_arg; } +int subselect_single_select_engine::get_identifier() +{ + return select_lex->select_number; +} void subselect_single_select_engine::cleanup() { === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-03-20 12:01:47 +0000 +++ b/sql/item_subselect.h 2010-03-29 14:04:35 +0000 @@ -433,7 +433,12 @@ /* Inform 'this' that it was computed, and contains a valid result. */ void set_first_execution() { if (first_execution) first_execution= FALSE; } bool is_expensive_processor(uchar *arg); - + + /* + Return the identifier that we could use to identify the subquery for the + user. + */ + int get_identifier(); friend class Item_ref_null_helper; friend class Item_is_not_null_test; friend class Item_in_optimizer; @@ -534,7 +539,7 @@ /* Check if subquery produced any rows during last query execution */ virtual bool no_rows() = 0; virtual enum_engine_type engine_type() { return ABSTRACT_ENGINE; } - + virtual int get_identifier() { DBUG_ASSERT(0); return 0; } protected: void set_row(List<Item> &item_list, Item_cache **row); }; @@ -566,6 +571,7 @@ bool is_executed() const { return executed; } bool no_rows(); virtual enum_engine_type engine_type() { return SINGLE_SELECT_ENGINE; } + int get_identifier(); friend class subselect_hash_sj_engine; friend class Item_in_subselect; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-03-20 16:59:30 +0000 +++ b/sql/sql_select.cc 2010-03-29 14:04:35 +0000 @@ -17889,8 +17889,15 @@ else { table_map used_tables=0; - uint last_sjm_table= MAX_TABLES; - for (uint i=0 ; i < join->tables ; i++) + + uchar sjm_nests[MAX_TABLES]; + uint sjm_nests_cur=0; + uint sjm_nests_end= 0; + uint end_table= join->tables; + bool printing_materialize_nest= FALSE; + uint select_id= join->select_lex->select_number; + + for (uint i=0 ; i < end_table ; i++) { JOIN_TAB *tab=join->join_tab+i; TABLE *table=tab->table; @@ -17898,6 +17905,7 @@ char buff[512]; char buff1[512], buff2[512], buff3[512]; char keylen_str_buf[64]; + my_bool key_read; String extra(buff, sizeof(buff),cs); char table_name_buffer[NAME_LEN]; String tmp1(buff1,sizeof(buff1),cs); @@ -17907,7 +17915,6 @@ tmp1.length(0); tmp2.length(0); tmp3.length(0); - quick_type= -1; /* Don't show eliminated tables */ @@ -17919,12 +17926,89 @@ item_list.empty(); /* id */ - item_list.push_back(new Item_uint((uint32) - join->select_lex->select_number)); + item_list.push_back(new Item_uint((uint32)select_id)); /* select_type */ - item_list.push_back(new Item_string(join->select_lex->type, - strlen(join->select_lex->type), - cs)); + const char* stype= printing_materialize_nest? "SUBQUERY" : + join->select_lex->type; + item_list.push_back(new Item_string(stype, strlen(stype), cs)); + + /* + Special processing for SJ-Materialization nests: print the fake table + and delay printing of the SJM nest contents until later. + */ + uint sj_strategy= join->best_positions[i].sj_strategy; + if (sj_is_materialize_strategy(sj_strategy) && + !printing_materialize_nest) + { + /* table */ + int len= my_snprintf(table_name_buffer, + sizeof(table_name_buffer)-1, + "subselect%d", + tab->emb_sj_nest->sj_subq_pred->get_identifier()); + item_list.push_back(new Item_string(table_name_buffer, len, cs)); + /* partitions */ + if (join->thd->lex->describe & DESCRIBE_PARTITIONS) + item_list.push_back(item_null); + /* type */ + uint type= (sj_strategy == SJ_OPT_MATERIALIZE_SCAN)? JT_ALL : JT_EQ_REF; + item_list.push_back(new Item_string(join_type_str[type], + strlen(join_type_str[type]), + cs)); + /* possible_keys */ + item_list.push_back(new Item_string("unique_key", + strlen("unique_key"), cs)); + if (sj_strategy == SJ_OPT_MATERIALIZE_SCAN) + { + item_list.push_back(item_null); /* key */ + item_list.push_back(item_null); /* key_len */ + item_list.push_back(item_null); /* ref */ + } + else + { + /* key */ + item_list.push_back(new Item_string("unique_key", strlen("unique_key"), cs)); + /* key_len */ + uint klen= tab->emb_sj_nest->sj_mat_info->table->key_info[0].key_length; + uint buflen= longlong2str(klen, keylen_str_buf, 10) - keylen_str_buf; + item_list.push_back(new Item_string(keylen_str_buf, buflen, cs)); + /* ref */ + item_list.push_back(new Item_string("func", strlen("func"), cs)); + } + /* rows */ + ha_rows rows= (sj_strategy == SJ_OPT_MATERIALIZE_SCAN)? + tab->emb_sj_nest->sj_mat_info->rows : 1; + item_list.push_back(new Item_int(rows)); + /* filtered */ + if (join->thd->lex->describe & DESCRIBE_EXTENDED) + item_list.push_back(new Item_float(1.0, 2)); + + /* Extra */ + if (need_tmp_table) + { + need_tmp_table=0; + extra.append(STRING_WITH_LEN("; Using temporary")); + } + if (need_order) + { + need_order=0; + extra.append(STRING_WITH_LEN("; Using filesort")); + } + /* Skip initial "; "*/ + const char *str= extra.ptr(); + uint32 extra_len= extra.length(); + if (extra_len) + { + str += 2; + extra_len -= 2; + } + item_list.push_back(new Item_string(str, extra_len, cs)); + + /* Register the nest for further processing: */ + sjm_nests[sjm_nests_end++]= i; + i += join->best_positions[i].n_sj_tables-1; + goto loop_end; + } + if (tab->type == JT_ALL && tab->select && tab->select->quick) { quick_type= tab->select->quick->get_type(); @@ -17935,6 +18019,7 @@ else tab->type = JT_RANGE; } + /* table */ if (table->derived_select_number) { @@ -18113,7 +18198,7 @@ } /* Build "Extra" field and add it to item_list. */ - my_bool key_read=table->key_read; + key_read=table->key_read; if ((tab->type == JT_NEXT || tab->type == JT_CONST) && table->covering_keys.is_set(tab->index)) key_read=1; @@ -18269,7 +18354,8 @@ extra.append(STRING_WITH_LEN(")")); } } - uint sj_strategy= join->best_positions[i].sj_strategy; + + /* if (sj_is_materialize_strategy(sj_strategy)) { if (join->best_positions[i].n_sj_tables == 1) @@ -18286,6 +18372,7 @@ { extra.append(STRING_WITH_LEN("; End materialize")); } + */ for (uint part= 0; part < tab->ref.key_parts; part++) { @@ -18309,6 +18396,15 @@ } item_list.push_back(new Item_string(str, len, cs)); } + loop_end: + if (i+1 == end_table && sjm_nests_cur != sjm_nests_end) + { + printing_materialize_nest= TRUE; + i= sjm_nests[sjm_nests_cur++] - 1; + end_table= (i+1) + join->best_positions[i+1].n_sj_tables; + select_id= join->join_tab[i+1].emb_sj_nest->sj_subq_pred->get_identifier(); + } + // For next iteration used_tables|=table->map; if (result->send_data(item_list))
participants (1)
-
Sergey Petrunya