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 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 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 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 ALL distinct_key NULL NULL NULL 5 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 8 .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 t3 index PRIMARY PRIMARY 8 NULL 100 Using index 1 PRIMARY eq_ref distinct_key distinct_key 4 test.t3.a 1 Using where 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 t3 index PRIMARY PRIMARY 8 NULL 100 Using index 1 PRIMARY eq_ref distinct_key distinct_key 4 test.t3.a 1 Using where 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 ALL distinct_key NULL NULL NULL 5 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t4 eq_ref a a 10 .max(t2.a),test.t0.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 ALL distinct_key NULL NULL NULL 5 1 PRIMARY ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) 1 PRIMARY t4 eq_ref a a 10 .max(t2.a),.max(t2.a) 1 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 ALL distinct_key NULL NULL NULL 1 1 PRIMARY X ref a a 5 .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;