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));
12
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
14
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;
15
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
17
SELECT (SELECT a) as a;
18
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
19
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
22
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
24
SELECT 1 FROM (SELECT (SELECT a) b) c;
25
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
27
SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1);
28
SELECT 1 IN (SELECT 1);
29
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
30
-- error ER_BAD_FIELD_ERROR
31
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
32
-- error ER_BAD_FIELD_ERROR
33
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
34
SELECT (SELECT 1,2,3) = ROW(1,2,3);
35
SELECT (SELECT 1,2,3) = ROW(1,2,1);
36
SELECT (SELECT 1,2,3) < ROW(1,2,1);
37
SELECT (SELECT 1,2,3) > ROW(1,2,1);
38
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
39
SELECT ROW(1,2,3) = (SELECT 1,2,3);
40
SELECT ROW(1,2,3) = (SELECT 1,2,1);
41
SELECT ROW(1,2,3) < (SELECT 1,2,1);
42
SELECT ROW(1,2,3) > (SELECT 1,2,1);
43
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
44
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
45
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
46
SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
47
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
48
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
49
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
52
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
54
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
56
create table t1 (a int);
57
create table t2 (a int, b int);
58
create table t3 (a int);
59
create table t4 (a int not null, b int not null);
60
insert into t1 values (2);
61
insert into t2 values (1,7),(2,7);
62
insert into t4 values (4,8),(3,8),(5,9);
64
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
65
select (select a from t1 where t1.a=t2.a), a from t2;
66
select (select a from t1 where t1.a=t2.b), a from t2;
67
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
68
select (select a from t3), a from t2;
69
select * from t2 where t2.a=(select a from t1);
70
insert into t3 values (6),(7),(3);
71
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
72
(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;
73
(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);
74
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);
75
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
76
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
77
(select * from t2 where a>1) as tt;
78
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
79
(select * from t2 where a>1) as tt;
80
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);
81
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);
82
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);
83
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
84
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
85
select * from t3 where exists (select * from t2 where t2.b=t3.a);
86
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
87
select * from t3 where a in (select b from t2);
88
select * from t3 where a not in (select b from t2);
89
select * from t3 where a = some (select b from t2);
90
select * from t3 where a <> any (select b from t2);
92
# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);
93
select * from t3 where a = all (select b from t2);
95
select * from t3 where a <> all (select b from t2);
96
insert into t2 values (100, 5);
97
select * from t3 where a < any (select b from t2);
98
select * from t3 where a < all (select b from t2);
99
select * from t3 where a >= any (select b from t2);
100
explain extended select * from t3 where a >= any (select b from t2);
101
select * from t3 where a >= all (select b from t2);
102
delete from t2 where a=100;
104
select * from t3 where a in (select a,b from t2);
106
select * from t3 where a in (select * from t2);
107
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
109
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
110
insert into t2 values (2,10);
111
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
112
delete from t2 where a=2 and b=10;
113
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
114
create table t5 (a int);
115
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
116
insert into t5 values (5);
117
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
118
insert into t5 values (2);
119
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
120
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;
122
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
123
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
124
create table t7( uq int primary key, name char(25));
125
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
126
insert into t6 values (1,1),(1,2),(2,2),(1,3);
127
select * from t6 where exists (select * from t7 where uq = clinic_uq);
128
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
132
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
134
# different tipes & group functions
137
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
138
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
139
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
140
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
141
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
142
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
143
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
144
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
145
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
148
`pseudo` varchar(35) character set latin1 NOT NULL default '',
149
`email` varchar(60) character set latin1 NOT NULL default '',
150
PRIMARY KEY (`pseudo`),
151
UNIQUE KEY `email` (`email`)
152
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
154
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
155
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
156
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
157
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');
159
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
160
t8 WHERE pseudo='joce');
162
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
164
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
166
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
168
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
170
#searchconthardwarefr3 forumconthardwarefr7
172
`topic` mediumint(8) unsigned NOT NULL default '0',
173
`date` date NOT NULL default '0000-00-00',
174
`pseudo` varchar(35) character set latin1 NOT NULL default '',
175
PRIMARY KEY (`pseudo`,`date`,`topic`),
176
KEY `topic` (`topic`)
177
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
178
INSERT INTO t1 (topic,date,pseudo) VALUES
179
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
180
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
181
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
182
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
183
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
184
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
186
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
187
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
190
#forumconthardwarefr7 searchconthardwarefr7
192
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
193
`maxnumrep` int(10) unsigned NOT NULL default '0',
194
PRIMARY KEY (`numeropost`),
195
UNIQUE KEY `maxnumrep` (`maxnumrep`)
196
) ENGINE=MyISAM ROW_FORMAT=FIXED;
198
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
201
`mot` varchar(30) NOT NULL default '',
202
`topic` mediumint(8) unsigned NOT NULL default '0',
203
`date` date NOT NULL default '0000-00-00',
204
`pseudo` varchar(35) NOT NULL default '',
205
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
206
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
208
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
209
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
210
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;
212
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
214
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
216
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
217
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
218
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
219
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
220
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
221
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
222
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
223
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
224
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
225
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
226
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
227
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
228
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
229
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
230
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
233
#forumconthardwarefr7
235
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
236
`maxnumrep` int(10) unsigned NOT NULL default '0',
237
PRIMARY KEY (`numeropost`),
238
UNIQUE KEY `maxnumrep` (`maxnumrep`)
239
) ENGINE=MyISAM ROW_FORMAT=FIXED;
241
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
243
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
245
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
248
create table t1 (a int);
249
insert into t1 values (1),(2),(3);
250
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
254
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
255
INSERT INTO t1 VALUES ();
257
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
262
`numeropost` mediumint(8) unsigned NOT NULL default '0',
263
`numreponse` int(10) unsigned NOT NULL auto_increment,
264
`pseudo` varchar(35) NOT NULL default '',
265
PRIMARY KEY (`numeropost`,`numreponse`),
266
UNIQUE KEY `numreponse` (`numreponse`),
267
KEY `pseudo` (`pseudo`,`numeropost`)
270
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
272
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
273
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
274
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
276
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
277
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
278
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
281
CREATE TABLE t1 (a int(1));
282
INSERT INTO t1 VALUES (1);
283
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
286
#update with subselects
287
create table t1 (a int NOT NULL, b int, primary key (a));
288
create table t2 (a int NOT NULL, b int, primary key (a));
289
insert into t1 values (0, 10),(1, 11),(2, 12);
290
insert into t2 values (1, 21),(2, 22),(3, 23);
293
update t1 set b= (select b from t1);
295
update t1 set b= (select b from t2);
296
update t1 set b= (select b from t2 where t1.a = t2.a);
300
#delete with subselects
301
create table t1 (a int NOT NULL, b int, primary key (a));
302
create table t2 (a int NOT NULL, b int, primary key (a));
303
insert into t1 values (0, 10),(1, 11),(2, 12);
304
insert into t2 values (1, 21),(2, 12),(3, 23);
306
select * from t1 where b = (select b from t2 where t1.a = t2.a);
308
delete from t1 where b = (select b from t1);
310
delete from t1 where b = (select b from t2);
311
delete from t1 where b = (select b from t2 where t1.a = t2.a);
315
#multi-delete with subselects
317
create table t11 (a int NOT NULL, b int, primary key (a));
318
create table t12 (a int NOT NULL, b int, primary key (a));
319
create table t2 (a int NOT NULL, b int, primary key (a));
320
insert into t11 values (0, 10),(1, 11),(2, 12);
321
insert into t12 values (33, 10),(22, 11),(2, 12);
322
insert into t2 values (1, 21),(2, 12),(3, 23);
326
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
328
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
329
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
332
drop table t11, t12, t2;
334
#insert with subselects
335
CREATE TABLE t1 (x int);
336
create table t2 (a int);
337
create table t3 (b int);
338
insert into t2 values (1);
339
insert into t3 values (1),(2);
341
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
343
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
344
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
346
insert into t2 values (1);
347
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
350
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
352
# After this, only data based on old t1 records should have been added.
353
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
356
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
357
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
361
#TODO: should be uncommented after bug 380 fix pushed
362
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
364
drop table t1, t2, t3;
366
#replace with subselects
367
CREATE TABLE t1 (x int not null, y int, primary key (x));
368
create table t2 (a int);
369
create table t3 (a int);
370
insert into t2 values (1);
371
insert into t3 values (1),(2);
374
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
376
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
377
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
379
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
381
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
384
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
387
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
389
drop table t1, t2, t3;
392
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
394
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
395
INSERT INTO t2 VALUES (1),(2);
396
SELECT * FROM t2 WHERE id IN (SELECT 1);
397
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
398
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
399
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
400
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
401
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
402
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
403
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
405
INSERT INTO t2 VALUES ((SELECT * FROM t2));
407
INSERT INTO t2 VALUES ((SELECT id FROM t2));
409
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
410
INSERT INTO t1 values (1),(1);
412
UPDATE t2 SET id=(SELECT * FROM t1);
416
create table t1 (a int);
417
insert into t1 values (1),(2),(3);
418
select 1 IN (SELECT * from t1);
419
select 10 IN (SELECT * from t1);
420
select NULL IN (SELECT * from t1);
421
update t1 set a=NULL where a=2;
422
select 1 IN (SELECT * from t1);
423
select 3 IN (SELECT * from t1);
424
select 10 IN (SELECT * from t1);
425
select 1 > ALL (SELECT * from t1);
426
select 10 > ALL (SELECT * from t1);
427
select 1 > ANY (SELECT * from t1);
428
select 10 > ANY (SELECT * from t1);
430
create table t1 (a varchar(20));
431
insert into t1 values ('A'),('BC'),('DEF');
432
select 'A' IN (SELECT * from t1);
433
select 'XYZS' IN (SELECT * from t1);
434
select NULL IN (SELECT * from t1);
435
update t1 set a=NULL where a='BC';
436
select 'A' IN (SELECT * from t1);
437
select 'DEF' IN (SELECT * from t1);
438
select 'XYZS' IN (SELECT * from t1);
439
select 'A' > ALL (SELECT * from t1);
440
select 'XYZS' > ALL (SELECT * from t1);
441
select 'A' > ANY (SELECT * from t1);
442
select 'XYZS' > ANY (SELECT * from t1);
444
create table t1 (a float);
445
insert into t1 values (1.5),(2.5),(3.5);
446
select 1.5 IN (SELECT * from t1);
447
select 10.5 IN (SELECT * from t1);
448
select NULL IN (SELECT * from t1);
449
update t1 set a=NULL where a=2.5;
450
select 1.5 IN (SELECT * from t1);
451
select 3.5 IN (SELECT * from t1);
452
select 10.5 IN (SELECT * from t1);
453
select 1.5 > ALL (SELECT * from t1);
454
select 10.5 > ALL (SELECT * from t1);
455
select 1.5 > ANY (SELECT * from t1);
456
select 10.5 > ANY (SELECT * from t1);
457
explain extended select (select a+1) from t1;
458
select (select a+1) from t1;
465
CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a));
466
CREATE TABLE t2 (a int(11) default '0', INDEX (a));
467
INSERT INTO t1 VALUES (1),(2),(3),(4);
468
INSERT INTO t2 VALUES (1),(2),(3);
469
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
470
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
471
CREATE TABLE t3 (a int(11) default '0');
472
INSERT INTO t3 VALUES (1),(2),(3);
473
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
474
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
477
#LIMIT is not supported now
478
create table t1 (a float);
480
select 10.5 IN (SELECT * from t1 LIMIT 1);
482
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
485
create table t1 (a int, b int, c varchar(10));
486
create table t2 (a int);
487
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
488
insert into t2 values (1),(2),(NULL);
489
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;
490
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;
491
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;
494
create table t1 (a int, b real, c varchar(10));
495
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
496
select ROW(1, 1, 'a') IN (select a,b,c from t1);
497
select ROW(1, 2, 'a') IN (select a,b,c from t1);
498
select ROW(1, 1, 'a') IN (select b,a,c from t1);
499
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
500
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
501
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
502
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
503
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
504
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
506
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
509
#test of uncacheable subqueries
510
CREATE TABLE t1 (a int(1));
511
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
512
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
513
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
518
`mot` varchar(30) character set latin1 NOT NULL default '',
519
`topic` mediumint(8) unsigned NOT NULL default '0',
520
`date` date NOT NULL default '0000-00-00',
521
`pseudo` varchar(35) character set latin1 NOT NULL default '',
522
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
523
KEY `pseudo` (`pseudo`,`date`,`topic`),
524
KEY `topic` (`topic`)
525
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
528
`mot` varchar(30) character set latin1 NOT NULL default '',
529
`topic` mediumint(8) unsigned NOT NULL default '0',
530
`date` date NOT NULL default '0000-00-00',
531
`pseudo` varchar(35) character set latin1 NOT NULL default '',
532
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
533
KEY `pseudo` (`pseudo`,`date`,`topic`),
534
KEY `topic` (`topic`)
535
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
538
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
539
`maxnumrep` int(10) unsigned NOT NULL default '0',
540
PRIMARY KEY (`numeropost`),
541
UNIQUE KEY `maxnumrep` (`maxnumrep`)
542
) ENGINE=MyISAM CHARSET=latin1;
543
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
545
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
547
INSERT INTO t3 VALUES (1,1);
549
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
552
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
553
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
556
drop table t1, t2, t3;
558
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
559
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
560
SHOW CREATE TABLE t1;
562
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
563
SHOW CREATE TABLE t1;
565
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
566
SHOW CREATE TABLE t1;
568
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
570
SHOW CREATE TABLE t1;
573
create table t1 (a int);
574
insert into t1 values (1), (2), (3);
575
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
583
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);
590
ID int(11) NOT NULL auto_increment,
591
name char(35) NOT NULL default '',
592
t2 char(3) NOT NULL default '',
593
District char(20) NOT NULL default '',
594
Population int(11) NOT NULL default '0',
598
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
599
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
600
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
603
Code char(3) NOT NULL default '',
604
Name char(52) NOT NULL default '',
605
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
606
Region char(26) NOT NULL default '',
607
SurfaceArea float(10,2) NOT NULL default '0.00',
608
IndepYear smallint(6) default NULL,
609
Population int(11) NOT NULL default '0',
610
LifeExpectancy float(3,1) default NULL,
611
GNP float(10,2) default NULL,
612
GNPOld float(10,2) default NULL,
613
LocalName char(45) NOT NULL default '',
614
GovernmentForm char(45) NOT NULL default '',
615
HeadOfState char(60) default NULL,
616
Capital int(11) default NULL,
617
Code2 char(2) NOT NULL default '',
621
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');
622
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');
624
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);
632
`id` mediumint(8) unsigned NOT NULL auto_increment,
633
`pseudo` varchar(35) character set latin1 NOT NULL default '',
635
UNIQUE KEY `pseudo` (`pseudo`)
636
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
637
INSERT INTO t1 (pseudo) VALUES ('test');
638
SELECT 0 IN (SELECT 1 FROM t1 a);
639
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
640
INSERT INTO t1 (pseudo) VALUES ('test1');
641
SELECT 0 IN (SELECT 1 FROM t1 a);
642
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
646
`i` int(11) NOT NULL default '0',
648
) ENGINE=MyISAM CHARSET=latin1;
650
INSERT INTO t1 VALUES (1);
651
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
652
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
654
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
662
id int(11) default NULL
663
) ENGINE=MyISAM CHARSET=latin1;
664
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
666
id int(11) default NULL,
667
name varchar(15) default NULL
668
) ENGINE=MyISAM CHARSET=latin1;
670
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
671
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
676
# correct NULL in <CONSTANT> IN (SELECT ...)
678
create table t1 (a int, unique index indexa (a));
679
insert into t1 values (-1), (-4), (-2), (NULL);
680
select -10 IN (select a from t1 FORCE INDEX (indexa));
684
# Test optimization for sub selects
686
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
687
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
688
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
692
ID int(10) unsigned NOT NULL auto_increment,
693
SUB_ID int(3) unsigned NOT NULL default '0',
694
REF_ID int(10) unsigned default NULL,
695
REF_SUB int(3) unsigned default '0',
696
PRIMARY KEY (ID,SUB_ID),
697
UNIQUE KEY t1_PK (ID,SUB_ID),
698
KEY t1_FK (REF_ID,REF_SUB),
699
KEY t1_REFID (REF_ID)
700
) ENGINE=MyISAM CHARSET=cp1251;
701
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
702
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
706
# uninterruptable update
708
create table t1 (a int, b int);
709
create table t2 (a int, b int);
711
insert into t1 values (1,0), (2,0), (3,0);
712
insert into t2 values (1,1), (2,1), (3,1), (2,2);
714
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
720
# reduced subselect in ORDER BY & GROUP BY clauses
724
`id` mediumint(8) unsigned NOT NULL auto_increment,
725
`pseudo` varchar(35) NOT NULL default '',
726
`email` varchar(60) NOT NULL default '',
728
UNIQUE KEY `email` (`email`),
729
UNIQUE KEY `pseudo` (`pseudo`)
730
) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
731
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
732
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
733
drop table if exists t1;
735
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
738
# IN subselect optimization test
740
create table t1 (a int not null, b int, primary key (a));
741
create table t2 (a int not null, primary key (a));
742
create table t3 (a int not null, b int, primary key (a));
743
insert into t1 values (1,10), (2,20), (3,30), (4,40);
744
insert into t2 values (2), (3), (4), (5);
745
insert into t3 values (10,3), (20,4), (30,5);
746
select * from t2 where t2.a in (select a from t1);
747
explain extended select * from t2 where t2.a in (select a from t1);
748
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
749
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
750
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
751
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
752
drop table t1, t2, t3;
753
create table t1 (a int, b int, index a (a,b));
754
create table t2 (a int, index a (a));
755
create table t3 (a int, b int, index a (a));
756
insert into t1 values (1,10), (2,20), (3,30), (4,40);
758
# making table large enough
762
eval insert into t1 values (rand()*100000+200,rand()*100000);
766
insert into t2 values (2), (3), (4), (5);
767
insert into t3 values (10,3), (20,4), (30,5);
768
select * from t2 where t2.a in (select a from t1);
769
explain extended select * from t2 where t2.a in (select a from t1);
770
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
771
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
772
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
773
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
774
insert into t1 values (3,31);
775
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
776
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
777
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
778
drop table t1, t2, t3;
781
# alloc_group_fields() working
783
create table t1 (a int, b int);
784
create table t2 (a int, b int);
785
create table t3 (a int, b int);
786
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
787
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
788
insert into t3 values (3,3), (2,2), (1,1);
789
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;
793
# aggregate functions in HAVING test
795
create table t1 (s1 int);
796
create table t2 (s1 int);
797
insert into t1 values (1);
798
insert into t2 values (1);
799
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
803
# update subquery with wrong field (to force name resolving
804
# in UPDATE name space)
806
create table t1 (s1 int);
807
create table t2 (s1 int);
808
insert into t1 values (1);
809
insert into t2 values (1);
811
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
817
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
818
s2 CHAR(5) COLLATE latin1_swedish_ci);
819
INSERT INTO t1 VALUES ('z','?');
821
select * from t1 where s1 > (select max(s2) from t1);
823
select * from t1 where s1 > any (select max(s2) from t1);
827
# aggregate functions reinitialization
829
create table t1(toid int,rd int);
830
create table t2(userid int,pmnew int,pmtotal int);
831
insert into t2 values(1,0,0),(2,0,0);
832
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);
833
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);
839
create table t1 (s1 char(5));
841
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
842
insert into t1 values ('tttt');
843
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
844
explain extended (select * from t1);
849
# IN optimisation test results
851
create table t1 (s1 char(5), index s1(s1));
852
create table t2 (s1 char(5), index s1(s1));
853
insert into t1 values ('a1'),('a2'),('a3');
854
insert into t2 values ('a1'),('a2');
855
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
856
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
857
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
858
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
859
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
860
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
861
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
862
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
866
# correct ALL optimisation
868
create table t2 (a int, b int);
869
create table t3 (a int);
870
insert into t3 values (6),(7),(3);
871
select * from t3 where a >= all (select b from t2);
872
explain extended select * from t3 where a >= all (select b from t2);
873
select * from t3 where a >= some (select b from t2);
874
explain extended select * from t3 where a >= some (select b from t2);
875
select * from t3 where a >= all (select b from t2 group by 1);
876
explain extended select * from t3 where a >= all (select b from t2 group by 1);
877
select * from t3 where a >= some (select b from t2 group by 1);
878
explain extended select * from t3 where a >= some (select b from t2 group by 1);
879
select * from t3 where NULL >= any (select b from t2);
880
explain extended select * from t3 where NULL >= any (select b from t2);
881
select * from t3 where NULL >= any (select b from t2 group by 1);
882
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
883
select * from t3 where NULL >= some (select b from t2);
884
explain extended select * from t3 where NULL >= some (select b from t2);
885
select * from t3 where NULL >= some (select b from t2 group by 1);
886
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
888
# optimized static ALL/ANY with grouping
890
insert into t2 values (2,2), (2,1), (3,3), (3,1);
891
select * from t3 where a > all (select max(b) from t2 group by a);
892
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
896
# correct used_tables()
899
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) 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 CHARSET=latin1 AUTO_INCREMENT=3 ;
900
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());
901
CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
902
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);
903
CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
904
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);
905
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
906
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
907
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;
908
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;
909
drop table t1,t2,t3,t4;
914
CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
915
INSERT INTO t1 VALUES (1),(5);
916
CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
917
INSERT INTO t2 VALUES (2),(6);
919
select * from t1 where (1,2,6) in (select * from t2);
923
# optimized ALL/ANY with union
925
create table t1 (s1 char);
926
insert into t1 values ('e');
927
select * from t1 where 'f' > any (select s1 from t1);
928
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
929
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
933
# filesort in subquery (restoring join_tab)
935
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
936
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
937
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
938
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
939
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;
943
# unresolved field error
945
create table t1 (s1 int);
946
create table t2 (s1 int);
948
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
950
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
952
select count(*) from t2 group by t1.s2;
956
# fix_fields() in add_ref_to_table_cond()
958
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
959
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
960
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
961
INSERT INTO t2 VALUES (100, 200, 'C');
962
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
965
CREATE TABLE t1 (a int(1));
966
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
967
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
975
`id` int(11) NOT NULL auto_increment,
976
`id_cns` tinyint(3) unsigned NOT NULL default '0',
977
`tipo` enum('','UNO','DUE') NOT NULL default '',
978
`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
979
`particolare` mediumint(8) unsigned NOT NULL default '0',
980
`generale` mediumint(8) unsigned NOT NULL default '0',
981
`bis` tinyint(3) unsigned NOT NULL default '0',
983
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
984
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
986
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);
988
`id` tinyint(3) unsigned NOT NULL auto_increment,
989
`max_anno_dep` smallint(6) unsigned NOT NULL default '0',
992
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
994
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;
1001
create table t1 (a int);
1002
insert into t1 values (1), (2), (3);
1003
SET SQL_SELECT_LIMIT=1;
1004
select sum(a) from (select * from t1) as a;
1005
select 2 in (select * from t1);
1006
SET SQL_SELECT_LIMIT=default;
1010
# Bug #3118: subselect + order by
1013
CREATE TABLE t1 (a int, b int, INDEX (a));
1014
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1015
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1018
# Item_cond fix field
1020
create table t1(val varchar(10));
1021
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1022
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%');
1026
# ref_or_null replacing with ref
1028
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1029
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');
1030
select * from t1 where id not in (select id from t1 where id < 8);
1031
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);
1032
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1033
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);
1034
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1035
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
1036
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');
1037
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);
1038
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);
1042
# Static tables & rund() in subqueries
1044
create table t1 (a int);
1045
insert into t1 values (1);
1046
explain select benchmark(1000, (select a from t1 where a=rand()));
1052
create table t1(id int);
1053
create table t2(id int);
1054
create table t3(flag int);
1056
select (select * from t3 where id not null) from t1, t2;
1057
drop table t1,t2,t3;
1060
# aggregate functions (Bug #3505)
1062
CREATE TABLE t1 (id INT);
1063
CREATE TABLE t2 (id INT);
1064
INSERT INTO t1 VALUES (1), (2);
1065
INSERT INTO t2 VALUES (1);
1066
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);
1067
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1068
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;
1069
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;
1075
CREATE TABLE t1 ( a int, b int );
1076
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1077
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1078
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1079
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1080
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1081
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1082
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1083
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1084
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1085
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1086
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1087
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1088
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1090
ALTER TABLE t1 ADD INDEX (a);
1091
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1092
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1093
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1094
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1095
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1096
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1097
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1098
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1099
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1100
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1101
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1102
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1103
# having clause test
1104
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1105
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
1106
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
1107
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
1108
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
1109
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
1110
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
1111
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
1112
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
1113
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
1114
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
1115
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
1117
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1118
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1119
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1120
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1121
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1122
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1123
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1124
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1125
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1126
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1127
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1128
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1129
# union + having test
1130
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1131
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1132
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1133
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1134
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1135
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1136
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1137
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1138
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1139
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1140
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1141
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1143
# < > >= <= and = ALL/ <> ANY do not support row operation
1145
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1147
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1149
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1151
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
1153
SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1155
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1157
SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
1159
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
1160
# following should be converted to IN
1162
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
1164
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1165
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1167
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
1169
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1170
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1171
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
1172
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
1173
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
1174
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
1175
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);
1176
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);
1177
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);
1178
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);
1179
# without optimisation
1180
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1181
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1182
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1183
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1184
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1185
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1186
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1187
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1188
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1189
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1190
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1191
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1192
# without optimisation + having
1193
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
1194
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
1195
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
1196
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
1197
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
1198
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
1199
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
1200
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
1201
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
1202
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
1203
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
1204
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
1205
# EXISTS in string contence
1206
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
1207
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
1208
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
1210
CREATE TABLE t1 ( a double, b double );
1211
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1212
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
1213
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
1214
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
1215
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
1216
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
1217
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
1218
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
1219
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
1220
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
1221
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
1222
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
1223
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
1225
CREATE TABLE t1 ( a char(1), b char(1));
1226
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
1227
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
1228
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
1229
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
1230
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
1231
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
1232
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
1233
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
1234
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
1235
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
1236
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
1237
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
1238
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
1243
# SELECT(EXISTS * ...)optimisation
1245
create table t1 (a int, b int);
1246
insert into t1 values (1,2),(3,4);
1247
select * from t1 up where exists (select * from t1 where t1.a=up.a);
1248
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
1252
# Bug #4102: subselect in HAVING
1255
CREATE TABLE t1 (t1_a int);
1256
INSERT INTO t1 VALUES (1);
1257
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
1258
INSERT INTO t2 VALUES (1, 1), (1, 2);
1259
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
1260
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
1264
# Test problem with NULL and derived tables (Bug #4097)
1267
CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL);
1268
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
1269
CREATE TABLE t2 (id int(11) default NULL, pet varchar(10) default NULL);
1270
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
1271
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
1275
# Aggregate function comparation with ALL/ANY/SOME subselect
1277
CREATE TABLE `t1` ( `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1278
insert into t1 values (1);
1279
CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1280
insert into t2 values (1,2);
1281
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
1285
# BUG#5003 - like in subselect
1287
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin);
1288
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
1289
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
1290
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
1291
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
1292
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
1293
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
1297
# Optimized IN with compound index
1299
CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
1300
CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
1301
insert into t1 values (1,1),(1,2),(2,1),(2,2);
1302
insert into t2 values (1,2),(2,2);
1303
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1304
alter table t2 drop primary key;
1305
alter table t2 add key KEY1 (aid, bid);
1306
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1307
alter table t2 drop key KEY1;
1308
alter table t2 add primary key (bid, aid);
1309
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1313
# resolving fields of grouped outer SELECT
1315
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
1316
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
1317
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
1318
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
1319
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
1320
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
1321
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
1322
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
1325
create table t1 (x int);
1326
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;
1330
# Test of correct maybe_null flag returning by subquwery for temporary table
1333
CREATE TABLE `t1` ( `master` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `slave` int(10) unsigned NOT NULL default '0', `access` int(10) unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
1334
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);
1335
CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL default '0', `pid` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `level` tinyint(4) unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
1336
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');
1338
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;
1339
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;
1343
# Subselect in non-select command just after connection
1345
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
1347
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
1350
# primary query with temporary table and subquery with groupping
1352
create table t1 (a int, b int);
1353
create table t2 (a int, b int);
1354
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
1355
insert into t2 values (1,3),(2,1);
1356
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
1360
# Equal operation under row and empty subquery
1362
create table t1 (s1 int,s2 int);
1363
insert into t1 values (20,15);
1364
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
1370
create table t1 (s1 int);
1371
insert into t1 values (1),(null);
1372
select * from t1 where s1 < all (select s1 from t1);
1373
select s1, s1 < all (select s1 from t1) from t1;
1377
# reference on changable fields from subquery
1380
Code char(3) NOT NULL default '',
1381
Name char(52) NOT NULL default '',
1382
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1383
Region char(26) NOT NULL default '',
1384
SurfaceArea float(10,2) NOT NULL default '0.00',
1385
IndepYear smallint(6) default NULL,
1386
Population int(11) NOT NULL default '0',
1387
LifeExpectancy float(3,1) default NULL,
1388
GNP float(10,2) default NULL,
1389
GNPOld float(10,2) default NULL,
1390
LocalName char(45) NOT NULL default '',
1391
GovernmentForm char(45) NOT NULL default '',
1392
HeadOfState char(60) default NULL,
1393
Capital int(11) default NULL,
1394
Code2 char(2) NOT NULL default ''
1396
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
1397
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');
1398
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');
1399
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');
1400
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
1401
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
1402
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
1407
# Test for BUG#7885: Server crash when 'any' subselect compared to
1408
# non-existant field.
1410
create table t1 (a1 int);
1411
create table t2 (b1 int);
1413
select * from t1 where a2 > any(select b1 from t2);
1414
select * from t1 where a1 > any(select b1 from t2);
1419
# Comparison subquery with * and row
1421
create table t1 (a integer, b integer);
1422
select (select * from t1) = (select 1,2);
1423
select (select 1,2) = (select * from t1);
1424
# queries whih can be converted to IN
1425
select row(1,2) = ANY (select * from t1);
1426
select row(1,2) != ALL (select * from t1);
1430
# Comparison subquery and row with nested rows
1432
create table t1 (a integer, b integer);
1434
select row(1,(2,2)) in (select * from t1 );
1436
select row(1,(2,2)) = (select * from t1 );
1438
select (select * from t1) = row(1,(2,2));
1442
# Forward reference detection
1444
create table t1 (a integer);
1445
insert into t1 values (1);
1447
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
1449
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
1450
select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );
1452
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
1459
categoryId int(11) NOT NULL,
1460
courseId int(11) NOT NULL,
1461
startDate datetime NOT NULL,
1462
endDate datetime NOT NULL,
1463
createDate datetime NOT NULL,
1464
modifyDate timestamp NOT NULL,
1465
attributes text NOT NULL
1467
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
1468
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1469
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1470
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
1471
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
1472
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1473
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1474
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
1475
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
1478
userId int(11) NOT NULL,
1479
courseId int(11) NOT NULL,
1480
date datetime NOT NULL
1482
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
1483
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
1484
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
1485
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
1486
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
1487
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
1491
groupId int(11) NOT NULL,
1492
parentId int(11) NOT NULL,
1493
startDate datetime NOT NULL,
1494
endDate datetime NOT NULL,
1495
createDate datetime NOT NULL,
1496
modifyDate timestamp NOT NULL,
1499
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
1502
id int(11) NOT NULL,
1503
groupTypeId int(11) NOT NULL,
1504
groupKey varchar(50) NOT NULL,
1508
createDate datetime NOT NULL,
1509
modifyDate timestamp NOT NULL
1511
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
1512
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
1515
userId int(11) NOT NULL,
1516
groupId int(11) NOT NULL,
1517
createDate datetime NOT NULL,
1518
modifyDate timestamp NOT NULL
1520
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
1523
count(distinct t2.userid) pass,
1525
count(t2.courseid) crse,
1528
date_format(date, '%b%y') as colhead
1530
join t1 on t2.courseid=t1.courseid
1543
select t4.id as parentid,
1544
t4.name as parentgroup,
1546
t4.name as groupname,
1549
) as gin on t5.groupid=gin.childid
1550
) as groupstuff on t2.userid = groupstuff.userid
1552
groupstuff.groupname, colhead , t2.courseid;
1554
drop table t1, t2, t3, t4, t5;
1557
# Transformation in left expression of subquery (BUG#8888)
1559
create table t1 (a int);
1560
insert into t1 values (1), (2), (3);
1561
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
1565
# single row subqueries and row operations (code covarage improvement)
1567
create table t1 (a int, b int);
1568
insert into t1 values (1,2);
1570
select 1 = (select * from t1);
1572
select (select * from t1) = 1;
1574
select (1,2) = (select a from t1);
1576
select (select a from t1) = (1,2);
1578
select (1,2,3) = (select * from t1);
1580
select (select * from t1) = (1,2,3);
1584
# Item_int_with_ref check (BUG#10020)
1587
`itemid` bigint(20) unsigned NOT NULL auto_increment,
1588
`sessionid` bigint(20) unsigned default NULL,
1589
`time` int(10) unsigned NOT NULL default '0',
1590
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
1592
`data` text collate latin1_general_ci NOT NULL,
1593
PRIMARY KEY (`itemid`)
1594
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
1595
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
1597
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
1598
`pid` int(10) unsigned NOT NULL default '0',
1599
`date` int(10) unsigned NOT NULL default '0',
1600
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
1601
PRIMARY KEY (`sessionid`)
1602
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
1603
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
1604
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;
1607
# BUG#11821 : Select from subselect using aggregate function on an enum
1609
create table t1 (fld enum('0','1'));
1610
insert into t1 values ('1');
1611
select * from (select max(fld) from t1) as foo;
1615
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
1618
CREATE TABLE t1 (one int, two int, flag char(1));
1619
CREATE TABLE t2 (one int, two int, flag char(1));
1620
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1621
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1624
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
1626
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
1628
insert into t2 values (null,null,'N');
1629
insert into t2 values (null,3,'0');
1630
insert into t2 values (null,5,'0');
1631
insert into t2 values (10,null,'0');
1632
insert into t1 values (10,3,'0');
1633
insert into t1 values (10,5,'0');
1634
insert into t1 values (10,10,'0');
1635
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
1636
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
1637
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
1638
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
1639
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
1640
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
1641
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
1642
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;
1646
# Bug #12392: where cond with IN predicate for rows and NULL values in table
1649
CREATE TABLE t1 (a char(5), b char(5));
1650
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
1652
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
1657
# Bug #11479: subquery over left join with an empty inner table
1660
CREATE TABLE t1 (a int);
1661
CREATE TABLE t2 (a int, b int);
1662
CREATE TABLE t3 (b int NOT NULL);
1663
INSERT INTO t1 VALUES (1), (2), (3), (4);
1664
INSERT INTO t2 VALUES (1,10), (3,30);
1666
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
1667
WHERE t3.b IS NOT NULL OR t2.a > 10;
1669
WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
1670
WHERE t3.b IS NOT NULL OR t2.a > 10);
1672
DROP TABLE t1,t2,t3;
1675
# Bug#18503: Queries with a quantified subquery returning empty set may
1676
# return a wrong result.
1678
CREATE TABLE t1 (f1 INT);
1679
CREATE TABLE t2 (f2 INT);
1680
INSERT INTO t1 VALUES (1);
1681
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
1682
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
1683
INSERT INTO t2 VALUES (1);
1684
INSERT INTO t2 VALUES (2);
1685
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
1688
# BUG#20975 Wrong query results for subqueries within NOT
1689
create table t1 (s1 char);
1690
insert into t1 values (1),(2);
1692
select * from t1 where (s1 < any (select s1 from t1));
1693
select * from t1 where not (s1 < any (select s1 from t1));
1695
select * from t1 where (s1 < ALL (select s1+1 from t1));
1696
select * from t1 where not(s1 < ALL (select s1+1 from t1));
1698
select * from t1 where (s1+1 = ANY (select s1 from t1));
1699
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
1701
select * from t1 where (s1 = ALL (select s1/s1 from t1));
1702
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
1706
# Bug #16255: Subquery in where
1709
retailerID varchar(8) NOT NULL,
1710
statusID int(10) unsigned NOT NULL,
1711
changed datetime NOT NULL,
1712
UNIQUE KEY retailerID (retailerID, statusID, changed)
1715
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
1716
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
1717
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
1718
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
1719
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
1720
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
1723
where (r1.retailerID,(r1.changed)) in
1724
(SELECT r2.retailerId,(max(changed)) from t1 r2
1725
group by r2.retailerId);
1729
# Bug #21180: Subselect with index for both WHERE and ORDER BY
1730
# produces empty result
1732
create table t1(a int, primary key (a));
1733
insert into t1 values (10);
1735
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
1736
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
1738
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1739
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1740
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
1741
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1742
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1743
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
1745
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1746
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1747
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
1748
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
1749
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1750
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
1755
# Bug #21853: assert failure for a grouping query with
1756
# an ALL/ANY quantified subquery in HAVING
1760
field1 int NOT NULL,
1761
field2 int NOT NULL,
1762
field3 int NOT NULL,
1763
PRIMARY KEY (field1,field2,field3)
1766
fieldA int NOT NULL,
1767
fieldB int NOT NULL,
1768
PRIMARY KEY (fieldA,fieldB)
1771
INSERT INTO t1 VALUES
1772
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
1773
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
1775
SELECT field1, field2, COUNT(*)
1776
FROM t1 GROUP BY field1, field2;
1778
SELECT field1, field2
1780
GROUP BY field1, field2
1781
HAVING COUNT(*) >= ALL (SELECT fieldB
1782
FROM t2 WHERE fieldA = field1);
1783
SELECT field1, field2
1785
GROUP BY field1, field2
1786
HAVING COUNT(*) < ANY (SELECT fieldB
1787
FROM t2 WHERE fieldA = field1);
1792
# Bug #23478: not top-level IN subquery returning a non-empty result set
1793
# with possible NULL values by index access from the outer query
1796
CREATE TABLE t1(a int, INDEX (a));
1797
INSERT INTO t1 VALUES (1), (3), (5), (7);
1798
INSERT INTO t1 VALUES (NULL);
1800
CREATE TABLE t2(a int);
1801
INSERT INTO t2 VALUES (1),(2),(3);
1803
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
1804
SELECT a, a IN (SELECT a FROM t1) FROM t2;
1809
# Bug #11302: getObject() returns a String for a sub-query of type datetime
1811
CREATE TABLE t1 (a DATETIME);
1812
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
1814
CREATE TABLE t2 AS SELECT
1815
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
1816
FROM t1 WHERE a > '2000-01-01';
1817
SHOW CREATE TABLE t2;
1819
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
1820
SHOW CREATE TABLE t3;
1822
DROP TABLE t1,t2,t3;
1825
# Bug 24653: sorting by expressions containing subselects
1826
# that return more than one row
1829
CREATE TABLE t1 (a int);
1830
INSERT INTO t1 VALUES (2), (4), (1), (3);
1832
CREATE TABLE t2 (b int, c int);
1833
INSERT INTO t2 VALUES
1834
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
1836
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
1838
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
1839
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
1841
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
1843
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
1845
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
1848
SELECT a FROM t1 GROUP BY a
1849
HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
1850
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1852
SELECT a FROM t1 GROUP BY a
1853
HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
1854
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1856
SELECT a FROM t1 GROUP BY a
1857
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
1858
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1860
SELECT a FROM t1 GROUP BY a
1861
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
1862
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
1865
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
1866
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
1869
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
1870
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
1873
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
1874
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
1877
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
1878
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
1885
#decimal-related tests
1887
create table t1 (df decimal(5,1));
1888
insert into t1 values(1.1);
1889
insert into t1 values(2.2);
1891
select * from t1 where df <= all (select avg(df) from t1 group by df);
1892
select * from t1 where df >= all (select avg(df) from t1 group by df);
1895
create table t1 (df decimal(5,1));
1896
insert into t1 values(1.1);
1897
select 1.1 * exists(select * from t1);
1901
grp int(11) default NULL,
1902
a decimal(10,2) default NULL);
1904
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
1906
select min(a) from t1 group by grp;
1910
# Test for bug #9338: lame substitution of c1 instead of c2
1913
CREATE table t1 ( c1 integer );
1914
INSERT INTO t1 VALUES ( 1 );
1915
INSERT INTO t1 VALUES ( 2 );
1916
INSERT INTO t1 VALUES ( 3 );
1918
CREATE TABLE t2 ( c2 integer );
1919
INSERT INTO t2 VALUES ( 1 );
1920
INSERT INTO t2 VALUES ( 4 );
1921
INSERT INTO t2 VALUES ( 5 );
1923
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
1925
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
1926
WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
1931
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ
1933
CREATE TABLE t1 ( c1 integer );
1934
INSERT INTO t1 VALUES ( 1 );
1935
INSERT INTO t1 VALUES ( 2 );
1936
INSERT INTO t1 VALUES ( 3 );
1937
INSERT INTO t1 VALUES ( 6 );
1939
CREATE TABLE t2 ( c2 integer );
1940
INSERT INTO t2 VALUES ( 1 );
1941
INSERT INTO t2 VALUES ( 4 );
1942
INSERT INTO t2 VALUES ( 5 );
1943
INSERT INTO t2 VALUES ( 6 );
1945
CREATE TABLE t3 ( c3 integer );
1946
INSERT INTO t3 VALUES ( 7 );
1947
INSERT INTO t3 VALUES ( 8 );
1949
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
1950
WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
1952
DROP TABLE t1,t2,t3;
1955
# Item_int_with_ref check (BUG#10020)
1958
`itemid` bigint(20) unsigned NOT NULL auto_increment,
1959
`sessionid` bigint(20) unsigned default NULL,
1960
`time` int(10) unsigned NOT NULL default '0',
1961
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
1963
`data` text collate latin1_general_ci NOT NULL,
1964
PRIMARY KEY (`itemid`)
1965
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
1966
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
1968
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
1969
`pid` int(10) unsigned NOT NULL default '0',
1970
`date` int(10) unsigned NOT NULL default '0',
1971
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
1972
PRIMARY KEY (`sessionid`)
1973
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
1974
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
1975
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;
1979
# Correct building of equal fields list (do not include outer
1980
# fields) (BUG#6384)
1982
CREATE TABLE t1 (EMPNUM CHAR(3));
1983
CREATE TABLE t2 (EMPNUM CHAR(3) );
1984
INSERT INTO t1 VALUES ('E1'),('E2');
1985
INSERT INTO t2 VALUES ('E1');
1987
WHERE t1.EMPNUM NOT IN
1990
WHERE t1.EMPNUM = t2.EMPNUM);
1995
# Test for bug #11487: range access in a subquery
1998
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
1999
INSERT INTO t1 VALUES (1, 1);
2000
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
2001
PRIMARY KEY(select_id,values_id));
2002
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
2004
SELECT values_id FROM t1
2005
WHERE values_id IN (SELECT values_id FROM t2
2006
WHERE select_id IN (1, 0));
2007
SELECT values_id FROM t1
2008
WHERE values_id IN (SELECT values_id FROM t2
2009
WHERE select_id BETWEEN 0 AND 1);
2010
SELECT values_id FROM t1
2011
WHERE values_id IN (SELECT values_id FROM t2
2012
WHERE select_id = 0 OR select_id = 1);
2016
# BUG#11821 : Select from subselect using aggregate function on an enum
2018
create table t1 (fld enum('0','1'));
2019
insert into t1 values ('1');
2020
select * from (select max(fld) from t1) as foo;
2024
# Test for bug #11762: subquery with an aggregate function in HAVING
2027
CREATE TABLE t1 (a int, b int);
2028
CREATE TABLE t2 (c int, d int);
2029
CREATE TABLE t3 (e int);
2031
INSERT INTO t1 VALUES
2032
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
2033
INSERT INTO t2 VALUES
2034
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
2035
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
2037
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
2041
SELECT a FROM t1 GROUP BY a
2042
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
2043
SELECT a FROM t1 GROUP BY a
2044
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
2045
SELECT a FROM t1 GROUP BY a
2046
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
2047
SELECT a FROM t1 GROUP BY a
2048
HAVING a IN (SELECT c FROM t2
2049
WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
2050
SELECT a FROM t1 GROUP BY a
2051
HAVING a IN (SELECT c FROM t2
2052
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
2053
SELECT a FROM t1 GROUP BY a
2054
HAVING a IN (SELECT c FROM t2
2055
WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
2056
SELECT a FROM t1 GROUP BY a
2057
HAVING a IN (SELECT c FROM t2
2058
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
2059
SELECT a FROM t1 GROUP BY a
2060
HAVING a IN (SELECT c FROM t2
2061
WHERE MIN(b) < d AND
2062
EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
2064
SELECT a, SUM(a) FROM t1 GROUP BY a;
2067
WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
2068
SELECT a FROM t1 GROUP BY a
2069
HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
2073
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
2076
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
2078
SELECT t1.a FROM t1 GROUP BY t1.a
2079
HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
2080
HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
2081
HAVING SUM(t1.a+t2.c) < t3.e/4));
2082
SELECT t1.a FROM t1 GROUP BY t1.a
2083
HAVING t1.a > ALL(SELECT t2.c FROM t2
2084
WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
2085
HAVING SUM(t1.a+t2.c) < t3.e/4));
2087
SELECT t1.a FROM t1 GROUP BY t1.a
2088
HAVING t1.a > ALL(SELECT t2.c FROM t2
2089
WHERE EXISTS(SELECT t3.e FROM t3
2090
WHERE SUM(t1.a+t2.c) < t3.e/4));
2092
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
2094
SELECT t1.a FROM t1 GROUP BY t1.a
2095
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
2096
HAVING AVG(t2.c+SUM(t1.b)) > 20);
2097
SELECT t1.a FROM t1 GROUP BY t1.a
2098
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
2099
HAVING AVG(SUM(t1.b)) > 20);
2101
SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
2102
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
2103
HAVING t2.c+sum > 20);
2105
DROP TABLE t1,t2,t3;
2108
# Test for bug #16603: GROUP BY in a row subquery with a quantifier
2109
# when an index is defined on the grouping field
2111
CREATE TABLE t1 (a varchar(5), b varchar(10));
2112
INSERT INTO t1 VALUES
2113
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
2114
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
2116
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2118
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2120
ALTER TABLE t1 ADD INDEX(a);
2122
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2124
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2129
# Bug#17366: Unchecked Item_int results in server crash
2131
create table t1( f1 int,f2 int);
2132
insert into t1 values (1,1),(2,2);
2133
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';
2137
# Bug #18306: server crash on delete using subquery.
2140
create table t1 (c int, key(c));
2141
insert into t1 values (1142477582), (1142455969);
2142
create table t2 (a int, b int);
2143
insert into t2 values (2, 1), (1, 0);
2144
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
2148
# Bug#19077: A nested materialized derived table is used before being populated.
2150
create table t1 (i int, j bigint);
2151
insert into t1 values (1, 2), (2, 2), (3, 2);
2152
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
2156
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
2158
CREATE TABLE t1 (i BIGINT UNSIGNED);
2159
INSERT INTO t1 VALUES (10000000000000000000); # > MAX SIGNED BIGINT 9323372036854775807
2160
INSERT INTO t1 VALUES (1);
2162
CREATE TABLE t2 (i BIGINT UNSIGNED);
2163
INSERT INTO t2 VALUES (10000000000000000000); # same as first table
2164
INSERT INTO t2 VALUES (1);
2167
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
2170
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
2172
/* subquery test with cast*/
2173
SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);
2179
# Bug#20519: subselect with LIMIT M, N
2183
id bigint(20) unsigned NOT NULL auto_increment,
2184
name varchar(255) NOT NULL,
2187
INSERT INTO t1 VALUES
2188
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
2191
id bigint(20) unsigned NOT NULL auto_increment,
2192
mid bigint(20) unsigned NOT NULL,
2196
INSERT INTO t2 VALUES
2197
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
2198
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
2201
(SELECT date FROM t2 WHERE mid = t1.id
2202
ORDER BY date DESC LIMIT 0, 1) AS date_last,
2203
(SELECT date FROM t2 WHERE mid = t1.id
2204
ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
2207
(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
2208
ORDER BY date DESC LIMIT 1, 1) AS date_count
2211
(SELECT date FROM t2 WHERE mid = t1.id
2212
ORDER BY date DESC LIMIT 0, 1) AS date_last,
2213
(SELECT date FROM t2 WHERE mid = t1.id
2214
ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
2219
# Bug#20869: subselect with range access by DESC
2223
i1 int(11) NOT NULL default '0',
2224
i2 int(11) NOT NULL default '0',
2225
t datetime NOT NULL default '0000-00-00 00:00:00',
2226
PRIMARY KEY (i1,i2,t)
2228
INSERT INTO t1 VALUES
2229
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
2230
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
2231
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
2232
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
2233
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
2234
(24,2,'2005-05-27 12:40:06');
2237
i1 int(11) NOT NULL default '0',
2238
i2 int(11) NOT NULL default '0',
2239
t datetime default NULL,
2242
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
2246
WHERE t1.t = (SELECT t1.t FROM t1
2247
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
2248
ORDER BY t1.t DESC LIMIT 1);
2250
WHERE t1.t = (SELECT t1.t FROM t1
2251
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
2252
ORDER BY t1.t DESC LIMIT 1);
2257
# Bug#14654 : Cannot select from the same table twice within a UNION
2260
CREATE TABLE t1 (i INT);
2262
(SELECT i FROM t1) UNION (SELECT i FROM t1);
2263
SELECT * FROM t1 WHERE NOT EXISTS
2265
(SELECT i FROM t1) UNION
2270
--error ER_PARSE_ERROR
2272
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
2276
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
2279
explain select * from t1 where not exists
2280
((select t11.i from t1 t11) union (select t12.i from t1 t12));
2285
# Bug#21798: memory leak during query execution with subquery in column
2286
# list using a function
2288
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
2289
insert into t1 (a) values (FLOOR(rand() * 100));
2290
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2291
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2292
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2293
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2294
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2295
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2296
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2297
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2298
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2299
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2300
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2301
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2302
insert into t1 (a) select FLOOR(rand() * 100) from t1;
2305
(SELECT REPEAT(' ',250) FROM t1 i1
2306
WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a
2307
FROM t1 ORDER BY a LIMIT 5;
2311
# Bug #21540: Subqueries with no from and aggregate functions return
2313
CREATE TABLE t1 (a INT, b INT);
2314
CREATE TABLE t2 (a INT);
2315
INSERT INTO t2 values (1);
2316
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
2317
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
2318
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
2319
FROM t1 GROUP BY t1.a;
2320
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
2324
SELECT COUNT(DISTINCT t1.b)
2327
FROM t1 GROUP BY t1.a LIMIT 1)
2333
# Bug #21727: Correlated subquery that requires filesort:
2334
# slow with big sort_buffer_size
2337
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
2338
CREATE TABLE t2 (x int auto_increment, y int, z int,
2339
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
2345
eval INSERT INTO t1(a) VALUES(RAND()*1000);
2346
eval SELECT MAX(b) FROM t1 INTO @id;
2350
eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000);
2357
SET SESSION sort_buffer_size = 32 * 1024;
2359
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
2362
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
2364
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
2370
# Bug #25219: EXIST subquery with UNION over a mix of
2371
# correlated and uncorrelated selects
2374
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
2375
CREATE TABLE t2 (c int);
2377
INSERT INTO t1 VALUES ('aa', 1);
2378
INSERT INTO t2 VALUES (1);
2381
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
2383
SELECT c from t2 WHERE c=t1.c);
2385
INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
2388
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
2390
SELECT c from t2 WHERE c=t1.c);
2392
INSERT INTO t2 VALUES (2);
2393
CREATE TABLE t3 (c int);
2394
INSERT INTO t3 VALUES (1);
2397
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
2399
SELECT c from t2 WHERE c=t1.c);
2401
DROP TABLE t1,t2,t3;
2404
# Bug#21904 (parser problem when using IN with a double "(())")
2408
DROP TABLE IF EXISTS t1;
2409
DROP TABLE IF EXISTS t2;
2410
DROP TABLE IF EXISTS t1xt2;
2414
id_1 int(5) NOT NULL,
2415
t varchar(4) DEFAULT NULL
2419
id_2 int(5) NOT NULL,
2420
t varchar(4) DEFAULT NULL
2423
CREATE TABLE t1xt2 (
2424
id_1 int(5) NOT NULL,
2425
id_2 int(5) NOT NULL
2428
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
2430
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
2432
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
2434
# subselect returns 0 rows
2436
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2437
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2439
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2440
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2442
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2443
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2445
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2446
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2448
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2449
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
2451
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2452
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
2454
insert INTO t1xt2 VALUES (1, 12);
2456
# subselect returns 1 row
2458
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2459
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2461
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2462
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2464
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2465
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2467
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2468
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2470
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2471
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2473
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2474
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2476
insert INTO t1xt2 VALUES (2, 12);
2478
# subselect returns more than 1 row
2480
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2481
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2483
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2484
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2486
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2487
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2489
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2490
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2492
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2493
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
2495
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2496
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
2503
# Bug #26728: derived table with concatanation of literals in select list
2506
CREATE TABLE t1 (a int);
2507
INSERT INTO t1 VALUES (3), (1), (2);
2509
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
2510
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
2515
# Bug #27257: COUNT(*) aggregated in outer query
2518
CREATE TABLE t1 (a int, b int);
2519
CREATE TABLE t2 (m int, n int);
2520
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2521
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2524
(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
2528
(SELECT MIN(m) FROM t2 WHERE m = count(*))
2533
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
2538
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument
2541
CREATE TABLE t1 (a int, b int);
2542
CREATE TABLE t2 (m int, n int);
2543
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2544
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2546
SELECT COUNT(*) c, a,
2547
(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
2550
SELECT COUNT(*) c, a,
2551
(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
2557
# Bug#27321: Wrong subquery result in a grouping select
2559
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
2560
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
2561
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
2562
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
2565
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
2568
(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
2571
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
2572
FROM t1 WHERE t1.d=0 GROUP BY a;
2575
(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2576
LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2580
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2582
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2583
FROM t1 as tt GROUP BY tt.a;
2586
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2588
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
2589
FROM t1 as tt GROUP BY tt.a;
2594
# Bug #27363: nested aggregates in outer, subquery / sum(select
2597
CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
2598
CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
2600
--error ER_INVALID_GROUP_FUNC_USE
2601
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
2602
--error ER_INVALID_GROUP_FUNC_USE
2603
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
2605
--error ER_INVALID_GROUP_FUNC_USE
2607
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
2610
--error ER_INVALID_GROUP_FUNC_USE
2612
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
2618
# Bug #27807: Server crash when executing subquery with EXPLAIN
2620
CREATE TABLE t1 (a int, b int, KEY (a));
2621
INSERT INTO t1 VALUES (1,1),(2,1);
2622
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
2626
# Bug #28377: grouping query with a correlated subquery in WHERE condition
2629
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
2630
INSERT INTO t1 VALUES
2631
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
2632
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
2633
INSERT INTO t2 VALUES (7), (5), (1), (3);
2635
SELECT id, st FROM t1
2636
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
2637
SELECT id, st FROM t1
2638
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
2641
SELECT id, st FROM t1
2642
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
2643
SELECT id, st FROM t1
2644
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
2650
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
2651
# over a grouping subselect
2654
CREATE TABLE t1 (a int);
2656
INSERT INTO t1 VALUES (1), (2);
2659
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
2664
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1
2668
a varchar(255) default NULL,
2669
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2673
a varchar(255) default NULL
2676
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
2677
INSERT INTO t1 SELECT * FROM t1;
2678
INSERT INTO t1 SELECT * FROM t1;
2679
INSERT INTO t1 SELECT * FROM t1;
2680
INSERT INTO t1 SELECT * FROM t1;
2681
INSERT INTO t1 SELECT * FROM t1;
2682
INSERT INTO t1 SELECT * FROM t1;
2683
INSERT INTO t1 SELECT * FROM t1;
2684
INSERT INTO t1 SELECT * FROM t1;
2685
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
2686
INSERT INTO `t2` VALUES ('abcdefghijk');
2687
INSERT INTO `t2` VALUES ('asdf');
2689
SET session sort_buffer_size=8192;
2691
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
2697
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
2700
CREATE TABLE t1 (a INTEGER, b INTEGER);
2701
CREATE TABLE t2 (x INTEGER);
2702
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
2703
INSERT INTO t2 VALUES (1), (2);
2705
# wasn't failing, but should
2706
--error ER_SUBQUERY_NO_1_ROW
2707
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
2709
# fails as it should
2710
--error ER_SUBQUERY_NO_1_ROW
2711
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
2713
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
2716
# second test case from 27333
2717
CREATE TABLE t1 (a INT, b INT);
2718
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
2720
# returns no rows, when it should
2721
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
2722
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
2726
#test cases from 29297
2727
CREATE TABLE t1 (a INT);
2728
CREATE TABLE t2 (a INT);
2729
INSERT INTO t1 VALUES (1),(2);
2730
INSERT INTO t2 VALUES (1),(2);
2731
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
2732
--error ER_SUBQUERY_NO_1_ROW
2733
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
2734
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
2738
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
2741
CREATE TABLE t1 (a1 INT, a2 INT);
2742
CREATE TABLE t2 (b1 INT, b2 INT);
2744
INSERT INTO t1 VALUES (100, 200);
2745
INSERT INTO t1 VALUES (101, 201);
2746
INSERT INTO t2 VALUES (101, 201);
2747
INSERT INTO t2 VALUES (103, 203);
2749
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
2753
# Bug #28076: inconsistent binary/varbinary comparison
2756
CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
2757
INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
2759
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
2760
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
2762
CREATE INDEX I1 ON t1 (s1);
2763
CREATE INDEX I2 ON t1 (s2);
2765
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
2766
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
2769
INSERT INTO t1 VALUES (0x41,0x41);
2770
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
2774
CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
2775
CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
2776
CREATE TABLE t3 (a3 BINARY(2) default '0');
2777
INSERT INTO t1 VALUES (1),(2),(3),(4);
2778
INSERT INTO t2 VALUES (1),(2),(3);
2779
INSERT INTO t3 VALUES (1),(2),(3);
2780
SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
2781
SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
2782
DROP TABLE t1,t2,t3;
2784
CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
2785
CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
2786
CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
2787
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
2788
INSERT INTO t2 VALUES (2), (3), (4), (5);
2789
INSERT INTO t3 VALUES (10), (20), (30);
2790
SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
2791
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
2792
DROP TABLE t1, t2, t3;
2795
# Bug #30788: Inconsistent retrieval of char/varchar
2798
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
2799
INSERT INTO t1 VALUES ('a', 'aa');
2800
INSERT INTO t1 VALUES ('a', 'aaa');
2801
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2802
CREATE INDEX I1 ON t1 (a);
2803
CREATE INDEX I2 ON t1 (b);
2804
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2805
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2807
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
2808
INSERT INTO t2 SELECT * FROM t1;
2809
CREATE INDEX I1 ON t2 (a);
2810
CREATE INDEX I2 ON t2 (b);
2811
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2812
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2814
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2815
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2820
# Bug #32400: Complex SELECT query returns correct result only on some
2824
CREATE TABLE t1(a INT, b INT);
2825
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
2827
--error ER_BAD_FIELD_ERROR
2829
SELECT a AS out_a, MIN(b) FROM t1
2830
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
2833
--error ER_BAD_FIELD_ERROR
2834
SELECT a AS out_a, MIN(b) FROM t1
2835
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
2839
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
2840
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
2843
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
2844
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
2851
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
2854
CREATE TABLE t1 (a INT);
2855
CREATE TABLE t2 (a INT);
2857
INSERT INTO t1 VALUES (1),(2);
2858
INSERT INTO t2 VALUES (1),(2);
2860
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
2862
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
2866
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
2867
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
2872
# BUG#33794 "MySQL crashes executing specific query on specific dump"
2875
f7 varchar(32) collate utf8_bin NOT NULL default '',
2876
f10 varchar(32) collate utf8_bin default NULL,
2879
INSERT INTO t4 VALUES(1,1), (2,null);
2882
f4 varchar(32) collate utf8_bin NOT NULL default '',
2883
f2 varchar(50) collate utf8_bin default NULL,
2884
f3 varchar(10) collate utf8_bin default NULL,
2888
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
2891
f8 varchar(32) collate utf8_bin NOT NULL default '',
2892
f1 varchar(10) collate utf8_bin default NULL,
2893
f9 varchar(32) collate utf8_bin default NULL,
2896
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
2899
f6 varchar(32) collate utf8_bin NOT NULL default '',
2900
f5 varchar(50) collate utf8_bin default NULL,
2903
INSERT INTO t3 VALUES (1,null), (2,null);
2906
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
2907
IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
2911
FROM t2 VPC, t4 a2, t2 a3
2913
VPC.f4 = a2.f10 AND a3.f2 = a4
2920
t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
2923
DROP TABLE t1, t2, t3, t4;
2925
--echo End of 5.0 tests.
2928
# Test [NOT] IN truth table (both as top-level and general predicate).
2931
create table t_out (subcase char(3),
2932
a1 char(2), b1 char(2), c1 char(2));
2933
create table t_in (a2 char(2), b2 char(2), c2 char(2));
2935
insert into t_out values ('A.1','2a', NULL, '2a');
2936
#------------------------- A.2 - impossible
2937
insert into t_out values ('A.3', '2a', NULL, '2a');
2938
insert into t_out values ('A.4', '2a', NULL, 'xx');
2939
insert into t_out values ('B.1', '2a', '2a', '2a');
2940
insert into t_out values ('B.2', '2a', '2a', '2a');
2941
insert into t_out values ('B.3', '3a', 'xx', '3a');
2942
insert into t_out values ('B.4', 'xx', '3a', '3a');
2944
insert into t_in values ('1a', '1a', '1a');
2945
insert into t_in values ('2a', '2a', '2a');
2946
insert into t_in values (NULL, '2a', '2a');
2947
insert into t_in values ('3a', NULL, '3a');
2949
-- echo Test general IN semantics (not top-level)
2953
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
2954
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
2955
from t_out where subcase = 'A.1';
2957
-- echo case A.2 - impossible
2961
(a1, b1, c1) IN (select * from t_in) pred_in,
2962
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2963
from t_out where subcase = 'A.3';
2967
(a1, b1, c1) IN (select * from t_in) pred_in,
2968
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2969
from t_out where subcase = 'A.4';
2973
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
2974
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
2975
from t_out where subcase = 'B.1';
2979
(a1, b1, c1) IN (select * from t_in) pred_in,
2980
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2981
from t_out where subcase = 'B.2';
2985
(a1, b1, c1) IN (select * from t_in) pred_in,
2986
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2987
from t_out where subcase = 'B.3';
2991
(a1, b1, c1) IN (select * from t_in) pred_in,
2992
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
2993
from t_out where subcase = 'B.4';
2996
-- echo Test IN as top-level predicate, and
2997
-- echo as non-top level for cases A.3, B.3 (the only cases with NULL result).
3000
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3001
where subcase = 'A.1' and
3002
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
3004
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3005
where subcase = 'A.1' and
3006
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
3008
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3009
where subcase = 'A.1' and
3010
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
3013
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3014
where subcase = 'A.3' and
3015
(a1, b1, c1) IN (select * from t_in);
3017
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3018
where subcase = 'A.3' and
3019
(a1, b1, c1) NOT IN (select * from t_in);
3021
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3022
where subcase = 'A.3' and
3023
NOT((a1, b1, c1) IN (select * from t_in));
3024
# test non-top level result indirectly
3025
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
3026
where subcase = 'A.3' and
3027
((a1, b1, c1) IN (select * from t_in)) is NULL and
3028
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
3031
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3032
where subcase = 'A.4' and
3033
(a1, b1, c1) IN (select * from t_in);
3035
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3036
where subcase = 'A.4' and
3037
(a1, b1, c1) NOT IN (select * from t_in);
3039
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3040
where subcase = 'A.4' and
3041
NOT((a1, b1, c1) IN (select * from t_in));
3044
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3045
where subcase = 'B.1' and
3046
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
3048
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3049
where subcase = 'B.1' and
3050
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
3052
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3053
where subcase = 'B.1' and
3054
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
3057
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3058
where subcase = 'B.2' and
3059
(a1, b1, c1) IN (select * from t_in);
3061
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3062
where subcase = 'B.2' and
3063
(a1, b1, c1) NOT IN (select * from t_in);
3065
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3066
where subcase = 'B.2' and
3067
NOT((a1, b1, c1) IN (select * from t_in));
3070
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3071
where subcase = 'B.3' and
3072
(a1, b1, c1) IN (select * from t_in);
3074
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3075
where subcase = 'B.3' and
3076
(a1, b1, c1) NOT IN (select * from t_in);
3078
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3079
where subcase = 'B.3' and
3080
NOT((a1, b1, c1) IN (select * from t_in));
3081
# test non-top level result indirectly
3082
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
3083
where subcase = 'B.3' and
3084
((a1, b1, c1) IN (select * from t_in)) is NULL and
3085
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
3088
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3089
where subcase = 'B.4' and
3090
(a1, b1, c1) IN (select * from t_in);
3092
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3093
where subcase = 'B.4' and
3094
(a1, b1, c1) NOT IN (select * from t_in);
3096
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3097
where subcase = 'B.4' and
3098
NOT((a1, b1, c1) IN (select * from t_in));
3105
# Bug#20835 (literal string with =any values)
3107
CREATE TABLE t1 (s1 char(1));
3108
INSERT INTO t1 VALUES ('a');
3109
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
3113
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
3115
CREATE TABLE t1( a INT );
3116
INSERT INTO t1 VALUES (1),(2);
3118
CREATE TABLE t2( a INT, b INT );
3120
--error ER_PARSE_ERROR
3122
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
3123
--error ER_PARSE_ERROR
3125
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
3126
--error ER_PARSE_ERROR
3128
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
3130
--error ER_PARSE_ERROR
3134
SELECT a INTO @var FROM t1 WHERE a = 2
3137
--error ER_PARSE_ERROR
3141
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
3144
--error ER_PARSE_ERROR
3148
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
3151
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
3154
SELECT a FROM t1 WHERE a = 2
3156
SELECT a FROM t1 WHERE a = 2
3162
SELECT a FROM t1 WHERE a = 2
3164
SELECT a FROM t1 WHERE a = 2
3167
# This was not allowed previously. Possibly, it should be allowed on the future.
3168
# For now, the intent is to keep the fix as non-intrusive as possible.
3169
--error ER_PARSE_ERROR
3170
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
3171
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
3172
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
3173
--error ER_PARSE_ERROR
3174
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
3175
--error ER_PARSE_ERROR
3176
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
3177
--error ER_PARSE_ERROR
3178
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
3180
--error ER_PARSE_ERROR
3181
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
3182
--error ER_PARSE_ERROR
3183
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
3184
--error ER_PARSE_ERROR
3185
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
3187
--error ER_PARSE_ERROR
3188
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
3189
--error ER_PARSE_ERROR
3190
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
3191
--error ER_PARSE_ERROR
3192
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
3194
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
3195
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;
3196
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
3197
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
3200
# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias
3201
# UNION should not be allowed inside the parentheses, nor should
3204
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
3205
--error ER_PARSE_ERROR
3206
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
3207
--error ER_PARSE_ERROR
3208
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
3209
--error ER_PARSE_ERROR
3210
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
3211
--error ER_PARSE_ERROR
3212
SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;
3213
--error ER_PARSE_ERROR
3214
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
3216
SELECT * FROM t1 JOIN (t1 t1a) ON 1;
3217
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
3219
SELECT * FROM (t1 t1a);
3220
SELECT * FROM ((t1 t1a));
3222
SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;
3223
SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;
3225
SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;
3226
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
3228
# For the join, TABLE_LIST::select_lex == NULL
3229
# Check that we handle this.
3230
--error ER_PARSE_ERROR
3231
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
3233
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
3234
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );
3235
SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );
3237
--error ER_PARSE_ERROR
3238
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
3239
--error ER_PARSE_ERROR
3240
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
3241
--error ER_PARSE_ERROR
3242
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3244
SELECT * FROM t1 WHERE a = ( SELECT 1 );
3245
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );
3246
--error ER_PARSE_ERROR
3247
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
3248
--error ER_PARSE_ERROR
3249
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
3250
--error ER_PARSE_ERROR
3251
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
3253
--error ER_PARSE_ERROR
3254
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
3255
--error ER_PARSE_ERROR
3256
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
3257
--error ER_PARSE_ERROR
3258
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3260
--error ER_PARSE_ERROR
3261
SELECT ( SELECT 1 INTO @v );
3262
--error ER_PARSE_ERROR
3263
SELECT ( SELECT 1 INTO OUTFILE 'file' );
3264
--error ER_PARSE_ERROR
3265
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
3267
--error ER_PARSE_ERROR
3268
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
3269
--error ER_PARSE_ERROR
3270
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
3271
--error ER_PARSE_ERROR
3272
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3274
# Make sure context is popped when we leave the nested select
3275
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
3276
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
3278
# Make sure we have feature F561 (see .yy file)
3279
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
3281
# Make sure the parser does not allow nested UNIONs anywhere
3283
--error ER_PARSE_ERROR
3284
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
3285
--error ER_PARSE_ERROR
3286
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
3288
--error ER_PARSE_ERROR
3289
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3290
--error ER_PARSE_ERROR
3291
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
3292
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3293
SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
3295
--error ER_PARSE_ERROR
3296
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3297
--error ER_PARSE_ERROR
3298
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3299
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
3301
--error ER_PARSE_ERROR
3302
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3303
--error ER_PARSE_ERROR
3304
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3305
--error ER_PARSE_ERROR
3306
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3307
--error ER_PARSE_ERROR
3308
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
3310
--error ER_PARSE_ERROR
3311
SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3312
--error ER_PARSE_ERROR
3313
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3314
--error ER_PARSE_ERROR
3315
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3316
--error ER_PARSE_ERROR
3317
SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
3319
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3320
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3321
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3322
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
3324
--error ER_PARSE_ERROR
3325
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
3326
SELECT EXISTS(SELECT 1+1);
3327
--error ER_PARSE_ERROR
3328
SELECT EXISTS(SELECT 1+1 INTO @test);
3329
--error ER_PARSE_ERROR
3330
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
3332
--error ER_PARSE_ERROR
3333
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
3334
--error ER_PARSE_ERROR
3335
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
added
removed
mysql-test/t/subselect.test
