1
set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on';
2
set @old_opt_switch=@@optimizer_switch;
3
set optimizer_switch='subquery_materialization_cost_based=off';
4
drop table if exists t1, t2, t3, t1i, t2i, t3i;
5
drop view if exists v1, v2, v1m, v2m;
6
create table t1 (a1 char(8), a2 char(8));
7
create table t2 (b1 char(8), b2 char(8));
8
create table t3 (c1 char(8), c2 char(8));
9
insert into t1 values ('1 - 00', '2 - 00');
10
insert into t1 values ('1 - 01', '2 - 01');
11
insert into t1 values ('1 - 02', '2 - 02');
12
insert into t2 values ('1 - 01', '2 - 01');
13
insert into t2 values ('1 - 01', '2 - 01');
14
insert into t2 values ('1 - 02', '2 - 02');
15
insert into t2 values ('1 - 02', '2 - 02');
16
insert into t2 values ('1 - 03', '2 - 03');
17
insert into t3 values ('1 - 01', '2 - 01');
18
insert into t3 values ('1 - 02', '2 - 02');
19
insert into t3 values ('1 - 03', '2 - 03');
20
insert into t3 values ('1 - 04', '2 - 04');
21
create table t1i (a1 char(8), a2 char(8));
22
create table t2i (b1 char(8), b2 char(8));
23
create table t3i (c1 char(8), c2 char(8));
24
create index it1i1 on t1i (a1);
25
create index it1i2 on t1i (a2);
26
create index it1i3 on t1i (a1, a2);
27
create index it2i1 on t2i (b1);
28
create index it2i2 on t2i (b2);
29
create index it2i3 on t2i (b1, b2);
30
create index it3i1 on t3i (c1);
31
create index it3i2 on t3i (c2);
32
create index it3i3 on t3i (c1, c2);
33
insert into t1i select * from t1;
34
insert into t2i select * from t2;
35
insert into t3i select * from t3;
36
/******************************************************************************
38
******************************************************************************/
39
# non-indexed nullable fields
41
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
42
id select_type table type possible_keys key key_len ref rows filtered Extra
43
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
44
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
46
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` > '0'))
47
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
52
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
53
id select_type table type possible_keys key key_len ref rows filtered Extra
54
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
55
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
57
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` > '0'))
58
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
63
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
64
id select_type table type possible_keys key key_len ref rows filtered Extra
65
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
66
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
68
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` > '0'))
69
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
74
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
75
id select_type table type possible_keys key key_len ref rows filtered Extra
76
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
77
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
79
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` having 1 ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1`.`a1` = `materialized-subquery`.`b1`) and (`test`.`t1`.`a2` = `materialized-subquery`.`min(b2)`)))))
80
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
85
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
86
id select_type table type possible_keys key key_len ref rows filtered Extra
87
1 SIMPLE t2i index it2i1,it2i3 it2i1 9 NULL 5 50.00 Using where; Using index; LooseScan
88
1 SIMPLE t1i ref it1i1,it1i3 it1i1 9 test.t2i.b1 1 100.00 NULL
90
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
91
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
96
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
97
id select_type table type possible_keys key key_len ref rows filtered Extra
98
1 SIMPLE t2i index it2i1,it2i3 it2i1 9 NULL 5 50.00 Using where; Using index; LooseScan
99
1 SIMPLE t1i ref it1i1,it1i3 it1i1 9 test.t2i.b1 1 100.00 NULL
101
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
102
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
107
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
108
id select_type table type possible_keys key key_len ref rows filtered Extra
109
1 SIMPLE t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan
110
1 SIMPLE t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where
112
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
113
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
118
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
119
id select_type table type possible_keys key key_len ref rows filtered Extra
120
1 SIMPLE t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan
121
1 SIMPLE t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where
123
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
124
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
129
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
130
id select_type table type possible_keys key key_len ref rows filtered Extra
131
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
132
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
134
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` having 1 ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1i`.`a1` = `materialized-subquery`.`b1`) and (`test`.`t1i`.`a2` = `materialized-subquery`.`min(b2)`)))))
135
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
140
select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
141
id select_type table type possible_keys key key_len ref rows filtered Extra
142
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
143
2 SUBQUERY t2i range it2i1,it2i3 it2i3 9 NULL 3 100.00 Using index for group-by
145
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1` having 1 ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1`.`a1` = `materialized-subquery`.`b1`) and (`test`.`t1`.`a2` = `materialized-subquery`.`max(b2)`)))))
146
select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
150
prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
152
id select_type table type possible_keys key key_len ref rows Extra
153
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
154
2 SUBQUERY t2i range it2i1,it2i3 it2i3 9 NULL 3 Using index for group-by
156
id select_type table type possible_keys key key_len ref rows Extra
157
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
158
2 SUBQUERY t2i range it2i1,it2i3 it2i3 9 NULL 3 Using index for group-by
159
prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
169
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
170
id select_type table type possible_keys key key_len ref rows filtered Extra
171
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
172
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
174
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` having 1 ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1`.`a1` = `materialized-subquery`.`b1`) and (`test`.`t1`.`a2` = `materialized-subquery`.`min(b2)`)))))
175
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
179
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1);
180
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
182
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
183
id select_type table type possible_keys key key_len ref rows filtered Extra
184
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 NULL
185
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
187
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`))
188
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
193
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
194
id select_type table type possible_keys key key_len ref rows filtered Extra
195
1 SIMPLE t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan
196
1 SIMPLE t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where
198
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`))
199
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
203
/******************************************************************************
204
* Views, UNIONs, several levels of nesting.
205
******************************************************************************/
206
# materialize the result of subquery over temp-table view
207
create algorithm=merge view v1 as
208
select b1, c2 from t2, t3 where b2 > c2;
209
create algorithm=merge view v2 as
210
select b1, c2 from t2, t3 group by b2, c2;
212
Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm)
213
create algorithm=temptable view v1m as
214
select b1, c2 from t2, t3 where b2 > c2;
215
create algorithm=temptable view v2m as
216
select b1, c2 from t2, t3 group by b2, c2;
217
select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null);
223
select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null);
229
select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null);
235
select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null);
241
drop view v1, v2, v1m, v2m;
244
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and
245
(a1, a2) in (select c1, c2 from t3
246
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
247
id select_type table type possible_keys key key_len ref rows filtered Extra
248
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
249
1 SIMPLE <subquery3> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL
250
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(<subquery3>); Using join buffer (Block Nested Loop)
251
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where
252
3 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
254
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`<subquery3>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t3`.`c2` > '0') and (`test`.`t1`.`a1` > '0'))
256
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and
257
(a1, a2) in (select c1, c2 from t3
258
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
264
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
265
(a1, a2) in (select c1, c2 from t3i
266
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
267
id select_type table type possible_keys key key_len ref rows filtered Extra
268
1 SIMPLE t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan
269
1 SIMPLE t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where
270
1 SIMPLE t3i ref it3i1,it3i2,it3i3 it3i1 9 test.t2i.b1 1 100.00 Using where
271
1 SIMPLE t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t2i.b1,test.t2i.b2 2 100.00 Using index; FirstMatch(t1i)
273
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i` join `test`.`t3i`) semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b2` > '0') and (`test`.`t2i`.`b1` > '0'))
275
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
276
(a1, a2) in (select c1, c2 from t3i
277
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
283
where (a1, a2) in (select b1, b2 from t2
284
where b2 in (select c2 from t3 where c2 LIKE '%02') or
285
b2 in (select c2 from t3 where c2 LIKE '%03')) and
286
(a1, a2) in (select c1, c2 from t3
287
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
288
id select_type table type possible_keys key key_len ref rows filtered Extra
289
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
290
1 PRIMARY <subquery5> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL
291
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(<subquery5>); Using join buffer (Block Nested Loop)
292
5 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where
293
5 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
294
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
295
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
297
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) semi join (`test`.`t2`) where ((`<subquery5>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`<subquery5>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t3`.`c2` > '0') and (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#3 */ select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`)))))))
299
where (a1, a2) in (select b1, b2 from t2
300
where b2 in (select c2 from t3 where c2 LIKE '%02') or
301
b2 in (select c2 from t3 where c2 LIKE '%03')) and
302
(a1, a2) in (select c1, c2 from t3
303
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
308
where (a1, a2) in (select b1, b2 from t2
309
where b2 in (select c2 from t3 t3a where c1 = a1) or
310
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
311
(a1, a2) in (select c1, c2 from t3 t3c
312
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
313
id select_type table type possible_keys key key_len ref rows filtered Extra
314
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
315
1 PRIMARY <subquery5> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL
316
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(<subquery5>); Using join buffer (Block Nested Loop)
317
5 MATERIALIZED t3c ALL NULL NULL NULL NULL 4 100.00 Using where
318
5 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
319
4 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where
320
3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where
322
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
323
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`<subquery5>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3c`.`c2`) and (`<subquery5>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3c`.`c1`) and (<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`)))))) and (`test`.`t3c`.`c2` > '0'))
325
where (a1, a2) in (select b1, b2 from t2
326
where b2 in (select c2 from t3 t3a where c1 = a1) or
327
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
328
(a1, a2) in (select c1, c2 from t3 t3c
329
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
335
where (a1, a2) in (select b1, b2 from t2
336
where b2 in (select c2 from t3 where c2 LIKE '%02') or
337
b2 in (select c2 from t3 where c2 LIKE '%03')
339
(a1, a2) in (select c1, c2 from t3
340
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
343
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
344
(a1, a2) in (select c1, c2 from t3i
345
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
346
id select_type table type possible_keys key key_len ref rows filtered Extra
347
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
348
1 PRIMARY <subquery5> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL
349
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(<subquery5>); Using join buffer (Block Nested Loop)
350
5 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where
351
5 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
352
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
353
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
354
7 UNION t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan
355
7 UNION t1i ref it1i1,it1i2,it1i3 it1i1 9 test.t2i.b1 1 100.00 Using where
356
7 UNION t3i ref it3i1,it3i2,it3i3 it3i1 9 test.t2i.b1 1 100.00 Using where
357
7 UNION t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t2i.b1,test.t2i.b2 2 100.00 Using index; FirstMatch(t1i)
358
NULL UNION RESULT <union1,7> ALL NULL NULL NULL NULL NULL NULL Using temporary
360
Note 1003 (/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) semi join (`test`.`t2`) where ((`<subquery5>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`<subquery5>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t3`.`c2` > '0') and (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#3 */ select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`)))))))) union (/* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i` join `test`.`t3i`) semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b2` > '0') and (`test`.`t2i`.`b1` > '0')))
362
where (a1, a2) in (select b1, b2 from t2
363
where b2 in (select c2 from t3 where c2 LIKE '%02') or
364
b2 in (select c2 from t3 where c2 LIKE '%03')
366
(a1, a2) in (select c1, c2 from t3
367
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
370
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
371
(a1, a2) in (select c1, c2 from t3i
372
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
378
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
379
(a1, a2) in (select c1, c2 from t3
380
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
381
id select_type table type possible_keys key key_len ref rows filtered Extra
382
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
383
1 PRIMARY <subquery4> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t1.a2 1 100.00 NULL
384
4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where
385
4 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
386
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
387
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
388
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Using temporary
390
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where ((`<subquery4>`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`<subquery4>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union /* select#3 */ select 1,1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and (`test`.`t3`.`c2` > '0'))
392
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
393
(a1, a2) in (select c1, c2 from t3
394
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
400
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
401
(c1, c2) in (select c1, c2 from t3
402
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
404
id select_type table type possible_keys key key_len ref rows filtered Extra
405
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
406
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (Block Nested Loop)
407
1 PRIMARY <subquery4> eq_ref <auto_key> <auto_key> 18 test.t1.a1,test.t3.c2 1 100.00 NULL
408
4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where
409
4 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
410
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
411
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
412
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Using temporary
414
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where ((`<subquery4>`.`c2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and (`<subquery4>`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union /* select#3 */ select 1,1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and (`test`.`t3`.`c2` > '0'))
416
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
417
(c1, c2) in (select c1, c2 from t3
418
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
421
1 - 01 2 - 01 1 - 01 2 - 01
422
1 - 02 2 - 02 1 - 02 2 - 02
423
/******************************************************************************
424
* Negative tests, where materialization should not be applied.
425
******************************************************************************/
426
# UNION in a subquery
429
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
430
id select_type table type possible_keys key key_len ref rows filtered Extra
431
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
432
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
433
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
434
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Using temporary
436
Note 1003 /* select#1 */ select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`a1`)) union /* select#3 */ select 1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2`.`b1`))))
438
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
445
where (a1, a2) in (select b1, b2 from t2
446
where b2 in (select c2 from t3 t3a where c1 = a1) or
447
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
448
(a1, a2) in (select c1, c2 from t3 t3c
449
where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
450
id select_type table type possible_keys key key_len ref rows filtered Extra
451
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
452
1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 2 100.00 Using index
453
1 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (Block Nested Loop)
454
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t3c); Using join buffer (Block Nested Loop)
455
4 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where
456
3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where
458
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
459
Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1
460
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3` `t3c`) semi join (`test`.`t2`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on <auto_key> where ((`test`.`t2`.`b2` = `materialized-subquery`.`c2`)))))))
462
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
463
id select_type table type possible_keys key key_len ref rows filtered Extra
464
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
465
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
467
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having ((<cache>(`test`.`t1`.`a1`) = '1 - 01') and (<cache>(`test`.`t1`.`a2`) = '2 - 01'))))
468
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
472
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
473
id select_type table type possible_keys key key_len ref rows filtered Extra
474
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
475
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
477
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having ((<cache>(`test`.`t1`.`a1`) = '1 - 01') and (<cache>(`test`.`t1`.`a2`) = '2 - 01'))))
478
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
481
/******************************************************************************
482
* Subqueries in other uncovered clauses.
483
******************************************************************************/
485
select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
486
((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL
490
/* GROUP BY clause */
491
create table columns (col int key);
492
insert into columns values (1), (2);
494
select * from t1 group by (select col from columns limit 1);
495
id select_type table type possible_keys key key_len ref rows filtered Extra
496
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 NULL
497
2 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index
499
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by (/* select#2 */ select `test`.`columns`.`col` from `test`.`columns` limit 1)
500
select * from t1 group by (select col from columns limit 1);
504
select * from t1 group by (a1 in (select col from columns));
505
id select_type table type possible_keys key key_len ref rows filtered Extra
506
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort
507
2 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key
509
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where <if>(outer_field_is_not_null, (<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`), true))))
510
select * from t1 group by (a1 in (select col from columns));
513
/* ORDER BY clause */
515
select * from t1 order by (select col from columns limit 1);
516
id select_type table type possible_keys key key_len ref rows filtered Extra
517
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 NULL
518
2 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index
520
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` order by (/* select#2 */ select `test`.`columns`.`col` from `test`.`columns` limit 1)
521
select * from t1 order by (select col from columns limit 1);
526
/******************************************************************************
527
* Column types/sizes that affect materialization.
528
******************************************************************************/
529
# test for BIT fields
530
create table t1bit (a1 bit(3), a2 bit(3));
531
create table t2bit (b1 bit(3), b2 bit(3));
532
insert into t1bit values (b'000', b'100');
533
insert into t1bit values (b'001', b'101');
534
insert into t1bit values (b'010', b'110');
535
insert into t2bit values (b'001', b'101');
536
insert into t2bit values (b'010', b'110');
537
insert into t2bit values (b'110', b'111');
538
explain extended select bin(a1), bin(a2)
540
where (a1, a2) in (select b1, b2 from t2bit);
541
id select_type table type possible_keys key key_len ref rows filtered Extra
542
1 SIMPLE t1bit ALL NULL NULL NULL NULL 3 100.00 NULL
543
1 SIMPLE t2bit ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1bit); Using join buffer (Block Nested Loop)
545
Note 1003 /* select#1 */ select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` semi join (`test`.`t2bit`) where ((`test`.`t2bit`.`b2` = `test`.`t1bit`.`a2`) and (`test`.`t2bit`.`b1` = `test`.`t1bit`.`a1`))
546
select bin(a1), bin(a2)
548
where (a1, a2) in (select b1, b2 from t2bit);
552
drop table t1bit, t2bit;
553
create table t1bb (a1 bit(3), a2 blob(3));
554
create table t2bb (b1 bit(3), b2 blob(3));
555
insert into t1bb values (b'000', '100');
556
insert into t1bb values (b'001', '101');
557
insert into t1bb values (b'010', '110');
558
insert into t2bb values (b'001', '101');
559
insert into t2bb values (b'010', '110');
560
insert into t2bb values (b'110', '111');
561
explain extended select bin(a1), a2
563
where (a1, a2) in (select b1, b2 from t2bb);
564
id select_type table type possible_keys key key_len ref rows filtered Extra
565
1 SIMPLE t1bb ALL NULL NULL NULL NULL 3 100.00 NULL
566
1 SIMPLE t2bb ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1bb); Using join buffer (Block Nested Loop)
568
Note 1003 /* select#1 */ select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2` from `test`.`t1bb` semi join (`test`.`t2bb`) where ((`test`.`t2bb`.`b2` = `test`.`t1bb`.`a2`) and (`test`.`t2bb`.`b1` = `test`.`t1bb`.`a1`))
571
where (a1, a2) in (select b1, b2 from t2bb);
575
drop table t1bb, t2bb;
576
drop table t1, t2, t3, t1i, t2i, t3i, columns;
577
/******************************************************************************
578
* Test the cache of the left operand of IN.
579
******************************************************************************/
580
# Test that default values of Cached_item are not used for comparison
581
create table t1 (s1 int);
582
create table t2 (s2 int);
583
insert into t1 values (5),(1),(0);
584
insert into t2 values (0), (1);
585
select s2 from t2 where s2 in (select s1 from t1);
590
create table t1 (a int not null, b int not null);
591
create table t2 (c int not null, d int not null);
592
create table t3 (e int not null);
593
insert into t1 values (1,10);
594
insert into t1 values (1,20);
595
insert into t1 values (2,10);
596
insert into t1 values (2,20);
597
insert into t1 values (2,30);
598
insert into t1 values (3,20);
599
insert into t1 values (4,40);
600
insert into t2 values (2,10);
601
insert into t2 values (2,20);
602
insert into t2 values (2,40);
603
insert into t2 values (3,20);
604
insert into t2 values (4,10);
605
insert into t2 values (5,10);
606
insert into t3 values (10);
607
insert into t3 values (10);
608
insert into t3 values (20);
609
insert into t3 values (30);
611
select a from t1 where a in (select c from t2 where d >= 20);
612
id select_type table type possible_keys key key_len ref rows filtered Extra
613
1 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL 0.00 NULL
614
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using join buffer (Block Nested Loop)
615
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where
617
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `<subquery2>`.`c`) and (`test`.`t2`.`d` >= 20))
618
select a from t1 where a in (select c from t2 where d >= 20);
624
create index it1a on t1(a);
626
select a from t1 where a in (select c from t2 where d >= 20);
627
id select_type table type possible_keys key key_len ref rows filtered Extra
628
1 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL 0.00 NULL
629
1 SIMPLE t1 ref it1a it1a 4 <subquery2>.c 2 100.00 Using index
630
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where
632
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `<subquery2>`.`c`) and (`test`.`t2`.`d` >= 20))
633
select a from t1 where a in (select c from t2 where d >= 20);
639
insert into t2 values (1,10);
641
select a from t1 where a in (select c from t2 where d >= 20);
642
id select_type table type possible_keys key key_len ref rows filtered Extra
643
1 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL 0.00 NULL
644
1 SIMPLE t1 ref it1a it1a 4 <subquery2>.c 2 100.00 Using index
645
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where
647
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `<subquery2>`.`c`) and (`test`.`t2`.`d` >= 20))
648
select a from t1 where a in (select c from t2 where d >= 20);
655
select a from t1 group by a having a in (select c from t2 where d >= 20);
656
id select_type table type possible_keys key key_len ref rows filtered Extra
657
1 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index
658
2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
660
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`c`)))))
661
select a from t1 group by a having a in (select c from t2 where d >= 20);
665
create index iab on t1(a, b);
667
select a from t1 group by a having a in (select c from t2 where d >= 20);
668
id select_type table type possible_keys key key_len ref rows filtered Extra
669
1 PRIMARY t1 index it1a,iab it1a 4 NULL 7 100.00 Using index
670
2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
672
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`c`)))))
673
select a from t1 group by a having a in (select c from t2 where d >= 20);
678
select a from t1 group by a
679
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
680
id select_type table type possible_keys key key_len ref rows filtered Extra
681
1 PRIMARY t1 index it1a,iab iab 8 NULL 7 100.00 Using index
682
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
683
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
685
Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
686
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` where (max(`test`.`t1`.`b`) = `test`.`t3`.`e`) having (<cache>(`test`.`t2`.`d`) >= <ref_null_helper>(`test`.`t3`.`e`))))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
687
select a from t1 group by a
688
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
694
where a in (select c from t2 where d >= some(select e from t3 where b=e));
695
id select_type table type possible_keys key key_len ref rows filtered Extra
696
1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 Start temporary
697
1 PRIMARY t1 ref it1a,iab iab 4 test.t2.c 1 100.00 Using where; Using index; End temporary
698
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
700
Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
701
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
703
where a in (select c from t2 where d >= some(select e from t3 where b=e));
710
drop table t1, t2, t3;
711
create table t2 (a int, b int, key(a), key(b));
712
insert into t2 values (3,3),(3,3),(3,3);
713
select 1 from t2 where
716
t2.a = 3 and not t2.a not in (select t2.b from t2);
722
create table t1 (a1 int key);
723
create table t2 (b1 int);
724
insert into t1 values (5);
725
explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
726
id select_type table type possible_keys key key_len ref rows Extra
727
1 SIMPLE t1 system NULL NULL NULL NULL 1 NULL
728
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where; FirstMatch(t1)
729
select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
732
explain select min(a1) from t1 where 7 in (select b1 from t2);
733
id select_type table type possible_keys key key_len ref rows Extra
734
1 SIMPLE t1 system NULL NULL NULL NULL 1 NULL
735
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where; FirstMatch(t1)
736
select min(a1) from t1 where 7 in (select b1 from t2);
740
create table t1 (a char(2), b varchar(10));
741
insert into t1 values ('a', 'aaa');
742
insert into t1 values ('aa', 'aaaa');
743
explain select a,b from t1 where b in (select a from t1);
744
id select_type table type possible_keys key key_len ref rows Extra
745
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
746
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
747
select a,b from t1 where b in (select a from t1);
749
prepare st1 from "select a,b from t1 where b in (select a from t1)";
755
CREATE TABLE t1 (a varchar(5), b varchar(10));
756
INSERT INTO t1 VALUES
757
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
758
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
759
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
765
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
766
id select_type table type possible_keys key key_len ref rows Extra
767
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
768
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
769
ALTER TABLE t1 ADD INDEX(a);
771
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
776
SHOW SESSION STATUS LIKE 'Sort_scan%';
779
EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
780
id select_type table type possible_keys key key_len ref rows Extra
781
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
782
2 SUBQUERY t1 ALL a NULL NULL NULL 9 Using temporary; Using filesort
784
CREATE TABLE t1 (a INT);
785
INSERT INTO t1 VALUES (1),(2);
786
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
787
id select_type table type possible_keys key key_len ref rows filtered Extra
788
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 FirstMatch
789
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop)
791
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` semi join (`test`.`t1`) where 1
792
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
793
id select_type table type possible_keys key key_len ref rows filtered Extra
794
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch
795
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop)
797
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` semi join (`test`.`t1`) where (`test`.`t1`.`a` > 3)
800
# BUG#49630: Segfault in select_describe() with double
801
# nested subquery and materialization
803
CREATE TABLE t1 (t1i int);
804
CREATE TABLE t2 (t2i int);
805
CREATE TABLE t3 (t3i int);
806
CREATE TABLE t4 (t4i int);
807
INSERT INTO t1 VALUES (1);
808
INSERT INTO t2 VALUES (1),(2);
809
INSERT INTO t3 VALUES (1),(2);
810
INSERT INTO t4 VALUES (1),(2);
814
FROM t1 JOIN t4 ON t1i=t4i
824
id select_type table type possible_keys key key_len ref rows Extra
825
1 SIMPLE t1 system NULL NULL NULL NULL 1 NULL
826
1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where
827
1 SIMPLE <subquery2> const <auto_key> <auto_key> 5 const 1 NULL
828
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where
829
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
830
DROP TABLE t1,t2,t3,t4;
832
# BUG#46680 - Assertion failed in file item_subselect.cc,
833
# line 305 crashing on HAVING subquery
839
v VARCHAR(1) DEFAULT NULL,
842
CREATE TABLE t2 LIKE t1;
843
CREATE TABLE t3 LIKE t1;
844
CREATE TABLE empty1 (a int);
845
INSERT INTO t1 VALUES (1,'c'),(2,NULL);
846
INSERT INTO t2 VALUES (3,'m'),(4,NULL);
847
INSERT INTO t3 VALUES (1,'n');
850
# 1) Test that subquery materialization is setup for query with
851
# premature optimize() exit due to "Impossible WHERE"
854
FROM t2 JOIN t1 ON t1.pk=t2.pk
862
Warning 1292 Truncated incorrect INTEGER value: 'j'
866
FROM t2 JOIN t1 ON t1.pk=t2.pk
871
id select_type table type possible_keys key key_len ref rows Extra
872
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
873
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
875
Warning 1292 Truncated incorrect INTEGER value: 'j'
878
# 2) Test that subquery materialization is setup for query with
879
# premature optimize() exit due to "No matching min/max row"
897
id select_type table type possible_keys key key_len ref rows Extra
898
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No matching min/max row
899
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
902
# 3) Test that subquery materialization is setup for query with
903
# premature optimize() exit due to "Select tables optimized away"
921
id select_type table type possible_keys key key_len ref rows Extra
922
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
923
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
926
# 4) Test that subquery materialization is setup for query with
927
# premature optimize() exit due to "No matching row in const table"
931
FROM (SELECT a FROM empty1) tt
940
FROM (SELECT a FROM empty1) tt
944
id select_type table type possible_keys key key_len ref rows Extra
945
1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
946
3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
947
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
950
# 5) Test that subquery materialization is setup for query with
951
# premature optimize() exit due to "Impossible WHERE noticed
952
# after reading const tables"
956
WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
966
WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
970
id select_type table type possible_keys key key_len ref rows Extra
971
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index
972
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index; FirstMatch(t1)
973
3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
975
# Cleanup for BUG#46680
977
DROP TABLE IF EXISTS t1,t2,t3,empty1;
979
# BUG#52344 - Subquery materialization:
980
# Assertion if subquery in on-clause of outer join
982
CREATE TABLE t1 (i INTEGER);
983
INSERT INTO t1 VALUES (10);
984
CREATE TABLE t2 (j INTEGER);
985
INSERT INTO t2 VALUES (5);
986
CREATE TABLE t3 (k INTEGER);
988
SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
989
id select_type table type possible_keys key key_len ref rows Extra
990
1 SIMPLE t1 system NULL NULL NULL NULL 1 NULL
991
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Start temporary
992
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; End temporary
993
SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
997
SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
998
id select_type table type possible_keys key key_len ref rows Extra
999
1 PRIMARY t1 system NULL NULL NULL NULL 1 NULL
1000
1 PRIMARY t2 system NULL NULL NULL NULL 1 NULL
1001
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
1002
SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
1005
DROP TABLE t1, t2, t3;
1008
pk INTEGER AUTO_INCREMENT,
1009
col_int_nokey INTEGER,
1010
col_int_key INTEGER,
1011
col_varchar_key VARCHAR(1),
1014
KEY (col_varchar_key, col_int_key)
1018
col_int_key, col_int_nokey, col_varchar_key
1042
SELECT table2.col_varchar_key AS field1,
1043
table2.col_int_nokey AS field2
1044
FROM ( t1 AS table1 LEFT OUTER JOIN t1 AS table2
1045
ON (table2.col_varchar_key = table1.col_varchar_key ) )
1047
HAVING ( field2 ) IN
1048
( SELECT SUBQUERY2_t2.col_int_nokey AS SUBQUERY2_field2
1049
FROM ( t1 AS SUBQUERY2_t1 JOIN t1 AS SUBQUERY2_t2
1050
ON (SUBQUERY2_t2.col_varchar_key = SUBQUERY2_t1.col_varchar_key ) ) )
1058
# BUG#53103: MTR test ps crashes in optimize_cond()
1059
# when running with --debug
1061
CREATE TABLE t1(track varchar(15));
1062
INSERT INTO t1 VALUES ('CAD'), ('CAD');
1066
track IN (SELECT track FROM t1
1068
HAVING track>='CAD')";
1077
DEALLOCATE PREPARE STMT;
1081
# BUG#54511 - Assertion failed: cache != 0L in file
1082
# sql_select.cc::sub_select_cache on HAVING
1084
CREATE TABLE t1 (i int(11));
1085
CREATE TABLE t2 (c char(1));
1086
CREATE TABLE t3 (c char(1));
1087
INSERT INTO t1 VALUES (1), (2);
1088
INSERT INTO t2 VALUES ('a'), ('b');
1089
INSERT INTO t3 VALUES ('x'), ('y');
1093
IN (SELECT t2.c FROM (t2 JOIN t3));
1095
DROP TABLE t1,t2,t3;
1098
# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION...
1099
# on subquery in FROM
1101
CREATE TABLE t1 (a INTEGER);
1102
CREATE TABLE t2 (b INTEGER);
1103
INSERT INTO t2 VALUES (1);
1104
explain SELECT a FROM (
1105
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
1107
id select_type table type possible_keys key key_len ref rows Extra
1108
1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
1109
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
1110
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
1112
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
1118
# Bug#59833 - materialization=on/off leads to different result set
1123
f1 int DEFAULT NULL,
1128
f1 int DEFAULT NULL,
1131
INSERT INTO t1 VALUES (10,0);
1132
INSERT INTO t2 VALUES (10,0),(11,0);
1133
explain SELECT * FROM t1 JOIN t2 USING (f1)
1134
WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
1135
id select_type table type possible_keys key key_len ref rows Extra
1136
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1137
SELECT * FROM t1 JOIN t2 USING (f1)
1138
WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
1143
# Bug#11852644 - CRASH IN ITEM_REF::SAVE_IN_FIELD ON SELECT DISTINCT
1146
col_varchar_key varchar(1) DEFAULT NULL,
1147
col_varchar_nokey varchar(1) DEFAULT NULL,
1148
KEY col_varchar_key (col_varchar_key))
1150
INSERT INTO t1 VALUES
1151
('v','v'),('r','r');
1153
col_varchar_key varchar(1) DEFAULT NULL,
1154
col_varchar_nokey varchar(1) DEFAULT NULL,
1155
KEY col_varchar_key(col_varchar_key))
1157
INSERT INTO t2 VALUES
1158
('r','r'),('c','c');
1159
CREATE VIEW v3 AS SELECT * FROM t2;
1160
SELECT DISTINCT alias2.col_varchar_key
1161
FROM t1 AS alias1 JOIN v3 AS alias2
1162
ON alias2.col_varchar_key = alias1.col_varchar_key
1163
HAVING col_varchar_key IN (SELECT col_varchar_nokey FROM t2)
1171
# Bug#12668294 - GROUP BY ON EMPTY RESULT GIVES EMPTY ROW
1172
# INSTEAD OF NULL WHEN MATERIALIZATION ON
1174
CREATE TABLE t1 (col_int_nokey INT) ENGINE=MEMORY;
1175
CREATE TABLE t2 (col_int_nokey INT) ENGINE=MEMORY;
1176
INSERT INTO t2 VALUES (8),(7);
1177
CREATE TABLE t3 (col_int_nokey INT) ENGINE=MEMORY;
1178
INSERT INTO t3 VALUES (7);
1179
SELECT MIN(t3.col_int_nokey),t1.col_int_nokey AS field3
1183
WHERE (194, 200) IN (
1184
SELECT SQ4_alias1.col_int_nokey,
1185
SQ4_alias2.col_int_nokey
1186
FROM t2 AS SQ4_alias1
1189
ON SQ4_alias2.col_int_nokey = 5
1192
MIN(t3.col_int_nokey) field3
1197
# Bug#13419028 - SUBQUERY MATERIALIZATION NOT USED IN CREATE
1200
CREATE TABLE t1(a int);
1201
INSERT INTO t1 values(1),(2);
1202
CREATE TABLE t2(a int);
1203
INSERT INTO t2 values(1),(2);
1204
EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
1205
id select_type table type possible_keys key key_len ref rows Extra
1206
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
1207
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
1209
SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
1213
CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
1218
SHOW STATUS LIKE "CREATED_TMP_TABLES";
1220
Created_tmp_tables 0
1221
DROP TABLE t1,t2,t3;
1223
# Bug#13552968: Extra row with materialization on join + subquery in
1226
col_varchar_nokey varchar(1) NOT NULL
1228
INSERT INTO t1 VALUES ('b');
1230
col_varchar_nokey varchar(1) NOT NULL
1232
INSERT INTO t2 VALUES ('k');
1234
col_varchar_nokey varchar(1) NOT NULL
1236
explain SELECT STRAIGHT_JOIN *
1237
FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
1239
id select_type table type possible_keys key key_len ref rows Extra
1240
1 PRIMARY t1 system NULL NULL NULL NULL 1 NULL
1241
1 PRIMARY t2 system NULL NULL NULL NULL 1 NULL
1242
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
1243
SELECT STRAIGHT_JOIN *
1244
FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
1246
col_varchar_nokey col_varchar_nokey
1248
DROP TABLE t1, t2, t3;
1249
# End of test for bug#13552968
1251
# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive())
1252
# in join_read_const_table()
1254
CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM;
1255
INSERT INTO t1 VALUES(1);
1256
CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM;
1258
FROM t1 LEFT JOIN t2
1259
ON t2.v IN(SELECT v FROM t1);
1263
# End of test for bug#13591383.
1265
# Bug#13607423: Assertion !(*tab->on_expr_ref->is_expensive())
1266
# in join_read_const_table()
1270
col_int_nokey int DEFAULT NULL,
1271
col_int_key int DEFAULT NULL,
1273
KEY col_int_key (col_int_key)
1275
INSERT INTO t1 VALUES (1,2,4), (2,150,62);
1278
col_int_key int DEFAULT NULL,
1281
INSERT INTO t2 VALUES (1,7);
1282
explain SELECT table1.pk, table2.pk
1283
FROM t2 AS table1 LEFT JOIN t2 AS table2
1284
ON table2.pk = table1.pk AND
1285
table2.col_int_key IN
1288
WHERE innr.col_int_nokey > innr.col_int_nokey
1289
GROUP BY col_int_key
1292
id select_type table type possible_keys key key_len ref rows Extra
1293
1 PRIMARY table1 system NULL NULL NULL NULL 1 NULL
1294
1 PRIMARY table2 system PRIMARY NULL NULL NULL 1 NULL
1295
2 DEPENDENT SUBQUERY innr ALL col_int_key NULL NULL NULL 2 Using where; Using temporary; Using filesort
1297
SELECT table1.pk, table2.pk
1298
FROM t2 AS table1 LEFT JOIN t2 AS table2
1299
ON table2.pk = table1.pk AND
1300
table2.col_int_key IN
1303
WHERE innr.col_int_nokey > innr.col_int_nokey
1304
GROUP BY col_int_key
1309
SHOW SESSION STATUS LIKE 'Sort_scan%';
1313
# End of test for bug#13607423.
1315
Test of WL#6094 "Allow subquery materialization in NOT IN if all
1316
columns are not nullable"
1318
create table t1(a int not null);
1319
create table t2(a int not null);
1320
insert into t1 values(1),(2);
1321
insert into t2 values(1),(2);
1324
cols not nullable => subq materialization
1325
explain extended select a, (a,a) in (select a,a from t2) from t1;
1326
id select_type table type possible_keys key key_len ref rows filtered Extra
1327
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL
1328
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL
1330
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),(`test`.`t1`.`a`,`test`.`t1`.`a`) in ( <materialize> (/* select#2 */ select `test`.`t2`.`a`,`test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`) and (`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `(a,a) in (select a,a from t2)` from `test`.`t1`
1331
select a, (a,a) in (select a,a from t2) from t1;
1332
a (a,a) in (select a,a from t2)
1336
cols not nullable => subq materialization
1337
explain extended select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3)
1338
from t1 join t2 on t1.a+t2.a=1000;
1339
id select_type table type possible_keys key key_len ref rows filtered Extra
1340
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL
1341
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1342
2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 NULL
1344
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),(`test`.`t1`.`a`,`test`.`t1`.`a`) in ( <materialize> (/* select#2 */ select `test`.`t3`.`a`,`test`.`t3`.`a` from `test`.`t2` `t3` where 1 having 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`) and (`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `(t1.a,t1.a) in (select a,a from t2 as t3)` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)
1345
select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3)
1346
from t1 join t2 on t1.a+t2.a=1000;
1347
a a (t1.a,t1.a) in (select a,a from t2 as t3)
1349
t2.a is not nullable, but in the query it may appear as NULL
1350
as it's in an outer join. So, no materialization.
1351
explain extended select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3)
1352
from t1 left join t2 on t1.a+t2.a=1000;
1353
id select_type table type possible_keys key key_len ref rows filtered Extra
1354
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL
1355
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1356
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
1358
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where (<if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or <cache>(isnull(`test`.`t3`.`a`))), true) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or <cache>(isnull(`test`.`t3`.`a`))), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true)))) AS `(t2.a,t2.a) in (select a,a from t2 as t3)` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)) where 1
1359
select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3)
1360
from t1 left join t2 on t1.a+t2.a=1000;
1361
a a (t2.a,t2.a) in (select a,a from t2 as t3)
1365
alter table t2 modify a int;
1366
two nullable inner cols => no subq materialization
1367
explain extended select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3)
1368
from t1 join t2 on t1.a+t2.a=1000;
1369
id select_type table type possible_keys key key_len ref rows filtered Extra
1370
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL
1371
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1372
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
1374
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where (((<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`)) and ((<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`))) having (<is_not_null_test>(`test`.`t3`.`a`) and <is_not_null_test>(`test`.`t3`.`a`)))) AS `(t1.a,t1.a) in (select a,a from t2 as t3)` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)
1375
select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3)
1376
from t1 join t2 on t1.a+t2.a=1000;
1377
a a (t1.a,t1.a) in (select a,a from t2 as t3)
1378
alter table t2 modify a int not null;
1382
top-level => subq materialization. With one exception: if
1383
semijoin is enabled in @@optimizer_switch, semijoin is chosen,
1384
then rejected (due to outer join), and in that case, the
1385
fallback is IN->EXISTS, subq-materialization is not tried...
1386
explain extended select t1.a, t2.a
1387
from t1 join t2 on t1.a+t2.a=3
1388
where (t2.a,t2.a) in (select a,a from t2 as t3);
1389
id select_type table type possible_keys key key_len ref rows filtered Extra
1390
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 NULL
1391
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1392
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t2); Using join buffer (Block Nested Loop)
1394
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2` `t3`) join `test`.`t2` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 3))
1396
from t1 join t2 on t1.a+t2.a=3
1397
where (t2.a,t2.a) in (select a,a from t2 as t3);
1402
cols not nullable => subq materialization
1403
explain extended select t1.a, t2.a
1404
from t1 join t2 on t1.a+t2.a=3
1405
where (t2.a,t2.a) not in (select a,a from t2 as t3);
1406
id select_type table type possible_keys key key_len ref rows filtered Extra
1407
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL
1408
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1409
2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 NULL
1411
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((not(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),(`test`.`t2`.`a`,`test`.`t2`.`a`) in ( <materialize> (/* select#2 */ select `test`.`t3`.`a`,`test`.`t3`.`a` from `test`.`t2` `t3` where 1 having 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`) and (`test`.`t2`.`a` = `materialized-subquery`.`a`))))))) and ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 3))
1413
from t1 join t2 on t1.a+t2.a=3
1414
where (t2.a,t2.a) not in (select a,a from t2 as t3);
1418
Test of WL6095 "Allow subquery materialization in NOT IN if
1419
single-column subquery"
1421
create table t1(a int null);
1422
create table t2(a int null);
1423
insert into t1 values(1),(2);
1424
insert into t2 values(1),(2);
1426
one col => subq materialization
1427
explain extended select a, a in (select a from t2) from t1;
1428
id select_type table type possible_keys key key_len ref rows filtered Extra
1429
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL
1430
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL
1432
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `a in (select a from t2)` from `test`.`t1`
1433
select a, a in (select a from t2) from t1;
1434
a a in (select a from t2)
1438
t2.a is not nullable, but in the query it may appear as NULL
1439
as it's in an outer join. But there is only one inner column so
1440
materialization is possible
1441
explain extended select t1.a, t2.a, t2.a in (select * from t2 as t3)
1442
from t1 left join t2 on t1.a+t2.a=1000;
1443
id select_type table type possible_keys key key_len ref rows filtered Extra
1444
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL
1445
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1446
2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 NULL
1448
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t3`.`a` from `test`.`t2` `t3` where 1 having 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`))))) AS `t2.a in (select * from t2 as t3)` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)) where 1
1449
select t1.a, t2.a, t2.a in (select * from t2 as t3)
1450
from t1 left join t2 on t1.a+t2.a=1000;
1451
a a t2.a in (select * from t2 as t3)
1455
_two_ outer columns, nullable => no materialization
1456
explain extended select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3)
1457
from t1 left join t2 on t1.a+t2.a=1000;
1458
id select_type table type possible_keys key key_len ref rows filtered Extra
1459
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 NULL
1460
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1461
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
1463
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where (<if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`)), true) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true)))) AS `(t2.a,t2.a) in (select a,a from t2 as t3)` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)) where 1
1464
select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3)
1465
from t1 left join t2 on t1.a+t2.a=1000;
1466
a a (t2.a,t2.a) in (select a,a from t2 as t3)
1472
create table t1(a int, b int);
1473
create table t2(a int);
1474
insert into t1 values(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
1475
insert into t2 values(10),(20);
1477
explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a
1478
having (z in (select * from t2)) is null;
1479
id select_type table type possible_keys key key_len ref rows filtered Extra
1480
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort
1481
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL
1483
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having isnull(<in_optimizer>(`z`,`z` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`z` in <temporary table> on <auto_key> where ((`z` = `materialized-subquery`.`a`))))))
1484
select t1.a as z, sum(t1.b) from t1 group by t1.a
1485
having (z in (select * from t2)) is null;
1488
insert into t1 values(null,null);
1489
explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a
1490
having (z in (select * from t2)) is null;
1491
id select_type table type possible_keys key key_len ref rows filtered Extra
1492
1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort
1493
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL
1495
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having isnull(<in_optimizer>(`z`,`z` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`z` in <temporary table> on <auto_key> where ((`z` = `materialized-subquery`.`a`))))))
1496
select t1.a as z, sum(t1.b) from t1 group by t1.a
1497
having (z in (select * from t2)) is null;
1500
one outer NULL and one inner NULL
1501
insert into t2 values(null);
1502
explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a
1503
having (z in (select * from t2)) is null;
1504
id select_type table type possible_keys key key_len ref rows filtered Extra
1505
1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort
1506
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 NULL
1508
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having isnull(<in_optimizer>(`z`,`z` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`z` in <temporary table> on <auto_key> where ((`z` = `materialized-subquery`.`a`))))))
1509
select t1.a as z, sum(t1.b) from t1 group by t1.a
1510
having (z in (select * from t2)) is null;
1516
delete from t1 where a is null;
1517
explain extended select t1.a as z, sum(t1.b) from t1 group by t1.a
1518
having (z in (select * from t2)) is null;
1519
id select_type table type possible_keys key key_len ref rows filtered Extra
1520
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort
1521
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 NULL
1523
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having isnull(<in_optimizer>(`z`,`z` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`z` in <temporary table> on <auto_key> where ((`z` = `materialized-subquery`.`a`))))))
1524
select t1.a as z, sum(t1.b) from t1 group by t1.a
1525
having (z in (select * from t2)) is null;
1531
Verify that an inner NULL is looked up only once (result is
1533
create table t1(a int);
1534
create table t2(a int);
1535
insert into t1 values(1),(2),(3),(4),(5),(6);
1536
insert into t1 select * from t1;
1537
insert into t2 values(10),(20),(NULL);
1538
explain extended select a, (a in (select * from t2)) from t1;
1539
id select_type table type possible_keys key key_len ref rows filtered Extra
1540
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 NULL
1541
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 NULL
1543
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `(a in (select * from t2))` from `test`.`t1`
1545
select a, (a in (select * from t2)) from t1;
1546
a (a in (select * from t2))
1559
There will be one look-up in the temporary table for each row
1560
of t1 (12), plus one additional look-up to check whether table
1561
contains a NULL value.
1562
show status like "handler_read_key";
1567
# Bug#13495157 - SUBQUERY MATERIALIZATION NOT USED FOR CERTAIN
1570
CREATE TABLE t1(a INT);
1571
INSERT INTO t1 VALUES(1),(2),(3);
1572
CREATE TABLE t2(a INT);
1573
INSERT INTO t2 VALUES(1),(2),(4);
1574
# subquery materialization used for SELECT:
1575
EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
1576
id select_type table type possible_keys key key_len ref rows Extra
1577
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1578
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
1579
SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
1582
# Also used for INSERT SELECT:
1583
CREATE TABLE t3 SELECT * FROM t1;
1584
EXPLAIN INSERT INTO t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
1585
id select_type table type possible_keys key key_len ref rows Extra
1586
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1587
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
1588
INSERT INTO t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
1595
EXPLAIN INSERT INTO t2 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
1596
id select_type table type possible_keys key key_len ref rows Extra
1597
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using temporary
1598
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
1599
INSERT INTO t2 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
1606
EXPLAIN INSERT INTO t2 SELECT * FROM t2 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
1607
id select_type table type possible_keys key key_len ref rows Extra
1608
1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary
1609
1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t2); Using join buffer (Block Nested Loop)
1610
INSERT INTO t2 SELECT * FROM t2 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
1620
# Not used for single-table UPDATE, DELETE:
1621
EXPLAIN SELECT * FROM t2 WHERE a IN (SELECT * FROM t1);
1622
id select_type table type possible_keys key key_len ref rows Extra
1623
1 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL NULL
1624
1 SIMPLE t2 ALL NULL NULL NULL NULL 7 Using where; Using join buffer (Block Nested Loop)
1625
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 NULL
1626
EXPLAIN UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t1);
1627
id select_type table type possible_keys key key_len ref rows Extra
1628
1 PRIMARY t2 ALL NULL NULL NULL NULL 7 Using where
1629
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
1630
UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t1);
1640
EXPLAIN DELETE FROM t2 WHERE a IN (SELECT * FROM t1);
1641
id select_type table type possible_keys key key_len ref rows Extra
1642
1 PRIMARY t2 ALL NULL NULL NULL NULL 7 Using where
1643
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
1644
DELETE FROM t2 WHERE a IN (SELECT * FROM t1);
1653
EXPLAIN UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t2);
1654
ERROR HY000: You can't specify target table 't2' for update in FROM clause
1655
EXPLAIN DELETE FROM t2 WHERE a IN (SELECT * FROM t2);
1656
ERROR HY000: You can't specify target table 't2' for update in FROM clause
1657
UPDATE t2 SET a=3 WHERE a=0;
1658
# Used for multi-table UPDATE, DELETE:
1659
EXPLAIN SELECT * FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
1660
id select_type table type possible_keys key key_len ref rows Extra
1661
1 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL NULL
1662
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
1663
1 SIMPLE t3 ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop)
1664
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where
1665
EXPLAIN UPDATE t2,t3 SET t2.a=t2.a-2 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
1666
id select_type table type possible_keys key key_len ref rows Extra
1667
1 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL NULL
1668
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
1669
1 SIMPLE t3 ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop)
1670
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where
1671
UPDATE t2,t3 SET t2.a=t2.a-2 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
1680
EXPLAIN DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
1681
id select_type table type possible_keys key key_len ref rows Extra
1682
1 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL NULL
1683
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
1684
1 SIMPLE t3 ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop)
1685
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where
1686
DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
1691
EXPLAIN UPDATE t2,t3 SET t2.a=10 WHERE t2.a IN (SELECT * FROM t2 WHERE a <> 2);
1692
ERROR HY000: You can't specify target table 't2' for update in FROM clause
1693
EXPLAIN DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t2 WHERE a <> 2);
1694
ERROR HY000: You can't specify target table 't2' for update in FROM clause
1695
DROP TABLE t1,t2,t3;
1697
# Test that subquery materialization only does one lookup: does
1698
# not try to read the next row if the first row failed the
1699
# subquery's WHERE. We use a case where index lookup is not
1700
# enough to satisfy IN(), because index has length two when the
1701
# outer value has length three, and thus the post-filtering
1702
# WHERE added by subselect_hash_sj_engine::setup() makes the
1705
create table t1 (a varchar(3));
1706
create table t2 (a varchar(2));
1707
insert into t1 values('aaa'), ('aaa');
1708
insert into t2 values('aa'), ('aa');
1709
explain select * from t1 where a in (select a from t2);
1710
id select_type table type possible_keys key key_len ref rows Extra
1711
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
1712
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
1714
select * from t1 where a in (select a from t2);
1716
show status like "handler_read%";
1718
Handler_read_first 0
1724
Handler_read_rnd_next 6
1727
# Bug#13655791 DIFFERENT RESULT WITH WL6094 ON QUERY WITH XOR
1728
# IN WHERE CLAUSE + MYISAM
1732
col_varchar_nokey varchar(1) DEFAULT NULL,
1735
INSERT INTO t1 VALUES (10,'x');
1738
col_varchar_nokey varchar(1) DEFAULT NULL,
1741
INSERT INTO t2 VALUES (1,'v'), (5,'x'), (11,'z'), (12,'c'), (15,'y');
1744
col_int_key int DEFAULT NULL,
1746
KEY col_int_key (col_int_key)
1748
INSERT INTO t3 VALUES (10,8);
1751
col_varchar_nokey varchar(1) DEFAULT NULL,
1754
INSERT INTO t4 VALUES (1,'x');
1755
EXPLAIN SELECT OUTR.pk, OUTR.col_varchar_nokey, OUTR2.col_varchar_nokey
1758
ON (OUTR2.col_varchar_nokey > OUTR.col_varchar_nokey)
1760
OUTR.col_varchar_nokey IN (
1761
SELECT INNR.col_varchar_nokey
1763
LEFT JOIN t1 AS INNR
1764
ON (INNR2.col_int_key >= INNR.pk)
1768
id select_type table type possible_keys key key_len ref rows Extra
1769
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1770
2 DEPENDENT SUBQUERY INNR2 system NULL NULL NULL NULL 1 NULL
1771
2 DEPENDENT SUBQUERY INNR system PRIMARY NULL NULL NULL 1 NULL
1773
SELECT OUTR.pk, OUTR.col_varchar_nokey, OUTR2.col_varchar_nokey
1776
ON (OUTR2.col_varchar_nokey > OUTR.col_varchar_nokey)
1778
OUTR.col_varchar_nokey IN (
1779
SELECT INNR.col_varchar_nokey
1781
LEFT JOIN t1 AS INNR
1782
ON (INNR2.col_int_key >= INNR.pk)
1786
pk col_varchar_nokey col_varchar_nokey
1787
SHOW STATUS LIKE "HANDLER_READ%";
1789
Handler_read_first 3
1795
Handler_read_rnd_next 3
1796
DROP TABLE t1,t2,t3,t4;
1798
# Bug#13727407: Assert !item->const_item() || !item->not_null_tables()
1802
KEY col_int_key (col_int_key)
1804
INSERT INTO t1 VALUES (1);
1808
col_datetime_nokey DATETIME,
1809
KEY col_int_key (col_int_key),
1810
KEY col_time_key (col_time_key)
1812
INSERT INTO t2 VALUES
1813
(7,'14:03:03','2001-11-28 00:50:27'), (1,'01:46:09','2007-10-09 19:53:04');
1814
EXPLAIN SELECT col_datetime_nokey AS x
1816
WHERE col_int_key IN (
1817
SELECT STRAIGHT_JOIN col_int_key
1819
) AND outr.col_int_key = 0
1820
HAVING x = '2000-09-09'
1821
ORDER BY col_time_key;
1822
id select_type table type possible_keys key key_len ref rows Extra
1823
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1824
2 DEPENDENT SUBQUERY t1 system col_int_key NULL NULL NULL 1 NULL
1825
SELECT col_datetime_nokey AS x
1827
WHERE col_int_key IN (
1828
SELECT STRAIGHT_JOIN col_int_key
1830
) AND outr.col_int_key = 0
1831
HAVING x = '2000-09-09'
1832
ORDER BY col_time_key;
1836
# Bug#13838501 ASSERTION `TABLE->FILE->INITED' FAILED IN
1837
# SUBSELECT_HASH_SJ_ENGINE::EXEC
1840
(c1 bigint,c2 char,pk INT,c3 char,c4 int,c5 INT,key (c5))
1842
INSERT INTO t1 VALUES (763078661862588416,0,1,'',1,'');
1844
Warning 1366 Incorrect integer value: '' for column 'c5' at row 1
1845
CREATE TABLE t2 (c4k int,c4 int,cminnuk INT,key (cminnuk)) ENGINE=InnoDB;
1846
INSERT INTO t2 VALUES(0,'','');
1848
Warning 1366 Incorrect integer value: '' for column 'c4' at row 1
1849
Warning 1366 Incorrect integer value: '' for column 'cminnuk' at row 1
1851
(c4 int,pk INT,c1 bigint,cyk year,cy year,key (cyk))
1853
INSERT INTO t3 VALUES(0,8,'',0,'');
1855
Warning 1366 Incorrect integer value: '' for column 'c1' at row 1
1856
Warning 1366 Incorrect integer value: '' for column 'cy' at row 1
1857
EXPLAIN SELECT o.c2 AS x FROM t1 AS o
1859
(SELECT innr.c4 AS y
1860
FROM t2 AS innr2 JOIN t3 AS innr
1861
ON (innr2.c4k=innr.c4)
1862
WHERE innr.c1=6 OR NOT innr.c1=innr.pk
1864
AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
1865
id select_type table type possible_keys key key_len ref rows Extra
1866
1 PRIMARY o ALL NULL NULL NULL NULL 1 Using where; Using filesort
1867
2 SUBQUERY innr2 ALL NULL NULL NULL NULL 1 NULL
1868
2 SUBQUERY innr ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
1869
SELECT o.c2 AS x FROM t1 AS o
1871
(SELECT innr.c4 AS y
1872
FROM t2 AS innr2 JOIN t3 AS innr
1873
ON (innr2.c4k=innr.c4)
1874
WHERE innr.c1=6 OR NOT innr.c1=innr.pk
1876
AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
1878
DROP TABLE t1,t2,t3;
1880
set @@optimizer_switch=@old_opt_switch;
1881
set optimizer_switch=default;
added
removed
mysql-test/r/subquery_mat_all.result
