mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
122 lines
5.6 KiB
Text
122 lines
5.6 KiB
Text
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 t3 index PRIMARY PRIMARY 8 NULL 100 Using index
|
|
1 PRIMARY <subquery2> 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 <subquery2> 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 <subquery2> 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 <subquery2>.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 <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 eq_ref a a 10 <subquery2>.max(t2.a),<subquery3>.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 <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;
|