← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect_mat.test

  • Committer: Monty Taylor
  • Date: 2008-08-16 21:06:22 UTC
  • Revision ID: monty@inaugust.com-20080816210622-zpnn13unyinqzn72
Updated po files.

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)) ENGINE=MyISAM;
11
 
create table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;
12
 
create table t3 (c1 char(8), c2 char(8)) ENGINE=MyISAM;
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)) ENGINE=MyISAM;
31
 
create table t2i (b1 char(8), b2 char(8)) ENGINE=MyISAM;
32
 
create 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
 
/******************************************************************************
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
 
 
231
 
 
232
 
/******************************************************************************
233
 
* Subqueries in other uncovered clauses.
234
 
******************************************************************************/
235
 
 
236
 
/* SELECT clause */
237
 
select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
238
 
 
239
 
/* GROUP BY clause */
240
 
create table columns (col int key) ENGINE=MyISAM;
241
 
insert into columns values (1), (2);
242
 
 
243
 
explain extended
244
 
select * from t1 group by (select col from columns limit 1);
245
 
select * from t1 group by (select col from columns limit 1);
246
 
 
247
 
explain extended
248
 
select * from t1 group by (a1 in (select col from columns));
249
 
select * from t1 group by (a1 in (select col from columns));
250
 
 
251
 
/* ORDER BY clause */
252
 
explain extended
253
 
select * from t1 order by (select col from columns limit 1);
254
 
select * from t1 order by (select col from columns limit 1);
255
 
 
256
 
/******************************************************************************
257
 
* Column types/sizes that affect materialization.
258
 
******************************************************************************/
259
 
 
260
 
/*
261
 
  Test that BLOBs are not materialized (except when arguments of some functions).
262
 
*/
263
 
# force materialization to be always considered
264
 
set @@optimizer_switch=no_semijoin;
265
 
set @prefix_len = 6;
266
 
 
267
 
# BLOB == 16 (small blobs that could be stored in HEAP tables)
268
 
set @blob_len = 16;
269
 
set @suffix_len = @blob_len - @prefix_len;
270
 
 
271
 
create table t1_16 (a1 blob, a2 blob) ENGINE=MyISAM;
272
 
create table t2_16 (b1 blob, b2 blob) ENGINE=MyISAM;
273
 
create table t3_16 (c1 blob, c2 blob) ENGINE=MyISAM;
274
 
 
275
 
insert into t1_16 values
276
 
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
277
 
insert into t1_16 values
278
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
279
 
insert into t1_16 values
280
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
281
 
 
282
 
insert into t2_16 values
283
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
284
 
insert into t2_16 values
285
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
286
 
insert into t2_16 values
287
 
 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
288
 
 
289
 
insert into t3_16 values
290
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
291
 
insert into t3_16 values
292
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
293
 
insert into t3_16 values
294
 
 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
295
 
insert into t3_16 values
296
 
 (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
297
 
 
298
 
# single value transformer
299
 
explain extended select left(a1,7), left(a2,7)
300
 
from t1_16
301
 
where a1 in (select b1 from t2_16 where b1 > '0');
302
 
 
303
 
select left(a1,7), left(a2,7)
304
 
from t1_16
305
 
where a1 in (select b1 from t2_16 where b1 > '0');
306
 
 
307
 
# row value transformer
308
 
explain extended select left(a1,7), left(a2,7)
309
 
from t1_16
310
 
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
311
 
 
312
 
select left(a1,7), left(a2,7)
313
 
from t1_16
314
 
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
315
 
 
316
 
# string function with a blob argument, the return type may be != blob
317
 
explain extended select left(a1,7), left(a2,7)
318
 
from t1_16
319
 
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
320
 
 
321
 
select left(a1,7), left(a2,7)
322
 
from t1_16
323
 
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
324
 
 
325
 
# group_concat with a blob argument - depends on
326
 
# the variable group_concat_max_len, and
327
 
# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
328
 
explain extended select left(a1,7), left(a2,7)
329
 
from t1_16
330
 
where a1 in (select group_concat(b1) from t2_16 group by b2);
331
 
 
332
 
select left(a1,7), left(a2,7)
333
 
from t1_16
334
 
where a1 in (select group_concat(b1) from t2_16 group by b2);
335
 
 
336
 
set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512)
337
 
 
338
 
explain extended select left(a1,7), left(a2,7)
339
 
from t1_16
340
 
where a1 in (select group_concat(b1) from t2_16 group by b2);
341
 
 
342
 
select left(a1,7), left(a2,7)
343
 
from t1_16
344
 
where a1 in (select group_concat(b1) from t2_16 group by b2);
345
 
 
346
 
# BLOB column at the second (intermediate) level of nesting
347
 
explain extended
348
 
select * from t1
349
 
where concat(a1,'x') IN
350
 
      (select left(a1,8) from t1_16
351
 
       where (a1, a2) IN
352
 
             (select t2_16.b1, t2_16.b2 from t2_16, t2
353
 
              where t2.b2 = substring(t2_16.b2,1,6) and
354
 
                    t2.b1 IN (select c1 from t3 where c2 > '0')));
355
 
 
356
 
 
357
 
drop table t1_16, t2_16, t3_16;
358
 
 
359
 
 
360
 
# BLOB == 512 (CONVERT_IF_BIGGER_TO_BLOB == 512)
361
 
set @blob_len = 512;
362
 
set @suffix_len = @blob_len - @prefix_len;
363
 
 
364
 
create table t1_512 (a1 blob, a2 blob) ENGINE=MyISAM;
365
 
create table t2_512 (b1 blob, b2 blob) ENGINE=MyISAM;
366
 
create table t3_512 (c1 blob, c2 blob) ENGINE=MyISAM;
367
 
 
368
 
insert into t1_512 values
369
 
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
370
 
insert into t1_512 values
371
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
372
 
insert into t1_512 values
373
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
374
 
 
375
 
insert into t2_512 values
376
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
377
 
insert into t2_512 values
378
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
379
 
insert into t2_512 values
380
 
 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
381
 
 
382
 
insert into t3_512 values
383
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
384
 
insert into t3_512 values
385
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
386
 
insert into t3_512 values
387
 
 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
388
 
insert into t3_512 values
389
 
 (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
390
 
 
391
 
# single value transformer
392
 
explain extended select left(a1,7), left(a2,7)
393
 
from t1_512
394
 
where a1 in (select b1 from t2_512 where b1 > '0');
395
 
 
396
 
select left(a1,7), left(a2,7)
397
 
from t1_512
398
 
where a1 in (select b1 from t2_512 where b1 > '0');
399
 
 
400
 
# row value transformer
401
 
explain extended select left(a1,7), left(a2,7)
402
 
from t1_512
403
 
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
404
 
 
405
 
select left(a1,7), left(a2,7)
406
 
from t1_512
407
 
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
408
 
 
409
 
# string function with a blob argument, the return type may be != blob
410
 
explain extended select left(a1,7), left(a2,7)
411
 
from t1_512
412
 
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
413
 
 
414
 
select left(a1,7), left(a2,7)
415
 
from t1_512
416
 
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
417
 
 
418
 
# group_concat with a blob argument - depends on
419
 
# the variable group_concat_max_len, and
420
 
# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
421
 
explain extended select left(a1,7), left(a2,7)
422
 
from t1_512
423
 
where a1 in (select group_concat(b1) from t2_512 group by b2);
424
 
 
425
 
select left(a1,7), left(a2,7)
426
 
from t1_512
427
 
where a1 in (select group_concat(b1) from t2_512 group by b2);
428
 
 
429
 
set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512)
430
 
 
431
 
explain extended select left(a1,7), left(a2,7)
432
 
from t1_512
433
 
where a1 in (select group_concat(b1) from t2_512 group by b2);
434
 
 
435
 
select left(a1,7), left(a2,7)
436
 
from t1_512
437
 
where a1 in (select group_concat(b1) from t2_512 group by b2);
438
 
 
439
 
drop table t1_512, t2_512, t3_512;
440
 
 
441
 
 
442
 
# BLOB == 1024 (group_concat_max_len == 1024)
443
 
set @blob_len = 1024;
444
 
set @suffix_len = @blob_len - @prefix_len;
445
 
 
446
 
create table t1_1024 (a1 blob, a2 blob) ENGINE=MyISAM;
447
 
create table t2_1024 (b1 blob, b2 blob) ENGINE=MyISAM;
448
 
create table t3_1024 (c1 blob, c2 blob) ENGINE=MyISAM;
449
 
 
450
 
insert into t1_1024 values
451
 
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
452
 
insert into t1_1024 values
453
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
454
 
insert into t1_1024 values
455
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
456
 
 
457
 
insert into t2_1024 values
458
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
459
 
insert into t2_1024 values
460
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
461
 
insert into t2_1024 values
462
 
 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
463
 
 
464
 
insert into t3_1024 values
465
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
466
 
insert into t3_1024 values
467
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
468
 
insert into t3_1024 values
469
 
 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
470
 
insert into t3_1024 values
471
 
 (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
472
 
 
473
 
# single value transformer
474
 
explain extended select left(a1,7), left(a2,7)
475
 
from t1_1024
476
 
where a1 in (select b1 from t2_1024 where b1 > '0');
477
 
 
478
 
select left(a1,7), left(a2,7)
479
 
from t1_1024
480
 
where a1 in (select b1 from t2_1024 where b1 > '0');
481
 
 
482
 
# row value transformer
483
 
explain extended select left(a1,7), left(a2,7)
484
 
from t1_1024
485
 
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
486
 
 
487
 
select left(a1,7), left(a2,7)
488
 
from t1_1024
489
 
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
490
 
 
491
 
# string function with a blob argument, the return type may be != blob
492
 
explain extended select left(a1,7), left(a2,7)
493
 
from t1_1024
494
 
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
495
 
 
496
 
select left(a1,7), left(a2,7)
497
 
from t1_1024
498
 
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
499
 
 
500
 
# group_concat with a blob argument - depends on
501
 
# the variable group_concat_max_len, and
502
 
# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
503
 
explain extended select left(a1,7), left(a2,7)
504
 
from t1_1024
505
 
where a1 in (select group_concat(b1) from t2_1024 group by b2);
506
 
 
507
 
select left(a1,7), left(a2,7)
508
 
from t1_1024
509
 
where a1 in (select group_concat(b1) from t2_1024 group by b2);
510
 
 
511
 
set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 1024)
512
 
 
513
 
explain extended select left(a1,7), left(a2,7)
514
 
from t1_1024
515
 
where a1 in (select group_concat(b1) from t2_1024 group by b2);
516
 
 
517
 
select left(a1,7), left(a2,7)
518
 
from t1_1024
519
 
where a1 in (select group_concat(b1) from t2_1024 group by b2);
520
 
 
521
 
drop table t1_1024, t2_1024, t3_1024;
522
 
 
523
 
 
524
 
# BLOB == 1025
525
 
set @blob_len = 1025;
526
 
set @suffix_len = @blob_len - @prefix_len;
527
 
 
528
 
create table t1_1025 (a1 blob, a2 blob) ENGINE=MyISAM;
529
 
create table t2_1025 (b1 blob, b2 blob) ENGINE=MyISAM;
530
 
create table t3_1025 (c1 blob, c2 blob) ENGINE=MyISAM;
531
 
 
532
 
insert into t1_1025 values
533
 
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
534
 
insert into t1_1025 values
535
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
536
 
insert into t1_1025 values
537
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
538
 
 
539
 
insert into t2_1025 values
540
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
541
 
insert into t2_1025 values
542
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
543
 
insert into t2_1025 values
544
 
 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
545
 
 
546
 
insert into t3_1025 values
547
 
 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
548
 
insert into t3_1025 values
549
 
 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
550
 
insert into t3_1025 values
551
 
 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
552
 
insert into t3_1025 values
553
 
 (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
554
 
 
555
 
# single value transformer
556
 
explain extended select left(a1,7), left(a2,7)
557
 
from t1_1025
558
 
where a1 in (select b1 from t2_1025 where b1 > '0');
559
 
 
560
 
select left(a1,7), left(a2,7)
561
 
from t1_1025
562
 
where a1 in (select b1 from t2_1025 where b1 > '0');
563
 
 
564
 
# row value transformer
565
 
explain extended select left(a1,7), left(a2,7)
566
 
from t1_1025
567
 
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
568
 
 
569
 
select left(a1,7), left(a2,7)
570
 
from t1_1025
571
 
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
572
 
 
573
 
# string function with a blob argument, the return type may be != blob
574
 
explain extended select left(a1,7), left(a2,7)
575
 
from t1_1025
576
 
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
577
 
 
578
 
select left(a1,7), left(a2,7)
579
 
from t1_1025
580
 
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
581
 
 
582
 
# group_concat with a blob argument - depends on
583
 
# the variable group_concat_max_len, and
584
 
# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
585
 
explain extended select left(a1,7), left(a2,7)
586
 
from t1_1025
587
 
where a1 in (select group_concat(b1) from t2_1025 group by b2);
588
 
 
589
 
select left(a1,7), left(a2,7)
590
 
from t1_1025
591
 
where a1 in (select group_concat(b1) from t2_1025 group by b2);
592
 
 
593
 
set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 1025)
594
 
 
595
 
explain extended select left(a1,7), left(a2,7)
596
 
from t1_1025
597
 
where a1 in (select group_concat(b1) from t2_1025 group by b2);
598
 
 
599
 
select left(a1,7), left(a2,7)
600
 
from t1_1025
601
 
where a1 in (select group_concat(b1) from t2_1025 group by b2);
602
 
 
603
 
drop table t1_1025, t2_1025, t3_1025;
604
 
drop table t1, t2, t3;
605
 
 
606
 
/******************************************************************************
607
 
* Test the cache of the left operand of IN.
608
 
******************************************************************************/
609
 
set @@optimizer_switch=no_semijoin;
610
 
 
611
 
# Test that default values of Cached_item are not used for comparison
612
 
create table t1 (s1 int) ENGINE=MyISAM;
613
 
create table t2 (s2 int) ENGINE=MyISAM;
614
 
insert into t1 values (5),(1),(0);
615
 
insert into t2 values (0), (1);
616
 
select s2 from t2 where s2 in (select s1 from t1);
617
 
drop table t1, t2;
618
 
 
619
 
create table t1 (a int not null, b int not null) ENGINE=MyISAM;
620
 
create table t2 (c int not null, d int not null) ENGINE=MyISAM;
621
 
create table t3 (e int not null) ENGINE=MyISAM;
622
 
 
623
 
# the first outer row has no matching inner row
624
 
insert into t1 values (1,10);
625
 
insert into t1 values (1,20);
626
 
insert into t1 values (2,10);
627
 
insert into t1 values (2,20);
628
 
insert into t1 values (2,30);
629
 
insert into t1 values (3,20);
630
 
insert into t1 values (4,40);
631
 
 
632
 
insert into t2 values (2,10);
633
 
insert into t2 values (2,20);
634
 
insert into t2 values (2,40);
635
 
insert into t2 values (3,20);
636
 
insert into t2 values (4,10);
637
 
insert into t2 values (5,10);
638
 
 
639
 
insert into t3 values (10);
640
 
insert into t3 values (10);
641
 
insert into t3 values (20);
642
 
insert into t3 values (30);
643
 
 
644
 
explain extended
645
 
select a from t1 where a in (select c from t2 where d >= 20);
646
 
select a from t1 where a in (select c from t2 where d >= 20);
647
 
 
648
 
create index it1a on t1(a);
649
 
 
650
 
explain extended
651
 
select a from t1 where a in (select c from t2 where d >= 20);
652
 
select a from t1 where a in (select c from t2 where d >= 20);
653
 
 
654
 
# the first outer row has a matching inner row
655
 
insert into t2 values (1,10);
656
 
 
657
 
explain extended
658
 
select a from t1 where a in (select c from t2 where d >= 20);
659
 
select a from t1 where a in (select c from t2 where d >= 20);
660
 
 
661
 
# cacheing for IN predicates inside a having clause - here the cached
662
 
# items are changed to point to temporary tables.
663
 
explain extended
664
 
select a from t1 group by a having a in (select c from t2 where d >= 20);
665
 
select a from t1 group by a having a in (select c from t2 where d >= 20);
666
 
 
667
 
# create an index that can be used for the outer query GROUP BY 
668
 
create index iab on t1(a, b);
669
 
explain extended
670
 
select a from t1 group by a having a in (select c from t2 where d >= 20);
671
 
select a from t1 group by a having a in (select c from t2 where d >= 20);
672
 
 
673
 
explain extended
674
 
select a from t1 group by a
675
 
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
676
 
select a from t1 group by a
677
 
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
678
 
explain extended
679
 
select a from t1
680
 
where a in (select c from t2 where d >= some(select e from t3 where b=e));
681
 
select a from t1
682
 
where a in (select c from t2 where d >= some(select e from t3 where b=e));
683
 
 
684
 
drop table t1, t2, t3;