maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #02771
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@xxxxxxxxxxxx-20100329140435-bb21mofh3i85tt4q
parent: psergey@xxxxxxxxxxxx-20100323145750-sr9oucry979i3p60
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
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))
