diff options
Diffstat (limited to 'mysql-test/main/subselect_sj_nonmerged.result')
| -rw-r--r-- | mysql-test/main/subselect_sj_nonmerged.result | 122 |
1 files changed, 122 insertions, 0 deletions
diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result new file mode 100644 index 00000000000..c7e04225ffe --- /dev/null +++ b/mysql-test/main/subselect_sj_nonmerged.result @@ -0,0 +1,122 @@ +drop table if exists t0, t1, t2, t3, t4; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=on,materialization=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 as select * from t0; +# The following should use full scan on <subquery2> and it must scan 1 row: +explain select * from t0 where a in (select max(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 1 +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 +select * from t0 where a in (select max(a) from t1); +a +9 +insert into t1 values (11); +select * from t0 where a in (select max(a) from t1); +a +delete from t1 where a=11; +insert into t0 values (NULL); +select * from t0 where a in (select max(a) from t1); +a +9 +delete from t0 where a is NULL; +delete from t1; +select * from t0 where a in (select max(a) from t1); +a +insert into t0 values (NULL); +select * from t0 where a in (select max(a) from t1); +a +delete from t0 where a is NULL; +drop table t1; +create table t1 (a int, b int); +insert into t1 select a,a from t0; +create table t2 as select * from t1 where a<5; +create table t3 as select (A.a + 10*B.a) as a from t0 A, t0 B; +alter table t3 add primary key(a); +# The following should have do a full scan on <subquery2> and scan 5 rows +# (despite that subquery's join output estimate is 50 rows) +explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 8 <subquery2>.max(t2.a) 1 Using where; Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +# Compare to this which really will have 50 record combinations: +explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b, t1.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 50 +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 8 <subquery2>.max(t2.a) 1 Using where; Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; +# Outer joins also work: +explain select * from t3 +where a in (select max(t2.a) from t1 left join t2 on t1.a=t2.a group by t2.b, t1.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 50 +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 8 <subquery2>.max(t2.a) 1 Using where; Using index +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using temporary +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using where +SET optimizer_switch=@save_optimizer_switch; +create table t4 (a int, b int, filler char(20), unique key(a,b)); +insert into t4 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t0 A, t0 B; +explain select * from t0, t4 where +t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 +1 PRIMARY t4 ALL a NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t4.a 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B; +explain select * from t4 where +t4.a in (select max(t2.a) from t1, t2 group by t2.b) and +t4.b in (select max(t2.a) from t1, t2 group by t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) +1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 12 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +drop table t1,t2,t3,t4; +drop table t0; +# +# BUG#780359: Crash with get_fanout_with_deps in maria-5.3-mwl90 +# +CREATE TABLE t1 (f1 int); +INSERT INTO t1 VALUES (2),(2); +CREATE TABLE t2 (f3 int); +INSERT INTO t2 VALUES (2),(2); +SELECT * +FROM t1 +WHERE ( f1 ) IN ( +SELECT t2.f3 +FROM t2 +WHERE t2.f3 = 97 +AND t2.f3 = 50 +GROUP BY 1 +); +f1 +DROP TABLE t1, t2; +# +# BUG#727183: WL#90 does not trigger with non-comma joins +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int, key(a)); +insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; +# The following must use non-merged SJ-Materialization: +explain select * from t1 X join t0 Y on X.a < Y.a where X.a in (select max(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 1 +1 PRIMARY X ref a a 5 <subquery2>.max(a) 1 Using index +1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 +drop table t0, t1; +set optimizer_switch=@save_optimizer_switch; |