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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
|
set optimizer_switch='batched_key_access=on,block_nested_loop=off,mrr_cost_based=off';
set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off';
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(12),(14),(16),(18);
create table t1 (
a int,
b int
) engine=innodb;
insert into t1 values (1,1),(1,1),(2,2);
create table t2 (
a int,
b int,
key(b)
) engine=innodb;
insert into t2 select a, a/2 from t0;
select * from t1;
a b
1 1
1 1
2 2
select * from t2;
a b
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4
9 5
10 5
12 6
14 7
16 8
18 9
explain select * from t2 where b in (select a from t1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 Using where
1 SIMPLE t2 NULL ref b b 5 <subquery2>.a 1 100.00 Using join buffer (Batched Key Access)
2 MATERIALIZED t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t2`.`b` = `<subquery2>`.`a`)
select * from t2 where b in (select a from t1);
a b
1 1
2 1
3 2
4 2
truncate table t0;
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200), pk3 char(200),
primary key(pk1, pk2, pk3)
) engine=innodb;
insert into t3 select a,a, a,a,a from t0;
explain select * from t3 where b in (select a from t1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 Using where
1 SIMPLE t3 NULL ref b b 5 <subquery2>.a 1 100.00 Using join buffer (Batched Key Access)
2 MATERIALIZED t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`pk1` AS `pk1`,`test`.`t3`.`pk2` AS `pk2`,`test`.`t3`.`pk3` AS `pk3` from `test`.`t3` semi join (`test`.`t1`) where (`test`.`t3`.`b` = `<subquery2>`.`a`)
select * from t3 where b in (select a from t1);
a b pk1 pk2 pk3
1 1 1 1 1
2 2 2 2 2
set @save_max_heap_table_size= @@max_heap_table_size;
set max_heap_table_size=16384;
set @save_join_buffer_size = @@join_buffer_size;
set join_buffer_size= 8192;
drop table t3;
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200),
primary key(pk1, pk2)
) engine=innodb;
insert into t3 select
A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
from t0 A, t0 B where B.a <5;
explain select * from t3 where b in (select a from t0);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL # 100.00 Using where
1 SIMPLE t3 NULL ref b b 5 <subquery2>.a # 100.00 Using join buffer (Batched Key Access)
2 MATERIALIZED t0 NULL ALL NULL NULL NULL NULL # 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`pk1` AS `pk1`,`test`.`t3`.`pk2` AS `pk2` from `test`.`t3` semi join (`test`.`t0`) where (`test`.`t3`.`b` = `<subquery2>`.`a`)
select * from t3 where b in (select a.a+b.a from t0 a, t0 b where b.a<5);
a b pk1 pk2
0 0 0 0
1 1 1 1
10 10 10 10
11 11 11 11
12 12 12 12
13 13 13 13
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
set join_buffer_size= @save_join_buffer_size;
set max_heap_table_size= @save_max_heap_table_size;
explain select * from t1 where a in (select b from t2);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
1 SIMPLE t2 NULL ref b b 5 test.t1.a 1 100.00 Using index; FirstMatch(t1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`b` = `test`.`t1`.`a`)
select * from t1;
a b
1 1
1 1
2 2
select * from t1 where a in (select b from t2);
a b
1 1
1 1
2 2
drop table t0, t1, t2, t3;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a char(200), b char(200), c char(200), primary key (a,b,c)) engine=innodb;
insert into t2 select concat(a, repeat('X',198)),repeat('B',200),repeat('B',200) from t1;
insert into t2 select concat(a, repeat('Y',198)),repeat('B',200),repeat('B',200) from t1;
alter table t2 add filler1 int;
insert into t1 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
set @save_join_buffer_size=@@join_buffer_size;
set join_buffer_size=1;
select * from t2 where filler1 in ( select a from t1);
a b c filler1
set join_buffer_size=default;
drop table t1, t2;
BUG#42740: crash in optimize_semijoin_nests
create table t1 (c6 timestamp,key (c6)) engine=innodb;
create table t2 (c2 double) engine=innodb;
explain select 1 from t2 where c2 = any (select log10(null) from t1 where c6 <null) ;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`c6` < NULL) and multiple equal(log10(NULL), `test`.`t2`.`c2`))
drop table t1, t2;
#
# BUG#42742: crash in setup_sj_materialization, Copy_field::set
#
create table t3 ( c1 year) charset latin1 engine=innodb;
insert into t3 values (2135),(2142);
create table t2 (c1 tinytext,c2 text,c6 timestamp) charset latin1 engine=innodb;
# The following must not crash, EXPLAIN should show one SJ strategy, not a mix:
explain select 1 from t2 where
c2 in (select 1 from t3, t2) and
c1 in (select convert(c6,char(1)) from t2);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Start temporary
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 2 100.00 End temporary
Warnings:
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t2` semi join (`test`.`t3` join `test`.`t2`) semi join (`test`.`t2`) where ((`test`.`t2`.`c2` = 1) and (`test`.`t2`.`c1` = cast(`test`.`t2`.`c6` as char(1) charset utf8mb4)))
drop table t2, t3;
#
# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
#
CREATE TABLE t1 (
i INT
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (2),(4);
CREATE TABLE t2 (
i INT,
vc VARCHAR(1)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES (8,NULL);
SELECT i
FROM t1
WHERE i IN (SELECT innr.i
FROM t2 LEFT JOIN t2 innr ON innr.vc)
AND i = 2;
i
DROP TABLE t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
|
