summaryrefslogtreecommitdiff
path: root/mysql-test/main/subselect_sj_nonmerged.result
blob: 422af02c31a7123352dbae2fa38c72506c0bf1e4 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
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	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;