← 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-10-29 13:46:43 UTC
  • Revision ID: brian@tangent.org-20081029134643-z6jcwjvyruhk2vlu
Updates for ignore file.

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