1
drop table if exists t1, t2, t3, t1i, t2i, t3i;
2
create table t1 (a1 char(8), a2 char(8));
3
create table t2 (b1 char(8), b2 char(8));
4
create table t3 (c1 char(8), c2 char(8));
5
insert into t1 values ('1 - 00', '2 - 00');
6
insert into t1 values ('1 - 01', '2 - 01');
7
insert into t1 values ('1 - 02', '2 - 02');
8
insert into t2 values ('1 - 01', '2 - 01');
9
insert into t2 values ('1 - 01', '2 - 01');
10
insert into t2 values ('1 - 02', '2 - 02');
11
insert into t2 values ('1 - 02', '2 - 02');
12
insert into t2 values ('1 - 03', '2 - 03');
13
insert into t3 values ('1 - 01', '2 - 01');
14
insert into t3 values ('1 - 02', '2 - 02');
15
insert into t3 values ('1 - 03', '2 - 03');
16
insert into t3 values ('1 - 04', '2 - 04');
17
create table t1i (a1 char(8), a2 char(8));
18
create table t2i (b1 char(8), b2 char(8));
19
create table t3i (c1 char(8), c2 char(8));
20
create index it1i1 on t1i (a1);
21
create index it1i2 on t1i (a2);
22
create index it1i3 on t1i (a1, a2);
23
create index it2i1 on t2i (b1);
24
create index it2i2 on t2i (b2);
25
create index it2i3 on t2i (b1, b2);
26
create index it3i1 on t3i (c1);
27
create index it3i2 on t3i (c2);
28
create index it3i3 on t3i (c1, c2);
29
insert into t1i select * from t1;
30
insert into t2i select * from t2;
31
insert into t3i select * from t3;
32
set @@optimizer_switch=no_semijoin;
33
/******************************************************************************
35
******************************************************************************/
36
# non-indexed nullable fields
38
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
39
id select_type table type possible_keys key key_len ref rows filtered Extra
40
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
41
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
43
Note 1003 select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>("test"."t1"."a1","test"."t1"."a1" in ( <materialize> (select "test"."t2"."b1" AS "b1" from "test"."t2" where ("test"."t2"."b1" > '0') ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
44
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
49
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
50
id select_type table type possible_keys key key_len ref rows filtered Extra
51
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
52
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
54
Note 1003 select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>("test"."t1"."a1","test"."t1"."a1" in ( <materialize> (select "test"."t2"."b1" AS "b1" from "test"."t2" where ("test"."t2"."b1" > '0') group by "test"."t2"."b1" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
55
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
60
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
61
id select_type table type possible_keys key key_len ref rows filtered Extra
62
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
63
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
65
Note 1003 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 "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where ("test"."t2"."b1" > '0') group by "test"."t2"."b1","test"."t2"."b2" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
66
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
71
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
72
id select_type table type possible_keys key key_len ref rows filtered Extra
73
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
74
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
76
Note 1003 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 "test"."t2"."b1" AS "b1",min("test"."t2"."b2") AS "min(b2)" from "test"."t2" where ("test"."t2"."b1" > '0') group by "test"."t2"."b1" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
77
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
82
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
83
id select_type table type possible_keys key key_len ref rows filtered Extra
84
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
85
2 SUBQUERY t2i index it2i1,it2i3 it2i1 9 NULL 5 100.00 Using where; Using index
87
Note 1003 select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where <in_optimizer>("test"."t1i"."a1","test"."t1i"."a1" in ( <materialize> (select "test"."t2i"."b1" AS "b1" from "test"."t2i" where ("test"."t2i"."b1" > '0') ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
88
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
93
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
94
id select_type table type possible_keys key key_len ref rows filtered Extra
95
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
96
2 SUBQUERY t2i range it2i1,it2i3 it2i1 9 NULL 3 100.00 Using where; Using index for group-by
98
Note 1003 select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where <in_optimizer>("test"."t1i"."a1","test"."t1i"."a1" in ( <materialize> (select "test"."t2i"."b1" AS "b1" from "test"."t2i" where ("test"."t2i"."b1" > '0') group by "test"."t2i"."b1" ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
99
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
104
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
105
id select_type table type possible_keys key key_len ref rows filtered Extra
106
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
107
2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
109
Note 1003 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 "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b1" > '0') ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
110
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
115
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
116
id select_type table type possible_keys key key_len ref rows filtered Extra
117
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
118
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
120
Note 1003 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 "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b1" > '0') group by "test"."t2i"."b1","test"."t2i"."b2" ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
121
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
126
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
127
id select_type table type possible_keys key key_len ref rows filtered Extra
128
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
129
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
131
Note 1003 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 "test"."t2i"."b1" AS "b1",min("test"."t2i"."b2") AS "min(b2)" from "test"."t2i" where ("test"."t2i"."b1" > '0') group by "test"."t2i"."b1" ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
132
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
137
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
138
id select_type table type possible_keys key key_len ref rows filtered Extra
139
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
140
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00
142
Note 1003 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 "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" order by "test"."t2"."b1","test"."t2"."b2" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
143
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
148
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
149
id select_type table type possible_keys key key_len ref rows filtered Extra
150
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
151
2 SUBQUERY t2i index NULL it2i3 18 NULL 5 100.00 Using index
153
Note 1003 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 "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" order by "test"."t2i"."b1","test"."t2i"."b2" ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
154
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
160
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and
161
(a1, a2) in (select c1, c2 from t3
162
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
163
id select_type table type possible_keys key key_len ref rows filtered Extra
164
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
165
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
166
4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
167
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
169
Note 1003 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 "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where ("test"."t2"."b1" > '0') ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))
171
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and
172
(a1, a2) in (select c1, c2 from t3
173
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
179
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
180
(a1, a2) in (select c1, c2 from t3i
181
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
182
id select_type table type possible_keys key key_len ref rows filtered Extra
183
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
184
3 SUBQUERY t3i index NULL it3i3 18 NULL 4 100.00 Using where; Using index
185
4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
186
2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
188
Note 1003 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 "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b1" > '0') ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t3i"."c1" AS "c1","test"."t3i"."c2" AS "c2" from "test"."t3i" where <in_optimizer>(("test"."t3i"."c1","test"."t3i"."c2"),("test"."t3i"."c1","test"."t3i"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3i"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key))))
190
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
191
(a1, a2) in (select c1, c2 from t3i
192
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
198
where (a1, a2) in (select b1, b2 from t2
199
where b2 in (select c2 from t3 where c2 LIKE '%02') or
200
b2 in (select c2 from t3 where c2 LIKE '%03')) and
201
(a1, a2) in (select c1, c2 from t3
202
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
203
id select_type table type possible_keys key key_len ref rows filtered Extra
204
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
205
5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
206
6 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
207
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
208
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
209
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
211
Note 1003 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 "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where (<in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3"."c2" AS "c2" from "test"."t3" where ("test"."t3"."c2" like '%02') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key))) or <in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3"."c2" AS "c2" from "test"."t3" where ("test"."t3"."c2" like '%03') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key)))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))
213
where (a1, a2) in (select b1, b2 from t2
214
where b2 in (select c2 from t3 where c2 LIKE '%02') or
215
b2 in (select c2 from t3 where c2 LIKE '%03')) and
216
(a1, a2) in (select c1, c2 from t3
217
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
222
where (a1, a2) in (select b1, b2 from t2
223
where b2 in (select c2 from t3 t3a where c1 = a1) or
224
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
225
(a1, a2) in (select c1, c2 from t3 t3c
226
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
227
id select_type table type possible_keys key key_len ref rows filtered Extra
228
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
229
5 SUBQUERY t3c ALL NULL NULL NULL NULL 4 100.00 Using where
230
6 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
231
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
232
4 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where
233
3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where
235
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
236
Note 1003 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 "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where ((<in_optimizer>("test"."t2"."b2",<exists>(select 1 AS "Not_used" 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 "test"."t3b"."c2" AS "c2" from "test"."t3" "t3b" where ("test"."t3b"."c2" like '%03') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key)))) and (<cache>("test"."t1"."a1") = "test"."t2"."b1") and (<cache>("test"."t1"."a2") = "test"."t2"."b2")))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3c"."c1" AS "c1","test"."t3c"."c2" AS "c2" from "test"."t3" "t3c" where <in_optimizer>(("test"."t3c"."c1","test"."t3c"."c2"),("test"."t3c"."c1","test"."t3c"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3c"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))
238
where (a1, a2) in (select b1, b2 from t2
239
where b2 in (select c2 from t3 t3a where c1 = a1) or
240
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
241
(a1, a2) in (select c1, c2 from t3 t3c
242
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
248
where (a1, a2) in (select b1, b2 from t2
249
where b2 in (select c2 from t3 where c2 LIKE '%02') or
250
b2 in (select c2 from t3 where c2 LIKE '%03')
252
(a1, a2) in (select c1, c2 from t3
253
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
256
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
257
(a1, a2) in (select c1, c2 from t3i
258
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
259
id select_type table type possible_keys key key_len ref rows filtered Extra
260
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
261
5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
262
6 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
263
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
264
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
265
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
266
7 UNION t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
267
9 SUBQUERY t3i index NULL it3i3 18 NULL 4 100.00 Using where; Using index
268
10 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
269
8 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
270
NULL UNION RESULT <union1,7> ALL NULL NULL NULL NULL NULL NULL
272
Note 1003 (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 "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where (<in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3"."c2" AS "c2" from "test"."t3" where ("test"."t3"."c2" like '%02') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key))) or <in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3"."c2" AS "c2" from "test"."t3" where ("test"."t3"."c2" like '%03') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key)))) group by "test"."t2"."b1","test"."t2"."b2" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))) union (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 "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b1" > '0') ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t3i"."c1" AS "c1","test"."t3i"."c2" AS "c2" from "test"."t3i" where <in_optimizer>(("test"."t3i"."c1","test"."t3i"."c2"),("test"."t3i"."c1","test"."t3i"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3i"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))))
274
where (a1, a2) in (select b1, b2 from t2
275
where b2 in (select c2 from t3 where c2 LIKE '%02') or
276
b2 in (select c2 from t3 where c2 LIKE '%03')
278
(a1, a2) in (select c1, c2 from t3
279
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
282
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
283
(a1, a2) in (select c1, c2 from t3i
284
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
290
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
291
(a1, a2) in (select c1, c2 from t3
292
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
293
id select_type table type possible_keys key key_len ref rows filtered Extra
294
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
295
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
296
5 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
297
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
298
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
299
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
301
Note 1003 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 "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" 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 "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" 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 <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))
303
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
304
(a1, a2) in (select c1, c2 from t3
305
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
311
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
312
(c1, c2) in (select c1, c2 from t3
313
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
315
id select_type table type possible_keys key key_len ref rows filtered Extra
316
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
317
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer
318
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
319
5 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
320
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
321
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
322
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
324
Note 1003 select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2","test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t1" join "test"."t3" where (("test"."t3"."c1" = "test"."t1"."a1") and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),<exists>(select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" 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 "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" 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 <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))))
326
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
327
(c1, c2) in (select c1, c2 from t3
328
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
331
1 - 01 2 - 01 1 - 01 2 - 01
332
1 - 02 2 - 02 1 - 02 2 - 02
333
/******************************************************************************
334
* Negative tests, where materialization should not be applied.
335
******************************************************************************/
336
# UNION in a subquery
339
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
340
id select_type table type possible_keys key key_len ref rows filtered Extra
341
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
342
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
343
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
344
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
346
Note 1003 select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>("test"."t3"."c1",<exists>(select 1 AS "Not_used" from "test"."t1" where (("test"."t1"."a1" > '0') and (<cache>("test"."t3"."c1") = "test"."t1"."a1")) union select 1 AS "Not_used" from "test"."t2" where (("test"."t2"."b1" < '9') and (<cache>("test"."t3"."c1") = "test"."t2"."b1"))))
348
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
355
where (a1, a2) in (select b1, b2 from t2
356
where b2 in (select c2 from t3 t3a where c1 = a1) or
357
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
358
(a1, a2) in (select c1, c2 from t3 t3c
359
where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
360
id select_type table type possible_keys key key_len ref rows filtered Extra
361
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
362
5 DEPENDENT SUBQUERY t3c ALL NULL NULL NULL NULL 4 100.00 Using where
363
6 DEPENDENT SUBQUERY t2i index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using index; Using where
364
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
365
4 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where
366
3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where
368
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
369
Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1
370
Note 1003 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 "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where ((<in_optimizer>("test"."t2"."b2",<exists>(select 1 AS "Not_used" 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 "test"."t3b"."c2" AS "c2" from "test"."t3" "t3b" where ("test"."t3b"."c2" like '%03') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key)))) and (<cache>("test"."t1"."a1") = "test"."t2"."b1") and (<cache>("test"."t1"."a2") = "test"."t2"."b2")))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),<exists>(select "test"."t3c"."c1" AS "c1","test"."t3c"."c2" AS "c2" from "test"."t3" "t3c" where (<in_optimizer>(("test"."t3c"."c1","test"."t3c"."c2"),<exists>(<index_lookup>(<cache>("test"."t3c"."c1") in t2i on it2i3 where ((("test"."t2i"."b2" > '0') or ("test"."t2i"."b2" = "test"."t1"."a2")) and (<cache>("test"."t3c"."c1") = "test"."t2i"."b1") and (<cache>("test"."t3c"."c2") = "test"."t2i"."b2"))))) and (<cache>("test"."t1"."a1") = "test"."t3c"."c1") and (<cache>("test"."t1"."a2") = "test"."t3c"."c2")))))
372
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
373
id select_type table type possible_keys key key_len ref rows filtered Extra
374
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
375
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
377
Note 1003 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 '1 - 01' AS "1 - 01",'2 - 01' AS "2 - 01" having ((<cache>("test"."t1"."a1") = '1 - 01') and (<cache>("test"."t1"."a2") = '2 - 01'))))
378
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
382
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
383
id select_type table type possible_keys key key_len ref rows filtered Extra
384
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
385
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
387
Note 1003 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 '1 - 01' AS "1 - 01",'2 - 01' AS "2 - 01" having ((<cache>("test"."t1"."a1") = '1 - 01') and (<cache>("test"."t1"."a2") = '2 - 01'))))
388
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
391
/******************************************************************************
392
* Subqueries in other uncovered clauses.
393
******************************************************************************/
395
select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
396
((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL
400
/* GROUP BY clause */
401
create table columns (col int key);
402
insert into columns values (1), (2);
404
select * from t1 group by (select col from columns limit 1);
405
id select_type table type possible_keys key key_len ref rows filtered Extra
406
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
407
2 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index
409
Note 1003 select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" group by (select "test"."columns"."col" AS "col" from "test"."columns" limit 1)
410
select * from t1 group by (select col from columns limit 1);
414
select * from t1 group by (a1 in (select col from columns));
415
id select_type table type possible_keys key key_len ref rows filtered Extra
416
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort
417
2 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key
419
Note 1003 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 trigcond((<cache>("test"."t1"."a1") = "test"."columns"."col")))))
420
select * from t1 group by (a1 in (select col from columns));
423
/* ORDER BY clause */
425
select * from t1 order by (select col from columns limit 1);
426
id select_type table type possible_keys key key_len ref rows filtered Extra
427
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
428
2 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index
430
Note 1003 select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" order by (select "test"."columns"."col" AS "col" from "test"."columns" limit 1)
431
select * from t1 order by (select col from columns limit 1);
436
/******************************************************************************
437
* Column types/sizes that affect materialization.
438
******************************************************************************/
440
Test that BLOBs are not materialized (except when arguments of some functions).
442
# force materialization to be always considered
443
set @@optimizer_switch=no_semijoin;
446
set @suffix_len = @blob_len - @prefix_len;
447
create table t1_16 (a1 blob(16), a2 blob(16));
448
create table t2_16 (b1 blob(16), b2 blob(16));
449
create table t3_16 (c1 blob(16), c2 blob(16));
450
insert into t1_16 values
451
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
452
insert into t1_16 values
453
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
454
insert into t1_16 values
455
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
456
insert into t2_16 values
457
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
458
insert into t2_16 values
459
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
460
insert into t2_16 values
461
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
462
insert into t3_16 values
463
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
464
insert into t3_16 values
465
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
466
insert into t3_16 values
467
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
468
insert into t3_16 values
469
(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
470
explain extended select left(a1,7), left(a2,7)
472
where a1 in (select b1 from t2_16 where b1 > '0');
473
id select_type table type possible_keys key key_len ref rows filtered Extra
474
1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
475
2 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where
477
Note 1003 select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>("test"."t1_16"."a1","test"."t1_16"."a1" in (select 1 AS "Not_used" from "test"."t2_16" where (("test"."t2_16"."b1" > '0') and (<cache>("test"."t1_16"."a1") = "test"."t2_16"."b1"))))
478
select left(a1,7), left(a2,7)
480
where a1 in (select b1 from t2_16 where b1 > '0');
481
left(a1,7) left(a2,7)
484
explain extended select left(a1,7), left(a2,7)
486
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
487
id select_type table type possible_keys key key_len ref rows filtered Extra
488
1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
489
2 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where
491
Note 1003 select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>(("test"."t1_16"."a1","test"."t1_16"."a2"),("test"."t1_16"."a1","test"."t1_16"."a2") in (select "test"."t2_16"."b1" AS "b1","test"."t2_16"."b2" AS "b2" from "test"."t2_16" where (("test"."t2_16"."b1" > '0') and (<cache>("test"."t1_16"."a1") = "test"."t2_16"."b1") and (<cache>("test"."t1_16"."a2") = "test"."t2_16"."b2"))))
492
select left(a1,7), left(a2,7)
494
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
495
left(a1,7) left(a2,7)
498
explain extended select left(a1,7), left(a2,7)
500
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
501
id select_type table type possible_keys key key_len ref rows filtered Extra
502
1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
503
2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where
505
Note 1003 select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>("test"."t1_16"."a1","test"."t1_16"."a1" in ( <materialize> (select substr("test"."t2_16"."b1",1,16) AS "substring(b1,1,16)" from "test"."t2_16" where ("test"."t2_16"."b1" > '0') ), <primary_index_lookup>("test"."t1_16"."a1" in <temporary table> on distinct_key)))
506
select left(a1,7), left(a2,7)
508
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
509
left(a1,7) left(a2,7)
512
explain extended select left(a1,7), left(a2,7)
514
where a1 in (select group_concat(b1) from t2_16 group by b2);
515
id select_type table type possible_keys key key_len ref rows filtered Extra
516
1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
517
2 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort
519
Note 1003 select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>("test"."t1_16"."a1","test"."t1_16"."a1" in (select group_concat("test"."t2_16"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_16" group by "test"."t2_16"."b2" having (<cache>("test"."t1_16"."a1") = <ref_null_helper>(group_concat("test"."t2_16"."b1" separator ',')))))
520
select left(a1,7), left(a2,7)
522
where a1 in (select group_concat(b1) from t2_16 group by b2);
523
left(a1,7) left(a2,7)
526
set @@group_concat_max_len = 256;
527
explain extended select left(a1,7), left(a2,7)
529
where a1 in (select group_concat(b1) from t2_16 group by b2);
530
id select_type table type possible_keys key key_len ref rows filtered Extra
531
1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
532
2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort
534
Note 1003 select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>("test"."t1_16"."a1","test"."t1_16"."a1" in ( <materialize> (select group_concat("test"."t2_16"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_16" group by "test"."t2_16"."b2" ), <primary_index_lookup>("test"."t1_16"."a1" in <temporary table> on distinct_key)))
535
select left(a1,7), left(a2,7)
537
where a1 in (select group_concat(b1) from t2_16 group by b2);
538
left(a1,7) left(a2,7)
543
where concat(a1,'x') IN
544
(select left(a1,8) from t1_16
546
(select t2_16.b1, t2_16.b2 from t2_16, t2
547
where t2.b2 = substring(t2_16.b2,1,6) and
548
t2.b1 IN (select c1 from t3 where c2 > '0')));
549
id select_type table type possible_keys key key_len ref rows filtered Extra
550
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
551
2 SUBQUERY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
552
3 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where
553
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer
554
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
556
Note 1003 select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>(concat("test"."t1"."a1",'x'),concat("test"."t1"."a1",'x') in ( <materialize> (select left("test"."t1_16"."a1",8) AS "left(a1,8)" from "test"."t1_16" where <in_optimizer>(("test"."t1_16"."a1","test"."t1_16"."a2"),("test"."t1_16"."a1","test"."t1_16"."a2") in (select "test"."t2_16"."b1" AS "b1","test"."t2_16"."b2" AS "b2" from "test"."t2_16" join "test"."t2" where (("test"."t2"."b2" = substr("test"."t2_16"."b2",1,6)) and <in_optimizer>("test"."t2"."b1","test"."t2"."b1" in ( <materialize> (select "test"."t3"."c1" AS "c1" from "test"."t3" where ("test"."t3"."c2" > '0') ), <primary_index_lookup>("test"."t2"."b1" in <temporary table> on distinct_key))) and (<cache>("test"."t1_16"."a1") = "test"."t2_16"."b1") and (<cache>("test"."t1_16"."a2") = "test"."t2_16"."b2")))) ), <primary_index_lookup>(concat("test"."t1"."a1",'x') in <temporary table> on distinct_key)))
557
drop table t1_16, t2_16, t3_16;
559
set @suffix_len = @blob_len - @prefix_len;
560
create table t1_512 (a1 blob(512), a2 blob(512));
561
create table t2_512 (b1 blob(512), b2 blob(512));
562
create table t3_512 (c1 blob(512), c2 blob(512));
563
insert into t1_512 values
564
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
565
insert into t1_512 values
566
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
567
insert into t1_512 values
568
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
569
insert into t2_512 values
570
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
571
insert into t2_512 values
572
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
573
insert into t2_512 values
574
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
575
insert into t3_512 values
576
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
577
insert into t3_512 values
578
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
579
insert into t3_512 values
580
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
581
insert into t3_512 values
582
(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
583
explain extended select left(a1,7), left(a2,7)
585
where a1 in (select b1 from t2_512 where b1 > '0');
586
id select_type table type possible_keys key key_len ref rows filtered Extra
587
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
588
2 DEPENDENT SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where
590
Note 1003 select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>("test"."t1_512"."a1","test"."t1_512"."a1" in (select 1 AS "Not_used" from "test"."t2_512" where (("test"."t2_512"."b1" > '0') and (<cache>("test"."t1_512"."a1") = "test"."t2_512"."b1"))))
591
select left(a1,7), left(a2,7)
593
where a1 in (select b1 from t2_512 where b1 > '0');
594
left(a1,7) left(a2,7)
597
explain extended select left(a1,7), left(a2,7)
599
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
600
id select_type table type possible_keys key key_len ref rows filtered Extra
601
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
602
2 DEPENDENT SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where
604
Note 1003 select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>(("test"."t1_512"."a1","test"."t1_512"."a2"),("test"."t1_512"."a1","test"."t1_512"."a2") in (select "test"."t2_512"."b1" AS "b1","test"."t2_512"."b2" AS "b2" from "test"."t2_512" where (("test"."t2_512"."b1" > '0') and (<cache>("test"."t1_512"."a1") = "test"."t2_512"."b1") and (<cache>("test"."t1_512"."a2") = "test"."t2_512"."b2"))))
605
select left(a1,7), left(a2,7)
607
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
608
left(a1,7) left(a2,7)
611
explain extended select left(a1,7), left(a2,7)
613
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
614
id select_type table type possible_keys key key_len ref rows filtered Extra
615
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
616
2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where
618
Note 1003 select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>("test"."t1_512"."a1","test"."t1_512"."a1" in ( <materialize> (select substr("test"."t2_512"."b1",1,512) AS "substring(b1,1,512)" from "test"."t2_512" where ("test"."t2_512"."b1" > '0') ), <primary_index_lookup>("test"."t1_512"."a1" in <temporary table> on distinct_key)))
619
select left(a1,7), left(a2,7)
621
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
622
left(a1,7) left(a2,7)
625
explain extended select left(a1,7), left(a2,7)
627
where a1 in (select group_concat(b1) from t2_512 group by b2);
628
id select_type table type possible_keys key key_len ref rows filtered Extra
629
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
630
2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort
632
Note 1003 select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>("test"."t1_512"."a1","test"."t1_512"."a1" in ( <materialize> (select group_concat("test"."t2_512"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_512" group by "test"."t2_512"."b2" ), <primary_index_lookup>("test"."t1_512"."a1" in <temporary table> on distinct_key)))
633
select left(a1,7), left(a2,7)
635
where a1 in (select group_concat(b1) from t2_512 group by b2);
636
left(a1,7) left(a2,7)
639
set @@group_concat_max_len = 256;
640
explain extended select left(a1,7), left(a2,7)
642
where a1 in (select group_concat(b1) from t2_512 group by b2);
643
id select_type table type possible_keys key key_len ref rows filtered Extra
644
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
645
2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort
647
Note 1003 select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>("test"."t1_512"."a1","test"."t1_512"."a1" in ( <materialize> (select group_concat("test"."t2_512"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_512" group by "test"."t2_512"."b2" ), <primary_index_lookup>("test"."t1_512"."a1" in <temporary table> on distinct_key)))
648
select left(a1,7), left(a2,7)
650
where a1 in (select group_concat(b1) from t2_512 group by b2);
651
left(a1,7) left(a2,7)
654
drop table t1_512, t2_512, t3_512;
655
set @blob_len = 1024;
656
set @suffix_len = @blob_len - @prefix_len;
657
create table t1_1024 (a1 blob(1024), a2 blob(1024));
658
create table t2_1024 (b1 blob(1024), b2 blob(1024));
659
create table t3_1024 (c1 blob(1024), c2 blob(1024));
660
insert into t1_1024 values
661
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
662
insert into t1_1024 values
663
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
664
insert into t1_1024 values
665
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
666
insert into t2_1024 values
667
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
668
insert into t2_1024 values
669
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
670
insert into t2_1024 values
671
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
672
insert into t3_1024 values
673
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
674
insert into t3_1024 values
675
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
676
insert into t3_1024 values
677
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
678
insert into t3_1024 values
679
(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
680
explain extended select left(a1,7), left(a2,7)
682
where a1 in (select b1 from t2_1024 where b1 > '0');
683
id select_type table type possible_keys key key_len ref rows filtered Extra
684
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
685
2 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
687
Note 1003 select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>("test"."t1_1024"."a1","test"."t1_1024"."a1" in (select 1 AS "Not_used" from "test"."t2_1024" where (("test"."t2_1024"."b1" > '0') and (<cache>("test"."t1_1024"."a1") = "test"."t2_1024"."b1"))))
688
select left(a1,7), left(a2,7)
690
where a1 in (select b1 from t2_1024 where b1 > '0');
691
left(a1,7) left(a2,7)
694
explain extended select left(a1,7), left(a2,7)
696
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
697
id select_type table type possible_keys key key_len ref rows filtered Extra
698
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
699
2 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
701
Note 1003 select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>(("test"."t1_1024"."a1","test"."t1_1024"."a2"),("test"."t1_1024"."a1","test"."t1_1024"."a2") in (select "test"."t2_1024"."b1" AS "b1","test"."t2_1024"."b2" AS "b2" from "test"."t2_1024" where (("test"."t2_1024"."b1" > '0') and (<cache>("test"."t1_1024"."a1") = "test"."t2_1024"."b1") and (<cache>("test"."t1_1024"."a2") = "test"."t2_1024"."b2"))))
702
select left(a1,7), left(a2,7)
704
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
705
left(a1,7) left(a2,7)
708
explain extended select left(a1,7), left(a2,7)
710
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
711
id select_type table type possible_keys key key_len ref rows filtered Extra
712
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
713
2 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
715
Note 1003 select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>("test"."t1_1024"."a1","test"."t1_1024"."a1" in (select 1 AS "Not_used" from "test"."t2_1024" where (("test"."t2_1024"."b1" > '0') and (<cache>("test"."t1_1024"."a1") = substr("test"."t2_1024"."b1",1,1024)))))
716
select left(a1,7), left(a2,7)
718
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
719
left(a1,7) left(a2,7)
722
explain extended select left(a1,7), left(a2,7)
724
where a1 in (select group_concat(b1) from t2_1024 group by b2);
725
id select_type table type possible_keys key key_len ref rows filtered Extra
726
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
727
2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort
729
Note 1003 select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>("test"."t1_1024"."a1","test"."t1_1024"."a1" in ( <materialize> (select group_concat("test"."t2_1024"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_1024" group by "test"."t2_1024"."b2" ), <primary_index_lookup>("test"."t1_1024"."a1" in <temporary table> on distinct_key)))
730
select left(a1,7), left(a2,7)
732
where a1 in (select group_concat(b1) from t2_1024 group by b2);
733
left(a1,7) left(a2,7)
736
set @@group_concat_max_len = 256;
737
explain extended select left(a1,7), left(a2,7)
739
where a1 in (select group_concat(b1) from t2_1024 group by b2);
740
id select_type table type possible_keys key key_len ref rows filtered Extra
741
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
742
2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort
744
Note 1003 select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>("test"."t1_1024"."a1","test"."t1_1024"."a1" in ( <materialize> (select group_concat("test"."t2_1024"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_1024" group by "test"."t2_1024"."b2" ), <primary_index_lookup>("test"."t1_1024"."a1" in <temporary table> on distinct_key)))
745
select left(a1,7), left(a2,7)
747
where a1 in (select group_concat(b1) from t2_1024 group by b2);
748
left(a1,7) left(a2,7)
751
drop table t1_1024, t2_1024, t3_1024;
752
set @blob_len = 1025;
753
set @suffix_len = @blob_len - @prefix_len;
754
create table t1_1025 (a1 blob(1025), a2 blob(1025));
755
create table t2_1025 (b1 blob(1025), b2 blob(1025));
756
create table t3_1025 (c1 blob(1025), c2 blob(1025));
757
insert into t1_1025 values
758
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
759
insert into t1_1025 values
760
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
761
insert into t1_1025 values
762
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
763
insert into t2_1025 values
764
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
765
insert into t2_1025 values
766
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
767
insert into t2_1025 values
768
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
769
insert into t3_1025 values
770
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
771
insert into t3_1025 values
772
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
773
insert into t3_1025 values
774
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
775
insert into t3_1025 values
776
(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
777
explain extended select left(a1,7), left(a2,7)
779
where a1 in (select b1 from t2_1025 where b1 > '0');
780
id select_type table type possible_keys key key_len ref rows filtered Extra
781
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
782
2 DEPENDENT SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
784
Note 1003 select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>("test"."t1_1025"."a1","test"."t1_1025"."a1" in (select 1 AS "Not_used" from "test"."t2_1025" where (("test"."t2_1025"."b1" > '0') and (<cache>("test"."t1_1025"."a1") = "test"."t2_1025"."b1"))))
785
select left(a1,7), left(a2,7)
787
where a1 in (select b1 from t2_1025 where b1 > '0');
788
left(a1,7) left(a2,7)
791
explain extended select left(a1,7), left(a2,7)
793
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
794
id select_type table type possible_keys key key_len ref rows filtered Extra
795
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
796
2 DEPENDENT SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
798
Note 1003 select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>(("test"."t1_1025"."a1","test"."t1_1025"."a2"),("test"."t1_1025"."a1","test"."t1_1025"."a2") in (select "test"."t2_1025"."b1" AS "b1","test"."t2_1025"."b2" AS "b2" from "test"."t2_1025" where (("test"."t2_1025"."b1" > '0') and (<cache>("test"."t1_1025"."a1") = "test"."t2_1025"."b1") and (<cache>("test"."t1_1025"."a2") = "test"."t2_1025"."b2"))))
799
select left(a1,7), left(a2,7)
801
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
802
left(a1,7) left(a2,7)
805
explain extended select left(a1,7), left(a2,7)
807
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
808
id select_type table type possible_keys key key_len ref rows filtered Extra
809
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
810
2 DEPENDENT SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
812
Note 1003 select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>("test"."t1_1025"."a1","test"."t1_1025"."a1" in (select 1 AS "Not_used" from "test"."t2_1025" where (("test"."t2_1025"."b1" > '0') and (<cache>("test"."t1_1025"."a1") = substr("test"."t2_1025"."b1",1,1025)))))
813
select left(a1,7), left(a2,7)
815
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
816
left(a1,7) left(a2,7)
819
explain extended select left(a1,7), left(a2,7)
821
where a1 in (select group_concat(b1) from t2_1025 group by b2);
822
id select_type table type possible_keys key key_len ref rows filtered Extra
823
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
824
2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort
826
Note 1003 select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>("test"."t1_1025"."a1","test"."t1_1025"."a1" in ( <materialize> (select group_concat("test"."t2_1025"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_1025" group by "test"."t2_1025"."b2" ), <primary_index_lookup>("test"."t1_1025"."a1" in <temporary table> on distinct_key)))
827
select left(a1,7), left(a2,7)
829
where a1 in (select group_concat(b1) from t2_1025 group by b2);
830
left(a1,7) left(a2,7)
833
set @@group_concat_max_len = 256;
834
explain extended select left(a1,7), left(a2,7)
836
where a1 in (select group_concat(b1) from t2_1025 group by b2);
837
id select_type table type possible_keys key key_len ref rows filtered Extra
838
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
839
2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort
841
Note 1003 select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>("test"."t1_1025"."a1","test"."t1_1025"."a1" in ( <materialize> (select group_concat("test"."t2_1025"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_1025" group by "test"."t2_1025"."b2" ), <primary_index_lookup>("test"."t1_1025"."a1" in <temporary table> on distinct_key)))
842
select left(a1,7), left(a2,7)
844
where a1 in (select group_concat(b1) from t2_1025 group by b2);
845
left(a1,7) left(a2,7)
848
drop table t1_1025, t2_1025, t3_1025;
849
create table t1bit (a1 bit(3), a2 bit(3));
850
create table t2bit (b1 bit(3), b2 bit(3));
851
insert into t1bit values (b'000', b'100');
852
insert into t1bit values (b'001', b'101');
853
insert into t1bit values (b'010', b'110');
854
insert into t2bit values (b'001', b'101');
855
insert into t2bit values (b'010', b'110');
856
insert into t2bit values (b'110', b'111');
857
set @@optimizer_switch=no_semijoin;
858
explain extended select bin(a1), bin(a2)
860
where (a1, a2) in (select b1, b2 from t2bit);
861
id select_type table type possible_keys key key_len ref rows filtered Extra
862
1 PRIMARY t1bit ALL NULL NULL NULL NULL 3 100.00 Using where
863
2 SUBQUERY t2bit ALL NULL NULL NULL NULL 3 100.00
865
Note 1003 select conv("test"."t1bit"."a1",10,2) AS "bin(a1)",conv("test"."t1bit"."a2",10,2) AS "bin(a2)" from "test"."t1bit" where <in_optimizer>(("test"."t1bit"."a1","test"."t1bit"."a2"),("test"."t1bit"."a1","test"."t1bit"."a2") in ( <materialize> (select "test"."t2bit"."b1" AS "b1","test"."t2bit"."b2" AS "b2" from "test"."t2bit" ), <primary_index_lookup>("test"."t1bit"."a1" in <temporary table> on distinct_key)))
866
select bin(a1), bin(a2)
868
where (a1, a2) in (select b1, b2 from t2bit);
872
drop table t1bit, t2bit;
873
create table t1bb (a1 bit(3), a2 blob(3));
874
create table t2bb (b1 bit(3), b2 blob(3));
875
insert into t1bb values (b'000', '100');
876
insert into t1bb values (b'001', '101');
877
insert into t1bb values (b'010', '110');
878
insert into t2bb values (b'001', '101');
879
insert into t2bb values (b'010', '110');
880
insert into t2bb values (b'110', '111');
881
explain extended select bin(a1), a2
883
where (a1, a2) in (select b1, b2 from t2bb);
884
id select_type table type possible_keys key key_len ref rows filtered Extra
885
1 PRIMARY t1bb ALL NULL NULL NULL NULL 3 100.00 Using where
886
2 DEPENDENT SUBQUERY t2bb ALL NULL NULL NULL NULL 3 100.00 Using where
888
Note 1003 select conv("test"."t1bb"."a1",10,2) AS "bin(a1)","test"."t1bb"."a2" AS "a2" from "test"."t1bb" where <in_optimizer>(("test"."t1bb"."a1","test"."t1bb"."a2"),("test"."t1bb"."a1","test"."t1bb"."a2") in (select "test"."t2bb"."b1" AS "b1","test"."t2bb"."b2" AS "b2" from "test"."t2bb" where ((<cache>("test"."t1bb"."a1") = "test"."t2bb"."b1") and (<cache>("test"."t1bb"."a2") = "test"."t2bb"."b2"))))
891
where (a1, a2) in (select b1, b2 from t2bb);
895
drop table t1bb, t2bb;
896
drop table t1, t2, t3, t1i, t2i, t3i, columns;
897
/******************************************************************************
898
* Test the cache of the left operand of IN.
899
******************************************************************************/
900
set @@optimizer_switch=no_semijoin;
901
create table t1 (s1 int);
902
create table t2 (s2 int);
903
insert into t1 values (5),(1),(0);
904
insert into t2 values (0), (1);
905
select s2 from t2 where s2 in (select s1 from t1);
910
create table t1 (a int not null, b int not null);
911
create table t2 (c int not null, d int not null);
912
create table t3 (e int not null);
913
insert into t1 values (1,10);
914
insert into t1 values (1,20);
915
insert into t1 values (2,10);
916
insert into t1 values (2,20);
917
insert into t1 values (2,30);
918
insert into t1 values (3,20);
919
insert into t1 values (4,40);
920
insert into t2 values (2,10);
921
insert into t2 values (2,20);
922
insert into t2 values (2,40);
923
insert into t2 values (3,20);
924
insert into t2 values (4,10);
925
insert into t2 values (5,10);
926
insert into t3 values (10);
927
insert into t3 values (10);
928
insert into t3 values (20);
929
insert into t3 values (30);
931
select a from t1 where a in (select c from t2 where d >= 20);
932
id select_type table type possible_keys key key_len ref rows filtered Extra
933
1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where
934
2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
936
Note 1003 select "test"."t1"."a" AS "a" from "test"."t1" where <in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (select "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
937
select a from t1 where a in (select c from t2 where d >= 20);
943
create index it1a on t1(a);
945
select a from t1 where a in (select c from t2 where d >= 20);
946
id select_type table type possible_keys key key_len ref rows filtered Extra
947
1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index
948
2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
950
Note 1003 select "test"."t1"."a" AS "a" from "test"."t1" where <in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (select "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
951
select a from t1 where a in (select c from t2 where d >= 20);
957
insert into t2 values (1,10);
959
select a from t1 where a in (select c from t2 where d >= 20);
960
id select_type table type possible_keys key key_len ref rows filtered Extra
961
1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index
962
2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
964
Note 1003 select "test"."t1"."a" AS "a" from "test"."t1" where <in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (select "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
965
select a from t1 where a in (select c from t2 where d >= 20);
972
select a from t1 group by a having a in (select c from t2 where d >= 20);
973
id select_type table type possible_keys key key_len ref rows filtered Extra
974
1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index
975
2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
977
Note 1003 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 "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
978
select a from t1 group by a having a in (select c from t2 where d >= 20);
982
create index iab on t1(a, b);
984
select a from t1 group by a having a in (select c from t2 where d >= 20);
985
id select_type table type possible_keys key key_len ref rows filtered Extra
986
1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index
987
2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
989
Note 1003 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 "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
990
select a from t1 group by a having a in (select c from t2 where d >= 20);
995
select a from t1 group by a
996
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
997
id select_type table type possible_keys key key_len ref rows filtered Extra
998
1 PRIMARY t1 index NULL iab 8 NULL 7 100.00 Using index
999
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
1000
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
1002
Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
1003
Note 1003 select "test"."t1"."a" AS "a" from "test"."t1" group by "test"."t1"."a" having <in_optimizer>("test"."t1"."a",<exists>(select 1 AS "Not_used" from "test"."t2" where (<nop>(<in_optimizer>("test"."t2"."d",<exists>(select "test"."t3"."e" AS "e" 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"))))
1004
select a from t1 group by a
1005
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
1011
where a in (select c from t2 where d >= some(select e from t3 where b=e));
1012
id select_type table type possible_keys key key_len ref rows filtered Extra
1013
1 PRIMARY t1 index NULL iab 8 NULL 7 100.00 Using where; Using index
1014
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
1015
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
1017
Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
1018
Note 1003 select "test"."t1"."a" AS "a" from "test"."t1" where <in_optimizer>("test"."t1"."a",<exists>(select 1 AS "Not_used" from "test"."t2" where (<nop>(<in_optimizer>("test"."t2"."d",<exists>(select 1 AS "Not_used" from "test"."t3" where (("test"."t1"."b" = "test"."t3"."e") and (<cache>("test"."t2"."d") >= "test"."t3"."e"))))) and (<cache>("test"."t1"."a") = "test"."t2"."c"))))
1020
where a in (select c from t2 where d >= some(select e from t3 where b=e));
1027
drop table t1, t2, t3;
added
removed
mysql-test/r/subselect_mat.result
