1
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
5
explain extended select (select 2);
6
id select_type table type possible_keys key key_len ref rows filtered Extra
7
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
9
Note 1249 Select 2 was reduced during optimization
10
Note 1003 select 2 AS "(select 2)"
11
SELECT (SELECT 1) UNION SELECT (SELECT 2);
15
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
16
id select_type table type possible_keys key key_len ref rows filtered Extra
17
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
18
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
19
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
21
Note 1249 Select 2 was reduced during optimization
22
Note 1249 Select 4 was reduced during optimization
23
Note 1003 select 1 AS "(SELECT 1)" union select 2 AS "(SELECT 2)"
24
SELECT (SELECT (SELECT 0 UNION SELECT 0));
25
(SELECT (SELECT 0 UNION SELECT 0))
27
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
28
id select_type table type possible_keys key key_len ref rows filtered Extra
29
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
30
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
31
4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
32
NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL
34
Note 1249 Select 2 was reduced during optimization
35
Note 1003 select (select 0 AS "0" union select 0 AS "0") AS "(SELECT (SELECT 0 UNION SELECT 0))"
36
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
37
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
38
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;
39
ERROR 42S22: Reference 'b' not supported (forward reference in item list)
40
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
43
SELECT (SELECT a) as a;
44
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
45
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
46
id select_type table type possible_keys key key_len ref rows filtered Extra
47
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
48
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
49
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
51
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
52
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
53
Note 1003 select 1 AS "1" from (select 1 AS "a") "b" having ((select '1' AS "a") = 1)
54
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
58
ERROR 42S22: Unknown column 'a' in 'field list'
59
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
62
SELECT 1 FROM (SELECT (SELECT a) b) c;
63
ERROR 42S22: Unknown column 'a' in 'field list'
64
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
67
SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1);
68
ERROR 21000: Operand should contain 1 column(s)
69
SELECT 1 IN (SELECT 1);
72
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
75
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
76
ERROR 42S22: Unknown column 'a' in 'field list'
77
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
78
ERROR 42S22: Unknown column 'a' in 'field list'
79
SELECT (SELECT 1,2,3) = ROW(1,2,3);
80
(SELECT 1,2,3) = ROW(1,2,3)
82
SELECT (SELECT 1,2,3) = ROW(1,2,1);
83
(SELECT 1,2,3) = ROW(1,2,1)
85
SELECT (SELECT 1,2,3) < ROW(1,2,1);
86
(SELECT 1,2,3) < ROW(1,2,1)
88
SELECT (SELECT 1,2,3) > ROW(1,2,1);
89
(SELECT 1,2,3) > ROW(1,2,1)
91
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
92
(SELECT 1,2,3) = ROW(1,2,NULL)
94
SELECT ROW(1,2,3) = (SELECT 1,2,3);
95
ROW(1,2,3) = (SELECT 1,2,3)
97
SELECT ROW(1,2,3) = (SELECT 1,2,1);
98
ROW(1,2,3) = (SELECT 1,2,1)
100
SELECT ROW(1,2,3) < (SELECT 1,2,1);
101
ROW(1,2,3) < (SELECT 1,2,1)
103
SELECT ROW(1,2,3) > (SELECT 1,2,1);
104
ROW(1,2,3) > (SELECT 1,2,1)
106
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
107
ROW(1,2,3) = (SELECT 1,2,NULL)
109
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
110
(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
112
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
113
(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
115
SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
116
(SELECT 1.5,2,'a') = ROW('1.5b',2,'b')
119
Warning 1292 Truncated incorrect DOUBLE value: '1.5b'
120
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
121
(SELECT 'b',2,'a') = ROW(1.5,2,'a')
123
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
124
(SELECT 1.5,2,'a') = ROW(1.5,'2','a')
126
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
127
(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
129
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
130
ERROR 21000: Operand should contain 1 column(s)
131
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
134
create table t1 (a int);
135
create table t2 (a int, b int);
136
create table t3 (a int);
137
create table t4 (a int not null, b int not null);
138
insert into t1 values (2);
139
insert into t2 values (1,7),(2,7);
140
insert into t4 values (4,8),(3,8),(5,9);
141
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
142
ERROR 42S22: Reference 'a1' not supported (forward reference in item list)
143
select (select a from t1 where t1.a=t2.a), a from t2;
144
(select a from t1 where t1.a=t2.a) a
147
select (select a from t1 where t1.a=t2.b), a from t2;
148
(select a from t1 where t1.a=t2.b) a
151
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
152
(select a from t1) a (select 1 union select 2 limit 1)
155
select (select a from t3), a from t2;
159
select * from t2 where t2.a=(select a from t1);
162
insert into t3 values (6),(7),(3);
163
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
167
(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;
172
(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);
178
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);
179
id select_type table type possible_keys key key_len ref rows filtered Extra
180
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
181
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using filesort
182
3 UNION t4 ALL NULL NULL NULL NULL 3 100.00 Using where
183
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
184
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
186
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")
187
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
188
(select a from t3 where a<t2.a*4 order by 1 desc limit 1) a
191
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
192
(select * from t2 where a>1) as tt;
193
(select t3.a from t3 where a<8 order by 1 desc limit 1) a
195
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
196
(select * from t2 where a>1) as tt;
197
id select_type table type possible_keys key key_len ref rows filtered Extra
198
1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00
199
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
200
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
202
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"
203
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);
206
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);
209
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);
211
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
212
b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
216
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
217
id select_type table type possible_keys key key_len ref rows filtered Extra
218
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00
219
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
220
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
222
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
223
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"
224
select * from t3 where exists (select * from t2 where t2.b=t3.a);
227
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
231
select * from t3 where a in (select b from t2);
234
select * from t3 where a not in (select b from t2);
238
select * from t3 where a = some (select b from t2);
241
select * from t3 where a <> any (select b from t2);
245
select * from t3 where a = all (select b from t2);
248
select * from t3 where a <> all (select b from t2);
252
insert into t2 values (100, 5);
253
select * from t3 where a < any (select b from t2);
257
select * from t3 where a < all (select b from t2);
260
select * from t3 where a >= any (select b from t2);
264
explain extended select * from t3 where a >= any (select b from t2);
265
id select_type table type possible_keys key key_len ref rows filtered Extra
266
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
267
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
269
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= (select min("test"."t2"."b") from "test"."t2")))
270
select * from t3 where a >= all (select b from t2);
273
delete from t2 where a=100;
274
select * from t3 where a in (select a,b from t2);
275
ERROR 21000: Operand should contain 1 column(s)
276
select * from t3 where a in (select * from t2);
277
ERROR 21000: Operand should contain 1 column(s)
278
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
279
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
281
insert into t2 values (2,10);
282
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
285
delete from t2 where a=2 and b=10;
286
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
289
create table t5 (a int);
290
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
291
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
294
insert into t5 values (5);
295
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
296
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
299
insert into t5 values (2);
300
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
301
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
304
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;
305
id select_type table type possible_keys key key_len ref rows filtered Extra
306
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
307
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
308
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where
309
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
311
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
312
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
313
Note 1003 select (select '2' AS "a" from "test"."t1" where ('2' = "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"
314
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
315
ERROR 21000: Subquery returns more than 1 row
316
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
317
create table t7( uq int primary key, name char(25));
318
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
319
insert into t6 values (1,1),(1,2),(2,2),(1,3);
320
select * from t6 where exists (select * from t7 where uq = clinic_uq);
325
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
326
id select_type table type possible_keys key key_len ref rows filtered Extra
327
1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
328
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
330
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
331
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"))
332
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
333
ERROR 23000: Column 'a' in field list is ambiguous
335
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
336
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
337
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
338
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
339
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
340
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
341
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
344
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
347
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
351
`pseudo` varchar(35) character set latin1 NOT NULL default '',
352
`email` varchar(60) character set latin1 NOT NULL default '',
353
PRIMARY KEY (`pseudo`),
354
UNIQUE KEY `email` (`email`)
355
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
356
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
357
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
358
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
359
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');
360
id select_type table type possible_keys key key_len ref rows filtered Extra
361
1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index
362
4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
363
2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00
364
3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
366
Note 1003 select 'joce' AS "pseudo",(select 'test' AS "email" from "test"."t8" where 1) AS "(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))" from "test"."t8" where 1
367
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
368
t8 WHERE pseudo='joce');
369
ERROR 21000: Operand should contain 1 column(s)
370
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
372
ERROR 21000: Operand should contain 1 column(s)
373
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
376
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
377
ERROR 21000: Subquery returns more than 1 row
378
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
380
`topic` mediumint(8) unsigned NOT NULL default '0',
381
`date` date NOT NULL default '0000-00-00',
382
`pseudo` varchar(35) character set latin1 NOT NULL default '',
383
PRIMARY KEY (`pseudo`,`date`,`topic`),
384
KEY `topic` (`topic`)
385
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
386
INSERT INTO t1 (topic,date,pseudo) VALUES
387
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
388
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
389
id select_type table type possible_keys key key_len ref rows filtered Extra
390
1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
392
Note 1003 select distinct "test"."t1"."date" AS "date" from "test"."t1" where ("test"."t1"."date" = '2002-08-03')
393
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
394
id select_type table type possible_keys key key_len ref rows filtered Extra
395
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
396
2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
398
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')"
399
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
402
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
403
(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
405
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
410
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
411
ERROR 21000: Subquery returns more than 1 row
412
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
413
id select_type table type possible_keys key key_len ref rows filtered Extra
414
1 PRIMARY t1 index NULL topic 3 NULL 2 100.00 Using index
415
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
416
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
417
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
419
Note 1003 select 1 AS "1" from "test"."t1" where 1
422
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
423
`maxnumrep` int(10) unsigned NOT NULL default '0',
424
PRIMARY KEY (`numeropost`),
425
UNIQUE KEY `maxnumrep` (`maxnumrep`)
426
) ENGINE=MyISAM ROW_FORMAT=FIXED;
427
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
429
`mot` varchar(30) NOT NULL default '',
430
`topic` mediumint(8) unsigned NOT NULL default '0',
431
`date` date NOT NULL default '0000-00-00',
432
`pseudo` varchar(35) NOT NULL default '',
433
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
434
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
435
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
436
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
439
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;
443
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
444
ERROR 42S22: Unknown column 'a' in 'having clause'
445
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
446
ERROR 42S22: Unknown column 'a' in 'having clause'
447
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
448
mot topic date pseudo
449
joce 40143 2002-10-22 joce
450
joce 43506 2002-10-22 joce
451
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
452
mot topic date pseudo
453
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
454
mot topic date pseudo
455
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
456
mot topic date pseudo
457
joce 40143 2002-10-22 joce
458
joce 43506 2002-10-22 joce
459
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
460
mot topic date pseudo
461
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
462
mot topic date pseudo
463
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
464
mot topic date pseudo
465
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
466
mot topic date pseudo
467
joce 40143 2002-10-22 joce
468
joce 43506 2002-10-22 joce
469
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
470
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100)
471
joce 40143 2002-10-22 joce 1
472
joce 43506 2002-10-22 joce 1
473
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
474
mot topic date pseudo
475
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
476
mot topic date pseudo
477
joce 40143 2002-10-22 joce
478
joce 43506 2002-10-22 joce
479
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
480
mot topic date pseudo
481
joce 40143 2002-10-22 joce
482
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
483
mot topic date pseudo
484
joce 40143 2002-10-22 joce
485
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
486
mot topic date pseudo
487
joce 40143 2002-10-22 joce
488
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
489
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000)
490
joce 40143 2002-10-22 joce 1
491
joce 43506 2002-10-22 joce 0
494
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
495
`maxnumrep` int(10) unsigned NOT NULL default '0',
496
PRIMARY KEY (`numeropost`),
497
UNIQUE KEY `maxnumrep` (`maxnumrep`)
498
) ENGINE=MyISAM ROW_FORMAT=FIXED;
499
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
500
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
501
ERROR 21000: Subquery returns more than 1 row
502
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
503
ERROR 21000: Subquery returns more than 1 row
505
create table t1 (a int);
506
insert into t1 values (1),(2),(3);
507
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
513
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
514
INSERT INTO t1 VALUES ();
515
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
516
ERROR 21000: Subquery returns more than 1 row
519
`numeropost` mediumint(8) unsigned NOT NULL default '0',
520
`numreponse` int(10) unsigned NOT NULL auto_increment,
521
`pseudo` varchar(35) NOT NULL default '',
522
PRIMARY KEY (`numeropost`,`numreponse`),
523
UNIQUE KEY `numreponse` (`numreponse`),
524
KEY `pseudo` (`pseudo`,`numeropost`)
526
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
527
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
528
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
529
ERROR 42S22: Unknown column 'a' in 'having clause'
530
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
531
numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)
532
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
533
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
534
ERROR 21000: Subquery returns more than 1 row
535
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
536
id select_type table type possible_keys key key_len ref rows filtered Extra
537
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
539
Note 1003 select max("test"."t1"."numreponse") AS "MAX(numreponse)" from "test"."t1" where ("test"."t1"."numeropost" = '1')
540
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
541
id select_type table type possible_keys key key_len ref rows filtered Extra
542
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
543
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
545
Note 1003 select '3' AS "numreponse" from "test"."t1" where (('1' = '1'))
547
CREATE TABLE t1 (a int(1));
548
INSERT INTO t1 VALUES (1);
549
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
553
create table t1 (a int NOT NULL, b int, primary key (a));
554
create table t2 (a int NOT NULL, b int, primary key (a));
555
insert into t1 values (0, 10),(1, 11),(2, 12);
556
insert into t2 values (1, 21),(2, 22),(3, 23);
562
update t1 set b= (select b from t1);
563
ERROR HY000: You can't specify target table 't1' for update in FROM clause
564
update t1 set b= (select b from t2);
565
ERROR 21000: Subquery returns more than 1 row
566
update t1 set b= (select b from t2 where t1.a = t2.a);
573
create table t1 (a int NOT NULL, b int, primary key (a));
574
create table t2 (a int NOT NULL, b int, primary key (a));
575
insert into t1 values (0, 10),(1, 11),(2, 12);
576
insert into t2 values (1, 21),(2, 12),(3, 23);
582
select * from t1 where b = (select b from t2 where t1.a = t2.a);
585
delete from t1 where b = (select b from t1);
586
ERROR HY000: You can't specify target table 't1' for update in FROM clause
587
delete from t1 where b = (select b from t2);
588
ERROR 21000: Subquery returns more than 1 row
589
delete from t1 where b = (select b from t2 where t1.a = t2.a);
595
create table t11 (a int NOT NULL, b int, primary key (a));
596
create table t12 (a int NOT NULL, b int, primary key (a));
597
create table t2 (a int NOT NULL, b int, primary key (a));
598
insert into t11 values (0, 10),(1, 11),(2, 12);
599
insert into t12 values (33, 10),(22, 11),(2, 12);
600
insert into t2 values (1, 21),(2, 12),(3, 23);
611
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
612
ERROR HY000: You can't specify target table 't12' for update in FROM clause
613
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
614
ERROR 21000: Subquery returns more than 1 row
615
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
624
drop table t11, t12, t2;
625
CREATE TABLE t1 (x int);
626
create table t2 (a int);
627
create table t3 (b int);
628
insert into t2 values (1);
629
insert into t3 values (1),(2);
630
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
631
ERROR HY000: You can't specify target table 't1' for update in FROM clause
632
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
633
ERROR 21000: Subquery returns more than 1 row
634
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
638
insert into t2 values (1);
639
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
644
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
651
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
660
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
661
ERROR 42S22: Unknown column 'x' in 'field list'
662
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
672
drop table t1, t2, t3;
673
CREATE TABLE t1 (x int not null, y int, primary key (x));
674
create table t2 (a int);
675
create table t3 (a int);
676
insert into t2 values (1);
677
insert into t3 values (1),(2);
680
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
681
ERROR HY000: You can't specify target table 't1' for update in FROM clause
682
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
683
ERROR 21000: Subquery returns more than 1 row
684
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
688
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
692
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
697
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
702
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
708
drop table t1, t2, t3;
709
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
710
ERROR HY000: No tables used
711
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
712
INSERT INTO t2 VALUES (1),(2);
713
SELECT * FROM t2 WHERE id IN (SELECT 1);
716
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
717
id select_type table type possible_keys key key_len ref rows filtered Extra
718
1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
720
Note 1249 Select 2 was reduced during optimization
721
Note 1003 select "test"."t2"."id" AS "id" from "test"."t2" where ("test"."t2"."id" = 1)
722
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
725
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
728
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
729
id select_type table type possible_keys key key_len ref rows filtered Extra
730
1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
732
Note 1249 Select 3 was reduced during optimization
733
Note 1249 Select 2 was reduced during optimization
734
Note 1003 select "test"."t2"."id" AS "id" from "test"."t2" where ("test"."t2"."id" = (1 + 1))
735
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
736
id select_type table type possible_keys key key_len ref rows filtered Extra
737
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
738
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
739
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
740
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
742
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))))
743
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
745
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
748
INSERT INTO t2 VALUES ((SELECT * FROM t2));
749
ERROR HY000: You can't specify target table 't2' for update in FROM clause
750
INSERT INTO t2 VALUES ((SELECT id FROM t2));
751
ERROR HY000: You can't specify target table 't2' for update in FROM clause
756
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
757
INSERT INTO t1 values (1),(1);
758
UPDATE t2 SET id=(SELECT * FROM t1);
759
ERROR 21000: Subquery returns more than 1 row
761
create table t1 (a int);
762
insert into t1 values (1),(2),(3);
763
select 1 IN (SELECT * from t1);
764
1 IN (SELECT * from t1)
766
select 10 IN (SELECT * from t1);
767
10 IN (SELECT * from t1)
769
select NULL IN (SELECT * from t1);
770
NULL IN (SELECT * from t1)
772
update t1 set a=NULL where a=2;
773
select 1 IN (SELECT * from t1);
774
1 IN (SELECT * from t1)
776
select 3 IN (SELECT * from t1);
777
3 IN (SELECT * from t1)
779
select 10 IN (SELECT * from t1);
780
10 IN (SELECT * from t1)
782
select 1 > ALL (SELECT * from t1);
783
1 > ALL (SELECT * from t1)
785
select 10 > ALL (SELECT * from t1);
786
10 > ALL (SELECT * from t1)
788
select 1 > ANY (SELECT * from t1);
789
1 > ANY (SELECT * from t1)
791
select 10 > ANY (SELECT * from t1);
792
10 > ANY (SELECT * from t1)
795
create table t1 (a varchar(20));
796
insert into t1 values ('A'),('BC'),('DEF');
797
select 'A' IN (SELECT * from t1);
798
'A' IN (SELECT * from t1)
800
select 'XYZS' IN (SELECT * from t1);
801
'XYZS' IN (SELECT * from t1)
803
select NULL IN (SELECT * from t1);
804
NULL IN (SELECT * from t1)
806
update t1 set a=NULL where a='BC';
807
select 'A' IN (SELECT * from t1);
808
'A' IN (SELECT * from t1)
810
select 'DEF' IN (SELECT * from t1);
811
'DEF' IN (SELECT * from t1)
813
select 'XYZS' IN (SELECT * from t1);
814
'XYZS' IN (SELECT * from t1)
816
select 'A' > ALL (SELECT * from t1);
817
'A' > ALL (SELECT * from t1)
819
select 'XYZS' > ALL (SELECT * from t1);
820
'XYZS' > ALL (SELECT * from t1)
822
select 'A' > ANY (SELECT * from t1);
823
'A' > ANY (SELECT * from t1)
825
select 'XYZS' > ANY (SELECT * from t1);
826
'XYZS' > ANY (SELECT * from t1)
829
create table t1 (a float);
830
insert into t1 values (1.5),(2.5),(3.5);
831
select 1.5 IN (SELECT * from t1);
832
1.5 IN (SELECT * from t1)
834
select 10.5 IN (SELECT * from t1);
835
10.5 IN (SELECT * from t1)
837
select NULL IN (SELECT * from t1);
838
NULL IN (SELECT * from t1)
840
update t1 set a=NULL where a=2.5;
841
select 1.5 IN (SELECT * from t1);
842
1.5 IN (SELECT * from t1)
844
select 3.5 IN (SELECT * from t1);
845
3.5 IN (SELECT * from t1)
847
select 10.5 IN (SELECT * from t1);
848
10.5 IN (SELECT * from t1)
850
select 1.5 > ALL (SELECT * from t1);
851
1.5 > ALL (SELECT * from t1)
853
select 10.5 > ALL (SELECT * from t1);
854
10.5 > ALL (SELECT * from t1)
856
select 1.5 > ANY (SELECT * from t1);
857
1.5 > ANY (SELECT * from t1)
859
select 10.5 > ANY (SELECT * from t1);
860
10.5 > ANY (SELECT * from t1)
862
explain extended select (select a+1) from t1;
863
id select_type table type possible_keys key key_len ref rows filtered Extra
864
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
866
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
867
Note 1249 Select 2 was reduced during optimization
868
Note 1003 select ("test"."t1"."a" + 1) AS "(select a+1)" from "test"."t1"
869
select (select a+1) from t1;
875
CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a));
876
CREATE TABLE t2 (a int(11) default '0', INDEX (a));
877
INSERT INTO t1 VALUES (1),(2),(3),(4);
878
INSERT INTO t2 VALUES (1),(2),(3);
879
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
880
a t1.a in (select t2.a from t2)
885
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
886
id select_type table type possible_keys key key_len ref rows filtered Extra
887
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
888
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
890
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"
891
CREATE TABLE t3 (a int(11) default '0');
892
INSERT INTO t3 VALUES (1),(2),(3);
893
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
894
a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
899
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
900
id select_type table type possible_keys key key_len ref rows filtered Extra
901
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
902
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
903
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
905
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"
907
create table t1 (a float);
908
select 10.5 IN (SELECT * from t1 LIMIT 1);
909
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
910
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
911
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
913
create table t1 (a int, b int, c varchar(10));
914
create table t2 (a int);
915
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
916
insert into t2 values (1),(2),(NULL);
917
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;
918
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a)
922
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;
923
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a)
927
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;
928
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a)
933
create table t1 (a int, b real, c varchar(10));
934
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
935
select ROW(1, 1, 'a') IN (select a,b,c from t1);
936
ROW(1, 1, 'a') IN (select a,b,c from t1)
938
select ROW(1, 2, 'a') IN (select a,b,c from t1);
939
ROW(1, 2, 'a') IN (select a,b,c from t1)
941
select ROW(1, 1, 'a') IN (select b,a,c from t1);
942
ROW(1, 1, 'a') IN (select b,a,c from t1)
944
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
945
ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null)
947
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
948
ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null)
950
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
951
ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null)
953
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
954
ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
956
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
957
ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
959
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
960
ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
962
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
963
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
965
CREATE TABLE t1 (a int(1));
966
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
967
id select_type table type possible_keys key key_len ref rows filtered Extra
968
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
969
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
971
Note 1003 select (select rand() AS "RAND()" from "test"."t1") AS "(SELECT RAND() FROM t1)" from "test"."t1"
972
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
973
id select_type table type possible_keys key key_len ref rows filtered Extra
974
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
975
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
977
Note 1003 select (select encrypt('test') AS "ENCRYPT('test')" from "test"."t1") AS "(SELECT ENCRYPT('test') FROM t1)" from "test"."t1"
978
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
979
id select_type table type possible_keys key key_len ref rows filtered Extra
980
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
981
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
983
Note 1003 select (select benchmark(1,1) AS "BENCHMARK(1,1)" from "test"."t1") AS "(SELECT BENCHMARK(1,1) FROM t1)" from "test"."t1"
986
`mot` varchar(30) character set latin1 NOT NULL default '',
987
`topic` mediumint(8) unsigned NOT NULL default '0',
988
`date` date NOT NULL default '0000-00-00',
989
`pseudo` varchar(35) character set latin1 NOT NULL default '',
990
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
991
KEY `pseudo` (`pseudo`,`date`,`topic`),
992
KEY `topic` (`topic`)
993
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
995
`mot` varchar(30) character set latin1 NOT NULL default '',
996
`topic` mediumint(8) unsigned NOT NULL default '0',
997
`date` date NOT NULL default '0000-00-00',
998
`pseudo` varchar(35) character set latin1 NOT NULL default '',
999
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
1000
KEY `pseudo` (`pseudo`,`date`,`topic`),
1001
KEY `topic` (`topic`)
1002
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
1004
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
1005
`maxnumrep` int(10) unsigned NOT NULL default '0',
1006
PRIMARY KEY (`numeropost`),
1007
UNIQUE KEY `maxnumrep` (`maxnumrep`)
1008
) ENGINE=MyISAM CHARSET=latin1;
1009
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1010
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1011
INSERT INTO t3 VALUES (1,1);
1012
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
1017
mot topic date pseudo
1018
joce 1 0000-00-00 joce
1019
test 2 0000-00-00 test
1020
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
1021
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
1023
mot topic date pseudo
1024
joce 1 0000-00-00 joce
1025
drop table t1, t2, t3;
1026
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1029
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
1030
SHOW CREATE TABLE t1;
1032
t1 CREATE TABLE "t1" (
1033
"a" int(1) NOT NULL,
1034
"(SELECT 1)" int(1) NOT NULL
1035
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1037
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1038
SHOW CREATE TABLE t1;
1040
t1 CREATE TABLE "t1" (
1041
"a" int(1) NOT NULL,
1042
"(SELECT a)" int(1) NOT NULL
1043
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1045
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
1046
SHOW CREATE TABLE t1;
1048
t1 CREATE TABLE "t1" (
1049
"a" int(1) NOT NULL,
1050
"(SELECT a+0)" int(3) NOT NULL
1051
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1053
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1057
SHOW CREATE TABLE t1;
1059
t1 CREATE TABLE "t1" (
1060
"a" bigint(20) NOT NULL
1061
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1063
create table t1 (a int);
1064
insert into t1 values (1), (2), (3);
1065
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
1067
id select_type table type possible_keys key key_len ref rows filtered Extra
1068
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1069
2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1070
3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1072
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"
1074
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);
1075
ERROR 42S02: Table 'test.t1' doesn't exist
1077
ID int(11) NOT NULL auto_increment,
1078
name char(35) NOT NULL default '',
1079
t2 char(3) NOT NULL default '',
1080
District char(20) NOT NULL default '',
1081
Population int(11) NOT NULL default '0',
1084
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
1085
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
1086
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
1088
Code char(3) NOT NULL default '',
1089
Name char(52) NOT NULL default '',
1090
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1091
Region char(26) NOT NULL default '',
1092
SurfaceArea float(10,2) NOT NULL default '0.00',
1093
IndepYear smallint(6) default NULL,
1094
Population int(11) NOT NULL default '0',
1095
LifeExpectancy float(3,1) default NULL,
1096
GNP float(10,2) default NULL,
1097
GNPOld float(10,2) default NULL,
1098
LocalName char(45) NOT NULL default '',
1099
GovernmentForm char(45) NOT NULL default '',
1100
HeadOfState char(60) default NULL,
1101
Capital int(11) default NULL,
1102
Code2 char(2) NOT NULL default '',
1105
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');
1106
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');
1107
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);
1108
Continent Name Population
1109
Oceania Sydney 3276207
1112
`id` mediumint(8) unsigned NOT NULL auto_increment,
1113
`pseudo` varchar(35) character set latin1 NOT NULL default '',
1115
UNIQUE KEY `pseudo` (`pseudo`)
1116
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1117
INSERT INTO t1 (pseudo) VALUES ('test');
1118
SELECT 0 IN (SELECT 1 FROM t1 a);
1119
0 IN (SELECT 1 FROM t1 a)
1121
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1122
id select_type table type possible_keys key key_len ref rows filtered Extra
1123
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1124
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1126
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)"
1127
INSERT INTO t1 (pseudo) VALUES ('test1');
1128
SELECT 0 IN (SELECT 1 FROM t1 a);
1129
0 IN (SELECT 1 FROM t1 a)
1131
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1132
id select_type table type possible_keys key key_len ref rows filtered Extra
1133
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1134
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1136
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)"
1139
`i` int(11) NOT NULL default '0',
1141
) ENGINE=MyISAM CHARSET=latin1;
1142
INSERT INTO t1 VALUES (1);
1143
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
1144
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1145
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1146
ERROR 42S22: Unknown column 't.i' in 'field list'
1152
id int(11) default NULL
1153
) ENGINE=MyISAM CHARSET=latin1;
1154
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
1156
id int(11) default NULL,
1157
name varchar(15) default NULL
1158
) ENGINE=MyISAM CHARSET=latin1;
1159
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
1160
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
1168
create table t1 (a int, unique index indexa (a));
1169
insert into t1 values (-1), (-4), (-2), (NULL);
1170
select -10 IN (select a from t1 FORCE INDEX (indexa));
1171
-10 IN (select a from t1 FORCE INDEX (indexa))
1174
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
1175
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
1176
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
1177
id select_type table type possible_keys key key_len ref rows filtered Extra
1178
1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition
1179
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1181
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"))
1184
ID int(10) unsigned NOT NULL auto_increment,
1185
SUB_ID int(3) unsigned NOT NULL default '0',
1186
REF_ID int(10) unsigned default NULL,
1187
REF_SUB int(3) unsigned default '0',
1188
PRIMARY KEY (ID,SUB_ID),
1189
UNIQUE KEY t1_PK (ID,SUB_ID),
1190
KEY t1_FK (REF_ID,REF_SUB),
1191
KEY t1_REFID (REF_ID)
1192
) ENGINE=MyISAM CHARSET=cp1251;
1193
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
1194
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
1197
create table t1 (a int, b int);
1198
create table t2 (a int, b int);
1199
insert into t1 values (1,0), (2,0), (3,0);
1200
insert into t2 values (1,1), (2,1), (3,1), (2,2);
1201
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
1203
Error 1242 Subquery returns more than 1 row
1211
`id` mediumint(8) unsigned NOT NULL auto_increment,
1212
`pseudo` varchar(35) NOT NULL default '',
1213
`email` varchar(60) NOT NULL default '',
1215
UNIQUE KEY `email` (`email`),
1216
UNIQUE KEY `pseudo` (`pseudo`)
1217
) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1218
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
1219
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
1223
drop table if exists t1;
1224
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
1227
create table t1 (a int not null, b int, primary key (a));
1228
create table t2 (a int not null, primary key (a));
1229
create table t3 (a int not null, b int, primary key (a));
1230
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1231
insert into t2 values (2), (3), (4), (5);
1232
insert into t3 values (10,3), (20,4), (30,5);
1233
select * from t2 where t2.a in (select a from t1);
1238
explain extended select * from t2 where t2.a in (select a from t1);
1239
id select_type table type possible_keys key key_len ref rows filtered Extra
1240
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1241
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer
1243
Note 1003 select "test"."t2"."a" AS "a" from ("test"."t1") join "test"."t2" where ("test"."t1"."a" = "test"."t2"."a")
1244
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1248
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1249
id select_type table type possible_keys key key_len ref rows filtered Extra
1250
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1251
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
1253
Note 1003 select "test"."t2"."a" AS "a" from ("test"."t1") join "test"."t2" where (("test"."t1"."a" = "test"."t2"."a") and ("test"."t1"."b" <> 30))
1254
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1258
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1259
id select_type table type possible_keys key key_len ref rows filtered Extra
1260
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1261
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
1262
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
1264
Note 1003 select "test"."t2"."a" AS "a" from ("test"."t1" join "test"."t3") join "test"."t2" where (("test"."t1"."a" = "test"."t2"."a") and ("test"."t3"."a" = "test"."t1"."b"))
1265
drop table t1, t2, t3;
1266
create table t1 (a int, b int, index a (a,b));
1267
create table t2 (a int, index a (a));
1268
create table t3 (a int, b int, index a (a));
1269
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1270
insert into t2 values (2), (3), (4), (5);
1271
insert into t3 values (10,3), (20,4), (30,5);
1272
select * from t2 where t2.a in (select a from t1);
1277
explain extended select * from t2 where t2.a in (select a from t1);
1278
id select_type table type possible_keys key key_len ref rows filtered Extra
1279
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
1280
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)
1282
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" semi join ("test"."t1") where ("test"."t1"."a" = "test"."t2"."a")
1283
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1287
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1288
id select_type table type possible_keys key key_len ref rows filtered Extra
1289
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
1290
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
1292
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" semi join ("test"."t1") where (("test"."t1"."a" = "test"."t2"."a") and ("test"."t1"."b" <> 30))
1293
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1297
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1298
id select_type table type possible_keys key key_len ref rows filtered Extra
1299
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index; Start temporary
1300
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)
1301
1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary; Using join buffer
1303
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" semi join ("test"."t1" join "test"."t3") where (("test"."t1"."a" = "test"."t2"."a") and ("test"."t3"."a" = "test"."t1"."b"))
1304
insert into t1 values (3,31);
1305
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1310
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
1314
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1315
id select_type table type possible_keys key key_len ref rows filtered Extra
1316
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
1317
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
1319
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" semi join ("test"."t1") where (("test"."t1"."a" = "test"."t2"."a") and ("test"."t1"."b" <> 30))
1320
drop table t1, t2, t3;
1321
create table t1 (a int, b int);
1322
create table t2 (a int, b int);
1323
create table t3 (a int, b int);
1324
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
1325
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
1326
insert into t3 values (3,3), (2,2), (1,1);
1327
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;
1328
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)
1332
drop table t1,t2,t3;
1333
create table t1 (s1 int);
1334
create table t2 (s1 int);
1335
insert into t1 values (1);
1336
insert into t2 values (1);
1337
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
1341
create table t1 (s1 int);
1342
create table t2 (s1 int);
1343
insert into t1 values (1);
1344
insert into t2 values (1);
1345
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1346
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1348
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
1349
s2 CHAR(5) COLLATE latin1_swedish_ci);
1350
INSERT INTO t1 VALUES ('z','?');
1351
select * from t1 where s1 > (select max(s2) from t1);
1352
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1353
select * from t1 where s1 > any (select max(s2) from t1);
1354
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1356
create table t1(toid int,rd int);
1357
create table t2(userid int,pmnew int,pmtotal int);
1358
insert into t2 values(1,0,0),(2,0,0);
1359
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);
1360
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);
1361
userid pmtotal pmnew calc_total calc_new
1365
create table t1 (s1 char(5));
1366
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
1367
ERROR 21000: Operand should contain 1 column(s)
1368
insert into t1 values ('tttt');
1369
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
1372
explain extended (select * from t1);
1373
id select_type table type possible_keys key key_len ref rows filtered Extra
1374
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
1376
Note 1003 (select 'tttt' AS "s1" from "test"."t1")
1381
create table t1 (s1 char(5), index s1(s1));
1382
create table t2 (s1 char(5), index s1(s1));
1383
insert into t1 values ('a1'),('a2'),('a3');
1384
insert into t2 values ('a1'),('a2');
1385
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1386
s1 s1 NOT IN (SELECT s1 FROM t2)
1390
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1391
s1 s1 = ANY (SELECT s1 FROM t2)
1395
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1396
s1 s1 <> ALL (SELECT s1 FROM t2)
1400
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1401
s1 s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')
1405
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1406
id select_type table type possible_keys key key_len ref rows filtered Extra
1407
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1408
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1410
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"
1411
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1412
id select_type table type possible_keys key key_len ref rows filtered Extra
1413
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1414
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1416
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"
1417
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1418
id select_type table type possible_keys key key_len ref rows filtered Extra
1419
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1420
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1422
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"
1423
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1424
id select_type table type possible_keys key key_len ref rows filtered Extra
1425
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1426
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
1428
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"
1430
create table t2 (a int, b int);
1431
create table t3 (a int);
1432
insert into t3 values (6),(7),(3);
1433
select * from t3 where a >= all (select b from t2);
1438
explain extended select * from t3 where a >= all (select b from t2);
1439
id select_type table type possible_keys key key_len ref rows filtered Extra
1440
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1441
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1443
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <not>(("test"."t3"."a" < (select max('0') from "test"."t2")))
1444
select * from t3 where a >= some (select b from t2);
1446
explain extended select * from t3 where a >= some (select b from t2);
1447
id select_type table type possible_keys key key_len ref rows filtered Extra
1448
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1449
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1451
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= (select min('0') from "test"."t2")))
1452
select * from t3 where a >= all (select b from t2 group by 1);
1457
explain extended select * from t3 where a >= all (select b from t2 group by 1);
1458
id select_type table type possible_keys key key_len ref rows filtered Extra
1459
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1460
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1462
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <not>(("test"."t3"."a" < <max>(select '0' AS "b" from "test"."t2" group by 1)))
1463
select * from t3 where a >= some (select b from t2 group by 1);
1465
explain extended select * from t3 where a >= some (select b from t2 group by 1);
1466
id select_type table type possible_keys key key_len ref rows filtered Extra
1467
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1468
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1470
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= <min>(select '0' AS "b" from "test"."t2" group by 1)))
1471
select * from t3 where NULL >= any (select b from t2);
1473
explain extended select * from t3 where NULL >= any (select b from t2);
1474
id select_type table type possible_keys key key_len ref rows filtered Extra
1475
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1476
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1478
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1479
select * from t3 where NULL >= any (select b from t2 group by 1);
1481
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
1482
id select_type table type possible_keys key key_len ref rows filtered Extra
1483
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1484
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1486
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1487
select * from t3 where NULL >= some (select b from t2);
1489
explain extended select * from t3 where NULL >= some (select b from t2);
1490
id select_type table type possible_keys key key_len ref rows filtered Extra
1491
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1492
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1494
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1495
select * from t3 where NULL >= some (select b from t2 group by 1);
1497
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
1498
id select_type table type possible_keys key key_len ref rows filtered Extra
1499
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1500
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1502
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1503
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1504
select * from t3 where a > all (select max(b) from t2 group by a);
1508
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
1509
id select_type table type possible_keys key key_len ref rows filtered Extra
1510
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1511
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
1513
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")))
1515
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
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 TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
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 TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
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 TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
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;
1533
CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
1534
INSERT INTO t1 VALUES (1),(5);
1535
CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
1536
INSERT INTO t2 VALUES (2),(6);
1537
select * from t1 where (1,2,6) in (select * from t2);
1538
ERROR 21000: Operand should contain 3 column(s)
1540
create table t1 (s1 char);
1541
insert into t1 values ('e');
1542
select * from t1 where 'f' > any (select s1 from t1);
1545
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1548
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1549
id select_type table type possible_keys key key_len ref rows filtered Extra
1550
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1551
2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
1552
3 UNION t1 system NULL NULL NULL NULL 1 100.00
1553
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1555
Note 1003 select 'e' AS "s1" from "test"."t1" where 1
1557
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1558
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
1559
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
1560
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
1561
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;
1568
create table t1 (s1 int);
1569
create table t2 (s1 int);
1570
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
1571
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
1572
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
1573
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1574
select count(*) from t2 group by t1.s2;
1575
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1577
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
1578
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
1579
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
1580
INSERT INTO t2 VALUES (100, 200, 'C');
1581
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
1584
CREATE TABLE t1 (a int(1));
1585
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
1586
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
1595
`id` int(11) NOT NULL auto_increment,
1596
`id_cns` tinyint(3) unsigned NOT NULL default '0',
1597
`tipo` enum('','UNO','DUE') NOT NULL default '',
1598
`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
1599
`particolare` mediumint(8) unsigned NOT NULL default '0',
1600
`generale` mediumint(8) unsigned NOT NULL default '0',
1601
`bis` tinyint(3) unsigned NOT NULL default '0',
1603
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
1604
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
1606
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);
1608
`id` tinyint(3) unsigned NOT NULL auto_increment,
1609
`max_anno_dep` smallint(6) unsigned NOT NULL default '0',
1612
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
1613
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;
1614
id max_anno_dep PIPPO
1619
create table t1 (a int);
1620
insert into t1 values (1), (2), (3);
1621
SET SQL_SELECT_LIMIT=1;
1622
select sum(a) from (select * from t1) as a;
1625
select 2 in (select * from t1);
1626
2 in (select * from t1)
1628
SET SQL_SELECT_LIMIT=default;
1630
CREATE TABLE t1 (a int, b int, INDEX (a));
1631
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1632
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1638
create table t1(val varchar(10));
1639
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1640
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%');
1644
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1645
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');
1646
select * from t1 where id not in (select id from t1 where id < 8);
1653
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);
1660
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1661
id select_type table type possible_keys key key_len ref rows filtered Extra
1662
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
1663
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
1665
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")))))))
1666
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);
1667
id select_type table type possible_keys key key_len ref rows filtered Extra
1668
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
1669
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
1671
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1672
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))))
1673
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1674
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
1675
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');
1676
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);
1677
id text id text id text
1678
1 text1 1 text1 1 text1
1679
2 text2 2 text2 2 text2
1680
3 text3 3 text3 3 text3
1681
4 text4 4 text4 4 text4
1682
5 text5 5 text5 5 text5
1683
6 text6 6 text6 6 text6
1684
7 text7 7 text7 7 text7
1685
8 text8 8 text8 8 text8
1686
9 text9 9 text9 9 text9
1687
10 text10 10 text10 10 text10
1688
11 text11 11 text1 11 text11
1689
12 text12 12 text2 12 text12
1690
1000 text1000 NULL NULL 1000 text1000
1691
1001 text1001 NULL NULL 1000 text1000
1692
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);
1693
id select_type table type possible_keys key key_len ref rows filtered Extra
1694
1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00
1695
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00
1696
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using index condition
1698
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")
1700
create table t1 (a int);
1701
insert into t1 values (1);
1702
explain select benchmark(1000, (select a from t1 where a=rand()));
1703
id select_type table type possible_keys key key_len ref rows Extra
1704
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
1705
2 SUBQUERY t1 system NULL NULL NULL NULL 1
1707
create table t1(id int);
1708
create table t2(id int);
1709
create table t3(flag int);
1710
select (select * from t3 where id not null) from t1, t2;
1711
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null) from t1, t2' at line 1
1712
drop table t1,t2,t3;
1713
CREATE TABLE t1 (id INT);
1714
CREATE TABLE t2 (id INT);
1715
INSERT INTO t1 VALUES (1), (2);
1716
INSERT INTO t2 VALUES (1);
1717
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);
1721
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1725
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;
1729
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;
1734
CREATE TABLE t1 ( a int, b int );
1735
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1736
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1739
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1742
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1745
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1749
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1753
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1757
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1760
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1763
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1766
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1770
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1774
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1778
ALTER TABLE t1 ADD INDEX (a);
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 );
1788
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1792
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1796
SELECT a FROM t1 WHERE a <> ANY ( 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 );
1809
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1813
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1817
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1821
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1824
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
1827
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
1830
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
1834
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
1838
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
1842
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
1845
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
1848
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
1851
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
1855
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
1859
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
1863
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1866
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1869
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1872
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1876
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1880
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1884
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1887
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1890
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1893
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1897
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1901
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1905
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1908
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1911
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1914
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1918
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1922
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1926
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1929
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1932
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1935
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1939
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1943
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1947
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1948
ERROR 21000: Operand should contain 1 column(s)
1949
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1950
ERROR 21000: Operand should contain 1 column(s)
1951
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1952
ERROR 21000: Operand should contain 1 column(s)
1953
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
1954
ERROR 21000: Operand should contain 1 column(s)
1955
SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1956
ERROR 21000: Operand should contain 1 column(s)
1957
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1958
ERROR 21000: Operand should contain 1 column(s)
1959
SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
1960
ERROR 21000: Operand should contain 1 column(s)
1961
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
1962
ERROR 21000: Operand should contain 1 column(s)
1963
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
1964
ERROR 21000: Operand should contain 2 column(s)
1965
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1966
ERROR 21000: Operand should contain 1 column(s)
1967
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1969
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
1970
ERROR 21000: Operand should contain 2 column(s)
1971
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1972
ERROR 21000: Operand should contain 1 column(s)
1973
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1978
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
1981
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
1985
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
1988
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
1992
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);
1995
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);
1999
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);
2002
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);
2006
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2009
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2012
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2015
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2019
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2023
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2027
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2030
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2033
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2036
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2040
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2044
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2048
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
2051
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
2054
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
2057
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
2061
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
2065
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
2069
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
2072
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
2075
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
2078
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
2082
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
2086
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
2090
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
2091
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
2095
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
2096
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-')
2100
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
2101
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-')
2106
CREATE TABLE t1 ( a double, b double );
2107
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2108
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
2111
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
2114
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
2117
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
2121
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
2125
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
2129
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
2132
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
2135
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
2138
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
2142
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
2146
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
2151
CREATE TABLE t1 ( a char(1), b char(1));
2152
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
2153
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
2156
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
2159
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
2162
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
2166
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
2170
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
2174
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
2177
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
2180
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
2183
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
2187
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
2191
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
2196
create table t1 (a int, b int);
2197
insert into t1 values (1,2),(3,4);
2198
select * from t1 up where exists (select * from t1 where t1.a=up.a);
2202
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
2203
id select_type table type possible_keys key key_len ref rows filtered Extra
2204
1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
2205
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2207
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
2208
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"))
2210
CREATE TABLE t1 (t1_a int);
2211
INSERT INTO t1 VALUES (1);
2212
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
2213
INSERT INTO t2 VALUES (1, 1), (1, 2);
2214
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
2215
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
2219
CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL);
2220
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
2221
CREATE TABLE t2 (id int(11) default NULL, pet varchar(10) default NULL);
2222
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
2223
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
2229
CREATE TABLE `t1` ( `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2230
insert into t1 values (1);
2231
CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2232
insert into t2 values (1,2);
2233
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2237
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin);
2238
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2239
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2240
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
2241
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
2242
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
2243
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
2247
CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
2248
CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
2249
insert into t1 values (1,1),(1,2),(2,1),(2,2);
2250
insert into t2 values (1,2),(2,2);
2251
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2255
alter table t2 drop primary key;
2256
alter table t2 add key KEY1 (aid, bid);
2257
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2261
alter table t2 drop key KEY1;
2262
alter table t2 add primary key (bid, aid);
2263
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2268
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
2269
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
2270
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
2271
howmanyvalues count(*)
2276
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2277
howmanyvalues mycount
2282
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
2283
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
2284
howmanyvalues mycount
2289
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2290
howmanyvalues mycount
2295
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
2296
howmanyvalues mycount
2302
create table t1 (x int);
2303
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;
2304
(select b.x from t1 as b where b.x=a.x)
2306
CREATE TABLE `t1` ( `master` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `slave` int(10) unsigned NOT NULL default '0', `access` int(10) unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
2307
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);
2308
CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL default '0', `pid` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `level` tinyint(4) unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
2309
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');
2310
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;
2311
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2312
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;
2317
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
2318
create table t1 (a int, b int);
2319
create table t2 (a int, b int);
2320
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
2321
insert into t2 values (1,3),(2,1);
2322
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
2323
a b (select max(b) from t2 where t1.b=t2.a)
2330
create table t1 (s1 int,s2 int);
2331
insert into t1 values (20,15);
2332
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
2335
create table t1 (s1 int);
2336
insert into t1 values (1),(null);
2337
select * from t1 where s1 < all (select s1 from t1);
2339
select s1, s1 < all (select s1 from t1) from t1;
2340
s1 s1 < all (select s1 from t1)
2345
Code char(3) NOT NULL default '',
2346
Name char(52) NOT NULL default '',
2347
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
2348
Region char(26) NOT NULL default '',
2349
SurfaceArea float(10,2) NOT NULL default '0.00',
2350
IndepYear smallint(6) default NULL,
2351
Population int(11) NOT NULL default '0',
2352
LifeExpectancy float(3,1) default NULL,
2353
GNP float(10,2) default NULL,
2354
GNPOld float(10,2) default NULL,
2355
LocalName char(45) NOT NULL default '',
2356
GovernmentForm char(45) NOT NULL default '',
2357
HeadOfState char(60) default NULL,
2358
Capital int(11) default NULL,
2359
Code2 char(2) NOT NULL default ''
2361
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2362
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');
2363
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');
2364
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');
2365
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
2366
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
2367
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
2372
create table t1 (a1 int);
2373
create table t2 (b1 int);
2374
select * from t1 where a2 > any(select b1 from t2);
2375
ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
2376
select * from t1 where a1 > any(select b1 from t2);
2379
create table t1 (a integer, b integer);
2380
select (select * from t1) = (select 1,2);
2381
(select * from t1) = (select 1,2)
2383
select (select 1,2) = (select * from t1);
2384
(select 1,2) = (select * from t1)
2386
select row(1,2) = ANY (select * from t1);
2387
row(1,2) = ANY (select * from t1)
2389
select row(1,2) != ALL (select * from t1);
2390
row(1,2) != ALL (select * from t1)
2393
create table t1 (a integer, b integer);
2394
select row(1,(2,2)) in (select * from t1 );
2395
ERROR 21000: Operand should contain 2 column(s)
2396
select row(1,(2,2)) = (select * from t1 );
2397
ERROR 21000: Operand should contain 2 column(s)
2398
select (select * from t1) = row(1,(2,2));
2399
ERROR 21000: Operand should contain 1 column(s)
2401
create table t1 (a integer);
2402
insert into t1 values (1);
2403
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
2404
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2405
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2406
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2407
select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );
2408
xx 1 = ALL ( select 1 from t1 where 1 = xx )
2410
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2411
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2414
categoryId int(11) NOT NULL,
2415
courseId int(11) NOT NULL,
2416
startDate datetime NOT NULL,
2417
endDate datetime NOT NULL,
2418
createDate datetime NOT NULL,
2419
modifyDate timestamp NOT NULL,
2420
attributes text NOT NULL
2422
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2423
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2424
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2425
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
2426
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
2427
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2428
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2429
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2430
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
2432
userId int(11) NOT NULL,
2433
courseId int(11) NOT NULL,
2434
date datetime NOT NULL
2436
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
2437
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
2438
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
2439
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
2440
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
2441
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
2443
groupId int(11) NOT NULL,
2444
parentId int(11) NOT NULL,
2445
startDate datetime NOT NULL,
2446
endDate datetime NOT NULL,
2447
createDate datetime NOT NULL,
2448
modifyDate timestamp NOT NULL,
2451
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
2453
id int(11) NOT NULL,
2454
groupTypeId int(11) NOT NULL,
2455
groupKey varchar(50) NOT NULL,
2459
createDate datetime NOT NULL,
2460
modifyDate timestamp NOT NULL
2462
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
2463
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
2465
userId int(11) NOT NULL,
2466
groupId int(11) NOT NULL,
2467
createDate datetime NOT NULL,
2468
modifyDate timestamp NOT NULL
2470
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
2472
count(distinct t2.userid) pass,
2474
count(t2.courseid) crse,
2477
date_format(date, '%b%y') as colhead
2479
join t1 on t2.courseid=t1.courseid
2492
select t4.id as parentid,
2493
t4.name as parentgroup,
2495
t4.name as groupname,
2498
) as gin on t5.groupid=gin.childid
2499
) as groupstuff on t2.userid = groupstuff.userid
2501
groupstuff.groupname, colhead , t2.courseid;
2502
pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead
2503
1 5141 12 group2 12 group2 5 1 5 12 Aug04
2504
1 5141 12 group2 12 group2 5 1 1 41 Aug04
2505
1 5141 12 group2 12 group2 5 1 2 52 Aug04
2506
1 5141 12 group2 12 group2 5 1 2 53 Aug04
2507
1 5141 12 group2 12 group2 5 1 3 51 Oct04
2508
1 5141 12 group2 12 group2 5 1 1 86 Oct04
2509
1 5141 12 group2 12 group2 5 1 1 87 Oct04
2510
1 5141 12 group2 12 group2 5 1 2 88 Oct04
2511
1 5141 12 group2 12 group2 5 1 2 89 Oct04
2512
drop table t1, t2, t3, t4, t5;
2513
create table t1 (a int);
2514
insert into t1 values (1), (2), (3);
2515
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
2521
create table t1 (a int, b int);
2522
insert into t1 values (1,2);
2523
select 1 = (select * from t1);
2524
ERROR 21000: Operand should contain 1 column(s)
2525
select (select * from t1) = 1;
2526
ERROR 21000: Operand should contain 2 column(s)
2527
select (1,2) = (select a from t1);
2528
ERROR 21000: Operand should contain 2 column(s)
2529
select (select a from t1) = (1,2);
2530
ERROR 21000: Operand should contain 1 column(s)
2531
select (1,2,3) = (select * from t1);
2532
ERROR 21000: Operand should contain 3 column(s)
2533
select (select * from t1) = (1,2,3);
2534
ERROR 21000: Operand should contain 2 column(s)
2537
`itemid` bigint(20) unsigned NOT NULL auto_increment,
2538
`sessionid` bigint(20) unsigned default NULL,
2539
`time` int(10) unsigned NOT NULL default '0',
2540
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
2542
`data` text collate latin1_general_ci NOT NULL,
2543
PRIMARY KEY (`itemid`)
2544
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2545
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
2547
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
2548
`pid` int(10) unsigned NOT NULL default '0',
2549
`date` int(10) unsigned NOT NULL default '0',
2550
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
2551
PRIMARY KEY (`sessionid`)
2552
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2553
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
2554
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;
2555
ip count( e.itemid )
2558
create table t1 (fld enum('0','1'));
2559
insert into t1 values ('1');
2560
select * from (select max(fld) from t1) as foo;
2564
CREATE TABLE t1 (one int, two int, flag char(1));
2565
CREATE TABLE t2 (one int, two int, flag char(1));
2566
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2567
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2569
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
2574
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
2578
insert into t2 values (null,null,'N');
2579
insert into t2 values (null,3,'0');
2580
insert into t2 values (null,5,'0');
2581
insert into t2 values (10,null,'0');
2582
insert into t1 values (10,3,'0');
2583
insert into t1 values (10,5,'0');
2584
insert into t1 values (10,10,'0');
2585
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
2595
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2599
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
2609
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2619
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
2629
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2630
id select_type table type possible_keys key key_len ref rows filtered Extra
2631
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2632
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
2634
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"
2635
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2636
id select_type table type possible_keys key key_len ref rows filtered Extra
2637
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
2638
1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
2640
Note 1003 select "test"."t1"."one" AS "one","test"."t1"."two" AS "two" from "test"."t1" semi join ("test"."t2") where (("test"."t2"."two" = "test"."t1"."two") and ("test"."t2"."one" = "test"."t1"."one") and ("test"."t2"."flag" = 'N'))
2641
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;
2642
id select_type table type possible_keys key key_len ref rows filtered Extra
2643
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2644
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
2646
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"
2648
CREATE TABLE t1 (a char(5), b char(5));
2649
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2650
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
2654
CREATE TABLE t1 (a int);
2655
CREATE TABLE t2 (a int, b int);
2656
CREATE TABLE t3 (b int NOT NULL);
2657
INSERT INTO t1 VALUES (1), (2), (3), (4);
2658
INSERT INTO t2 VALUES (1,10), (3,30);
2659
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2660
WHERE t3.b IS NOT NULL OR t2.a > 10;
2663
WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2664
WHERE t3.b IS NOT NULL OR t2.a > 10);
2670
DROP TABLE t1,t2,t3;
2671
CREATE TABLE t1 (f1 INT);
2672
CREATE TABLE t2 (f2 INT);
2673
INSERT INTO t1 VALUES (1);
2674
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
2677
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
2680
INSERT INTO t2 VALUES (1);
2681
INSERT INTO t2 VALUES (2);
2682
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
2686
create table t1 (s1 char);
2687
insert into t1 values (1),(2);
2688
select * from t1 where (s1 < any (select s1 from t1));
2691
select * from t1 where not (s1 < any (select s1 from t1));
2694
select * from t1 where (s1 < ALL (select s1+1 from t1));
2697
select * from t1 where not(s1 < ALL (select s1+1 from t1));
2700
select * from t1 where (s1+1 = ANY (select s1 from t1));
2703
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
2706
select * from t1 where (s1 = ALL (select s1/s1 from t1));
2709
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
2714
retailerID varchar(8) NOT NULL,
2715
statusID int(10) unsigned NOT NULL,
2716
changed datetime NOT NULL,
2717
UNIQUE KEY retailerID (retailerID, statusID, changed)
2719
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
2720
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
2721
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
2722
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
2723
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
2724
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
2726
where (r1.retailerID,(r1.changed)) in
2727
(SELECT r2.retailerId,(max(changed)) from t1 r2
2728
group by r2.retailerId);
2729
retailerID statusID changed
2730
0026 2 2006-01-06 12:25:53
2731
0037 2 2006-01-06 12:25:53
2732
0048 1 2006-01-06 12:37:50
2733
0059 1 2006-01-06 12:37:50
2735
create table t1(a int, primary key (a));
2736
insert into t1 values (10);
2737
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
2738
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
2739
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2740
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2741
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2742
id select_type table type possible_keys key key_len ref rows Extra
2743
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2744
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2745
2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
2746
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2747
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2748
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2751
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2752
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2753
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2754
id select_type table type possible_keys key key_len ref rows Extra
2755
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2756
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2757
2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition; Using MRR
2758
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2759
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2760
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2765
field1 int NOT NULL,
2766
field2 int NOT NULL,
2767
field3 int NOT NULL,
2768
PRIMARY KEY (field1,field2,field3)
2771
fieldA int NOT NULL,
2772
fieldB int NOT NULL,
2773
PRIMARY KEY (fieldA,fieldB)
2775
INSERT INTO t1 VALUES
2776
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
2777
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
2778
SELECT field1, field2, COUNT(*)
2779
FROM t1 GROUP BY field1, field2;
2780
field1 field2 COUNT(*)
2784
SELECT field1, field2
2786
GROUP BY field1, field2
2787
HAVING COUNT(*) >= ALL (SELECT fieldB
2788
FROM t2 WHERE fieldA = field1);
2791
SELECT field1, field2
2793
GROUP BY field1, field2
2794
HAVING COUNT(*) < ANY (SELECT fieldB
2795
FROM t2 WHERE fieldA = field1);
2800
CREATE TABLE t1(a int, INDEX (a));
2801
INSERT INTO t1 VALUES (1), (3), (5), (7);
2802
INSERT INTO t1 VALUES (NULL);
2803
CREATE TABLE t2(a int);
2804
INSERT INTO t2 VALUES (1),(2),(3);
2805
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
2806
id select_type table type possible_keys key key_len ref rows Extra
2807
1 PRIMARY t2 ALL NULL NULL NULL NULL 3
2808
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
2809
SELECT a, a IN (SELECT a FROM t1) FROM t2;
2810
a a IN (SELECT a FROM t1)
2815
CREATE TABLE t1 (a DATETIME);
2816
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
2817
CREATE TABLE t2 AS SELECT
2818
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
2819
FROM t1 WHERE a > '2000-01-01';
2820
SHOW CREATE TABLE t2;
2822
t2 CREATE TABLE "t2" (
2824
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2825
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
2826
SHOW CREATE TABLE t3;
2828
t3 CREATE TABLE "t3" (
2830
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2831
DROP TABLE t1,t2,t3;
2832
CREATE TABLE t1 (a int);
2833
INSERT INTO t1 VALUES (2), (4), (1), (3);
2834
CREATE TABLE t2 (b int, c int);
2835
INSERT INTO t2 VALUES
2836
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
2837
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
2843
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
2844
ERROR 21000: Subquery returns more than 1 row
2845
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
2851
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
2852
ERROR 21000: Subquery returns more than 1 row
2853
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
2858
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
2859
ERROR 21000: Subquery returns more than 1 row
2860
SELECT a FROM t1 GROUP BY a
2861
HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
2862
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2868
SELECT a FROM t1 GROUP BY a
2869
HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
2870
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2871
ERROR 21000: Subquery returns more than 1 row
2872
SELECT a FROM t1 GROUP BY a
2873
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
2874
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2877
SELECT a FROM t1 GROUP BY a
2878
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
2879
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
2880
ERROR 21000: Subquery returns more than 1 row
2882
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
2883
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
2890
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
2891
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2892
ERROR 21000: Subquery returns more than 1 row
2894
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
2895
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
2902
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
2903
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2904
ERROR 21000: Subquery returns more than 1 row
2906
create table t1 (df decimal(5,1));
2907
insert into t1 values(1.1);
2908
insert into t1 values(2.2);
2909
select * from t1 where df <= all (select avg(df) from t1 group by df);
2912
select * from t1 where df >= all (select avg(df) from t1 group by df);
2916
create table t1 (df decimal(5,1));
2917
insert into t1 values(1.1);
2918
select 1.1 * exists(select * from t1);
2919
1.1 * exists(select * from t1)
2923
grp int(11) default NULL,
2924
a decimal(10,2) default NULL);
2925
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
2935
select min(a) from t1 group by grp;
2942
CREATE table t1 ( c1 integer );
2943
INSERT INTO t1 VALUES ( 1 );
2944
INSERT INTO t1 VALUES ( 2 );
2945
INSERT INTO t1 VALUES ( 3 );
2946
CREATE TABLE t2 ( c2 integer );
2947
INSERT INTO t2 VALUES ( 1 );
2948
INSERT INTO t2 VALUES ( 4 );
2949
INSERT INTO t2 VALUES ( 5 );
2950
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
2953
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
2954
WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
2958
CREATE TABLE t1 ( c1 integer );
2959
INSERT INTO t1 VALUES ( 1 );
2960
INSERT INTO t1 VALUES ( 2 );
2961
INSERT INTO t1 VALUES ( 3 );
2962
INSERT INTO t1 VALUES ( 6 );
2963
CREATE TABLE t2 ( c2 integer );
2964
INSERT INTO t2 VALUES ( 1 );
2965
INSERT INTO t2 VALUES ( 4 );
2966
INSERT INTO t2 VALUES ( 5 );
2967
INSERT INTO t2 VALUES ( 6 );
2968
CREATE TABLE t3 ( c3 integer );
2969
INSERT INTO t3 VALUES ( 7 );
2970
INSERT INTO t3 VALUES ( 8 );
2971
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
2972
WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
2976
DROP TABLE t1,t2,t3;
2978
`itemid` bigint(20) unsigned NOT NULL auto_increment,
2979
`sessionid` bigint(20) unsigned default NULL,
2980
`time` int(10) unsigned NOT NULL default '0',
2981
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
2983
`data` text collate latin1_general_ci NOT NULL,
2984
PRIMARY KEY (`itemid`)
2985
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2986
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
2988
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
2989
`pid` int(10) unsigned NOT NULL default '0',
2990
`date` int(10) unsigned NOT NULL default '0',
2991
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
2992
PRIMARY KEY (`sessionid`)
2993
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2994
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
2995
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;
2996
ip count( e.itemid )
2999
CREATE TABLE t1 (EMPNUM CHAR(3));
3000
CREATE TABLE t2 (EMPNUM CHAR(3) );
3001
INSERT INTO t1 VALUES ('E1'),('E2');
3002
INSERT INTO t2 VALUES ('E1');
3004
WHERE t1.EMPNUM NOT IN
3007
WHERE t1.EMPNUM = t2.EMPNUM);
3012
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
3013
INSERT INTO t1 VALUES (1, 1);
3014
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
3015
PRIMARY KEY(select_id,values_id));
3016
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
3017
SELECT values_id FROM t1
3018
WHERE values_id IN (SELECT values_id FROM t2
3019
WHERE select_id IN (1, 0));
3022
SELECT values_id FROM t1
3023
WHERE values_id IN (SELECT values_id FROM t2
3024
WHERE select_id BETWEEN 0 AND 1);
3027
SELECT values_id FROM t1
3028
WHERE values_id IN (SELECT values_id FROM t2
3029
WHERE select_id = 0 OR select_id = 1);
3033
create table t1 (fld enum('0','1'));
3034
insert into t1 values ('1');
3035
select * from (select max(fld) from t1) as foo;
3039
CREATE TABLE t1 (a int, b int);
3040
CREATE TABLE t2 (c int, d int);
3041
CREATE TABLE t3 (e int);
3042
INSERT INTO t1 VALUES
3043
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
3044
INSERT INTO t2 VALUES
3045
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
3046
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
3047
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
3067
SELECT a FROM t1 GROUP BY a
3068
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
3072
SELECT a FROM t1 GROUP BY a
3073
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
3076
SELECT a FROM t1 GROUP BY a
3077
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
3081
SELECT a FROM t1 GROUP BY a
3082
HAVING a IN (SELECT c FROM t2
3083
WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
3087
SELECT a FROM t1 GROUP BY a
3088
HAVING a IN (SELECT c FROM t2
3089
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
3093
SELECT a FROM t1 GROUP BY a
3094
HAVING a IN (SELECT c FROM t2
3095
WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
3098
SELECT a FROM t1 GROUP BY a
3099
HAVING a IN (SELECT c FROM t2
3100
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
3103
SELECT a FROM t1 GROUP BY a
3104
HAVING a IN (SELECT c FROM t2
3105
WHERE MIN(b) < d AND
3106
EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
3109
SELECT a, SUM(a) FROM t1 GROUP BY a;
3116
WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
3120
SELECT a FROM t1 GROUP BY a
3121
HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
3128
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
3134
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
3141
SELECT t1.a FROM t1 GROUP BY t1.a
3142
HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
3143
HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3144
HAVING SUM(t1.a+t2.c) < t3.e/4));
3148
SELECT t1.a FROM t1 GROUP BY t1.a
3149
HAVING t1.a > ALL(SELECT t2.c FROM t2
3150
WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3151
HAVING SUM(t1.a+t2.c) < t3.e/4));
3154
SELECT t1.a FROM t1 GROUP BY t1.a
3155
HAVING t1.a > ALL(SELECT t2.c FROM t2
3156
WHERE EXISTS(SELECT t3.e FROM t3
3157
WHERE SUM(t1.a+t2.c) < t3.e/4));
3158
ERROR HY000: Invalid use of group function
3159
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
3160
ERROR HY000: Invalid use of group function
3161
SELECT t1.a FROM t1 GROUP BY t1.a
3162
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3163
HAVING AVG(t2.c+SUM(t1.b)) > 20);
3168
SELECT t1.a FROM t1 GROUP BY t1.a
3169
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3170
HAVING AVG(SUM(t1.b)) > 20);
3174
SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
3175
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3176
HAVING t2.c+sum > 20);
3181
DROP TABLE t1,t2,t3;
3182
CREATE TABLE t1 (a varchar(5), b varchar(10));
3183
INSERT INTO t1 VALUES
3184
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
3185
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
3186
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3192
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3193
id select_type table type possible_keys key key_len ref rows Extra
3194
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3195
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3196
ALTER TABLE t1 ADD INDEX(a);
3197
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3203
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3204
id select_type table type possible_keys key key_len ref rows Extra
3205
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3206
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3208
create table t1( f1 int,f2 int);
3209
insert into t1 values (1,1),(2,2);
3210
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';
3215
create table t1 (c int, key(c));
3216
insert into t1 values (1142477582), (1142455969);
3217
create table t2 (a int, b int);
3218
insert into t2 values (2, 1), (1, 0);
3219
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
3221
create table t1 (i int, j bigint);
3222
insert into t1 values (1, 2), (2, 2), (3, 2);
3223
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
3227
CREATE TABLE t1 (i BIGINT UNSIGNED);
3228
INSERT INTO t1 VALUES (10000000000000000000);
3229
INSERT INTO t1 VALUES (1);
3230
CREATE TABLE t2 (i BIGINT UNSIGNED);
3231
INSERT INTO t2 VALUES (10000000000000000000);
3232
INSERT INTO t2 VALUES (1);
3234
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
3236
10000000000000000000
3239
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
3241
10000000000000000000
3242
/* subquery test with cast*/
3243
SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);
3245
10000000000000000000
3249
id bigint(20) unsigned NOT NULL auto_increment,
3250
name varchar(255) NOT NULL,
3253
INSERT INTO t1 VALUES
3254
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
3256
id bigint(20) unsigned NOT NULL auto_increment,
3257
mid bigint(20) unsigned NOT NULL,
3261
INSERT INTO t2 VALUES
3262
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
3263
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
3265
(SELECT date FROM t2 WHERE mid = t1.id
3266
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3267
(SELECT date FROM t2 WHERE mid = t1.id
3268
ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
3270
id name date_last date_next_to_last
3271
1 Balazs 2006-05-01 NULL
3272
2 Joe 2006-04-20 NULL
3273
3 Frank 2006-04-13 NULL
3275
(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
3276
ORDER BY date DESC LIMIT 1, 1) AS date_count
3283
(SELECT date FROM t2 WHERE mid = t1.id
3284
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3285
(SELECT date FROM t2 WHERE mid = t1.id
3286
ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
3288
id name date_last date_next_to_last
3289
1 Balazs 2006-05-01 2006-03-30
3290
2 Joe 2006-04-20 2006-04-06
3291
3 Frank 2006-04-13 NULL
3294
i1 int(11) NOT NULL default '0',
3295
i2 int(11) NOT NULL default '0',
3296
t datetime NOT NULL default '0000-00-00 00:00:00',
3297
PRIMARY KEY (i1,i2,t)
3299
INSERT INTO t1 VALUES
3300
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
3301
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
3302
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
3303
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
3304
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
3305
(24,2,'2005-05-27 12:40:06');
3307
i1 int(11) NOT NULL default '0',
3308
i2 int(11) NOT NULL default '0',
3309
t datetime default NULL,
3312
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
3315
WHERE t1.t = (SELECT t1.t FROM t1
3316
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3317
ORDER BY t1.t DESC LIMIT 1);
3318
id select_type table type possible_keys key key_len ref rows Extra
3319
1 PRIMARY t2 system NULL NULL NULL NULL 1
3320
1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
3321
2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
3323
WHERE t1.t = (SELECT t1.t FROM t1
3324
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3325
ORDER BY t1.t DESC LIMIT 1);
3327
24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40
3329
CREATE TABLE t1 (i INT);
3330
(SELECT i FROM t1) UNION (SELECT i FROM t1);
3332
SELECT * FROM t1 WHERE NOT EXISTS
3334
(SELECT i FROM t1) UNION
3339
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3340
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1
3341
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3343
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))
3345
explain select * from t1 where not exists
3346
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3347
id select_type table type possible_keys key key_len ref rows Extra
3348
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
3349
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3350
3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3351
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
3353
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
3354
insert into t1 (a) values (FLOOR(rand() * 100));
3355
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3356
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3357
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3358
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3359
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3360
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3361
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3362
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3363
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3364
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3365
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3366
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3367
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3369
(SELECT REPEAT(' ',250) FROM t1 i1
3370
WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a
3371
FROM t1 ORDER BY a LIMIT 5;
3379
CREATE TABLE t1 (a INT, b INT);
3380
CREATE TABLE t2 (a INT);
3381
INSERT INTO t2 values (1);
3382
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
3383
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
3384
(SELECT COUNT(DISTINCT t1.b) from t2)
3388
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3389
FROM t1 GROUP BY t1.a;
3390
(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3394
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
3395
COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))
3402
SELECT COUNT(DISTINCT t1.b)
3405
FROM t1 GROUP BY t1.a LIMIT 1)
3411
SELECT COUNT(DISTINCT t1.b)
3414
FROM t1 GROUP BY t1.a LIMIT 1)
3419
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
3420
CREATE TABLE t2 (x int auto_increment, y int, z int,
3421
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
3422
SET SESSION sort_buffer_size = 32 * 1024;
3424
Warning 1292 Truncated incorrect sort_buffer_size value: '32768'
3426
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3430
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
3432
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3437
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
3438
CREATE TABLE t2 (c int);
3439
INSERT INTO t1 VALUES ('aa', 1);
3440
INSERT INTO t2 VALUES (1);
3442
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3444
SELECT c from t2 WHERE c=t1.c);
3447
INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
3449
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3451
SELECT c from t2 WHERE c=t1.c);
3457
INSERT INTO t2 VALUES (2);
3458
CREATE TABLE t3 (c int);
3459
INSERT INTO t3 VALUES (1);
3461
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
3463
SELECT c from t2 WHERE c=t1.c);
3469
DROP TABLE t1,t2,t3;
3470
DROP TABLE IF EXISTS t1;
3471
DROP TABLE IF EXISTS t2;
3472
DROP TABLE IF EXISTS t1xt2;
3474
id_1 int(5) NOT NULL,
3475
t varchar(4) DEFAULT NULL
3478
id_2 int(5) NOT NULL,
3479
t varchar(4) DEFAULT NULL
3481
CREATE TABLE t1xt2 (
3482
id_1 int(5) NOT NULL,
3483
id_2 int(5) NOT NULL
3485
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
3486
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
3487
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
3488
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3489
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3491
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3492
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3494
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3495
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3497
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3498
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3504
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3505
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
3511
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3512
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
3518
insert INTO t1xt2 VALUES (1, 12);
3519
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3520
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3523
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3524
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3527
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3528
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3531
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3532
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3537
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3538
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3543
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3544
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3549
insert INTO t1xt2 VALUES (2, 12);
3550
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3551
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3555
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3556
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3560
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3561
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3565
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3566
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3570
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3571
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3575
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3576
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3583
CREATE TABLE t1 (a int);
3584
INSERT INTO t1 VALUES (3), (1), (2);
3585
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
3590
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
3596
CREATE TABLE t1 (a int, b int);
3597
CREATE TABLE t2 (m int, n int);
3598
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3599
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3601
(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3603
COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3608
(SELECT MIN(m) FROM t2 WHERE m = count(*))
3610
COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))
3616
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
3621
CREATE TABLE t1 (a int, b int);
3622
CREATE TABLE t2 (m int, n int);
3623
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3624
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3625
SELECT COUNT(*) c, a,
3626
(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3628
c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3632
SELECT COUNT(*) c, a,
3633
(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3635
c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3640
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
3641
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
3642
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
3643
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
3645
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
3652
(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
3659
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
3660
FROM t1 WHERE t1.d=0 GROUP BY a;
3666
(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3667
LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3687
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3689
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3690
FROM t1 as tt GROUP BY tt.a;
3696
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3698
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
3699
FROM t1 as tt GROUP BY tt.a;
3705
CREATE TABLE t1 (a INT);
3706
INSERT INTO t1 values (1),(1),(1),(1);
3707
CREATE TABLE t2 (x INT);
3708
INSERT INTO t1 values (1000),(1001),(1002);
3709
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
3710
ERROR HY000: Invalid use of group function
3711
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
3712
ERROR HY000: Invalid use of group function
3714
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
3716
ERROR HY000: Invalid use of group function
3718
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
3720
ERROR HY000: Invalid use of group function
3722
CREATE TABLE t1 (a int, b int, KEY (a));
3723
INSERT INTO t1 VALUES (1,1),(2,1);
3724
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
3725
id select_type table type possible_keys key key_len ref rows Extra
3726
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
3727
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3729
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
3730
INSERT INTO t1 VALUES
3731
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
3732
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
3733
INSERT INTO t2 VALUES (7), (5), (1), (3);
3734
SELECT id, st FROM t1
3735
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3740
SELECT id, st FROM t1
3741
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3747
SELECT id, st FROM t1
3748
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3752
SELECT id, st FROM t1
3753
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3759
CREATE TABLE t1 (a int);
3760
INSERT INTO t1 VALUES (1), (2);
3762
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
3763
id select_type table type possible_keys key key_len ref rows filtered Extra
3764
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
3765
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
3767
Note 1003 select "res"."count(*)" AS "count(*)" from (select count(0) AS "count(*)" from "test"."t1" group by "test"."t1"."a") "res"
3770
a varchar(255) default NULL,
3771
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3775
a varchar(255) default NULL
3777
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
3778
INSERT INTO t1 SELECT * FROM t1;
3779
INSERT INTO t1 SELECT * FROM t1;
3780
INSERT INTO t1 SELECT * FROM t1;
3781
INSERT INTO t1 SELECT * FROM t1;
3782
INSERT INTO t1 SELECT * FROM t1;
3783
INSERT INTO t1 SELECT * FROM t1;
3784
INSERT INTO t1 SELECT * FROM t1;
3785
INSERT INTO t1 SELECT * FROM t1;
3786
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
3787
INSERT INTO `t2` VALUES ('abcdefghijk');
3788
INSERT INTO `t2` VALUES ('asdf');
3789
SET session sort_buffer_size=8192;
3791
Warning 1292 Truncated incorrect sort_buffer_size value: '8192'
3792
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
3797
CREATE TABLE t1 (a INTEGER, b INTEGER);
3798
CREATE TABLE t2 (x INTEGER);
3799
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3800
INSERT INTO t2 VALUES (1), (2);
3801
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
3802
ERROR 21000: Subquery returns more than 1 row
3803
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
3804
ERROR 21000: Subquery returns more than 1 row
3805
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
3806
(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
3809
CREATE TABLE t1 (a INT, b INT);
3810
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
3811
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
3812
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
3817
CREATE TABLE t1 (a INT);
3818
CREATE TABLE t2 (a INT);
3819
INSERT INTO t1 VALUES (1),(2);
3820
INSERT INTO t2 VALUES (1),(2);
3821
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
3822
(SELECT SUM(t1.a) FROM t2 WHERE a=0)
3824
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
3825
ERROR 21000: Subquery returns more than 1 row
3826
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
3827
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
3830
CREATE TABLE t1 (a1 INT, a2 INT);
3831
CREATE TABLE t2 (b1 INT, b2 INT);
3832
INSERT INTO t1 VALUES (100, 200);
3833
INSERT INTO t1 VALUES (101, 201);
3834
INSERT INTO t2 VALUES (101, 201);
3835
INSERT INTO t2 VALUES (103, 203);
3836
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
3837
((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL
3841
CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
3842
INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
3843
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3845
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3847
CREATE INDEX I1 ON t1 (s1);
3848
CREATE INDEX I2 ON t1 (s2);
3849
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3851
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3854
INSERT INTO t1 VALUES (0x41,0x41);
3855
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
3858
CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
3859
CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
3860
CREATE TABLE t3 (a3 BINARY(2) default '0');
3861
INSERT INTO t1 VALUES (1),(2),(3),(4);
3862
INSERT INTO t2 VALUES (1),(2),(3);
3863
INSERT INTO t3 VALUES (1),(2),(3);
3864
SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
3869
SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
3870
a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)
3875
DROP TABLE t1,t2,t3;
3876
CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
3877
CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
3878
CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
3879
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
3880
INSERT INTO t2 VALUES (2), (3), (4), (5);
3881
INSERT INTO t3 VALUES (10), (20), (30);
3882
SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
3887
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
3889
DROP TABLE t1, t2, t3;
3890
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
3891
INSERT INTO t1 VALUES ('a', 'aa');
3892
INSERT INTO t1 VALUES ('a', 'aaa');
3893
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3895
CREATE INDEX I1 ON t1 (a);
3896
CREATE INDEX I2 ON t1 (b);
3897
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3898
id select_type table type possible_keys key key_len ref rows Extra
3899
1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
3900
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
3901
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3903
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
3904
INSERT INTO t2 SELECT * FROM t1;
3905
CREATE INDEX I1 ON t2 (a);
3906
CREATE INDEX I2 ON t2 (b);
3907
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3908
id select_type table type possible_keys key key_len ref rows Extra
3909
1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
3910
1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
3911
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3914
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3915
id select_type table type possible_keys key key_len ref rows Extra
3916
1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
3917
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
3918
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3921
CREATE TABLE t1(a INT, b INT);
3922
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
3924
SELECT a AS out_a, MIN(b) FROM t1
3925
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
3927
ERROR 42S22: Unknown column 'out_a' in 'where clause'
3928
SELECT a AS out_a, MIN(b) FROM t1
3929
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
3931
ERROR 42S22: Unknown column 'out_a' in 'where clause'
3933
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
3934
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
3936
id select_type table type possible_keys key key_len ref rows Extra
3937
1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
3938
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
3939
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
3940
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
3946
CREATE TABLE t1 (a INT);
3947
CREATE TABLE t2 (a INT);
3948
INSERT INTO t1 VALUES (1),(2);
3949
INSERT INTO t2 VALUES (1),(2);
3950
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
3955
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
3956
id select_type table type possible_keys key key_len ref rows filtered Extra
3957
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3958
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3960
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
3961
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"))
3963
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
3964
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
3965
id select_type table type possible_keys key key_len ref rows filtered Extra
3966
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3967
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3968
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3969
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
3971
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
3972
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
3973
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")))
3976
f7 varchar(32) collate utf8_bin NOT NULL default '',
3977
f10 varchar(32) collate utf8_bin default NULL,
3980
INSERT INTO t4 VALUES(1,1), (2,null);
3982
f4 varchar(32) collate utf8_bin NOT NULL default '',
3983
f2 varchar(50) collate utf8_bin default NULL,
3984
f3 varchar(10) collate utf8_bin default NULL,
3988
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
3990
f8 varchar(32) collate utf8_bin NOT NULL default '',
3991
f1 varchar(10) collate utf8_bin default NULL,
3992
f9 varchar(32) collate utf8_bin default NULL,
3995
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
3997
f6 varchar(32) collate utf8_bin NOT NULL default '',
3998
f5 varchar(50) collate utf8_bin default NULL,
4001
INSERT INTO t3 VALUES (1,null), (2,null);
4003
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
4004
IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
4008
FROM t2 VPC, t4 a2, t2 a3
4010
VPC.f4 = a2.f10 AND a3.f2 = a4
4017
t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
4022
DROP TABLE t1, t2, t3, t4;
4024
create table t_out (subcase char(3),
4025
a1 char(2), b1 char(2), c1 char(2));
4026
create table t_in (a2 char(2), b2 char(2), c2 char(2));
4027
insert into t_out values ('A.1','2a', NULL, '2a');
4028
insert into t_out values ('A.3', '2a', NULL, '2a');
4029
insert into t_out values ('A.4', '2a', NULL, 'xx');
4030
insert into t_out values ('B.1', '2a', '2a', '2a');
4031
insert into t_out values ('B.2', '2a', '2a', '2a');
4032
insert into t_out values ('B.3', '3a', 'xx', '3a');
4033
insert into t_out values ('B.4', 'xx', '3a', '3a');
4034
insert into t_in values ('1a', '1a', '1a');
4035
insert into t_in values ('2a', '2a', '2a');
4036
insert into t_in values (NULL, '2a', '2a');
4037
insert into t_in values ('3a', NULL, '3a');
4039
Test general IN semantics (not top-level)
4043
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
4044
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
4045
from t_out where subcase = 'A.1';
4046
subcase pred_in pred_not_in
4048
case A.2 - impossible
4051
(a1, b1, c1) IN (select * from t_in) pred_in,
4052
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4053
from t_out where subcase = 'A.3';
4054
subcase pred_in pred_not_in
4058
(a1, b1, c1) IN (select * from t_in) pred_in,
4059
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4060
from t_out where subcase = 'A.4';
4061
subcase pred_in pred_not_in
4065
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
4066
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
4067
from t_out where subcase = 'B.1';
4068
subcase pred_in pred_not_in
4072
(a1, b1, c1) IN (select * from t_in) pred_in,
4073
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4074
from t_out where subcase = 'B.2';
4075
subcase pred_in pred_not_in
4079
(a1, b1, c1) IN (select * from t_in) pred_in,
4080
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4081
from t_out where subcase = 'B.3';
4082
subcase pred_in pred_not_in
4086
(a1, b1, c1) IN (select * from t_in) pred_in,
4087
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4088
from t_out where subcase = 'B.4';
4089
subcase pred_in pred_not_in
4092
Test IN as top-level predicate, and
4093
as non-top level for cases A.3, B.3 (the only cases with NULL result).
4096
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4097
where subcase = 'A.1' and
4098
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
4101
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4102
where subcase = 'A.1' and
4103
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
4106
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4107
where subcase = 'A.1' and
4108
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
4112
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4113
where subcase = 'A.3' and
4114
(a1, b1, c1) IN (select * from t_in);
4117
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4118
where subcase = 'A.3' and
4119
(a1, b1, c1) NOT IN (select * from t_in);
4122
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4123
where subcase = 'A.3' and
4124
NOT((a1, b1, c1) IN (select * from t_in));
4127
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4128
where subcase = 'A.3' and
4129
((a1, b1, c1) IN (select * from t_in)) is NULL and
4130
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
4134
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4135
where subcase = 'A.4' and
4136
(a1, b1, c1) IN (select * from t_in);
4139
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4140
where subcase = 'A.4' and
4141
(a1, b1, c1) NOT IN (select * from t_in);
4144
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4145
where subcase = 'A.4' and
4146
NOT((a1, b1, c1) IN (select * from t_in));
4150
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4151
where subcase = 'B.1' and
4152
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
4155
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4156
where subcase = 'B.1' and
4157
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
4160
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4161
where subcase = 'B.1' and
4162
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
4166
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4167
where subcase = 'B.2' and
4168
(a1, b1, c1) IN (select * from t_in);
4171
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4172
where subcase = 'B.2' and
4173
(a1, b1, c1) NOT IN (select * from t_in);
4176
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4177
where subcase = 'B.2' and
4178
NOT((a1, b1, c1) IN (select * from t_in));
4182
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4183
where subcase = 'B.3' and
4184
(a1, b1, c1) IN (select * from t_in);
4187
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4188
where subcase = 'B.3' and
4189
(a1, b1, c1) NOT IN (select * from t_in);
4192
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4193
where subcase = 'B.3' and
4194
NOT((a1, b1, c1) IN (select * from t_in));
4197
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4198
where subcase = 'B.3' and
4199
((a1, b1, c1) IN (select * from t_in)) is NULL and
4200
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
4204
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4205
where subcase = 'B.4' and
4206
(a1, b1, c1) IN (select * from t_in);
4209
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4210
where subcase = 'B.4' and
4211
(a1, b1, c1) NOT IN (select * from t_in);
4214
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4215
where subcase = 'B.4' and
4216
NOT((a1, b1, c1) IN (select * from t_in));
4221
CREATE TABLE t1 (s1 char(1));
4222
INSERT INTO t1 VALUES ('a');
4223
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
4227
CREATE TABLE t1( a INT );
4228
INSERT INTO t1 VALUES (1),(2);
4229
CREATE TABLE t2( a INT, b INT );
4231
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
4232
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2
4234
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
4235
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
4237
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
4238
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
4242
SELECT a INTO @var FROM t1 WHERE a = 2
4244
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2
4249
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
4251
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
4256
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
4258
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
4260
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
4264
SELECT a FROM t1 WHERE a = 2
4266
SELECT a FROM t1 WHERE a = 2
4273
SELECT a FROM t1 WHERE a = 2
4275
SELECT a FROM t1 WHERE a = 2
4280
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
4281
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
4282
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
4285
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
4288
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
4289
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4290
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
4291
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4292
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4293
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4294
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
4295
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4296
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4297
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4298
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
4299
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4300
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
4301
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a))) t1a' at line 1
4302
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
4303
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1
4304
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
4305
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1
4306
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
4309
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;
4312
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
4315
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
4318
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
4322
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
4323
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ON 1' at line 1
4324
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
4325
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON 1' at line 1
4326
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
4327
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ON 1' at line 1
4328
SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;
4329
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1
4330
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
4331
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1
4332
SELECT * FROM t1 JOIN (t1 t1a) ON 1;
4338
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
4344
SELECT * FROM (t1 t1a);
4348
SELECT * FROM ((t1 t1a));
4352
SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;
4356
SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;
4360
SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;
4364
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
4368
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
4369
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a2' at line 1
4370
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
4373
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );
4376
SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );
4379
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
4380
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1
4381
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4382
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4383
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4384
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4385
SELECT * FROM t1 WHERE a = ( SELECT 1 );
4388
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );
4391
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
4392
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1
4393
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
4394
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4395
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
4396
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4397
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
4398
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1
4399
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4400
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4401
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4402
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4403
SELECT ( SELECT 1 INTO @v );
4404
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
4405
SELECT ( SELECT 1 INTO OUTFILE 'file' );
4406
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4407
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
4408
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4409
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
4410
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
4411
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4412
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4413
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4414
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4415
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4416
( SELECT a FROM t1 WHERE a = 1 ) a
4419
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
4420
( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a
4423
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
4425
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
4426
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4427
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4428
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1' at line 1
4429
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4430
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4431
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4432
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1' at line 1
4433
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4434
( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
4436
SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
4437
((SELECT 1 UNION SELECT 1 UNION SELECT 1))
4439
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4440
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4441
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4442
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
4443
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
4446
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4447
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4448
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4449
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4450
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4451
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4452
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4453
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4454
SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4455
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4456
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4457
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4458
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4459
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4460
SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4461
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4462
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4465
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4468
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4471
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4474
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
4475
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
4476
SELECT EXISTS(SELECT 1+1);
4479
SELECT EXISTS(SELECT 1+1 INTO @test);
4480
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @test)' at line 1
4481
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
4482
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
4483
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
4484
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
4485
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
4486
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
added
removed
mysql-test/r/subselect.result
