1
show variables like 'optimizer_switch';
4
set optimizer_switch='no_materialization,no_semijoin';
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 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
80
ERROR HY000: Incorrect usage of PROCEDURE and subquery
81
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
82
ERROR HY000: Incorrect parameters to procedure 'ANALYSE'
83
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
84
ERROR 42S22: Unknown column 'a' in 'field list'
85
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
86
ERROR 42S22: Unknown column 'a' in 'field list'
87
SELECT (SELECT 1,2,3) = ROW(1,2,3);
88
(SELECT 1,2,3) = ROW(1,2,3)
90
SELECT (SELECT 1,2,3) = ROW(1,2,1);
91
(SELECT 1,2,3) = ROW(1,2,1)
93
SELECT (SELECT 1,2,3) < ROW(1,2,1);
94
(SELECT 1,2,3) < ROW(1,2,1)
96
SELECT (SELECT 1,2,3) > ROW(1,2,1);
97
(SELECT 1,2,3) > ROW(1,2,1)
99
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
100
(SELECT 1,2,3) = ROW(1,2,NULL)
102
SELECT ROW(1,2,3) = (SELECT 1,2,3);
103
ROW(1,2,3) = (SELECT 1,2,3)
105
SELECT ROW(1,2,3) = (SELECT 1,2,1);
106
ROW(1,2,3) = (SELECT 1,2,1)
108
SELECT ROW(1,2,3) < (SELECT 1,2,1);
109
ROW(1,2,3) < (SELECT 1,2,1)
111
SELECT ROW(1,2,3) > (SELECT 1,2,1);
112
ROW(1,2,3) > (SELECT 1,2,1)
114
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
115
ROW(1,2,3) = (SELECT 1,2,NULL)
117
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
118
(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
120
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
121
(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
123
SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
124
(SELECT 1.5,2,'a') = ROW('1.5b',2,'b')
127
Warning 1292 Truncated incorrect DOUBLE value: '1.5b'
128
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
129
(SELECT 'b',2,'a') = ROW(1.5,2,'a')
131
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
132
(SELECT 1.5,2,'a') = ROW(1.5,'2','a')
134
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
135
(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
137
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
138
ERROR 21000: Operand should contain 1 column(s)
139
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
142
create table t1 (a int);
143
create table t2 (a int, b int);
144
create table t3 (a int);
145
create table t4 (a int not null, b int not null);
146
insert into t1 values (2);
147
insert into t2 values (1,7),(2,7);
148
insert into t4 values (4,8),(3,8),(5,9);
149
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
150
ERROR 42S22: Reference 'a1' not supported (forward reference in item list)
151
select (select a from t1 where t1.a=t2.a), a from t2;
152
(select a from t1 where t1.a=t2.a) a
155
select (select a from t1 where t1.a=t2.b), a from t2;
156
(select a from t1 where t1.a=t2.b) a
159
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
160
(select a from t1) a (select 1 union select 2 limit 1)
163
select (select a from t3), a from t2;
167
select * from t2 where t2.a=(select a from t1);
170
insert into t3 values (6),(7),(3);
171
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
175
(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;
180
(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);
186
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);
187
id select_type table type possible_keys key key_len ref rows filtered Extra
188
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
189
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using filesort
190
3 UNION t4 ALL NULL NULL NULL NULL 3 100.00 Using where
191
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
192
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
194
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")
195
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
196
(select a from t3 where a<t2.a*4 order by 1 desc limit 1) a
199
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
(select t3.a from t3 where a<8 order by 1 desc limit 1) a
203
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
204
(select * from t2 where a>1) as tt;
205
id select_type table type possible_keys key key_len ref rows filtered Extra
206
1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00
207
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
208
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
210
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"
211
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);
214
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);
217
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);
219
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
220
b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
224
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
225
id select_type table type possible_keys key key_len ref rows filtered Extra
226
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00
227
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
228
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
230
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
231
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"
232
select * from t3 where exists (select * from t2 where t2.b=t3.a);
235
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
239
select * from t3 where a in (select b from t2);
242
select * from t3 where a not in (select b from t2);
246
select * from t3 where a = some (select b from t2);
249
select * from t3 where a <> any (select b from t2);
253
select * from t3 where a = all (select b from t2);
256
select * from t3 where a <> all (select b from t2);
260
insert into t2 values (100, 5);
261
select * from t3 where a < any (select b from t2);
265
select * from t3 where a < all (select b from t2);
268
select * from t3 where a >= any (select b from t2);
272
explain extended select * from t3 where a >= any (select b from t2);
273
id select_type table type possible_keys key key_len ref rows filtered Extra
274
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
275
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
277
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= (select min("test"."t2"."b") from "test"."t2")))
278
select * from t3 where a >= all (select b from t2);
281
delete from t2 where a=100;
282
select * from t3 where a in (select a,b from t2);
283
ERROR 21000: Operand should contain 1 column(s)
284
select * from t3 where a in (select * from t2);
285
ERROR 21000: Operand should contain 1 column(s)
286
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
287
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
289
insert into t2 values (2,10);
290
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
293
delete from t2 where a=2 and b=10;
294
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
297
create table t5 (a int);
298
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
299
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
302
insert into t5 values (5);
303
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
304
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
307
insert into t5 values (2);
308
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
309
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
312
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;
313
id select_type table type possible_keys key key_len ref rows filtered Extra
314
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
315
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
316
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where
317
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
319
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
320
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
321
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"
322
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
323
ERROR 21000: Subquery returns more than 1 row
324
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
325
create table t7( uq int primary key, name char(25));
326
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
327
insert into t6 values (1,1),(1,2),(2,2),(1,3);
328
select * from t6 where exists (select * from t7 where uq = clinic_uq);
333
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
334
id select_type table type possible_keys key key_len ref rows filtered Extra
335
1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
336
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
338
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
339
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"))
340
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
341
ERROR 23000: Column 'a' in field list is ambiguous
343
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
344
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
345
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
346
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
347
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
348
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
349
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
352
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
355
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
359
`pseudo` varchar(35) character set latin1 NOT NULL default '',
360
`email` varchar(60) character set latin1 NOT NULL default '',
361
PRIMARY KEY (`pseudo`),
362
UNIQUE KEY `email` (`email`)
363
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
364
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
365
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
366
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
367
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');
368
id select_type table type possible_keys key key_len ref rows filtered Extra
369
1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index
370
4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
371
2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00
372
3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
374
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
375
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
376
t8 WHERE pseudo='joce');
377
ERROR 21000: Operand should contain 1 column(s)
378
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
380
ERROR 21000: Operand should contain 1 column(s)
381
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
384
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
385
ERROR 21000: Subquery returns more than 1 row
386
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
388
`topic` mediumint(8) unsigned NOT NULL default '0',
389
`date` date NOT NULL default '0000-00-00',
390
`pseudo` varchar(35) character set latin1 NOT NULL default '',
391
PRIMARY KEY (`pseudo`,`date`,`topic`),
392
KEY `topic` (`topic`)
393
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
394
INSERT INTO t1 (topic,date,pseudo) VALUES
395
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
396
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
397
id select_type table type possible_keys key key_len ref rows filtered Extra
398
1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
400
Note 1003 select distinct "test"."t1"."date" AS "date" from "test"."t1" where ("test"."t1"."date" = '2002-08-03')
401
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
402
id select_type table type possible_keys key key_len ref rows filtered Extra
403
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
404
2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
406
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')"
407
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
410
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
411
(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
413
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
418
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
419
ERROR 21000: Subquery returns more than 1 row
420
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
421
id select_type table type possible_keys key key_len ref rows filtered Extra
422
1 PRIMARY t1 index NULL topic 3 NULL 2 100.00 Using index
423
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
424
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
425
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
427
Note 1003 select 1 AS "1" from "test"."t1" where 1
430
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
431
`maxnumrep` int(10) unsigned NOT NULL default '0',
432
PRIMARY KEY (`numeropost`),
433
UNIQUE KEY `maxnumrep` (`maxnumrep`)
434
) ENGINE=MyISAM ROW_FORMAT=FIXED;
435
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
437
`mot` varchar(30) NOT NULL default '',
438
`topic` mediumint(8) unsigned NOT NULL default '0',
439
`date` date NOT NULL default '0000-00-00',
440
`pseudo` varchar(35) NOT NULL default '',
441
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
442
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
443
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
444
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
447
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;
451
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
452
ERROR 42S22: Unknown column 'a' in 'having clause'
453
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
454
ERROR 42S22: Unknown column 'a' in 'having clause'
455
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
456
mot topic date pseudo
457
joce 40143 2002-10-22 joce
458
joce 43506 2002-10-22 joce
459
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
460
mot topic date pseudo
461
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
462
mot topic date pseudo
463
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
464
mot topic date pseudo
465
joce 40143 2002-10-22 joce
466
joce 43506 2002-10-22 joce
467
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
468
mot topic date pseudo
469
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
470
mot topic date pseudo
471
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
472
mot topic date pseudo
473
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
474
mot topic date pseudo
475
joce 40143 2002-10-22 joce
476
joce 43506 2002-10-22 joce
477
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
478
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100)
479
joce 40143 2002-10-22 joce 1
480
joce 43506 2002-10-22 joce 1
481
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
482
mot topic date pseudo
483
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
484
mot topic date pseudo
485
joce 40143 2002-10-22 joce
486
joce 43506 2002-10-22 joce
487
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
488
mot topic date pseudo
489
joce 40143 2002-10-22 joce
490
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
491
mot topic date pseudo
492
joce 40143 2002-10-22 joce
493
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
494
mot topic date pseudo
495
joce 40143 2002-10-22 joce
496
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
497
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000)
498
joce 40143 2002-10-22 joce 1
499
joce 43506 2002-10-22 joce 0
502
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
503
`maxnumrep` int(10) unsigned NOT NULL default '0',
504
PRIMARY KEY (`numeropost`),
505
UNIQUE KEY `maxnumrep` (`maxnumrep`)
506
) ENGINE=MyISAM ROW_FORMAT=FIXED;
507
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
508
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
509
ERROR 21000: Subquery returns more than 1 row
510
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
511
ERROR 21000: Subquery returns more than 1 row
513
create table t1 (a int);
514
insert into t1 values (1),(2),(3);
515
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
521
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
522
INSERT INTO t1 VALUES ();
523
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
524
ERROR 21000: Subquery returns more than 1 row
527
`numeropost` mediumint(8) unsigned NOT NULL default '0',
528
`numreponse` int(10) unsigned NOT NULL auto_increment,
529
`pseudo` varchar(35) NOT NULL default '',
530
PRIMARY KEY (`numeropost`,`numreponse`),
531
UNIQUE KEY `numreponse` (`numreponse`),
532
KEY `pseudo` (`pseudo`,`numeropost`)
534
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
535
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
536
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
537
ERROR 42S22: Unknown column 'a' in 'having clause'
538
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
539
numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)
540
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
541
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
542
ERROR 21000: Subquery returns more than 1 row
543
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
544
id select_type table type possible_keys key key_len ref rows filtered Extra
545
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
547
Note 1003 select max("test"."t1"."numreponse") AS "MAX(numreponse)" from "test"."t1" where ("test"."t1"."numeropost" = '1')
548
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
549
id select_type table type possible_keys key key_len ref rows filtered Extra
550
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
551
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
553
Note 1003 select '3' AS "numreponse" from "test"."t1" where (('1' = '1'))
555
CREATE TABLE t1 (a int(1));
556
INSERT INTO t1 VALUES (1);
557
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
561
create table t1 (a int NOT NULL, b int, primary key (a));
562
create table t2 (a int NOT NULL, b int, primary key (a));
563
insert into t1 values (0, 10),(1, 11),(2, 12);
564
insert into t2 values (1, 21),(2, 22),(3, 23);
570
update t1 set b= (select b from t1);
571
ERROR HY000: You can't specify target table 't1' for update in FROM clause
572
update t1 set b= (select b from t2);
573
ERROR 21000: Subquery returns more than 1 row
574
update t1 set b= (select b from t2 where t1.a = t2.a);
581
create table t1 (a int NOT NULL, b int, primary key (a));
582
create table t2 (a int NOT NULL, b int, primary key (a));
583
insert into t1 values (0, 10),(1, 11),(2, 12);
584
insert into t2 values (1, 21),(2, 12),(3, 23);
590
select * from t1 where b = (select b from t2 where t1.a = t2.a);
593
delete from t1 where b = (select b from t1);
594
ERROR HY000: You can't specify target table 't1' for update in FROM clause
595
delete from t1 where b = (select b from t2);
596
ERROR 21000: Subquery returns more than 1 row
597
delete from t1 where b = (select b from t2 where t1.a = t2.a);
603
create table t11 (a int NOT NULL, b int, primary key (a));
604
create table t12 (a int NOT NULL, b int, primary key (a));
605
create table t2 (a int NOT NULL, b int, primary key (a));
606
insert into t11 values (0, 10),(1, 11),(2, 12);
607
insert into t12 values (33, 10),(22, 11),(2, 12);
608
insert into t2 values (1, 21),(2, 12),(3, 23);
619
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
620
ERROR HY000: You can't specify target table 't12' for update in FROM clause
621
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
622
ERROR 21000: Subquery returns more than 1 row
623
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
632
drop table t11, t12, t2;
633
CREATE TABLE t1 (x int);
634
create table t2 (a int);
635
create table t3 (b int);
636
insert into t2 values (1);
637
insert into t3 values (1),(2);
638
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
639
ERROR HY000: You can't specify target table 't1' for update in FROM clause
640
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
641
ERROR 21000: Subquery returns more than 1 row
642
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
646
insert into t2 values (1);
647
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
652
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
659
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
668
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
669
ERROR 42S22: Unknown column 'x' in 'field list'
670
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
680
drop table t1, t2, t3;
681
CREATE TABLE t1 (x int not null, y int, primary key (x));
682
create table t2 (a int);
683
create table t3 (a int);
684
insert into t2 values (1);
685
insert into t3 values (1),(2);
688
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
689
ERROR HY000: You can't specify target table 't1' for update in FROM clause
690
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
691
ERROR 21000: Subquery returns more than 1 row
692
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
696
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
700
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
705
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
710
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
716
drop table t1, t2, t3;
717
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
718
ERROR HY000: No tables used
719
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
720
INSERT INTO t2 VALUES (1),(2);
721
SELECT * FROM t2 WHERE id IN (SELECT 1);
724
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
725
id select_type table type possible_keys key key_len ref rows filtered Extra
726
1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
728
Note 1249 Select 2 was reduced during optimization
729
Note 1003 select "test"."t2"."id" AS "id" from "test"."t2" where ("test"."t2"."id" = 1)
730
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
733
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
736
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
737
id select_type table type possible_keys key key_len ref rows filtered Extra
738
1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
740
Note 1249 Select 3 was reduced during optimization
741
Note 1249 Select 2 was reduced during optimization
742
Note 1003 select "test"."t2"."id" AS "id" from "test"."t2" where ("test"."t2"."id" = (1 + 1))
743
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
744
id select_type table type possible_keys key key_len ref rows filtered Extra
745
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
746
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
747
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
748
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
750
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))))
751
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
753
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
756
INSERT INTO t2 VALUES ((SELECT * FROM t2));
757
ERROR HY000: You can't specify target table 't2' for update in FROM clause
758
INSERT INTO t2 VALUES ((SELECT id FROM t2));
759
ERROR HY000: You can't specify target table 't2' for update in FROM clause
764
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
765
INSERT INTO t1 values (1),(1);
766
UPDATE t2 SET id=(SELECT * FROM t1);
767
ERROR 21000: Subquery returns more than 1 row
769
create table t1 (a int);
770
insert into t1 values (1),(2),(3);
771
select 1 IN (SELECT * from t1);
772
1 IN (SELECT * from t1)
774
select 10 IN (SELECT * from t1);
775
10 IN (SELECT * from t1)
777
select NULL IN (SELECT * from t1);
778
NULL IN (SELECT * from t1)
780
update t1 set a=NULL where a=2;
781
select 1 IN (SELECT * from t1);
782
1 IN (SELECT * from t1)
784
select 3 IN (SELECT * from t1);
785
3 IN (SELECT * from t1)
787
select 10 IN (SELECT * from t1);
788
10 IN (SELECT * from t1)
790
select 1 > ALL (SELECT * from t1);
791
1 > ALL (SELECT * from t1)
793
select 10 > ALL (SELECT * from t1);
794
10 > ALL (SELECT * from t1)
796
select 1 > ANY (SELECT * from t1);
797
1 > ANY (SELECT * from t1)
799
select 10 > ANY (SELECT * from t1);
800
10 > ANY (SELECT * from t1)
803
create table t1 (a varchar(20));
804
insert into t1 values ('A'),('BC'),('DEF');
805
select 'A' IN (SELECT * from t1);
806
'A' IN (SELECT * from t1)
808
select 'XYZS' IN (SELECT * from t1);
809
'XYZS' IN (SELECT * from t1)
811
select NULL IN (SELECT * from t1);
812
NULL IN (SELECT * from t1)
814
update t1 set a=NULL where a='BC';
815
select 'A' IN (SELECT * from t1);
816
'A' IN (SELECT * from t1)
818
select 'DEF' IN (SELECT * from t1);
819
'DEF' IN (SELECT * from t1)
821
select 'XYZS' IN (SELECT * from t1);
822
'XYZS' IN (SELECT * from t1)
824
select 'A' > ALL (SELECT * from t1);
825
'A' > ALL (SELECT * from t1)
827
select 'XYZS' > ALL (SELECT * from t1);
828
'XYZS' > ALL (SELECT * from t1)
830
select 'A' > ANY (SELECT * from t1);
831
'A' > ANY (SELECT * from t1)
833
select 'XYZS' > ANY (SELECT * from t1);
834
'XYZS' > ANY (SELECT * from t1)
837
create table t1 (a float);
838
insert into t1 values (1.5),(2.5),(3.5);
839
select 1.5 IN (SELECT * from t1);
840
1.5 IN (SELECT * from t1)
842
select 10.5 IN (SELECT * from t1);
843
10.5 IN (SELECT * from t1)
845
select NULL IN (SELECT * from t1);
846
NULL IN (SELECT * from t1)
848
update t1 set a=NULL where a=2.5;
849
select 1.5 IN (SELECT * from t1);
850
1.5 IN (SELECT * from t1)
852
select 3.5 IN (SELECT * from t1);
853
3.5 IN (SELECT * from t1)
855
select 10.5 IN (SELECT * from t1);
856
10.5 IN (SELECT * from t1)
858
select 1.5 > ALL (SELECT * from t1);
859
1.5 > ALL (SELECT * from t1)
861
select 10.5 > ALL (SELECT * from t1);
862
10.5 > ALL (SELECT * from t1)
864
select 1.5 > ANY (SELECT * from t1);
865
1.5 > ANY (SELECT * from t1)
867
select 10.5 > ANY (SELECT * from t1);
868
10.5 > ANY (SELECT * from t1)
870
explain extended select (select a+1) from t1;
871
id select_type table type possible_keys key key_len ref rows filtered Extra
872
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
874
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
875
Note 1249 Select 2 was reduced during optimization
876
Note 1003 select ("test"."t1"."a" + 1) AS "(select a+1)" from "test"."t1"
877
select (select a+1) from t1;
883
CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a));
884
CREATE TABLE t2 (a int(11) default '0', INDEX (a));
885
INSERT INTO t1 VALUES (1),(2),(3),(4);
886
INSERT INTO t2 VALUES (1),(2),(3);
887
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
888
a t1.a in (select t2.a from t2)
893
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
894
id select_type table type possible_keys key key_len ref rows filtered Extra
895
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
896
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
898
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"
899
CREATE TABLE t3 (a int(11) default '0');
900
INSERT INTO t3 VALUES (1),(2),(3);
901
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
902
a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
907
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
908
id select_type table type possible_keys key key_len ref rows filtered Extra
909
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
910
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
911
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
913
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"
915
create table t1 (a float);
916
select 10.5 IN (SELECT * from t1 LIMIT 1);
917
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
918
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
919
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
921
create table t1 (a int, b int, c varchar(10));
922
create table t2 (a int);
923
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
924
insert into t2 values (1),(2),(NULL);
925
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;
926
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a)
930
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;
931
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a)
935
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;
936
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a)
941
create table t1 (a int, b real, c varchar(10));
942
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
943
select ROW(1, 1, 'a') IN (select a,b,c from t1);
944
ROW(1, 1, 'a') IN (select a,b,c from t1)
946
select ROW(1, 2, 'a') IN (select a,b,c from t1);
947
ROW(1, 2, 'a') IN (select a,b,c from t1)
949
select ROW(1, 1, 'a') IN (select b,a,c from t1);
950
ROW(1, 1, 'a') IN (select b,a,c from t1)
952
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
953
ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null)
955
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
956
ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null)
958
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
959
ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null)
961
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
962
ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
964
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
965
ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
967
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
968
ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
970
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
971
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
973
CREATE TABLE t1 (a int(1));
974
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
975
id select_type table type possible_keys key key_len ref rows filtered Extra
976
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
977
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
979
Note 1003 select (select rand() AS "RAND()" from "test"."t1") AS "(SELECT RAND() FROM t1)" from "test"."t1"
980
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
981
id select_type table type possible_keys key key_len ref rows filtered Extra
982
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
983
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
985
Note 1003 select (select encrypt('test') AS "ENCRYPT('test')" from "test"."t1") AS "(SELECT ENCRYPT('test') FROM t1)" from "test"."t1"
986
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
987
id select_type table type possible_keys key key_len ref rows filtered Extra
988
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
989
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
991
Note 1003 select (select benchmark(1,1) AS "BENCHMARK(1,1)" from "test"."t1") AS "(SELECT BENCHMARK(1,1) FROM t1)" from "test"."t1"
994
`mot` varchar(30) character set latin1 NOT NULL default '',
995
`topic` mediumint(8) unsigned NOT NULL default '0',
996
`date` date NOT NULL default '0000-00-00',
997
`pseudo` varchar(35) character set latin1 NOT NULL default '',
998
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
999
KEY `pseudo` (`pseudo`,`date`,`topic`),
1000
KEY `topic` (`topic`)
1001
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
1003
`mot` varchar(30) character set latin1 NOT NULL default '',
1004
`topic` mediumint(8) unsigned NOT NULL default '0',
1005
`date` date NOT NULL default '0000-00-00',
1006
`pseudo` varchar(35) character set latin1 NOT NULL default '',
1007
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
1008
KEY `pseudo` (`pseudo`,`date`,`topic`),
1009
KEY `topic` (`topic`)
1010
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
1012
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
1013
`maxnumrep` int(10) unsigned NOT NULL default '0',
1014
PRIMARY KEY (`numeropost`),
1015
UNIQUE KEY `maxnumrep` (`maxnumrep`)
1016
) ENGINE=MyISAM CHARSET=latin1;
1017
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1018
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1019
INSERT INTO t3 VALUES (1,1);
1020
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
1025
mot topic date pseudo
1026
joce 1 0000-00-00 joce
1027
test 2 0000-00-00 test
1028
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
1029
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
1031
mot topic date pseudo
1032
joce 1 0000-00-00 joce
1033
drop table t1, t2, t3;
1034
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1037
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
1038
SHOW CREATE TABLE t1;
1040
t1 CREATE TABLE "t1" (
1041
"a" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP,
1042
"(SELECT 1)" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP
1043
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1045
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1046
SHOW CREATE TABLE t1;
1048
t1 CREATE TABLE "t1" (
1049
"a" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP,
1050
"(SELECT a)" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP
1051
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1053
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
1054
SHOW CREATE TABLE t1;
1056
t1 CREATE TABLE "t1" (
1057
"a" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP,
1058
"(SELECT a+0)" int(3) NOT NULL ON UPDATE CURRENT_TIMESTAMP
1059
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1061
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1065
SHOW CREATE TABLE t1;
1067
t1 CREATE TABLE "t1" (
1068
"a" bigint(20) NOT NULL ON UPDATE CURRENT_TIMESTAMP
1069
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1071
create table t1 (a int);
1072
insert into t1 values (1), (2), (3);
1073
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
1075
id select_type table type possible_keys key key_len ref rows filtered Extra
1076
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1077
2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1078
3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1080
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"
1082
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);
1083
ERROR 42S02: Table 'test.t1' doesn't exist
1085
ID int(11) NOT NULL auto_increment,
1086
name char(35) NOT NULL default '',
1087
t2 char(3) NOT NULL default '',
1088
District char(20) NOT NULL default '',
1089
Population int(11) NOT NULL default '0',
1092
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
1093
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
1094
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
1096
Code char(3) NOT NULL default '',
1097
Name char(52) NOT NULL default '',
1098
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1099
Region char(26) NOT NULL default '',
1100
SurfaceArea float(10,2) NOT NULL default '0.00',
1101
IndepYear smallint(6) default NULL,
1102
Population int(11) NOT NULL default '0',
1103
LifeExpectancy float(3,1) default NULL,
1104
GNP float(10,2) default NULL,
1105
GNPOld float(10,2) default NULL,
1106
LocalName char(45) NOT NULL default '',
1107
GovernmentForm char(45) NOT NULL default '',
1108
HeadOfState char(60) default NULL,
1109
Capital int(11) default NULL,
1110
Code2 char(2) NOT NULL default '',
1113
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');
1114
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');
1115
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);
1116
Continent Name Population
1117
Oceania Sydney 3276207
1120
`id` mediumint(8) unsigned NOT NULL auto_increment,
1121
`pseudo` varchar(35) character set latin1 NOT NULL default '',
1123
UNIQUE KEY `pseudo` (`pseudo`)
1124
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1125
INSERT INTO t1 (pseudo) VALUES ('test');
1126
SELECT 0 IN (SELECT 1 FROM t1 a);
1127
0 IN (SELECT 1 FROM t1 a)
1129
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1130
id select_type table type possible_keys key key_len ref rows filtered Extra
1131
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1132
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1134
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)"
1135
INSERT INTO t1 (pseudo) VALUES ('test1');
1136
SELECT 0 IN (SELECT 1 FROM t1 a);
1137
0 IN (SELECT 1 FROM t1 a)
1139
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1140
id select_type table type possible_keys key key_len ref rows filtered Extra
1141
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1142
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1144
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)"
1147
`i` int(11) NOT NULL default '0',
1149
) ENGINE=MyISAM CHARSET=latin1;
1150
INSERT INTO t1 VALUES (1);
1151
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
1152
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1153
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1154
ERROR 42S22: Unknown column 't.i' in 'field list'
1160
id int(11) default NULL
1161
) ENGINE=MyISAM CHARSET=latin1;
1162
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
1164
id int(11) default NULL,
1165
name varchar(15) default NULL
1166
) ENGINE=MyISAM CHARSET=latin1;
1167
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
1168
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
1176
create table t1 (a int, unique index indexa (a));
1177
insert into t1 values (-1), (-4), (-2), (NULL);
1178
select -10 IN (select a from t1 FORCE INDEX (indexa));
1179
-10 IN (select a from t1 FORCE INDEX (indexa))
1182
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
1183
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
1184
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
1185
id select_type table type possible_keys key key_len ref rows filtered Extra
1186
1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition
1187
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1189
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"))
1192
ID int(10) unsigned NOT NULL auto_increment,
1193
SUB_ID int(3) unsigned NOT NULL default '0',
1194
REF_ID int(10) unsigned default NULL,
1195
REF_SUB int(3) unsigned default '0',
1196
PRIMARY KEY (ID,SUB_ID),
1197
UNIQUE KEY t1_PK (ID,SUB_ID),
1198
KEY t1_FK (REF_ID,REF_SUB),
1199
KEY t1_REFID (REF_ID)
1200
) ENGINE=MyISAM CHARSET=cp1251;
1201
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
1202
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
1205
create table t1 (a int, b int);
1206
create table t2 (a int, b int);
1207
insert into t1 values (1,0), (2,0), (3,0);
1208
insert into t2 values (1,1), (2,1), (3,1), (2,2);
1209
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
1211
Error 1242 Subquery returns more than 1 row
1219
`id` mediumint(8) unsigned NOT NULL auto_increment,
1220
`pseudo` varchar(35) NOT NULL default '',
1221
`email` varchar(60) NOT NULL default '',
1223
UNIQUE KEY `email` (`email`),
1224
UNIQUE KEY `pseudo` (`pseudo`)
1225
) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1226
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
1227
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
1231
drop table if exists t1;
1232
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
1235
create table t1 (a int not null, b int, primary key (a));
1236
create table t2 (a int not null, primary key (a));
1237
create table t3 (a int not null, b int, primary key (a));
1238
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1239
insert into t2 values (2), (3), (4), (5);
1240
insert into t3 values (10,3), (20,4), (30,5);
1241
select * from t2 where t2.a in (select a from t1);
1246
explain extended select * from t2 where t2.a in (select a from t1);
1247
id select_type table type possible_keys key key_len ref rows filtered Extra
1248
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1249
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
1251
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<primary_index_lookup>(<cache>("test"."t2"."a") in t1 on PRIMARY)))
1252
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1256
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1257
id select_type table type possible_keys key key_len ref rows filtered Extra
1258
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1259
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where
1261
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<primary_index_lookup>(<cache>("test"."t2"."a") in t1 on PRIMARY where (("test"."t1"."b" <> 30) and (<cache>("test"."t2"."a") = "test"."t1"."a")))))
1262
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1266
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1267
id select_type table type possible_keys key key_len ref rows filtered Extra
1268
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1269
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00
1270
2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
1272
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(select 1 AS "Not_used" from "test"."t1" join "test"."t3" where (("test"."t3"."a" = "test"."t1"."b") and (<cache>("test"."t2"."a") = "test"."t1"."a"))))
1273
drop table t1, t2, t3;
1274
create table t1 (a int, b int, index a (a,b));
1275
create table t2 (a int, index a (a));
1276
create table t3 (a int, b int, index a (a));
1277
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1278
insert into t2 values (2), (3), (4), (5);
1279
insert into t3 values (10,3), (20,4), (30,5);
1280
select * from t2 where t2.a in (select a from t1);
1285
explain extended select * from t2 where t2.a in (select a from t1);
1286
id select_type table type possible_keys key key_len ref rows filtered Extra
1287
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1288
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index
1290
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<index_lookup>(<cache>("test"."t2"."a") in t1 on a)))
1291
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1295
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1296
id select_type table type possible_keys key key_len ref rows filtered Extra
1297
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1298
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
1300
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<index_lookup>(<cache>("test"."t2"."a") in t1 on a where (("test"."t1"."b" <> 30) and (<cache>("test"."t2"."a") = "test"."t1"."a")))))
1301
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1305
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1306
id select_type table type possible_keys key key_len ref rows filtered Extra
1307
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1308
2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using index
1309
2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer
1311
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(select 1 AS "Not_used" from "test"."t1" join "test"."t3" where (("test"."t3"."a" = "test"."t1"."b") and (<cache>("test"."t2"."a") = "test"."t1"."a"))))
1312
insert into t1 values (3,31);
1313
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1318
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
1322
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1323
id select_type table type possible_keys key key_len ref rows filtered Extra
1324
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1325
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
1327
Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<index_lookup>(<cache>("test"."t2"."a") in t1 on a where (("test"."t1"."b" <> 30) and (<cache>("test"."t2"."a") = "test"."t1"."a")))))
1328
drop table t1, t2, t3;
1329
create table t1 (a int, b int);
1330
create table t2 (a int, b int);
1331
create table t3 (a int, b int);
1332
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
1333
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
1334
insert into t3 values (3,3), (2,2), (1,1);
1335
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;
1336
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)
1340
drop table t1,t2,t3;
1341
create table t1 (s1 int);
1342
create table t2 (s1 int);
1343
insert into t1 values (1);
1344
insert into t2 values (1);
1345
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
1349
create table t1 (s1 int);
1350
create table t2 (s1 int);
1351
insert into t1 values (1);
1352
insert into t2 values (1);
1353
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1354
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1356
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
1357
s2 CHAR(5) COLLATE latin1_swedish_ci);
1358
INSERT INTO t1 VALUES ('z','?');
1359
select * from t1 where s1 > (select max(s2) from t1);
1360
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1361
select * from t1 where s1 > any (select max(s2) from t1);
1362
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1364
create table t1(toid int,rd int);
1365
create table t2(userid int,pmnew int,pmtotal int);
1366
insert into t2 values(1,0,0),(2,0,0);
1367
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);
1368
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);
1369
userid pmtotal pmnew calc_total calc_new
1373
create table t1 (s1 char(5));
1374
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
1375
ERROR 21000: Operand should contain 1 column(s)
1376
insert into t1 values ('tttt');
1377
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
1380
explain extended (select * from t1);
1381
id select_type table type possible_keys key key_len ref rows filtered Extra
1382
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
1384
Note 1003 (select 'tttt' AS "s1" from "test"."t1")
1389
create table t1 (s1 char(5), index s1(s1));
1390
create table t2 (s1 char(5), index s1(s1));
1391
insert into t1 values ('a1'),('a2'),('a3');
1392
insert into t2 values ('a1'),('a2');
1393
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1394
s1 s1 NOT IN (SELECT s1 FROM t2)
1398
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1399
s1 s1 = ANY (SELECT s1 FROM t2)
1403
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1404
s1 s1 <> ALL (SELECT s1 FROM t2)
1408
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1409
s1 s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')
1413
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1414
id select_type table type possible_keys key key_len ref rows filtered Extra
1415
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1416
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1418
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"
1419
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1420
id select_type table type possible_keys key key_len ref rows filtered Extra
1421
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1422
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1424
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"
1425
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1426
id select_type table type possible_keys key key_len ref rows filtered Extra
1427
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1428
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1430
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"
1431
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1432
id select_type table type possible_keys key key_len ref rows filtered Extra
1433
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1434
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
1436
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"
1438
create table t2 (a int, b int);
1439
create table t3 (a int);
1440
insert into t3 values (6),(7),(3);
1441
select * from t3 where a >= all (select b from t2);
1446
explain extended select * from t3 where a >= all (select b from t2);
1447
id select_type table type possible_keys key key_len ref rows filtered Extra
1448
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1449
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1451
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <not>(("test"."t3"."a" < (select max('0') from "test"."t2")))
1452
select * from t3 where a >= some (select b from t2);
1454
explain extended select * from t3 where a >= some (select b from t2);
1455
id select_type table type possible_keys key key_len ref rows filtered Extra
1456
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1457
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1459
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= (select min('0') from "test"."t2")))
1460
select * from t3 where a >= all (select b from t2 group by 1);
1465
explain extended select * from t3 where a >= all (select b from t2 group by 1);
1466
id select_type table type possible_keys key key_len ref rows filtered Extra
1467
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1468
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1470
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <not>(("test"."t3"."a" < <max>(select '0' AS "b" from "test"."t2" group by 1)))
1471
select * from t3 where a >= some (select b from t2 group by 1);
1473
explain extended select * from t3 where a >= some (select b from t2 group by 1);
1474
id select_type table type possible_keys key key_len ref rows filtered Extra
1475
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1476
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1478
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= <min>(select '0' AS "b" from "test"."t2" group by 1)))
1479
select * from t3 where NULL >= any (select b from t2);
1481
explain extended select * from t3 where NULL >= any (select b from t2);
1482
id select_type table type possible_keys key key_len ref rows filtered Extra
1483
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1484
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1486
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1487
select * from t3 where NULL >= any (select b from t2 group by 1);
1489
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
1490
id select_type table type possible_keys key key_len ref rows filtered Extra
1491
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1492
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1494
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1495
select * from t3 where NULL >= some (select b from t2);
1497
explain extended select * from t3 where NULL >= some (select b from t2);
1498
id select_type table type possible_keys key key_len ref rows filtered Extra
1499
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1500
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1502
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1503
select * from t3 where NULL >= some (select b from t2 group by 1);
1505
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
1506
id select_type table type possible_keys key key_len ref rows filtered Extra
1507
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1508
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1510
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1511
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1512
select * from t3 where a > all (select max(b) from t2 group by a);
1516
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
1517
id select_type table type possible_keys key key_len ref rows filtered Extra
1518
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1519
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
1521
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")))
1523
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 ;
1524
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());
1525
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;
1526
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);
1527
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 ;
1528
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);
1529
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1530
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1531
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;
1532
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')
1535
-1 Should Not Return 0
1536
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;
1540
drop table t1,t2,t3,t4;
1541
CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
1542
INSERT INTO t1 VALUES (1),(5);
1543
CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
1544
INSERT INTO t2 VALUES (2),(6);
1545
select * from t1 where (1,2,6) in (select * from t2);
1546
ERROR 21000: Operand should contain 3 column(s)
1548
create table t1 (s1 char);
1549
insert into t1 values ('e');
1550
select * from t1 where 'f' > any (select s1 from t1);
1553
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1556
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1557
id select_type table type possible_keys key key_len ref rows filtered Extra
1558
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1559
2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
1560
3 UNION t1 system NULL NULL NULL NULL 1 100.00
1561
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1563
Note 1003 select 'e' AS "s1" from "test"."t1" where 1
1565
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1566
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
1567
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
1568
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
1569
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;
1576
create table t1 (s1 int);
1577
create table t2 (s1 int);
1578
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
1579
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
1580
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
1581
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1582
select count(*) from t2 group by t1.s2;
1583
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1585
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
1586
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
1587
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
1588
INSERT INTO t2 VALUES (100, 200, 'C');
1589
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
1592
CREATE TABLE t1 (a int(1));
1593
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
1594
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
1603
`id` int(11) NOT NULL auto_increment,
1604
`id_cns` tinyint(3) unsigned NOT NULL default '0',
1605
`tipo` enum('','UNO','DUE') NOT NULL default '',
1606
`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
1607
`particolare` mediumint(8) unsigned NOT NULL default '0',
1608
`generale` mediumint(8) unsigned NOT NULL default '0',
1609
`bis` tinyint(3) unsigned NOT NULL default '0',
1611
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
1612
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
1614
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);
1616
`id` tinyint(3) unsigned NOT NULL auto_increment,
1617
`max_anno_dep` smallint(6) unsigned NOT NULL default '0',
1620
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
1621
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;
1622
id max_anno_dep PIPPO
1627
create table t1 (a int);
1628
insert into t1 values (1), (2), (3);
1629
SET SQL_SELECT_LIMIT=1;
1630
select sum(a) from (select * from t1) as a;
1633
select 2 in (select * from t1);
1634
2 in (select * from t1)
1636
SET SQL_SELECT_LIMIT=default;
1638
CREATE TABLE t1 (a int, b int, INDEX (a));
1639
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1640
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1646
create table t1(val varchar(10));
1647
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1648
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%');
1652
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1653
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');
1654
select * from t1 where id not in (select id from t1 where id < 8);
1661
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);
1668
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1669
id select_type table type possible_keys key key_len ref rows filtered Extra
1670
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
1671
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
1673
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")))))))
1674
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);
1675
id select_type table type possible_keys key key_len ref rows filtered Extra
1676
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
1677
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
1679
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1680
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))))
1681
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1682
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
1683
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');
1684
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);
1685
id text id text id text
1686
1 text1 1 text1 1 text1
1687
2 text2 2 text2 2 text2
1688
3 text3 3 text3 3 text3
1689
4 text4 4 text4 4 text4
1690
5 text5 5 text5 5 text5
1691
6 text6 6 text6 6 text6
1692
7 text7 7 text7 7 text7
1693
8 text8 8 text8 8 text8
1694
9 text9 9 text9 9 text9
1695
10 text10 10 text10 10 text10
1696
11 text11 11 text1 11 text11
1697
12 text12 12 text2 12 text12
1698
1000 text1000 NULL NULL 1000 text1000
1699
1001 text1001 NULL NULL 1000 text1000
1700
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);
1701
id select_type table type possible_keys key key_len ref rows filtered Extra
1702
1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00
1703
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00
1704
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using index condition
1706
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")
1708
create table t1 (a int);
1709
insert into t1 values (1);
1710
explain select benchmark(1000, (select a from t1 where a=sha(rand())));
1711
id select_type table type possible_keys key key_len ref rows Extra
1712
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
1713
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 1
1715
create table t1(id int);
1716
create table t2(id int);
1717
create table t3(flag int);
1718
select (select * from t3 where id not null) from t1, t2;
1719
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
1720
drop table t1,t2,t3;
1721
CREATE TABLE t1 (id INT);
1722
CREATE TABLE t2 (id INT);
1723
INSERT INTO t1 VALUES (1), (2);
1724
INSERT INTO t2 VALUES (1);
1725
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1729
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1733
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;
1737
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;
1742
CREATE TABLE t1 ( a int, b int );
1743
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1744
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1747
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1750
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 <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1765
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1768
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1771
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
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1786
ALTER TABLE t1 ADD INDEX (a);
1787
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1790
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1793
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
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 <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1808
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1811
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1814
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
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 <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1829
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1832
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
1835
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 <> ANY (SELECT a FROM t1 HAVING a = 2);
1850
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
1853
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
1856
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 <> ALL (SELECT a FROM t1 HAVING a = 2);
1871
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1874
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1877
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 <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1892
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1895
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1898
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 <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1913
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1916
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1919
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 <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1934
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1937
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1940
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 a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1955
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1956
ERROR 21000: Operand should contain 1 column(s)
1957
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1958
ERROR 21000: Operand should contain 1 column(s)
1959
SELECT a FROM t1 WHERE (1,2) > ANY (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 FROM t1 WHERE b = 2);
1962
ERROR 21000: Operand should contain 1 column(s)
1963
SELECT a FROM t1 WHERE a > 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) > ALL (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) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
1968
ERROR 21000: Operand should contain 1 column(s)
1969
SELECT a FROM t1 WHERE (1,2) <> 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 FROM t1 WHERE b = 2);
1972
ERROR 21000: Operand should contain 2 column(s)
1973
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1974
ERROR 21000: Operand should contain 1 column(s)
1975
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1977
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
1978
ERROR 21000: Operand should contain 2 column(s)
1979
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1980
ERROR 21000: Operand should contain 1 column(s)
1981
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1986
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
1989
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
1993
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
1996
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
2000
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);
2003
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);
2007
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);
2010
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);
2014
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2017
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2020
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 <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2035
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2038
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2041
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 <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
2056
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
2059
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
2062
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 <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
2077
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
2080
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
2083
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 a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
2098
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
2099
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
2103
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
2104
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-')
2108
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
2109
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-')
2114
CREATE TABLE t1 ( a double, b double );
2115
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2116
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
2119
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
2122
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 <> ANY (SELECT a FROM t1 WHERE b = 2e0);
2137
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
2140
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
2143
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);
2154
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
2159
CREATE TABLE t1 ( a char(1), b char(1));
2160
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
2161
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
2164
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
2167
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 <> ANY (SELECT a FROM t1 WHERE b = '2');
2182
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
2185
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
2188
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');
2199
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
2204
create table t1 (a int, b int);
2205
insert into t1 values (1,2),(3,4);
2206
select * from t1 up where exists (select * from t1 where t1.a=up.a);
2210
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
2211
id select_type table type possible_keys key key_len ref rows filtered Extra
2212
1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
2213
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2215
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
2216
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"))
2218
CREATE TABLE t1 (t1_a int);
2219
INSERT INTO t1 VALUES (1);
2220
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
2221
INSERT INTO t2 VALUES (1, 1), (1, 2);
2222
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
2223
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
2227
CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL);
2228
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
2229
CREATE TABLE t2 (id int(11) default NULL, pet varchar(10) default NULL);
2230
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
2231
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
2237
CREATE TABLE `t1` ( `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2238
insert into t1 values (1);
2239
CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2240
insert into t2 values (1,2);
2241
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2245
create table t1 (a int not null auto_increment primary key, b varchar(40), fulltext(b));
2246
insert into t1 (b) values ('ball'),('ball games'), ('games'), ('foo'), ('foobar'), ('Serg'), ('Sergei'),('Georg'), ('Patrik'),('Hakan');
2247
create table t2 (a int);
2248
insert into t2 values (1),(3),(2),(7);
2249
select a,b from t1 where match(b) against ('Ball') > 0;
2253
select a from t2 where a in (select a from t1 where match(b) against ('Ball') > 0);
2258
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);
2259
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2260
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2261
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
2262
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
2263
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
2264
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
2268
CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
2269
CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
2270
insert into t1 values (1,1),(1,2),(2,1),(2,2);
2271
insert into t2 values (1,2),(2,2);
2272
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2276
alter table t2 drop primary key;
2277
alter table t2 add key KEY1 (aid, bid);
2278
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2282
alter table t2 drop key KEY1;
2283
alter table t2 add primary key (bid, aid);
2284
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2289
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
2290
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
2291
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
2292
howmanyvalues count(*)
2297
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2298
howmanyvalues mycount
2303
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
2304
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
2305
howmanyvalues mycount
2310
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2311
howmanyvalues mycount
2316
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
2317
howmanyvalues mycount
2323
create table t1 (x int);
2324
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;
2325
(select b.x from t1 as b where b.x=a.x)
2327
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`));
2328
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);
2329
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`)) ;
2330
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');
2331
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;
2332
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2333
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;
2338
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
2339
create table t1 (a int, b int);
2340
create table t2 (a int, b int);
2341
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
2342
insert into t2 values (1,3),(2,1);
2343
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
2344
a b (select max(b) from t2 where t1.b=t2.a)
2351
create table t1 (id int);
2352
create table t2 (id int, body text, fulltext (body));
2353
insert into t1 values(1),(2),(3);
2354
insert into t2 values (1,'test'), (2,'mysql'), (3,'test'), (4,'test');
2355
select count(distinct id) from t1 where id in (select id from t2 where match(body) against ('mysql' in boolean mode));
2359
create table t1 (s1 int,s2 int);
2360
insert into t1 values (20,15);
2361
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
2364
create table t1 (s1 int);
2365
insert into t1 values (1),(null);
2366
select * from t1 where s1 < all (select s1 from t1);
2368
select s1, s1 < all (select s1 from t1) from t1;
2369
s1 s1 < all (select s1 from t1)
2374
Code char(3) NOT NULL default '',
2375
Name char(52) NOT NULL default '',
2376
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
2377
Region char(26) NOT NULL default '',
2378
SurfaceArea float(10,2) NOT NULL default '0.00',
2379
IndepYear smallint(6) default NULL,
2380
Population int(11) NOT NULL default '0',
2381
LifeExpectancy float(3,1) default NULL,
2382
GNP float(10,2) default NULL,
2383
GNPOld float(10,2) default NULL,
2384
LocalName char(45) NOT NULL default '',
2385
GovernmentForm char(45) NOT NULL default '',
2386
HeadOfState char(60) default NULL,
2387
Capital int(11) default NULL,
2388
Code2 char(2) NOT NULL default ''
2390
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2391
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');
2392
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');
2393
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');
2394
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
2395
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
2396
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
2401
create table t1 (a1 int);
2402
create table t2 (b1 int);
2403
select * from t1 where a2 > any(select b1 from t2);
2404
ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
2405
select * from t1 where a1 > any(select b1 from t2);
2408
create table t1 (a integer, b integer);
2409
select (select * from t1) = (select 1,2);
2410
(select * from t1) = (select 1,2)
2412
select (select 1,2) = (select * from t1);
2413
(select 1,2) = (select * from t1)
2415
select row(1,2) = ANY (select * from t1);
2416
row(1,2) = ANY (select * from t1)
2418
select row(1,2) != ALL (select * from t1);
2419
row(1,2) != ALL (select * from t1)
2422
create table t1 (a integer, b integer);
2423
select row(1,(2,2)) in (select * from t1 );
2424
ERROR 21000: Operand should contain 2 column(s)
2425
select row(1,(2,2)) = (select * from t1 );
2426
ERROR 21000: Operand should contain 2 column(s)
2427
select (select * from t1) = row(1,(2,2));
2428
ERROR 21000: Operand should contain 1 column(s)
2430
create table t1 (a integer);
2431
insert into t1 values (1);
2432
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
2433
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2434
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2435
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2436
select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );
2437
xx 1 = ALL ( select 1 from t1 where 1 = xx )
2439
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2440
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2441
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL;
2442
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2445
categoryId int(11) NOT NULL,
2446
courseId int(11) NOT NULL,
2447
startDate datetime NOT NULL,
2448
endDate datetime NOT NULL,
2449
createDate datetime NOT NULL,
2450
modifyDate timestamp NOT NULL,
2451
attributes text NOT NULL
2453
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2454
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2455
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2456
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
2457
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
2458
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2459
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2460
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2461
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
2463
userId int(11) NOT NULL,
2464
courseId int(11) NOT NULL,
2465
date datetime NOT NULL
2467
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
2468
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
2469
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
2470
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
2471
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
2472
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
2474
groupId int(11) NOT NULL,
2475
parentId int(11) NOT NULL,
2476
startDate datetime NOT NULL,
2477
endDate datetime NOT NULL,
2478
createDate datetime NOT NULL,
2479
modifyDate timestamp NOT NULL,
2482
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
2484
id int(11) NOT NULL,
2485
groupTypeId int(11) NOT NULL,
2486
groupKey varchar(50) NOT NULL,
2490
createDate datetime NOT NULL,
2491
modifyDate timestamp NOT NULL
2493
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
2494
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
2496
userId int(11) NOT NULL,
2497
groupId int(11) NOT NULL,
2498
createDate datetime NOT NULL,
2499
modifyDate timestamp NOT NULL
2501
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
2503
count(distinct t2.userid) pass,
2505
count(t2.courseid) crse,
2508
date_format(date, '%b%y') as colhead
2510
join t1 on t2.courseid=t1.courseid
2523
select t4.id as parentid,
2524
t4.name as parentgroup,
2526
t4.name as groupname,
2529
) as gin on t5.groupid=gin.childid
2530
) as groupstuff on t2.userid = groupstuff.userid
2532
groupstuff.groupname, colhead , t2.courseid;
2533
pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead
2534
1 5141 12 group2 12 group2 5 1 5 12 Aug04
2535
1 5141 12 group2 12 group2 5 1 1 41 Aug04
2536
1 5141 12 group2 12 group2 5 1 2 52 Aug04
2537
1 5141 12 group2 12 group2 5 1 2 53 Aug04
2538
1 5141 12 group2 12 group2 5 1 3 51 Oct04
2539
1 5141 12 group2 12 group2 5 1 1 86 Oct04
2540
1 5141 12 group2 12 group2 5 1 1 87 Oct04
2541
1 5141 12 group2 12 group2 5 1 2 88 Oct04
2542
1 5141 12 group2 12 group2 5 1 2 89 Oct04
2543
drop table t1, t2, t3, t4, t5;
2544
create table t1 (a int);
2545
insert into t1 values (1), (2), (3);
2546
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
2552
create table t1 (a int, b int);
2553
insert into t1 values (1,2);
2554
select 1 = (select * from t1);
2555
ERROR 21000: Operand should contain 1 column(s)
2556
select (select * from t1) = 1;
2557
ERROR 21000: Operand should contain 2 column(s)
2558
select (1,2) = (select a from t1);
2559
ERROR 21000: Operand should contain 2 column(s)
2560
select (select a from t1) = (1,2);
2561
ERROR 21000: Operand should contain 1 column(s)
2562
select (1,2,3) = (select * from t1);
2563
ERROR 21000: Operand should contain 3 column(s)
2564
select (select * from t1) = (1,2,3);
2565
ERROR 21000: Operand should contain 2 column(s)
2568
`itemid` bigint(20) unsigned NOT NULL auto_increment,
2569
`sessionid` bigint(20) unsigned default NULL,
2570
`time` int(10) unsigned NOT NULL default '0',
2571
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
2573
`data` text collate latin1_general_ci NOT NULL,
2574
PRIMARY KEY (`itemid`)
2575
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2576
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
2578
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
2579
`pid` int(10) unsigned NOT NULL default '0',
2580
`date` int(10) unsigned NOT NULL default '0',
2581
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
2582
PRIMARY KEY (`sessionid`)
2583
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2584
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
2585
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;
2586
ip count( e.itemid )
2589
create table t1 (fld enum('0','1'));
2590
insert into t1 values ('1');
2591
select * from (select max(fld) from t1) as foo;
2595
CREATE TABLE t1 (one int, two int, flag char(1));
2596
CREATE TABLE t2 (one int, two int, flag char(1));
2597
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2598
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2600
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
2605
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
2609
insert into t2 values (null,null,'N');
2610
insert into t2 values (null,3,'0');
2611
insert into t2 values (null,5,'0');
2612
insert into t2 values (10,null,'0');
2613
insert into t1 values (10,3,'0');
2614
insert into t1 values (10,5,'0');
2615
insert into t1 values (10,10,'0');
2616
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
2626
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2630
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
2640
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2650
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
2660
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2661
id select_type table type possible_keys key key_len ref rows filtered Extra
2662
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2663
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
2665
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"
2666
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2667
id select_type table type possible_keys key key_len ref rows filtered Extra
2668
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
2669
1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
2671
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'))
2672
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;
2673
id select_type table type possible_keys key key_len ref rows filtered Extra
2674
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2675
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
2677
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"
2679
CREATE TABLE t1 (a char(5), b char(5));
2680
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2681
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
2685
CREATE TABLE t1 (a int);
2686
CREATE TABLE t2 (a int, b int);
2687
CREATE TABLE t3 (b int NOT NULL);
2688
INSERT INTO t1 VALUES (1), (2), (3), (4);
2689
INSERT INTO t2 VALUES (1,10), (3,30);
2690
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2691
WHERE t3.b IS NOT NULL OR t2.a > 10;
2694
WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2695
WHERE t3.b IS NOT NULL OR t2.a > 10);
2701
DROP TABLE t1,t2,t3;
2702
CREATE TABLE t1 (f1 INT);
2703
CREATE TABLE t2 (f2 INT);
2704
INSERT INTO t1 VALUES (1);
2705
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
2708
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
2711
INSERT INTO t2 VALUES (1);
2712
INSERT INTO t2 VALUES (2);
2713
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
2717
select 1 from dual where 1 < any (select 2);
2720
select 1 from dual where 1 < all (select 2);
2723
select 1 from dual where 2 > any (select 1);
2726
select 1 from dual where 2 > all (select 1);
2729
select 1 from dual where 1 < any (select 2 from dual);
2732
select 1 from dual where 1 < all (select 2 from dual where 1!=1);
2735
create table t1 (s1 char);
2736
insert into t1 values (1),(2);
2737
select * from t1 where (s1 < any (select s1 from t1));
2740
select * from t1 where not (s1 < any (select s1 from t1));
2743
select * from t1 where (s1 < ALL (select s1+1 from t1));
2746
select * from t1 where not(s1 < ALL (select s1+1 from t1));
2749
select * from t1 where (s1+1 = ANY (select s1 from t1));
2752
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
2755
select * from t1 where (s1 = ALL (select s1/s1 from t1));
2758
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
2763
retailerID varchar(8) NOT NULL,
2764
statusID int(10) unsigned NOT NULL,
2765
changed datetime NOT NULL,
2766
UNIQUE KEY retailerID (retailerID, statusID, changed)
2768
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
2769
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
2770
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
2771
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
2772
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
2773
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
2775
where (r1.retailerID,(r1.changed)) in
2776
(SELECT r2.retailerId,(max(changed)) from t1 r2
2777
group by r2.retailerId);
2778
retailerID statusID changed
2779
0026 2 2006-01-06 12:25:53
2780
0037 2 2006-01-06 12:25:53
2781
0048 1 2006-01-06 12:37:50
2782
0059 1 2006-01-06 12:37:50
2784
create table t1(a int, primary key (a));
2785
insert into t1 values (10);
2786
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
2787
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
2788
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2789
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2790
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2791
id select_type table type possible_keys key key_len ref rows Extra
2792
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2793
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2794
2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
2795
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2796
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2797
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2800
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2801
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2802
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2803
id select_type table type possible_keys key key_len ref rows Extra
2804
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2805
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2806
2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition; Using MRR
2807
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2808
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2809
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2814
field1 int NOT NULL,
2815
field2 int NOT NULL,
2816
field3 int NOT NULL,
2817
PRIMARY KEY (field1,field2,field3)
2820
fieldA int NOT NULL,
2821
fieldB int NOT NULL,
2822
PRIMARY KEY (fieldA,fieldB)
2824
INSERT INTO t1 VALUES
2825
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
2826
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
2827
SELECT field1, field2, COUNT(*)
2828
FROM t1 GROUP BY field1, field2;
2829
field1 field2 COUNT(*)
2833
SELECT field1, field2
2835
GROUP BY field1, field2
2836
HAVING COUNT(*) >= ALL (SELECT fieldB
2837
FROM t2 WHERE fieldA = field1);
2840
SELECT field1, field2
2842
GROUP BY field1, field2
2843
HAVING COUNT(*) < ANY (SELECT fieldB
2844
FROM t2 WHERE fieldA = field1);
2849
CREATE TABLE t1(a int, INDEX (a));
2850
INSERT INTO t1 VALUES (1), (3), (5), (7);
2851
INSERT INTO t1 VALUES (NULL);
2852
CREATE TABLE t2(a int);
2853
INSERT INTO t2 VALUES (1),(2),(3);
2854
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
2855
id select_type table type possible_keys key key_len ref rows Extra
2856
1 PRIMARY t2 ALL NULL NULL NULL NULL 3
2857
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
2858
SELECT a, a IN (SELECT a FROM t1) FROM t2;
2859
a a IN (SELECT a FROM t1)
2864
CREATE TABLE t1 (a DATETIME);
2865
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
2866
CREATE TABLE t2 AS SELECT
2867
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
2868
FROM t1 WHERE a > '2000-01-01';
2869
SHOW CREATE TABLE t2;
2871
t2 CREATE TABLE "t2" (
2872
"sub_a" datetime ON UPDATE CURRENT_TIMESTAMP
2873
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2874
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
2875
SHOW CREATE TABLE t3;
2877
t3 CREATE TABLE "t3" (
2878
"a" datetime ON UPDATE CURRENT_TIMESTAMP
2879
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2880
DROP TABLE t1,t2,t3;
2881
CREATE TABLE t1 (a int);
2882
INSERT INTO t1 VALUES (1), (2);
2883
SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
2885
SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
2889
EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
2890
id select_type table type possible_keys key key_len ref rows Extra
2891
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
2892
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2894
CREATE TABLE t1 (a int);
2895
INSERT INTO t1 VALUES (2), (4), (1), (3);
2896
CREATE TABLE t2 (b int, c int);
2897
INSERT INTO t2 VALUES
2898
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
2899
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
2905
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
2906
ERROR 21000: Subquery returns more than 1 row
2907
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
2913
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
2914
ERROR 21000: Subquery returns more than 1 row
2915
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
2920
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
2921
ERROR 21000: Subquery returns more than 1 row
2922
SELECT a FROM t1 GROUP BY a
2923
HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
2924
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2930
SELECT a FROM t1 GROUP BY a
2931
HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
2932
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2933
ERROR 21000: Subquery returns more than 1 row
2934
SELECT a FROM t1 GROUP BY a
2935
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
2936
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2939
SELECT a FROM t1 GROUP BY a
2940
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
2941
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
2942
ERROR 21000: Subquery returns more than 1 row
2944
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
2945
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
2952
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
2953
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2954
ERROR 21000: Subquery returns more than 1 row
2956
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
2957
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
2964
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
2965
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2966
ERROR 21000: Subquery returns more than 1 row
2968
create table t1 (df decimal(5,1));
2969
insert into t1 values(1.1);
2970
insert into t1 values(2.2);
2971
select * from t1 where df <= all (select avg(df) from t1 group by df);
2974
select * from t1 where df >= all (select avg(df) from t1 group by df);
2978
create table t1 (df decimal(5,1));
2979
insert into t1 values(1.1);
2980
select 1.1 * exists(select * from t1);
2981
1.1 * exists(select * from t1)
2985
grp int(11) default NULL,
2986
a decimal(10,2) default NULL);
2987
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
2997
select min(a) from t1 group by grp;
3004
CREATE table t1 ( c1 integer );
3005
INSERT INTO t1 VALUES ( 1 );
3006
INSERT INTO t1 VALUES ( 2 );
3007
INSERT INTO t1 VALUES ( 3 );
3008
CREATE TABLE t2 ( c2 integer );
3009
INSERT INTO t2 VALUES ( 1 );
3010
INSERT INTO t2 VALUES ( 4 );
3011
INSERT INTO t2 VALUES ( 5 );
3012
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
3015
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
3016
WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
3020
CREATE TABLE t1 ( c1 integer );
3021
INSERT INTO t1 VALUES ( 1 );
3022
INSERT INTO t1 VALUES ( 2 );
3023
INSERT INTO t1 VALUES ( 3 );
3024
INSERT INTO t1 VALUES ( 6 );
3025
CREATE TABLE t2 ( c2 integer );
3026
INSERT INTO t2 VALUES ( 1 );
3027
INSERT INTO t2 VALUES ( 4 );
3028
INSERT INTO t2 VALUES ( 5 );
3029
INSERT INTO t2 VALUES ( 6 );
3030
CREATE TABLE t3 ( c3 integer );
3031
INSERT INTO t3 VALUES ( 7 );
3032
INSERT INTO t3 VALUES ( 8 );
3033
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
3034
WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
3038
DROP TABLE t1,t2,t3;
3040
`itemid` bigint(20) unsigned NOT NULL auto_increment,
3041
`sessionid` bigint(20) unsigned default NULL,
3042
`time` int(10) unsigned NOT NULL default '0',
3043
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
3045
`data` text collate latin1_general_ci NOT NULL,
3046
PRIMARY KEY (`itemid`)
3047
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
3048
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
3050
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
3051
`pid` int(10) unsigned NOT NULL default '0',
3052
`date` int(10) unsigned NOT NULL default '0',
3053
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
3054
PRIMARY KEY (`sessionid`)
3055
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
3056
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
3057
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;
3058
ip count( e.itemid )
3061
CREATE TABLE t1 (EMPNUM CHAR(3));
3062
CREATE TABLE t2 (EMPNUM CHAR(3) );
3063
INSERT INTO t1 VALUES ('E1'),('E2');
3064
INSERT INTO t2 VALUES ('E1');
3066
WHERE t1.EMPNUM NOT IN
3069
WHERE t1.EMPNUM = t2.EMPNUM);
3074
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
3075
INSERT INTO t1 VALUES (1, 1);
3076
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
3077
PRIMARY KEY(select_id,values_id));
3078
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
3079
SELECT values_id FROM t1
3080
WHERE values_id IN (SELECT values_id FROM t2
3081
WHERE select_id IN (1, 0));
3084
SELECT values_id FROM t1
3085
WHERE values_id IN (SELECT values_id FROM t2
3086
WHERE select_id BETWEEN 0 AND 1);
3089
SELECT values_id FROM t1
3090
WHERE values_id IN (SELECT values_id FROM t2
3091
WHERE select_id = 0 OR select_id = 1);
3095
create table t1 (fld enum('0','1'));
3096
insert into t1 values ('1');
3097
select * from (select max(fld) from t1) as foo;
3101
CREATE TABLE t1 (a int, b int);
3102
CREATE TABLE t2 (c int, d int);
3103
CREATE TABLE t3 (e int);
3104
INSERT INTO t1 VALUES
3105
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
3106
INSERT INTO t2 VALUES
3107
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
3108
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
3109
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
3129
SELECT a FROM t1 GROUP BY a
3130
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
3134
SELECT a FROM t1 GROUP BY a
3135
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
3138
SELECT a FROM t1 GROUP BY a
3139
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
3143
SELECT a FROM t1 GROUP BY a
3144
HAVING a IN (SELECT c FROM t2
3145
WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
3149
SELECT a FROM t1 GROUP BY a
3150
HAVING a IN (SELECT c FROM t2
3151
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
3155
SELECT a FROM t1 GROUP BY a
3156
HAVING a IN (SELECT c FROM t2
3157
WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
3160
SELECT a FROM t1 GROUP BY a
3161
HAVING a IN (SELECT c FROM t2
3162
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
3165
SELECT a FROM t1 GROUP BY a
3166
HAVING a IN (SELECT c FROM t2
3167
WHERE MIN(b) < d AND
3168
EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
3171
SELECT a, SUM(a) FROM t1 GROUP BY a;
3178
WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
3182
SELECT a FROM t1 GROUP BY a
3183
HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
3190
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
3196
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
3203
SELECT t1.a FROM t1 GROUP BY t1.a
3204
HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
3205
HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3206
HAVING SUM(t1.a+t2.c) < t3.e/4));
3210
SELECT t1.a FROM t1 GROUP BY t1.a
3211
HAVING t1.a > ALL(SELECT t2.c FROM t2
3212
WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3213
HAVING SUM(t1.a+t2.c) < t3.e/4));
3216
SELECT t1.a FROM t1 GROUP BY t1.a
3217
HAVING t1.a > ALL(SELECT t2.c FROM t2
3218
WHERE EXISTS(SELECT t3.e FROM t3
3219
WHERE SUM(t1.a+t2.c) < t3.e/4));
3220
ERROR HY000: Invalid use of group function
3221
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
3222
ERROR HY000: Invalid use of group function
3223
SELECT t1.a FROM t1 GROUP BY t1.a
3224
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3225
HAVING AVG(t2.c+SUM(t1.b)) > 20);
3230
SELECT t1.a FROM t1 GROUP BY t1.a
3231
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3232
HAVING AVG(SUM(t1.b)) > 20);
3236
SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
3237
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3238
HAVING t2.c+sum > 20);
3243
DROP TABLE t1,t2,t3;
3244
CREATE TABLE t1 (a varchar(5), b varchar(10));
3245
INSERT INTO t1 VALUES
3246
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
3247
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
3248
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3254
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3255
id select_type table type possible_keys key key_len ref rows Extra
3256
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3257
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3258
ALTER TABLE t1 ADD INDEX(a);
3259
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3265
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3266
id select_type table type possible_keys key key_len ref rows Extra
3267
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3268
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3270
create table t1( f1 int,f2 int);
3271
insert into t1 values (1,1),(2,2);
3272
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';
3277
create table t1 (c int, key(c));
3278
insert into t1 values (1142477582), (1142455969);
3279
create table t2 (a int, b int);
3280
insert into t2 values (2, 1), (1, 0);
3281
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
3283
create table t1 (i int, j bigint);
3284
insert into t1 values (1, 2), (2, 2), (3, 2);
3285
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
3289
CREATE TABLE t1 (i BIGINT UNSIGNED);
3290
INSERT INTO t1 VALUES (10000000000000000000);
3291
INSERT INTO t1 VALUES (1);
3292
CREATE TABLE t2 (i BIGINT UNSIGNED);
3293
INSERT INTO t2 VALUES (10000000000000000000);
3294
INSERT INTO t2 VALUES (1);
3296
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
3298
10000000000000000000
3301
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
3303
10000000000000000000
3304
/* subquery test with cast*/
3305
SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);
3307
10000000000000000000
3311
id bigint(20) unsigned NOT NULL auto_increment,
3312
name varchar(255) NOT NULL,
3315
INSERT INTO t1 VALUES
3316
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
3318
id bigint(20) unsigned NOT NULL auto_increment,
3319
mid bigint(20) unsigned NOT NULL,
3323
INSERT INTO t2 VALUES
3324
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
3325
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
3327
(SELECT date FROM t2 WHERE mid = t1.id
3328
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3329
(SELECT date FROM t2 WHERE mid = t1.id
3330
ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
3332
id name date_last date_next_to_last
3333
1 Balazs 2006-05-01 NULL
3334
2 Joe 2006-04-20 NULL
3335
3 Frank 2006-04-13 NULL
3337
(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
3338
ORDER BY date DESC LIMIT 1, 1) AS date_count
3345
(SELECT date FROM t2 WHERE mid = t1.id
3346
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3347
(SELECT date FROM t2 WHERE mid = t1.id
3348
ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
3350
id name date_last date_next_to_last
3351
1 Balazs 2006-05-01 2006-03-30
3352
2 Joe 2006-04-20 2006-04-06
3353
3 Frank 2006-04-13 NULL
3356
i1 int(11) NOT NULL default '0',
3357
i2 int(11) NOT NULL default '0',
3358
t datetime NOT NULL default '0000-00-00 00:00:00',
3359
PRIMARY KEY (i1,i2,t)
3361
INSERT INTO t1 VALUES
3362
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
3363
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
3364
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
3365
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
3366
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
3367
(24,2,'2005-05-27 12:40:06');
3369
i1 int(11) NOT NULL default '0',
3370
i2 int(11) NOT NULL default '0',
3371
t datetime default NULL,
3374
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
3377
WHERE t1.t = (SELECT t1.t FROM t1
3378
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3379
ORDER BY t1.t DESC LIMIT 1);
3380
id select_type table type possible_keys key key_len ref rows Extra
3381
1 PRIMARY t2 system NULL NULL NULL NULL 1
3382
1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
3383
2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
3385
WHERE t1.t = (SELECT t1.t FROM t1
3386
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3387
ORDER BY t1.t DESC LIMIT 1);
3389
24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40
3391
CREATE TABLE t1 (i INT);
3392
(SELECT i FROM t1) UNION (SELECT i FROM t1);
3394
SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS
3396
(SELECT i FROM t1) UNION
3401
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3402
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
3403
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3405
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))
3407
explain select * from t1 where not exists
3408
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3409
id select_type table type possible_keys key key_len ref rows Extra
3410
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
3411
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3412
3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3413
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
3415
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
3416
insert into t1 (a) values (FLOOR(rand() * 100));
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;
3426
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3427
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3428
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3429
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3431
(SELECT REPEAT(' ',250) FROM t1 i1
3432
WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a
3433
FROM t1 ORDER BY a LIMIT 5;
3441
CREATE TABLE t1 (a INT, b INT);
3442
CREATE TABLE t2 (a INT);
3443
INSERT INTO t2 values (1);
3444
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
3445
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
3446
(SELECT COUNT(DISTINCT t1.b) from t2)
3450
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3451
FROM t1 GROUP BY t1.a;
3452
(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3456
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
3457
COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))
3461
SELECT COUNT(DISTINCT t1.b),
3462
(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
3463
FROM t1 GROUP BY t1.a;
3464
COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
3470
SELECT COUNT(DISTINCT t1.b)
3473
FROM t1 GROUP BY t1.a;
3476
SELECT COUNT(DISTINCT t1.b)
3485
SELECT COUNT(DISTINCT t1.b)
3488
FROM t1 GROUP BY t1.a LIMIT 1)
3494
SELECT COUNT(DISTINCT t1.b)
3497
FROM t1 GROUP BY t1.a LIMIT 1)
3502
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
3503
CREATE TABLE t2 (x int auto_increment, y int, z int,
3504
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
3505
SET SESSION sort_buffer_size = 32 * 1024;
3507
Warning 1292 Truncated incorrect sort_buffer_size value: '32768'
3508
SELECT SQL_NO_CACHE COUNT(*)
3509
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3513
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
3514
SELECT SQL_NO_CACHE COUNT(*)
3515
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3520
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
3521
CREATE TABLE t2 (c int);
3522
INSERT INTO t1 VALUES ('aa', 1);
3523
INSERT INTO t2 VALUES (1);
3525
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3527
SELECT c from t2 WHERE c=t1.c);
3530
INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
3532
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3534
SELECT c from t2 WHERE c=t1.c);
3540
INSERT INTO t2 VALUES (2);
3541
CREATE TABLE t3 (c int);
3542
INSERT INTO t3 VALUES (1);
3544
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
3546
SELECT c from t2 WHERE c=t1.c);
3552
DROP TABLE t1,t2,t3;
3553
DROP TABLE IF EXISTS t1;
3554
DROP TABLE IF EXISTS t2;
3555
DROP TABLE IF EXISTS t1xt2;
3557
id_1 int(5) NOT NULL,
3558
t varchar(4) DEFAULT NULL
3561
id_2 int(5) NOT NULL,
3562
t varchar(4) DEFAULT NULL
3564
CREATE TABLE t1xt2 (
3565
id_1 int(5) NOT NULL,
3566
id_2 int(5) NOT NULL
3568
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
3569
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
3570
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
3571
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3572
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3574
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3575
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3577
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3578
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3580
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3581
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3587
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3588
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
3594
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3595
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
3601
insert INTO t1xt2 VALUES (1, 12);
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 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3614
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3615
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3620
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3621
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3626
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3627
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3632
insert INTO t1xt2 VALUES (2, 12);
3633
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3634
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3638
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3639
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3643
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3644
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3648
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3649
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3653
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3654
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3658
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3659
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3666
CREATE TABLE t1 (a int);
3667
INSERT INTO t1 VALUES (3), (1), (2);
3668
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
3673
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
3679
CREATE TABLE t1 (a int, b int);
3680
CREATE TABLE t2 (m int, n int);
3681
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3682
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3684
(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3686
COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3691
(SELECT MIN(m) FROM t2 WHERE m = count(*))
3693
COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))
3699
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
3704
CREATE TABLE t1 (a int, b int);
3705
CREATE TABLE t2 (m int, n int);
3706
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3707
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3708
SELECT COUNT(*) c, a,
3709
(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3711
c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3715
SELECT COUNT(*) c, a,
3716
(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3718
c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3723
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
3724
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
3725
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
3726
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
3728
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
3735
(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
3742
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
3743
FROM t1 WHERE t1.d=0 GROUP BY a;
3749
(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3750
LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3770
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3772
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3773
FROM t1 as tt GROUP BY tt.a;
3779
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3781
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
3782
FROM t1 as tt GROUP BY tt.a;
3788
CREATE TABLE t1 (a INT);
3789
INSERT INTO t1 values (1),(1),(1),(1);
3790
CREATE TABLE t2 (x INT);
3791
INSERT INTO t1 values (1000),(1001),(1002);
3792
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
3793
ERROR HY000: Invalid use of group function
3794
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
3795
ERROR HY000: Invalid use of group function
3796
SELECT COUNT(1) FROM DUAL;
3799
SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;
3800
ERROR HY000: Invalid use of group function
3802
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
3804
ERROR HY000: Invalid use of group function
3806
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
3808
ERROR HY000: Invalid use of group function
3810
CREATE TABLE t1 (a int, b int, KEY (a));
3811
INSERT INTO t1 VALUES (1,1),(2,1);
3812
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
3813
id select_type table type possible_keys key key_len ref rows Extra
3814
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
3815
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3817
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
3818
INSERT INTO t1 VALUES
3819
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
3820
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
3821
INSERT INTO t2 VALUES (7), (5), (1), (3);
3822
SELECT id, st FROM t1
3823
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3828
SELECT id, st FROM t1
3829
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3835
SELECT id, st FROM t1
3836
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3840
SELECT id, st FROM t1
3841
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3847
CREATE TABLE t1 (a int);
3848
INSERT INTO t1 VALUES (1), (2);
3850
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
3851
id select_type table type possible_keys key key_len ref rows filtered Extra
3852
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
3853
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
3855
Note 1003 select "res"."count(*)" AS "count(*)" from (select count(0) AS "count(*)" from "test"."t1" group by "test"."t1"."a") "res"
3858
a varchar(255) default NULL,
3859
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3863
a varchar(255) default NULL
3865
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
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 SELECT * FROM t1;
3871
INSERT INTO t1 SELECT * FROM t1;
3872
INSERT INTO t1 SELECT * FROM t1;
3873
INSERT INTO t1 SELECT * FROM t1;
3874
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
3875
INSERT INTO `t2` VALUES ('abcdefghijk');
3876
INSERT INTO `t2` VALUES ('asdf');
3877
SET session sort_buffer_size=8192;
3879
Warning 1292 Truncated incorrect sort_buffer_size value: '8192'
3880
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
3885
CREATE TABLE t1 (a INTEGER, b INTEGER);
3886
CREATE TABLE t2 (x INTEGER);
3887
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3888
INSERT INTO t2 VALUES (1), (2);
3889
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
3890
ERROR 21000: Subquery returns more than 1 row
3891
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
3892
ERROR 21000: Subquery returns more than 1 row
3893
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
3894
(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
3897
CREATE TABLE t1 (a INT, b INT);
3898
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
3899
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
3900
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
3905
CREATE TABLE t1 (a INT);
3906
CREATE TABLE t2 (a INT);
3907
INSERT INTO t1 VALUES (1),(2);
3908
INSERT INTO t2 VALUES (1),(2);
3909
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
3910
(SELECT SUM(t1.a) FROM t2 WHERE a=0)
3912
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
3913
ERROR 21000: Subquery returns more than 1 row
3914
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
3915
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
3918
CREATE TABLE t1 (a1 INT, a2 INT);
3919
CREATE TABLE t2 (b1 INT, b2 INT);
3920
INSERT INTO t1 VALUES (100, 200);
3921
INSERT INTO t1 VALUES (101, 201);
3922
INSERT INTO t2 VALUES (101, 201);
3923
INSERT INTO t2 VALUES (103, 203);
3924
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
3925
((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL
3929
CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
3930
INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
3931
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3933
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3935
CREATE INDEX I1 ON t1 (s1);
3936
CREATE INDEX I2 ON t1 (s2);
3937
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3939
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3942
INSERT INTO t1 VALUES (0x41,0x41);
3943
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
3946
CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
3947
CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
3948
CREATE TABLE t3 (a3 BINARY(2) default '0');
3949
INSERT INTO t1 VALUES (1),(2),(3),(4);
3950
INSERT INTO t2 VALUES (1),(2),(3);
3951
INSERT INTO t3 VALUES (1),(2),(3);
3952
SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
3957
SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
3958
a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)
3963
DROP TABLE t1,t2,t3;
3964
CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
3965
CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
3966
CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
3967
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
3968
INSERT INTO t2 VALUES (2), (3), (4), (5);
3969
INSERT INTO t3 VALUES (10), (20), (30);
3970
SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
3975
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
3977
DROP TABLE t1, t2, t3;
3978
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
3979
INSERT INTO t1 VALUES ('a', 'aa');
3980
INSERT INTO t1 VALUES ('a', 'aaa');
3981
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3983
CREATE INDEX I1 ON t1 (a);
3984
CREATE INDEX I2 ON t1 (b);
3985
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3986
id select_type table type possible_keys key key_len ref rows Extra
3987
1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
3988
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
3989
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3991
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
3992
INSERT INTO t2 SELECT * FROM t1;
3993
CREATE INDEX I1 ON t2 (a);
3994
CREATE INDEX I2 ON t2 (b);
3995
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3996
id select_type table type possible_keys key key_len ref rows Extra
3997
1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
3998
1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
3999
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4002
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4003
id select_type table type possible_keys key key_len ref rows Extra
4004
1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
4005
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
4006
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4009
CREATE TABLE t1(a INT, b INT);
4010
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
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'
4016
SELECT a AS out_a, MIN(b) FROM t1
4017
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
4019
ERROR 42S22: Unknown column 'out_a' in 'where clause'
4021
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
4022
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4024
id select_type table type possible_keys key key_len ref rows Extra
4025
1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
4026
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
4027
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
4028
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4034
CREATE TABLE t1 (a INT);
4035
CREATE TABLE t2 (a INT);
4036
INSERT INTO t1 VALUES (1),(2);
4037
INSERT INTO t2 VALUES (1),(2);
4038
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
4043
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
4044
id select_type table type possible_keys key key_len ref rows filtered Extra
4045
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
4046
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
4048
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
4049
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"))
4051
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
4052
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
4053
id select_type table type possible_keys key key_len ref rows filtered Extra
4054
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
4055
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
4056
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where
4057
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
4059
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
4060
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
4061
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")))
4064
f7 varchar(32) collate utf8_bin NOT NULL default '',
4065
f10 varchar(32) collate utf8_bin default NULL,
4068
INSERT INTO t4 VALUES(1,1), (2,null);
4070
f4 varchar(32) collate utf8_bin NOT NULL default '',
4071
f2 varchar(50) collate utf8_bin default NULL,
4072
f3 varchar(10) collate utf8_bin default NULL,
4076
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
4078
f8 varchar(32) collate utf8_bin NOT NULL default '',
4079
f1 varchar(10) collate utf8_bin default NULL,
4080
f9 varchar(32) collate utf8_bin default NULL,
4083
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
4085
f6 varchar(32) collate utf8_bin NOT NULL default '',
4086
f5 varchar(50) collate utf8_bin default NULL,
4089
INSERT INTO t3 VALUES (1,null), (2,null);
4091
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
4092
IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
4096
FROM t2 VPC, t4 a2, t2 a3
4098
VPC.f4 = a2.f10 AND a3.f2 = a4
4105
t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
4110
DROP TABLE t1, t2, t3, t4;
4112
create table t_out (subcase char(3),
4113
a1 char(2), b1 char(2), c1 char(2));
4114
create table t_in (a2 char(2), b2 char(2), c2 char(2));
4115
insert into t_out values ('A.1','2a', NULL, '2a');
4116
insert into t_out values ('A.3', '2a', NULL, '2a');
4117
insert into t_out values ('A.4', '2a', NULL, 'xx');
4118
insert into t_out values ('B.1', '2a', '2a', '2a');
4119
insert into t_out values ('B.2', '2a', '2a', '2a');
4120
insert into t_out values ('B.3', '3a', 'xx', '3a');
4121
insert into t_out values ('B.4', 'xx', '3a', '3a');
4122
insert into t_in values ('1a', '1a', '1a');
4123
insert into t_in values ('2a', '2a', '2a');
4124
insert into t_in values (NULL, '2a', '2a');
4125
insert into t_in values ('3a', NULL, '3a');
4127
Test general IN semantics (not top-level)
4131
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
4132
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
4133
from t_out where subcase = 'A.1';
4134
subcase pred_in pred_not_in
4136
case A.2 - impossible
4139
(a1, b1, c1) IN (select * from t_in) pred_in,
4140
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4141
from t_out where subcase = 'A.3';
4142
subcase pred_in pred_not_in
4146
(a1, b1, c1) IN (select * from t_in) pred_in,
4147
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4148
from t_out where subcase = 'A.4';
4149
subcase pred_in pred_not_in
4153
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
4154
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
4155
from t_out where subcase = 'B.1';
4156
subcase pred_in pred_not_in
4160
(a1, b1, c1) IN (select * from t_in) pred_in,
4161
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4162
from t_out where subcase = 'B.2';
4163
subcase pred_in pred_not_in
4167
(a1, b1, c1) IN (select * from t_in) pred_in,
4168
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4169
from t_out where subcase = 'B.3';
4170
subcase pred_in pred_not_in
4174
(a1, b1, c1) IN (select * from t_in) pred_in,
4175
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
4176
from t_out where subcase = 'B.4';
4177
subcase pred_in pred_not_in
4180
Test IN as top-level predicate, and
4181
as non-top level for cases A.3, B.3 (the only cases with NULL result).
4184
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4185
where subcase = 'A.1' and
4186
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
4189
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4190
where subcase = 'A.1' and
4191
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
4194
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4195
where subcase = 'A.1' and
4196
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
4200
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4201
where subcase = 'A.3' and
4202
(a1, b1, c1) IN (select * from t_in);
4205
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4206
where subcase = 'A.3' and
4207
(a1, b1, c1) NOT IN (select * from t_in);
4210
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4211
where subcase = 'A.3' and
4212
NOT((a1, b1, c1) IN (select * from t_in));
4215
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4216
where subcase = 'A.3' and
4217
((a1, b1, c1) IN (select * from t_in)) is NULL and
4218
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
4222
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4223
where subcase = 'A.4' and
4224
(a1, b1, c1) IN (select * from t_in);
4227
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4228
where subcase = 'A.4' and
4229
(a1, b1, c1) NOT IN (select * from t_in);
4232
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4233
where subcase = 'A.4' and
4234
NOT((a1, b1, c1) IN (select * from t_in));
4238
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4239
where subcase = 'B.1' and
4240
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
4243
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4244
where subcase = 'B.1' and
4245
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
4248
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4249
where subcase = 'B.1' and
4250
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
4254
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4255
where subcase = 'B.2' and
4256
(a1, b1, c1) IN (select * from t_in);
4259
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4260
where subcase = 'B.2' and
4261
(a1, b1, c1) NOT IN (select * from t_in);
4264
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4265
where subcase = 'B.2' and
4266
NOT((a1, b1, c1) IN (select * from t_in));
4270
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4271
where subcase = 'B.3' and
4272
(a1, b1, c1) IN (select * from t_in);
4275
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4276
where subcase = 'B.3' and
4277
(a1, b1, c1) NOT IN (select * from t_in);
4280
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4281
where subcase = 'B.3' and
4282
NOT((a1, b1, c1) IN (select * from t_in));
4285
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4286
where subcase = 'B.3' and
4287
((a1, b1, c1) IN (select * from t_in)) is NULL and
4288
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
4292
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4293
where subcase = 'B.4' and
4294
(a1, b1, c1) IN (select * from t_in);
4297
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4298
where subcase = 'B.4' and
4299
(a1, b1, c1) NOT IN (select * from t_in);
4302
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4303
where subcase = 'B.4' and
4304
NOT((a1, b1, c1) IN (select * from t_in));
4309
CREATE TABLE t1 (s1 char(1));
4310
INSERT INTO t1 VALUES ('a');
4311
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
4315
CREATE TABLE t1( a INT );
4316
INSERT INTO t1 VALUES (1),(2);
4317
CREATE TABLE t2( a INT, b INT );
4319
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
4320
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
4322
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
4323
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
4325
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
4326
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
4330
SELECT a INTO @var FROM t1 WHERE a = 2
4332
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
4337
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
4339
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
4344
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
4346
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
4348
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
4352
SELECT a FROM t1 WHERE a = 2
4354
SELECT a FROM t1 WHERE a = 2
4361
SELECT a FROM t1 WHERE a = 2
4363
SELECT a FROM t1 WHERE a = 2
4368
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
4369
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
4370
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
4373
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
4376
SELECT * FROM ((SELECT 1 a INTO @a)) 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 @a)) t1a' at line 1
4378
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) 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 OUTFILE 'file' )) t1a' at line 1
4380
SELECT * FROM ((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 @a)) 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 @a)) t1a' at line 1
4384
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) 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 DUMPFILE 'file' )) t1a' at line 1
4386
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE '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 OUTFILE 'file' )) t1a' at line 1
4388
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) 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 @a))) t1a' at line 1
4390
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
4391
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
4392
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
4393
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
4394
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
4397
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;
4400
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
4403
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
4406
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
4410
SELECT * FROM t1 JOIN ((SELECT 1 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 UNION SELECT 1) 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 'ON 1' at line 1
4414
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) 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 ') ON 1' at line 1
4416
SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;
4417
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
4418
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
4419
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
4420
SELECT * FROM t1 JOIN (t1 t1a) ON 1;
4426
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
4432
SELECT * FROM (t1 t1a);
4436
SELECT * FROM ((t1 t1a));
4440
SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;
4444
SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;
4448
SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;
4452
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
4456
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
4457
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a2' at line 1
4458
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
4461
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );
4464
SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );
4467
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
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 @a)' at line 1
4469
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4470
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
4471
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4472
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
4473
SELECT * FROM t1 WHERE a = ( SELECT 1 );
4476
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );
4479
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
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 @a)' at line 1
4481
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
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 OUTFILE 'file' )' at line 1
4483
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE '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 DUMPFILE 'file' )' at line 1
4485
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
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 @a)' at line 1
4487
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
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 OUTFILE 'file' )' at line 1
4489
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE '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 DUMPFILE 'file' )' at line 1
4491
SELECT ( SELECT 1 INTO @v );
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 @v )' at line 1
4493
SELECT ( SELECT 1 INTO OUTFILE 'file' );
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 OUTFILE 'file' )' at line 1
4495
SELECT ( SELECT 1 INTO DUMPFILE '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 DUMPFILE 'file' )' at line 1
4497
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
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 @v )' at line 1
4499
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4500
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
4501
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4502
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
4503
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4504
( SELECT a FROM t1 WHERE a = 1 ) a
4507
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
4508
( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a
4511
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
4513
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 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 ) UNION SELECT 1' at line 1
4517
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4518
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
4519
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4520
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
4521
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4522
( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
4524
SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
4525
((SELECT 1 UNION SELECT 1 UNION SELECT 1))
4527
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4528
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
4529
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4530
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
4531
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
4534
SELECT * FROM t1 WHERE a = ( 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 = ALL ( 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 = ANY ( 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 IN ( 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 ) )' at line 1
4542
SELECT * FROM t1 WHERE a = ( ( 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 )' at line 1
4544
SELECT * FROM t1 WHERE a = ALL ( ( 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 ) UNION SELECT 1 )' at line 1
4546
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4547
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
4548
SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4549
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
4550
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4553
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4556
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4559
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4562
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
4563
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
4564
SELECT EXISTS(SELECT 1+1);
4567
SELECT EXISTS(SELECT 1+1 INTO @test);
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 @test)' at line 1
4569
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION 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
4571
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
4572
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
4573
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
4574
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
4576
set optimizer_switch='';
4577
show variables like 'optimizer_switch';
added
removed
mysql-test/r/subselect_no_opts.result
