← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect_mat.test

  • Committer: Brian Aker
  • Date: 2008-07-28 18:01:38 UTC
  • Revision ID: brian@tangent.org-20080728180138-q2pxlq0qiapvqsdn
Remove YEAR field type

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/subselect_mat.test
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 temporary table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;
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 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;
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
 
--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 #
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
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 #
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
104
 
--replace_column 9 #
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
109
 
--replace_column 9 #
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
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 #
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
153
 
--replace_column 9 #
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
170
 
--replace_column 9 #
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)
200
 
--replace_column 9 #
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
211
 
--replace_column 9 #
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
 
 
225
 
--echo /*********************************************************************
226
 
--echo * Negative tests, where materialization should not be applied.
227
 
--echo **********************************************************************/
228
 
--echo # UNION in a subquery
229
 
--replace_column 9 #
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
237
 
--replace_column 9 #
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
 
 
246
 
DROP TABLE t1i, t2i, t3i;
247
 
 
248
 
# subquery has no tables
249
 
--replace_column 9 #
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 */
263
 
create temporary table columns (col int key) ENGINE=MyISAM;
264
 
insert into columns values (1), (2);
265
 
 
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 #
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
 
 
282
 
DROP TABLE columns;
283
 
 
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
 
 
295
 
# BLOB == 16 (small blobs that could be stored in MEMORY tables)
296
 
set @blob_len = 16;
297
 
set @suffix_len = @blob_len - @prefix_len;
298
 
 
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;
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
 
 
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;
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
 
 
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;
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
 
 
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;
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;
632
 
drop table t1, t2, t3;
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
640
 
create temporary table t1 (s1 int) ENGINE=MyISAM;
641
 
create temporary table t2 (s2 int) ENGINE=MyISAM;
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
 
 
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;
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;