3
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
6
explain extended select (select 2);
7
SELECT (SELECT 1) UNION SELECT (SELECT 2);
8
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
9
SELECT (SELECT (SELECT 0 UNION SELECT 0));
10
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
11
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
12
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b;
13
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
14
SELECT (SELECT a) as a;
15
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
16
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
18
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
19
SELECT 1 FROM (SELECT (SELECT a) b) c;
20
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
21
SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1);
22
SELECT 1 IN (SELECT 1);
23
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
24
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
25
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
26
SELECT (SELECT 1,2,3) = ROW(1,2,3);
27
SELECT (SELECT 1,2,3) = ROW(1,2,1);
28
SELECT (SELECT 1,2,3) < ROW(1,2,1);
29
SELECT (SELECT 1,2,3) > ROW(1,2,1);
30
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
31
SELECT ROW(1,2,3) = (SELECT 1,2,3);
32
SELECT ROW(1,2,3) = (SELECT 1,2,1);
33
SELECT ROW(1,2,3) < (SELECT 1,2,1);
34
SELECT ROW(1,2,3) > (SELECT 1,2,1);
35
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
36
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
37
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
38
SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
39
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
40
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
41
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
43
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
45
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
47
create table t1 (a int);
48
create table t2 (a int, b int);
49
create table t3 (a int);
50
create table t4 (a int not null, b int not null);
51
insert into t1 values (2);
52
insert into t2 values (1,7),(2,7);
53
insert into t4 values (4,8),(3,8),(5,9);
54
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
55
select (select a from t1 where t1.a=t2.a), a from t2;
56
select (select a from t1 where t1.a=t2.b), a from t2;
57
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
58
select (select a from t3), a from t2;
59
select * from t2 where t2.a=(select a from t1);
60
insert into t3 values (6),(7),(3);
61
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
62
(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 order by a limit 2) limit 3;
63
(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
64
explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
65
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
66
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
67
(select * from t2 where a>1) as tt;
68
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
69
(select * from t2 where a>1) as tt;
70
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
71
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
72
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);
73
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
74
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
75
select * from t3 where exists (select * from t2 where t2.b=t3.a);
76
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
77
select * from t3 where a in (select b from t2);
78
select * from t3 where a not in (select b from t2);
79
select * from t3 where a = some (select b from t2);
80
select * from t3 where a <> any (select b from t2);
82
# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);
83
select * from t3 where a = all (select b from t2);
85
select * from t3 where a <> all (select b from t2);
86
insert into t2 values (100, 5);
87
select * from t3 where a < any (select b from t2);
88
select * from t3 where a < all (select b from t2);
89
select * from t3 where a >= any (select b from t2);
90
explain extended select * from t3 where a >= any (select b from t2);
91
select * from t3 where a >= all (select b from t2);
92
delete from t2 where a=100;
93
select * from t3 where a in (select a,b from t2);
94
select * from t3 where a in (select * from t2);
95
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
97
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
98
insert into t2 values (2,10);
99
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
100
delete from t2 where a=2 and b=10;
101
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
102
create table t5 (a int);
103
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
104
insert into t5 values (5);
105
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
106
insert into t5 values (2);
107
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
108
explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
109
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
110
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
111
create table t7( uq int primary key, name char(25));
112
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
113
insert into t6 values (1,1),(1,2),(2,2),(1,3);
114
select * from t6 where exists (select * from t7 where uq = clinic_uq);
115
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
118
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
120
# different tipes & group functions
123
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
124
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
125
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
126
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
127
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
128
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
129
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
130
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
131
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
134
`pseudo` varchar(35) NOT NULL default '',
135
`email` varchar(60) NOT NULL default '',
136
PRIMARY KEY (`pseudo`),
137
UNIQUE KEY `email` (`email`)
138
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
140
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
141
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
142
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
143
EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
144
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
145
t8 WHERE pseudo='joce');
146
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
148
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
149
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
151
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
153
#searchconthardwarefr3 forumconthardwarefr7
155
`topic` bigint unsigned NOT NULL default '0',
156
`date` date NOT NULL default '0000-00-00',
157
`pseudo` varchar(35) NOT NULL default '',
158
PRIMARY KEY (`pseudo`,`date`,`topic`),
159
KEY `topic` (`topic`)
160
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
161
INSERT INTO t1 (topic,date,pseudo) VALUES
162
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
163
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
164
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
165
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
166
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
167
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
168
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
169
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
172
#forumconthardwarefr7 searchconthardwarefr7
174
`numeropost` bigint unsigned NOT NULL auto_increment,
175
`maxnumrep` int unsigned NOT NULL default '0',
176
PRIMARY KEY (`numeropost`),
177
UNIQUE KEY `maxnumrep` (`maxnumrep`)
178
) ENGINE=MyISAM ROW_FORMAT=FIXED;
180
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
183
`mot` varchar(30) NOT NULL default '',
184
`topic` bigint unsigned NOT NULL default '0',
185
`date` date NOT NULL default '0000-00-00',
186
`pseudo` varchar(35) NOT NULL default '',
187
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
188
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
190
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
191
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
192
SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
193
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
194
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
196
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
197
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
198
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
199
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
200
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
201
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
202
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
203
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
204
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
205
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
206
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
207
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
208
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
209
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
210
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
213
#forumconthardwarefr7
215
`numeropost` bigint unsigned NOT NULL auto_increment,
216
`maxnumrep` int unsigned NOT NULL default '0',
217
PRIMARY KEY (`numeropost`),
218
UNIQUE KEY `maxnumrep` (`maxnumrep`)
219
) ENGINE=MyISAM ROW_FORMAT=FIXED;
221
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
222
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
223
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
226
create table t1 (a int);
227
insert into t1 values (1),(2),(3);
228
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
232
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
233
INSERT INTO t1 VALUES ();
234
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
239
`numeropost` bigint unsigned NOT NULL default '0',
240
`numreponse` int unsigned NOT NULL auto_increment,
241
`pseudo` varchar(35) NOT NULL default '',
242
PRIMARY KEY (`numeropost`,`numreponse`),
243
UNIQUE KEY `numreponse` (`numreponse`),
244
KEY `pseudo` (`pseudo`,`numeropost`)
246
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
247
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
248
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
249
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
250
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
251
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
252
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
255
CREATE TABLE t1 (a int);
256
INSERT INTO t1 VALUES (1);
257
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
260
#update with subselects
261
create table t1 (a int NOT NULL, b int, primary key (a));
262
create table t2 (a int NOT NULL, b int, primary key (a));
263
insert into t1 values (0, 10),(1, 11),(2, 12);
264
insert into t2 values (1, 21),(2, 22),(3, 23);
266
update t1 set b= (select b from t1);
267
update t1 set b= (select b from t2);
268
update t1 set b= (select b from t2 where t1.a = t2.a);
272
#delete with subselects
273
create table t1 (a int NOT NULL, b int, primary key (a));
274
create table t2 (a int NOT NULL, b int, primary key (a));
275
insert into t1 values (0, 10),(1, 11),(2, 12);
276
insert into t2 values (1, 21),(2, 12),(3, 23);
278
select * from t1 where b = (select b from t2 where t1.a = t2.a);
279
delete from t1 where b = (select b from t1);
280
delete from t1 where b = (select b from t2);
281
delete from t1 where b = (select b from t2 where t1.a = t2.a);
285
#multi-delete with subselects
287
create table t11 (a int NOT NULL, b int, primary key (a));
288
create table t12 (a int NOT NULL, b int, primary key (a));
289
create table t2 (a int NOT NULL, b int, primary key (a));
290
insert into t11 values (0, 10),(1, 11),(2, 12);
291
insert into t12 values (33, 10),(22, 11),(2, 12);
292
insert into t2 values (1, 21),(2, 12),(3, 23);
295
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
296
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
297
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
300
drop table t11, t12, t2;
302
#insert with subselects
303
CREATE TABLE t1 (x int);
304
create table t2 (a int);
305
create table t3 (b int);
306
insert into t2 values (1);
307
insert into t3 values (1),(2);
308
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
309
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
310
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
312
insert into t2 values (1);
313
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
315
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
317
# After this, only data based on old t1 records should have been added.
318
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
320
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
321
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
324
#TODO: should be uncommented after bug 380 fix pushed
325
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
327
drop table t1, t2, t3;
329
#replace with subselects
330
CREATE TABLE t1 (x int not null, y int, primary key (x));
331
create table t2 (a int);
332
create table t3 (a int);
333
insert into t2 values (1);
334
insert into t3 values (1),(2);
336
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
337
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
338
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
340
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
342
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
344
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
346
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
348
drop table t1, t2, t3;
350
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
352
CREATE TABLE t2 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
353
INSERT INTO t2 VALUES (1),(2);
354
SELECT * FROM t2 WHERE id IN (SELECT 1);
355
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
356
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
357
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
358
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
359
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
360
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
361
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
362
INSERT INTO t2 VALUES ((SELECT * FROM t2));
363
INSERT INTO t2 VALUES ((SELECT id FROM t2));
365
CREATE TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
366
INSERT INTO t1 values (1),(1);
367
UPDATE t2 SET id=(SELECT * FROM t1);
371
create table t1 (a int);
372
insert into t1 values (1),(2),(3);
373
select 1 IN (SELECT * from t1);
374
select 10 IN (SELECT * from t1);
375
select NULL IN (SELECT * from t1);
376
update t1 set a=NULL where a=2;
377
select 1 IN (SELECT * from t1);
378
select 3 IN (SELECT * from t1);
379
select 10 IN (SELECT * from t1);
380
select 1 > ALL (SELECT * from t1);
381
select 10 > ALL (SELECT * from t1);
382
select 1 > ANY (SELECT * from t1);
383
select 10 > ANY (SELECT * from t1);
385
create table t1 (a varchar(20));
386
insert into t1 values ('A'),('BC'),('DEF');
387
select 'A' IN (SELECT * from t1);
388
select 'XYZS' IN (SELECT * from t1);
389
select NULL IN (SELECT * from t1);
390
update t1 set a=NULL where a='BC';
391
select 'A' IN (SELECT * from t1);
392
select 'DEF' IN (SELECT * from t1);
393
select 'XYZS' IN (SELECT * from t1);
394
select 'A' > ALL (SELECT * from t1);
395
select 'XYZS' > ALL (SELECT * from t1);
396
select 'A' > ANY (SELECT * from t1);
397
select 'XYZS' > ANY (SELECT * from t1);
399
create table t1 (a float);
400
insert into t1 values (1.5),(2.5),(3.5);
401
select 1.5 IN (SELECT * from t1);
402
select 10.5 IN (SELECT * from t1);
403
select NULL IN (SELECT * from t1);
404
update t1 set a=NULL where a=2.5;
405
select 1.5 IN (SELECT * from t1);
406
select 3.5 IN (SELECT * from t1);
407
select 10.5 IN (SELECT * from t1);
408
select 1.5 > ALL (SELECT * from t1);
409
select 10.5 > ALL (SELECT * from t1);
410
select 1.5 > ANY (SELECT * from t1);
411
select 10.5 > ANY (SELECT * from t1);
412
explain extended select (select a+1) from t1;
413
select (select a+1) from t1;
420
CREATE TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a));
421
CREATE TABLE t2 (a int default '0', INDEX (a));
422
INSERT INTO t1 VALUES (1),(2),(3),(4);
423
INSERT INTO t2 VALUES (1),(2),(3);
424
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
425
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
426
CREATE TABLE t3 (a int default '0');
427
INSERT INTO t3 VALUES (1),(2),(3);
428
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
429
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
432
#LIMIT is not supported now
433
#create table t1 (a float);
435
#select 10.5 IN (SELECT * from t1 LIMIT 1);
437
#select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
440
#create table t1 (a int, b int, c varchar(10));
441
#create table t2 (a int);
442
#insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
443
#insert into t2 values (1),(2),(NULL);
444
#select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;
445
#select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
446
#select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
449
#create table t1 (a int, b real, c varchar(10));
450
#insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
451
#select ROW(1, 1, 'a') IN (select a,b,c from t1);
452
#select ROW(1, 2, 'a') IN (select a,b,c from t1);
453
#select ROW(1, 1, 'a') IN (select b,a,c from t1);
454
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
455
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
456
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
457
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
458
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
459
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
461
#select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
464
#test of uncacheable subqueries
465
CREATE TABLE t1 (a int);
466
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
467
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
472
`mot` varchar(30) NOT NULL default '',
473
`topic` bigint unsigned NOT NULL default '0',
474
`date` date NOT NULL default '0000-00-00',
475
`pseudo` varchar(35) NOT NULL default '',
476
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
477
KEY `pseudo` (`pseudo`,`date`,`topic`),
478
KEY `topic` (`topic`)
479
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
482
`mot` varchar(30) NOT NULL default '',
483
`topic` bigint unsigned NOT NULL default '0',
484
`date` date NOT NULL default '0000-00-00',
485
`pseudo` varchar(35) NOT NULL default '',
486
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
487
KEY `pseudo` (`pseudo`,`date`,`topic`),
488
KEY `topic` (`topic`)
489
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
492
`numeropost` bigint unsigned NOT NULL auto_increment,
493
`maxnumrep` int unsigned NOT NULL default '0',
494
PRIMARY KEY (`numeropost`),
495
UNIQUE KEY `maxnumrep` (`maxnumrep`)
497
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
499
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
501
INSERT INTO t3 VALUES (1,1);
503
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
506
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
507
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
510
drop table t1, t2, t3;
512
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
513
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
514
SHOW CREATE TABLE t1;
516
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
517
SHOW CREATE TABLE t1;
519
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
520
SHOW CREATE TABLE t1;
522
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
524
SHOW CREATE TABLE t1;
527
create table t1 (a int);
528
insert into t1 values (1), (2), (3);
529
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
536
select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent);
543
ID int NOT NULL auto_increment,
544
name char(35) NOT NULL default '',
545
t2 char(3) NOT NULL default '',
546
District char(20) NOT NULL default '',
547
Population int NOT NULL default '0',
551
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
552
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
553
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
556
Code char(3) NOT NULL default '',
557
Name char(52) NOT NULL default '',
558
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
559
Region char(26) NOT NULL default '',
560
SurfaceArea float(10,2) NOT NULL default '0.00',
561
IndepYear smallint default NULL,
562
Population int NOT NULL default '0',
563
LifeExpectancy float(3,1) default NULL,
564
GNP float(10,2) default NULL,
565
GNPOld float(10,2) default NULL,
566
LocalName char(45) NOT NULL default '',
567
GovernmentForm char(45) NOT NULL default '',
568
HeadOfState char(60) default NULL,
569
Capital int default NULL,
570
Code2 char(2) NOT NULL default '',
574
INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU');
575
INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');
577
select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent);
585
`id` bigint unsigned NOT NULL auto_increment,
586
`pseudo` varchar(35) NOT NULL default '',
588
UNIQUE KEY `pseudo` (`pseudo`)
589
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
590
INSERT INTO t1 (pseudo) VALUES ('test');
591
SELECT 0 IN (SELECT 1 FROM t1 a);
592
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
593
INSERT INTO t1 (pseudo) VALUES ('test1');
594
SELECT 0 IN (SELECT 1 FROM t1 a);
595
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
599
`i` int NOT NULL default '0',
603
INSERT INTO t1 VALUES (1);
604
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
605
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
606
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
616
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
619
name varchar(15) default NULL
622
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
623
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
628
# correct NULL in <CONSTANT> IN (SELECT ...)
630
create table t1 (a int, unique index indexa (a));
631
insert into t1 values (-1), (-4), (-2), (NULL);
632
select -10 IN (select a from t1 FORCE INDEX (indexa));
636
# Test optimization for sub selects
638
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
639
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
640
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
644
ID int unsigned NOT NULL auto_increment,
645
SUB_ID int unsigned NOT NULL default '0',
646
REF_ID int unsigned default NULL,
647
REF_SUB int unsigned default '0',
648
PRIMARY KEY (ID,SUB_ID),
649
UNIQUE KEY t1_PK (ID,SUB_ID),
650
KEY t1_FK (REF_ID,REF_SUB),
651
KEY t1_REFID (REF_ID)
653
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
654
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
658
# uninterruptable update
660
create table t1 (a int, b int);
661
create table t2 (a int, b int);
663
insert into t1 values (1,0), (2,0), (3,0);
664
insert into t2 values (1,1), (2,1), (3,1), (2,2);
666
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
672
# reduced subselect in ORDER BY & GROUP BY clauses
676
`id` bigint unsigned NOT NULL auto_increment,
677
`pseudo` varchar(35) NOT NULL default '',
678
`email` varchar(60) NOT NULL default '',
680
UNIQUE KEY `email` (`email`),
681
UNIQUE KEY `pseudo` (`pseudo`)
682
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
683
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
684
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
685
drop table if exists t1;
687
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
690
# IN subselect optimization test
692
create table t1 (a int not null, b int, primary key (a));
693
create table t2 (a int not null, primary key (a));
694
create table t3 (a int not null, b int, primary key (a));
695
insert into t1 values (1,10), (2,20), (3,30), (4,40);
696
insert into t2 values (2), (3), (4), (5);
697
insert into t3 values (10,3), (20,4), (30,5);
698
select * from t2 where t2.a in (select a from t1);
699
explain extended select * from t2 where t2.a in (select a from t1);
700
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
701
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
702
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
703
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
704
drop table t1, t2, t3;
705
create table t1 (a int, b int, index a (a,b));
706
create table t2 (a int, index a (a));
707
create table t3 (a int, b int, index a (a));
708
insert into t1 values (1,10), (2,20), (3,30), (4,40);
710
# making table large enough
716
eval insert into t1 values (rand()*100000+200,rand()*100000);
722
insert into t2 values (2), (3), (4), (5);
723
insert into t3 values (10,3), (20,4), (30,5);
724
select * from t2 where t2.a in (select a from t1);
725
explain extended select * from t2 where t2.a in (select a from t1);
726
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
727
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
728
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
729
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
730
insert into t1 values (3,31);
731
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
732
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
733
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
734
drop table t1, t2, t3;
737
# alloc_group_fields() working
739
create table t1 (a int, b int);
740
create table t2 (a int, b int);
741
create table t3 (a int, b int);
742
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
743
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
744
insert into t3 values (3,3), (2,2), (1,1);
745
select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3;
749
# aggregate functions in HAVING test
751
create table t1 (s1 int);
752
create table t2 (s1 int);
753
insert into t1 values (1);
754
insert into t2 values (1);
755
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
759
# update subquery with wrong field (to force name resolving
760
# in UPDATE name space)
762
create table t1 (s1 int);
763
create table t2 (s1 int);
764
insert into t1 values (1);
765
insert into t2 values (1);
766
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
772
#CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
773
# s2 CHAR(5) COLLATE latin1_swedish_ci);
774
#INSERT INTO t1 VALUES ('z','?');
776
#select * from t1 where s1 > (select max(s2) from t1);
778
#select * from t1 where s1 > any (select max(s2) from t1);
782
# aggregate functions reinitialization
784
create table t1(toid int,rd int);
785
create table t2(userid int,pmnew int,pmtotal int);
786
insert into t2 values(1,0,0),(2,0,0);
787
insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2);
788
select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1);
794
create table t1 (s1 char(5));
795
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
796
insert into t1 values ('tttt');
797
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
798
explain extended (select * from t1);
803
# IN optimisation test results
805
create table t1 (s1 char(5), index s1(s1));
806
create table t2 (s1 char(5), index s1(s1));
807
insert into t1 values ('a1'),('a2'),('a3');
808
insert into t2 values ('a1'),('a2');
809
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
810
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
811
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
812
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
813
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
814
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
815
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
816
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
820
# correct ALL optimisation
822
create table t2 (a int, b int);
823
create table t3 (a int);
824
insert into t3 values (6),(7),(3);
825
select * from t3 where a >= all (select b from t2);
826
explain extended select * from t3 where a >= all (select b from t2);
827
select * from t3 where a >= some (select b from t2);
828
explain extended select * from t3 where a >= some (select b from t2);
829
select * from t3 where a >= all (select b from t2 group by 1);
830
explain extended select * from t3 where a >= all (select b from t2 group by 1);
831
select * from t3 where a >= some (select b from t2 group by 1);
832
explain extended select * from t3 where a >= some (select b from t2 group by 1);
833
select * from t3 where NULL >= any (select b from t2);
834
explain extended select * from t3 where NULL >= any (select b from t2);
835
select * from t3 where NULL >= any (select b from t2 group by 1);
836
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
837
select * from t3 where NULL >= some (select b from t2);
838
explain extended select * from t3 where NULL >= some (select b from t2);
839
select * from t3 where NULL >= some (select b from t2 group by 1);
840
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
842
# optimized static ALL/ANY with grouping
844
insert into t2 values (2,2), (2,1), (3,3), (3,1);
845
select * from t3 where a > all (select max(b) from t2 group by a);
846
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
850
# correct used_tables()
853
CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
854
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
855
CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint NOT NULL default '0',`secondary_uid` smallint NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;
856
INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);
857
CREATE TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` tinyint NOT NULL default '1',`tues` tinyint NOT NULL default '1',`wed` tinyint NOT NULL default '1',`thur` tinyint NOT NULL default '1',`fri` tinyint NOT NULL default '1',`sat` tinyint NOT NULL default '0',`sun` tinyint NOT NULL default '0',`how_often` smallint NOT NULL default '1',`userid` smallint NOT NULL default '0',`active` tinyint NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;
858
INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);
859
CREATE TABLE `t4` (`task_id` smallint NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
860
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
861
select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;
862
SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid;
863
drop table t1,t2,t3,t4;
868
CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM;
869
INSERT INTO t1 VALUES (1),(5);
870
CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM;
871
INSERT INTO t2 VALUES (2),(6);
872
select * from t1 where (1,2,6) in (select * from t2);
876
# optimized ALL/ANY with union
878
create table t1 (s1 char);
879
insert into t1 values ('e');
880
select * from t1 where 'f' > any (select s1 from t1);
881
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
882
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
886
# filesort in subquery (restoring join_tab)
888
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
889
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
890
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
891
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
892
select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;
896
# unresolved field error
898
create table t1 (s1 int);
899
create table t2 (s1 int);
900
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
901
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
902
select count(*) from t2 group by t1.s2;
906
# fix_fields() in add_ref_to_table_cond()
908
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
909
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
910
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
911
INSERT INTO t2 VALUES (100, 200, 'C');
912
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
915
CREATE TABLE t1 (a int);
916
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
917
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
925
`id` int NOT NULL auto_increment,
926
`id_cns` tinyint unsigned NOT NULL default '0',
927
`tipo` enum('','UNO','DUE') NOT NULL default '',
928
`anno_dep` smallint unsigned NOT NULL default '0',
929
`particolare` bigint unsigned NOT NULL default '0',
930
`generale` bigint unsigned NOT NULL default '0',
931
`bis` tinyint unsigned NOT NULL default '0',
933
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
934
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
936
INSERT INTO `t1` VALUES (1,16,'UNO',1987,2048,9681,0),(2,50,'UNO',1987,1536,13987,0),(3,16,'UNO',1987,2432,14594,0),(4,16,'UNO',1987,1792,13422,0),(5,16,'UNO',1987,1025,10240,0),(6,16,'UNO',1987,1026,7089,0);
938
`id` tinyint unsigned NOT NULL auto_increment,
939
`max_anno_dep` smallint unsigned NOT NULL default '0',
942
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
944
SELECT cns.id, cns.max_anno_dep, cns.max_anno_dep = (SELECT s.anno_dep FROM t1 AS s WHERE s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM t2 AS cns;
951
create table t1 (a int);
952
insert into t1 values (1), (2), (3);
953
SET SQL_SELECT_LIMIT=1;
954
select sum(a) from (select * from t1) as a;
955
select 2 in (select * from t1);
956
SET SQL_SELECT_LIMIT=default;
960
# Bug #3118: subselect + order by
963
CREATE TABLE t1 (a int, b int, INDEX (a));
964
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
965
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
968
# Item_cond fix field
970
create table t1(val varchar(10));
971
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
972
select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');
976
# ref_or_null replacing with ref
978
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
979
insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');
980
select * from t1 where id not in (select id from t1 where id < 8);
981
select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
982
explain extended select * from t1 where id not in (select id from t1 where id < 8);
983
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
984
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
985
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
986
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
987
select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
988
explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
992
# Static tables & rund() in subqueries
994
create table t1 (a int);
995
insert into t1 values (1);
996
explain select benchmark(1000, (select a from t1 where a=rand()));
1002
create table t1(id int);
1003
create table t2(id int);
1004
create table t3(flag int);
1005
select (select * from t3 where id not null) from t1, t2;
1006
drop table t1,t2,t3;
1009
# aggregate functions (Bug #3505)
1011
CREATE TABLE t1 (id INT);
1012
CREATE TABLE t2 (id INT);
1013
INSERT INTO t1 VALUES (1), (2);
1014
INSERT INTO t2 VALUES (1);
1015
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1016
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1017
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;
1018
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY id;
1024
CREATE TABLE t1 ( a int, b int );
1025
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1026
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1027
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1028
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1029
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1030
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1031
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1032
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1033
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1034
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1035
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1036
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1037
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1039
ALTER TABLE t1 ADD INDEX (a);
1040
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1041
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1042
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1043
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1044
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1045
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1046
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1047
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1048
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1049
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1050
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1051
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1052
# having clause test
1053
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1054
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
1055
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
1056
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
1057
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
1058
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
1059
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
1060
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
1061
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
1062
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
1063
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
1064
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
1066
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1067
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1068
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1069
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1070
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1071
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1072
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1073
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1074
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1075
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1076
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1077
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1078
# union + having test
1079
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1080
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1081
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1082
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1083
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1084
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1085
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1086
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1087
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1088
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1089
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1090
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1092
# < > >= <= and = ALL/ <> ANY do not support row operation
1093
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1094
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1095
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1096
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
1097
SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1098
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1099
SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
1100
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
1101
# following should be converted to IN
1102
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
1103
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1104
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1105
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
1106
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1107
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1108
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
1109
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
1110
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
1111
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
1112
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
1113
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
1114
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
1115
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
1116
# without optimisation
1117
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1118
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1119
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1120
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1121
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1122
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1123
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1124
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1125
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1126
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1127
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1128
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1129
# without optimisation + having
1130
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
1131
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
1132
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
1133
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
1134
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
1135
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
1136
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
1137
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
1138
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
1139
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
1140
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
1141
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
1142
# EXISTS in string contence
1143
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
1144
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
1145
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
1147
CREATE TABLE t1 ( a double, b double );
1148
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1149
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
1150
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
1151
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
1152
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
1153
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
1154
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
1155
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
1156
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
1157
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
1158
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
1159
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
1160
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
1162
CREATE TABLE t1 ( a char(1), b char(1));
1163
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
1164
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
1165
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
1166
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
1167
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
1168
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
1169
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
1170
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
1171
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
1172
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
1173
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
1174
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
1175
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
1180
# SELECT(EXISTS * ...)optimisation
1182
create table t1 (a int, b int);
1183
insert into t1 values (1,2),(3,4);
1184
select * from t1 up where exists (select * from t1 where t1.a=up.a);
1185
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
1189
# Bug #4102: subselect in HAVING
1192
CREATE TABLE t1 (t1_a int);
1193
INSERT INTO t1 VALUES (1);
1194
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
1195
INSERT INTO t2 VALUES (1, 1), (1, 2);
1196
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
1197
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
1201
# Test problem with NULL and derived tables (Bug #4097)
1204
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL);
1205
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
1206
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL);
1207
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
1208
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
1212
# Aggregate function comparation with ALL/ANY/SOME subselect
1214
CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
1215
insert into t1 values (1);
1216
CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
1217
insert into t2 values (1,2);
1218
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
1222
# BUG#5003 - like in subselect
1224
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin);
1225
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
1226
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
1227
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
1228
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
1229
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
1230
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
1234
# Optimized IN with compound index
1236
CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
1237
CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
1238
insert into t1 values (1,1),(1,2),(2,1),(2,2);
1239
insert into t2 values (1,2),(2,2);
1240
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1241
alter table t2 drop primary key;
1242
alter table t2 add key KEY1 (aid, bid);
1243
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1244
alter table t2 drop key KEY1;
1245
alter table t2 add primary key (bid, aid);
1246
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1250
# resolving fields of grouped outer SELECT
1252
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
1253
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
1254
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
1255
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
1256
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
1257
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
1258
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
1259
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
1262
create table t1 (x int);
1263
select (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x;
1267
# Test of correct maybe_null flag returning by subquwery for temporary table
1270
CREATE TABLE `t1` ( `master` int unsigned NOT NULL default '0', `map` smallint unsigned NOT NULL default '0', `slave` int unsigned NOT NULL default '0', `access` int unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
1271
INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);
1272
CREATE TABLE `t2` ( `id` int unsigned NOT NULL default '0', `pid` int unsigned NOT NULL default '0', `map` smallint unsigned NOT NULL default '0', `level` tinyint unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
1273
INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a');
1274
SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
1275
SELECT b.ac FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
1279
# Subselect in non-select command just after connection
1281
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
1283
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
1286
# primary query with temporary table and subquery with groupping
1288
create table t1 (a int, b int);
1289
create table t2 (a int, b int);
1290
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
1291
insert into t2 values (1,3),(2,1);
1292
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
1296
# Equal operation under row and empty subquery
1298
create table t1 (s1 int,s2 int);
1299
insert into t1 values (20,15);
1300
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
1306
create table t1 (s1 int);
1307
insert into t1 values (1),(null);
1308
select * from t1 where s1 < all (select s1 from t1);
1309
select s1, s1 < all (select s1 from t1) from t1;
1313
# reference on changable fields from subquery
1316
Code char(3) NOT NULL default '',
1317
Name char(52) NOT NULL default '',
1318
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1319
Region char(26) NOT NULL default '',
1320
SurfaceArea float(10,2) NOT NULL default '0.00',
1321
IndepYear smallint default NULL,
1322
Population int NOT NULL default '0',
1323
LifeExpectancy float(3,1) default NULL,
1324
GNP float(10,2) default NULL,
1325
GNPOld float(10,2) default NULL,
1326
LocalName char(45) NOT NULL default '',
1327
GovernmentForm char(45) NOT NULL default '',
1328
HeadOfState char(60) default NULL,
1329
Capital int default NULL,
1330
Code2 char(2) NOT NULL default ''
1332
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
1333
INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS');
1334
INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
1335
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
1336
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
1337
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
1338
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
1343
# Test for BUG#7885: Server crash when 'any' subselect compared to
1344
# non-existant field.
1346
create table t1 (a1 int);
1347
create table t2 (b1 int);
1349
select * from t1 where a2 > any(select b1 from t2);
1350
select * from t1 where a1 > any(select b1 from t2);
1355
# Comparison subquery with * and row
1357
create table t1 (a integer, b integer);
1358
select (select * from t1) = (select 1,2);
1359
select (select 1,2) = (select * from t1);
1360
# queries whih can be converted to IN
1361
select row(1,2) = ANY (select * from t1);
1362
select row(1,2) != ALL (select * from t1);
1366
# Comparison subquery and row with nested rows
1368
create table t1 (a integer, b integer);
1369
select row(1,(2,2)) in (select * from t1 );
1370
select row(1,(2,2)) = (select * from t1 );
1371
select (select * from t1) = row(1,(2,2));
1375
# Forward reference detection
1377
create table t1 (a integer);
1378
insert into t1 values (1);
1379
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
1380
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
1381
select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );
1382
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
1389
categoryId int NOT NULL,
1390
courseId int NOT NULL,
1391
startDate datetime NOT NULL,
1392
endDate datetime NOT NULL,
1393
createDate datetime NOT NULL,
1394
modifyDate timestamp NOT NULL,
1395
attributes text NOT NULL
1397
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
1398
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1399
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1400
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
1401
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
1402
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1403
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1404
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
1405
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
1408
userId int NOT NULL,
1409
courseId int NOT NULL,
1410
date datetime NOT NULL
1412
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
1413
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
1414
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
1415
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
1416
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
1417
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
1421
groupId int NOT NULL,
1422
parentId int NOT NULL,
1423
startDate datetime NOT NULL,
1424
endDate datetime NOT NULL,
1425
createDate datetime NOT NULL,
1426
modifyDate timestamp NOT NULL,
1429
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
1433
groupTypeId int NOT NULL,
1434
groupKey varchar(50) NOT NULL,
1438
createDate datetime NOT NULL,
1439
modifyDate timestamp NOT NULL
1441
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
1442
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
1445
userId int NOT NULL,
1446
groupId int NOT NULL,
1447
createDate datetime NOT NULL,
1448
modifyDate timestamp NOT NULL
1450
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
1453
count(distinct t2.userid) pass,
1455
count(t2.courseid) crse,
1458
date_format(date, '%b%y') as colhead
1460
join t1 on t2.courseid=t1.courseid
1473
select t4.id as parentid,
1474
t4.name as parentgroup,
1476
t4.name as groupname,
1479
) as gin on t5.groupid=gin.childid
1480
) as groupstuff on t2.userid = groupstuff.userid
1482
groupstuff.groupname, colhead , t2.courseid;
1484
drop table t1, t2, t3, t4, t5;
1487
# Transformation in left expression of subquery (BUG#8888)
1489
create table t1 (a int);
1490
insert into t1 values (1), (2), (3);
1491
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
1495
# single row subqueries and row operations (code covarage improvement)
1497
create table t1 (a int, b int);
1498
insert into t1 values (1,2);
1499
select 1 = (select * from t1);
1500
select (select * from t1) = 1;
1501
select (1,2) = (select a from t1);
1502
select (select a from t1) = (1,2);
1503
select (1,2,3) = (select * from t1);
1504
select (select * from t1) = (1,2,3);
1508
# Item_int_with_ref check (BUG#10020)
1510
#CREATE TABLE `t1` (
1511
# `itemid` bigint unsigned NOT NULL auto_increment,
1512
# `sessionid` bigint unsigned default NULL,
1513
# `time` int unsigned NOT NULL default '0',
1514
# `data` text collate latin1_general_ci NOT NULL,
1515
# PRIMARY KEY (`itemid`)
1517
#INSERT INTO `t1` VALUES (1, 1, 1, '');
1518
#CREATE TABLE `t2` (
1519
# `sessionid` bigint unsigned NOT NULL auto_increment,
1520
# `pid` int unsigned NOT NULL default '0',
1521
# `date` int unsigned NOT NULL default '0',
1522
# `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
1523
# PRIMARY KEY (`sessionid`)
1525
#INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
1526
#SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;
1529
# BUG#11821 : Select from subselect using aggregate function on an enum
1531
create table t1 (fld enum('0','1'));
1532
insert into t1 values ('1');
1533
select * from (select max(fld) from t1) as foo;
1537
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
1540
CREATE TABLE t1 (one int, two int, flag char(1));
1541
CREATE TABLE t2 (one int, two int, flag char(1));
1542
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1543
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1546
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
1548
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
1550
insert into t2 values (null,null,'N');
1551
insert into t2 values (null,3,'0');
1552
insert into t2 values (null,5,'0');
1553
insert into t2 values (10,null,'0');
1554
insert into t1 values (10,3,'0');
1555
insert into t1 values (10,5,'0');
1556
insert into t1 values (10,10,'0');
1557
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
1558
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
1559
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
1560
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
1561
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
1562
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
1563
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
1564
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
1568
# Bug #12392: where cond with IN predicate for rows and NULL values in table
1571
CREATE TABLE t1 (a char(5), b char(5));
1572
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
1574
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
1579
# Bug #11479: subquery over left join with an empty inner table
1582
CREATE TABLE t1 (a int);
1583
CREATE TABLE t2 (a int, b int);
1584
CREATE TABLE t3 (b int NOT NULL);
1585
INSERT INTO t1 VALUES (1), (2), (3), (4);
1586
INSERT INTO t2 VALUES (1,10), (3,30);
1588
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
1589
WHERE t3.b IS NOT NULL OR t2.a > 10;
1591
WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
1592
WHERE t3.b IS NOT NULL OR t2.a > 10);
1594
DROP TABLE t1,t2,t3;
1597
# Bug#18503: Queries with a quantified subquery returning empty set may
1598
# return a wrong result.
1600
CREATE TABLE t1 (f1 INT);
1601
CREATE TABLE t2 (f2 INT);
1602
INSERT INTO t1 VALUES (1);
1603
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
1604
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
1605
INSERT INTO t2 VALUES (1);
1606
INSERT INTO t2 VALUES (2);
1607
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
1610
# BUG#20975 Wrong query results for subqueries within NOT
1611
create table t1 (s1 char);
1612
insert into t1 values (1),(2);
1614
select * from t1 where (s1 < any (select s1 from t1));
1615
select * from t1 where not (s1 < any (select s1 from t1));
1617
select * from t1 where (s1 < ALL (select s1+1 from t1));
1618
select * from t1 where not(s1 < ALL (select s1+1 from t1));
1620
select * from t1 where (s1+1 = ANY (select s1 from t1));
1621
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
1623
select * from t1 where (s1 = ALL (select s1/s1 from t1));
1624
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
1628
# Bug #16255: Subquery in where
1631
retailerID varchar(8) NOT NULL,
1632
statusID int unsigned NOT NULL,
1633
changed datetime NOT NULL,
1634
UNIQUE KEY retailerID (retailerID, statusID, changed)
1637
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
1638
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
1639
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
1640
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
1641
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
1642
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
1645
where (r1.retailerID,(r1.changed)) in
1646
(SELECT r2.retailerId,(max(changed)) from t1 r2
1647
group by r2.retailerId);
1651
# Bug #21180: Subselect with index for both WHERE and ORDER BY
1652
# produces empty result
1654
create table t1(a int, primary key (a));
1655
insert into t1 values (10);
1657
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
1658
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
1660
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1661
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1662
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
1663
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1664
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1665
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
1667
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1668
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1669
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
1670
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1671
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1672
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
1677
# Bug #21853: assert failure for a grouping query with
1678
# an ALL/ANY quantified subquery in HAVING
1682
field1 int NOT NULL,
1683
field2 int NOT NULL,
1684
field3 int NOT NULL,
1685
PRIMARY KEY (field1,field2,field3)
1688
fieldA int NOT NULL,
1689
fieldB int NOT NULL,
1690
PRIMARY KEY (fieldA,fieldB)
1693
INSERT INTO t1 VALUES
1694
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
1695
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
1697
SELECT field1, field2, COUNT(*)
1698
FROM t1 GROUP BY field1, field2;
1700
SELECT field1, field2
1702
GROUP BY field1, field2
1703
HAVING COUNT(*) >= ALL (SELECT fieldB
1704
FROM t2 WHERE fieldA = field1);
1705
SELECT field1, field2
1707
GROUP BY field1, field2
1708
HAVING COUNT(*) < ANY (SELECT fieldB
1709
FROM t2 WHERE fieldA = field1);
1714
# Bug #23478: not top-level IN subquery returning a non-empty result set
1715
# with possible NULL values by index access from the outer query
1718
CREATE TABLE t1(a int, INDEX (a));
1719
INSERT INTO t1 VALUES (1), (3), (5), (7);
1720
INSERT INTO t1 VALUES (NULL);
1722
CREATE TABLE t2(a int);
1723
INSERT INTO t2 VALUES (1),(2),(3);
1725
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
1726
SELECT a, a IN (SELECT a FROM t1) FROM t2;
1731
# Bug #11302: getObject() returns a String for a sub-query of type datetime
1733
CREATE TABLE t1 (a DATETIME);
1734
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
1736
CREATE TABLE t2 AS SELECT
1737
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
1738
FROM t1 WHERE a > '2000-01-01';
1739
SHOW CREATE TABLE t2;
1741
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
1742
SHOW CREATE TABLE t3;
1744
DROP TABLE t1,t2,t3;
1747
# Bug 24653: sorting by expressions containing subselects
1748
# that return more than one row
1751
CREATE TABLE t1 (a int);
1752
INSERT INTO t1 VALUES (2), (4), (1), (3);
1754
CREATE TABLE t2 (b int, c int);
1755
INSERT INTO t2 VALUES
1756
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
1758
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
1760
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
1761
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
1763
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
1765
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
1767
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
1770
SELECT a FROM t1 GROUP BY a
1771
HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
1772
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1774
SELECT a FROM t1 GROUP BY a
1775
HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
1776
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1778
SELECT a FROM t1 GROUP BY a
1779
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
1780
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1782
SELECT a FROM t1 GROUP BY a
1783
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
1784
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
1787
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
1788
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
1791
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
1792
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
1795
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
1796
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
1799
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
1800
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
1807
#decimal-related tests
1809
create table t1 (df decimal(5,1));
1810
insert into t1 values(1.1);
1811
insert into t1 values(2.2);
1813
select * from t1 where df <= all (select avg(df) from t1 group by df);
1814
select * from t1 where df >= all (select avg(df) from t1 group by df);
1817
create table t1 (df decimal(5,1));
1818
insert into t1 values(1.1);
1819
select 1.1 * exists(select * from t1);
1823
grp int default NULL,
1824
a decimal(10,2) default NULL);
1826
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
1828
select min(a) from t1 group by grp;
1832
# Test for bug #9338: lame substitution of c1 instead of c2
1835
CREATE table t1 ( c1 integer );
1836
INSERT INTO t1 VALUES ( 1 );
1837
INSERT INTO t1 VALUES ( 2 );
1838
INSERT INTO t1 VALUES ( 3 );
1840
CREATE TABLE t2 ( c2 integer );
1841
INSERT INTO t2 VALUES ( 1 );
1842
INSERT INTO t2 VALUES ( 4 );
1843
INSERT INTO t2 VALUES ( 5 );
1845
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
1847
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
1848
WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
1853
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ
1855
CREATE TABLE t1 ( c1 integer );
1856
INSERT INTO t1 VALUES ( 1 );
1857
INSERT INTO t1 VALUES ( 2 );
1858
INSERT INTO t1 VALUES ( 3 );
1859
INSERT INTO t1 VALUES ( 6 );
1861
CREATE TABLE t2 ( c2 integer );
1862
INSERT INTO t2 VALUES ( 1 );
1863
INSERT INTO t2 VALUES ( 4 );
1864
INSERT INTO t2 VALUES ( 5 );
1865
INSERT INTO t2 VALUES ( 6 );
1867
CREATE TABLE t3 ( c3 integer );
1868
INSERT INTO t3 VALUES ( 7 );
1869
INSERT INTO t3 VALUES ( 8 );
1871
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
1872
WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
1874
DROP TABLE t1,t2,t3;
1877
# Correct building of equal fields list (do not include outer
1878
# fields) (BUG#6384)
1880
CREATE TABLE t1 (EMPNUM CHAR(3));
1881
CREATE TABLE t2 (EMPNUM CHAR(3) );
1882
INSERT INTO t1 VALUES ('E1'),('E2');
1883
INSERT INTO t2 VALUES ('E1');
1885
WHERE t1.EMPNUM NOT IN
1888
WHERE t1.EMPNUM = t2.EMPNUM);
1893
# Test for bug #11487: range access in a subquery
1896
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
1897
INSERT INTO t1 VALUES (1, 1);
1898
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
1899
PRIMARY KEY(select_id,values_id));
1900
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
1902
SELECT values_id FROM t1
1903
WHERE values_id IN (SELECT values_id FROM t2
1904
WHERE select_id IN (1, 0));
1905
SELECT values_id FROM t1
1906
WHERE values_id IN (SELECT values_id FROM t2
1907
WHERE select_id BETWEEN 0 AND 1);
1908
SELECT values_id FROM t1
1909
WHERE values_id IN (SELECT values_id FROM t2
1910
WHERE select_id = 0 OR select_id = 1);
1914
# BUG#11821 : Select from subselect using aggregate function on an enum
1916
create table t1 (fld enum('0','1'));
1917
insert into t1 values ('1');
1918
select * from (select max(fld) from t1) as foo;
1922
# Test for bug #11762: subquery with an aggregate function in HAVING
1925
CREATE TABLE t1 (a int, b int);
1926
CREATE TABLE t2 (c int, d int);
1927
CREATE TABLE t3 (e int);
1929
INSERT INTO t1 VALUES
1930
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
1931
INSERT INTO t2 VALUES
1932
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
1933
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
1935
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
1939
SELECT a FROM t1 GROUP BY a
1940
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
1941
SELECT a FROM t1 GROUP BY a
1942
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
1943
SELECT a FROM t1 GROUP BY a
1944
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
1945
SELECT a FROM t1 GROUP BY a
1946
HAVING a IN (SELECT c FROM t2
1947
WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
1948
SELECT a FROM t1 GROUP BY a
1949
HAVING a IN (SELECT c FROM t2
1950
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
1951
SELECT a FROM t1 GROUP BY a
1952
HAVING a IN (SELECT c FROM t2
1953
WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
1954
SELECT a FROM t1 GROUP BY a
1955
HAVING a IN (SELECT c FROM t2
1956
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
1957
SELECT a FROM t1 GROUP BY a
1958
HAVING a IN (SELECT c FROM t2
1959
WHERE MIN(b) < d AND
1960
EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
1962
SELECT a, SUM(a) FROM t1 GROUP BY a;
1965
WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
1966
SELECT a FROM t1 GROUP BY a
1967
HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
1971
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
1974
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
1976
SELECT t1.a FROM t1 GROUP BY t1.a
1977
HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
1978
HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
1979
HAVING SUM(t1.a+t2.c) < t3.e/4));
1980
SELECT t1.a FROM t1 GROUP BY t1.a
1981
HAVING t1.a > ALL(SELECT t2.c FROM t2
1982
WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
1983
HAVING SUM(t1.a+t2.c) < t3.e/4));
1984
SELECT t1.a FROM t1 GROUP BY t1.a
1985
HAVING t1.a > ALL(SELECT t2.c FROM t2
1986
WHERE EXISTS(SELECT t3.e FROM t3
1987
WHERE SUM(t1.a+t2.c) < t3.e/4));
1988
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
1990
SELECT t1.a FROM t1 GROUP BY t1.a
1991
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
1992
HAVING AVG(t2.c+SUM(t1.b)) > 20);
1993
SELECT t1.a FROM t1 GROUP BY t1.a
1994
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
1995
HAVING AVG(SUM(t1.b)) > 20);
1997
SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
1998
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
1999
HAVING t2.c+sum > 20);
2001
DROP TABLE t1,t2,t3;
2004
# Test for bug #16603: GROUP BY in a row subquery with a quantifier
2005
# when an index is defined on the grouping field
2007
CREATE TABLE t1 (a varchar(5), b varchar(10));
2008
INSERT INTO t1 VALUES
2009
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
2010
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
2012
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2014
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2016
ALTER TABLE t1 ADD INDEX(a);
2018
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2020
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2025
# Bug#17366: Unchecked Item_int results in server crash
2027
create table t1( f1 int,f2 int);
2028
insert into t1 values (1,1),(2,2);
2029
select tt.t from (select 'crash1' as t, f2 from t1) as tt left join t1 on tt.t = 'crash2' and tt.f2 = t1.f2 where tt.t = 'crash1';
2033
# Bug #18306: server crash on delete using subquery.
2036
create table t1 (c int, key(c));
2037
insert into t1 values (1142477582), (1142455969);
2038
create table t2 (a int, b int);
2039
insert into t2 values (2, 1), (1, 0);
2040
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
2044
# Bug#19077: A nested materialized derived table is used before being populated.
2046
create table t1 (i int, j bigint);
2047
insert into t1 values (1, 2), (2, 2), (3, 2);
2048
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
2052
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
2054
CREATE TABLE t1 (i BIGINT UNSIGNED);
2055
INSERT INTO t1 VALUES (10000000000000000000); # > MAX SIGNED BIGINT 9323372036854775807
2056
INSERT INTO t1 VALUES (1);
2058
CREATE TABLE t2 (i BIGINT UNSIGNED);
2059
INSERT INTO t2 VALUES (10000000000000000000); # same as first table
2060
INSERT INTO t2 VALUES (1);
2063
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
2066
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
2068
/* subquery test with cast*/
2069
SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);
2075
# Bug#20519: subselect with LIMIT M, N
2079
id bigint unsigned NOT NULL auto_increment,
2080
name varchar(255) NOT NULL,
2083
INSERT INTO t1 VALUES
2084
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
2087
id bigint unsigned NOT NULL auto_increment,
2088
mid bigint unsigned NOT NULL,
2092
INSERT INTO t2 VALUES
2093
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
2094
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
2097
(SELECT date FROM t2 WHERE mid = t1.id
2098
ORDER BY date DESC LIMIT 0, 1) AS date_last,
2099
(SELECT date FROM t2 WHERE mid = t1.id
2100
ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
2103
(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
2104
ORDER BY date DESC LIMIT 1, 1) AS date_count
2107
(SELECT date FROM t2 WHERE mid = t1.id
2108
ORDER BY date DESC LIMIT 0, 1) AS date_last,
2109
(SELECT date FROM t2 WHERE mid = t1.id
2110
ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
2115
# Bug#20869: subselect with range access by DESC
2119
i1 int NOT NULL default '0',
2120
i2 int NOT NULL default '0',
2121
t datetime NOT NULL default '0000-00-00 00:00:00',
2122
PRIMARY KEY (i1,i2,t)
2124
INSERT INTO t1 VALUES
2125
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
2126
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
2127
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
2128
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
2129
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
2130
(24,2,'2005-05-27 12:40:06');
2133
i1 int NOT NULL default '0',
2134
i2 int NOT NULL default '0',
2135
t datetime default NULL,
2138
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
2142
WHERE t1.t = (SELECT t1.t FROM t1
2143
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
2144
ORDER BY t1.t DESC LIMIT 1);
2146
WHERE t1.t = (SELECT t1.t FROM t1
2147
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
2148
ORDER BY t1.t DESC LIMIT 1);
2153
# Bug#14654 : Cannot select from the same table twice within a UNION
2156
CREATE TABLE t1 (i INT);
2158
(SELECT i FROM t1) UNION (SELECT i FROM t1);
2159
SELECT * FROM t1 WHERE NOT EXISTS
2161
(SELECT i FROM t1) UNION
2166
--error ER_PARSE_ERROR
2168
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
2172
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
2175
explain select * from t1 where not exists
2176
((select t11.i from t1 t11) union (select t12.i from t1 t12));
2181
# Bug #21540: Subqueries with no from and aggregate functions return
2183
CREATE TABLE t1 (a INT, b INT);
2184
CREATE TABLE t2 (a INT);
2185
INSERT INTO t2 values (1);
2186
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
2187
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
2188
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
2189
FROM t1 GROUP BY t1.a;
2190
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
2194
SELECT COUNT(DISTINCT t1.b)
2197
FROM t1 GROUP BY t1.a LIMIT 1)
2203
# Bug #21727: Correlated subquery that requires filesort:
2204
# slow with big sort_buffer_size
2207
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
2208
CREATE TABLE t2 (x int auto_increment, y int, z int,
2209
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
2216
eval INSERT INTO t1(a) VALUES(RAND()*1000);
2217
eval SELECT MAX(b) FROM t1 INTO @id;
2221
eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000);
2230
SET SESSION sort_buffer_size = 32 * 1024;
2232
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
2235
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
2237
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
2243
# Bug #25219: EXIST subquery with UNION over a mix of
2244
# correlated and uncorrelated selects
2247
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
2248
CREATE TABLE t2 (c int);
2250
INSERT INTO t1 VALUES ('aa', 1);
2251
INSERT INTO t2 VALUES (1);
2254
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
2256
SELECT c from t2 WHERE c=t1.c);
2258
INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
2261
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
2263
SELECT c from t2 WHERE c=t1.c);
2265
INSERT INTO t2 VALUES (2);
2266
CREATE TABLE t3 (c int);
2267
INSERT INTO t3 VALUES (1);
2270
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
2272
SELECT c from t2 WHERE c=t1.c);
2274
DROP TABLE t1,t2,t3;
2277
# Bug#21904 (parser problem when using IN with a double "(())")
2281
DROP TABLE IF EXISTS t1;
2282
DROP TABLE IF EXISTS t2;
2283
DROP TABLE IF EXISTS t1xt2;
2288
t varchar(4) DEFAULT NULL
2293
t varchar(4) DEFAULT NULL
2296
CREATE TABLE t1xt2 (
2301
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
2303
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
2305
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
2307
# subselect returns 0 rows
2309
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2310
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2312
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2313
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2315
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2316
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2318
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2319
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2321
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2322
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
2324
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2325
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
2327
insert INTO t1xt2 VALUES (1, 12);
2329
# subselect returns 1 row
2331
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2332
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2334
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2335
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2337
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2338
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2340
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2341
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2343
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2344
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2346
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2347
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2349
insert INTO t1xt2 VALUES (2, 12);
2351
# subselect returns more than 1 row
2353
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2354
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2356
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2357
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2359
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2360
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2362
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2363
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2365
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2366
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2368
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2369
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2376
# Bug #26728: derived table with concatanation of literals in select list
2379
CREATE TABLE t1 (a int);
2380
INSERT INTO t1 VALUES (3), (1), (2);
2382
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
2383
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
2388
# Bug #27257: COUNT(*) aggregated in outer query
2391
CREATE TABLE t1 (a int, b int);
2392
CREATE TABLE t2 (m int, n int);
2393
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2394
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2397
(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
2401
(SELECT MIN(m) FROM t2 WHERE m = count(*))
2406
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
2411
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument
2414
CREATE TABLE t1 (a int, b int);
2415
CREATE TABLE t2 (m int, n int);
2416
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2417
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2419
SELECT COUNT(*) c, a,
2420
(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
2423
SELECT COUNT(*) c, a,
2424
(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
2430
# Bug#27321: Wrong subquery result in a grouping select
2432
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
2433
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
2434
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
2435
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
2438
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
2441
(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
2444
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
2445
FROM t1 WHERE t1.d=0 GROUP BY a;
2448
(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2449
LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2453
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2455
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2456
FROM t1 as tt GROUP BY tt.a;
2459
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2461
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
2462
FROM t1 as tt GROUP BY tt.a;
2467
# Bug #27363: nested aggregates in outer, subquery / sum(select
2470
CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
2471
CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
2473
--error ER_INVALID_GROUP_FUNC_USE
2474
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
2475
--error ER_INVALID_GROUP_FUNC_USE
2476
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
2478
--error ER_INVALID_GROUP_FUNC_USE
2480
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
2483
--error ER_INVALID_GROUP_FUNC_USE
2485
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
2491
# Bug #27807: Server crash when executing subquery with EXPLAIN
2493
CREATE TABLE t1 (a int, b int, KEY (a));
2494
INSERT INTO t1 VALUES (1,1),(2,1);
2495
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
2499
# Bug #28377: grouping query with a correlated subquery in WHERE condition
2502
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
2503
INSERT INTO t1 VALUES
2504
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
2505
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
2506
INSERT INTO t2 VALUES (7), (5), (1), (3);
2508
SELECT id, st FROM t1
2509
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
2510
SELECT id, st FROM t1
2511
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
2514
SELECT id, st FROM t1
2515
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
2516
SELECT id, st FROM t1
2517
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
2523
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
2524
# over a grouping subselect
2527
CREATE TABLE t1 (a int);
2529
INSERT INTO t1 VALUES (1), (2);
2532
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
2537
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1
2541
a varchar(255) default NULL,
2542
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2546
a varchar(255) default NULL
2549
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
2550
INSERT INTO t1 SELECT * FROM t1;
2551
INSERT INTO t1 SELECT * FROM t1;
2552
INSERT INTO t1 SELECT * FROM t1;
2553
INSERT INTO t1 SELECT * FROM t1;
2554
INSERT INTO t1 SELECT * FROM t1;
2555
INSERT INTO t1 SELECT * FROM t1;
2556
INSERT INTO t1 SELECT * FROM t1;
2557
INSERT INTO t1 SELECT * FROM t1;
2558
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
2559
INSERT INTO `t2` VALUES ('abcdefghijk');
2560
INSERT INTO `t2` VALUES ('asdf');
2562
SET session sort_buffer_size=8192;
2564
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
2570
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
2573
CREATE TABLE t1 (a INTEGER, b INTEGER);
2574
CREATE TABLE t2 (x INTEGER);
2575
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
2576
INSERT INTO t2 VALUES (1), (2);
2578
# wasn't failing, but should
2579
--error ER_SUBQUERY_NO_1_ROW
2580
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
2582
# fails as it should
2583
--error ER_SUBQUERY_NO_1_ROW
2584
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
2586
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
2589
# second test case from 27333
2590
CREATE TABLE t1 (a INT, b INT);
2591
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
2593
# returns no rows, when it should
2594
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
2595
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
2599
#test cases from 29297
2600
CREATE TABLE t1 (a INT);
2601
CREATE TABLE t2 (a INT);
2602
INSERT INTO t1 VALUES (1),(2);
2603
INSERT INTO t2 VALUES (1),(2);
2604
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
2605
--error ER_SUBQUERY_NO_1_ROW
2606
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
2607
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
2611
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
2614
CREATE TABLE t1 (a1 INT, a2 INT);
2615
CREATE TABLE t2 (b1 INT, b2 INT);
2617
INSERT INTO t1 VALUES (100, 200);
2618
INSERT INTO t1 VALUES (101, 201);
2619
INSERT INTO t2 VALUES (101, 201);
2620
INSERT INTO t2 VALUES (103, 203);
2622
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
2626
# Bug #30788: Inconsistent retrieval of char/varchar
2629
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
2630
INSERT INTO t1 VALUES ('a', 'aa');
2631
INSERT INTO t1 VALUES ('a', 'aaa');
2632
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2633
CREATE INDEX I1 ON t1 (a);
2634
CREATE INDEX I2 ON t1 (b);
2635
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2636
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2638
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
2639
INSERT INTO t2 SELECT * FROM t1;
2640
CREATE INDEX I1 ON t2 (a);
2641
CREATE INDEX I2 ON t2 (b);
2642
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2643
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2645
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2646
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2651
# Bug #32400: Complex SELECT query returns correct result only on some
2655
CREATE TABLE t1(a INT, b INT);
2656
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
2658
--error ER_BAD_FIELD_ERROR
2660
SELECT a AS out_a, MIN(b) FROM t1
2661
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
2664
--error ER_BAD_FIELD_ERROR
2665
SELECT a AS out_a, MIN(b) FROM t1
2666
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
2670
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
2671
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
2674
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
2675
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
2682
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
2685
CREATE TABLE t1 (a INT);
2686
CREATE TABLE t2 (a INT);
2688
INSERT INTO t1 VALUES (1),(2);
2689
INSERT INTO t2 VALUES (1),(2);
2691
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
2693
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
2697
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
2698
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
2703
# BUG#33794 "MySQL crashes executing specific query on specific dump"
2706
f7 varchar(32) collate utf8_bin NOT NULL default '',
2707
f10 varchar(32) collate utf8_bin default NULL,
2710
INSERT INTO t4 VALUES(1,1), (2,null);
2713
f4 varchar(32) collate utf8_bin NOT NULL default '',
2714
f2 varchar(50) collate utf8_bin default NULL,
2715
f3 varchar(10) collate utf8_bin default NULL,
2719
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
2722
f8 varchar(32) collate utf8_bin NOT NULL default '',
2723
f1 varchar(10) collate utf8_bin default NULL,
2724
f9 varchar(32) collate utf8_bin default NULL,
2727
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
2730
f6 varchar(32) collate utf8_bin NOT NULL default '',
2731
f5 varchar(50) collate utf8_bin default NULL,
2734
INSERT INTO t3 VALUES (1,null), (2,null);
2737
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
2738
IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
2742
FROM t2 VPC, t4 a2, t2 a3
2744
VPC.f4 = a2.f10 AND a3.f2 = a4
2751
t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
2754
DROP TABLE t1, t2, t3, t4;
2756
--echo End of 5.0 tests.
2759
# Test [NOT] IN truth table (both as top-level and general predicate).
2762
create table t_out (subcase char(3),
2763
a1 char(2), b1 char(2), c1 char(2));
2764
create table t_in (a2 char(2), b2 char(2), c2 char(2));
2766
insert into t_out values ('A.1','2a', NULL, '2a');
2767
#------------------------- A.2 - impossible
2768
insert into t_out values ('A.3', '2a', NULL, '2a');
2769
insert into t_out values ('A.4', '2a', NULL, 'xx');
2770
insert into t_out values ('B.1', '2a', '2a', '2a');
2771
insert into t_out values ('B.2', '2a', '2a', '2a');
2772
insert into t_out values ('B.3', '3a', 'xx', '3a');
2773
insert into t_out values ('B.4', 'xx', '3a', '3a');
2775
insert into t_in values ('1a', '1a', '1a');
2776
insert into t_in values ('2a', '2a', '2a');
2777
insert into t_in values (NULL, '2a', '2a');
2778
insert into t_in values ('3a', NULL, '3a');
2780
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
2781
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
2782
from t_out where subcase = 'A.1';
2786
(a1, b1, c1) IN (select * from t_in) pred_in,
2787
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2788
from t_out where subcase = 'A.3';
2791
(a1, b1, c1) IN (select * from t_in) pred_in,
2792
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2793
from t_out where subcase = 'A.4';
2796
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
2797
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
2798
from t_out where subcase = 'B.1';
2801
(a1, b1, c1) IN (select * from t_in) pred_in,
2802
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2803
from t_out where subcase = 'B.2';
2806
(a1, b1, c1) IN (select * from t_in) pred_in,
2807
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2808
from t_out where subcase = 'B.3';
2811
(a1, b1, c1) IN (select * from t_in) pred_in,
2812
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2813
from t_out where subcase = 'B.4';
2815
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2816
where subcase = 'A.1' and
2817
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
2819
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2820
where subcase = 'A.1' and
2821
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
2823
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2824
where subcase = 'A.1' and
2825
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
2827
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2828
where subcase = 'A.3' and
2829
(a1, b1, c1) IN (select * from t_in);
2831
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2832
where subcase = 'A.3' and
2833
(a1, b1, c1) NOT IN (select * from t_in);
2835
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2836
where subcase = 'A.3' and
2837
NOT((a1, b1, c1) IN (select * from t_in));
2838
# test non-top level result indirectly
2839
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
2840
where subcase = 'A.3' and
2841
((a1, b1, c1) IN (select * from t_in)) is NULL and
2842
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
2844
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2845
where subcase = 'A.4' and
2846
(a1, b1, c1) IN (select * from t_in);
2848
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2849
where subcase = 'A.4' and
2850
(a1, b1, c1) NOT IN (select * from t_in);
2852
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2853
where subcase = 'A.4' and
2854
NOT((a1, b1, c1) IN (select * from t_in));
2856
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2857
where subcase = 'B.1' and
2858
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
2860
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2861
where subcase = 'B.1' and
2862
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
2864
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2865
where subcase = 'B.1' and
2866
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
2868
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2869
where subcase = 'B.2' and
2870
(a1, b1, c1) IN (select * from t_in);
2872
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2873
where subcase = 'B.2' and
2874
(a1, b1, c1) NOT IN (select * from t_in);
2876
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2877
where subcase = 'B.2' and
2878
NOT((a1, b1, c1) IN (select * from t_in));
2880
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2881
where subcase = 'B.3' and
2882
(a1, b1, c1) IN (select * from t_in);
2884
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2885
where subcase = 'B.3' and
2886
(a1, b1, c1) NOT IN (select * from t_in);
2888
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2889
where subcase = 'B.3' and
2890
NOT((a1, b1, c1) IN (select * from t_in));
2891
# test non-top level result indirectly
2892
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
2893
where subcase = 'B.3' and
2894
((a1, b1, c1) IN (select * from t_in)) is NULL and
2895
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
2897
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2898
where subcase = 'B.4' and
2899
(a1, b1, c1) IN (select * from t_in);
2901
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2902
where subcase = 'B.4' and
2903
(a1, b1, c1) NOT IN (select * from t_in);
2905
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2906
where subcase = 'B.4' and
2907
NOT((a1, b1, c1) IN (select * from t_in));
2914
# Bug#20835 (literal string with =any values)
2916
CREATE TABLE t1 (s1 char(1));
2917
INSERT INTO t1 VALUES ('a');
2918
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
2922
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
2924
CREATE TABLE t1( a INT );
2925
INSERT INTO t1 VALUES (1),(2);
2927
CREATE TABLE t2( a INT, b INT );
2929
--error ER_PARSE_ERROR
2931
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
2932
--error ER_PARSE_ERROR
2934
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
2935
--error ER_PARSE_ERROR
2937
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
2939
--error ER_PARSE_ERROR
2943
SELECT a INTO @var FROM t1 WHERE a = 2
2946
--error ER_PARSE_ERROR
2950
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
2953
--error ER_PARSE_ERROR
2957
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
2960
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
2963
SELECT a FROM t1 WHERE a = 2
2965
SELECT a FROM t1 WHERE a = 2
2971
SELECT a FROM t1 WHERE a = 2
2973
SELECT a FROM t1 WHERE a = 2
2976
# This was not allowed previously. Possibly, it should be allowed on the future.
2977
# For now, the intent is to keep the fix as non-intrusive as possible.
2978
--error ER_PARSE_ERROR
2979
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
2980
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
2981
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
2982
--error ER_PARSE_ERROR
2983
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
2984
--error ER_PARSE_ERROR
2985
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
2986
--error ER_PARSE_ERROR
2987
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
2989
--error ER_PARSE_ERROR
2990
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
2991
--error ER_PARSE_ERROR
2992
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
2993
--error ER_PARSE_ERROR
2994
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
2996
--error ER_PARSE_ERROR
2997
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
2998
--error ER_PARSE_ERROR
2999
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
3000
--error ER_PARSE_ERROR
3001
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
3003
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
3004
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;
3005
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
3006
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
3009
# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias
3010
# UNION should not be allowed inside the parentheses, nor should
3013
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
3014
--error ER_PARSE_ERROR
3015
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
3016
--error ER_PARSE_ERROR
3017
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
3018
--error ER_PARSE_ERROR
3019
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
3020
--error ER_PARSE_ERROR
3021
SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;
3022
--error ER_PARSE_ERROR
3023
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
3025
SELECT * FROM t1 JOIN (t1 t1a) ON 1;
3026
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
3028
SELECT * FROM (t1 t1a);
3029
SELECT * FROM ((t1 t1a));
3031
SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;
3032
SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;
3034
SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;
3035
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
3037
# For the join, TABLE_LIST::select_lex == NULL
3038
# Check that we handle this.
3039
--error ER_PARSE_ERROR
3040
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
3042
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
3043
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );
3044
SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );
3046
--error ER_PARSE_ERROR
3047
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
3048
--error ER_PARSE_ERROR
3049
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
3050
--error ER_PARSE_ERROR
3051
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3053
SELECT * FROM t1 WHERE a = ( SELECT 1 );
3054
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );
3055
--error ER_PARSE_ERROR
3056
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
3057
--error ER_PARSE_ERROR
3058
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
3059
--error ER_PARSE_ERROR
3060
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
3062
--error ER_PARSE_ERROR
3063
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
3064
--error ER_PARSE_ERROR
3065
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
3066
--error ER_PARSE_ERROR
3067
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3069
--error ER_PARSE_ERROR
3070
SELECT ( SELECT 1 INTO @v );
3071
--error ER_PARSE_ERROR
3072
SELECT ( SELECT 1 INTO OUTFILE 'file' );
3073
--error ER_PARSE_ERROR
3074
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
3076
--error ER_PARSE_ERROR
3077
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
3078
--error ER_PARSE_ERROR
3079
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
3080
--error ER_PARSE_ERROR
3081
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3083
# Make sure context is popped when we leave the nested select
3084
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
3085
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
3087
# Make sure we have feature F561 (see .yy file)
3088
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
3090
# Make sure the parser does not allow nested UNIONs anywhere
3092
--error ER_PARSE_ERROR
3093
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
3094
--error ER_PARSE_ERROR
3095
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
3097
--error ER_PARSE_ERROR
3098
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3099
--error ER_PARSE_ERROR
3100
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
3101
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3102
SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
3104
--error ER_PARSE_ERROR
3105
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3106
--error ER_PARSE_ERROR
3107
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3108
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
3110
--error ER_PARSE_ERROR
3111
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3112
--error ER_PARSE_ERROR
3113
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3114
--error ER_PARSE_ERROR
3115
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3116
--error ER_PARSE_ERROR
3117
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3119
--error ER_PARSE_ERROR
3120
SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3121
--error ER_PARSE_ERROR
3122
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3123
--error ER_PARSE_ERROR
3124
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3125
--error ER_PARSE_ERROR
3126
SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3128
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3129
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3130
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3131
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3133
--error ER_PARSE_ERROR
3134
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
3135
SELECT EXISTS(SELECT 1+1);
3136
--error ER_PARSE_ERROR
3137
SELECT EXISTS(SELECT 1+1 INTO @test);
3138
--error ER_PARSE_ERROR
3139
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
3141
--error ER_PARSE_ERROR
3142
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
3143
--error ER_PARSE_ERROR
3144
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
added
removed
tests/t/subselect.test
