← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

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

Merged in changes. 
Edited a the comment test case so deal with our version bump.

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/t/subselect_mat.test
8
8
--enable_warnings
9
9
 
10
10
create table t1 (a1 char(8), a2 char(8));
11
 
create temporary table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;
 
11
create table t2 (b1 char(8), b2 char(8));
12
12
create table t3 (c1 char(8), c2 char(8));
13
13
 
14
14
insert into t1 values ('1 - 00', '2 - 00');
27
27
insert into t3 values ('1 - 04', '2 - 04');
28
28
 
29
29
# Indexed columns
30
 
create temporary table t1i (a1 char(8), a2 char(8)) ENGINE=MyISAM;
 
30
create table t1i (a1 char(8), a2 char(8));
31
31
create table t2i (b1 char(8), b2 char(8));
32
 
create temporary table t3i (c1 char(8), c2 char(8)) ENGINE=MyISAM;
 
32
create table t3i (c1 char(8), c2 char(8));
33
33
create index it1i1 on t1i (a1);
34
34
create index it1i2 on t1i (a2);
35
35
create index it1i3 on t1i (a1, a2);
49
49
# force the use of materialization
50
50
set @@optimizer_switch=no_semijoin;
51
51
 
52
 
--echo /********************************************************************
53
 
--echo  * Simple tests.
54
 
--echo  ********************************************************************/
55
 
--echo # non-indexed nullable fields
56
 
--replace_column 9 #
57
 
explain extended
58
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
59
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
60
 
 
61
 
--replace_column 9 #
62
 
explain extended
63
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
64
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
65
 
 
66
 
--replace_column 9 #
67
 
explain extended
68
 
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
69
 
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
70
 
 
71
 
--replace_column 9 #
 
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
 
72
68
explain extended
73
69
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
74
70
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
75
71
 
76
72
# indexed columns
77
 
--replace_column 9 #
78
 
explain extended
79
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
80
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
81
 
 
82
 
--replace_column 9 #
83
 
explain extended
84
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
85
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
86
 
 
87
 
--replace_column 9 #
88
 
explain extended
89
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
90
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
91
 
 
92
 
--replace_column 9 #
93
 
explain extended
94
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
95
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
96
 
 
97
 
--replace_column 9 #
 
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
 
98
89
explain extended
99
90
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
100
91
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
101
92
 
102
93
# materialize the result of ORDER BY
103
94
# non-indexed fields
104
 
--replace_column 9 #
105
95
explain extended
106
96
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
107
97
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
108
98
# indexed fields
109
 
--replace_column 9 #
110
99
explain extended
111
100
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
112
101
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
113
102
 
114
103
# nested subqueries, views
115
 
--replace_column 9 #
116
 
explain extended
117
 
select * from t1
118
 
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
119
 
      (a1, a2) in (select c1, c2 from t3
120
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
121
 
select * from t1
122
 
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
123
 
      (a1, a2) in (select c1, c2 from t3
124
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
125
 
 
126
 
--replace_column 9 #
127
 
explain extended
128
 
select * from t1i
129
 
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
130
 
      (a1, a2) in (select c1, c2 from t3i
131
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
132
 
select * from t1i
133
 
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
134
 
      (a1, a2) in (select c1, c2 from t3i
135
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
136
 
 
137
 
--replace_column 9 #
 
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
 
138
124
explain extended
139
125
select * from t1
140
126
where (a1, a2) in (select b1, b2 from t2
150
136
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
151
137
 
152
138
# as above with correlated innermost subquery
153
 
--replace_column 9 #
154
139
explain extended
155
140
select * from t1
156
141
where (a1, a2) in (select b1, b2 from t2
167
152
 
168
153
 
169
154
# multiple levels of nesting subqueries, unions
170
 
--replace_column 9 #
171
155
explain extended
172
156
(select * from t1
173
157
where (a1, a2) in (select b1, b2 from t2
197
181
 
198
182
 
199
183
# UNION of subqueries as a subquery (thus it is not computed via materialization)
200
 
--replace_column 9 #
201
184
explain extended
202
185
select * from t1
203
186
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
208
191
      (a1, a2) in (select c1, c2 from t3
209
192
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
210
193
# as above, with a join conditon between the outer references
211
 
--replace_column 9 #
212
194
explain extended
213
195
select * from t1, t3
214
196
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
222
204
       a1 = c1;
223
205
 
224
206
 
225
 
--echo /*********************************************************************
226
 
--echo * Negative tests, where materialization should not be applied.
227
 
--echo **********************************************************************/
228
 
--echo # UNION in a subquery
229
 
--replace_column 9 #
 
207
/******************************************************************************
 
208
* Negative tests, where materialization should not be applied.
 
209
******************************************************************************/
 
210
# UNION in a subquery
230
211
explain extended
231
212
select * from t3
232
213
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
234
215
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
235
216
 
236
217
# correlation
237
 
--replace_column 9 #
238
218
explain extended
239
219
select * from t1
240
220
where (a1, a2) in (select b1, b2 from t2
243
223
      (a1, a2) in (select c1, c2 from t3 t3c
244
224
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
245
225
 
246
 
DROP TABLE t1i, t2i, t3i;
247
 
 
248
226
# subquery has no tables
249
 
--replace_column 9 #
250
 
explain extended
251
 
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
252
 
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 
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);
253
233
 
254
234
 
255
235
/******************************************************************************
260
240
select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
261
241
 
262
242
/* GROUP BY clause */
263
 
create temporary table columns (col int key) ENGINE=MyISAM;
 
243
create table columns (col int key);
264
244
insert into columns values (1), (2);
265
245
 
266
 
--replace_column 9 #
267
 
explain extended
268
 
select * from t1 group by (select col from columns limit 1);
269
 
select * from t1 group by (select col from columns limit 1);
270
 
 
271
 
--replace_column 9 #
272
 
explain extended
273
 
select * from t1 group by (a1 in (select col from columns));
274
 
select * from t1 group by (a1 in (select col from columns));
275
 
 
276
 
--echo /* ORDER BY clause */
277
 
--replace_column 9 #
278
 
explain extended
279
 
select * from t1 order by (select col from columns limit 1);
280
 
select * from t1 order by (select col from columns limit 1);
281
 
 
282
 
DROP TABLE columns;
 
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);
283
258
 
284
259
/******************************************************************************
285
260
* Column types/sizes that affect materialization.
292
267
set @@optimizer_switch=no_semijoin;
293
268
set @prefix_len = 6;
294
269
 
295
 
# BLOB == 16 (small blobs that could be stored in MEMORY tables)
 
270
# BLOB == 16 (small blobs that could be stored in HEAP tables)
296
271
set @blob_len = 16;
297
272
set @suffix_len = @blob_len - @prefix_len;
298
273
 
299
 
create temporary table t1_16 (a1 blob, a2 blob) ENGINE=MyISAM;
300
 
create temporary table t2_16 (b1 blob, b2 blob) ENGINE=MyISAM;
301
 
create temporary table t3_16 (c1 blob, c2 blob) ENGINE=MyISAM;
 
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));
302
277
 
303
278
insert into t1_16 values
304
279
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
389
364
set @blob_len = 512;
390
365
set @suffix_len = @blob_len - @prefix_len;
391
366
 
392
 
create temporary table t1_512 (a1 blob, a2 blob) ENGINE=MyISAM;
393
 
create temporary table t2_512 (b1 blob, b2 blob) ENGINE=MyISAM;
394
 
create temporary table t3_512 (c1 blob, c2 blob) ENGINE=MyISAM;
 
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));
395
370
 
396
371
insert into t1_512 values
397
372
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
471
446
set @blob_len = 1024;
472
447
set @suffix_len = @blob_len - @prefix_len;
473
448
 
474
 
create temporary table t1_1024 (a1 blob, a2 blob) ENGINE=MyISAM;
475
 
create temporary table t2_1024 (b1 blob, b2 blob) ENGINE=MyISAM;
476
 
create temporary table t3_1024 (c1 blob, c2 blob) ENGINE=MyISAM;
 
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));
477
452
 
478
453
insert into t1_1024 values
479
454
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
553
528
set @blob_len = 1025;
554
529
set @suffix_len = @blob_len - @prefix_len;
555
530
 
556
 
create temporary table t1_1025 (a1 blob, a2 blob) ENGINE=MyISAM;
557
 
create temporary table t2_1025 (b1 blob, b2 blob) ENGINE=MyISAM;
558
 
create temporary table t3_1025 (c1 blob, c2 blob) ENGINE=MyISAM;
 
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));
559
534
 
560
535
insert into t1_1025 values
561
536
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
629
604
where a1 in (select group_concat(b1) from t2_1025 group by b2);
630
605
 
631
606
drop table t1_1025, t2_1025, t3_1025;
632
 
drop table t1, t2, t3;
 
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;
633
654
 
634
655
/******************************************************************************
635
656
* Test the cache of the left operand of IN.
637
658
set @@optimizer_switch=no_semijoin;
638
659
 
639
660
# Test that default values of Cached_item are not used for comparison
640
 
create temporary table t1 (s1 int) ENGINE=MyISAM;
641
 
create temporary table t2 (s2 int) ENGINE=MyISAM;
 
661
create table t1 (s1 int);
 
662
create table t2 (s2 int);
642
663
insert into t1 values (5),(1),(0);
643
664
insert into t2 values (0), (1);
644
665
select s2 from t2 where s2 in (select s1 from t1);
645
666
drop table t1, t2;
646
667
 
647
 
create temporary table t1 (a int not null, b int not null) ENGINE=MyISAM;
648
 
create temporary table t2 (c int not null, d int not null) ENGINE=MyISAM;
649
 
create temporary table t3 (e int not null) ENGINE=MyISAM;
 
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);
650
671
 
651
672
# the first outer row has no matching inner row
652
673
insert into t1 values (1,10);