← Back to branch summary

~linuxjedi/drizzle/trunk-bug-667053

« back to all changes in this revision

Viewing changes to mysql-test/t/subselect_mat.test

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

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