|
1
by brian
clean slate |
1 |
#
|
2 |
# Hash semi-join regression tests |
|
3 |
# (WL#1110: Subquery optimization: materialization) |
|
4 |
#
|
|
5 |
||
6 |
--disable_warnings
|
|
7 |
drop table if exists t1, t2, t3, t1i, t2i, t3i; |
|
8 |
--enable_warnings
|
|
9 |
||
10 |
create table t1 (a1 char(8), a2 char(8)); |
|
11 |
create table t2 (b1 char(8), b2 char(8)); |
|
12 |
create table t3 (c1 char(8), c2 char(8)); |
|
13 |
||
14 |
insert into t1 values ('1 - 00', '2 - 00'); |
|
15 |
insert into t1 values ('1 - 01', '2 - 01'); |
|
16 |
insert into t1 values ('1 - 02', '2 - 02'); |
|
17 |
||
18 |
insert into t2 values ('1 - 01', '2 - 01'); |
|
19 |
insert into t2 values ('1 - 01', '2 - 01'); |
|
20 |
insert into t2 values ('1 - 02', '2 - 02'); |
|
21 |
insert into t2 values ('1 - 02', '2 - 02'); |
|
22 |
insert into t2 values ('1 - 03', '2 - 03'); |
|
23 |
||
24 |
insert into t3 values ('1 - 01', '2 - 01'); |
|
25 |
insert into t3 values ('1 - 02', '2 - 02'); |
|
26 |
insert into t3 values ('1 - 03', '2 - 03'); |
|
27 |
insert into t3 values ('1 - 04', '2 - 04'); |
|
28 |
||
29 |
# Indexed columns |
|
30 |
create table t1i (a1 char(8), a2 char(8)); |
|
31 |
create table t2i (b1 char(8), b2 char(8)); |
|
32 |
create table t3i (c1 char(8), c2 char(8)); |
|
33 |
create index it1i1 on t1i (a1); |
|
34 |
create index it1i2 on t1i (a2); |
|
35 |
create index it1i3 on t1i (a1, a2); |
|
36 |
||
37 |
create index it2i1 on t2i (b1); |
|
38 |
create index it2i2 on t2i (b2); |
|
39 |
create index it2i3 on t2i (b1, b2); |
|
40 |
||
41 |
create index it3i1 on t3i (c1); |
|
42 |
create index it3i2 on t3i (c2); |
|
43 |
create index it3i3 on t3i (c1, c2); |
|
44 |
||
45 |
insert into t1i select * from t1; |
|
46 |
insert into t2i select * from t2; |
|
47 |
insert into t3i select * from t3; |
|
48 |
||
49 |
# force the use of materialization |
|
50 |
set @@optimizer_switch=no_semijoin; |
|
51 |
||
52 |
/******************************************************************************
|
|
53 |
* Simple tests.
|
|
54 |
******************************************************************************/
|
|
55 |
# non-indexed nullable fields |
|
56 |
explain extended |
|
57 |
select * from t1 where a1 in (select b1 from t2 where b1 > '0'); |
|
58 |
select * from t1 where a1 in (select b1 from t2 where b1 > '0'); |
|
59 |
||
60 |
explain extended |
|
61 |
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); |
|
62 |
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); |
|
63 |
||
64 |
explain extended |
|
65 |
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); |
|
66 |
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); |
|
67 |
||
68 |
explain extended |
|
69 |
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); |
|
70 |
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); |
|
71 |
||
72 |
# indexed columns |
|
73 |
explain extended |
|
74 |
select * from t1i where a1 in (select b1 from t2i where b1 > '0'); |
|
75 |
select * from t1i where a1 in (select b1 from t2i where b1 > '0'); |
|
76 |
||
77 |
explain extended |
|
78 |
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); |
|
79 |
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); |
|
80 |
||
81 |
explain extended |
|
82 |
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); |
|
83 |
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); |
|
84 |
||
85 |
explain extended |
|
86 |
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); |
|
87 |
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); |
|
88 |
||
89 |
explain extended |
|
90 |
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); |
|
91 |
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); |
|
92 |
||
93 |
# materialize the result of ORDER BY |
|
94 |
# non-indexed fields |
|
95 |
explain extended |
|
96 |
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); |
|
97 |
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); |
|
98 |
# indexed fields |
|
99 |
explain extended |
|
100 |
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); |
|
101 |
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); |
|
102 |
||
103 |
# nested subqueries, views |
|
104 |
explain extended |
|
105 |
select * from t1 |
|
106 |
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and |
|
107 |
(a1, a2) in (select c1, c2 from t3 |
|
108 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
109 |
select * from t1 |
|
110 |
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and |
|
111 |
(a1, a2) in (select c1, c2 from t3 |
|
112 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
113 |
||
114 |
explain extended |
|
115 |
select * from t1i |
|
116 |
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and |
|
117 |
(a1, a2) in (select c1, c2 from t3i |
|
118 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
119 |
select * from t1i |
|
120 |
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and |
|
121 |
(a1, a2) in (select c1, c2 from t3i |
|
122 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
123 |
||
124 |
explain extended |
|
125 |
select * from t1 |
|
126 |
where (a1, a2) in (select b1, b2 from t2 |
|
127 |
where b2 in (select c2 from t3 where c2 LIKE '%02') or |
|
128 |
b2 in (select c2 from t3 where c2 LIKE '%03')) and |
|
129 |
(a1, a2) in (select c1, c2 from t3 |
|
130 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
131 |
select * from t1 |
|
132 |
where (a1, a2) in (select b1, b2 from t2 |
|
133 |
where b2 in (select c2 from t3 where c2 LIKE '%02') or |
|
134 |
b2 in (select c2 from t3 where c2 LIKE '%03')) and |
|
135 |
(a1, a2) in (select c1, c2 from t3 |
|
136 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
137 |
||
138 |
# as above with correlated innermost subquery |
|
139 |
explain extended |
|
140 |
select * from t1 |
|
141 |
where (a1, a2) in (select b1, b2 from t2 |
|
142 |
where b2 in (select c2 from t3 t3a where c1 = a1) or |
|
143 |
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and |
|
144 |
(a1, a2) in (select c1, c2 from t3 t3c |
|
145 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
146 |
select * from t1 |
|
147 |
where (a1, a2) in (select b1, b2 from t2 |
|
148 |
where b2 in (select c2 from t3 t3a where c1 = a1) or |
|
149 |
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and |
|
150 |
(a1, a2) in (select c1, c2 from t3 t3c |
|
151 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
152 |
||
153 |
||
154 |
# multiple levels of nesting subqueries, unions |
|
155 |
explain extended |
|
156 |
(select * from t1 |
|
157 |
where (a1, a2) in (select b1, b2 from t2 |
|
158 |
where b2 in (select c2 from t3 where c2 LIKE '%02') or |
|
159 |
b2 in (select c2 from t3 where c2 LIKE '%03') |
|
160 |
group by b1, b2) and |
|
161 |
(a1, a2) in (select c1, c2 from t3 |
|
162 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) |
|
163 |
UNION
|
|
164 |
(select * from t1i |
|
165 |
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and |
|
166 |
(a1, a2) in (select c1, c2 from t3i |
|
167 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); |
|
168 |
||
169 |
(select * from t1 |
|
170 |
where (a1, a2) in (select b1, b2 from t2 |
|
171 |
where b2 in (select c2 from t3 where c2 LIKE '%02') or |
|
172 |
b2 in (select c2 from t3 where c2 LIKE '%03') |
|
173 |
group by b1, b2) and |
|
174 |
(a1, a2) in (select c1, c2 from t3 |
|
175 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) |
|
176 |
UNION
|
|
177 |
(select * from t1i |
|
178 |
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and |
|
179 |
(a1, a2) in (select c1, c2 from t3i |
|
180 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); |
|
181 |
||
182 |
||
183 |
# UNION of subqueries as a subquery (thus it is not computed via materialization) |
|
184 |
explain extended |
|
185 |
select * from t1 |
|
186 |
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and |
|
187 |
(a1, a2) in (select c1, c2 from t3 |
|
188 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
189 |
select * from t1 |
|
190 |
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and |
|
191 |
(a1, a2) in (select c1, c2 from t3 |
|
192 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); |
|
193 |
# as above, with a join conditon between the outer references |
|
194 |
explain extended |
|
195 |
select * from t1, t3 |
|
196 |
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and |
|
197 |
(c1, c2) in (select c1, c2 from t3 |
|
198 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and |
|
199 |
a1 = c1; |
|
200 |
select * from t1, t3 |
|
201 |
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and |
|
202 |
(c1, c2) in (select c1, c2 from t3 |
|
203 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and |
|
204 |
a1 = c1; |
|
205 |
||
206 |
||
207 |
/******************************************************************************
|
|
208 |
* Negative tests, where materialization should not be applied.
|
|
209 |
******************************************************************************/
|
|
210 |
# UNION in a subquery |
|
211 |
explain extended |
|
212 |
select * from t3 |
|
213 |
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); |
|
214 |
select * from t3 |
|
215 |
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); |
|
216 |
||
217 |
# correlation |
|
218 |
explain extended |
|
219 |
select * from t1 |
|
220 |
where (a1, a2) in (select b1, b2 from t2 |
|
221 |
where b2 in (select c2 from t3 t3a where c1 = a1) or |
|
222 |
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and |
|
223 |
(a1, a2) in (select c1, c2 from t3 t3c |
|
224 |
where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); |
|
225 |
||
226 |
# subquery has no tables |
|
227 |
explain extended |
|
228 |
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); |
|
229 |
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); |
|
230 |
explain extended |
|
231 |
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); |
|
232 |
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); |
|
233 |
||
234 |
||
235 |
/******************************************************************************
|
|
236 |
* Subqueries in other uncovered clauses.
|
|
237 |
******************************************************************************/
|
|
238 |
||
239 |
/* SELECT clause */
|
|
240 |
select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1; |
|
241 |
||
242 |
/* GROUP BY clause */
|
|
243 |
create table columns (col int key); |
|
244 |
insert into columns values (1), (2); |
|
245 |
||
246 |
explain extended |
|
247 |
select * from t1 group by (select col from columns limit 1); |
|
248 |
select * from t1 group by (select col from columns limit 1); |
|
249 |
||
250 |
explain extended |
|
251 |
select * from t1 group by (a1 in (select col from columns)); |
|
252 |
select * from t1 group by (a1 in (select col from columns)); |
|
253 |
||
254 |
/* ORDER BY clause */
|
|
255 |
explain extended |
|
256 |
select * from t1 order by (select col from columns limit 1); |
|
257 |
select * from t1 order by (select col from columns limit 1); |
|
258 |
||
259 |
/******************************************************************************
|
|
260 |
* Column types/sizes that affect materialization.
|
|
261 |
******************************************************************************/
|
|
262 |
||
263 |
/*
|
|
264 |
Test that BLOBs are not materialized (except when arguments of some functions).
|
|
265 |
*/
|
|
266 |
# force materialization to be always considered |
|
267 |
set @@optimizer_switch=no_semijoin; |
|
268 |
set @prefix_len = 6; |
|
269 |
||
270 |
# BLOB == 16 (small blobs that could be stored in HEAP tables) |
|
271 |
set @blob_len = 16; |
|
272 |
set @suffix_len = @blob_len - @prefix_len; |
|
273 |
||
274 |
create table t1_16 (a1 blob(16), a2 blob(16)); |
|
275 |
create table t2_16 (b1 blob(16), b2 blob(16)); |
|
276 |
create table t3_16 (c1 blob(16), c2 blob(16)); |
|
277 |
||
278 |
insert into t1_16 values |
|
279 |
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); |
|
280 |
insert into t1_16 values |
|
281 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
282 |
insert into t1_16 values |
|
283 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
284 |
||
285 |
insert into t2_16 values |
|
286 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
287 |
insert into t2_16 values |
|
288 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
289 |
insert into t2_16 values |
|
290 |
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); |
|
291 |
||
292 |
insert into t3_16 values |
|
293 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
294 |
insert into t3_16 values |
|
295 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
296 |
insert into t3_16 values |
|
297 |
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); |
|
298 |
insert into t3_16 values |
|
299 |
(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); |
|
300 |
||
301 |
# single value transformer |
|
302 |
explain extended select left(a1,7), left(a2,7) |
|
303 |
from t1_16 |
|
304 |
where a1 in (select b1 from t2_16 where b1 > '0'); |
|
305 |
||
306 |
select left(a1,7), left(a2,7) |
|
307 |
from t1_16 |
|
308 |
where a1 in (select b1 from t2_16 where b1 > '0'); |
|
309 |
||
310 |
# row value transformer |
|
311 |
explain extended select left(a1,7), left(a2,7) |
|
312 |
from t1_16 |
|
313 |
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0'); |
|
314 |
||
315 |
select left(a1,7), left(a2,7) |
|
316 |
from t1_16 |
|
317 |
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0'); |
|
318 |
||
319 |
# string function with a blob argument, the return type may be != blob |
|
320 |
explain extended select left(a1,7), left(a2,7) |
|
321 |
from t1_16 |
|
322 |
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); |
|
323 |
||
324 |
select left(a1,7), left(a2,7) |
|
325 |
from t1_16 |
|
326 |
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); |
|
327 |
||
328 |
# group_concat with a blob argument - depends on |
|
329 |
# the variable group_concat_max_len, and |
|
330 |
# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB |
|
331 |
explain extended select left(a1,7), left(a2,7) |
|
332 |
from t1_16 |
|
333 |
where a1 in (select group_concat(b1) from t2_16 group by b2); |
|
334 |
||
335 |
select left(a1,7), left(a2,7) |
|
336 |
from t1_16 |
|
337 |
where a1 in (select group_concat(b1) from t2_16 group by b2); |
|
338 |
||
339 |
set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512) |
|
340 |
||
341 |
explain extended select left(a1,7), left(a2,7) |
|
342 |
from t1_16 |
|
343 |
where a1 in (select group_concat(b1) from t2_16 group by b2); |
|
344 |
||
345 |
select left(a1,7), left(a2,7) |
|
346 |
from t1_16 |
|
347 |
where a1 in (select group_concat(b1) from t2_16 group by b2); |
|
348 |
||
349 |
# BLOB column at the second (intermediate) level of nesting |
|
350 |
explain extended |
|
351 |
select * from t1 |
|
352 |
where concat(a1,'x') IN |
|
353 |
(select left(a1,8) from t1_16 |
|
354 |
where (a1, a2) IN |
|
355 |
(select t2_16.b1, t2_16.b2 from t2_16, t2 |
|
356 |
where t2.b2 = substring(t2_16.b2,1,6) and |
|
357 |
t2.b1 IN (select c1 from t3 where c2 > '0'))); |
|
358 |
||
359 |
||
360 |
drop table t1_16, t2_16, t3_16; |
|
361 |
||
362 |
||
363 |
# BLOB == 512 (CONVERT_IF_BIGGER_TO_BLOB == 512) |
|
364 |
set @blob_len = 512; |
|
365 |
set @suffix_len = @blob_len - @prefix_len; |
|
366 |
||
367 |
create table t1_512 (a1 blob(512), a2 blob(512)); |
|
368 |
create table t2_512 (b1 blob(512), b2 blob(512)); |
|
369 |
create table t3_512 (c1 blob(512), c2 blob(512)); |
|
370 |
||
371 |
insert into t1_512 values |
|
372 |
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); |
|
373 |
insert into t1_512 values |
|
374 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
375 |
insert into t1_512 values |
|
376 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
377 |
||
378 |
insert into t2_512 values |
|
379 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
380 |
insert into t2_512 values |
|
381 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
382 |
insert into t2_512 values |
|
383 |
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); |
|
384 |
||
385 |
insert into t3_512 values |
|
386 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
387 |
insert into t3_512 values |
|
388 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
389 |
insert into t3_512 values |
|
390 |
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); |
|
391 |
insert into t3_512 values |
|
392 |
(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); |
|
393 |
||
394 |
# single value transformer |
|
395 |
explain extended select left(a1,7), left(a2,7) |
|
396 |
from t1_512 |
|
397 |
where a1 in (select b1 from t2_512 where b1 > '0'); |
|
398 |
||
399 |
select left(a1,7), left(a2,7) |
|
400 |
from t1_512 |
|
401 |
where a1 in (select b1 from t2_512 where b1 > '0'); |
|
402 |
||
403 |
# row value transformer |
|
404 |
explain extended select left(a1,7), left(a2,7) |
|
405 |
from t1_512 |
|
406 |
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0'); |
|
407 |
||
408 |
select left(a1,7), left(a2,7) |
|
409 |
from t1_512 |
|
410 |
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0'); |
|
411 |
||
412 |
# string function with a blob argument, the return type may be != blob |
|
413 |
explain extended select left(a1,7), left(a2,7) |
|
414 |
from t1_512 |
|
415 |
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); |
|
416 |
||
417 |
select left(a1,7), left(a2,7) |
|
418 |
from t1_512 |
|
419 |
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); |
|
420 |
||
421 |
# group_concat with a blob argument - depends on |
|
422 |
# the variable group_concat_max_len, and |
|
423 |
# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB |
|
424 |
explain extended select left(a1,7), left(a2,7) |
|
425 |
from t1_512 |
|
426 |
where a1 in (select group_concat(b1) from t2_512 group by b2); |
|
427 |
||
428 |
select left(a1,7), left(a2,7) |
|
429 |
from t1_512 |
|
430 |
where a1 in (select group_concat(b1) from t2_512 group by b2); |
|
431 |
||
432 |
set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512) |
|
433 |
||
434 |
explain extended select left(a1,7), left(a2,7) |
|
435 |
from t1_512 |
|
436 |
where a1 in (select group_concat(b1) from t2_512 group by b2); |
|
437 |
||
438 |
select left(a1,7), left(a2,7) |
|
439 |
from t1_512 |
|
440 |
where a1 in (select group_concat(b1) from t2_512 group by b2); |
|
441 |
||
442 |
drop table t1_512, t2_512, t3_512; |
|
443 |
||
444 |
||
445 |
# BLOB == 1024 (group_concat_max_len == 1024) |
|
446 |
set @blob_len = 1024; |
|
447 |
set @suffix_len = @blob_len - @prefix_len; |
|
448 |
||
449 |
create table t1_1024 (a1 blob(1024), a2 blob(1024)); |
|
450 |
create table t2_1024 (b1 blob(1024), b2 blob(1024)); |
|
451 |
create table t3_1024 (c1 blob(1024), c2 blob(1024)); |
|
452 |
||
453 |
insert into t1_1024 values |
|
454 |
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); |
|
455 |
insert into t1_1024 values |
|
456 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
457 |
insert into t1_1024 values |
|
458 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
459 |
||
460 |
insert into t2_1024 values |
|
461 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
462 |
insert into t2_1024 values |
|
463 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
464 |
insert into t2_1024 values |
|
465 |
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); |
|
466 |
||
467 |
insert into t3_1024 values |
|
468 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
469 |
insert into t3_1024 values |
|
470 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
471 |
insert into t3_1024 values |
|
472 |
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); |
|
473 |
insert into t3_1024 values |
|
474 |
(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); |
|
475 |
||
476 |
# single value transformer |
|
477 |
explain extended select left(a1,7), left(a2,7) |
|
478 |
from t1_1024 |
|
479 |
where a1 in (select b1 from t2_1024 where b1 > '0'); |
|
480 |
||
481 |
select left(a1,7), left(a2,7) |
|
482 |
from t1_1024 |
|
483 |
where a1 in (select b1 from t2_1024 where b1 > '0'); |
|
484 |
||
485 |
# row value transformer |
|
486 |
explain extended select left(a1,7), left(a2,7) |
|
487 |
from t1_1024 |
|
488 |
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0'); |
|
489 |
||
490 |
select left(a1,7), left(a2,7) |
|
491 |
from t1_1024 |
|
492 |
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0'); |
|
493 |
||
494 |
# string function with a blob argument, the return type may be != blob |
|
495 |
explain extended select left(a1,7), left(a2,7) |
|
496 |
from t1_1024 |
|
497 |
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0'); |
|
498 |
||
499 |
select left(a1,7), left(a2,7) |
|
500 |
from t1_1024 |
|
501 |
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0'); |
|
502 |
||
503 |
# group_concat with a blob argument - depends on |
|
504 |
# the variable group_concat_max_len, and |
|
505 |
# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB |
|
506 |
explain extended select left(a1,7), left(a2,7) |
|
507 |
from t1_1024 |
|
508 |
where a1 in (select group_concat(b1) from t2_1024 group by b2); |
|
509 |
||
510 |
select left(a1,7), left(a2,7) |
|
511 |
from t1_1024 |
|
512 |
where a1 in (select group_concat(b1) from t2_1024 group by b2); |
|
513 |
||
514 |
set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 1024) |
|
515 |
||
516 |
explain extended select left(a1,7), left(a2,7) |
|
517 |
from t1_1024 |
|
518 |
where a1 in (select group_concat(b1) from t2_1024 group by b2); |
|
519 |
||
520 |
select left(a1,7), left(a2,7) |
|
521 |
from t1_1024 |
|
522 |
where a1 in (select group_concat(b1) from t2_1024 group by b2); |
|
523 |
||
524 |
drop table t1_1024, t2_1024, t3_1024; |
|
525 |
||
526 |
||
527 |
# BLOB == 1025 |
|
528 |
set @blob_len = 1025; |
|
529 |
set @suffix_len = @blob_len - @prefix_len; |
|
530 |
||
531 |
create table t1_1025 (a1 blob(1025), a2 blob(1025)); |
|
532 |
create table t2_1025 (b1 blob(1025), b2 blob(1025)); |
|
533 |
create table t3_1025 (c1 blob(1025), c2 blob(1025)); |
|
534 |
||
535 |
insert into t1_1025 values |
|
536 |
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); |
|
537 |
insert into t1_1025 values |
|
538 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
539 |
insert into t1_1025 values |
|
540 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
541 |
||
542 |
insert into t2_1025 values |
|
543 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
544 |
insert into t2_1025 values |
|
545 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
546 |
insert into t2_1025 values |
|
547 |
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); |
|
548 |
||
549 |
insert into t3_1025 values |
|
550 |
(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); |
|
551 |
insert into t3_1025 values |
|
552 |
(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); |
|
553 |
insert into t3_1025 values |
|
554 |
(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); |
|
555 |
insert into t3_1025 values |
|
556 |
(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); |
|
557 |
||
558 |
# single value transformer |
|
559 |
explain extended select left(a1,7), left(a2,7) |
|
560 |
from t1_1025 |
|
561 |
where a1 in (select b1 from t2_1025 where b1 > '0'); |
|
562 |
||
563 |
select left(a1,7), left(a2,7) |
|
564 |
from t1_1025 |
|
565 |
where a1 in (select b1 from t2_1025 where b1 > '0'); |
|
566 |
||
567 |
# row value transformer |
|
568 |
explain extended select left(a1,7), left(a2,7) |
|
569 |
from t1_1025 |
|
570 |
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0'); |
|
571 |
||
572 |
select left(a1,7), left(a2,7) |
|
573 |
from t1_1025 |
|
574 |
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0'); |
|
575 |
||
576 |
# string function with a blob argument, the return type may be != blob |
|
577 |
explain extended select left(a1,7), left(a2,7) |
|
578 |
from t1_1025 |
|
579 |
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0'); |
|
580 |
||
581 |
select left(a1,7), left(a2,7) |
|
582 |
from t1_1025 |
|
583 |
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0'); |
|
584 |
||
585 |
# group_concat with a blob argument - depends on |
|
586 |
# the variable group_concat_max_len, and |
|
587 |
# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB |
|
588 |
explain extended select left(a1,7), left(a2,7) |
|
589 |
from t1_1025 |
|
590 |
where a1 in (select group_concat(b1) from t2_1025 group by b2); |
|
591 |
||
592 |
select left(a1,7), left(a2,7) |
|
593 |
from t1_1025 |
|
594 |
where a1 in (select group_concat(b1) from t2_1025 group by b2); |
|
595 |
||
596 |
set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 1025) |
|
597 |
||
598 |
explain extended select left(a1,7), left(a2,7) |
|
599 |
from t1_1025 |
|
600 |
where a1 in (select group_concat(b1) from t2_1025 group by b2); |
|
601 |
||
602 |
select left(a1,7), left(a2,7) |
|
603 |
from t1_1025 |
|
604 |
where a1 in (select group_concat(b1) from t2_1025 group by b2); |
|
605 |
||
606 |
drop table t1_1025, t2_1025, t3_1025; |
|
607 |
||
608 |
# test for BIT fields |
|
609 |
create table t1bit (a1 bit(3), a2 bit(3)); |
|
610 |
create table t2bit (b1 bit(3), b2 bit(3)); |
|
611 |
||
612 |
insert into t1bit values (b'000', b'100'); |
|
613 |
insert into t1bit values (b'001', b'101'); |
|
614 |
insert into t1bit values (b'010', b'110'); |
|
615 |
||
616 |
insert into t2bit values (b'001', b'101'); |
|
617 |
insert into t2bit values (b'010', b'110'); |
|
618 |
insert into t2bit values (b'110', b'111'); |
|
619 |
||
620 |
set @@optimizer_switch=no_semijoin; |
|
621 |
||
622 |
explain extended select bin(a1), bin(a2) |
|
623 |
from t1bit |
|
624 |
where (a1, a2) in (select b1, b2 from t2bit); |
|
625 |
||
626 |
select bin(a1), bin(a2) |
|
627 |
from t1bit |
|
628 |
where (a1, a2) in (select b1, b2 from t2bit); |
|
629 |
||
630 |
drop table t1bit, t2bit; |
|
631 |
||
632 |
# test mixture of BIT and BLOB |
|
633 |
create table t1bb (a1 bit(3), a2 blob(3)); |
|
634 |
create table t2bb (b1 bit(3), b2 blob(3)); |
|
635 |
||
636 |
insert into t1bb values (b'000', '100'); |
|
637 |
insert into t1bb values (b'001', '101'); |
|
638 |
insert into t1bb values (b'010', '110'); |
|
639 |
||
640 |
insert into t2bb values (b'001', '101'); |
|
641 |
insert into t2bb values (b'010', '110'); |
|
642 |
insert into t2bb values (b'110', '111'); |
|
643 |
||
644 |
explain extended select bin(a1), a2 |
|
645 |
from t1bb |
|
646 |
where (a1, a2) in (select b1, b2 from t2bb); |
|
647 |
||
648 |
select bin(a1), a2 |
|
649 |
from t1bb |
|
650 |
where (a1, a2) in (select b1, b2 from t2bb); |
|
651 |
||
652 |
drop table t1bb, t2bb; |
|
653 |
drop table t1, t2, t3, t1i, t2i, t3i, columns; |
|
654 |
||
655 |
/******************************************************************************
|
|
656 |
* Test the cache of the left operand of IN.
|
|
657 |
******************************************************************************/
|
|
658 |
set @@optimizer_switch=no_semijoin; |
|
659 |
||
660 |
# Test that default values of Cached_item are not used for comparison |
|
661 |
create table t1 (s1 int); |
|
662 |
create table t2 (s2 int); |
|
663 |
insert into t1 values (5),(1),(0); |
|
664 |
insert into t2 values (0), (1); |
|
665 |
select s2 from t2 where s2 in (select s1 from t1); |
|
666 |
drop table t1, t2; |
|
667 |
||
668 |
create table t1 (a int not null, b int not null); |
|
669 |
create table t2 (c int not null, d int not null); |
|
670 |
create table t3 (e int not null); |
|
671 |
||
672 |
# the first outer row has no matching inner row |
|
673 |
insert into t1 values (1,10); |
|
674 |
insert into t1 values (1,20); |
|
675 |
insert into t1 values (2,10); |
|
676 |
insert into t1 values (2,20); |
|
677 |
insert into t1 values (2,30); |
|
678 |
insert into t1 values (3,20); |
|
679 |
insert into t1 values (4,40); |
|
680 |
||
681 |
insert into t2 values (2,10); |
|
682 |
insert into t2 values (2,20); |
|
683 |
insert into t2 values (2,40); |
|
684 |
insert into t2 values (3,20); |
|
685 |
insert into t2 values (4,10); |
|
686 |
insert into t2 values (5,10); |
|
687 |
||
688 |
insert into t3 values (10); |
|
689 |
insert into t3 values (10); |
|
690 |
insert into t3 values (20); |
|
691 |
insert into t3 values (30); |
|
692 |
||
693 |
explain extended |
|
694 |
select a from t1 where a in (select c from t2 where d >= 20); |
|
695 |
select a from t1 where a in (select c from t2 where d >= 20); |
|
696 |
||
697 |
create index it1a on t1(a); |
|
698 |
||
699 |
explain extended |
|
700 |
select a from t1 where a in (select c from t2 where d >= 20); |
|
701 |
select a from t1 where a in (select c from t2 where d >= 20); |
|
702 |
||
703 |
# the first outer row has a matching inner row |
|
704 |
insert into t2 values (1,10); |
|
705 |
||
706 |
explain extended |
|
707 |
select a from t1 where a in (select c from t2 where d >= 20); |
|
708 |
select a from t1 where a in (select c from t2 where d >= 20); |
|
709 |
||
710 |
# cacheing for IN predicates inside a having clause - here the cached |
|
711 |
# items are changed to point to temporary tables. |
|
712 |
explain extended |
|
713 |
select a from t1 group by a having a in (select c from t2 where d >= 20); |
|
714 |
select a from t1 group by a having a in (select c from t2 where d >= 20); |
|
715 |
||
716 |
# create an index that can be used for the outer query GROUP BY |
|
717 |
create index iab on t1(a, b); |
|
718 |
explain extended |
|
719 |
select a from t1 group by a having a in (select c from t2 where d >= 20); |
|
720 |
select a from t1 group by a having a in (select c from t2 where d >= 20); |
|
721 |
||
722 |
explain extended |
|
723 |
select a from t1 group by a |
|
724 |
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); |
|
725 |
select a from t1 group by a |
|
726 |
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); |
|
727 |
explain extended |
|
728 |
select a from t1 |
|
729 |
where a in (select c from t2 where d >= some(select e from t3 where b=e)); |
|
730 |
select a from t1 |
|
731 |
where a in (select c from t2 where d >= some(select e from t3 where b=e)); |
|
732 |
||
733 |
drop table t1, t2, t3; |