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 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 NOT NULL auto_increment,
175
`maxnumrep` int 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 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 NOT NULL auto_increment,
216
`maxnumrep` int 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 NOT NULL default '0',
240
`numreponse` int 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);
296
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
297
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
298
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
302
drop table t11, t12, t2;
304
#insert with subselects
305
CREATE TABLE t1 (x int);
306
create table t2 (a int);
307
create table t3 (b int);
308
insert into t2 values (1);
309
insert into t3 values (1),(2);
310
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
311
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
312
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
314
insert into t2 values (1);
315
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
317
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
319
# After this, only data based on old t1 records should have been added.
320
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
322
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
323
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
326
#TODO: should be uncommented after bug 380 fix pushed
327
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
329
drop table t1, t2, t3;
331
#replace with subselects
332
CREATE TABLE t1 (x int not null, y int, primary key (x));
333
create table t2 (a int);
334
create table t3 (a int);
335
insert into t2 values (1);
336
insert into t3 values (1),(2);
338
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
339
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
340
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
342
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
344
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
347
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
349
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
352
drop table t1, t2, t3;
354
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
356
CREATE TABLE t2 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
357
INSERT INTO t2 VALUES (1),(2);
358
SELECT * FROM t2 WHERE id IN (SELECT 1);
359
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
360
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
361
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
362
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
363
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
364
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
365
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
366
INSERT INTO t2 VALUES ((SELECT * FROM t2));
367
INSERT INTO t2 VALUES ((SELECT id FROM t2));
369
CREATE TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
370
INSERT INTO t1 values (1),(1);
371
UPDATE t2 SET id=(SELECT * FROM t1);
375
create table t1 (a int);
376
insert into t1 values (1),(2),(3);
377
select 1 IN (SELECT * from t1);
378
select 10 IN (SELECT * from t1);
379
select NULL IN (SELECT * from t1);
380
update t1 set a=NULL where a=2;
381
select 1 IN (SELECT * from t1);
382
select 3 IN (SELECT * from t1);
383
select 10 IN (SELECT * from t1);
384
select 1 > ALL (SELECT * from t1);
385
select 10 > ALL (SELECT * from t1);
386
select 1 > ANY (SELECT * from t1);
387
select 10 > ANY (SELECT * from t1);
389
create table t1 (a varchar(20));
390
insert into t1 values ('A'),('BC'),('DEF');
391
select 'A' IN (SELECT * from t1);
392
select 'XYZS' IN (SELECT * from t1);
393
select NULL IN (SELECT * from t1);
394
update t1 set a=NULL where a='BC';
395
select 'A' IN (SELECT * from t1);
396
select 'DEF' IN (SELECT * from t1);
397
select 'XYZS' IN (SELECT * from t1);
398
select 'A' > ALL (SELECT * from t1);
399
select 'XYZS' > ALL (SELECT * from t1);
400
select 'A' > ANY (SELECT * from t1);
401
select 'XYZS' > ANY (SELECT * from t1);
403
create table t1 (a float);
404
insert into t1 values (1.5),(2.5),(3.5);
405
select 1.5 IN (SELECT * from t1);
406
select 10.5 IN (SELECT * from t1);
407
select NULL IN (SELECT * from t1);
408
update t1 set a=NULL where a=2.5;
409
select 1.5 IN (SELECT * from t1);
410
select 3.5 IN (SELECT * from t1);
411
select 10.5 IN (SELECT * from t1);
412
select 1.5 > ALL (SELECT * from t1);
413
select 10.5 > ALL (SELECT * from t1);
414
select 1.5 > ANY (SELECT * from t1);
415
select 10.5 > ANY (SELECT * from t1);
416
explain extended select (select a+1) from t1;
418
select (select a+1) from t1;
425
CREATE TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a));
426
CREATE TABLE t2 (a int default '0', INDEX (a));
427
INSERT INTO t1 VALUES (1),(2),(3),(4);
428
INSERT INTO t2 VALUES (1),(2),(3);
429
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
430
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
431
CREATE TABLE t3 (a int default '0');
432
INSERT INTO t3 VALUES (1),(2),(3);
433
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
434
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
437
#LIMIT is not supported now
438
#create table t1 (a float);
440
#select 10.5 IN (SELECT * from t1 LIMIT 1);
442
#select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
445
#create table t1 (a int, b int, c varchar(10));
446
#create table t2 (a int);
447
#insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
448
#insert into t2 values (1),(2),(NULL);
449
#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;
450
#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;
451
#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;
454
#create table t1 (a int, b real, c varchar(10));
455
#insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
456
#select ROW(1, 1, 'a') IN (select a,b,c from t1);
457
#select ROW(1, 2, 'a') IN (select a,b,c from t1);
458
#select ROW(1, 1, 'a') IN (select b,a,c from t1);
459
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
460
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
461
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
462
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
463
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
464
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
466
#select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
469
#test of uncacheable subqueries
470
CREATE TABLE t1 (a int);
471
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
472
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
477
`mot` varchar(30) NOT NULL default '',
478
`topic` bigint NOT NULL default '0',
479
`date` date NOT NULL default '0000-00-00',
480
`pseudo` varchar(35) NOT NULL default '',
481
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
482
KEY `pseudo` (`pseudo`,`date`,`topic`),
483
KEY `topic` (`topic`)
484
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
487
`mot` varchar(30) NOT NULL default '',
488
`topic` bigint NOT NULL default '0',
489
`date` date NOT NULL default '0000-00-00',
490
`pseudo` varchar(35) NOT NULL default '',
491
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
492
KEY `pseudo` (`pseudo`,`date`,`topic`),
493
KEY `topic` (`topic`)
494
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
497
`numeropost` bigint NOT NULL auto_increment,
498
`maxnumrep` int NOT NULL default '0',
499
PRIMARY KEY (`numeropost`),
500
UNIQUE KEY `maxnumrep` (`maxnumrep`)
502
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
504
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
506
INSERT INTO t3 VALUES (1,1);
508
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
511
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
512
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
515
drop table t1, t2, t3;
517
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
518
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
519
SHOW CREATE TABLE t1;
521
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
522
SHOW CREATE TABLE t1;
524
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
525
SHOW CREATE TABLE t1;
527
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
529
SHOW CREATE TABLE t1;
532
create table t1 (a int);
533
insert into t1 values (1), (2), (3);
534
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
541
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);
548
ID int NOT NULL auto_increment,
549
name char(35) NOT NULL default '',
550
t2 char(3) NOT NULL default '',
551
District char(20) NOT NULL default '',
552
Population int NOT NULL default '0',
556
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
557
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
558
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
561
Code char(3) NOT NULL default '',
562
Name char(52) NOT NULL default '',
563
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
564
Region char(26) NOT NULL default '',
565
SurfaceArea float(10,2) NOT NULL default '0.00',
566
IndepYear int default NULL,
567
Population int NOT NULL default '0',
568
LifeExpectancy float(3,1) default NULL,
569
GNP float(10,2) default NULL,
570
GNPOld float(10,2) default NULL,
571
LocalName char(45) NOT NULL default '',
572
GovernmentForm char(45) NOT NULL default '',
573
HeadOfState char(60) default NULL,
574
Capital int default NULL,
575
Code2 char(2) NOT NULL default '',
579
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');
580
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');
582
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);
590
`id` bigint NOT NULL auto_increment,
591
`pseudo` varchar(35) NOT NULL default '',
593
UNIQUE KEY `pseudo` (`pseudo`)
594
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
595
INSERT INTO t1 (pseudo) VALUES ('test');
596
SELECT 0 IN (SELECT 1 FROM t1 a);
597
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
598
INSERT INTO t1 (pseudo) VALUES ('test1');
599
SELECT 0 IN (SELECT 1 FROM t1 a);
600
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
604
`i` int NOT NULL default '0',
608
INSERT INTO t1 VALUES (1);
609
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
610
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
611
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
621
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
624
name varchar(15) default NULL
627
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
628
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
633
# correct NULL in <CONSTANT> IN (SELECT ...)
635
create table t1 (a int, unique index indexa (a));
636
insert into t1 values (-1), (-4), (-2), (NULL);
637
select -10 IN (select a from t1 FORCE INDEX (indexa));
641
# Test optimization for sub selects
643
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
644
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
645
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
649
ID int NOT NULL auto_increment,
650
SUB_ID int NOT NULL default '0',
651
REF_ID int default NULL,
652
REF_SUB int default '0',
653
PRIMARY KEY (ID,SUB_ID),
654
UNIQUE KEY t1_PK (ID,SUB_ID),
655
KEY t1_FK (REF_ID,REF_SUB),
656
KEY t1_REFID (REF_ID)
658
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
659
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
663
# uninterruptable update
665
create table t1 (a int, b int);
666
create table t2 (a int, b int);
668
insert into t1 values (1,0), (2,0), (3,0);
669
insert into t2 values (1,1), (2,1), (3,1), (2,2);
671
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
677
# reduced subselect in ORDER BY & GROUP BY clauses
681
`id` bigint NOT NULL auto_increment,
682
`pseudo` varchar(35) NOT NULL default '',
683
`email` varchar(60) NOT NULL default '',
685
UNIQUE KEY `email` (`email`),
686
UNIQUE KEY `pseudo` (`pseudo`)
687
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
688
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
689
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
690
drop table if exists t1;
692
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
695
# IN subselect optimization test
697
create table t1 (a int not null, b int, primary key (a));
698
create table t2 (a int not null, primary key (a));
699
create table t3 (a int not null, b int, primary key (a));
700
insert into t1 values (1,10), (2,20), (3,30), (4,40);
701
insert into t2 values (2), (3), (4), (5);
702
insert into t3 values (10,3), (20,4), (30,5);
703
select * from t2 where t2.a in (select a from t1);
704
explain extended select * from t2 where t2.a in (select a from t1);
705
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
706
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
707
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
708
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
709
drop table t1, t2, t3;
710
create table t1 (a int, b int, index a (a,b));
711
create table t2 (a int, index a (a));
712
create table t3 (a int, b int, index a (a));
713
insert into t1 values (1,10), (2,20), (3,30), (4,40);
715
# making table large enough
721
eval insert into t1 values (rand()*100000+200,rand()*100000);
727
insert into t2 values (2), (3), (4), (5);
728
insert into t3 values (10,3), (20,4), (30,5);
729
select * from t2 where t2.a in (select a from t1);
730
explain extended select * from t2 where t2.a in (select a from t1);
731
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
732
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
733
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
734
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
735
insert into t1 values (3,31);
736
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
737
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
738
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
739
drop table t1, t2, t3;
742
# alloc_group_fields() working
744
create table t1 (a int, b int);
745
create table t2 (a int, b int);
746
create table t3 (a int, b int);
747
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
748
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
749
insert into t3 values (3,3), (2,2), (1,1);
750
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;
754
# aggregate functions in HAVING test
756
create table t1 (s1 int);
757
create table t2 (s1 int);
758
insert into t1 values (1);
759
insert into t2 values (1);
760
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
764
# update subquery with wrong field (to force name resolving
765
# in UPDATE name space)
767
create table t1 (s1 int);
768
create table t2 (s1 int);
769
insert into t1 values (1);
770
insert into t2 values (1);
771
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
777
#CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
778
# s2 CHAR(5) COLLATE latin1_swedish_ci);
779
#INSERT INTO t1 VALUES ('z','?');
781
#select * from t1 where s1 > (select max(s2) from t1);
783
#select * from t1 where s1 > any (select max(s2) from t1);
787
# aggregate functions reinitialization
789
create table t1(toid int,rd int);
790
create table t2(userid int,pmnew int,pmtotal int);
791
insert into t2 values(1,0,0),(2,0,0);
792
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);
793
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);
799
create table t1 (s1 char(5));
800
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
801
insert into t1 values ('tttt');
802
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
803
explain extended (select * from t1);
808
# IN optimisation test results
810
create table t1 (s1 char(5), index s1(s1));
811
create table t2 (s1 char(5), index s1(s1));
812
insert into t1 values ('a1'),('a2'),('a3');
813
insert into t2 values ('a1'),('a2');
814
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
815
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
816
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
817
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
818
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
819
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
820
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
821
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
825
# correct ALL optimisation
827
create table t2 (a int, b int);
828
create table t3 (a int);
829
insert into t3 values (6),(7),(3);
830
select * from t3 where a >= all (select b from t2);
831
explain extended select * from t3 where a >= all (select b from t2);
832
select * from t3 where a >= some (select b from t2);
833
explain extended select * from t3 where a >= some (select b from t2);
834
select * from t3 where a >= all (select b from t2 group by 1);
835
explain extended select * from t3 where a >= all (select b from t2 group by 1);
836
select * from t3 where a >= some (select b from t2 group by 1);
837
explain extended select * from t3 where a >= some (select b from t2 group by 1);
838
select * from t3 where NULL >= any (select b from t2);
839
explain extended select * from t3 where NULL >= any (select b from t2);
840
select * from t3 where NULL >= any (select b from t2 group by 1);
841
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
842
select * from t3 where NULL >= some (select b from t2);
843
explain extended select * from t3 where NULL >= some (select b from t2);
844
select * from t3 where NULL >= some (select b from t2 group by 1);
845
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
847
# optimized static ALL/ANY with grouping
849
insert into t2 values (2,2), (2,1), (3,3), (3,1);
850
select * from t3 where a > all (select max(b) from t2 group by a);
851
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
855
# correct used_tables()
858
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 ;
859
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());
860
CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;
861
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);
862
CREATE TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;
863
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);
864
CREATE TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
865
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
866
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;
867
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;
868
drop table t1,t2,t3,t4;
873
CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM;
874
INSERT INTO t1 VALUES (1),(5);
875
CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM;
876
INSERT INTO t2 VALUES (2),(6);
877
select * from t1 where (1,2,6) in (select * from t2);
881
# optimized ALL/ANY with union
883
create table t1 (s1 char);
884
insert into t1 values ('e');
885
select * from t1 where 'f' > any (select s1 from t1);
886
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
887
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
891
# filesort in subquery (restoring join_tab)
893
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
894
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
895
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
896
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
897
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;
901
# unresolved field error
903
create table t1 (s1 int);
904
create table t2 (s1 int);
905
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
906
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
907
select count(*) from t2 group by t1.s2;
911
# fix_fields() in add_ref_to_table_cond()
913
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
914
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
915
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
916
INSERT INTO t2 VALUES (100, 200, 'C');
917
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
920
CREATE TABLE t1 (a int);
921
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
922
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
930
`id` int NOT NULL auto_increment,
931
`id_cns` int NOT NULL default '0',
932
`tipo` enum('','UNO','DUE') NOT NULL default '',
933
`anno_dep` int NOT NULL default '0',
934
`particolare` bigint NOT NULL default '0',
935
`generale` bigint NOT NULL default '0',
936
`bis` int NOT NULL default '0',
938
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
939
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
941
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);
943
`id` int NOT NULL auto_increment,
944
`max_anno_dep` int NOT NULL default '0',
947
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
949
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;
956
create table t1 (a int);
957
insert into t1 values (1), (2), (3);
958
SET SQL_SELECT_LIMIT=1;
959
select sum(a) from (select * from t1) as a;
960
select 2 in (select * from t1);
961
SET SQL_SELECT_LIMIT=default;
965
# Bug #3118: subselect + order by
968
CREATE TABLE t1 (a int, b int, INDEX (a));
969
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
970
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
973
# Item_cond fix field
975
create table t1(val varchar(10));
976
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
977
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%');
981
# ref_or_null replacing with ref
983
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
984
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');
985
select * from t1 where id not in (select id from t1 where id < 8);
986
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);
987
explain extended select * from t1 where id not in (select id from t1 where id < 8);
988
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);
989
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
990
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
991
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');
992
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);
993
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);
997
# Static tables & rund() in subqueries
999
create table t1 (a int);
1000
insert into t1 values (1);
1001
explain select benchmark(1000, (select a from t1 where a=rand()));
1007
create table t1(id int);
1008
create table t2(id int);
1009
create table t3(flag int);
1010
select (select * from t3 where id not null) from t1, t2;
1011
drop table t1,t2,t3;
1014
# aggregate functions (Bug #3505)
1016
CREATE TABLE t1 (id INT);
1017
CREATE TABLE t2 (id INT);
1018
INSERT INTO t1 VALUES (1), (2);
1019
INSERT INTO t2 VALUES (1);
1020
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);
1021
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1022
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;
1023
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;
1029
CREATE TABLE t1 ( a int, b int );
1030
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1031
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1032
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1033
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1034
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1035
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1036
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1037
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1038
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1039
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1040
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1041
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1042
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1044
ALTER TABLE t1 ADD INDEX (a);
1045
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1046
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1047
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1048
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1049
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1050
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1051
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1052
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1053
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1054
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1055
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1056
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1057
# having clause test
1058
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1059
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
1060
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
1061
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
1062
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
1063
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
1064
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
1065
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
1066
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
1067
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
1068
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
1069
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 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 < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1073
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1074
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1075
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1076
SELECT a FROM t1 WHERE a <> ANY (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
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1079
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1080
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1081
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1082
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1083
# union + having test
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 < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1086
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1087
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1088
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1089
SELECT a FROM t1 WHERE a <> ANY (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);
1091
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1092
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1093
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1094
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1095
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1097
# < > >= <= and = ALL/ <> ANY do not support row operation
1098
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1099
SELECT a FROM t1 WHERE a > ANY (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
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
1102
SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1103
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1104
SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
1105
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
1106
# following should be converted to IN
1107
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
1108
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1109
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1110
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
1111
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1112
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1113
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
1114
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
1115
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
1116
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
1117
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);
1118
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);
1119
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);
1120
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);
1121
# without optimisation
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 < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1124
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1125
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1126
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1127
SELECT a FROM t1 WHERE a <> ANY (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
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1130
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1131
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1132
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1133
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1134
# without optimisation + having
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 < ANY (SELECT a FROM t1 group by a HAVING a = 2);
1137
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
1138
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
1139
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
1140
SELECT a FROM t1 WHERE a <> ANY (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
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
1143
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
1144
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
1145
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
1146
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
1147
# EXISTS in string contence
1148
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
1149
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
1150
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
1152
CREATE TABLE t1 ( a double, b double );
1153
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1154
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
1155
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
1156
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
1157
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
1158
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
1159
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
1160
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
1161
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
1162
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
1163
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
1164
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
1165
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
1167
CREATE TABLE t1 ( a char(1), b char(1));
1168
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
1169
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
1170
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
1171
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
1172
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
1173
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
1174
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
1175
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
1176
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
1177
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
1178
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
1179
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
1180
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
1185
# SELECT(EXISTS * ...)optimisation
1187
create table t1 (a int, b int);
1188
insert into t1 values (1,2),(3,4);
1189
select * from t1 up where exists (select * from t1 where t1.a=up.a);
1190
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
1194
# Bug #4102: subselect in HAVING
1197
CREATE TABLE t1 (t1_a int);
1198
INSERT INTO t1 VALUES (1);
1199
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
1200
INSERT INTO t2 VALUES (1, 1), (1, 2);
1201
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
1202
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
1206
# Test problem with NULL and derived tables (Bug #4097)
1209
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL);
1210
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
1211
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL);
1212
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
1213
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
1217
# Aggregate function comparation with ALL/ANY/SOME subselect
1219
CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
1220
insert into t1 values (1);
1221
CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
1222
insert into t2 values (1,2);
1223
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
1227
# BUG#5003 - like in subselect
1229
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);
1230
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
1231
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
1232
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
1233
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
1234
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
1235
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
1239
# Optimized IN with compound index
1241
CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
1242
CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
1243
insert into t1 values (1,1),(1,2),(2,1),(2,2);
1244
insert into t2 values (1,2),(2,2);
1245
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1246
alter table t2 drop primary key;
1247
alter table t2 add key KEY1 (aid, bid);
1248
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1249
alter table t2 drop key KEY1;
1250
alter table t2 add primary key (bid, aid);
1251
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1255
# resolving fields of grouped outer SELECT
1257
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
1258
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
1259
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
1260
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
1261
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
1262
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
1263
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
1264
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
1267
create table t1 (x int);
1268
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;
1272
# Test of correct maybe_null flag returning by subquwery for temporary table
1275
CREATE TABLE `t1` ( `master` int NOT NULL default '0', `map` int NOT NULL default '0', `slave` int NOT NULL default '0', `access` int NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
1276
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);
1277
CREATE TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
1278
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');
1279
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;
1280
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;
1284
# Subselect in non-select command just after connection
1286
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
1288
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
1291
# primary query with temporary table and subquery with groupping
1293
create table t1 (a int, b int);
1294
create table t2 (a int, b int);
1295
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
1296
insert into t2 values (1,3),(2,1);
1297
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
1301
# Equal operation under row and empty subquery
1303
create table t1 (s1 int,s2 int);
1304
insert into t1 values (20,15);
1305
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
1311
create table t1 (s1 int);
1312
insert into t1 values (1),(null);
1313
select * from t1 where s1 < all (select s1 from t1);
1314
select s1, s1 < all (select s1 from t1) from t1;
1318
# reference on changable fields from subquery
1321
Code char(3) NOT NULL default '',
1322
Name char(52) NOT NULL default '',
1323
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1324
Region char(26) NOT NULL default '',
1325
SurfaceArea float(10,2) NOT NULL default '0.00',
1326
IndepYear int default NULL,
1327
Population int NOT NULL default '0',
1328
LifeExpectancy float(3,1) default NULL,
1329
GNP float(10,2) default NULL,
1330
GNPOld float(10,2) default NULL,
1331
LocalName char(45) NOT NULL default '',
1332
GovernmentForm char(45) NOT NULL default '',
1333
HeadOfState char(60) default NULL,
1334
Capital int default NULL,
1335
Code2 char(2) NOT NULL default ''
1337
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
1338
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');
1339
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');
1340
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');
1341
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
1342
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
1343
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
1348
# Test for BUG#7885: Server crash when 'any' subselect compared to
1349
# non-existant field.
1351
create table t1 (a1 int);
1352
create table t2 (b1 int);
1354
select * from t1 where a2 > any(select b1 from t2);
1355
select * from t1 where a1 > any(select b1 from t2);
1360
# Comparison subquery with * and row
1362
create table t1 (a integer, b integer);
1363
select (select * from t1) = (select 1,2);
1364
select (select 1,2) = (select * from t1);
1365
# queries whih can be converted to IN
1366
select row(1,2) = ANY (select * from t1);
1367
select row(1,2) != ALL (select * from t1);
1371
# Comparison subquery and row with nested rows
1373
create table t1 (a integer, b integer);
1374
select row(1,(2,2)) in (select * from t1 );
1375
select row(1,(2,2)) = (select * from t1 );
1376
select (select * from t1) = row(1,(2,2));
1380
# Forward reference detection
1382
create table t1 (a integer);
1383
insert into t1 values (1);
1384
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
1385
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
1386
select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );
1387
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
1394
categoryId int NOT NULL,
1395
courseId int NOT NULL,
1396
startDate datetime NOT NULL,
1397
endDate datetime NOT NULL,
1398
createDate datetime NOT NULL,
1399
modifyDate timestamp NOT NULL,
1400
attributes text NOT NULL
1402
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
1403
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1404
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1405
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
1406
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
1407
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1408
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1409
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
1410
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
1413
userId int NOT NULL,
1414
courseId int NOT NULL,
1415
date datetime NOT NULL
1417
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
1418
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
1419
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
1420
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
1421
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
1422
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
1426
groupId int NOT NULL,
1427
parentId int NOT NULL,
1428
startDate datetime NOT NULL,
1429
endDate datetime NOT NULL,
1430
createDate datetime NOT NULL,
1431
modifyDate timestamp NOT NULL,
1434
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
1438
groupTypeId int NOT NULL,
1439
groupKey varchar(50) NOT NULL,
1443
createDate datetime NOT NULL,
1444
modifyDate timestamp NOT NULL
1446
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
1447
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
1450
userId int NOT NULL,
1451
groupId int NOT NULL,
1452
createDate datetime NOT NULL,
1453
modifyDate timestamp NOT NULL
1455
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
1458
count(distinct t2.userid) pass,
1460
count(t2.courseid) crse,
1463
date_format(date, '%b%y') as colhead
1465
join t1 on t2.courseid=t1.courseid
1478
select t4.id as parentid,
1479
t4.name as parentgroup,
1481
t4.name as groupname,
1484
) as gin on t5.groupid=gin.childid
1485
) as groupstuff on t2.userid = groupstuff.userid
1487
groupstuff.groupname, colhead , t2.courseid;
1489
drop table t1, t2, t3, t4, t5;
1492
# Transformation in left expression of subquery (BUG#8888)
1494
create table t1 (a int);
1495
insert into t1 values (1), (2), (3);
1496
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
1500
# single row subqueries and row operations (code covarage improvement)
1502
create table t1 (a int, b int);
1503
insert into t1 values (1,2);
1504
select 1 = (select * from t1);
1505
select (select * from t1) = 1;
1506
select (1,2) = (select a from t1);
1507
select (select a from t1) = (1,2);
1508
select (1,2,3) = (select * from t1);
1509
select (select * from t1) = (1,2,3);
1513
# Item_int_with_ref check (BUG#10020)
1515
#CREATE TABLE `t1` (
1516
# `itemid` bigint NOT NULL auto_increment,
1517
# `sessionid` bigint default NULL,
1518
# `time` int NOT NULL default '0',
1519
# `data` text collate latin1_general_ci NOT NULL,
1520
# PRIMARY KEY (`itemid`)
1522
#INSERT INTO `t1` VALUES (1, 1, 1, '');
1523
#CREATE TABLE `t2` (
1524
# `sessionid` bigint NOT NULL auto_increment,
1525
# `pid` int NOT NULL default '0',
1526
# `date` int NOT NULL default '0',
1527
# `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
1528
# PRIMARY KEY (`sessionid`)
1530
#INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
1531
#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;
1534
# BUG#11821 : Select from subselect using aggregate function on an enum
1536
create table t1 (fld enum('0','1'));
1537
insert into t1 values ('1');
1538
select * from (select max(fld) from t1) as foo;
1542
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
1545
CREATE TABLE t1 (one int, two int, flag char(1));
1546
CREATE TABLE t2 (one int, two int, flag char(1));
1547
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1548
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1551
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
1553
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
1555
insert into t2 values (null,null,'N');
1556
insert into t2 values (null,3,'0');
1557
insert into t2 values (null,5,'0');
1558
insert into t2 values (10,null,'0');
1559
insert into t1 values (10,3,'0');
1560
insert into t1 values (10,5,'0');
1561
insert into t1 values (10,10,'0');
1562
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
1563
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
1564
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
1565
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
1566
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
1567
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
1568
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
1569
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;
1573
# Bug #12392: where cond with IN predicate for rows and NULL values in table
1576
CREATE TABLE t1 (a char(5), b char(5));
1577
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
1579
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
1584
# Bug #11479: subquery over left join with an empty inner table
1587
CREATE TABLE t1 (a int);
1588
CREATE TABLE t2 (a int, b int);
1589
CREATE TABLE t3 (b int NOT NULL);
1590
INSERT INTO t1 VALUES (1), (2), (3), (4);
1591
INSERT INTO t2 VALUES (1,10), (3,30);
1593
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
1594
WHERE t3.b IS NOT NULL OR t2.a > 10;
1596
WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
1597
WHERE t3.b IS NOT NULL OR t2.a > 10);
1599
DROP TABLE t1,t2,t3;
1602
# Bug#18503: Queries with a quantified subquery returning empty set may
1603
# return a wrong result.
1605
CREATE TABLE t1 (f1 INT);
1606
CREATE TABLE t2 (f2 INT);
1607
INSERT INTO t1 VALUES (1);
1608
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
1609
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
1610
INSERT INTO t2 VALUES (1);
1611
INSERT INTO t2 VALUES (2);
1612
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
1615
# BUG#20975 Wrong query results for subqueries within NOT
1616
create table t1 (s1 char);
1617
insert into t1 values (1),(2);
1619
select * from t1 where (s1 < any (select s1 from t1));
1620
select * from t1 where not (s1 < any (select s1 from t1));
1622
select * from t1 where (s1 < ALL (select s1+1 from t1));
1623
select * from t1 where not(s1 < ALL (select s1+1 from t1));
1625
select * from t1 where (s1+1 = ANY (select s1 from t1));
1626
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
1628
select * from t1 where (s1 = ALL (select s1/s1 from t1));
1629
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
1633
# Bug #16255: Subquery in where
1636
retailerID varchar(8) NOT NULL,
1637
statusID int NOT NULL,
1638
changed datetime NOT NULL,
1639
UNIQUE KEY retailerID (retailerID, statusID, changed)
1642
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
1643
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
1644
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
1645
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
1646
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
1647
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
1650
where (r1.retailerID,(r1.changed)) in
1651
(SELECT r2.retailerId,(max(changed)) from t1 r2
1652
group by r2.retailerId);
1656
# Bug #21180: Subselect with index for both WHERE and ORDER BY
1657
# produces empty result
1659
create table t1(a int, primary key (a));
1660
insert into t1 values (10);
1662
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
1663
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
1665
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1666
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1667
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
1668
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1669
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1670
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
1672
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1673
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1674
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
1675
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1676
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1677
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
1682
# Bug #21853: assert failure for a grouping query with
1683
# an ALL/ANY quantified subquery in HAVING
1687
field1 int NOT NULL,
1688
field2 int NOT NULL,
1689
field3 int NOT NULL,
1690
PRIMARY KEY (field1,field2,field3)
1693
fieldA int NOT NULL,
1694
fieldB int NOT NULL,
1695
PRIMARY KEY (fieldA,fieldB)
1698
INSERT INTO t1 VALUES
1699
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
1700
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
1702
SELECT field1, field2, COUNT(*)
1703
FROM t1 GROUP BY field1, field2;
1705
SELECT field1, field2
1707
GROUP BY field1, field2
1708
HAVING COUNT(*) >= ALL (SELECT fieldB
1709
FROM t2 WHERE fieldA = field1);
1710
SELECT field1, field2
1712
GROUP BY field1, field2
1713
HAVING COUNT(*) < ANY (SELECT fieldB
1714
FROM t2 WHERE fieldA = field1);
1719
# Bug #23478: not top-level IN subquery returning a non-empty result set
1720
# with possible NULL values by index access from the outer query
1723
CREATE TABLE t1(a int, INDEX (a));
1724
INSERT INTO t1 VALUES (1), (3), (5), (7);
1725
INSERT INTO t1 VALUES (NULL);
1727
CREATE TABLE t2(a int);
1728
INSERT INTO t2 VALUES (1),(2),(3);
1730
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
1731
SELECT a, a IN (SELECT a FROM t1) FROM t2;
1736
# Bug #11302: getObject() returns a String for a sub-query of type datetime
1738
CREATE TABLE t1 (a DATETIME);
1739
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
1741
CREATE TABLE t2 AS SELECT
1742
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
1743
FROM t1 WHERE a > '2000-01-01';
1744
SHOW CREATE TABLE t2;
1746
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
1747
SHOW CREATE TABLE t3;
1749
DROP TABLE t1,t2,t3;
1752
# Bug 24653: sorting by expressions containing subselects
1753
# that return more than one row
1756
CREATE TABLE t1 (a int);
1757
INSERT INTO t1 VALUES (2), (4), (1), (3);
1759
CREATE TABLE t2 (b int, c int);
1760
INSERT INTO t2 VALUES
1761
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
1763
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
1765
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
1766
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
1768
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
1770
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
1772
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
1775
SELECT a FROM t1 GROUP BY a
1776
HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
1777
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1779
SELECT a FROM t1 GROUP BY a
1780
HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
1781
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1783
SELECT a FROM t1 GROUP BY a
1784
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
1785
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1787
SELECT a FROM t1 GROUP BY a
1788
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
1789
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
1792
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
1793
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
1796
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
1797
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
1800
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
1801
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
1804
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
1805
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
1812
#decimal-related tests
1814
create table t1 (df decimal(5,1));
1815
insert into t1 values(1.1);
1816
insert into t1 values(2.2);
1818
select * from t1 where df <= all (select avg(df) from t1 group by df);
1819
select * from t1 where df >= all (select avg(df) from t1 group by df);
1822
create table t1 (df decimal(5,1));
1823
insert into t1 values(1.1);
1824
select 1.1 * exists(select * from t1);
1828
grp int default NULL,
1829
a decimal(10,2) default NULL);
1831
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
1833
select min(a) from t1 group by grp;
1837
# Test for bug #9338: lame substitution of c1 instead of c2
1840
CREATE table t1 ( c1 integer );
1841
INSERT INTO t1 VALUES ( 1 );
1842
INSERT INTO t1 VALUES ( 2 );
1843
INSERT INTO t1 VALUES ( 3 );
1845
CREATE TABLE t2 ( c2 integer );
1846
INSERT INTO t2 VALUES ( 1 );
1847
INSERT INTO t2 VALUES ( 4 );
1848
INSERT INTO t2 VALUES ( 5 );
1850
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
1852
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
1853
WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
1858
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ
1860
CREATE TABLE t1 ( c1 integer );
1861
INSERT INTO t1 VALUES ( 1 );
1862
INSERT INTO t1 VALUES ( 2 );
1863
INSERT INTO t1 VALUES ( 3 );
1864
INSERT INTO t1 VALUES ( 6 );
1866
CREATE TABLE t2 ( c2 integer );
1867
INSERT INTO t2 VALUES ( 1 );
1868
INSERT INTO t2 VALUES ( 4 );
1869
INSERT INTO t2 VALUES ( 5 );
1870
INSERT INTO t2 VALUES ( 6 );
1872
CREATE TABLE t3 ( c3 integer );
1873
INSERT INTO t3 VALUES ( 7 );
1874
INSERT INTO t3 VALUES ( 8 );
1876
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
1877
WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
1879
DROP TABLE t1,t2,t3;
1882
# Correct building of equal fields list (do not include outer
1883
# fields) (BUG#6384)
1885
CREATE TABLE t1 (EMPNUM CHAR(3));
1886
CREATE TABLE t2 (EMPNUM CHAR(3) );
1887
INSERT INTO t1 VALUES ('E1'),('E2');
1888
INSERT INTO t2 VALUES ('E1');
1890
WHERE t1.EMPNUM NOT IN
1893
WHERE t1.EMPNUM = t2.EMPNUM);
1898
# Test for bug #11487: range access in a subquery
1901
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
1902
INSERT INTO t1 VALUES (1, 1);
1903
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
1904
PRIMARY KEY(select_id,values_id));
1905
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
1907
SELECT values_id FROM t1
1908
WHERE values_id IN (SELECT values_id FROM t2
1909
WHERE select_id IN (1, 0));
1910
SELECT values_id FROM t1
1911
WHERE values_id IN (SELECT values_id FROM t2
1912
WHERE select_id BETWEEN 0 AND 1);
1913
SELECT values_id FROM t1
1914
WHERE values_id IN (SELECT values_id FROM t2
1915
WHERE select_id = 0 OR select_id = 1);
1919
# BUG#11821 : Select from subselect using aggregate function on an enum
1921
create table t1 (fld enum('0','1'));
1922
insert into t1 values ('1');
1923
select * from (select max(fld) from t1) as foo;
1927
# Test for bug #11762: subquery with an aggregate function in HAVING
1930
CREATE TABLE t1 (a int, b int);
1931
CREATE TABLE t2 (c int, d int);
1932
CREATE TABLE t3 (e int);
1934
INSERT INTO t1 VALUES
1935
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
1936
INSERT INTO t2 VALUES
1937
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
1938
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
1940
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
1944
SELECT a FROM t1 GROUP BY a
1945
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
1946
SELECT a FROM t1 GROUP BY a
1947
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
1948
SELECT a FROM t1 GROUP BY a
1949
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
1950
SELECT a FROM t1 GROUP BY a
1951
HAVING a IN (SELECT c FROM t2
1952
WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
1953
SELECT a FROM t1 GROUP BY a
1954
HAVING a IN (SELECT c FROM t2
1955
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
1956
SELECT a FROM t1 GROUP BY a
1957
HAVING a IN (SELECT c FROM t2
1958
WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
1959
SELECT a FROM t1 GROUP BY a
1960
HAVING a IN (SELECT c FROM t2
1961
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
1962
SELECT a FROM t1 GROUP BY a
1963
HAVING a IN (SELECT c FROM t2
1964
WHERE MIN(b) < d AND
1965
EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
1967
SELECT a, SUM(a) FROM t1 GROUP BY a;
1970
WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
1971
SELECT a FROM t1 GROUP BY a
1972
HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
1976
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
1979
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
1981
SELECT t1.a FROM t1 GROUP BY t1.a
1982
HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
1983
HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
1984
HAVING SUM(t1.a+t2.c) < t3.e/4));
1985
SELECT t1.a FROM t1 GROUP BY t1.a
1986
HAVING t1.a > ALL(SELECT t2.c FROM t2
1987
WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
1988
HAVING SUM(t1.a+t2.c) < t3.e/4));
1989
SELECT t1.a FROM t1 GROUP BY t1.a
1990
HAVING t1.a > ALL(SELECT t2.c FROM t2
1991
WHERE EXISTS(SELECT t3.e FROM t3
1992
WHERE SUM(t1.a+t2.c) < t3.e/4));
1993
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
1995
SELECT t1.a FROM t1 GROUP BY t1.a
1996
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
1997
HAVING AVG(t2.c+SUM(t1.b)) > 20);
1998
SELECT t1.a FROM t1 GROUP BY t1.a
1999
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
2000
HAVING AVG(SUM(t1.b)) > 20);
2002
SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
2003
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
2004
HAVING t2.c+sum > 20);
2006
DROP TABLE t1,t2,t3;
2009
# Test for bug #16603: GROUP BY in a row subquery with a quantifier
2010
# when an index is defined on the grouping field
2012
CREATE TABLE t1 (a varchar(5), b varchar(10));
2013
INSERT INTO t1 VALUES
2014
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
2015
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
2017
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2019
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2021
ALTER TABLE t1 ADD INDEX(a);
2023
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2025
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2030
# Bug#17366: Unchecked Item_int results in server crash
2032
create table t1( f1 int,f2 int);
2033
insert into t1 values (1,1),(2,2);
2034
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';
2038
# Bug #18306: server crash on delete using subquery.
2041
create table t1 (c int, key(c));
2042
insert into t1 values (1142477582), (1142455969);
2043
create table t2 (a int, b int);
2044
insert into t2 values (2, 1), (1, 0);
2045
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
2049
# Bug#19077: A nested materialized derived table is used before being populated.
2051
create table t1 (i int, j bigint);
2052
insert into t1 values (1, 2), (2, 2), (3, 2);
2053
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
2057
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
2059
CREATE TABLE t1 (i BIGINT);
2060
INSERT INTO t1 VALUES (10000000000000000); # > MAX SIGNED BIGINT 9323372036854775807
2061
INSERT INTO t1 VALUES (1);
2063
CREATE TABLE t2 (i BIGINT);
2064
INSERT INTO t2 VALUES (10000000000000000); # same as first table
2065
INSERT INTO t2 VALUES (1);
2068
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
2071
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
2073
/* subquery test with cast*/
2074
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
2080
# Bug#20519: subselect with LIMIT M, N
2084
id bigint NOT NULL auto_increment,
2085
name varchar(255) NOT NULL,
2088
INSERT INTO t1 VALUES
2089
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
2092
id bigint NOT NULL auto_increment,
2093
mid bigint NOT NULL,
2097
INSERT INTO t2 VALUES
2098
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
2099
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
2102
(SELECT date FROM t2 WHERE mid = t1.id
2103
ORDER BY date DESC LIMIT 0, 1) AS date_last,
2104
(SELECT date FROM t2 WHERE mid = t1.id
2105
ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
2108
(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
2109
ORDER BY date DESC LIMIT 1, 1) AS date_count
2112
(SELECT date FROM t2 WHERE mid = t1.id
2113
ORDER BY date DESC LIMIT 0, 1) AS date_last,
2114
(SELECT date FROM t2 WHERE mid = t1.id
2115
ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
2120
# Bug#20869: subselect with range access by DESC
2124
i1 int NOT NULL default '0',
2125
i2 int NOT NULL default '0',
2126
t datetime NOT NULL default '0000-00-00 00:00:00',
2127
PRIMARY KEY (i1,i2,t)
2129
INSERT INTO t1 VALUES
2130
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
2131
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
2132
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
2133
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
2134
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
2135
(24,2,'2005-05-27 12:40:06');
2138
i1 int NOT NULL default '0',
2139
i2 int NOT NULL default '0',
2140
t datetime default NULL,
2143
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
2147
WHERE t1.t = (SELECT t1.t FROM t1
2148
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
2149
ORDER BY t1.t DESC LIMIT 1);
2151
WHERE t1.t = (SELECT t1.t FROM t1
2152
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
2153
ORDER BY t1.t DESC LIMIT 1);
2158
# Bug#14654 : Cannot select from the same table twice within a UNION
2161
CREATE TABLE t1 (i INT);
2163
(SELECT i FROM t1) UNION (SELECT i FROM t1);
2164
SELECT * FROM t1 WHERE NOT EXISTS
2166
(SELECT i FROM t1) UNION
2171
--error ER_PARSE_ERROR
2173
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
2177
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
2180
explain select * from t1 where not exists
2181
((select t11.i from t1 t11) union (select t12.i from t1 t12));
2186
# Bug #21540: Subqueries with no from and aggregate functions return
2188
CREATE TABLE t1 (a INT, b INT);
2189
CREATE TABLE t2 (a INT);
2190
INSERT INTO t2 values (1);
2191
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
2192
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
2193
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
2194
FROM t1 GROUP BY t1.a;
2195
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
2199
SELECT COUNT(DISTINCT t1.b)
2202
FROM t1 GROUP BY t1.a LIMIT 1)
2208
# Bug #21727: Correlated subquery that requires filesort:
2209
# slow with big sort_buffer_size
2212
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
2213
CREATE TABLE t2 (x int auto_increment, y int, z int,
2214
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
2221
eval INSERT INTO t1(a) VALUES(RAND()*1000);
2222
eval SELECT MAX(b) FROM t1 INTO @id;
2226
eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000);
2235
SET SESSION sort_buffer_size = 32 * 1024;
2237
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
2240
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
2242
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
2248
# Bug #25219: EXIST subquery with UNION over a mix of
2249
# correlated and uncorrelated selects
2252
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
2253
CREATE TABLE t2 (c int);
2255
INSERT INTO t1 VALUES ('aa', 1);
2256
INSERT INTO t2 VALUES (1);
2259
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
2261
SELECT c from t2 WHERE c=t1.c);
2263
INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
2266
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
2268
SELECT c from t2 WHERE c=t1.c);
2270
INSERT INTO t2 VALUES (2);
2271
CREATE TABLE t3 (c int);
2272
INSERT INTO t3 VALUES (1);
2275
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
2277
SELECT c from t2 WHERE c=t1.c);
2279
DROP TABLE t1,t2,t3;
2282
# Bug#21904 (parser problem when using IN with a double "(())")
2286
DROP TABLE IF EXISTS t1;
2287
DROP TABLE IF EXISTS t2;
2288
DROP TABLE IF EXISTS t1xt2;
2293
t varchar(4) DEFAULT NULL
2298
t varchar(4) DEFAULT NULL
2301
CREATE TABLE t1xt2 (
2306
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
2308
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
2310
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
2312
# subselect returns 0 rows
2314
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2315
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2317
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2318
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2320
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2321
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2323
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2324
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2326
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2327
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
2329
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2330
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
2332
insert INTO t1xt2 VALUES (1, 12);
2334
# subselect returns 1 row
2336
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2337
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2339
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2340
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2342
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2343
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2345
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2346
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2348
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2349
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2351
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2352
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2354
insert INTO t1xt2 VALUES (2, 12);
2356
# subselect returns more than 1 row
2358
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2359
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2361
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2362
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2364
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2365
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2367
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2368
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2370
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2371
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2373
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2374
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2381
# Bug #26728: derived table with concatanation of literals in select list
2384
CREATE TABLE t1 (a int);
2385
INSERT INTO t1 VALUES (3), (1), (2);
2387
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
2388
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
2393
# Bug #27257: COUNT(*) aggregated in outer query
2396
CREATE TABLE t1 (a int, b int);
2397
CREATE TABLE t2 (m int, n int);
2398
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2399
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2402
(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
2406
(SELECT MIN(m) FROM t2 WHERE m = count(*))
2411
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
2416
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument
2419
CREATE TABLE t1 (a int, b int);
2420
CREATE TABLE t2 (m int, n int);
2421
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2422
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2424
SELECT COUNT(*) c, a,
2425
(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
2428
SELECT COUNT(*) c, a,
2429
(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
2435
# Bug#27321: Wrong subquery result in a grouping select
2437
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
2438
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
2439
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
2440
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
2443
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
2446
(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
2449
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
2450
FROM t1 WHERE t1.d=0 GROUP BY a;
2453
(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2454
LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2458
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2460
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2461
FROM t1 as tt GROUP BY tt.a;
2464
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2466
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
2467
FROM t1 as tt GROUP BY tt.a;
2472
# Bug #27363: nested aggregates in outer, subquery / sum(select
2475
CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
2476
CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
2478
--error ER_INVALID_GROUP_FUNC_USE
2479
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
2480
--error ER_INVALID_GROUP_FUNC_USE
2481
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
2483
--error ER_INVALID_GROUP_FUNC_USE
2485
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
2488
--error ER_INVALID_GROUP_FUNC_USE
2490
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
2496
# Bug #27807: Server crash when executing subquery with EXPLAIN
2498
CREATE TABLE t1 (a int, b int, KEY (a));
2499
INSERT INTO t1 VALUES (1,1),(2,1);
2500
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
2504
# Bug #28377: grouping query with a correlated subquery in WHERE condition
2507
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
2508
INSERT INTO t1 VALUES
2509
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
2510
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
2511
INSERT INTO t2 VALUES (7), (5), (1), (3);
2513
SELECT id, st FROM t1
2514
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
2515
SELECT id, st FROM t1
2516
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
2519
SELECT id, st FROM t1
2520
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
2521
SELECT id, st FROM t1
2522
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
2528
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
2529
# over a grouping subselect
2532
CREATE TABLE t1 (a int);
2534
INSERT INTO t1 VALUES (1), (2);
2537
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
2542
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1
2546
a varchar(255) default NULL,
2547
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2551
a varchar(255) default NULL
2554
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
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 SELECT * FROM t1;
2559
INSERT INTO t1 SELECT * FROM t1;
2560
INSERT INTO t1 SELECT * FROM t1;
2561
INSERT INTO t1 SELECT * FROM t1;
2562
INSERT INTO t1 SELECT * FROM t1;
2563
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
2564
INSERT INTO `t2` VALUES ('abcdefghijk');
2565
INSERT INTO `t2` VALUES ('asdf');
2567
SET session sort_buffer_size=8192;
2569
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
2575
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
2578
CREATE TABLE t1 (a INTEGER, b INTEGER);
2579
CREATE TABLE t2 (x INTEGER);
2580
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
2581
INSERT INTO t2 VALUES (1), (2);
2583
# wasn't failing, but should
2584
--error ER_SUBQUERY_NO_1_ROW
2585
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
2587
# fails as it should
2588
--error ER_SUBQUERY_NO_1_ROW
2589
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
2591
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
2594
# second test case from 27333
2595
CREATE TABLE t1 (a INT, b INT);
2596
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
2598
# returns no rows, when it should
2599
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
2600
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
2604
#test cases from 29297
2605
CREATE TABLE t1 (a INT);
2606
CREATE TABLE t2 (a INT);
2607
INSERT INTO t1 VALUES (1),(2);
2608
INSERT INTO t2 VALUES (1),(2);
2609
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
2610
--error ER_SUBQUERY_NO_1_ROW
2611
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
2612
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
2616
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
2619
CREATE TABLE t1 (a1 INT, a2 INT);
2620
CREATE TABLE t2 (b1 INT, b2 INT);
2622
INSERT INTO t1 VALUES (100, 200);
2623
INSERT INTO t1 VALUES (101, 201);
2624
INSERT INTO t2 VALUES (101, 201);
2625
INSERT INTO t2 VALUES (103, 203);
2627
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
2631
# Bug #30788: Inconsistent retrieval of char/varchar
2634
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
2635
INSERT INTO t1 VALUES ('a', 'aa');
2636
INSERT INTO t1 VALUES ('a', 'aaa');
2637
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2638
CREATE INDEX I1 ON t1 (a);
2639
CREATE INDEX I2 ON t1 (b);
2640
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2641
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2643
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
2644
INSERT INTO t2 SELECT * FROM t1;
2645
CREATE INDEX I1 ON t2 (a);
2646
CREATE INDEX I2 ON t2 (b);
2647
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2648
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2650
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2651
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2656
# Bug #32400: Complex SELECT query returns correct result only on some
2660
CREATE TABLE t1(a INT, b INT);
2661
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
2663
--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)
2669
--error ER_BAD_FIELD_ERROR
2670
SELECT a AS out_a, MIN(b) FROM t1
2671
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
2675
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
2676
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
2679
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
2680
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
2687
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
2690
CREATE TABLE t1 (a INT);
2691
CREATE TABLE t2 (a INT);
2693
INSERT INTO t1 VALUES (1),(2);
2694
INSERT INTO t2 VALUES (1),(2);
2696
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
2698
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
2702
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
2703
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
2708
# BUG#33794 "MySQL crashes executing specific query on specific dump"
2711
f7 varchar(32) collate utf8_bin NOT NULL default '',
2712
f10 varchar(32) collate utf8_bin default NULL,
2715
INSERT INTO t4 VALUES(1,1), (2,null);
2718
f4 varchar(32) collate utf8_bin NOT NULL default '',
2719
f2 varchar(50) collate utf8_bin default NULL,
2720
f3 varchar(10) collate utf8_bin default NULL,
2724
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
2727
f8 varchar(32) collate utf8_bin NOT NULL default '',
2728
f1 varchar(10) collate utf8_bin default NULL,
2729
f9 varchar(32) collate utf8_bin default NULL,
2732
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
2735
f6 varchar(32) collate utf8_bin NOT NULL default '',
2736
f5 varchar(50) collate utf8_bin default NULL,
2739
INSERT INTO t3 VALUES (1,null), (2,null);
2742
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
2743
IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
2747
FROM t2 VPC, t4 a2, t2 a3
2749
VPC.f4 = a2.f10 AND a3.f2 = a4
2756
t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
2759
DROP TABLE t1, t2, t3, t4;
2761
--echo End of 5.0 tests.
2764
# Test [NOT] IN truth table (both as top-level and general predicate).
2767
create table t_out (subcase char(3),
2768
a1 char(2), b1 char(2), c1 char(2));
2769
create table t_in (a2 char(2), b2 char(2), c2 char(2));
2771
insert into t_out values ('A.1','2a', NULL, '2a');
2772
#------------------------- A.2 - impossible
2773
insert into t_out values ('A.3', '2a', NULL, '2a');
2774
insert into t_out values ('A.4', '2a', NULL, 'xx');
2775
insert into t_out values ('B.1', '2a', '2a', '2a');
2776
insert into t_out values ('B.2', '2a', '2a', '2a');
2777
insert into t_out values ('B.3', '3a', 'xx', '3a');
2778
insert into t_out values ('B.4', 'xx', '3a', '3a');
2780
insert into t_in values ('1a', '1a', '1a');
2781
insert into t_in values ('2a', '2a', '2a');
2782
insert into t_in values (NULL, '2a', '2a');
2783
insert into t_in values ('3a', NULL, '3a');
2785
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
2786
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
2787
from t_out where subcase = 'A.1';
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.3';
2796
(a1, b1, c1) IN (select * from t_in) pred_in,
2797
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2798
from t_out where subcase = 'A.4';
2801
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
2802
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
2803
from t_out where subcase = 'B.1';
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.2';
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.3';
2816
(a1, b1, c1) IN (select * from t_in) pred_in,
2817
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2818
from t_out where subcase = 'B.4';
2820
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2821
where subcase = 'A.1' and
2822
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
2824
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2825
where subcase = 'A.1' and
2826
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
2828
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2829
where subcase = 'A.1' and
2830
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
2832
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2833
where subcase = 'A.3' and
2834
(a1, b1, c1) IN (select * from t_in);
2836
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2837
where subcase = 'A.3' and
2838
(a1, b1, c1) NOT IN (select * from t_in);
2840
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2841
where subcase = 'A.3' and
2842
NOT((a1, b1, c1) IN (select * from t_in));
2843
# test non-top level result indirectly
2844
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
2845
where subcase = 'A.3' and
2846
((a1, b1, c1) IN (select * from t_in)) is NULL and
2847
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
2849
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2850
where subcase = 'A.4' and
2851
(a1, b1, c1) IN (select * from t_in);
2853
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2854
where subcase = 'A.4' and
2855
(a1, b1, c1) NOT IN (select * from t_in);
2857
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2858
where subcase = 'A.4' and
2859
NOT((a1, b1, c1) IN (select * from t_in));
2861
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2862
where subcase = 'B.1' and
2863
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
2865
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2866
where subcase = 'B.1' and
2867
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
2869
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2870
where subcase = 'B.1' and
2871
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
2873
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2874
where subcase = 'B.2' and
2875
(a1, b1, c1) IN (select * from t_in);
2877
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2878
where subcase = 'B.2' and
2879
(a1, b1, c1) NOT IN (select * from t_in);
2881
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2882
where subcase = 'B.2' and
2883
NOT((a1, b1, c1) IN (select * from t_in));
2885
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2886
where subcase = 'B.3' and
2887
(a1, b1, c1) IN (select * from t_in);
2889
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2890
where subcase = 'B.3' and
2891
(a1, b1, c1) NOT IN (select * from t_in);
2893
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2894
where subcase = 'B.3' and
2895
NOT((a1, b1, c1) IN (select * from t_in));
2896
# test non-top level result indirectly
2897
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
2898
where subcase = 'B.3' and
2899
((a1, b1, c1) IN (select * from t_in)) is NULL and
2900
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
2902
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
2903
where subcase = 'B.4' and
2904
(a1, b1, c1) IN (select * from t_in);
2906
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
2907
where subcase = 'B.4' and
2908
(a1, b1, c1) NOT IN (select * from t_in);
2910
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2911
where subcase = 'B.4' and
2912
NOT((a1, b1, c1) IN (select * from t_in));
2919
# Bug#20835 (literal string with =any values)
2921
CREATE TABLE t1 (s1 char(1));
2922
INSERT INTO t1 VALUES ('a');
2923
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
2927
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
2929
CREATE TABLE t1( a INT );
2930
INSERT INTO t1 VALUES (1),(2);
2932
CREATE TABLE t2( a INT, b INT );
2934
--error ER_PARSE_ERROR
2936
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
2937
--error ER_PARSE_ERROR
2939
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
2940
--error ER_PARSE_ERROR
2942
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
2944
--error ER_PARSE_ERROR
2948
SELECT a INTO @var FROM t1 WHERE a = 2
2951
--error ER_PARSE_ERROR
2955
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
2958
--error ER_PARSE_ERROR
2962
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
2965
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
2968
SELECT a FROM t1 WHERE a = 2
2970
SELECT a FROM t1 WHERE a = 2
2976
SELECT a FROM t1 WHERE a = 2
2978
SELECT a FROM t1 WHERE a = 2
2981
# This was not allowed previously. Possibly, it should be allowed on the future.
2982
# For now, the intent is to keep the fix as non-intrusive as possible.
2983
--error ER_PARSE_ERROR
2984
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
2985
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
2986
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
2987
--error ER_PARSE_ERROR
2988
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
2989
--error ER_PARSE_ERROR
2990
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
2991
--error ER_PARSE_ERROR
2992
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
2994
--error ER_PARSE_ERROR
2995
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
2996
--error ER_PARSE_ERROR
2997
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
2998
--error ER_PARSE_ERROR
2999
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
3001
--error ER_PARSE_ERROR
3002
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
3003
--error ER_PARSE_ERROR
3004
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
3005
--error ER_PARSE_ERROR
3006
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
3008
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
3009
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;
3010
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
3011
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
3014
# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias
3015
# UNION should not be allowed inside the parentheses, nor should
3018
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
3019
--error ER_PARSE_ERROR
3020
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
3021
--error ER_PARSE_ERROR
3022
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
3023
--error ER_PARSE_ERROR
3024
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
3025
--error ER_PARSE_ERROR
3026
SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;
3027
--error ER_PARSE_ERROR
3028
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
3030
SELECT * FROM t1 JOIN (t1 t1a) ON 1;
3031
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
3033
SELECT * FROM (t1 t1a);
3034
SELECT * FROM ((t1 t1a));
3036
SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;
3037
SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;
3039
SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;
3040
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
3042
# For the join, TABLE_LIST::select_lex == NULL
3043
# Check that we handle this.
3044
--error ER_PARSE_ERROR
3045
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
3047
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
3048
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );
3049
SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );
3051
--error ER_PARSE_ERROR
3052
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
3053
--error ER_PARSE_ERROR
3054
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
3055
--error ER_PARSE_ERROR
3056
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3058
SELECT * FROM t1 WHERE a = ( SELECT 1 );
3059
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );
3060
--error ER_PARSE_ERROR
3061
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
3062
--error ER_PARSE_ERROR
3063
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
3064
--error ER_PARSE_ERROR
3065
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
3067
--error ER_PARSE_ERROR
3068
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
3069
--error ER_PARSE_ERROR
3070
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
3071
--error ER_PARSE_ERROR
3072
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3074
--error ER_PARSE_ERROR
3075
SELECT ( SELECT 1 INTO @v );
3076
--error ER_PARSE_ERROR
3077
SELECT ( SELECT 1 INTO OUTFILE 'file' );
3078
--error ER_PARSE_ERROR
3079
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
3081
--error ER_PARSE_ERROR
3082
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
3083
--error ER_PARSE_ERROR
3084
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
3085
--error ER_PARSE_ERROR
3086
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3088
# Make sure context is popped when we leave the nested select
3089
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
3090
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
3092
# Make sure we have feature F561 (see .yy file)
3093
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
3095
# Make sure the parser does not allow nested UNIONs anywhere
3097
--error ER_PARSE_ERROR
3098
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
3099
--error ER_PARSE_ERROR
3100
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
3102
--error ER_PARSE_ERROR
3103
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3104
--error ER_PARSE_ERROR
3105
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
3106
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3107
SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
3109
--error ER_PARSE_ERROR
3110
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3111
--error ER_PARSE_ERROR
3112
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3113
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
3115
--error ER_PARSE_ERROR
3116
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3117
--error ER_PARSE_ERROR
3118
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3119
--error ER_PARSE_ERROR
3120
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3121
--error ER_PARSE_ERROR
3122
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3124
--error ER_PARSE_ERROR
3125
SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3126
--error ER_PARSE_ERROR
3127
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3128
--error ER_PARSE_ERROR
3129
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3130
--error ER_PARSE_ERROR
3131
SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3133
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3134
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3135
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3136
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3138
--error ER_PARSE_ERROR
3139
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
3140
SELECT EXISTS(SELECT 1+1);
3141
--error ER_PARSE_ERROR
3142
SELECT EXISTS(SELECT 1+1 INTO @test);
3143
--error ER_PARSE_ERROR
3144
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
3146
--error ER_PARSE_ERROR
3147
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
3148
--error ER_PARSE_ERROR
3149
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
added
removed
tests/t/subselect.test
