← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect_mat.test

Merge of Jay

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/subselect_mat.test
8
8
--enable_warnings
9
9
 
10
10
create table t1 (a1 char(8), a2 char(8));
11
 
create table t2 (b1 char(8), b2 char(8));
 
11
create temporary table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;
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 table t1i (a1 char(8), a2 char(8));
 
30
create temporary table t1i (a1 char(8), a2 char(8)) ENGINE=MyISAM;
31
31
create table t2i (b1 char(8), b2 char(8));
32
 
create table t3i (c1 char(8), c2 char(8));
 
32
create temporary table t3i (c1 char(8), c2 char(8)) ENGINE=MyISAM;
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
 
/******************************************************************************
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
 
 
 
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 #
68
72
explain extended
69
73
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
70
74
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
71
75
 
72
76
# 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
 
 
 
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 #
89
98
explain extended
90
99
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
91
100
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
92
101
 
93
102
# materialize the result of ORDER BY
94
103
# non-indexed fields
 
104
--replace_column 9 #
95
105
explain extended
96
106
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
97
107
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
98
108
# indexed fields
 
109
--replace_column 9 #
99
110
explain extended
100
111
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
101
112
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
102
113
 
103
114
# 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
 
 
 
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 #
124
138
explain extended
125
139
select * from t1
126
140
where (a1, a2) in (select b1, b2 from t2
136
150
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
137
151
 
138
152
# as above with correlated innermost subquery
 
153
--replace_column 9 #
139
154
explain extended
140
155
select * from t1
141
156
where (a1, a2) in (select b1, b2 from t2
152
167
 
153
168
 
154
169
# multiple levels of nesting subqueries, unions
 
170
--replace_column 9 #
155
171
explain extended
156
172
(select * from t1
157
173
where (a1, a2) in (select b1, b2 from t2
181
197
 
182
198
 
183
199
# UNION of subqueries as a subquery (thus it is not computed via materialization)
 
200
--replace_column 9 #
184
201
explain extended
185
202
select * from t1
186
203
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
191
208
      (a1, a2) in (select c1, c2 from t3
192
209
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
193
210
# as above, with a join conditon between the outer references
 
211
--replace_column 9 #
194
212
explain extended
195
213
select * from t1, t3
196
214
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
204
222
       a1 = c1;
205
223
 
206
224
 
207
 
/******************************************************************************
208
 
* Negative tests, where materialization should not be applied.
209
 
******************************************************************************/
210
 
# UNION in a subquery
 
225
--echo /*********************************************************************
 
226
--echo * Negative tests, where materialization should not be applied.
 
227
--echo **********************************************************************/
 
228
--echo # UNION in a subquery
 
229
--replace_column 9 #
211
230
explain extended
212
231
select * from t3
213
232
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
215
234
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
216
235
 
217
236
# correlation
 
237
--replace_column 9 #
218
238
explain extended
219
239
select * from t1
220
240
where (a1, a2) in (select b1, b2 from t2
223
243
      (a1, a2) in (select c1, c2 from t3 t3c
224
244
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
225
245
 
 
246
DROP TABLE t1i, t2i, t3i;
 
247
 
226
248
# 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);
 
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');
233
253
 
234
254
 
235
255
/******************************************************************************
240
260
select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
241
261
 
242
262
/* GROUP BY clause */
243
 
create table columns (col int key);
 
263
create temporary table columns (col int key) ENGINE=MyISAM;
244
264
insert into columns values (1), (2);
245
265
 
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);
 
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;
258
283
 
259
284
/******************************************************************************
260
285
* Column types/sizes that affect materialization.
271
296
set @blob_len = 16;
272
297
set @suffix_len = @blob_len - @prefix_len;
273
298
 
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));
 
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;
277
302
 
278
303
insert into t1_16 values
279
304
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
364
389
set @blob_len = 512;
365
390
set @suffix_len = @blob_len - @prefix_len;
366
391
 
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));
 
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;
370
395
 
371
396
insert into t1_512 values
372
397
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
446
471
set @blob_len = 1024;
447
472
set @suffix_len = @blob_len - @prefix_len;
448
473
 
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));
 
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;
452
477
 
453
478
insert into t1_1024 values
454
479
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
528
553
set @blob_len = 1025;
529
554
set @suffix_len = @blob_len - @prefix_len;
530
555
 
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));
 
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;
534
559
 
535
560
insert into t1_1025 values
536
561
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
604
629
where a1 in (select group_concat(b1) from t2_1025 group by b2);
605
630
 
606
631
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;
 
632
drop table t1, t2, t3;
654
633
 
655
634
/******************************************************************************
656
635
* Test the cache of the left operand of IN.
658
637
set @@optimizer_switch=no_semijoin;
659
638
 
660
639
# Test that default values of Cached_item are not used for comparison
661
 
create table t1 (s1 int);
662
 
create table t2 (s2 int);
 
640
create temporary table t1 (s1 int) ENGINE=MyISAM;
 
641
create temporary table t2 (s2 int) ENGINE=MyISAM;
663
642
insert into t1 values (5),(1),(0);
664
643
insert into t2 values (0), (1);
665
644
select s2 from t2 where s2 in (select s1 from t1);
666
645
drop table t1, t2;
667
646
 
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);
 
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;
671
650
 
672
651
# the first outer row has no matching inner row
673
652
insert into t1 values (1,10);