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