1drop table if exists t0, t1, t2, t3, t4; 2set @save_optimizer_switch=@@optimizer_switch; 3set optimizer_switch='semijoin=on,materialization=on'; 4set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 5create table t0 (a int); 6insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 7create table t1 as select * from t0; 8# The following should use full scan on <subquery2> and it must scan 1 row: 9explain select * from t0 where a in (select max(a) from t1); 10id select_type table type possible_keys key key_len ref rows Extra 111 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 121 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) 132 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 14select * from t0 where a in (select max(a) from t1); 15a 169 17insert into t1 values (11); 18select * from t0 where a in (select max(a) from t1); 19a 20delete from t1 where a=11; 21insert into t0 values (NULL); 22select * from t0 where a in (select max(a) from t1); 23a 249 25delete from t0 where a is NULL; 26delete from t1; 27select * from t0 where a in (select max(a) from t1); 28a 29insert into t0 values (NULL); 30select * from t0 where a in (select max(a) from t1); 31a 32delete from t0 where a is NULL; 33drop table t1; 34create table t1 (a int, b int); 35insert into t1 select a,a from t0; 36create table t2 as select * from t1 where a<5; 37create table t3 as select (A.a + 10*B.a) as a from t0 A, t0 B; 38alter table t3 add primary key(a); 39# The following should have do a full scan on <subquery2> and scan 5 rows 40# (despite that subquery's join output estimate is 50 rows) 41explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b); 42id select_type table type possible_keys key key_len ref rows Extra 431 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 441 PRIMARY t3 eq_ref PRIMARY PRIMARY 8 <subquery2>.max(t2.a) 1 Using where; Using index 452 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 462 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 47# Compare to this which really will have 50 record combinations: 48explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b, t1.b); 49id select_type table type possible_keys key key_len ref rows Extra 501 PRIMARY t3 index PRIMARY PRIMARY 8 NULL 100 Using index 511 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t3.a 1 Using where 522 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 532 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 54SET @save_optimizer_switch=@@optimizer_switch; 55SET optimizer_switch='outer_join_with_cache=off'; 56# Outer joins also work: 57explain select * from t3 58where a in (select max(t2.a) from t1 left join t2 on t1.a=t2.a group by t2.b, t1.b); 59id select_type table type possible_keys key key_len ref rows Extra 601 PRIMARY t3 index PRIMARY PRIMARY 8 NULL 100 Using index 611 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t3.a 1 Using where 622 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using temporary 632 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using where 64SET optimizer_switch=@save_optimizer_switch; 65create table t4 (a int, b int, filler char(20), unique key(a,b)); 66insert into t4 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t0 A, t0 B; 67explain select * from t0, t4 where 68t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b); 69id select_type table type possible_keys key key_len ref rows Extra 701 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 711 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) 721 PRIMARY t4 eq_ref a a 10 <subquery2>.max(t2.a),test.t0.a 1 732 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 742 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 75insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B; 76explain select * from t4 where 77t4.a in (select max(t2.a) from t1, t2 group by t2.b) and 78t4.b in (select max(t2.a) from t1, t2 group by t2.b); 79id select_type table type possible_keys key key_len ref rows Extra 801 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 811 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) 821 PRIMARY t4 eq_ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 1 833 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 843 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 852 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 862 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 87drop table t1,t2,t3,t4; 88drop table t0; 89# 90# BUG#780359: Crash with get_fanout_with_deps in maria-5.3-mwl90 91# 92CREATE TABLE t1 (f1 int); 93INSERT INTO t1 VALUES (2),(2); 94CREATE TABLE t2 (f3 int); 95INSERT INTO t2 VALUES (2),(2); 96SELECT * 97FROM t1 98WHERE ( f1 ) IN ( 99SELECT t2.f3 100FROM t2 101WHERE t2.f3 = 97 102AND t2.f3 = 50 103GROUP BY 1 104); 105f1 106DROP TABLE t1, t2; 107# 108# BUG#727183: WL#90 does not trigger with non-comma joins 109# 110create table t0 (a int); 111insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 112create table t1(a int, key(a)); 113insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; 114# The following must use non-merged SJ-Materialization: 115explain select * from t1 X join t0 Y on X.a < Y.a where X.a in (select max(a) from t0); 116id select_type table type possible_keys key key_len ref rows Extra 1171 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 1181 PRIMARY X ref a a 5 <subquery2>.max(a) 1 Using index 1191 PRIMARY Y ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) 1202 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 121drop table t0, t1; 122set optimizer_switch=@save_optimizer_switch; 123