2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
6
explain extended select (select 2);
7
id select_type table type possible_keys key key_len ref rows filtered Extra
8
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
10
Note 1249 Select 2 was reduced during optimization
11
Note 1003 select 2 AS `(select 2)`
12
SELECT (SELECT 1) UNION SELECT (SELECT 2);
16
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
17
id select_type table type possible_keys key key_len ref rows filtered Extra
18
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
19
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
20
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
22
Note 1249 Select 2 was reduced during optimization
23
Note 1249 Select 4 was reduced during optimization
24
Note 1003 select 1 AS `(SELECT 1)` union select 2 AS `(SELECT 2)`
25
SELECT (SELECT (SELECT 0 UNION SELECT 0));
26
(SELECT (SELECT 0 UNION SELECT 0))
28
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
29
id select_type table type possible_keys key key_len ref rows filtered Extra
30
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
31
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
32
4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
33
NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL
35
Note 1249 Select 2 was reduced during optimization
36
Note 1003 select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))`
37
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
38
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
39
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;
40
ERROR 42S22: Reference 'b' not supported (forward reference in item list)
41
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
44
SELECT (SELECT a) as a;
45
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
46
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
47
id select_type table type possible_keys key key_len ref rows filtered Extra
48
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
49
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
50
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
52
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
53
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
54
Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
55
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
59
ERROR 42S22: Unknown column 'a' in 'field list'
60
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
63
SELECT 1 FROM (SELECT (SELECT a) b) c;
64
ERROR 42S22: Unknown column 'a' in 'field list'
65
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
68
SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1);
69
ERROR 21000: Operand should contain 1 column(s)
70
SELECT 1 IN (SELECT 1);
73
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
76
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
77
ERROR 42S22: Unknown column 'a' in 'field list'
78
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
79
ERROR 42S22: Unknown column 'a' in 'field list'
80
SELECT (SELECT 1,2,3) = ROW(1,2,3);
81
(SELECT 1,2,3) = ROW(1,2,3)
83
SELECT (SELECT 1,2,3) = ROW(1,2,1);
84
(SELECT 1,2,3) = ROW(1,2,1)
86
SELECT (SELECT 1,2,3) < ROW(1,2,1);
87
(SELECT 1,2,3) < ROW(1,2,1)
89
SELECT (SELECT 1,2,3) > ROW(1,2,1);
90
(SELECT 1,2,3) > ROW(1,2,1)
92
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
93
(SELECT 1,2,3) = ROW(1,2,NULL)
95
SELECT ROW(1,2,3) = (SELECT 1,2,3);
96
ROW(1,2,3) = (SELECT 1,2,3)
98
SELECT ROW(1,2,3) = (SELECT 1,2,1);
99
ROW(1,2,3) = (SELECT 1,2,1)
101
SELECT ROW(1,2,3) < (SELECT 1,2,1);
102
ROW(1,2,3) < (SELECT 1,2,1)
104
SELECT ROW(1,2,3) > (SELECT 1,2,1);
105
ROW(1,2,3) > (SELECT 1,2,1)
107
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
108
ROW(1,2,3) = (SELECT 1,2,NULL)
110
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
111
(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
113
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
114
(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
116
SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
117
(SELECT 1.5,2,'a') = ROW('1.5b',2,'b')
120
Warning 1292 Truncated incorrect DOUBLE value: '1.5b'
121
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
122
(SELECT 'b',2,'a') = ROW(1.5,2,'a')
124
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
125
(SELECT 1.5,2,'a') = ROW(1.5,'2','a')
127
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
128
(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
130
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
131
ERROR 21000: Operand should contain 1 column(s)
132
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
135
create table t1 (a int);
136
create table t2 (a int, b int);
137
create table t3 (a int);
138
create table t4 (a int not null, b int not null);
139
insert into t1 values (2);
140
insert into t2 values (1,7),(2,7);
141
insert into t4 values (4,8),(3,8),(5,9);
142
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
143
ERROR 42S22: Reference 'a1' not supported (forward reference in item list)
144
select (select a from t1 where t1.a=t2.a), a from t2;
145
(select a from t1 where t1.a=t2.a) a
148
select (select a from t1 where t1.a=t2.b), a from t2;
149
(select a from t1 where t1.a=t2.b) a
152
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
153
(select a from t1) a (select 1 union select 2 limit 1)
156
select (select a from t3), a from t2;
160
select * from t2 where t2.a=(select a from t1);
163
insert into t3 values (6),(7),(3);
164
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
168
(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;
173
(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);
179
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);
180
id select_type table type possible_keys key key_len ref rows filtered Extra
181
1 PRIMARY t2 ALL NULL NULL NULL NULL # 100.00 Using where
182
2 SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using filesort
183
3 UNION t4 ALL NULL NULL NULL NULL # 100.00 Using where
184
4 SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00
185
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL # NULL
187
Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` AS `a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) AS `max(t2.a)*4` from `test`.`t2`)) order by `a`)
188
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
189
(select a from t3 where a<t2.a*4 order by 1 desc limit 1) a
192
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
193
(select * from t2 where a>1) as tt;
194
(select t3.a from t3 where a<8 order by 1 desc limit 1) a
196
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
197
(select * from t2 where a>1) as tt;
198
id select_type table type possible_keys key key_len ref rows filtered Extra
199
1 PRIMARY <derived3> system NULL NULL NULL NULL # 100.00
200
3 DERIVED t2 ALL NULL NULL NULL NULL # 100.00 Using where
201
2 SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using where; Using filesort
203
Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
204
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);
207
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);
210
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);
212
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
213
b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
217
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
218
id select_type table type possible_keys key key_len ref rows filtered Extra
219
1 PRIMARY t4 ALL NULL NULL NULL NULL # 100.00
220
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00
221
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using where
223
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
224
Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
225
select * from t3 where exists (select * from t2 where t2.b=t3.a);
228
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
232
select * from t3 where a in (select b from t2);
235
select * from t3 where a not in (select b from t2);
239
select * from t3 where a = some (select b from t2);
242
select * from t3 where a <> any (select b from t2);
246
# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);
247
select * from t3 where a = all (select b from t2);
250
select * from t3 where a <> all (select b from t2);
254
insert into t2 values (100, 5);
255
select * from t3 where a < any (select b from t2);
259
select * from t3 where a < all (select b from t2);
262
select * from t3 where a >= any (select b from t2);
266
explain extended select * from t3 where a >= any (select b from t2);
267
id select_type table type possible_keys key key_len ref rows filtered Extra
268
1 PRIMARY t3 ALL NULL NULL NULL NULL # 100.00 Using where
269
2 SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00
271
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
272
select * from t3 where a >= all (select b from t2);
275
delete from t2 where a=100;
276
select * from t3 where a in (select a,b from t2);
277
ERROR 21000: Operand should contain 1 column(s)
278
select * from t3 where a in (select * from t2);
279
ERROR 21000: Operand should contain 1 column(s)
280
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
282
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
284
insert into t2 values (2,10);
285
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
288
delete from t2 where a=2 and b=10;
289
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
292
create temporary table t5 (a int) ENGINE=MyISAM;
293
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
294
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
297
insert into t5 values (5);
298
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
299
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
302
insert into t5 values (2);
303
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
304
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
307
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;
308
id select_type table type possible_keys key key_len ref rows filtered Extra
309
1 PRIMARY t2 ALL NULL NULL NULL NULL # 100.00
310
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00 Using where
311
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL # 100.00 Using where
312
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL
314
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
315
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
316
Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
317
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
318
ERROR 21000: Subquery returns more than 1 row
319
create temporary table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
320
create temporary table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
321
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
322
insert into t6 values (1,1),(1,2),(2,2),(1,3);
323
select * from t6 where exists (select * from t7 where uq = clinic_uq);
328
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
329
id select_type table type possible_keys key key_len ref rows filtered Extra
330
1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
331
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
333
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
334
Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
336
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
337
ERROR 23000: Column 'a' in field list is ambiguous
338
# different tipes & group functions
340
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
341
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
342
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
343
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
344
CREATE TABLE t1 (a varchar(20),b date NULL);
345
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
346
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
349
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
352
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
356
`pseudo` varchar(35) NOT NULL default '',
357
`email` varchar(60) NOT NULL default '',
358
PRIMARY KEY (`pseudo`),
359
UNIQUE KEY `email` (`email`)
361
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
362
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
363
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
364
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');
365
id select_type table type possible_keys key key_len ref rows filtered Extra
366
1 PRIMARY t8 const PRIMARY PRIMARY 142 const # 100.00
367
4 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00
368
2 SUBQUERY t8 const PRIMARY PRIMARY 142 const # 100.00
369
3 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00
371
Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))
372
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
373
t8 WHERE pseudo='joce');
374
ERROR 21000: Operand should contain 1 column(s)
375
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
377
ERROR 21000: Operand should contain 1 column(s)
378
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
381
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
382
ERROR 21000: Subquery returns more than 1 row
383
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
384
#searchconthardwarefr3 forumconthardwarefr7
385
CREATE TEMPORARY TABLE `t1` (
386
`topic` bigint NOT NULL default '0',
388
`pseudo` varchar(35) NOT NULL default '',
389
PRIMARY KEY (`pseudo`,`date`,`topic`),
390
KEY `topic` (`topic`)
391
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
392
INSERT INTO t1 (topic,date,pseudo) VALUES
393
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
394
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
395
id select_type table type possible_keys key key_len ref rows filtered Extra
396
1 SIMPLE t1 index NULL PRIMARY 153 NULL 2 100.00 Using where; Using index
398
Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')
399
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
400
id select_type table type possible_keys key key_len ref rows filtered Extra
401
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
402
2 SUBQUERY t1 index NULL PRIMARY 153 NULL 2 100.00 Using where; Using index
404
Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
405
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
408
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
409
(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
411
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
416
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
417
ERROR 21000: Subquery returns more than 1 row
418
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
419
id select_type table type possible_keys key key_len ref rows filtered Extra
420
1 PRIMARY t1 index NULL topic 8 NULL 2 100.00 Using index
421
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
422
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
423
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
425
Note 1003 select 1 AS `1` from `test`.`t1` where 1
427
#forumconthardwarefr7 searchconthardwarefr7
429
`numeropost` bigint NOT NULL auto_increment,
430
`maxnumrep` int NOT NULL default '0',
431
PRIMARY KEY (`numeropost`),
432
UNIQUE KEY `maxnumrep` (`maxnumrep`)
434
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
436
`mot` varchar(30) NOT NULL default '',
437
`topic` bigint NOT NULL default '0',
439
`pseudo` varchar(35) NOT NULL default '',
440
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
442
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
443
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
446
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;
450
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
451
ERROR 42S22: Unknown column 'a' in 'having clause'
452
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
453
ERROR 42S22: Unknown column 'a' in 'having clause'
454
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
455
mot topic date pseudo
456
joce 40143 2002-10-22 joce
457
joce 43506 2002-10-22 joce
458
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
459
mot topic date pseudo
460
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
461
mot topic date pseudo
462
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
463
mot topic date pseudo
464
joce 40143 2002-10-22 joce
465
joce 43506 2002-10-22 joce
466
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
467
mot topic date pseudo
468
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
469
mot topic date pseudo
470
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
471
mot topic date pseudo
472
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
473
mot topic date pseudo
474
joce 40143 2002-10-22 joce
475
joce 43506 2002-10-22 joce
476
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
477
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100)
478
joce 40143 2002-10-22 joce 1
479
joce 43506 2002-10-22 joce 1
480
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
481
mot topic date pseudo
482
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
483
mot topic date pseudo
484
joce 40143 2002-10-22 joce
485
joce 43506 2002-10-22 joce
486
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
487
mot topic date pseudo
488
joce 40143 2002-10-22 joce
489
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
490
mot topic date pseudo
491
joce 40143 2002-10-22 joce
492
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
493
mot topic date pseudo
494
joce 40143 2002-10-22 joce
495
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
496
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000)
497
joce 40143 2002-10-22 joce 1
498
joce 43506 2002-10-22 joce 0
500
#forumconthardwarefr7
502
`numeropost` bigint NOT NULL auto_increment,
503
`maxnumrep` int NOT NULL default '0',
504
PRIMARY KEY (`numeropost`),
505
UNIQUE KEY `maxnumrep` (`maxnumrep`)
507
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
508
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
509
ERROR 21000: Subquery returns more than 1 row
510
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
511
ERROR 21000: Subquery returns more than 1 row
513
create table t1 (a int);
514
insert into t1 values (1),(2),(3);
515
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
522
CREATE TEMPORARY TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;
523
INSERT INTO t1 VALUES ();
524
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
525
ERROR 21000: Subquery returns more than 1 row
529
`numeropost` bigint NOT NULL default '0',
530
`numreponse` int NOT NULL auto_increment,
531
`pseudo` varchar(35) NOT NULL default '',
532
PRIMARY KEY (`numeropost`,`numreponse`),
533
UNIQUE KEY `numreponse` (`numreponse`),
534
KEY `pseudo` (`pseudo`,`numeropost`)
536
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
537
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
538
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
539
ERROR 42S22: Unknown column 'a' in 'having clause'
540
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
541
numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)
542
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
543
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
544
ERROR 21000: Subquery returns more than 1 row
545
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
546
id select_type table type possible_keys key key_len ref rows filtered Extra
547
1 SIMPLE t1 ref PRIMARY PRIMARY 8 const # 100.00
549
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = 1)
550
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
551
id select_type table type possible_keys key key_len ref rows filtered Extra
552
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 12 const,const # 100.00
553
2 SUBQUERY t1 ref PRIMARY PRIMARY 8 # 100.00
555
Note 1003 select '3' AS `numreponse` from `test`.`t1` where 1
557
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
558
INSERT INTO t1 VALUES (1);
559
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
563
#update with subselects
564
create table t1 (a int NOT NULL, b int, primary key (a));
565
create table t2 (a int NOT NULL, b int, primary key (a));
566
insert into t1 values (0, 10),(1, 11),(2, 12);
567
insert into t2 values (1, 21),(2, 22),(3, 23);
573
update t1 set b= (select b from t1);
574
ERROR HY000: You can't specify target table 't1' for update in FROM clause
575
update t1 set b= (select b from t2);
576
ERROR 21000: Subquery returns more than 1 row
577
update t1 set b= (select b from t2 where t1.a = t2.a);
584
#delete with subselects
585
create table t1 (a int NOT NULL, b int, primary key (a));
586
create table t2 (a int NOT NULL, b int, primary key (a));
587
insert into t1 values (0, 10),(1, 11),(2, 12);
588
insert into t2 values (1, 21),(2, 12),(3, 23);
594
select * from t1 where b = (select b from t2 where t1.a = t2.a);
597
delete from t1 where b = (select b from t1);
598
ERROR HY000: You can't specify target table 't1' for update in FROM clause
599
delete from t1 where b = (select b from t2);
600
ERROR 21000: Subquery returns more than 1 row
601
delete from t1 where b = (select b from t2 where t1.a = t2.a);
607
#insert with subselects
608
CREATE TABLE t1 (x int);
609
create table t2 (a int);
610
create table t3 (b int);
611
insert into t2 values (1);
612
insert into t3 values (1),(2);
613
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
614
ERROR HY000: You can't specify target table 't1' for update in FROM clause
615
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
616
ERROR 21000: Subquery returns more than 1 row
617
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
621
insert into t2 values (1);
622
INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
627
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
634
# After this, only data based on old t1 records should have been added.
635
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
644
INSERT INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
645
ERROR 42S22: Unknown column 'x' in 'field list'
646
INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
657
#TODO: should be uncommented after bug 380 fix pushed
658
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
660
drop table t1, t2, t3;
661
#replace with subselects
662
CREATE TABLE t1 (x int not null, y int, primary key (x));
663
create table t2 (a int);
664
create temporary table t3 (a int) ENGINE=MyISAM;
665
insert into t2 values (1);
666
insert into t3 values (1),(2);
669
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
670
ERROR HY000: You can't specify target table 't1' for update in FROM clause
671
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
672
ERROR 21000: Subquery returns more than 1 row
673
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
677
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
681
replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
686
replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
691
replace into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
697
drop table t1, t2, t3;
698
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
699
ERROR HY000: No tables used
700
CREATE TABLE t2 (id int default NULL, KEY id (id));
701
INSERT INTO t2 VALUES (1),(2);
702
SELECT * FROM t2 WHERE id IN (SELECT 1);
705
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
706
id select_type table type possible_keys key key_len ref rows filtered Extra
707
1 PRIMARY t2 ref id id 5 const 1 100.00 Using where
709
Note 1249 Select 2 was reduced during optimization
710
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
711
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
714
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
717
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
718
id select_type table type possible_keys key key_len ref rows filtered Extra
719
1 PRIMARY t2 ref id id 5 const 1 100.00 Using where
721
Note 1249 Select 3 was reduced during optimization
722
Note 1249 Select 2 was reduced during optimization
723
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
724
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
725
id select_type table type possible_keys key key_len ref rows filtered Extra
726
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
727
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
728
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
729
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
731
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
732
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
734
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
737
INSERT INTO t2 VALUES ((SELECT * FROM t2));
738
ERROR HY000: You can't specify target table 't2' for update in FROM clause
739
INSERT INTO t2 VALUES ((SELECT id FROM t2));
740
ERROR HY000: You can't specify target table 't2' for update in FROM clause
745
CREATE TEMPORARY TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
746
INSERT INTO t1 values (1),(1);
747
UPDATE t2 SET id=(SELECT * FROM t1);
748
ERROR 21000: Subquery returns more than 1 row
751
create temporary table t1 (a int) ENGINE=MyISAM;
752
insert into t1 values (1),(2),(3);
753
select 1 IN (SELECT * from t1);
754
1 IN (SELECT * from t1)
756
select 10 IN (SELECT * from t1);
757
10 IN (SELECT * from t1)
759
select NULL IN (SELECT * from t1);
760
NULL IN (SELECT * from t1)
762
update t1 set a=NULL where a=2;
763
select 1 IN (SELECT * from t1);
764
1 IN (SELECT * from t1)
766
select 3 IN (SELECT * from t1);
767
3 IN (SELECT * from t1)
769
select 10 IN (SELECT * from t1);
770
10 IN (SELECT * from t1)
772
select 1 > ALL (SELECT * from t1);
773
1 > ALL (SELECT * from t1)
775
select 10 > ALL (SELECT * from t1);
776
10 > ALL (SELECT * from t1)
778
select 1 > ANY (SELECT * from t1);
779
1 > ANY (SELECT * from t1)
781
select 10 > ANY (SELECT * from t1);
782
10 > ANY (SELECT * from t1)
785
create temporary table t1 (a varchar(20)) ENGINE=MyISAM;
786
insert into t1 values ('A'),('BC'),('DEF');
787
select 'A' IN (SELECT * from t1);
788
'A' IN (SELECT * from t1)
790
select 'XYZS' IN (SELECT * from t1);
791
'XYZS' IN (SELECT * from t1)
793
select NULL IN (SELECT * from t1);
794
NULL IN (SELECT * from t1)
796
update t1 set a=NULL where a='BC';
797
select 'A' IN (SELECT * from t1);
798
'A' IN (SELECT * from t1)
800
select 'DEF' IN (SELECT * from t1);
801
'DEF' IN (SELECT * from t1)
803
select 'XYZS' IN (SELECT * from t1);
804
'XYZS' IN (SELECT * from t1)
806
select 'A' > ALL (SELECT * from t1);
807
'A' > ALL (SELECT * from t1)
809
select 'XYZS' > ALL (SELECT * from t1);
810
'XYZS' > ALL (SELECT * from t1)
812
select 'A' > ANY (SELECT * from t1);
813
'A' > ANY (SELECT * from t1)
815
select 'XYZS' > ANY (SELECT * from t1);
816
'XYZS' > ANY (SELECT * from t1)
819
create temporary table t1 (a float) ENGINE=MyISAM;
820
insert into t1 values (1.5),(2.5),(3.5);
821
select 1.5 IN (SELECT * from t1);
822
1.5 IN (SELECT * from t1)
824
select 10.5 IN (SELECT * from t1);
825
10.5 IN (SELECT * from t1)
827
select NULL IN (SELECT * from t1);
828
NULL IN (SELECT * from t1)
830
update t1 set a=NULL where a=2.5;
831
select 1.5 IN (SELECT * from t1);
832
1.5 IN (SELECT * from t1)
834
select 3.5 IN (SELECT * from t1);
835
3.5 IN (SELECT * from t1)
837
select 10.5 IN (SELECT * from t1);
838
10.5 IN (SELECT * from t1)
840
select 1.5 > ALL (SELECT * from t1);
841
1.5 > ALL (SELECT * from t1)
843
select 10.5 > ALL (SELECT * from t1);
844
10.5 > ALL (SELECT * from t1)
846
select 1.5 > ANY (SELECT * from t1);
847
1.5 > ANY (SELECT * from t1)
849
select 10.5 > ANY (SELECT * from t1);
850
10.5 > ANY (SELECT * from t1)
852
explain extended select (select a+1) from t1;
853
id select_type table type possible_keys key key_len ref rows filtered Extra
854
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
856
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
857
Note 1249 Select 2 was reduced during optimization
858
Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`
859
select (select a+1) from t1;
868
CREATE TEMPORARY TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a)) ENGINE=MyISAM;
869
CREATE TEMPORARY TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
870
INSERT INTO t1 VALUES (1),(2),(3),(4);
871
INSERT INTO t2 VALUES (1),(2),(3);
872
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
873
a t1.a in (select t2.a from t2)
878
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
879
id select_type table type possible_keys key key_len ref rows filtered Extra
880
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
881
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
883
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
884
CREATE TEMPORARY TABLE t3 (a int default '0') ENGINE=MyISAM;
885
INSERT INTO t3 VALUES (1),(2),(3);
886
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
887
a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
892
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
893
id select_type table type possible_keys key key_len ref rows filtered Extra
894
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
895
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
896
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
898
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
900
#LIMIT is not supported now
901
#create table t1 (a float) ENGINE=MyISAM;
903
#select 10.5 IN (SELECT * from t1 LIMIT 1);
905
#select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
908
#create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;
909
#create table t2 (a int) ENGINE=MyISAM;
910
#insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
911
#insert into t2 values (1),(2),(NULL);
912
#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;
913
#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;
914
#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;
917
#create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;
918
#insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
919
#select ROW(1, 1, 'a') IN (select a,b,c from t1);
920
#select ROW(1, 2, 'a') IN (select a,b,c from t1);
921
#select ROW(1, 1, 'a') IN (select b,a,c from t1);
922
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
923
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
924
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
925
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
926
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
927
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
929
#select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
931
#test of uncacheable subqueries
932
CREATE TABLE t1 (a int);
933
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
934
id select_type table type possible_keys key key_len ref rows filtered Extra
935
1 PRIMARY t1 ALL NULL NULL NULL NULL 0 0.00
936
2 SUBQUERY t1 ALL NULL NULL NULL NULL 0 0.00
938
Note 1003 select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
939
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
940
id select_type table type possible_keys key key_len ref rows filtered Extra
941
1 PRIMARY t1 ALL NULL NULL NULL NULL 0 0.00
942
2 SUBQUERY t1 ALL NULL NULL NULL NULL 0 0.00
944
Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
946
CREATE TEMPORARY TABLE `t1` (
947
`mot` varchar(30) NOT NULL default '',
948
`topic` bigint NOT NULL default '0',
949
`date` date NULL DEFAULT '2009-01-20',
950
`pseudo` varchar(35) NOT NULL default '',
951
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
952
KEY `pseudo` (`pseudo`,`date`,`topic`),
953
KEY `topic` (`topic`)
954
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
955
CREATE TEMPORARY TABLE `t2` (
956
`mot` varchar(30) NOT NULL default '',
957
`topic` bigint NOT NULL default '0',
958
`date` date NULL default '1997-08-29',
959
`pseudo` varchar(35) NOT NULL default '',
960
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
961
KEY `pseudo` (`pseudo`,`date`,`topic`),
962
KEY `topic` (`topic`)
963
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
964
CREATE TEMPORARY TABLE `t3` (
965
`numeropost` bigint NOT NULL auto_increment,
966
`maxnumrep` int NOT NULL default '0',
967
PRIMARY KEY (`numeropost`),
968
UNIQUE KEY `maxnumrep` (`maxnumrep`)
970
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
971
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
972
INSERT INTO t3 VALUES (1,1);
973
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
978
mot topic date pseudo
979
joce 1 2009-01-20 joce
980
test 2 2009-01-20 test
981
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
982
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
984
mot topic date pseudo
985
joce 1 2009-01-20 joce
986
drop table t1, t2, t3;
987
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
990
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
991
SHOW CREATE TABLE t1;
993
t1 CREATE TEMPORARY TABLE `t1` (
994
`a` INT NOT NULL DEFAULT '0',
995
`(SELECT 1)` INT NOT NULL DEFAULT '0'
996
) ENGINE=MyISAM COLLATE = utf8_general_ci
998
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
999
SHOW CREATE TABLE t1;
1001
t1 CREATE TEMPORARY TABLE `t1` (
1002
`a` INT NOT NULL DEFAULT '0',
1003
`(SELECT a)` INT NOT NULL DEFAULT '0'
1004
) ENGINE=MyISAM COLLATE = utf8_general_ci
1006
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
1007
SHOW CREATE TABLE t1;
1009
t1 CREATE TEMPORARY TABLE `t1` (
1010
`a` INT NOT NULL DEFAULT '0',
1011
`(SELECT a+0)` INT NOT NULL DEFAULT '0'
1012
) ENGINE=MyISAM COLLATE = utf8_general_ci
1014
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1018
SHOW CREATE TABLE t1;
1020
t1 CREATE TEMPORARY TABLE `t1` (
1022
) ENGINE=MyISAM COLLATE = utf8_general_ci
1024
create table t1 (a int);
1025
insert into t1 values (1), (2), (3);
1026
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
1028
id select_type table type possible_keys key key_len ref rows filtered Extra
1029
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00
1030
2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1031
3 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1033
Note 1003 select `test`.`t1`.`a` AS `a`,(select (select rand() AS `rand()` from `test`.`t1` limit 1) AS `(select rand() from t1 limit 1)` from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from `test`.`t1`
1038
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);
1039
ERROR 42S02: Table 'test.t1' doesn't exist
1044
ID int NOT NULL auto_increment,
1045
name char(35) NOT NULL default '',
1046
t2 char(3) NOT NULL default '',
1047
District char(20) NOT NULL default '',
1048
Population int NOT NULL default '0',
1051
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
1052
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
1053
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
1055
Code char(3) NOT NULL default '',
1056
Name char(52) NOT NULL default '',
1057
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1058
Region char(26) NOT NULL default '',
1059
SurfaceArea float(10,2) NOT NULL default '0.00',
1060
IndepYear int default NULL,
1061
Population int NOT NULL default '0',
1062
LifeExpectancy float(3,1) default NULL,
1063
GNP float(10,2) default NULL,
1064
GNPOld float(10,2) default NULL,
1065
LocalName char(45) NOT NULL default '',
1066
GovernmentForm char(45) NOT NULL default '',
1067
HeadOfState char(60) default NULL,
1068
Capital int default NULL,
1069
Code2 char(2) NOT NULL default '',
1072
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');
1073
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');
1074
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);
1075
Continent Name Population
1076
Oceania Sydney 3276207
1081
CREATE TEMPORARY TABLE `t1` (
1082
`id` bigint NOT NULL auto_increment,
1083
`pseudo` varchar(35) NOT NULL default '',
1085
UNIQUE KEY `pseudo` (`pseudo`)
1086
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
1087
INSERT INTO t1 (pseudo) VALUES ('test');
1088
SELECT 0 IN (SELECT 1 FROM t1 a);
1089
0 IN (SELECT 1 FROM t1 a)
1091
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1092
id select_type table type possible_keys key key_len ref rows filtered Extra
1093
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1094
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1096
Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
1097
INSERT INTO t1 (pseudo) VALUES ('test1');
1098
SELECT 0 IN (SELECT 1 FROM t1 a);
1099
0 IN (SELECT 1 FROM t1 a)
1101
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1102
id select_type table type possible_keys key key_len ref rows filtered Extra
1103
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1104
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1106
Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
1108
CREATE TEMPORARY TABLE `t1` (
1109
`i` int NOT NULL default '0',
1112
INSERT INTO t1 VALUES (1);
1113
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
1114
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1115
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1116
ERROR 42S22: Unknown column 't.i' in 'field list'
1122
# correct NULL in <CONSTANT> IN (SELECT ...)
1124
create temporary table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
1125
insert into t1 values (-1), (-4), (-2), (NULL);
1126
select -10 IN (select a from t1 FORCE INDEX (indexa));
1127
-10 IN (select a from t1 FORCE INDEX (indexa))
1131
# Test optimization for sub selects
1133
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
1134
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
1135
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
1136
id select_type table type possible_keys key key_len ref rows filtered Extra
1137
1 PRIMARY t1 ref salary salary 5 const # 100.00 Using where
1138
2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1140
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`))
1143
ID int NOT NULL auto_increment,
1144
SUB_ID int NOT NULL default '0',
1145
REF_ID int default NULL,
1146
REF_SUB int default '0',
1147
PRIMARY KEY (ID,SUB_ID),
1148
UNIQUE KEY t1_PK (ID,SUB_ID),
1149
KEY t1_FK (REF_ID,REF_SUB),
1150
KEY t1_REFID (REF_ID)
1152
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
1153
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
1157
# uninterruptable update
1159
create temporary table t1 (a int, b int) ENGINE=MyISAM;
1160
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1161
insert into t1 values (1,0), (2,0), (3,0);
1162
insert into t2 values (1,1), (2,1), (3,1), (2,2);
1163
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
1165
Error 1242 Subquery returns more than 1 row
1173
# reduced subselect in ORDER BY & GROUP BY clauses
1175
CREATE TEMPORARY TABLE `t1` (
1176
`id` bigint NOT NULL auto_increment,
1177
`pseudo` varchar(35) NOT NULL default '',
1178
`email` varchar(60) NOT NULL default '',
1180
UNIQUE KEY `email` (`email`),
1181
UNIQUE KEY `pseudo` (`pseudo`)
1182
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
1183
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
1184
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
1188
drop table if exists t1;
1189
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
1193
# IN subselect optimization test
1195
create temporary table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1196
create temporary table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
1197
create temporary table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1198
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1199
insert into t2 values (2), (3), (4), (5);
1200
insert into t3 values (10,3), (20,4), (30,5);
1201
select * from t2 where t2.a in (select a from t1);
1206
explain extended select * from t2 where t2.a in (select a from t1);
1207
id select_type table type possible_keys key key_len ref rows filtered Extra
1208
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1209
2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
1211
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1212
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1216
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1217
id select_type table type possible_keys key key_len ref rows filtered Extra
1218
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1219
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where
1221
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1222
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1226
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1227
id select_type table type possible_keys key key_len ref rows filtered Extra
1228
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1229
2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index
1230
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer
1232
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1233
drop table t1, t2, t3;
1234
create temporary table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
1235
create temporary table t2 (a int, index a (a)) ENGINE=MyISAM;
1236
create temporary table t3 (a int, b int, index a (a)) ENGINE=MyISAM;
1237
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1238
# making table large enough
1239
insert into t2 values (2), (3), (4), (5);
1240
insert into t3 values (10,3), (20,4), (30,5);
1241
select * from t2 where t2.a in (select a from t1);
1246
explain extended select * from t2 where t2.a in (select a from t1);
1247
id select_type table type possible_keys key key_len ref rows filtered Extra
1248
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1249
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index
1251
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1252
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1256
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1257
id select_type table type possible_keys key key_len ref rows filtered Extra
1258
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1259
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index
1261
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1262
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1266
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1267
id select_type table type possible_keys key key_len ref rows filtered Extra
1268
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1269
2 SUBQUERY t3 index a a 5 NULL 3 100.00 Using index
1270
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index; Using join buffer
1272
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1273
insert into t1 values (3,31);
1274
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1279
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
1283
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1284
id select_type table type possible_keys key key_len ref rows filtered Extra
1285
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1286
2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index
1288
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1289
drop table t1, t2, t3;
1291
# alloc_group_fields() working
1293
create temporary table t1 (a int, b int) ENGINE=MyISAM;
1294
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1295
create temporary table t3 (a int, b int) ENGINE=MyISAM;
1296
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
1297
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
1298
insert into t3 values (3,3), (2,2), (1,1);
1299
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;
1300
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)
1304
drop table t1,t2,t3;
1306
# aggregate functions in HAVING test
1308
create temporary table t1 (s1 int) ENGINE=MyISAM;
1309
create temporary table t2 (s1 int) ENGINE=MyISAM;
1310
insert into t1 values (1);
1311
insert into t2 values (1);
1312
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
1317
# update subquery with wrong field (to force name resolving
1318
# in UPDATE name space)
1320
create temporary table t1 (s1 int) ENGINE=MyISAM;
1321
create temporary table t2 (s1 int) ENGINE=MyISAM;
1322
insert into t1 values (1);
1323
insert into t2 values (1);
1324
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1325
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1330
#CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
1331
# s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;
1332
#INSERT INTO t1 VALUES ('z','?');
1334
#select * from t1 where s1 > (select max(s2) from t1);
1336
#select * from t1 where s1 > any (select max(s2) from t1);
1339
# aggregate functions reinitialization
1341
create table t1(toid int,rd int);
1342
create table t2(userid int,pmnew int,pmtotal int);
1343
insert into t2 values(1,0,0),(2,0,0);
1344
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);
1345
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);
1346
userid pmtotal pmnew calc_total calc_new
1353
create table t1 (s1 char(5));
1354
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
1355
ERROR 21000: Operand should contain 1 column(s)
1356
insert into t1 values ('tttt');
1357
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
1360
explain extended (select * from t1);
1361
id select_type table type possible_keys key key_len ref rows filtered Extra
1362
1 SIMPLE t1 ALL NULL NULL NULL NULL # 100.00
1364
Note 1003 (select `test`.`t1`.`s1` AS `s1` from `test`.`t1`)
1370
# IN optimisation test results
1372
create temporary table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1373
create temporary table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1374
insert into t1 values ('a1'),('a2'),('a3');
1375
insert into t2 values ('a1'),('a2');
1376
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1377
s1 s1 NOT IN (SELECT s1 FROM t2)
1381
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1382
s1 s1 = ANY (SELECT s1 FROM t2)
1386
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1387
s1 s1 <> ALL (SELECT s1 FROM t2)
1391
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1392
s1 s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')
1396
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1397
id select_type table type possible_keys key key_len ref rows filtered Extra
1398
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1399
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1401
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
1402
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1403
id select_type table type possible_keys key key_len ref rows filtered Extra
1404
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1405
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1407
Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
1408
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1409
id select_type table type possible_keys key key_len ref rows filtered Extra
1410
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1411
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1413
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
1414
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1415
id select_type table type possible_keys key key_len ref rows filtered Extra
1416
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1417
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Using where; Full scan on NULL key
1419
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
1422
# correct ALL optimisation
1424
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1425
create temporary table t3 (a int) ENGINE=MyISAM;
1426
insert into t3 values (6),(7),(3);
1427
select * from t3 where a >= all (select b from t2);
1432
explain extended select * from t3 where a >= all (select b from t2);
1433
id select_type table type possible_keys key key_len ref rows filtered Extra
1434
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1435
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1437
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`)))
1438
select * from t3 where a >= some (select b from t2);
1440
explain extended select * from t3 where a >= some (select b from t2);
1441
id select_type table type possible_keys key key_len ref rows filtered Extra
1442
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1443
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1445
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`)))
1446
select * from t3 where a >= all (select b from t2 group by 1);
1451
explain extended select * from t3 where a >= all (select b from t2 group by 1);
1452
id select_type table type possible_keys key key_len ref rows filtered Extra
1453
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1454
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1456
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL AS `b` from `test`.`t2` group by 1)))
1457
select * from t3 where a >= some (select b from t2 group by 1);
1459
explain extended select * from t3 where a >= some (select b from t2 group by 1);
1460
id select_type table type possible_keys key key_len ref rows filtered Extra
1461
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1462
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1464
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL AS `b` from `test`.`t2` group by 1)))
1465
select * from t3 where NULL >= any (select b from t2);
1467
explain extended select * from t3 where NULL >= any (select b from t2);
1468
id select_type table type possible_keys key key_len ref rows filtered Extra
1469
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1470
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1472
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1473
select * from t3 where NULL >= any (select b from t2 group by 1);
1475
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
1476
id select_type table type possible_keys key key_len ref rows filtered Extra
1477
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1478
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1480
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1481
select * from t3 where NULL >= some (select b from t2);
1483
explain extended select * from t3 where NULL >= some (select b from t2);
1484
id select_type table type possible_keys key key_len ref rows filtered Extra
1485
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1486
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1488
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1489
select * from t3 where NULL >= some (select b from t2 group by 1);
1491
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
1492
id select_type table type possible_keys key key_len ref rows filtered Extra
1493
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1494
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1496
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1498
# optimized static ALL/ANY with grouping
1500
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1501
select * from t3 where a > all (select max(b) from t2 group by a);
1505
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
1506
id select_type table type possible_keys key key_len ref rows filtered Extra
1507
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1508
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
1510
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) AS `max(b)` from `test`.`t2` group by `test`.`t2`.`a`)))
1513
# correct used_tables()
1515
CREATE TEMPORARY TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime, `last_update` datetime, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
1516
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());
1517
CREATE TEMPORARY TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;
1518
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);
1519
CREATE TEMPORARY TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;
1520
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);
1521
CREATE TEMPORARY TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
1522
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1523
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;
1524
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')
1527
-1 Should Not Return 0
1528
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;
1532
drop table t1,t2,t3,t4;
1536
CREATE TEMPORARY TABLE t1 (id int default NULL) ENGINE=MyISAM;
1537
INSERT INTO t1 VALUES (1),(5);
1538
CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;
1539
INSERT INTO t2 VALUES (2),(6);
1540
select * from t1 where (1,2,6) in (select * from t2);
1541
ERROR 21000: Operand should contain 3 column(s)
1544
# optimized ALL/ANY with union
1546
create table t1 (s1 char);
1547
insert into t1 values ('e');
1548
select * from t1 where 'f' > any (select s1 from t1);
1551
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1554
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1555
id select_type table type possible_keys key key_len ref rows filtered Extra
1556
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00
1557
2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1558
3 UNION t1 ALL NULL NULL NULL NULL # 100.00
1559
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL
1561
Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1
1564
# filesort in subquery (restoring join_tab)
1566
CREATE TEMPORARY TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
1567
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
1568
CREATE TEMPORARY TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
1569
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
1570
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;
1578
# unresolved field error
1580
create temporary table t1 (s1 int) ENGINE=MyISAM;
1581
create temporary table t2 (s1 int) ENGINE=MyISAM;
1582
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
1583
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
1584
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
1585
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1586
select count(*) from t2 group by t1.s2;
1587
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1590
# fix_fields() in add_ref_to_table_cond()
1592
CREATE TEMPORARY TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
1593
CREATE TEMPORARY TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
1594
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
1595
INSERT INTO t2 VALUES (100, 200, 'C');
1596
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
1599
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
1600
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
1601
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
1612
CREATE TEMPORARY TABLE `t1` (
1613
`id` int NOT NULL auto_increment,
1614
`id_cns` int NOT NULL default '0',
1615
`tipo` enum('','UNO','DUE') NOT NULL default '',
1616
`anno_dep` int NOT NULL default '0',
1617
`particolare` bigint NOT NULL default '0',
1618
`generale` bigint NOT NULL default '0',
1619
`bis` int NOT NULL default '0',
1621
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
1622
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`))
1624
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);
1625
CREATE TEMPORARY TABLE `t2` (
1626
`id` int NOT NULL auto_increment,
1627
`max_anno_dep` int NOT NULL default '0',
1628
PRIMARY KEY (`id`)) ENGINE=MyISAM;
1629
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
1630
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;
1631
id max_anno_dep PIPPO
1639
create temporary table t1 (a int) ENGINE=MyISAM;
1640
insert into t1 values (1), (2), (3);
1641
SET SQL_SELECT_LIMIT=1;
1642
select sum(a) from (select * from t1) as a;
1645
select 2 in (select * from t1);
1646
2 in (select * from t1)
1648
SET SQL_SELECT_LIMIT=default;
1651
# Bug #3118: subselect + order by
1653
CREATE TABLE t1 (a int, b int, INDEX (a));
1654
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1655
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1661
# Item_cond fix field
1663
create table t1(val varchar(10));
1664
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1665
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%');
1670
# ref_or_null replacing with ref
1672
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1673
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');
1674
select * from t1 where id not in (select id from t1 where id < 8);
1681
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);
1688
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1689
id select_type table type possible_keys key key_len ref rows filtered Extra
1690
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00 Using where
1691
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func # 100.00 Using where
1693
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
1694
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);
1695
id select_type table type possible_keys key key_len ref rows filtered Extra
1696
1 PRIMARY tt ALL NULL NULL NULL NULL # 100.00 Using where
1697
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id # 100.00 Using where
1699
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1700
Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
1701
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1702
create temporary table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1703
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');
1704
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);
1705
id text id text id text
1706
1 text1 1 text1 1 text1
1707
2 text2 2 text2 2 text2
1708
3 text3 3 text3 3 text3
1709
4 text4 4 text4 4 text4
1710
5 text5 5 text5 5 text5
1711
6 text6 6 text6 6 text6
1712
7 text7 7 text7 7 text7
1713
8 text8 8 text8 8 text8
1714
9 text9 9 text9 9 text9
1715
10 text10 10 text10 10 text10
1716
11 text11 11 text1 11 text11
1717
12 text12 12 text2 12 text12
1718
1000 text1000 NULL NULL 1000 text1000
1719
1001 text1001 NULL NULL 1000 text1000
1720
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);
1721
id select_type table type possible_keys key key_len ref rows filtered Extra
1722
1 SIMPLE a ALL NULL NULL NULL NULL # 100.00
1723
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id # 100.00
1724
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func # 100.00 Using where
1726
Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`)
1729
# Static tables & rund() in subqueries
1731
create temporary table t1 (a int) ENGINE=MyISAM;
1732
insert into t1 values (1);
1733
explain select benchmark(1000, (select a from t1 where a=rand()));
1734
id select_type table type possible_keys key key_len ref rows Extra
1735
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
1736
2 SUBQUERY t1 system NULL NULL NULL NULL 1
1741
create temporary table t1(id int) ENGINE=MyISAM;
1742
create temporary table t2(id int) ENGINE=MyISAM;
1743
create temporary table t3(flag int) ENGINE=MyISAM;
1744
select (select * from t3 where id not null) from t1, t2;
1745
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'null) from t1, t2' at line 1
1746
drop table t1,t2,t3;
1748
# aggregate functions (Bug #3505)
1750
CREATE TABLE t1 (id INT);
1751
CREATE TABLE t2 (id INT);
1752
INSERT INTO t1 VALUES (1), (2);
1753
INSERT INTO t2 VALUES (1);
1754
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);
1758
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1762
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;
1766
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;
1774
CREATE TABLE t1 ( a int, b int );
1775
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1776
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1779
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1782
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1785
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1789
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1793
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1797
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1800
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1803
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1806
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1810
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1814
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1819
ALTER TABLE t1 ADD INDEX (a);
1820
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1823
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1826
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1829
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1833
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1837
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1841
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1844
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1847
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1850
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1854
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1858
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1862
# having clause test
1863
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1866
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
1869
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
1872
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
1876
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
1880
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
1884
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
1887
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
1890
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
1893
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
1897
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
1901
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
1906
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1909
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1912
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1915
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1919
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1923
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1927
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1930
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1933
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1936
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1940
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1944
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1948
# union + having test
1949
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1952
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1955
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1958
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1962
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1966
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1970
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1973
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1976
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1979
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1983
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1987
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1992
# < > >= <= and = ALL/ <> ANY do not support row operation
1993
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1994
ERROR 21000: Operand should contain 1 column(s)
1995
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1996
ERROR 21000: Operand should contain 1 column(s)
1997
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1998
ERROR 21000: Operand should contain 1 column(s)
1999
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
2000
ERROR 21000: Operand should contain 1 column(s)
2001
SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
2002
ERROR 21000: Operand should contain 1 column(s)
2003
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
2004
ERROR 21000: Operand should contain 1 column(s)
2005
SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
2006
ERROR 21000: Operand should contain 1 column(s)
2007
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
2008
ERROR 21000: Operand should contain 1 column(s)
2009
# following should be converted to IN
2010
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
2011
ERROR 21000: Operand should contain 2 column(s)
2012
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
2013
ERROR 21000: Operand should contain 1 column(s)
2014
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
2016
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
2017
ERROR 21000: Operand should contain 2 column(s)
2018
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
2019
ERROR 21000: Operand should contain 1 column(s)
2020
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
2025
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
2028
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
2032
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
2035
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
2039
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);
2042
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);
2046
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);
2049
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);
2053
# without optimisation
2054
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2057
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2060
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2063
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2067
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2071
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2075
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2078
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2081
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2084
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2088
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2092
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2096
# without optimisation + having
2097
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
2100
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
2103
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
2106
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
2110
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
2114
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
2118
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
2121
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
2124
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
2127
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
2131
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
2135
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
2139
# EXISTS in string contence
2140
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
2141
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
2145
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
2146
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-')
2150
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
2151
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-')
2156
CREATE TABLE t1 ( a double, b double );
2157
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2158
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
2161
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
2164
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
2167
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
2171
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
2175
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
2179
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
2182
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
2185
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
2188
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
2192
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
2196
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
2201
CREATE TABLE t1 ( a char(1), b char(1));
2202
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
2203
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
2206
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
2209
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
2212
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
2216
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
2220
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
2224
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
2227
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
2230
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
2233
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
2237
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
2241
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
2247
# SELECT(EXISTS * ...)optimisation
2249
create table t1 (a int, b int);
2250
insert into t1 values (1,2),(3,4);
2251
select * from t1 up where exists (select * from t1 where t1.a=up.a);
2255
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
2256
id select_type table type possible_keys key key_len ref rows filtered Extra
2257
1 PRIMARY up ALL NULL NULL NULL NULL # 100.00 Using where
2258
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00 Using where
2260
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
2261
Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
2264
# Bug #4102: subselect in HAVING
2266
CREATE TEMPORARY TABLE t1 (t1_a int) ENGINE=MyISAM;
2267
INSERT INTO t1 VALUES (1);
2268
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
2269
INSERT INTO t2 VALUES (1, 1), (1, 2);
2270
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
2271
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
2276
# Test problem with NULL and derived tables (Bug #4097)
2278
CREATE TEMPORARY TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
2279
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
2280
CREATE TEMPORARY TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
2281
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
2282
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
2289
# Aggregate function comparation with ALL/ANY/SOME subselect
2291
CREATE TEMPORARY TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
2292
insert into t1 values (1);
2293
CREATE TEMPORARY TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
2294
insert into t2 values (1,2);
2295
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2300
# BUG#5003 - like in subselect
2302
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin);
2303
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2304
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2305
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
2306
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
2307
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
2308
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
2313
# Optimized IN with compound index
2315
CREATE TEMPORARY TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2316
CREATE TEMPORARY TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2317
insert into t1 values (1,1),(1,2),(2,1),(2,2);
2318
insert into t2 values (1,2),(2,2);
2319
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2323
alter table t2 drop primary key;
2324
alter table t2 add key KEY1 (aid, bid);
2325
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2329
alter table t2 drop key KEY1;
2330
alter table t2 add primary key (bid, aid);
2331
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2337
# resolving fields of grouped outer SELECT
2339
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
2340
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
2341
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
2342
howmanyvalues count(*)
2347
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2348
howmanyvalues mycount
2353
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
2354
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
2355
howmanyvalues mycount
2360
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2361
howmanyvalues mycount
2366
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
2367
howmanyvalues mycount
2373
create table t1 (x int);
2374
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;
2375
(select b.x from t1 as b where b.x=a.x)
2378
# Test of correct maybe_null flag returning by subquwery for temporary table
2381
CREATE TEMPORARY TABLE `t1` ( `master` int NOT NULL default '0', `map` int NOT NULL default '0', `slave` int NOT NULL default '0', `access` int NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`)) ENGINE=MyISAM;
2382
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);
2383
CREATE TEMPORARY TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ENGINE=MyISAM ;
2384
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');
2385
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;
2386
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2387
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;
2393
# Subselect in non-select command just after connection
2395
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
2397
# primary query with temporary table and subquery with groupping
2399
create temporary table t1 (a int, b int) ENGINE=MyISAM;
2400
create temporary table t2 (a int, b int) ENGINE=MyISAM;
2401
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
2402
insert into t2 values (1,3),(2,1);
2403
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
2404
a b (select max(b) from t2 where t1.b=t2.a)
2412
# Equal operation under row and empty subquery
2414
create table t1 (s1 int,s2 int);
2415
insert into t1 values (20,15);
2416
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
2422
create table t1 (s1 int);
2423
insert into t1 values (1),(null);
2424
select * from t1 where s1 < all (select s1 from t1);
2426
select s1, s1 < all (select s1 from t1) from t1;
2427
s1 s1 < all (select s1 from t1)
2432
# reference on changable fields from subquery
2435
Code char(3) NOT NULL default '',
2436
Name char(52) NOT NULL default '',
2437
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
2438
Region char(26) NOT NULL default '',
2439
SurfaceArea float(10,2) NOT NULL default '0.00',
2440
IndepYear int default NULL,
2441
Population int NOT NULL default '0',
2442
LifeExpectancy float(3,1) default NULL,
2443
GNP float(10,2) default NULL,
2444
GNPOld float(10,2) default NULL,
2445
LocalName char(45) NOT NULL default '',
2446
GovernmentForm char(45) NOT NULL default '',
2447
HeadOfState char(60) default NULL,
2448
Capital int default NULL,
2449
Code2 char(2) NOT NULL default ''
2451
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2452
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');
2453
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');
2454
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');
2455
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
2457
Note 1031 Table storage engine for 't1' doesn't have this option
2458
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
2459
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
2465
# Test for BUG#7885: Server crash when 'any' subselect compared to
2466
# non-existant field.
2468
create temporary table t1 (a1 int) ENGINE=MyISAM;
2469
create temporary table t2 (b1 int) ENGINE=MyISAM;
2470
select * from t1 where a2 > any(select b1 from t2);
2471
ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
2472
select * from t1 where a1 > any(select b1 from t2);
2476
# Comparison subquery with * and row
2478
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
2479
select (select * from t1) = (select 1,2);
2480
(select * from t1) = (select 1,2)
2482
select (select 1,2) = (select * from t1);
2483
(select 1,2) = (select * from t1)
2485
# queries whih can be converted to IN
2486
select row(1,2) = ANY (select * from t1);
2487
row(1,2) = ANY (select * from t1)
2489
select row(1,2) != ALL (select * from t1);
2490
row(1,2) != ALL (select * from t1)
2494
# Comparison subquery and row with nested rows
2496
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
2497
select row(1,(2,2)) in (select * from t1 );
2498
ERROR 21000: Operand should contain 2 column(s)
2499
select row(1,(2,2)) = (select * from t1 );
2500
ERROR 21000: Operand should contain 2 column(s)
2501
select (select * from t1) = row(1,(2,2));
2502
ERROR 21000: Operand should contain 1 column(s)
2505
# Forward reference detection
2507
create table t1 (a integer);
2508
insert into t1 values (1);
2509
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
2510
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2511
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2512
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2513
select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );
2514
xx 1 = ALL ( select 1 from t1 where 1 = xx )
2516
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2517
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2522
CREATE TEMPORARY TABLE t1 (
2523
categoryId int NOT NULL,
2524
courseId int NOT NULL,
2525
startDate datetime NOT NULL,
2526
endDate datetime NOT NULL,
2527
createDate datetime NOT NULL,
2528
modifyDate timestamp NOT NULL,
2529
attributes text NOT NULL)
2531
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2532
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2533
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2534
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
2535
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
2536
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2537
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2538
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2539
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
2540
CREATE TEMPORARY TABLE t2 (
2541
userId int NOT NULL,
2542
courseId int NOT NULL,
2543
date datetime NOT NULL)
2545
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
2546
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
2547
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
2548
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
2549
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
2550
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
2551
CREATE TEMPORARY TABLE t3 (
2552
groupId int NOT NULL,
2553
parentId int NOT NULL,
2554
startDate datetime NOT NULL,
2555
endDate datetime NOT NULL,
2556
createDate datetime NOT NULL,
2557
modifyDate timestamp NOT NULL,
2560
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
2561
CREATE TEMPORARY TABLE t4 (
2563
groupTypeId int NOT NULL,
2564
groupKey varchar(50) NOT NULL,
2568
createDate datetime NOT NULL,
2569
modifyDate timestamp NOT NULL)
2571
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
2572
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
2573
CREATE TEMPORARY TABLE t5 (
2574
userId int NOT NULL,
2575
groupId int NOT NULL,
2576
createDate datetime NOT NULL,
2577
modifyDate timestamp NOT NULL) ENGINE=MyISAM;
2578
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
2580
count(distinct t2.userid) pass,
2582
count(t2.courseid) crse,
2585
date_format(date, '%b%y') as colhead
2587
join t1 on t2.courseid=t1.courseid
2600
select t4.id as parentid,
2601
t4.name as parentgroup,
2603
t4.name as groupname,
2606
) as gin on t5.groupid=gin.childid
2607
) as groupstuff on t2.userid = groupstuff.userid
2609
groupstuff.groupname, colhead , t2.courseid;
2610
pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead
2611
1 5141 12 group2 12 group2 5 1 5 12 Aug04
2612
1 5141 12 group2 12 group2 5 1 1 41 Aug04
2613
1 5141 12 group2 12 group2 5 1 2 52 Aug04
2614
1 5141 12 group2 12 group2 5 1 2 53 Aug04
2615
1 5141 12 group2 12 group2 5 1 3 51 Oct04
2616
1 5141 12 group2 12 group2 5 1 1 86 Oct04
2617
1 5141 12 group2 12 group2 5 1 1 87 Oct04
2618
1 5141 12 group2 12 group2 5 1 2 88 Oct04
2619
1 5141 12 group2 12 group2 5 1 2 89 Oct04
2620
drop table t1, t2, t3, t4, t5;
2622
# Transformation in left expression of subquery (BUG#8888)
2624
create temporary table t1 (a int) ENGINE=MyISAM;
2625
insert into t1 values (1), (2), (3);
2626
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
2633
# single row subqueries and row operations (code covarage improvement)
2635
create temporary table t1 (a int, b int) ENGINE=MyISAM;
2636
insert into t1 values (1,2);
2637
select 1 = (select * from t1);
2638
ERROR 21000: Operand should contain 1 column(s)
2639
select (select * from t1) = 1;
2640
ERROR 21000: Operand should contain 2 column(s)
2641
select (1,2) = (select a from t1);
2642
ERROR 21000: Operand should contain 2 column(s)
2643
select (select a from t1) = (1,2);
2644
ERROR 21000: Operand should contain 1 column(s)
2645
select (1,2,3) = (select * from t1);
2646
ERROR 21000: Operand should contain 3 column(s)
2647
select (select * from t1) = (1,2,3);
2648
ERROR 21000: Operand should contain 2 column(s)
2651
# Item_int_with_ref check (BUG#10020)
2653
#CREATE TABLE `t1` (
2654
# `itemid` bigint NOT NULL auto_increment,
2655
# `sessionid` bigint default NULL,
2656
# `time` int NOT NULL default '0',
2657
# `data` text collate latin1_general_ci NOT NULL,
2658
# PRIMARY KEY (`itemid`)
2660
#INSERT INTO `t1` VALUES (1, 1, 1, '');
2661
#CREATE TABLE `t2` (
2662
# `sessionid` bigint NOT NULL auto_increment,
2663
# `pid` int NOT NULL default '0',
2664
# `date` int NOT NULL default '0',
2665
# `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
2666
# PRIMARY KEY (`sessionid`)
2668
#INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
2669
#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;
2671
# BUG#11821 : Select from subselect using aggregate function on an enum
2673
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
2674
insert into t1 values ('1');
2675
select * from (select max(fld) from t1) as foo;
2680
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
2682
CREATE TABLE t1 (one int, two int, flag char(1));
2683
CREATE TABLE t2 (one int, two int, flag char(1));
2684
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2685
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2687
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
2692
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
2696
insert into t2 values (null,null,'N');
2697
insert into t2 values (null,3,'0');
2698
insert into t2 values (null,5,'0');
2699
insert into t2 values (10,null,'0');
2700
insert into t1 values (10,3,'0');
2701
insert into t1 values (10,5,'0');
2702
insert into t1 values (10,10,'0');
2703
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
2713
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2717
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
2727
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2737
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
2747
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2748
id select_type table type possible_keys key key_len ref rows filtered Extra
2749
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2750
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
2752
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
2753
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2754
id select_type table type possible_keys key key_len ref rows filtered Extra
2755
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
2756
2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
2758
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key)))
2759
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;
2760
id select_type table type possible_keys key key_len ref rows filtered Extra
2761
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2762
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
2764
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
2767
# Bug #12392: where cond with IN predicate for rows and NULL values in table
2769
CREATE TEMPORARY TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
2770
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2771
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
2776
# Bug #11479: subquery over left join with an empty inner table
2778
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
2779
CREATE TEMPORARY TABLE t2 (a int, b int) ENGINE=MyISAM;
2780
CREATE TEMPORARY TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
2781
INSERT INTO t1 VALUES (1), (2), (3), (4);
2782
INSERT INTO t2 VALUES (1,10), (3,30);
2783
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2784
WHERE t3.b IS NOT NULL OR t2.a > 10;
2787
WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2788
WHERE t3.b IS NOT NULL OR t2.a > 10);
2794
DROP TABLE t1,t2,t3;
2796
# Bug#18503: Queries with a quantified subquery returning empty set may
2797
# return a wrong result.
2799
CREATE TEMPORARY TABLE t1 (f1 INT) ENGINE=MyISAM;
2800
CREATE TEMPORARY TABLE t2 (f2 INT) ENGINE=MyISAM;
2801
INSERT INTO t1 VALUES (1);
2802
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
2805
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
2808
INSERT INTO t2 VALUES (1);
2809
INSERT INTO t2 VALUES (2);
2810
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
2814
# BUG#20975 Wrong query results for subqueries within NOT
2815
create table t1 (s1 char);
2816
insert into t1 values (1),(2);
2817
select * from t1 where (s1 < any (select s1 from t1));
2820
select * from t1 where not (s1 < any (select s1 from t1));
2823
select * from t1 where (s1 < ALL (select s1+1 from t1));
2826
select * from t1 where not(s1 < ALL (select s1+1 from t1));
2829
select * from t1 where (s1+1 = ANY (select s1 from t1));
2832
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
2835
select * from t1 where (s1 = ALL (select s1/s1 from t1));
2838
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
2843
# Bug #16255: Subquery in where
2846
retailerID varchar(8) NOT NULL,
2847
statusID int NOT NULL,
2848
changed datetime NOT NULL,
2849
UNIQUE KEY retailerID (retailerID, statusID, changed));
2850
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
2851
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
2852
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
2853
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
2854
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
2855
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
2857
where (r1.retailerID,(r1.changed)) in
2858
(SELECT r2.retailerId,(max(changed)) from t1 r2
2859
group by r2.retailerId);
2860
retailerID statusID changed
2861
0026 2 2006-01-06 12:25:53
2862
0037 2 2006-01-06 12:25:53
2863
0048 1 2006-01-06 12:37:50
2864
0059 1 2006-01-06 12:37:50
2867
# Bug #21180: Subselect with index for both WHERE and ORDER BY
2868
# produces empty result
2870
create table t1(a int, primary key (a));
2871
insert into t1 values (10);
2872
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
2873
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
2874
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2875
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2876
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2877
id select_type table type possible_keys key key_len ref rows Extra
2878
1 PRIMARY t1 const PRIMARY PRIMARY 4 const #
2879
1 PRIMARY r const PRIMARY PRIMARY 4 const #
2880
2 DEPENDENT SUBQUERY t2 ALL b b 5 # Using filesort
2881
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2882
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2883
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2886
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2887
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2888
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2889
id select_type table type possible_keys key key_len ref rows Extra
2890
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1
2891
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2892
2 DEPENDENT SUBQUERY t2 ALL b b 5 3 Using filesort
2893
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2894
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2895
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2900
# Bug #21853: assert failure for a grouping query with
2901
# an ALL/ANY quantified subquery in HAVING
2903
CREATE TEMPORARY TABLE t1 (
2904
field1 int NOT NULL,
2905
field2 int NOT NULL,
2906
field3 int NOT NULL,
2907
PRIMARY KEY (field1,field2,field3))
2909
CREATE TEMPORARY TABLE t2 (
2910
fieldA int NOT NULL,
2911
fieldB int NOT NULL,
2912
PRIMARY KEY (fieldA,fieldB))
2914
INSERT INTO t1 VALUES
2915
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
2916
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
2917
SELECT field1, field2, COUNT(*)
2918
FROM t1 GROUP BY field1, field2;
2919
field1 field2 COUNT(*)
2923
SELECT field1, field2
2925
GROUP BY field1, field2
2926
HAVING COUNT(*) >= ALL (SELECT fieldB
2927
FROM t2 WHERE fieldA = field1);
2930
SELECT field1, field2
2932
GROUP BY field1, field2
2933
HAVING COUNT(*) < ANY (SELECT fieldB
2934
FROM t2 WHERE fieldA = field1);
2940
# Bug #23478: not top-level IN subquery returning a non-empty result set
2941
# with possible NULL values by index access from the outer query
2943
CREATE TEMPORARY TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
2944
INSERT INTO t1 VALUES (1), (3), (5), (7);
2945
INSERT INTO t1 VALUES (NULL);
2946
CREATE TEMPORARY TABLE t2(a int) ENGINE=MyISAM;
2947
INSERT INTO t2 VALUES (1),(2),(3);
2948
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
2949
id select_type table type possible_keys key key_len ref rows Extra
2950
1 PRIMARY t2 ALL NULL NULL NULL NULL 3
2951
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
2952
SELECT a, a IN (SELECT a FROM t1) FROM t2;
2953
a a IN (SELECT a FROM t1)
2959
# Bug #11302: getObject() returns a String for a sub-query of type datetime
2961
CREATE TABLE t1 (a DATETIME);
2962
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
2963
CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT
2964
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
2965
FROM t1 WHERE a > '2000-01-01';
2966
SHOW CREATE TABLE t2;
2968
t2 CREATE TEMPORARY TABLE `t2` (
2969
`sub_a` DATETIME DEFAULT NULL
2970
) ENGINE=MyISAM COLLATE = utf8_general_ci
2971
CREATE TEMPORARY TABLE t3 ENGINE=MyISAM AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
2972
SHOW CREATE TABLE t3;
2974
t3 CREATE TEMPORARY TABLE `t3` (
2975
`a` DATETIME DEFAULT NULL
2976
) ENGINE=MyISAM COLLATE = utf8_general_ci
2977
DROP TABLE t1,t2,t3;
2979
# Bug 24653: sorting by expressions containing subselects
2980
# that return more than one row
2982
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
2983
INSERT INTO t1 VALUES (2), (4), (1), (3);
2984
CREATE TABLE t2 (b int, c int);
2985
INSERT INTO t2 VALUES
2986
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
2987
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
2993
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
2994
ERROR 21000: Subquery returns more than 1 row
2995
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
3001
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
3002
ERROR 21000: Subquery returns more than 1 row
3003
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
3008
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
3009
ERROR 21000: Subquery returns more than 1 row
3010
SELECT a FROM t1 GROUP BY a
3011
HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
3012
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
3018
SELECT a FROM t1 GROUP BY a
3019
HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
3020
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
3021
ERROR 21000: Subquery returns more than 1 row
3022
SELECT a FROM t1 GROUP BY a
3023
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
3024
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
3027
SELECT a FROM t1 GROUP BY a
3028
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
3029
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
3030
ERROR 21000: Subquery returns more than 1 row
3032
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
3033
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
3040
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
3041
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
3042
ERROR 21000: Subquery returns more than 1 row
3044
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
3045
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
3052
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
3053
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
3054
ERROR 21000: Subquery returns more than 1 row
3058
#decimal-related tests
3060
create table t1 (df decimal(5,1));
3061
insert into t1 values(1.1);
3062
insert into t1 values(2.2);
3063
select * from t1 where df <= all (select avg(df) from t1 group by df);
3066
select * from t1 where df >= all (select avg(df) from t1 group by df);
3070
create temporary table t1 (df decimal(5,1)) ENGINE=MyISAM;
3071
insert into t1 values(1.1);
3072
select 1.1 * exists(select * from t1);
3073
1.1 * exists(select * from t1)
3076
CREATE TEMPORARY TABLE t1 (
3077
grp int default NULL,
3078
a decimal(10,2) default NULL) ENGINE=MyISAM;
3079
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
3089
select min(a) from t1 group by grp;
3097
# Test for bug #9338: lame substitution of c1 instead of c2
3099
CREATE temporary table t1 ( c1 integer ) ENGINE=MyISAM;
3100
INSERT INTO t1 VALUES ( 1 );
3101
INSERT INTO t1 VALUES ( 2 );
3102
INSERT INTO t1 VALUES ( 3 );
3103
CREATE TABLE t2 ( c2 integer );
3104
INSERT INTO t2 VALUES ( 1 );
3105
INSERT INTO t2 VALUES ( 4 );
3106
INSERT INTO t2 VALUES ( 5 );
3107
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
3110
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
3111
WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
3116
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ
3118
CREATE TEMPORARY TABLE t1 ( c1 integer ) ENGINE=MyISAM;
3119
INSERT INTO t1 VALUES ( 1 );
3120
INSERT INTO t1 VALUES ( 2 );
3121
INSERT INTO t1 VALUES ( 3 );
3122
INSERT INTO t1 VALUES ( 6 );
3123
CREATE TEMPORARY TABLE t2 ( c2 integer ) ENGINE=MyISAM;
3124
INSERT INTO t2 VALUES ( 1 );
3125
INSERT INTO t2 VALUES ( 4 );
3126
INSERT INTO t2 VALUES ( 5 );
3127
INSERT INTO t2 VALUES ( 6 );
3128
CREATE TEMPORARY TABLE t3 ( c3 integer ) ENGINE=MyISAM;
3129
INSERT INTO t3 VALUES ( 7 );
3130
INSERT INTO t3 VALUES ( 8 );
3131
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
3132
WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
3136
DROP TABLE t1,t2,t3;
3138
# Correct building of equal fields list (do not include outer
3139
# fields) (BUG#6384)
3141
CREATE TEMPORARY TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;
3142
CREATE TEMPORARY TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;
3143
INSERT INTO t1 VALUES ('E1'),('E2');
3144
INSERT INTO t2 VALUES ('E1');
3146
WHERE t1.EMPNUM NOT IN
3149
WHERE t1.EMPNUM = t2.EMPNUM);
3155
# Test for bug #11487: range access in a subquery
3157
CREATE TEMPORARY TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
3158
INSERT INTO t1 VALUES (1, 1);
3159
CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT,
3160
PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;
3161
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
3162
SELECT values_id FROM t1
3163
WHERE values_id IN (SELECT values_id FROM t2
3164
WHERE select_id IN (1, 0));
3167
SELECT values_id FROM t1
3168
WHERE values_id IN (SELECT values_id FROM t2
3169
WHERE select_id BETWEEN 0 AND 1);
3172
SELECT values_id FROM t1
3173
WHERE values_id IN (SELECT values_id FROM t2
3174
WHERE select_id = 0 OR select_id = 1);
3178
# BUG#11821 : Select from subselect using aggregate function on an enum
3180
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
3181
insert into t1 values ('1');
3182
select * from (select max(fld) from t1) as foo;
3187
# Test for bug #11762: subquery with an aggregate function in HAVING
3189
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3190
CREATE TEMPORARY TABLE t2 (c int, d int) ENGINE=MyISAM;
3191
CREATE TEMPORARY TABLE t3 (e int) ENGINE=MyISAM;
3192
INSERT INTO t1 VALUES
3193
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
3194
INSERT INTO t2 VALUES
3195
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
3196
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
3197
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
3217
SELECT a FROM t1 GROUP BY a
3218
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
3222
SELECT a FROM t1 GROUP BY a
3223
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
3226
SELECT a FROM t1 GROUP BY a
3227
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
3231
SELECT a FROM t1 GROUP BY a
3232
HAVING a IN (SELECT c FROM t2
3233
WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
3237
SELECT a FROM t1 GROUP BY a
3238
HAVING a IN (SELECT c FROM t2
3239
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
3243
SELECT a FROM t1 GROUP BY a
3244
HAVING a IN (SELECT c FROM t2
3245
WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
3248
SELECT a FROM t1 GROUP BY a
3249
HAVING a IN (SELECT c FROM t2
3250
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
3253
SELECT a FROM t1 GROUP BY a
3254
HAVING a IN (SELECT c FROM t2
3255
WHERE MIN(b) < d AND
3256
EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
3259
SELECT a, SUM(a) FROM t1 GROUP BY a;
3266
WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
3270
SELECT a FROM t1 GROUP BY a
3271
HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
3278
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
3284
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
3291
SELECT t1.a FROM t1 GROUP BY t1.a
3292
HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
3293
HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3294
HAVING SUM(t1.a+t2.c) < t3.e/4));
3298
SELECT t1.a FROM t1 GROUP BY t1.a
3299
HAVING t1.a > ALL(SELECT t2.c FROM t2
3300
WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3301
HAVING SUM(t1.a+t2.c) < t3.e/4));
3304
SELECT t1.a FROM t1 GROUP BY t1.a
3305
HAVING t1.a > ALL(SELECT t2.c FROM t2
3306
WHERE EXISTS(SELECT t3.e FROM t3
3307
WHERE SUM(t1.a+t2.c) < t3.e/4));
3308
ERROR HY000: Invalid use of group function
3309
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
3310
ERROR HY000: Invalid use of group function
3311
SELECT t1.a FROM t1 GROUP BY t1.a
3312
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3313
HAVING AVG(t2.c+SUM(t1.b)) > 20);
3318
SELECT t1.a FROM t1 GROUP BY t1.a
3319
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3320
HAVING AVG(SUM(t1.b)) > 20);
3324
SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
3325
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3326
HAVING t2.c+sum > 20);
3331
DROP TABLE t1,t2,t3;
3333
# Test for bug #16603: GROUP BY in a row subquery with a quantifier
3334
# when an index is defined on the grouping field
3335
CREATE TABLE t1 (a varchar(5), b varchar(10));
3336
INSERT INTO t1 VALUES
3337
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
3338
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
3339
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3345
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3346
id select_type table type possible_keys key key_len ref rows Extra
3347
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
3348
2 SUBQUERY t1 ALL NULL NULL NULL NULL # Using temporary; Using filesort
3349
ALTER TABLE t1 ADD INDEX(a);
3350
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3356
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3357
id select_type table type possible_keys key key_len ref rows Extra
3358
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
3359
2 SUBQUERY t1 ALL NULL NULL NULL NULL # Using temporary; Using filesort
3362
# Bug#17366: Unchecked Item_int results in server crash
3364
create table t1( f1 int,f2 int);
3365
insert into t1 values (1,1),(2,2);
3366
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';
3372
# Bug #18306: server crash on delete using subquery.
3374
create temporary table t1 (c int, key(c)) ENGINE=MyISAM;
3375
insert into t1 values (1142477582), (1142455969);
3376
create temporary table t2 (a int, b int) ENGINE=MyISAM;
3377
insert into t2 values (2, 1), (1, 0);
3378
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
3381
# Bug#19077: A nested materialized derived table is used before being populated.
3383
create table t1 (i int, j bigint);
3384
insert into t1 values (1, 2), (2, 2), (3, 2);
3385
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
3390
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
3392
CREATE TEMPORARY TABLE t1 (i BIGINT) ENGINE=MyISAM;
3393
INSERT INTO t1 VALUES (10000000000000000);
3394
INSERT INTO t1 VALUES (1);
3395
CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;
3396
INSERT INTO t2 VALUES (10000000000000000);
3397
INSERT INTO t2 VALUES (1);
3399
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
3404
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
3407
/* subquery test with cast*/
3408
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
3414
# Bug#20519: subselect with LIMIT M, N
3416
CREATE TEMPORARY TABLE t1 (
3417
id bigint NOT NULL auto_increment,
3418
name varchar(255) NOT NULL,
3421
INSERT INTO t1 VALUES
3422
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
3424
id bigint NOT NULL auto_increment,
3425
mid bigint NOT NULL,
3428
INSERT INTO t2 VALUES
3429
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
3430
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
3432
(SELECT date FROM t2 WHERE mid = t1.id
3433
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3434
(SELECT date FROM t2 WHERE mid = t1.id
3435
ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
3437
id name date_last date_next_to_last
3438
1 Balazs 2006-05-01 NULL
3439
2 Joe 2006-04-20 NULL
3440
3 Frank 2006-04-13 NULL
3442
(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
3443
ORDER BY date DESC LIMIT 1, 1) AS date_count
3450
(SELECT date FROM t2 WHERE mid = t1.id
3451
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3452
(SELECT date FROM t2 WHERE mid = t1.id
3453
ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
3455
id name date_last date_next_to_last
3456
1 Balazs 2006-05-01 2006-03-30
3457
2 Joe 2006-04-20 2006-04-06
3458
3 Frank 2006-04-13 NULL
3461
# Bug#20869: subselect with range access by DESC
3464
i1 int NOT NULL default '0',
3465
i2 int NOT NULL default '0',
3467
PRIMARY KEY (i1,i2,t));
3468
INSERT INTO t1 VALUES
3469
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
3470
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
3471
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
3472
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
3473
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
3474
(24,2,'2005-05-27 12:40:06');
3476
i1 int NOT NULL default '0',
3477
i2 int NOT NULL default '0',
3478
t datetime default NULL,
3480
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
3483
WHERE t1.t = (SELECT t1.t FROM t1
3484
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3485
ORDER BY t1.t DESC LIMIT 1);
3486
id select_type table type possible_keys key key_len ref rows Extra
3487
1 PRIMARY t2 ALL NULL NULL NULL NULL 1
3488
1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where; Using join buffer
3489
2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 2 Using where; Using filesort
3491
WHERE t1.t = (SELECT t1.t FROM t1
3492
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3493
ORDER BY t1.t DESC LIMIT 1);
3495
24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40
3498
# Bug#14654 : Cannot select from the same table twice within a UNION
3501
CREATE TABLE t1 (i INT);
3502
(SELECT i FROM t1) UNION (SELECT i FROM t1);
3504
SELECT * FROM t1 WHERE NOT EXISTS
3506
(SELECT i FROM t1) UNION
3512
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3513
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1
3515
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3517
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'union (select t12.i from t1 t12))
3519
explain select * from t1 where not exists
3520
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3521
id select_type table type possible_keys key key_len ref rows Extra
3522
1 PRIMARY t1 ALL NULL NULL NULL NULL #
3523
2 SUBQUERY t11 ALL NULL NULL NULL NULL #
3524
3 UNION t12 ALL NULL NULL NULL NULL #
3525
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL #
3528
# Bug #21540: Subqueries with no from and aggregate functions return
3530
CREATE TABLE t1 (a INT, b INT);
3531
CREATE TABLE t2 (a INT);
3532
INSERT INTO t2 values (1);
3533
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
3534
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
3535
(SELECT COUNT(DISTINCT t1.b) from t2)
3539
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3540
FROM t1 GROUP BY t1.a;
3541
(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3545
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
3546
COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))
3553
SELECT COUNT(DISTINCT t1.b)
3556
FROM t1 GROUP BY t1.a LIMIT 1)
3562
SELECT COUNT(DISTINCT t1.b)
3565
FROM t1 GROUP BY t1.a LIMIT 1)
3571
# Bug #21727: Correlated subquery that requires filesort:
3572
# slow with big sort_buffer_size
3574
CREATE TEMPORARY TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;
3575
CREATE TEMPORARY TABLE t2 (x int auto_increment, y int, z int,
3576
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;
3577
SET SESSION sort_buffer_size = 32 * 1024;
3579
Error 1292 Truncated incorrect sort_buffer_size value: '32768'
3581
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3585
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
3587
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3593
# Bug #25219: EXIST subquery with UNION over a mix of
3594
# correlated and uncorrelated selects
3596
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
3597
CREATE TABLE t2 (c int);
3598
INSERT INTO t1 VALUES ('aa', 1);
3599
INSERT INTO t2 VALUES (1);
3601
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3603
SELECT c from t2 WHERE c=t1.c);
3606
INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
3608
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3610
SELECT c from t2 WHERE c=t1.c);
3616
INSERT INTO t2 VALUES (2);
3617
CREATE TEMPORARY TABLE t3 (c int) ENGINE=MyISAM;
3618
INSERT INTO t3 VALUES (1);
3620
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
3622
SELECT c from t2 WHERE c=t1.c);
3628
DROP TABLE t1,t2,t3;
3630
# Bug#21904 (parser problem when using IN with a double "(())")
3632
DROP TABLE IF EXISTS t1;
3633
DROP TABLE IF EXISTS t2;
3634
DROP TABLE IF EXISTS t1xt2;
3635
CREATE TEMPORARY TABLE t1 (
3637
t varchar(4) DEFAULT NULL)
3639
CREATE TEMPORARY TABLE t2 (
3641
t varchar(4) DEFAULT NULL)
3643
CREATE TEMPORARY TABLE t1xt2 (
3647
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
3648
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
3649
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
3650
# subselect returns 0 rows
3651
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3652
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3654
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3655
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3657
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3658
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3660
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3661
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3667
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3668
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
3674
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3675
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
3681
insert INTO t1xt2 VALUES (1, 12);
3682
# subselect returns 1 row
3683
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3684
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3687
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3688
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3691
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3692
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3695
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3696
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3701
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3702
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3707
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3708
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3713
insert INTO t1xt2 VALUES (2, 12);
3714
# subselect returns more than 1 row
3715
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3716
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3720
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3721
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3725
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3726
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3730
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3731
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3735
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3736
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3740
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3741
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3749
# Bug #26728: derived table with concatanation of literals in select list
3751
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
3752
INSERT INTO t1 VALUES (3), (1), (2);
3753
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
3758
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
3765
# Bug #27257: COUNT(*) aggregated in outer query
3767
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3768
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
3769
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3770
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3772
(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3774
COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3779
(SELECT MIN(m) FROM t2 WHERE m = count(*))
3781
COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))
3787
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
3793
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument
3795
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3796
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
3797
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3798
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3799
SELECT COUNT(*) c, a,
3800
(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3802
c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3806
SELECT COUNT(*) c, a,
3807
(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3809
c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3815
# Bug#27321: Wrong subquery result in a grouping select
3817
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
3818
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
3819
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
3820
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
3822
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
3829
(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
3836
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
3837
FROM t1 WHERE t1.d=0 GROUP BY a;
3843
(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3844
LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3864
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3866
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3867
FROM t1 as tt GROUP BY tt.a;
3873
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3875
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
3876
FROM t1 as tt GROUP BY tt.a;
3883
# Bug #27363: nested aggregates in outer, subquery / sum(select
3886
CREATE TABLE t1 (a INT);
3887
INSERT INTO t1 values (1),(1),(1),(1);
3888
CREATE TEMPORARY TABLE t2 (x INT) ENGINE=MyISAM;
3889
INSERT INTO t1 values (1000),(1001),(1002);
3890
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
3891
ERROR HY000: Invalid use of group function
3892
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
3893
ERROR HY000: Invalid use of group function
3895
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
3897
ERROR HY000: Invalid use of group function
3899
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
3901
ERROR HY000: Invalid use of group function
3904
# Bug #27807: Server crash when executing subquery with EXPLAIN
3906
CREATE TABLE t1 (a int, b int, KEY (a));
3907
INSERT INTO t1 VALUES (1,1),(2,1);
3908
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
3909
id select_type table type possible_keys key key_len ref rows Extra
3910
1 PRIMARY t1 ref a a 5 const 1 Using where
3911
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3914
# Bug #28377: grouping query with a correlated subquery in WHERE condition
3916
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
3917
INSERT INTO t1 VALUES
3918
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
3919
CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
3920
INSERT INTO t2 VALUES (7), (5), (1), (3);
3921
SELECT id, st FROM t1
3922
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3927
SELECT id, st FROM t1
3928
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3934
SELECT id, st FROM t1
3935
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3939
SELECT id, st FROM t1
3940
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3947
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
3948
# over a grouping subselect
3950
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
3951
INSERT INTO t1 VALUES (1), (2);
3953
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
3954
id select_type table type possible_keys key key_len ref rows filtered Extra
3955
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
3956
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
3958
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
3961
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1
3964
a varchar(255) default NULL,
3965
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3968
a varchar(255) default NULL);
3969
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
3970
INSERT INTO t1 SELECT * FROM t1;
3971
INSERT INTO t1 SELECT * FROM t1;
3972
INSERT INTO t1 SELECT * FROM t1;
3973
INSERT INTO t1 SELECT * FROM t1;
3974
INSERT INTO t1 SELECT * FROM t1;
3975
INSERT INTO t1 SELECT * FROM t1;
3976
INSERT INTO t1 SELECT * FROM t1;
3977
INSERT INTO t1 SELECT * FROM t1;
3978
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
3979
INSERT INTO `t2` VALUES ('abcdefghijk');
3980
INSERT INTO `t2` VALUES ('asdf');
3981
SET session sort_buffer_size=8192;
3983
Error 1292 Truncated incorrect sort_buffer_size value: '8192'
3984
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
3990
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
3993
CREATE TEMPORARY TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
3994
CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;
3995
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3996
INSERT INTO t2 VALUES (1), (2);
3997
# wasn't failing, but should
3998
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
3999
ERROR 21000: Subquery returns more than 1 row
4000
# fails as it should
4001
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
4002
ERROR 21000: Subquery returns more than 1 row
4003
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
4004
(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
4007
# second test case from 27333
4008
CREATE TABLE t1 (a INT, b INT);
4009
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
4010
# returns no rows, when it should
4011
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
4012
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
4017
#test cases from 29297
4018
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
4019
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;
4020
INSERT INTO t1 VALUES (1),(2);
4021
INSERT INTO t2 VALUES (1),(2);
4022
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
4023
(SELECT SUM(t1.a) FROM t2 WHERE a=0)
4025
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
4026
ERROR 21000: Subquery returns more than 1 row
4027
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
4028
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
4032
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
4034
CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
4035
CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
4036
INSERT INTO t1 VALUES (100, 200);
4037
INSERT INTO t1 VALUES (101, 201);
4038
INSERT INTO t2 VALUES (101, 201);
4039
INSERT INTO t2 VALUES (103, 203);
4040
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
4041
((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL
4046
# Bug #30788: Inconsistent retrieval of char/varchar
4048
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
4049
INSERT INTO t1 VALUES ('a', 'aa');
4050
INSERT INTO t1 VALUES ('a', 'aaa');
4051
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4055
CREATE INDEX I1 ON t1 (a);
4056
CREATE INDEX I2 ON t1 (b);
4057
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4058
id select_type table type possible_keys key key_len ref rows Extra
4059
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
4060
2 SUBQUERY t1 ALL NULL NULL NULL NULL #
4061
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4065
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
4066
INSERT INTO t2 SELECT * FROM t1;
4067
CREATE INDEX I1 ON t2 (a);
4068
CREATE INDEX I2 ON t2 (b);
4069
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4070
id select_type table type possible_keys key key_len ref rows Extra
4071
1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where
4072
2 SUBQUERY t2 ALL NULL NULL NULL NULL #
4073
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4078
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4079
id select_type table type possible_keys key key_len ref rows Extra
4080
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
4081
2 SUBQUERY t1 ALL NULL NULL NULL NULL # Using where
4082
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4088
# Bug #32400: Complex SELECT query returns correct result only on some
4091
CREATE TABLE t1(a INT, b INT);
4092
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
4094
SELECT a AS out_a, MIN(b) FROM t1
4095
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
4097
ERROR 42S22: Unknown column 'out_a' in 'where clause'
4098
SELECT a AS out_a, MIN(b) FROM t1
4099
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
4101
ERROR 42S22: Unknown column 'out_a' in 'where clause'
4103
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
4104
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4106
id select_type table type possible_keys key key_len ref rows Extra
4107
1 PRIMARY t1_outer ALL NULL NULL NULL NULL # Using where; Using temporary; Using filesort
4108
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where
4109
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
4110
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4117
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
4119
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
4120
CREATE TABLE t2 (a INT);
4121
INSERT INTO t1 VALUES (1),(2);
4122
INSERT INTO t2 VALUES (1),(2);
4123
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
4128
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
4129
id select_type table type possible_keys key key_len ref rows filtered Extra
4130
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
4131
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
4133
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
4134
Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))
4136
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
4137
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
4138
id select_type table type possible_keys key key_len ref rows filtered Extra
4139
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00 Using where
4140
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00 Using where
4141
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL # 100.00 Using where
4142
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL
4144
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
4145
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
4146
Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
4149
# BUG#33794 "MySQL crashes executing specific query on specific dump"
4151
CREATE TEMPORARY TABLE t4 (
4152
f7 varchar(32) collate utf8_bin NOT NULL default '',
4153
f10 varchar(32) collate utf8_bin default NULL,
4156
INSERT INTO t4 VALUES(1,1), (2,null);
4158
f4 varchar(32) collate utf8_bin NOT NULL default '',
4159
f2 varchar(50) collate utf8_bin default NULL,
4160
f3 varchar(10) collate utf8_bin default NULL,
4162
UNIQUE KEY uk1 (f2));
4163
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
4164
CREATE TEMPORARY TABLE t1 (
4165
f8 varchar(32) collate utf8_bin NOT NULL default '',
4166
f1 varchar(10) collate utf8_bin default NULL,
4167
f9 varchar(32) collate utf8_bin default NULL,
4170
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
4171
CREATE TEMPORARY TABLE t3 (
4172
f6 varchar(32) collate utf8_bin NOT NULL default '',
4173
f5 varchar(50) collate utf8_bin default NULL,
4176
INSERT INTO t3 VALUES (1,null), (2,null);
4178
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
4179
IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
4183
FROM t2 VPC, t4 a2, t2 a3
4185
VPC.f4 = a2.f10 AND a3.f2 = a4
4192
t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
4197
DROP TABLE t1, t2, t3, t4;
4200
# Test [NOT] IN truth table (both as top-level and general predicate).
4202
create temporary table t_out (subcase char(3),
4203
a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;
4204
create table t_in (a2 char(2), b2 char(2), c2 char(2));
4205
insert into t_out values ('A.1','2a', NULL, '2a');
4206
#------------------------- A.2 - impossible
4207
insert into t_out values ('A.3', '2a', NULL, '2a');
4208
insert into t_out values ('A.4', '2a', NULL, 'xx');
4209
insert into t_out values ('B.1', '2a', '2a', '2a');
4210
insert into t_out values ('B.2', '2a', '2a', '2a');
4211
insert into t_out values ('B.3', '3a', 'xx', '3a');
4212
insert into t_out values ('B.4', 'xx', '3a', '3a');
4213
insert into t_in values ('1a', '1a', '1a');
4214
insert into t_in values ('2a', '2a', '2a');
4215
insert into t_in values (NULL, '2a', '2a');
4216
insert into t_in values ('3a', NULL, '3a');
4218
Test general IN semantics (not top-level)
4222
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
4223
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
4224
from t_out where subcase = 'A.1';
4225
subcase pred_in pred_not_in
4227
case A.2 - impossible
4230
(a1, b1, c1) IN (select * from t_in) pred_in,
4231
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4232
from t_out where subcase = 'A.3';
4233
subcase pred_in pred_not_in
4237
(a1, b1, c1) IN (select * from t_in) pred_in,
4238
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4239
from t_out where subcase = 'A.4';
4240
subcase pred_in pred_not_in
4244
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
4245
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
4246
from t_out where subcase = 'B.1';
4247
subcase pred_in pred_not_in
4251
(a1, b1, c1) IN (select * from t_in) pred_in,
4252
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4253
from t_out where subcase = 'B.2';
4254
subcase pred_in pred_not_in
4258
(a1, b1, c1) IN (select * from t_in) pred_in,
4259
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4260
from t_out where subcase = 'B.3';
4261
subcase pred_in pred_not_in
4265
(a1, b1, c1) IN (select * from t_in) pred_in,
4266
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4267
from t_out where subcase = 'B.4';
4268
subcase pred_in pred_not_in
4271
Test IN as top-level predicate, and
4272
as non-top level for cases A.3, B.3 (the only cases with NULL result).
4275
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4276
where subcase = 'A.1' and
4277
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
4280
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4281
where subcase = 'A.1' and
4282
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
4285
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4286
where subcase = 'A.1' and
4287
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
4291
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4292
where subcase = 'A.3' and
4293
(a1, b1, c1) IN (select * from t_in);
4296
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4297
where subcase = 'A.3' and
4298
(a1, b1, c1) NOT IN (select * from t_in);
4301
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4302
where subcase = 'A.3' and
4303
NOT((a1, b1, c1) IN (select * from t_in));
4306
# test non-top level result indirectly
4307
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4308
where subcase = 'A.3' and
4309
((a1, b1, c1) IN (select * from t_in)) is NULL and
4310
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
4314
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4315
where subcase = 'A.4' and
4316
(a1, b1, c1) IN (select * from t_in);
4319
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4320
where subcase = 'A.4' and
4321
(a1, b1, c1) NOT IN (select * from t_in);
4324
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4325
where subcase = 'A.4' and
4326
NOT((a1, b1, c1) IN (select * from t_in));
4330
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4331
where subcase = 'B.1' and
4332
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
4335
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4336
where subcase = 'B.1' and
4337
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
4340
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4341
where subcase = 'B.1' and
4342
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
4346
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4347
where subcase = 'B.2' and
4348
(a1, b1, c1) IN (select * from t_in);
4351
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4352
where subcase = 'B.2' and
4353
(a1, b1, c1) NOT IN (select * from t_in);
4356
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4357
where subcase = 'B.2' and
4358
NOT((a1, b1, c1) IN (select * from t_in));
4362
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4363
where subcase = 'B.3' and
4364
(a1, b1, c1) IN (select * from t_in);
4367
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4368
where subcase = 'B.3' and
4369
(a1, b1, c1) NOT IN (select * from t_in);
4372
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4373
where subcase = 'B.3' and
4374
NOT((a1, b1, c1) IN (select * from t_in));
4377
# test non-top level result indirectly
4378
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4379
where subcase = 'B.3' and
4380
((a1, b1, c1) IN (select * from t_in)) is NULL and
4381
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
4385
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4386
where subcase = 'B.4' and
4387
(a1, b1, c1) IN (select * from t_in);
4390
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4391
where subcase = 'B.4' and
4392
(a1, b1, c1) NOT IN (select * from t_in);
4395
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4396
where subcase = 'B.4' and
4397
NOT((a1, b1, c1) IN (select * from t_in));
4403
# Bug#20835 (literal string with =any values)
4405
CREATE TABLE t1 (s1 char(1));
4406
INSERT INTO t1 VALUES ('a');
4407
SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
4412
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
4414
CREATE TABLE t1( a INT );
4415
INSERT INTO t1 VALUES (1),(2);
4416
CREATE TABLE t2( a INT, b INT );
4418
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
4419
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2
4421
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
4422
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
4424
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
4425
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
4429
SELECT a INTO @var FROM t1 WHERE a = 2
4431
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2
4436
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
4438
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
4443
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
4445
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
4447
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
4451
SELECT a FROM t1 WHERE a = 2
4453
SELECT a FROM t1 WHERE a = 2
4460
SELECT a FROM t1 WHERE a = 2
4462
SELECT a FROM t1 WHERE a = 2
4467
# This was not allowed previously. Possibly, it should be allowed on the future.
4468
# For now, the intent is to keep the fix as non-intrusive as possible.
4469
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
4470
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
4471
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
4474
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
4477
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
4478
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4479
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
4480
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4481
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4482
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4483
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
4484
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4485
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4486
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4487
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
4488
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4489
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
4490
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a))) t1a' at line 1
4491
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
4492
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1
4493
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
4494
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1
4495
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
4498
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;
4501
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
4504
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
4508
# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias
4509
# UNION should not be allowed inside the parentheses, nor should
4512
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
4516
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
4517
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')) ON 1' at line 1
4518
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
4519
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'ON 1' at line 1
4520
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
4521
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ') ON 1' at line 1
4522
SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;
4523
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a ON 1' at line 1
4524
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
4525
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a ON 1' at line 1
4526
SELECT * FROM t1 JOIN (t1 t1a) ON 1;
4532
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
4538
SELECT * FROM (t1 t1a);
4542
SELECT * FROM ((t1 t1a));
4546
SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;
4550
SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;
4554
SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;
4558
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
4562
# For the join, TABLE_LIST::select_lex == NULL
4563
# Check that we handle this.
4564
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
4565
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a2' at line 1
4566
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
4569
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );
4572
SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );
4575
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
4576
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1
4577
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4578
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4579
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4580
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4581
SELECT * FROM t1 WHERE a = ( SELECT 1 );
4584
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );
4587
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
4588
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1
4589
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
4590
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4591
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
4592
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4593
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
4594
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1
4595
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4596
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4597
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4598
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4599
SELECT ( SELECT 1 INTO @v );
4600
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4601
SELECT ( SELECT 1 INTO OUTFILE 'file' );
4602
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4603
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
4604
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4605
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
4606
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4607
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4608
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4609
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4610
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4611
# Make sure context is popped when we leave the nested select
4612
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4613
( SELECT a FROM t1 WHERE a = 1 ) a
4616
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
4617
( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a
4620
# Make sure we have feature F561 (see .yy file)
4621
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
4623
# Make sure the parser does not allow nested UNIONs anywhere
4624
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
4625
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4626
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4627
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1' at line 1
4628
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4629
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4630
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4631
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1' at line 1
4632
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4633
( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
4635
SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
4636
((SELECT 1 UNION SELECT 1 UNION SELECT 1))
4638
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4639
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4640
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4641
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
4642
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
4645
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4646
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4647
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4648
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4649
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4650
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4651
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4652
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4653
SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4654
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4655
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4656
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4657
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4658
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4659
SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4660
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4661
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4664
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4667
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4670
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4673
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
4674
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4675
SELECT EXISTS(SELECT 1+1);
4678
SELECT EXISTS(SELECT 1+1 INTO @test);
4679
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @test)' at line 1
4680
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
4681
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4682
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
4683
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4684
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
4685
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
added
removed
tests/r/pbxt/subselect.result
