summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect_sj2_mat.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/subselect_sj2_mat.result')
-rw-r--r--mysql-test/r/subselect_sj2_mat.result65
1 files changed, 45 insertions, 20 deletions
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 1b7fccf924c..0284bab0972 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -3,7 +3,10 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
set @subselect_sj2_tmp= @@optimizer_switch;
set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
-drop table if exists t0, t1, t2, t3;
+SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'outer_join_with_cache=off');
+SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'semijoin_with_cache=off');
+set join_cache_level=1;
+drop table if exists t0, t1, t2, t3, t4, t5;
drop view if exists v1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -50,7 +53,7 @@ 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 <subquery2> ALL distinct_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 Using where
+2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where
select * from t2 where b in (select a from t1);
a b
1 1
@@ -68,9 +71,9 @@ insert into t3 select a,a, a,a,a from t0;
insert into t3 select a,a, a+100,a+100,a+100 from t0;
explain select * from t3 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL b NULL NULL NULL 20
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3
+1 PRIMARY t3 ref b b 5 test.t1.a 1
+2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where
select * from t3 where b in (select a from t1);
a b pk1 pk2 pk3
1 1 1 1 1
@@ -96,7 +99,7 @@ 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 <subquery2> ALL distinct_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 Using where
+2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 Using where
select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
a b pk1 pk2
0 0 0 0
@@ -147,7 +150,7 @@ 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 <subquery2> ALL distinct_key NULL NULL NULL 22
1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join)
-2 SUBQUERY it ALL NULL NULL NULL NULL 22
+2 MATERIALIZED 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);
@@ -180,7 +183,7 @@ from t2 ot where a in (select a from t1 it);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ot ALL NULL NULL NULL NULL 22
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1
-2 SUBQUERY it ALL NULL NULL NULL NULL 32
+2 MATERIALIZED 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);
@@ -215,7 +218,7 @@ 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 <subquery2> ALL distinct_key NULL NULL NULL 22
1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join)
-2 SUBQUERY it ALL NULL NULL NULL NULL 22
+2 MATERIALIZED 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);
@@ -248,7 +251,7 @@ from t2 ot where a in (select a from t1 it);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ot ALL NULL NULL NULL NULL 22
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1
-2 SUBQUERY it ALL NULL NULL NULL NULL 52
+2 MATERIALIZED 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);
@@ -289,9 +292,9 @@ from t0 where a in
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 ALL NULL NULL NULL NULL 10
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 Using where
-2 SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
-2 SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
-2 SUBQUERY t3 ref a a 5 test.t1.a 1 Using index
+2 MATERIALIZED t1 index a a 5 NULL 10 Using where; Using index
+2 MATERIALIZED t2 ref a a 5 test.t1.a 1 Using index
+2 MATERIALIZED t3 ref a a 5 test.t1.a 1 Using index
drop table t0, t1,t2,t3;
CREATE TABLE t1 (
ID int(11) NOT NULL auto_increment,
@@ -330,8 +333,8 @@ WHERE Language='English' AND Percentage > 10 AND
t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Rowid-ordered scan; Start temporary
-1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; End temporary
+1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where
set optimizer_switch=@bug35674_save_optimizer_switch;
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
@@ -369,7 +372,7 @@ SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1
-2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where
+2 MATERIALIZED 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);
@@ -604,7 +607,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
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 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 10 Using index
+2 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 10 Using index
drop table t0, t1, t2, t3;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -718,7 +721,7 @@ explain select count(a) from t2 where a in ( SELECT a FROM t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index a a 5 NULL 1000 Using index
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1
-2 SUBQUERY t3 index a a 5 NULL 30000 Using index
+2 MATERIALIZED t3 index a a 5 NULL 30000 Using index
select count(a) from t2 where a in ( SELECT a FROM t3);
count(a)
1000
@@ -874,10 +877,10 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
WHERE t3.b IN (SELECT b FROM t4);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 1
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1
-2 SUBQUERY t4 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2
3 DERIVED t1 ALL NULL NULL NULL NULL 1
SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
WHERE t3.b IN (SELECT b FROM t4);
@@ -891,3 +894,25 @@ set optimizer_switch=default;
select @@optimizer_switch like '%materialization=on%';
@@optimizer_switch like '%materialization=on%'
1
+#
+# BUG#906385: EXPLAIN EXTENDED crashes in TABLE_LIST::print with limited max_join_size
+#
+CREATE TABLE t1 ( a INT );
+CREATE TABLE t2 ( b INT );
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES
+(1),(2),(3),(4),(5),
+(6),(7),(8),(9),(10),
+(11),(12),(13),(14),(15),
+(16),(17),(18),(19),(20);
+set @tmp_906385=@@max_join_size;
+SET max_join_size = 80;
+EXPLAIN EXTENDED
+SELECT COUNT(*) FROM t1
+WHERE a IN
+( SELECT b FROM t2 GROUP BY b )
+AND ( 6 ) IN
+( SELECT MIN( t2.b ) FROM t2 alias1, t2 );
+ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
+DROP TABLE t1, t2;
+set max_join_size= @tmp_906385;
View Lorry Controller Status