1
show variables like 'optimizer_switch';
4
set optimizer_switch='no_materialization';
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
9
explain extended select (select 2);
10
id select_type table type possible_keys key key_len ref rows filtered Extra
11
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
13
Note 1249 Select 2 was reduced during optimization
14
Note 1003 select 2 AS "(select 2)"
15
SELECT (SELECT 1) UNION SELECT (SELECT 2);
19
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
20
id select_type table type possible_keys key key_len ref rows filtered Extra
21
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
22
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
23
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
25
Note 1249 Select 2 was reduced during optimization
26
Note 1249 Select 4 was reduced during optimization
27
Note 1003 select 1 AS "(SELECT 1)" union select 2 AS "(SELECT 2)"
28
SELECT (SELECT (SELECT 0 UNION SELECT 0));
29
(SELECT (SELECT 0 UNION SELECT 0))
31
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
32
id select_type table type possible_keys key key_len ref rows filtered Extra
33
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
34
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
35
4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
36
NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL
38
Note 1249 Select 2 was reduced during optimization
39
Note 1003 select (select 0 AS "0" union select 0 AS "0") AS "(SELECT (SELECT 0 UNION SELECT 0))"
40
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
41
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
42
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;
43
ERROR 42S22: Reference 'b' not supported (forward reference in item list)
44
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
47
SELECT (SELECT a) as a;
48
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
49
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
50
id select_type table type possible_keys key key_len ref rows filtered Extra
51
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
52
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
53
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
55
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
56
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
57
Note 1003 select 1 AS "1" from (select 1 AS "a") "b" having ((select '1' AS "a") = 1)
58
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
62
ERROR 42S22: Unknown column 'a' in 'field list'
63
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
66
SELECT 1 FROM (SELECT (SELECT a) b) c;
67
ERROR 42S22: Unknown column 'a' in 'field list'
68
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
71
SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1);
72
ERROR 21000: Operand should contain 1 column(s)
73
SELECT 1 IN (SELECT 1);
76
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
79
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
80
ERROR 42S22: Unknown column 'a' in 'field list'
81
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
82
ERROR 42S22: Unknown column 'a' in 'field list'
83
SELECT (SELECT 1,2,3) = ROW(1,2,3);
84
(SELECT 1,2,3) = ROW(1,2,3)
86
SELECT (SELECT 1,2,3) = ROW(1,2,1);
87
(SELECT 1,2,3) = ROW(1,2,1)
89
SELECT (SELECT 1,2,3) < ROW(1,2,1);
90
(SELECT 1,2,3) < ROW(1,2,1)
92
SELECT (SELECT 1,2,3) > ROW(1,2,1);
93
(SELECT 1,2,3) > ROW(1,2,1)
95
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
96
(SELECT 1,2,3) = ROW(1,2,NULL)
98
SELECT ROW(1,2,3) = (SELECT 1,2,3);
99
ROW(1,2,3) = (SELECT 1,2,3)
101
SELECT ROW(1,2,3) = (SELECT 1,2,1);
102
ROW(1,2,3) = (SELECT 1,2,1)
104
SELECT ROW(1,2,3) < (SELECT 1,2,1);
105
ROW(1,2,3) < (SELECT 1,2,1)
107
SELECT ROW(1,2,3) > (SELECT 1,2,1);
108
ROW(1,2,3) > (SELECT 1,2,1)
110
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
111
ROW(1,2,3) = (SELECT 1,2,NULL)
113
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
114
(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
116
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
117
(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
119
SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
120
(SELECT 1.5,2,'a') = ROW('1.5b',2,'b')
123
Warning 1292 Truncated incorrect DOUBLE value: '1.5b'
124
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
125
(SELECT 'b',2,'a') = ROW(1.5,2,'a')
127
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
128
(SELECT 1.5,2,'a') = ROW(1.5,'2','a')
130
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
131
(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
133
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
134
ERROR 21000: Operand should contain 1 column(s)
135
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
138
create table t1 (a int);
139
create table t2 (a int, b int);
140
create table t3 (a int);
141
create table t4 (a int not null, b int not null);
142
insert into t1 values (2);
143
insert into t2 values (1,7),(2,7);
144
insert into t4 values (4,8),(3,8),(5,9);
145
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
146
ERROR 42S22: Reference 'a1' not supported (forward reference in item list)
147
select (select a from t1 where t1.a=t2.a), a from t2;
148
(select a from t1 where t1.a=t2.a) a
151
select (select a from t1 where t1.a=t2.b), a from t2;
152
(select a from t1 where t1.a=t2.b) a
155
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
156
(select a from t1) a (select 1 union select 2 limit 1)
159
select (select a from t3), a from t2;
163
select * from t2 where t2.a=(select a from t1);
166
insert into t3 values (6),(7),(3);
167
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
171
(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;
176
(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);
182
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);
183
id select_type table type possible_keys key key_len ref rows filtered Extra
184
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
185
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using filesort
186
3 UNION t4 ALL NULL NULL NULL NULL 3 100.00 Using where
187
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
188
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
190
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")
191
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
192
(select a from t3 where a<t2.a*4 order by 1 desc limit 1) a
195
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
(select t3.a from t3 where a<8 order by 1 desc limit 1) a
199
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
200
(select * from t2 where a>1) as tt;
201
id select_type table type possible_keys key key_len ref rows filtered Extra
202
1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00
203
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
204
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
206
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"
207
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);
210
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
213
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);
215
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
216
b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
220
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
221
id select_type table type possible_keys key key_len ref rows filtered Extra
222
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00
223
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
224
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
226
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
227
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"
228
select * from t3 where exists (select * from t2 where t2.b=t3.a);
231
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
235
select * from t3 where a in (select b from t2);
238
select * from t3 where a not in (select b from t2);
242
select * from t3 where a = some (select b from t2);
245
select * from t3 where a <> any (select b from t2);
249
select * from t3 where a = all (select b from t2);
252
select * from t3 where a <> all (select b from t2);
256
insert into t2 values (100, 5);
257
select * from t3 where a < any (select b from t2);
261
select * from t3 where a < all (select b from t2);
264
select * from t3 where a >= any (select b from t2);
268
explain extended select * from t3 where a >= any (select b from t2);
269
id select_type table type possible_keys key key_len ref rows filtered Extra
270
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
271
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
273
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= (select min("test"."t2"."b") from "test"."t2")))
274
select * from t3 where a >= all (select b from t2);
277
delete from t2 where a=100;
278
select * from t3 where a in (select a,b from t2);
279
ERROR 21000: Operand should contain 1 column(s)
280
select * from t3 where a in (select * from t2);
281
ERROR 21000: Operand should contain 1 column(s)
282
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
283
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
285
insert into t2 values (2,10);
286
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
289
delete from t2 where a=2 and b=10;
290
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
293
create table t5 (a int);
294
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
295
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
298
insert into t5 values (5);
299
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
300
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
303
insert into t5 values (2);
304
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
305
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
308
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;
309
id select_type table type possible_keys key key_len ref rows filtered Extra
310
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
311
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
312
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where
313
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
315
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
316
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
317
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"
318
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
319
ERROR 21000: Subquery returns more than 1 row
320
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
321
create table t7( uq int primary key, name char(25));
322
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
323
insert into t6 values (1,1),(1,2),(2,2),(1,3);
324
select * from t6 where exists (select * from t7 where uq = clinic_uq);
329
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
330
id select_type table type possible_keys key key_len ref rows filtered Extra
331
1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
332
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
334
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
335
Note 1003 select "test"."t6"."patient_uq" AS "patient_uq","test"."t6"."clinic_uq" AS "clinic_uq" from "test"."t6" where exists(select 1 AS "Not_used" from "test"."t7" where ("test"."t7"."uq" = "test"."t6"."clinic_uq"))
336
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
337
ERROR 23000: Column 'a' in field list is ambiguous
339
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
340
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
341
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
342
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
343
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
344
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
345
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
348
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
351
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
355
`pseudo` varchar(35) character set latin1 NOT NULL default '',
356
`email` varchar(60) character set latin1 NOT NULL default '',
357
PRIMARY KEY (`pseudo`),
358
UNIQUE KEY `email` (`email`)
359
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
360
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
361
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
362
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
363
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');
364
id select_type table type possible_keys key key_len ref rows filtered Extra
365
1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index
366
4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
367
2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00
368
3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
370
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
371
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
372
t8 WHERE pseudo='joce');
373
ERROR 21000: Operand should contain 1 column(s)
374
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
376
ERROR 21000: Operand should contain 1 column(s)
377
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
380
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
381
ERROR 21000: Subquery returns more than 1 row
382
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
384
`topic` mediumint(8) unsigned NOT NULL default '0',
385
`date` date NOT NULL default '0000-00-00',
386
`pseudo` varchar(35) character set latin1 NOT NULL default '',
387
PRIMARY KEY (`pseudo`,`date`,`topic`),
388
KEY `topic` (`topic`)
389
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
390
INSERT INTO t1 (topic,date,pseudo) VALUES
391
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
392
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
393
id select_type table type possible_keys key key_len ref rows filtered Extra
394
1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
396
Note 1003 select distinct "test"."t1"."date" AS "date" from "test"."t1" where ("test"."t1"."date" = '2002-08-03')
397
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
398
id select_type table type possible_keys key key_len ref rows filtered Extra
399
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
400
2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
402
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')"
403
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
406
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
407
(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
409
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
414
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
415
ERROR 21000: Subquery returns more than 1 row
416
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
417
id select_type table type possible_keys key key_len ref rows filtered Extra
418
1 PRIMARY t1 index NULL topic 3 NULL 2 100.00 Using index
419
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
420
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
421
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
423
Note 1003 select 1 AS "1" from "test"."t1" where 1
426
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
427
`maxnumrep` int(10) unsigned NOT NULL default '0',
428
PRIMARY KEY (`numeropost`),
429
UNIQUE KEY `maxnumrep` (`maxnumrep`)
430
) ENGINE=MyISAM ROW_FORMAT=FIXED;
431
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
433
`mot` varchar(30) NOT NULL default '',
434
`topic` mediumint(8) unsigned NOT NULL default '0',
435
`date` date NOT NULL default '0000-00-00',
436
`pseudo` varchar(35) NOT NULL default '',
437
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
438
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
439
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
440
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
443
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;
447
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
448
ERROR 42S22: Unknown column 'a' in 'having clause'
449
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
450
ERROR 42S22: Unknown column 'a' in 'having clause'
451
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
452
mot topic date pseudo
453
joce 40143 2002-10-22 joce
454
joce 43506 2002-10-22 joce
455
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
456
mot topic date pseudo
457
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
458
mot topic date pseudo
459
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
460
mot topic date pseudo
461
joce 40143 2002-10-22 joce
462
joce 43506 2002-10-22 joce
463
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
464
mot topic date pseudo
465
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
466
mot topic date pseudo
467
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
468
mot topic date pseudo
469
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
470
mot topic date pseudo
471
joce 40143 2002-10-22 joce
472
joce 43506 2002-10-22 joce
473
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
474
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100)
475
joce 40143 2002-10-22 joce 1
476
joce 43506 2002-10-22 joce 1
477
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
478
mot topic date pseudo
479
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
480
mot topic date pseudo
481
joce 40143 2002-10-22 joce
482
joce 43506 2002-10-22 joce
483
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
484
mot topic date pseudo
485
joce 40143 2002-10-22 joce
486
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
487
mot topic date pseudo
488
joce 40143 2002-10-22 joce
489
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
490
mot topic date pseudo
491
joce 40143 2002-10-22 joce
492
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
493
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000)
494
joce 40143 2002-10-22 joce 1
495
joce 43506 2002-10-22 joce 0
498
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
499
`maxnumrep` int(10) unsigned NOT NULL default '0',
500
PRIMARY KEY (`numeropost`),
501
UNIQUE KEY `maxnumrep` (`maxnumrep`)
502
) ENGINE=MyISAM ROW_FORMAT=FIXED;
503
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
504
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
505
ERROR 21000: Subquery returns more than 1 row
506
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
507
ERROR 21000: Subquery returns more than 1 row
509
create table t1 (a int);
510
insert into t1 values (1),(2),(3);
511
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
517
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
518
INSERT INTO t1 VALUES ();
519
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
520
ERROR 21000: Subquery returns more than 1 row
523
`numeropost` mediumint(8) unsigned NOT NULL default '0',
524
`numreponse` int(10) unsigned NOT NULL auto_increment,
525
`pseudo` varchar(35) NOT NULL default '',
526
PRIMARY KEY (`numeropost`,`numreponse`),
527
UNIQUE KEY `numreponse` (`numreponse`),
528
KEY `pseudo` (`pseudo`,`numeropost`)
530
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
531
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
532
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
533
ERROR 42S22: Unknown column 'a' in 'having clause'
534
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
535
numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)
536
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
537
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
538
ERROR 21000: Subquery returns more than 1 row
539
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
540
id select_type table type possible_keys key key_len ref rows filtered Extra
541
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
543
Note 1003 select max("test"."t1"."numreponse") AS "MAX(numreponse)" from "test"."t1" where ("test"."t1"."numeropost" = '1')
544
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
545
id select_type table type possible_keys key key_len ref rows filtered Extra
546
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
547
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
549
Note 1003 select '3' AS "numreponse" from "test"."t1" where (('1' = '1'))
551
CREATE TABLE t1 (a int(1));
552
INSERT INTO t1 VALUES (1);
553
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
557
create table t1 (a int NOT NULL, b int, primary key (a));
558
create table t2 (a int NOT NULL, b int, primary key (a));
559
insert into t1 values (0, 10),(1, 11),(2, 12);
560
insert into t2 values (1, 21),(2, 22),(3, 23);
566
update t1 set b= (select b from t1);
567
ERROR HY000: You can't specify target table 't1' for update in FROM clause
568
update t1 set b= (select b from t2);
569
ERROR 21000: Subquery returns more than 1 row
570
update t1 set b= (select b from t2 where t1.a = t2.a);
577
create table t1 (a int NOT NULL, b int, primary key (a));
578
create table t2 (a int NOT NULL, b int, primary key (a));
579
insert into t1 values (0, 10),(1, 11),(2, 12);
580
insert into t2 values (1, 21),(2, 12),(3, 23);
586
select * from t1 where b = (select b from t2 where t1.a = t2.a);
589
delete from t1 where b = (select b from t1);
590
ERROR HY000: You can't specify target table 't1' for update in FROM clause
591
delete from t1 where b = (select b from t2);
592
ERROR 21000: Subquery returns more than 1 row
593
delete from t1 where b = (select b from t2 where t1.a = t2.a);
599
create table t11 (a int NOT NULL, b int, primary key (a));
600
create table t12 (a int NOT NULL, b int, primary key (a));
601
create table t2 (a int NOT NULL, b int, primary key (a));
602
insert into t11 values (0, 10),(1, 11),(2, 12);
603
insert into t12 values (33, 10),(22, 11),(2, 12);
604
insert into t2 values (1, 21),(2, 12),(3, 23);
615
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
616
ERROR HY000: You can't specify target table 't12' for update in FROM clause
617
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
618
ERROR 21000: Subquery returns more than 1 row
619
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
628
drop table t11, t12, t2;
629
CREATE TABLE t1 (x int);
630
create table t2 (a int);
631
create table t3 (b int);
632
insert into t2 values (1);
633
insert into t3 values (1),(2);
634
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
635
ERROR HY000: You can't specify target table 't1' for update in FROM clause
636
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
637
ERROR 21000: Subquery returns more than 1 row
638
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
642
insert into t2 values (1);
643
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
648
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
655
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
664
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
665
ERROR 42S22: Unknown column 'x' in 'field list'
666
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
676
drop table t1, t2, t3;
677
CREATE TABLE t1 (x int not null, y int, primary key (x));
678
create table t2 (a int);
679
create table t3 (a int);
680
insert into t2 values (1);
681
insert into t3 values (1),(2);
684
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
685
ERROR HY000: You can't specify target table 't1' for update in FROM clause
686
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
687
ERROR 21000: Subquery returns more than 1 row
688
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
692
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
696
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
701
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
706
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
712
drop table t1, t2, t3;
713
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
714
ERROR HY000: No tables used
715
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
716
INSERT INTO t2 VALUES (1),(2);
717
SELECT * FROM t2 WHERE id IN (SELECT 1);
720
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
721
id select_type table type possible_keys key key_len ref rows filtered Extra
722
1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
724
Note 1249 Select 2 was reduced during optimization
725
Note 1003 select "test"."t2"."id" AS "id" from "test"."t2" where ("test"."t2"."id" = 1)
726
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
729
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
732
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
733
id select_type table type possible_keys key key_len ref rows filtered Extra
734
1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
736
Note 1249 Select 3 was reduced during optimization
737
Note 1249 Select 2 was reduced during optimization
738
Note 1003 select "test"."t2"."id" AS "id" from "test"."t2" where ("test"."t2"."id" = (1 + 1))
739
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
740
id select_type table type possible_keys key key_len ref rows filtered Extra
741
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
742
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
743
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
744
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
746
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))))
747
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
749
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
752
INSERT INTO t2 VALUES ((SELECT * FROM t2));
753
ERROR HY000: You can't specify target table 't2' for update in FROM clause
754
INSERT INTO t2 VALUES ((SELECT id FROM t2));
755
ERROR HY000: You can't specify target table 't2' for update in FROM clause
760
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
761
INSERT INTO t1 values (1),(1);
762
UPDATE t2 SET id=(SELECT * FROM t1);
763
ERROR 21000: Subquery returns more than 1 row
765
create table t1 (a int);
766
insert into t1 values (1),(2),(3);
767
select 1 IN (SELECT * from t1);
768
1 IN (SELECT * from t1)
770
select 10 IN (SELECT * from t1);
771
10 IN (SELECT * from t1)
773
select NULL IN (SELECT * from t1);
774
NULL IN (SELECT * from t1)
776
update t1 set a=NULL where a=2;
777
select 1 IN (SELECT * from t1);
778
1 IN (SELECT * from t1)
780
select 3 IN (SELECT * from t1);
781
3 IN (SELECT * from t1)
783
select 10 IN (SELECT * from t1);
784
10 IN (SELECT * from t1)
786
select 1 > ALL (SELECT * from t1);
787
1 > ALL (SELECT * from t1)
789
select 10 > ALL (SELECT * from t1);
790
10 > ALL (SELECT * from t1)
792
select 1 > ANY (SELECT * from t1);
793
1 > ANY (SELECT * from t1)
795
select 10 > ANY (SELECT * from t1);
796
10 > ANY (SELECT * from t1)
799
create table t1 (a varchar(20));
800
insert into t1 values ('A'),('BC'),('DEF');
801
select 'A' IN (SELECT * from t1);
802
'A' IN (SELECT * from t1)
804
select 'XYZS' IN (SELECT * from t1);
805
'XYZS' IN (SELECT * from t1)
807
select NULL IN (SELECT * from t1);
808
NULL IN (SELECT * from t1)
810
update t1 set a=NULL where a='BC';
811
select 'A' IN (SELECT * from t1);
812
'A' IN (SELECT * from t1)
814
select 'DEF' IN (SELECT * from t1);
815
'DEF' IN (SELECT * from t1)
817
select 'XYZS' IN (SELECT * from t1);
818
'XYZS' IN (SELECT * from t1)
820
select 'A' > ALL (SELECT * from t1);
821
'A' > ALL (SELECT * from t1)
823
select 'XYZS' > ALL (SELECT * from t1);
824
'XYZS' > ALL (SELECT * from t1)
826
select 'A' > ANY (SELECT * from t1);
827
'A' > ANY (SELECT * from t1)
829
select 'XYZS' > ANY (SELECT * from t1);
830
'XYZS' > ANY (SELECT * from t1)
833
create table t1 (a float);
834
insert into t1 values (1.5),(2.5),(3.5);
835
select 1.5 IN (SELECT * from t1);
836
1.5 IN (SELECT * from t1)
838
select 10.5 IN (SELECT * from t1);
839
10.5 IN (SELECT * from t1)
841
select NULL IN (SELECT * from t1);
842
NULL IN (SELECT * from t1)
844
update t1 set a=NULL where a=2.5;
845
select 1.5 IN (SELECT * from t1);
846
1.5 IN (SELECT * from t1)
848
select 3.5 IN (SELECT * from t1);
849
3.5 IN (SELECT * from t1)
851
select 10.5 IN (SELECT * from t1);
852
10.5 IN (SELECT * from t1)
854
select 1.5 > ALL (SELECT * from t1);
855
1.5 > ALL (SELECT * from t1)
857
select 10.5 > ALL (SELECT * from t1);
858
10.5 > ALL (SELECT * from t1)
860
select 1.5 > ANY (SELECT * from t1);
861
1.5 > ANY (SELECT * from t1)
863
select 10.5 > ANY (SELECT * from t1);
864
10.5 > ANY (SELECT * from t1)
866
explain extended select (select a+1) from t1;
867
id select_type table type possible_keys key key_len ref rows filtered Extra
868
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
870
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
871
Note 1249 Select 2 was reduced during optimization
872
Note 1003 select ("test"."t1"."a" + 1) AS "(select a+1)" from "test"."t1"
873
select (select a+1) from t1;
879
CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a));
880
CREATE TABLE t2 (a int(11) default '0', INDEX (a));
881
INSERT INTO t1 VALUES (1),(2),(3),(4);
882
INSERT INTO t2 VALUES (1),(2),(3);
883
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
884
a t1.a in (select t2.a from t2)
889
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
890
id select_type table type possible_keys key key_len ref rows filtered Extra
891
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
892
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
894
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"
895
CREATE TABLE t3 (a int(11) default '0');
896
INSERT INTO t3 VALUES (1),(2),(3);
897
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
898
a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
903
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
904
id select_type table type possible_keys key key_len ref rows filtered Extra
905
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
906
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
907
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
909
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"
911
create table t1 (a float);
912
select 10.5 IN (SELECT * from t1 LIMIT 1);
913
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
914
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
915
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
917
create table t1 (a int, b int, c varchar(10));
918
create table t2 (a int);
919
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
920
insert into t2 values (1),(2),(NULL);
921
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;
922
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a)
926
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;
927
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a)
931
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;
932
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a)
937
create table t1 (a int, b real, c varchar(10));
938
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
939
select ROW(1, 1, 'a') IN (select a,b,c from t1);
940
ROW(1, 1, 'a') IN (select a,b,c from t1)
942
select ROW(1, 2, 'a') IN (select a,b,c from t1);
943
ROW(1, 2, 'a') IN (select a,b,c from t1)
945
select ROW(1, 1, 'a') IN (select b,a,c from t1);
946
ROW(1, 1, 'a') IN (select b,a,c from t1)
948
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
949
ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null)
951
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
952
ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null)
954
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
955
ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null)
957
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
958
ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
960
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
961
ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
963
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
964
ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
966
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
967
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
969
CREATE TABLE t1 (a int(1));
970
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
971
id select_type table type possible_keys key key_len ref rows filtered Extra
972
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
973
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
975
Note 1003 select (select rand() AS "RAND()" from "test"."t1") AS "(SELECT RAND() FROM t1)" from "test"."t1"
976
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
977
id select_type table type possible_keys key key_len ref rows filtered Extra
978
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
979
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
981
Note 1003 select (select encrypt('test') AS "ENCRYPT('test')" from "test"."t1") AS "(SELECT ENCRYPT('test') FROM t1)" from "test"."t1"
982
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
983
id select_type table type possible_keys key key_len ref rows filtered Extra
984
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
985
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
987
Note 1003 select (select benchmark(1,1) AS "BENCHMARK(1,1)" from "test"."t1") AS "(SELECT BENCHMARK(1,1) FROM t1)" from "test"."t1"
990
`mot` varchar(30) character set latin1 NOT NULL default '',
991
`topic` mediumint(8) unsigned NOT NULL default '0',
992
`date` date NOT NULL default '0000-00-00',
993
`pseudo` varchar(35) character set latin1 NOT NULL default '',
994
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
995
KEY `pseudo` (`pseudo`,`date`,`topic`),
996
KEY `topic` (`topic`)
997
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
999
`mot` varchar(30) character set latin1 NOT NULL default '',
1000
`topic` mediumint(8) unsigned NOT NULL default '0',
1001
`date` date NOT NULL default '0000-00-00',
1002
`pseudo` varchar(35) character set latin1 NOT NULL default '',
1003
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
1004
KEY `pseudo` (`pseudo`,`date`,`topic`),
1005
KEY `topic` (`topic`)
1006
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
1008
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
1009
`maxnumrep` int(10) unsigned NOT NULL default '0',
1010
PRIMARY KEY (`numeropost`),
1011
UNIQUE KEY `maxnumrep` (`maxnumrep`)
1012
) ENGINE=MyISAM CHARSET=latin1;
1013
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1014
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1015
INSERT INTO t3 VALUES (1,1);
1016
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
1021
mot topic date pseudo
1022
joce 1 0000-00-00 joce
1023
test 2 0000-00-00 test
1024
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
1025
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
1027
mot topic date pseudo
1028
joce 1 0000-00-00 joce
1029
drop table t1, t2, t3;
1030
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1033
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
1034
SHOW CREATE TABLE t1;
1036
t1 CREATE TABLE "t1" (
1037
"a" int(1) NOT NULL,
1038
"(SELECT 1)" int(1) NOT NULL
1039
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1041
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1042
SHOW CREATE TABLE t1;
1044
t1 CREATE TABLE "t1" (
1045
"a" int(1) NOT NULL,
1046
"(SELECT a)" int(1) NOT NULL
1047
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1049
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
1050
SHOW CREATE TABLE t1;
1052
t1 CREATE TABLE "t1" (
1053
"a" int(1) NOT NULL,
1054
"(SELECT a+0)" int(3) NOT NULL
1055
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1057
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1061
SHOW CREATE TABLE t1;
1063
t1 CREATE TABLE "t1" (
1064
"a" bigint(20) NOT NULL
1065
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1067
create table t1 (a int);
1068
insert into t1 values (1), (2), (3);
1069
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
1071
id select_type table type possible_keys key key_len ref rows filtered Extra
1072
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1073
2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1074
3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1076
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"
1078
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);
1079
ERROR 42S02: Table 'test.t1' doesn't exist
1081
ID int(11) NOT NULL auto_increment,
1082
name char(35) NOT NULL default '',
1083
t2 char(3) NOT NULL default '',
1084
District char(20) NOT NULL default '',
1085
Population int(11) NOT NULL default '0',
1088
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
1089
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
1090
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
1092
Code char(3) NOT NULL default '',
1093
Name char(52) NOT NULL default '',
1094
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1095
Region char(26) NOT NULL default '',
1096
SurfaceArea float(10,2) NOT NULL default '0.00',
1097
IndepYear smallint(6) default NULL,
1098
Population int(11) NOT NULL default '0',
1099
LifeExpectancy float(3,1) default NULL,
1100
GNP float(10,2) default NULL,
1101
GNPOld float(10,2) default NULL,
1102
LocalName char(45) NOT NULL default '',
1103
GovernmentForm char(45) NOT NULL default '',
1104
HeadOfState char(60) default NULL,
1105
Capital int(11) default NULL,
1106
Code2 char(2) NOT NULL default '',
1109
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');
1110
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');
1111
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);
1112
Continent Name Population
1113
Oceania Sydney 3276207
1116
`id` mediumint(8) unsigned NOT NULL auto_increment,
1117
`pseudo` varchar(35) character set latin1 NOT NULL default '',
1119
UNIQUE KEY `pseudo` (`pseudo`)
1120
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1121
INSERT INTO t1 (pseudo) VALUES ('test');
1122
SELECT 0 IN (SELECT 1 FROM t1 a);
1123
0 IN (SELECT 1 FROM t1 a)
1125
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1126
id select_type table type possible_keys key key_len ref rows filtered Extra
1127
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1128
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1130
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)"
1131
INSERT INTO t1 (pseudo) VALUES ('test1');
1132
SELECT 0 IN (SELECT 1 FROM t1 a);
1133
0 IN (SELECT 1 FROM t1 a)
1135
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1136
id select_type table type possible_keys key key_len ref rows filtered Extra
1137
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1138
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1140
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)"
1143
`i` int(11) NOT NULL default '0',
1145
) ENGINE=MyISAM CHARSET=latin1;
1146
INSERT INTO t1 VALUES (1);
1147
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
1148
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1149
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1150
ERROR 42S22: Unknown column 't.i' in 'field list'
1156
id int(11) default NULL
1157
) ENGINE=MyISAM CHARSET=latin1;
1158
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
1160
id int(11) default NULL,
1161
name varchar(15) default NULL
1162
) ENGINE=MyISAM CHARSET=latin1;
1163
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
1164
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
1172
create table t1 (a int, unique index indexa (a));
1173
insert into t1 values (-1), (-4), (-2), (NULL);
1174
select -10 IN (select a from t1 FORCE INDEX (indexa));
1175
-10 IN (select a from t1 FORCE INDEX (indexa))
1178
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
1179
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
1180
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
1181
id select_type table type possible_keys key key_len ref rows filtered Extra
1182
1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition
1183
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1185
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"))
1188
ID int(10) unsigned NOT NULL auto_increment,
1189
SUB_ID int(3) unsigned NOT NULL default '0',
1190
REF_ID int(10) unsigned default NULL,
1191
REF_SUB int(3) unsigned default '0',
1192
PRIMARY KEY (ID,SUB_ID),
1193
UNIQUE KEY t1_PK (ID,SUB_ID),
1194
KEY t1_FK (REF_ID,REF_SUB),
1195
KEY t1_REFID (REF_ID)
1196
) ENGINE=MyISAM CHARSET=cp1251;
1197
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
1198
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
1201
create table t1 (a int, b int);
1202
create table t2 (a int, b int);
1203
insert into t1 values (1,0), (2,0), (3,0);
1204
insert into t2 values (1,1), (2,1), (3,1), (2,2);
1205
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
1207
Error 1242 Subquery returns more than 1 row
1215
`id` mediumint(8) unsigned NOT NULL auto_increment,
1216
`pseudo` varchar(35) NOT NULL default '',
1217
`email` varchar(60) NOT NULL default '',
1219
UNIQUE KEY `email` (`email`),
1220
UNIQUE KEY `pseudo` (`pseudo`)
1221
) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1222
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
1223
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
1227
drop table if exists t1;
1228
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
1231
create table t1 (a int not null, b int, primary key (a));
1232
create table t2 (a int not null, primary key (a));
1233
create table t3 (a int not null, b int, primary key (a));
1234
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1235
insert into t2 values (2), (3), (4), (5);
1236
insert into t3 values (10,3), (20,4), (30,5);
1237
select * from t2 where t2.a in (select a from t1);
1242
explain extended select * from t2 where t2.a in (select a from t1);
1243
id select_type table type possible_keys key key_len ref rows filtered Extra
1244
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1245
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer
1247
Note 1003 select "test"."t2"."a" AS "a" from ("test"."t1") join "test"."t2" where ("test"."t1"."a" = "test"."t2"."a")
1248
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1252
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1253
id select_type table type possible_keys key key_len ref rows filtered Extra
1254
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1255
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
1257
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))
1258
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1262
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1263
id select_type table type possible_keys key key_len ref rows filtered Extra
1264
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1265
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
1266
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
1268
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"))
1269
drop table t1, t2, t3;
1270
create table t1 (a int, b int, index a (a,b));
1271
create table t2 (a int, index a (a));
1272
create table t3 (a int, b int, index a (a));
1273
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1274
insert into t2 values (2), (3), (4), (5);
1275
insert into t3 values (10,3), (20,4), (30,5);
1276
select * from t2 where t2.a in (select a from t1);
1281
explain extended select * from t2 where t2.a in (select a from t1);
1282
id select_type table type possible_keys key key_len ref rows filtered Extra
1283
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
1284
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)
1286
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" semi join ("test"."t1") where ("test"."t1"."a" = "test"."t2"."a")
1287
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1291
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1292
id select_type table type possible_keys key key_len ref rows filtered Extra
1293
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
1294
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
1296
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))
1297
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1301
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1302
id select_type table type possible_keys key key_len ref rows filtered Extra
1303
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index; Start temporary
1304
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)
1305
1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary; Using join buffer
1307
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"))
1308
insert into t1 values (3,31);
1309
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1314
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
1318
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1319
id select_type table type possible_keys key key_len ref rows filtered Extra
1320
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
1321
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
1323
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))
1324
drop table t1, t2, t3;
1325
create table t1 (a int, b int);
1326
create table t2 (a int, b int);
1327
create table t3 (a int, b int);
1328
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
1329
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
1330
insert into t3 values (3,3), (2,2), (1,1);
1331
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;
1332
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)
1336
drop table t1,t2,t3;
1337
create table t1 (s1 int);
1338
create table t2 (s1 int);
1339
insert into t1 values (1);
1340
insert into t2 values (1);
1341
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
1345
create table t1 (s1 int);
1346
create table t2 (s1 int);
1347
insert into t1 values (1);
1348
insert into t2 values (1);
1349
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1350
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1352
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
1353
s2 CHAR(5) COLLATE latin1_swedish_ci);
1354
INSERT INTO t1 VALUES ('z','?');
1355
select * from t1 where s1 > (select max(s2) from t1);
1356
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1357
select * from t1 where s1 > any (select max(s2) from t1);
1358
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1360
create table t1(toid int,rd int);
1361
create table t2(userid int,pmnew int,pmtotal int);
1362
insert into t2 values(1,0,0),(2,0,0);
1363
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);
1364
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);
1365
userid pmtotal pmnew calc_total calc_new
1369
create table t1 (s1 char(5));
1370
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
1371
ERROR 21000: Operand should contain 1 column(s)
1372
insert into t1 values ('tttt');
1373
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
1376
explain extended (select * from t1);
1377
id select_type table type possible_keys key key_len ref rows filtered Extra
1378
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
1380
Note 1003 (select 'tttt' AS "s1" from "test"."t1")
1385
create table t1 (s1 char(5), index s1(s1));
1386
create table t2 (s1 char(5), index s1(s1));
1387
insert into t1 values ('a1'),('a2'),('a3');
1388
insert into t2 values ('a1'),('a2');
1389
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1390
s1 s1 NOT IN (SELECT s1 FROM t2)
1394
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1395
s1 s1 = ANY (SELECT s1 FROM t2)
1399
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1400
s1 s1 <> ALL (SELECT s1 FROM t2)
1404
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1405
s1 s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')
1409
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1410
id select_type table type possible_keys key key_len ref rows filtered Extra
1411
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1412
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1414
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"
1415
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1416
id select_type table type possible_keys key key_len ref rows filtered Extra
1417
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1418
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1420
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"
1421
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1422
id select_type table type possible_keys key key_len ref rows filtered Extra
1423
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1424
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1426
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"
1427
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1428
id select_type table type possible_keys key key_len ref rows filtered Extra
1429
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1430
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
1432
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"
1434
create table t2 (a int, b int);
1435
create table t3 (a int);
1436
insert into t3 values (6),(7),(3);
1437
select * from t3 where a >= all (select b from t2);
1442
explain extended select * from t3 where a >= all (select b from t2);
1443
id select_type table type possible_keys key key_len ref rows filtered Extra
1444
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1445
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1447
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <not>(("test"."t3"."a" < (select max('0') from "test"."t2")))
1448
select * from t3 where a >= some (select b from t2);
1450
explain extended select * from t3 where a >= some (select b from t2);
1451
id select_type table type possible_keys key key_len ref rows filtered Extra
1452
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1453
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1455
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= (select min('0') from "test"."t2")))
1456
select * from t3 where a >= all (select b from t2 group by 1);
1461
explain extended select * from t3 where a >= all (select b from t2 group by 1);
1462
id select_type table type possible_keys key key_len ref rows filtered Extra
1463
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1464
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1466
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)))
1467
select * from t3 where a >= some (select b from t2 group by 1);
1469
explain extended select * from t3 where a >= some (select b from t2 group by 1);
1470
id select_type table type possible_keys key key_len ref rows filtered Extra
1471
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1472
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1474
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)))
1475
select * from t3 where NULL >= any (select b from t2);
1477
explain extended select * from t3 where NULL >= any (select b from t2);
1478
id select_type table type possible_keys key key_len ref rows filtered Extra
1479
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1480
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1482
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1483
select * from t3 where NULL >= any (select b from t2 group by 1);
1485
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
1486
id select_type table type possible_keys key key_len ref rows filtered Extra
1487
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1488
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1490
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1491
select * from t3 where NULL >= some (select b from t2);
1493
explain extended select * from t3 where NULL >= some (select b from t2);
1494
id select_type table type possible_keys key key_len ref rows filtered Extra
1495
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1496
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1498
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1499
select * from t3 where NULL >= some (select b from t2 group by 1);
1501
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
1502
id select_type table type possible_keys key key_len ref rows filtered Extra
1503
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1504
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1506
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1507
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1508
select * from t3 where a > all (select max(b) from t2 group by a);
1512
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
1513
id select_type table type possible_keys key key_len ref rows filtered Extra
1514
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1515
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
1517
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")))
1519
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 ;
1520
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());
1521
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`),FULLTEXT KEY `name` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
1522
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);
1523
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 ;
1524
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);
1525
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1526
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1527
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;
1528
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')
1531
-1 Should Not Return 0
1532
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;
1536
drop table t1,t2,t3,t4;
1537
CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
1538
INSERT INTO t1 VALUES (1),(5);
1539
CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
1540
INSERT INTO t2 VALUES (2),(6);
1541
select * from t1 where (1,2,6) in (select * from t2);
1542
ERROR 21000: Operand should contain 3 column(s)
1544
create table t1 (s1 char);
1545
insert into t1 values ('e');
1546
select * from t1 where 'f' > any (select s1 from t1);
1549
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1552
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1553
id select_type table type possible_keys key key_len ref rows filtered Extra
1554
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1555
2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
1556
3 UNION t1 system NULL NULL NULL NULL 1 100.00
1557
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1559
Note 1003 select 'e' AS "s1" from "test"."t1" where 1
1561
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1562
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
1563
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
1564
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
1565
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;
1572
create table t1 (s1 int);
1573
create table t2 (s1 int);
1574
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
1575
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
1576
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
1577
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1578
select count(*) from t2 group by t1.s2;
1579
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1581
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
1582
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
1583
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
1584
INSERT INTO t2 VALUES (100, 200, 'C');
1585
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
1588
CREATE TABLE t1 (a int(1));
1589
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
1590
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
1599
`id` int(11) NOT NULL auto_increment,
1600
`id_cns` tinyint(3) unsigned NOT NULL default '0',
1601
`tipo` enum('','UNO','DUE') NOT NULL default '',
1602
`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
1603
`particolare` mediumint(8) unsigned NOT NULL default '0',
1604
`generale` mediumint(8) unsigned NOT NULL default '0',
1605
`bis` tinyint(3) unsigned NOT NULL default '0',
1607
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
1608
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
1610
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);
1612
`id` tinyint(3) unsigned NOT NULL auto_increment,
1613
`max_anno_dep` smallint(6) unsigned NOT NULL default '0',
1616
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
1617
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;
1618
id max_anno_dep PIPPO
1623
create table t1 (a int);
1624
insert into t1 values (1), (2), (3);
1625
SET SQL_SELECT_LIMIT=1;
1626
select sum(a) from (select * from t1) as a;
1629
select 2 in (select * from t1);
1630
2 in (select * from t1)
1632
SET SQL_SELECT_LIMIT=default;
1634
CREATE TABLE t1 (a int, b int, INDEX (a));
1635
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1636
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1642
create table t1(val varchar(10));
1643
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1644
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%');
1648
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1649
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');
1650
select * from t1 where id not in (select id from t1 where id < 8);
1657
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);
1664
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1665
id select_type table type possible_keys key key_len ref rows filtered Extra
1666
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
1667
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
1669
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")))))))
1670
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);
1671
id select_type table type possible_keys key key_len ref rows filtered Extra
1672
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
1673
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
1675
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1676
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))))
1677
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1678
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
1679
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');
1680
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);
1681
id text id text id text
1682
1 text1 1 text1 1 text1
1683
2 text2 2 text2 2 text2
1684
3 text3 3 text3 3 text3
1685
4 text4 4 text4 4 text4
1686
5 text5 5 text5 5 text5
1687
6 text6 6 text6 6 text6
1688
7 text7 7 text7 7 text7
1689
8 text8 8 text8 8 text8
1690
9 text9 9 text9 9 text9
1691
10 text10 10 text10 10 text10
1692
11 text11 11 text1 11 text11
1693
12 text12 12 text2 12 text12
1694
1000 text1000 NULL NULL 1000 text1000
1695
1001 text1001 NULL NULL 1000 text1000
1696
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);
1697
id select_type table type possible_keys key key_len ref rows filtered Extra
1698
1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00
1699
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00
1700
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using index condition
1702
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")
1704
create table t1 (a int);
1705
insert into t1 values (1);
1706
explain select benchmark(1000, (select a from t1 where a=rand()));
1707
id select_type table type possible_keys key key_len ref rows Extra
1708
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
1709
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 1
1711
create table t1(id int);
1712
create table t2(id int);
1713
create table t3(flag int);
1714
select (select * from t3 where id not null) from t1, t2;
1715
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
1716
drop table t1,t2,t3;
1717
CREATE TABLE t1 (id INT);
1718
CREATE TABLE t2 (id INT);
1719
INSERT INTO t1 VALUES (1), (2);
1720
INSERT INTO t2 VALUES (1);
1721
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);
1725
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1729
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;
1733
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;
1738
CREATE TABLE t1 ( a int, b int );
1739
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1740
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1743
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1746
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 <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1761
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1764
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1767
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
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1782
ALTER TABLE t1 ADD INDEX (a);
1783
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1786
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1789
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
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 <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1804
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1807
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1810
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
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 <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1825
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1828
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
1831
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 <> ANY (SELECT a FROM t1 HAVING a = 2);
1846
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
1849
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
1852
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 <> ALL (SELECT a FROM t1 HAVING a = 2);
1867
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1870
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1873
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 <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1888
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1891
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1894
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 <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1909
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1912
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1915
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 <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1930
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1933
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1936
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 a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1951
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1952
ERROR 21000: Operand should contain 1 column(s)
1953
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1954
ERROR 21000: Operand should contain 1 column(s)
1955
SELECT a FROM t1 WHERE (1,2) > ANY (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 FROM t1 WHERE b = 2);
1958
ERROR 21000: Operand should contain 1 column(s)
1959
SELECT a FROM t1 WHERE a > 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) > ALL (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) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
1964
ERROR 21000: Operand should contain 1 column(s)
1965
SELECT a FROM t1 WHERE (1,2) <> 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 FROM t1 WHERE b = 2);
1968
ERROR 21000: Operand should contain 2 column(s)
1969
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1970
ERROR 21000: Operand should contain 1 column(s)
1971
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1973
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
1974
ERROR 21000: Operand should contain 2 column(s)
1975
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1976
ERROR 21000: Operand should contain 1 column(s)
1977
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1982
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
1985
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
1989
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
1992
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
1996
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);
1999
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);
2003
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);
2006
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);
2010
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2013
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2016
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 <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2031
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2034
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2037
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 <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2052
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
2055
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
2058
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 <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
2073
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
2076
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
2079
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 a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
2094
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
2095
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
2099
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
2100
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-')
2104
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
2105
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-')
2110
CREATE TABLE t1 ( a double, b double );
2111
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2112
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
2115
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
2118
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 <> ANY (SELECT a FROM t1 WHERE b = 2e0);
2133
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
2136
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
2139
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);
2150
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
2155
CREATE TABLE t1 ( a char(1), b char(1));
2156
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
2157
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
2160
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
2163
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 <> ANY (SELECT a FROM t1 WHERE b = '2');
2178
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
2181
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
2184
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');
2195
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
2200
create table t1 (a int, b int);
2201
insert into t1 values (1,2),(3,4);
2202
select * from t1 up where exists (select * from t1 where t1.a=up.a);
2206
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
2207
id select_type table type possible_keys key key_len ref rows filtered Extra
2208
1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
2209
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2211
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
2212
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"))
2214
CREATE TABLE t1 (t1_a int);
2215
INSERT INTO t1 VALUES (1);
2216
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
2217
INSERT INTO t2 VALUES (1, 1), (1, 2);
2218
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
2219
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
2223
CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL);
2224
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
2225
CREATE TABLE t2 (id int(11) default NULL, pet varchar(10) default NULL);
2226
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
2227
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
2233
CREATE TABLE `t1` ( `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2234
insert into t1 values (1);
2235
CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2236
insert into t2 values (1,2);
2237
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2241
create table t1 (a int not null auto_increment primary key, b varchar(40), fulltext(b));
2242
insert into t1 (b) values ('ball'),('ball games'), ('games'), ('foo'), ('foobar'), ('Serg'), ('Sergei'),('Georg'), ('Patrik'),('Hakan');
2243
create table t2 (a int);
2244
insert into t2 values (1),(3),(2),(7);
2245
select a,b from t1 where match(b) against ('Ball') > 0;
2249
select a from t2 where a in (select a from t1 where match(b) against ('Ball') > 0);
2254
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);
2255
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2256
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2257
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
2258
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
2259
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
2260
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
2264
CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
2265
CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
2266
insert into t1 values (1,1),(1,2),(2,1),(2,2);
2267
insert into t2 values (1,2),(2,2);
2268
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2272
alter table t2 drop primary key;
2273
alter table t2 add key KEY1 (aid, bid);
2274
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2278
alter table t2 drop key KEY1;
2279
alter table t2 add primary key (bid, aid);
2280
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2285
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
2286
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
2287
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
2288
howmanyvalues count(*)
2293
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2294
howmanyvalues mycount
2299
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
2300
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
2301
howmanyvalues mycount
2306
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2307
howmanyvalues mycount
2312
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
2313
howmanyvalues mycount
2319
create table t1 (x int);
2320
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;
2321
(select b.x from t1 as b where b.x=a.x)
2323
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`));
2324
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);
2325
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`)) ;
2326
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');
2327
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;
2328
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2329
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;
2334
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
2335
create table t1 (a int, b int);
2336
create table t2 (a int, b int);
2337
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
2338
insert into t2 values (1,3),(2,1);
2339
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
2340
a b (select max(b) from t2 where t1.b=t2.a)
2347
create table t1 (id int);
2348
create table t2 (id int, body text, fulltext (body));
2349
insert into t1 values(1),(2),(3);
2350
insert into t2 values (1,'test'), (2,'mysql'), (3,'test'), (4,'test');
2351
select count(distinct id) from t1 where id in (select id from t2 where match(body) against ('mysql' in boolean mode));
2355
create table t1 (s1 int,s2 int);
2356
insert into t1 values (20,15);
2357
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
2360
create table t1 (s1 int);
2361
insert into t1 values (1),(null);
2362
select * from t1 where s1 < all (select s1 from t1);
2364
select s1, s1 < all (select s1 from t1) from t1;
2365
s1 s1 < all (select s1 from t1)
2370
Code char(3) NOT NULL default '',
2371
Name char(52) NOT NULL default '',
2372
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
2373
Region char(26) NOT NULL default '',
2374
SurfaceArea float(10,2) NOT NULL default '0.00',
2375
IndepYear smallint(6) default NULL,
2376
Population int(11) NOT NULL default '0',
2377
LifeExpectancy float(3,1) default NULL,
2378
GNP float(10,2) default NULL,
2379
GNPOld float(10,2) default NULL,
2380
LocalName char(45) NOT NULL default '',
2381
GovernmentForm char(45) NOT NULL default '',
2382
HeadOfState char(60) default NULL,
2383
Capital int(11) default NULL,
2384
Code2 char(2) NOT NULL default ''
2386
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2387
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');
2388
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');
2389
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');
2390
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
2391
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
2392
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
2397
create table t1 (a1 int);
2398
create table t2 (b1 int);
2399
select * from t1 where a2 > any(select b1 from t2);
2400
ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
2401
select * from t1 where a1 > any(select b1 from t2);
2404
create table t1 (a integer, b integer);
2405
select (select * from t1) = (select 1,2);
2406
(select * from t1) = (select 1,2)
2408
select (select 1,2) = (select * from t1);
2409
(select 1,2) = (select * from t1)
2411
select row(1,2) = ANY (select * from t1);
2412
row(1,2) = ANY (select * from t1)
2414
select row(1,2) != ALL (select * from t1);
2415
row(1,2) != ALL (select * from t1)
2418
create table t1 (a integer, b integer);
2419
select row(1,(2,2)) in (select * from t1 );
2420
ERROR 21000: Operand should contain 2 column(s)
2421
select row(1,(2,2)) = (select * from t1 );
2422
ERROR 21000: Operand should contain 2 column(s)
2423
select (select * from t1) = row(1,(2,2));
2424
ERROR 21000: Operand should contain 1 column(s)
2426
create table t1 (a integer);
2427
insert into t1 values (1);
2428
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
2429
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2430
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2431
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2432
select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );
2433
xx 1 = ALL ( select 1 from t1 where 1 = xx )
2435
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2436
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2437
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL;
2438
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2441
categoryId int(11) NOT NULL,
2442
courseId int(11) NOT NULL,
2443
startDate datetime NOT NULL,
2444
endDate datetime NOT NULL,
2445
createDate datetime NOT NULL,
2446
modifyDate timestamp NOT NULL,
2447
attributes text NOT NULL
2449
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2450
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2451
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2452
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
2453
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
2454
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2455
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2456
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2457
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
2459
userId int(11) NOT NULL,
2460
courseId int(11) NOT NULL,
2461
date datetime NOT NULL
2463
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
2464
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
2465
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
2466
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
2467
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
2468
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
2470
groupId int(11) NOT NULL,
2471
parentId int(11) NOT NULL,
2472
startDate datetime NOT NULL,
2473
endDate datetime NOT NULL,
2474
createDate datetime NOT NULL,
2475
modifyDate timestamp NOT NULL,
2478
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
2480
id int(11) NOT NULL,
2481
groupTypeId int(11) NOT NULL,
2482
groupKey varchar(50) NOT NULL,
2486
createDate datetime NOT NULL,
2487
modifyDate timestamp NOT NULL
2489
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
2490
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
2492
userId int(11) NOT NULL,
2493
groupId int(11) NOT NULL,
2494
createDate datetime NOT NULL,
2495
modifyDate timestamp NOT NULL
2497
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
2499
count(distinct t2.userid) pass,
2501
count(t2.courseid) crse,
2504
date_format(date, '%b%y') as colhead
2506
join t1 on t2.courseid=t1.courseid
2519
select t4.id as parentid,
2520
t4.name as parentgroup,
2522
t4.name as groupname,
2525
) as gin on t5.groupid=gin.childid
2526
) as groupstuff on t2.userid = groupstuff.userid
2528
groupstuff.groupname, colhead , t2.courseid;
2529
pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead
2530
1 5141 12 group2 12 group2 5 1 5 12 Aug04
2531
1 5141 12 group2 12 group2 5 1 1 41 Aug04
2532
1 5141 12 group2 12 group2 5 1 2 52 Aug04
2533
1 5141 12 group2 12 group2 5 1 2 53 Aug04
2534
1 5141 12 group2 12 group2 5 1 3 51 Oct04
2535
1 5141 12 group2 12 group2 5 1 1 86 Oct04
2536
1 5141 12 group2 12 group2 5 1 1 87 Oct04
2537
1 5141 12 group2 12 group2 5 1 2 88 Oct04
2538
1 5141 12 group2 12 group2 5 1 2 89 Oct04
2539
drop table t1, t2, t3, t4, t5;
2540
create table t1 (a int);
2541
insert into t1 values (1), (2), (3);
2542
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
2548
create table t1 (a int, b int);
2549
insert into t1 values (1,2);
2550
select 1 = (select * from t1);
2551
ERROR 21000: Operand should contain 1 column(s)
2552
select (select * from t1) = 1;
2553
ERROR 21000: Operand should contain 2 column(s)
2554
select (1,2) = (select a from t1);
2555
ERROR 21000: Operand should contain 2 column(s)
2556
select (select a from t1) = (1,2);
2557
ERROR 21000: Operand should contain 1 column(s)
2558
select (1,2,3) = (select * from t1);
2559
ERROR 21000: Operand should contain 3 column(s)
2560
select (select * from t1) = (1,2,3);
2561
ERROR 21000: Operand should contain 2 column(s)
2564
`itemid` bigint(20) unsigned NOT NULL auto_increment,
2565
`sessionid` bigint(20) unsigned default NULL,
2566
`time` int(10) unsigned NOT NULL default '0',
2567
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
2569
`data` text collate latin1_general_ci NOT NULL,
2570
PRIMARY KEY (`itemid`)
2571
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2572
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
2574
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
2575
`pid` int(10) unsigned NOT NULL default '0',
2576
`date` int(10) unsigned NOT NULL default '0',
2577
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
2578
PRIMARY KEY (`sessionid`)
2579
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2580
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
2581
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;
2582
ip count( e.itemid )
2585
create table t1 (fld enum('0','1'));
2586
insert into t1 values ('1');
2587
select * from (select max(fld) from t1) as foo;
2591
CREATE TABLE t1 (one int, two int, flag char(1));
2592
CREATE TABLE t2 (one int, two int, flag char(1));
2593
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2594
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2596
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
2601
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
2605
insert into t2 values (null,null,'N');
2606
insert into t2 values (null,3,'0');
2607
insert into t2 values (null,5,'0');
2608
insert into t2 values (10,null,'0');
2609
insert into t1 values (10,3,'0');
2610
insert into t1 values (10,5,'0');
2611
insert into t1 values (10,10,'0');
2612
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
2622
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2626
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
2636
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2646
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
2656
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2657
id select_type table type possible_keys key key_len ref rows filtered Extra
2658
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2659
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
2661
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"
2662
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2663
id select_type table type possible_keys key key_len ref rows filtered Extra
2664
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
2665
1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
2667
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'))
2668
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;
2669
id select_type table type possible_keys key key_len ref rows filtered Extra
2670
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2671
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
2673
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"
2675
CREATE TABLE t1 (a char(5), b char(5));
2676
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2677
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
2681
CREATE TABLE t1 (a int);
2682
CREATE TABLE t2 (a int, b int);
2683
CREATE TABLE t3 (b int NOT NULL);
2684
INSERT INTO t1 VALUES (1), (2), (3), (4);
2685
INSERT INTO t2 VALUES (1,10), (3,30);
2686
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2687
WHERE t3.b IS NOT NULL OR t2.a > 10;
2690
WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2691
WHERE t3.b IS NOT NULL OR t2.a > 10);
2697
DROP TABLE t1,t2,t3;
2698
CREATE TABLE t1 (f1 INT);
2699
CREATE TABLE t2 (f2 INT);
2700
INSERT INTO t1 VALUES (1);
2701
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
2704
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
2707
INSERT INTO t2 VALUES (1);
2708
INSERT INTO t2 VALUES (2);
2709
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
2713
select 1 from dual where 1 < any (select 2);
2716
select 1 from dual where 1 < all (select 2);
2719
select 1 from dual where 2 > any (select 1);
2722
select 1 from dual where 2 > all (select 1);
2725
select 1 from dual where 1 < any (select 2 from dual);
2728
select 1 from dual where 1 < all (select 2 from dual where 1!=1);
2731
create table t1 (s1 char);
2732
insert into t1 values (1),(2);
2733
select * from t1 where (s1 < any (select s1 from t1));
2736
select * from t1 where not (s1 < any (select s1 from t1));
2739
select * from t1 where (s1 < ALL (select s1+1 from t1));
2742
select * from t1 where not(s1 < ALL (select s1+1 from t1));
2745
select * from t1 where (s1+1 = ANY (select s1 from t1));
2748
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
2751
select * from t1 where (s1 = ALL (select s1/s1 from t1));
2754
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
2759
retailerID varchar(8) NOT NULL,
2760
statusID int(10) unsigned NOT NULL,
2761
changed datetime NOT NULL,
2762
UNIQUE KEY retailerID (retailerID, statusID, changed)
2764
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
2765
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
2766
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
2767
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
2768
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
2769
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
2771
where (r1.retailerID,(r1.changed)) in
2772
(SELECT r2.retailerId,(max(changed)) from t1 r2
2773
group by r2.retailerId);
2774
retailerID statusID changed
2775
0026 2 2006-01-06 12:25:53
2776
0037 2 2006-01-06 12:25:53
2777
0048 1 2006-01-06 12:37:50
2778
0059 1 2006-01-06 12:37:50
2780
create table t1(a int, primary key (a));
2781
insert into t1 values (10);
2782
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
2783
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
2784
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2785
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2786
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2787
id select_type table type possible_keys key key_len ref rows Extra
2788
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2789
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2790
2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
2791
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2792
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2793
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2796
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2797
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2798
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2799
id select_type table type possible_keys key key_len ref rows Extra
2800
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2801
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2802
2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition; Using MRR
2803
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2804
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2805
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2810
field1 int NOT NULL,
2811
field2 int NOT NULL,
2812
field3 int NOT NULL,
2813
PRIMARY KEY (field1,field2,field3)
2816
fieldA int NOT NULL,
2817
fieldB int NOT NULL,
2818
PRIMARY KEY (fieldA,fieldB)
2820
INSERT INTO t1 VALUES
2821
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
2822
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
2823
SELECT field1, field2, COUNT(*)
2824
FROM t1 GROUP BY field1, field2;
2825
field1 field2 COUNT(*)
2829
SELECT field1, field2
2831
GROUP BY field1, field2
2832
HAVING COUNT(*) >= ALL (SELECT fieldB
2833
FROM t2 WHERE fieldA = field1);
2836
SELECT field1, field2
2838
GROUP BY field1, field2
2839
HAVING COUNT(*) < ANY (SELECT fieldB
2840
FROM t2 WHERE fieldA = field1);
2845
CREATE TABLE t1(a int, INDEX (a));
2846
INSERT INTO t1 VALUES (1), (3), (5), (7);
2847
INSERT INTO t1 VALUES (NULL);
2848
CREATE TABLE t2(a int);
2849
INSERT INTO t2 VALUES (1),(2),(3);
2850
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
2851
id select_type table type possible_keys key key_len ref rows Extra
2852
1 PRIMARY t2 ALL NULL NULL NULL NULL 3
2853
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
2854
SELECT a, a IN (SELECT a FROM t1) FROM t2;
2855
a a IN (SELECT a FROM t1)
2860
CREATE TABLE t1 (a DATETIME);
2861
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
2862
CREATE TABLE t2 AS SELECT
2863
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
2864
FROM t1 WHERE a > '2000-01-01';
2865
SHOW CREATE TABLE t2;
2867
t2 CREATE TABLE "t2" (
2869
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2870
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
2871
SHOW CREATE TABLE t3;
2873
t3 CREATE TABLE "t3" (
2875
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2876
DROP TABLE t1,t2,t3;
2877
CREATE TABLE t1 (a int);
2878
INSERT INTO t1 VALUES (1), (2);
2879
SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
2881
SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
2885
EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
2886
id select_type table type possible_keys key key_len ref rows Extra
2887
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
2888
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2890
CREATE TABLE t1 (a int);
2891
INSERT INTO t1 VALUES (2), (4), (1), (3);
2892
CREATE TABLE t2 (b int, c int);
2893
INSERT INTO t2 VALUES
2894
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
2895
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
2901
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
2902
ERROR 21000: Subquery returns more than 1 row
2903
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
2909
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
2910
ERROR 21000: Subquery returns more than 1 row
2911
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
2916
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
2917
ERROR 21000: Subquery returns more than 1 row
2918
SELECT a FROM t1 GROUP BY a
2919
HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
2920
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2926
SELECT a FROM t1 GROUP BY a
2927
HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
2928
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2929
ERROR 21000: Subquery returns more than 1 row
2930
SELECT a FROM t1 GROUP BY a
2931
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
2932
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2935
SELECT a FROM t1 GROUP BY a
2936
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
2937
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
2938
ERROR 21000: Subquery returns more than 1 row
2940
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
2941
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
2948
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
2949
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2950
ERROR 21000: Subquery returns more than 1 row
2952
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
2953
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
2960
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
2961
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2962
ERROR 21000: Subquery returns more than 1 row
2964
create table t1 (df decimal(5,1));
2965
insert into t1 values(1.1);
2966
insert into t1 values(2.2);
2967
select * from t1 where df <= all (select avg(df) from t1 group by df);
2970
select * from t1 where df >= all (select avg(df) from t1 group by df);
2974
create table t1 (df decimal(5,1));
2975
insert into t1 values(1.1);
2976
select 1.1 * exists(select * from t1);
2977
1.1 * exists(select * from t1)
2981
grp int(11) default NULL,
2982
a decimal(10,2) default NULL);
2983
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
2993
select min(a) from t1 group by grp;
3000
CREATE table t1 ( c1 integer );
3001
INSERT INTO t1 VALUES ( 1 );
3002
INSERT INTO t1 VALUES ( 2 );
3003
INSERT INTO t1 VALUES ( 3 );
3004
CREATE TABLE t2 ( c2 integer );
3005
INSERT INTO t2 VALUES ( 1 );
3006
INSERT INTO t2 VALUES ( 4 );
3007
INSERT INTO t2 VALUES ( 5 );
3008
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
3011
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
3012
WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
3016
CREATE TABLE t1 ( c1 integer );
3017
INSERT INTO t1 VALUES ( 1 );
3018
INSERT INTO t1 VALUES ( 2 );
3019
INSERT INTO t1 VALUES ( 3 );
3020
INSERT INTO t1 VALUES ( 6 );
3021
CREATE TABLE t2 ( c2 integer );
3022
INSERT INTO t2 VALUES ( 1 );
3023
INSERT INTO t2 VALUES ( 4 );
3024
INSERT INTO t2 VALUES ( 5 );
3025
INSERT INTO t2 VALUES ( 6 );
3026
CREATE TABLE t3 ( c3 integer );
3027
INSERT INTO t3 VALUES ( 7 );
3028
INSERT INTO t3 VALUES ( 8 );
3029
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
3030
WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
3034
DROP TABLE t1,t2,t3;
3036
`itemid` bigint(20) unsigned NOT NULL auto_increment,
3037
`sessionid` bigint(20) unsigned default NULL,
3038
`time` int(10) unsigned NOT NULL default '0',
3039
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
3041
`data` text collate latin1_general_ci NOT NULL,
3042
PRIMARY KEY (`itemid`)
3043
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
3044
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
3046
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
3047
`pid` int(10) unsigned NOT NULL default '0',
3048
`date` int(10) unsigned NOT NULL default '0',
3049
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
3050
PRIMARY KEY (`sessionid`)
3051
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
3052
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
3053
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;
3054
ip count( e.itemid )
3057
CREATE TABLE t1 (EMPNUM CHAR(3));
3058
CREATE TABLE t2 (EMPNUM CHAR(3) );
3059
INSERT INTO t1 VALUES ('E1'),('E2');
3060
INSERT INTO t2 VALUES ('E1');
3062
WHERE t1.EMPNUM NOT IN
3065
WHERE t1.EMPNUM = t2.EMPNUM);
3070
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
3071
INSERT INTO t1 VALUES (1, 1);
3072
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
3073
PRIMARY KEY(select_id,values_id));
3074
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
3075
SELECT values_id FROM t1
3076
WHERE values_id IN (SELECT values_id FROM t2
3077
WHERE select_id IN (1, 0));
3080
SELECT values_id FROM t1
3081
WHERE values_id IN (SELECT values_id FROM t2
3082
WHERE select_id BETWEEN 0 AND 1);
3085
SELECT values_id FROM t1
3086
WHERE values_id IN (SELECT values_id FROM t2
3087
WHERE select_id = 0 OR select_id = 1);
3091
create table t1 (fld enum('0','1'));
3092
insert into t1 values ('1');
3093
select * from (select max(fld) from t1) as foo;
3097
CREATE TABLE t1 (a int, b int);
3098
CREATE TABLE t2 (c int, d int);
3099
CREATE TABLE t3 (e int);
3100
INSERT INTO t1 VALUES
3101
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
3102
INSERT INTO t2 VALUES
3103
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
3104
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
3105
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
3125
SELECT a FROM t1 GROUP BY a
3126
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
3130
SELECT a FROM t1 GROUP BY a
3131
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
3134
SELECT a FROM t1 GROUP BY a
3135
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
3139
SELECT a FROM t1 GROUP BY a
3140
HAVING a IN (SELECT c FROM t2
3141
WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
3145
SELECT a FROM t1 GROUP BY a
3146
HAVING a IN (SELECT c FROM t2
3147
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
3151
SELECT a FROM t1 GROUP BY a
3152
HAVING a IN (SELECT c FROM t2
3153
WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
3156
SELECT a FROM t1 GROUP BY a
3157
HAVING a IN (SELECT c FROM t2
3158
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
3161
SELECT a FROM t1 GROUP BY a
3162
HAVING a IN (SELECT c FROM t2
3163
WHERE MIN(b) < d AND
3164
EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
3167
SELECT a, SUM(a) FROM t1 GROUP BY a;
3174
WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
3178
SELECT a FROM t1 GROUP BY a
3179
HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
3186
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
3192
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
3199
SELECT t1.a FROM t1 GROUP BY t1.a
3200
HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
3201
HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3202
HAVING SUM(t1.a+t2.c) < t3.e/4));
3206
SELECT t1.a FROM t1 GROUP BY t1.a
3207
HAVING t1.a > ALL(SELECT t2.c FROM t2
3208
WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3209
HAVING SUM(t1.a+t2.c) < t3.e/4));
3212
SELECT t1.a FROM t1 GROUP BY t1.a
3213
HAVING t1.a > ALL(SELECT t2.c FROM t2
3214
WHERE EXISTS(SELECT t3.e FROM t3
3215
WHERE SUM(t1.a+t2.c) < t3.e/4));
3216
ERROR HY000: Invalid use of group function
3217
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
3218
ERROR HY000: Invalid use of group function
3219
SELECT t1.a FROM t1 GROUP BY t1.a
3220
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3221
HAVING AVG(t2.c+SUM(t1.b)) > 20);
3226
SELECT t1.a FROM t1 GROUP BY t1.a
3227
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3228
HAVING AVG(SUM(t1.b)) > 20);
3232
SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
3233
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3234
HAVING t2.c+sum > 20);
3239
DROP TABLE t1,t2,t3;
3240
CREATE TABLE t1 (a varchar(5), b varchar(10));
3241
INSERT INTO t1 VALUES
3242
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
3243
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
3244
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3250
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3251
id select_type table type possible_keys key key_len ref rows Extra
3252
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3253
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3254
ALTER TABLE t1 ADD INDEX(a);
3255
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3261
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3262
id select_type table type possible_keys key key_len ref rows Extra
3263
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3264
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3266
create table t1( f1 int,f2 int);
3267
insert into t1 values (1,1),(2,2);
3268
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';
3273
create table t1 (c int, key(c));
3274
insert into t1 values (1142477582), (1142455969);
3275
create table t2 (a int, b int);
3276
insert into t2 values (2, 1), (1, 0);
3277
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
3279
create table t1 (i int, j bigint);
3280
insert into t1 values (1, 2), (2, 2), (3, 2);
3281
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
3285
CREATE TABLE t1 (i BIGINT UNSIGNED);
3286
INSERT INTO t1 VALUES (10000000000000000000);
3287
INSERT INTO t1 VALUES (1);
3288
CREATE TABLE t2 (i BIGINT UNSIGNED);
3289
INSERT INTO t2 VALUES (10000000000000000000);
3290
INSERT INTO t2 VALUES (1);
3292
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
3294
10000000000000000000
3297
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
3299
10000000000000000000
3300
/* subquery test with cast*/
3301
SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);
3303
10000000000000000000
3307
id bigint(20) unsigned NOT NULL auto_increment,
3308
name varchar(255) NOT NULL,
3311
INSERT INTO t1 VALUES
3312
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
3314
id bigint(20) unsigned NOT NULL auto_increment,
3315
mid bigint(20) unsigned NOT NULL,
3319
INSERT INTO t2 VALUES
3320
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
3321
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
3323
(SELECT date FROM t2 WHERE mid = t1.id
3324
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3325
(SELECT date FROM t2 WHERE mid = t1.id
3326
ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
3328
id name date_last date_next_to_last
3329
1 Balazs 2006-05-01 NULL
3330
2 Joe 2006-04-20 NULL
3331
3 Frank 2006-04-13 NULL
3333
(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
3334
ORDER BY date DESC LIMIT 1, 1) AS date_count
3341
(SELECT date FROM t2 WHERE mid = t1.id
3342
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3343
(SELECT date FROM t2 WHERE mid = t1.id
3344
ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
3346
id name date_last date_next_to_last
3347
1 Balazs 2006-05-01 2006-03-30
3348
2 Joe 2006-04-20 2006-04-06
3349
3 Frank 2006-04-13 NULL
3352
i1 int(11) NOT NULL default '0',
3353
i2 int(11) NOT NULL default '0',
3354
t datetime NOT NULL default '0000-00-00 00:00:00',
3355
PRIMARY KEY (i1,i2,t)
3357
INSERT INTO t1 VALUES
3358
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
3359
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
3360
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
3361
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
3362
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
3363
(24,2,'2005-05-27 12:40:06');
3365
i1 int(11) NOT NULL default '0',
3366
i2 int(11) NOT NULL default '0',
3367
t datetime default NULL,
3370
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
3373
WHERE t1.t = (SELECT t1.t FROM t1
3374
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3375
ORDER BY t1.t DESC LIMIT 1);
3376
id select_type table type possible_keys key key_len ref rows Extra
3377
1 PRIMARY t2 system NULL NULL NULL NULL 1
3378
1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
3379
2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
3381
WHERE t1.t = (SELECT t1.t FROM t1
3382
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3383
ORDER BY t1.t DESC LIMIT 1);
3385
24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40
3387
CREATE TABLE t1 (i INT);
3388
(SELECT i FROM t1) UNION (SELECT i FROM t1);
3390
SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS
3392
(SELECT i FROM t1) UNION
3397
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3398
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
3399
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3401
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))
3403
explain select * from t1 where not exists
3404
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3405
id select_type table type possible_keys key key_len ref rows Extra
3406
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
3407
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3408
3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3409
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
3411
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
3412
insert into t1 (a) values (FLOOR(rand() * 100));
3413
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3414
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3415
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3416
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3417
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3418
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3419
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3420
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3421
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3422
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3423
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3424
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3425
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3427
(SELECT REPEAT(' ',250) FROM t1 i1
3428
WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a
3429
FROM t1 ORDER BY a LIMIT 5;
3437
CREATE TABLE t1 (a INT, b INT);
3438
CREATE TABLE t2 (a INT);
3439
INSERT INTO t2 values (1);
3440
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
3441
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
3442
(SELECT COUNT(DISTINCT t1.b) from t2)
3446
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3447
FROM t1 GROUP BY t1.a;
3448
(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3452
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
3453
COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))
3457
SELECT COUNT(DISTINCT t1.b),
3458
(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
3459
FROM t1 GROUP BY t1.a;
3460
COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
3466
SELECT COUNT(DISTINCT t1.b)
3469
FROM t1 GROUP BY t1.a;
3472
SELECT COUNT(DISTINCT t1.b)
3481
SELECT COUNT(DISTINCT t1.b)
3484
FROM t1 GROUP BY t1.a LIMIT 1)
3490
SELECT COUNT(DISTINCT t1.b)
3493
FROM t1 GROUP BY t1.a LIMIT 1)
3498
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
3499
CREATE TABLE t2 (x int auto_increment, y int, z int,
3500
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
3501
SET SESSION sort_buffer_size = 32 * 1024;
3503
Warning 1292 Truncated incorrect sort_buffer_size value: '32768'
3504
SELECT SQL_NO_CACHE COUNT(*)
3505
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3509
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
3510
SELECT SQL_NO_CACHE COUNT(*)
3511
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3516
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
3517
CREATE TABLE t2 (c int);
3518
INSERT INTO t1 VALUES ('aa', 1);
3519
INSERT INTO t2 VALUES (1);
3521
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3523
SELECT c from t2 WHERE c=t1.c);
3526
INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
3528
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3530
SELECT c from t2 WHERE c=t1.c);
3536
INSERT INTO t2 VALUES (2);
3537
CREATE TABLE t3 (c int);
3538
INSERT INTO t3 VALUES (1);
3540
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
3542
SELECT c from t2 WHERE c=t1.c);
3548
DROP TABLE t1,t2,t3;
3549
DROP TABLE IF EXISTS t1;
3550
DROP TABLE IF EXISTS t2;
3551
DROP TABLE IF EXISTS t1xt2;
3553
id_1 int(5) NOT NULL,
3554
t varchar(4) DEFAULT NULL
3557
id_2 int(5) NOT NULL,
3558
t varchar(4) DEFAULT NULL
3560
CREATE TABLE t1xt2 (
3561
id_1 int(5) NOT NULL,
3562
id_2 int(5) NOT NULL
3564
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
3565
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
3566
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
3567
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3568
(12 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 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3573
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3574
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3576
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3577
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3583
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3584
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
3590
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3591
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
3597
insert INTO t1xt2 VALUES (1, 12);
3598
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3599
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3602
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3603
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3606
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3607
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3610
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3611
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3616
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3617
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3622
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3623
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3628
insert INTO t1xt2 VALUES (2, 12);
3629
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3630
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3634
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3635
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3639
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3640
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3644
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3645
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3649
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3650
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3654
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3655
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3662
CREATE TABLE t1 (a int);
3663
INSERT INTO t1 VALUES (3), (1), (2);
3664
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
3669
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
3675
CREATE TABLE t1 (a int, b int);
3676
CREATE TABLE t2 (m int, n int);
3677
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3678
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3680
(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3682
COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3687
(SELECT MIN(m) FROM t2 WHERE m = count(*))
3689
COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))
3695
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
3700
CREATE TABLE t1 (a int, b int);
3701
CREATE TABLE t2 (m int, n int);
3702
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3703
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3704
SELECT COUNT(*) c, a,
3705
(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3707
c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3711
SELECT COUNT(*) c, a,
3712
(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3714
c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3719
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
3720
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
3721
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
3722
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
3724
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
3731
(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
3738
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
3739
FROM t1 WHERE t1.d=0 GROUP BY a;
3745
(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3746
LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3766
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3768
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3769
FROM t1 as tt GROUP BY tt.a;
3775
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3777
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
3778
FROM t1 as tt GROUP BY tt.a;
3784
CREATE TABLE t1 (a INT);
3785
INSERT INTO t1 values (1),(1),(1),(1);
3786
CREATE TABLE t2 (x INT);
3787
INSERT INTO t1 values (1000),(1001),(1002);
3788
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
3789
ERROR HY000: Invalid use of group function
3790
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
3791
ERROR HY000: Invalid use of group function
3792
SELECT COUNT(1) FROM DUAL;
3795
SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;
3796
ERROR HY000: Invalid use of group function
3798
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
3800
ERROR HY000: Invalid use of group function
3802
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
3804
ERROR HY000: Invalid use of group function
3806
CREATE TABLE t1 (a int, b int, KEY (a));
3807
INSERT INTO t1 VALUES (1,1),(2,1);
3808
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
3809
id select_type table type possible_keys key key_len ref rows Extra
3810
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
3811
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3813
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
3814
INSERT INTO t1 VALUES
3815
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
3816
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
3817
INSERT INTO t2 VALUES (7), (5), (1), (3);
3818
SELECT id, st FROM t1
3819
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3824
SELECT id, st FROM t1
3825
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3831
SELECT id, st FROM t1
3832
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3836
SELECT id, st FROM t1
3837
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3843
CREATE TABLE t1 (a int);
3844
INSERT INTO t1 VALUES (1), (2);
3846
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
3847
id select_type table type possible_keys key key_len ref rows filtered Extra
3848
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
3849
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
3851
Note 1003 select "res"."count(*)" AS "count(*)" from (select count(0) AS "count(*)" from "test"."t1" group by "test"."t1"."a") "res"
3854
a varchar(255) default NULL,
3855
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3859
a varchar(255) default NULL
3861
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
3862
INSERT INTO t1 SELECT * FROM t1;
3863
INSERT INTO t1 SELECT * FROM t1;
3864
INSERT INTO t1 SELECT * FROM t1;
3865
INSERT INTO t1 SELECT * FROM t1;
3866
INSERT INTO t1 SELECT * FROM t1;
3867
INSERT INTO t1 SELECT * FROM t1;
3868
INSERT INTO t1 SELECT * FROM t1;
3869
INSERT INTO t1 SELECT * FROM t1;
3870
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
3871
INSERT INTO `t2` VALUES ('abcdefghijk');
3872
INSERT INTO `t2` VALUES ('asdf');
3873
SET session sort_buffer_size=8192;
3875
Warning 1292 Truncated incorrect sort_buffer_size value: '8192'
3876
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
3881
CREATE TABLE t1 (a INTEGER, b INTEGER);
3882
CREATE TABLE t2 (x INTEGER);
3883
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3884
INSERT INTO t2 VALUES (1), (2);
3885
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
3886
ERROR 21000: Subquery returns more than 1 row
3887
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
3888
ERROR 21000: Subquery returns more than 1 row
3889
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
3890
(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
3893
CREATE TABLE t1 (a INT, b INT);
3894
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
3895
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
3896
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
3901
CREATE TABLE t1 (a INT);
3902
CREATE TABLE t2 (a INT);
3903
INSERT INTO t1 VALUES (1),(2);
3904
INSERT INTO t2 VALUES (1),(2);
3905
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
3906
(SELECT SUM(t1.a) FROM t2 WHERE a=0)
3908
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
3909
ERROR 21000: Subquery returns more than 1 row
3910
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
3911
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
3914
CREATE TABLE t1 (a1 INT, a2 INT);
3915
CREATE TABLE t2 (b1 INT, b2 INT);
3916
INSERT INTO t1 VALUES (100, 200);
3917
INSERT INTO t1 VALUES (101, 201);
3918
INSERT INTO t2 VALUES (101, 201);
3919
INSERT INTO t2 VALUES (103, 203);
3920
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
3921
((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL
3925
CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
3926
INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
3927
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3929
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3931
CREATE INDEX I1 ON t1 (s1);
3932
CREATE INDEX I2 ON t1 (s2);
3933
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3935
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3938
INSERT INTO t1 VALUES (0x41,0x41);
3939
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
3942
CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
3943
CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
3944
CREATE TABLE t3 (a3 BINARY(2) default '0');
3945
INSERT INTO t1 VALUES (1),(2),(3),(4);
3946
INSERT INTO t2 VALUES (1),(2),(3);
3947
INSERT INTO t3 VALUES (1),(2),(3);
3948
SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
3953
SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
3954
a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)
3959
DROP TABLE t1,t2,t3;
3960
CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
3961
CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
3962
CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
3963
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
3964
INSERT INTO t2 VALUES (2), (3), (4), (5);
3965
INSERT INTO t3 VALUES (10), (20), (30);
3966
SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
3971
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
3973
DROP TABLE t1, t2, t3;
3974
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
3975
INSERT INTO t1 VALUES ('a', 'aa');
3976
INSERT INTO t1 VALUES ('a', 'aaa');
3977
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3979
CREATE INDEX I1 ON t1 (a);
3980
CREATE INDEX I2 ON t1 (b);
3981
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3982
id select_type table type possible_keys key key_len ref rows Extra
3983
1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
3984
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
3985
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3987
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
3988
INSERT INTO t2 SELECT * FROM t1;
3989
CREATE INDEX I1 ON t2 (a);
3990
CREATE INDEX I2 ON t2 (b);
3991
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3992
id select_type table type possible_keys key key_len ref rows Extra
3993
1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
3994
1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
3995
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3998
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3999
id select_type table type possible_keys key key_len ref rows Extra
4000
1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
4001
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
4002
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4005
CREATE TABLE t1(a INT, b INT);
4006
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
4008
SELECT a AS out_a, MIN(b) FROM t1
4009
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
4011
ERROR 42S22: Unknown column 'out_a' in 'where clause'
4012
SELECT a AS out_a, MIN(b) FROM t1
4013
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
4015
ERROR 42S22: Unknown column 'out_a' in 'where clause'
4017
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
4018
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4020
id select_type table type possible_keys key key_len ref rows Extra
4021
1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
4022
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
4023
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
4024
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4030
CREATE TABLE t1 (a INT);
4031
CREATE TABLE t2 (a INT);
4032
INSERT INTO t1 VALUES (1),(2);
4033
INSERT INTO t2 VALUES (1),(2);
4034
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
4039
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
4040
id select_type table type possible_keys key key_len ref rows filtered Extra
4041
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
4042
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
4044
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
4045
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"))
4047
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
4048
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
4049
id select_type table type possible_keys key key_len ref rows filtered Extra
4050
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
4051
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
4052
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where
4053
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
4055
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
4056
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
4057
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")))
4060
f7 varchar(32) collate utf8_bin NOT NULL default '',
4061
f10 varchar(32) collate utf8_bin default NULL,
4064
INSERT INTO t4 VALUES(1,1), (2,null);
4066
f4 varchar(32) collate utf8_bin NOT NULL default '',
4067
f2 varchar(50) collate utf8_bin default NULL,
4068
f3 varchar(10) collate utf8_bin default NULL,
4072
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
4074
f8 varchar(32) collate utf8_bin NOT NULL default '',
4075
f1 varchar(10) collate utf8_bin default NULL,
4076
f9 varchar(32) collate utf8_bin default NULL,
4079
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
4081
f6 varchar(32) collate utf8_bin NOT NULL default '',
4082
f5 varchar(50) collate utf8_bin default NULL,
4085
INSERT INTO t3 VALUES (1,null), (2,null);
4087
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
4088
IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
4092
FROM t2 VPC, t4 a2, t2 a3
4094
VPC.f4 = a2.f10 AND a3.f2 = a4
4101
t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
4106
DROP TABLE t1, t2, t3, t4;
4108
create table t_out (subcase char(3),
4109
a1 char(2), b1 char(2), c1 char(2));
4110
create table t_in (a2 char(2), b2 char(2), c2 char(2));
4111
insert into t_out values ('A.1','2a', NULL, '2a');
4112
insert into t_out values ('A.3', '2a', NULL, '2a');
4113
insert into t_out values ('A.4', '2a', NULL, 'xx');
4114
insert into t_out values ('B.1', '2a', '2a', '2a');
4115
insert into t_out values ('B.2', '2a', '2a', '2a');
4116
insert into t_out values ('B.3', '3a', 'xx', '3a');
4117
insert into t_out values ('B.4', 'xx', '3a', '3a');
4118
insert into t_in values ('1a', '1a', '1a');
4119
insert into t_in values ('2a', '2a', '2a');
4120
insert into t_in values (NULL, '2a', '2a');
4121
insert into t_in values ('3a', NULL, '3a');
4123
Test general IN semantics (not top-level)
4127
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
4128
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
4129
from t_out where subcase = 'A.1';
4130
subcase pred_in pred_not_in
4132
case A.2 - impossible
4135
(a1, b1, c1) IN (select * from t_in) pred_in,
4136
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4137
from t_out where subcase = 'A.3';
4138
subcase pred_in pred_not_in
4142
(a1, b1, c1) IN (select * from t_in) pred_in,
4143
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4144
from t_out where subcase = 'A.4';
4145
subcase pred_in pred_not_in
4149
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
4150
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
4151
from t_out where subcase = 'B.1';
4152
subcase pred_in pred_not_in
4156
(a1, b1, c1) IN (select * from t_in) pred_in,
4157
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4158
from t_out where subcase = 'B.2';
4159
subcase pred_in pred_not_in
4163
(a1, b1, c1) IN (select * from t_in) pred_in,
4164
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4165
from t_out where subcase = 'B.3';
4166
subcase pred_in pred_not_in
4170
(a1, b1, c1) IN (select * from t_in) pred_in,
4171
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4172
from t_out where subcase = 'B.4';
4173
subcase pred_in pred_not_in
4176
Test IN as top-level predicate, and
4177
as non-top level for cases A.3, B.3 (the only cases with NULL result).
4180
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4181
where subcase = 'A.1' and
4182
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
4185
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4186
where subcase = 'A.1' and
4187
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
4190
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4191
where subcase = 'A.1' and
4192
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
4196
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4197
where subcase = 'A.3' and
4198
(a1, b1, c1) IN (select * from t_in);
4201
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4202
where subcase = 'A.3' and
4203
(a1, b1, c1) NOT IN (select * from t_in);
4206
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4207
where subcase = 'A.3' and
4208
NOT((a1, b1, c1) IN (select * from t_in));
4211
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4212
where subcase = 'A.3' and
4213
((a1, b1, c1) IN (select * from t_in)) is NULL and
4214
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
4218
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4219
where subcase = 'A.4' and
4220
(a1, b1, c1) IN (select * from t_in);
4223
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4224
where subcase = 'A.4' and
4225
(a1, b1, c1) NOT IN (select * from t_in);
4228
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4229
where subcase = 'A.4' and
4230
NOT((a1, b1, c1) IN (select * from t_in));
4234
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4235
where subcase = 'B.1' and
4236
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
4239
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4240
where subcase = 'B.1' and
4241
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
4244
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4245
where subcase = 'B.1' and
4246
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
4250
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4251
where subcase = 'B.2' and
4252
(a1, b1, c1) IN (select * from t_in);
4255
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4256
where subcase = 'B.2' and
4257
(a1, b1, c1) NOT IN (select * from t_in);
4260
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4261
where subcase = 'B.2' and
4262
NOT((a1, b1, c1) IN (select * from t_in));
4266
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4267
where subcase = 'B.3' and
4268
(a1, b1, c1) IN (select * from t_in);
4271
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4272
where subcase = 'B.3' and
4273
(a1, b1, c1) NOT IN (select * from t_in);
4276
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4277
where subcase = 'B.3' and
4278
NOT((a1, b1, c1) IN (select * from t_in));
4281
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4282
where subcase = 'B.3' and
4283
((a1, b1, c1) IN (select * from t_in)) is NULL and
4284
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
4288
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4289
where subcase = 'B.4' and
4290
(a1, b1, c1) IN (select * from t_in);
4293
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4294
where subcase = 'B.4' and
4295
(a1, b1, c1) NOT IN (select * from t_in);
4298
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4299
where subcase = 'B.4' and
4300
NOT((a1, b1, c1) IN (select * from t_in));
4305
CREATE TABLE t1 (s1 char(1));
4306
INSERT INTO t1 VALUES ('a');
4307
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
4311
CREATE TABLE t1( a INT );
4312
INSERT INTO t1 VALUES (1),(2);
4313
CREATE TABLE t2( a INT, b INT );
4315
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
4316
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
4318
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
4319
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
4321
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
4322
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
4326
SELECT a INTO @var FROM t1 WHERE a = 2
4328
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
4333
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
4335
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
4340
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
4342
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
4344
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
4348
SELECT a FROM t1 WHERE a = 2
4350
SELECT a FROM t1 WHERE a = 2
4357
SELECT a FROM t1 WHERE a = 2
4359
SELECT a FROM t1 WHERE a = 2
4364
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
4365
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
4366
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
4369
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
4372
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
4373
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
4374
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
4375
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
4376
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4377
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
4378
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
4379
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
4380
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4381
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
4382
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
4383
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
4384
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
4385
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
4386
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
4387
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
4388
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
4389
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
4390
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
4393
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;
4396
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
4399
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
4402
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
4406
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
4407
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
4408
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
4409
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
4410
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
4411
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
4412
SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;
4413
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
4414
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
4415
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
4416
SELECT * FROM t1 JOIN (t1 t1a) ON 1;
4422
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
4428
SELECT * FROM (t1 t1a);
4432
SELECT * FROM ((t1 t1a));
4436
SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;
4440
SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;
4444
SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;
4448
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
4452
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
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 't1a2' at line 1
4454
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
4457
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );
4460
SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );
4463
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
4464
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
4465
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4466
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
4467
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4468
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
4469
SELECT * FROM t1 WHERE a = ( SELECT 1 );
4472
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );
4475
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
4476
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
4477
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
4478
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
4479
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
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 DUMPFILE 'file' )' at line 1
4481
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
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 @a)' at line 1
4483
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
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 OUTFILE 'file' )' at line 1
4485
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
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 DUMPFILE 'file' )' at line 1
4487
SELECT ( SELECT 1 INTO @v );
4488
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
4489
SELECT ( SELECT 1 INTO OUTFILE 'file' );
4490
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
4491
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
4492
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
4493
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
4494
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
4495
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4496
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
4497
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4498
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
4499
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4500
( SELECT a FROM t1 WHERE a = 1 ) a
4503
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
4504
( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a
4507
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
4509
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
4510
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
4511
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4512
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
4513
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4514
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
4515
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4516
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
4517
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4518
( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
4520
SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
4521
((SELECT 1 UNION SELECT 1 UNION SELECT 1))
4523
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4524
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
4525
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4526
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
4527
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
4530
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4531
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
4532
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4533
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
4534
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4535
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
4536
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4537
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
4538
SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4539
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
4540
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4541
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
4542
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4543
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
4544
SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4545
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
4546
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4549
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4552
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4555
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4558
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
4559
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
4560
SELECT EXISTS(SELECT 1+1);
4563
SELECT EXISTS(SELECT 1+1 INTO @test);
4564
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
4565
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
4566
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
4567
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
4568
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
4569
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
4570
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
4572
set optimizer_switch='';
4573
show variables like 'optimizer_switch';
added
removed
mysql-test/r/subselect_no_mat.result
