subselect.result 56.9 KB
Newer Older
1
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
unknown's avatar
unknown committed
2 3 4
select (select 2);
(select 2)
2
5 6 7 8
explain select (select 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
9
Note	1248	Select 2 was reduced during optimisation
10 11 12 13
SELECT (SELECT 1) UNION SELECT (SELECT 2);
(SELECT 1)
1
2
14 15 16 17 18
explain SELECT (SELECT 1) UNION SELECT (SELECT 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
19 20
Note	1248	Select 2 was reduced during optimisation
Note	1248	Select 4 was reduced during optimisation
unknown's avatar
unknown committed
21 22 23
SELECT (SELECT (SELECT 0 UNION SELECT 0));
(SELECT (SELECT 0 UNION SELECT 0))
0
24 25 26
explain SELECT (SELECT (SELECT 0 UNION SELECT 0));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
27
3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
28 29
4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
30
Note	1248	Select 2 was reduced during optimisation
31
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
32
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
33
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;
34
ERROR 42S22: Reference 'b' not supported (forward reference in item list)
35
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
36 37
(SELECT 1)	MAX(1)
1	1
unknown's avatar
unknown committed
38
SELECT (SELECT a) as a;
39
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
unknown's avatar
unknown committed
40
EXPLAIN SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
41 42
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
43
3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
44
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
45
Warnings:
unknown's avatar
unknown committed
46 47
Note	1275	Field or reference 'a' of SELECT #3 was resolved in SELECT #1
Note	1275	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
unknown's avatar
unknown committed
48
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
49 50 51
1
1
SELECT (SELECT 1), a;
unknown's avatar
unknown committed
52
ERROR 42S22: Unknown column 'a' in 'checking transformed subquery'
53
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
54 55
a
1
56
SELECT 1 FROM (SELECT (SELECT a) b) c;
57
ERROR 42S22: Unknown column 'a' in 'field list'
58
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
unknown's avatar
unknown committed
59 60
id
1
61
SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
unknown's avatar
unknown committed
62
ERROR 21000: Operand should contain 1 column(s)
unknown's avatar
unknown committed
63 64 65
SELECT 1 IN (SELECT 1);
1 IN (SELECT 1)
1
66
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
unknown's avatar
unknown committed
67 68
1
1
69
select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
70
ERROR HY000: Wrong usage of PROCEDURE and subquery
71
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
72
ERROR HY000: Incorrect parameters to procedure 'ANALYSE'
unknown's avatar
unknown committed
73 74 75 76 77
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
a
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
a
1
78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
SELECT (SELECT 1,2,3) = ROW(1,2,3);
(SELECT 1,2,3) = ROW(1,2,3)
1
SELECT (SELECT 1,2,3) = ROW(1,2,1);
(SELECT 1,2,3) = ROW(1,2,1)
0
SELECT (SELECT 1,2,3) < ROW(1,2,1);
(SELECT 1,2,3) < ROW(1,2,1)
0
SELECT (SELECT 1,2,3) > ROW(1,2,1);
(SELECT 1,2,3) > ROW(1,2,1)
1
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
(SELECT 1,2,3) = ROW(1,2,NULL)
NULL
SELECT ROW(1,2,3) = (SELECT 1,2,3);
ROW(1,2,3) = (SELECT 1,2,3)
1
SELECT ROW(1,2,3) = (SELECT 1,2,1);
ROW(1,2,3) = (SELECT 1,2,1)
0
SELECT ROW(1,2,3) < (SELECT 1,2,1);
ROW(1,2,3) < (SELECT 1,2,1)
0
SELECT ROW(1,2,3) > (SELECT 1,2,1);
ROW(1,2,3) > (SELECT 1,2,1)
1
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
ROW(1,2,3) = (SELECT 1,2,NULL)
NULL
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
1
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
0
SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
(SELECT 1.5,2,'a') = ROW('b',2,'b')
0
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
(SELECT 'b',2,'a') = ROW(1.5,2,'a')
0
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
(SELECT 1.5,2,'a') = ROW(1.5,'c','a')
0
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
0
unknown's avatar
unknown committed
126
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
unknown's avatar
unknown committed
127
ERROR 21000: Operand should contain 1 column(s)
128 129 130
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
a	b	(SELECT b)
1	2	2
131 132 133
create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
134
create table t4 (a int not null, b int not null);
135 136 137
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
138
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
139
ERROR 42S22: Reference 'a1' not supported (forward reference in item list)
140 141 142 143 144 145 146 147
select (select a from t1 where t1.a=t2.a), a from t2;
(select a from t1 where t1.a=t2.a)	a
NULL	1
2	2
select (select a from t1 where t1.a=t2.b), a from t2;
(select a from t1 where t1.a=t2.b)	a
NULL	1
NULL	2
148 149 150 151 152 153 154 155 156 157 158 159
select (select a from t1), a from t2;
(select a from t1)	a
2	1
2	2
select (select a from t3), a from t2;
(select a from t3)	a
NULL	1
NULL	2
select * from t2 where t2.a=(select a from t1);
a	b
2	7
insert into t3 values (6),(7),(3);
unknown's avatar
unknown committed
160
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
161 162 163
a	b
1	7
2	7
unknown's avatar
unknown committed
164
(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;
165 166 167 168
a	b
1	7
2	7
3	8
unknown's avatar
unknown committed
169
(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);
unknown's avatar
unknown committed
170 171 172 173 174
a	b
1	7
2	7
3	8
4	8
unknown's avatar
unknown committed
175
explain (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);
unknown's avatar
unknown committed
176
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
177
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
178
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using filesort
unknown's avatar
unknown committed
179
3	UNION	t4	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
180
4	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
unknown's avatar
unknown committed
181 182 183 184
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
(select a from t3 where a<t2.a*4 order by 1 desc limit 1)	a
3	1
7	2
unknown's avatar
unknown committed
185 186 187 188
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
(select * from t2 where a>1) as tt;
(select t3.a from t3 where a<8 order by 1 desc limit 1)	a
7	2
unknown's avatar
unknown committed
189 190 191
explain select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
(select * from t2 where a>1) as tt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
192
1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	
unknown's avatar
unknown committed
193
3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
194
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
unknown's avatar
unknown committed
195 196 197 198 199 200 201 202 203 204 205
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);
a
2
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);
a
2
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);
a
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
b	(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
8	7.5000
unknown's avatar
unknown committed
206 207
8	4.5000
9	7.5000
unknown's avatar
unknown committed
208 209
explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
210
1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
211 212
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
213
Warnings:
unknown's avatar
unknown committed
214
Note	1275	Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
unknown's avatar
unknown committed
215 216 217 218 219 220 221
select * from t3 where exists (select * from t2 where t2.b=t3.a);
a
7
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
a
6
3
unknown's avatar
unknown committed
222 223 224 225 226 227 228
select * from t3 where a in (select b from t2);
a
7
select * from t3 where a not in (select b from t2);
a
6
3
unknown's avatar
unknown committed
229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254
select * from t3 where a = some (select b from t2);
a
7
select * from t3 where a <> any (select b from t2);
a
6
3
select * from t3 where a = all (select b from t2);
a
7
select * from t3 where a <> all (select b from t2);
a
6
3
insert into t2 values (100, 5);
select * from t3 where a < any (select b from t2);
a
6
3
select * from t3 where a < all (select b from t2);
a
3
select * from t3 where a >= any (select b from t2);
a
6
7
255 256 257 258
explain select * from t3 where a >= any (select b from t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	
unknown's avatar
unknown committed
259 260 261 262
select * from t3 where a >= all (select b from t2);
a
7
delete from t2 where a=100;
unknown's avatar
unknown committed
263
select * from t3 where a in (select a,b from t2);
unknown's avatar
unknown committed
264
ERROR 21000: Operand should contain 1 column(s)
unknown's avatar
unknown committed
265
select * from t3 where a in (select * from t2);
unknown's avatar
unknown committed
266
ERROR 21000: Operand should contain 1 column(s)
267 268
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
unknown's avatar
unknown committed
269
b	ma
270 271 272 273 274 275
insert into t2 values (2,10);
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
b	ma
10	1
delete from t2 where a=2 and b=10;
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
unknown's avatar
unknown committed
276 277
b	ma
7	12
unknown's avatar
unknown committed
278 279 280 281 282 283 284 285 286 287 288 289 290 291 292
create table t5 (a int);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)	a
NULL	1
2	2
insert into t5 values (5);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)	a
NULL	1
2	2
insert into t5 values (2);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)	a
NULL	1
2	2
unknown's avatar
unknown committed
293 294
explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
295
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
296
2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
unknown's avatar
unknown committed
297
3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
298
Warnings:
unknown's avatar
unknown committed
299 300
Note	1275	Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1
Note	1275	Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1
unknown's avatar
unknown committed
301
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
unknown's avatar
unknown committed
302
ERROR 21000: Subquery returns more than 1 row
303 304 305 306 307
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
create table t7( uq int primary key, name char(25));
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
insert into t6 values (1,1),(1,2),(2,2),(1,3);
select * from t6 where exists (select * from t7 where uq = clinic_uq);
308 309 310 311
patient_uq	clinic_uq
1	1
1	2
2	2
unknown's avatar
unknown committed
312 313 314
explain select * from t6 where exists (select * from t7 where uq = clinic_uq);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t6	ALL	NULL	NULL	NULL	NULL	4	Using where
315
2	DEPENDENT SUBQUERY	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.clinic_uq	1	
316
Warnings:
unknown's avatar
unknown committed
317
Note	1275	Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
318
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
319
ERROR 23000: Column: 'a' in field list is ambiguous
320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335
drop table if exists t1,t2,t3;
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
a	b
W	1732-02-22
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
a	b
W	1
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
a	b
W	a
336
CREATE TABLE `t8` (
unknown's avatar
unknown committed
337 338 339 340 341
`pseudo` varchar(35) character set latin1 NOT NULL default '',
`email` varchar(60) character set latin1 NOT NULL default '',
PRIMARY KEY  (`pseudo`),
UNIQUE KEY `email` (`email`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
342 343 344 345
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
EXPLAIN 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');
unknown's avatar
unknown committed
346
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
347
1	PRIMARY	t8	const	PRIMARY	PRIMARY	35	const	1	
348 349 350
4	SUBQUERY	t8	const	PRIMARY	PRIMARY	35		1	
2	SUBQUERY	t8	const	PRIMARY	PRIMARY	35	const	1	
3	SUBQUERY	t8	const	PRIMARY	PRIMARY	35		1	
351 352
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
unknown's avatar
unknown committed
353
ERROR 21000: Operand should contain 1 column(s)
354
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
unknown's avatar
unknown committed
355
pseudo='joce');
unknown's avatar
unknown committed
356
ERROR 21000: Operand should contain 1 column(s)
357
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
358 359
pseudo
joce
360
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
unknown's avatar
unknown committed
361
ERROR 21000: Subquery returns more than 1 row
362 363
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
CREATE TABLE `t1` (
unknown's avatar
unknown committed
364 365 366 367 368 369
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
370
INSERT INTO t1 (topic,date,pseudo) VALUES
unknown's avatar
unknown committed
371
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
372
EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
unknown's avatar
unknown committed
373
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
374 375
1	SIMPLE	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
unknown's avatar
unknown committed
376
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
377
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
378
2	SUBQUERY	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
379
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
unknown's avatar
unknown committed
380 381
date
2002-08-03
382 383
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
unknown's avatar
unknown committed
384
2002-08-03
385
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
unknown's avatar
unknown committed
386 387 388 389
1
1
1
1
390
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
unknown's avatar
unknown committed
391
ERROR 21000: Subquery returns more than 1 row
392
EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
unknown's avatar
unknown committed
393
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
394
1	PRIMARY	t1	index	NULL	topic	3	NULL	2	Using index
395
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
396
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
397
drop table t1;
398
CREATE TABLE `t1` (
unknown's avatar
unknown committed
399 400 401 402 403
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM ROW_FORMAT=FIXED;
404 405
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
CREATE TABLE `t2` (
unknown's avatar
unknown committed
406 407 408 409 410 411
`mot` varchar(30) NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) NOT NULL default '',
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
412 413
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
unknown's avatar
unknown committed
414 415
a
40143
416
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;
unknown's avatar
unknown committed
417 418 419
numeropost	maxnumrep
43506	2
40143	1
420
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
421
ERROR 42S22: Unknown column 'a' in 'having clause'
unknown's avatar
unknown committed
422
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
423
ERROR 42S22: Unknown column 'a' in 'having clause'
424
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
unknown's avatar
unknown committed
425 426
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
427 428
joce	43506	2002-10-22	joce
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
unknown's avatar
unknown committed
429 430 431
mot	topic	date	pseudo
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
mot	topic	date	pseudo
432
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
unknown's avatar
unknown committed
433 434
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
435 436
joce	43506	2002-10-22	joce
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
unknown's avatar
unknown committed
437 438 439
mot	topic	date	pseudo
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
mot	topic	date	pseudo
440
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
unknown's avatar
unknown committed
441
mot	topic	date	pseudo
442
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
unknown's avatar
unknown committed
443 444
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
445
joce	43506	2002-10-22	joce
446 447 448 449
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
mot	topic	date	pseudo	topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100)
joce	40143	2002-10-22	joce	1
joce	43506	2002-10-22	joce	1
unknown's avatar
unknown committed
450 451 452 453 454 455
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
mot	topic	date	pseudo
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
joce	43506	2002-10-22	joce
456 457 458 459 460 461 462 463 464 465 466 467 468
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
mot	topic	date	pseudo	topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000)
joce	40143	2002-10-22	joce	1
joce	43506	2002-10-22	joce	0
unknown's avatar
unknown committed
469
drop table t1,t2;
470
CREATE TABLE `t1` (
unknown's avatar
unknown committed
471 472 473 474 475
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM ROW_FORMAT=FIXED;
476 477
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
unknown's avatar
unknown committed
478
ERROR 21000: Subquery returns more than 1 row
479
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
unknown's avatar
unknown committed
480
ERROR 21000: Subquery returns more than 1 row
481
drop table t1;
482 483 484 485 486 487 488 489
create table t1 (a int);
insert into t1 values (1),(2),(3);
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
a
1
2
3
drop table t1;
490 491
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO t1 VALUES ();
492
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
unknown's avatar
unknown committed
493
ERROR 21000: Subquery returns more than 1 row
494 495
drop table t1;
CREATE TABLE `t1` (
unknown's avatar
unknown committed
496 497 498 499 500 501 502
`numeropost` mediumint(8) unsigned NOT NULL default '0',
`numreponse` int(10) unsigned NOT NULL auto_increment,
`pseudo` varchar(35) NOT NULL default '',
PRIMARY KEY  (`numeropost`,`numreponse`),
UNIQUE KEY `numreponse` (`numreponse`),
KEY `pseudo` (`pseudo`,`numeropost`)
) TYPE=MyISAM;
503
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
504
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
505
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
506
ERROR 42S22: Unknown column 'a' in 'having clause'
507 508 509 510
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
numreponse	(SELECT numeropost FROM t1 HAVING numreponse=1)
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
unknown's avatar
unknown committed
511
ERROR 21000: Subquery returns more than 1 row
512
EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
unknown's avatar
unknown committed
513
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
514
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
515
EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
unknown's avatar
unknown committed
516
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
517
1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	
518
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
519
drop table t1;
520 521
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
unknown's avatar
unknown committed
522
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
523 524 525
1
1
drop table t1;
526 527 528 529 530 531 532 533 534
create table t1 (a int NOT NULL, b int, primary key (a));
create table t2 (a int NOT NULL, b int, primary key (a));
insert into t1 values (0, 10),(1, 11),(2, 12);
insert into t2 values (1, 21),(2, 22),(3, 23);
select * from t1;
a	b
0	10
1	11
2	12
535
update t1 set b= (select b from t1);
536
ERROR HY000: You can't specify target table 't1' for update in FROM clause
unknown's avatar
unknown committed
537
update t1 set b= (select b from t2);
unknown's avatar
unknown committed
538
ERROR 21000: Subquery returns more than 1 row
539 540 541 542 543 544 545
update t1 set b= (select b from t2 where t1.a = t2.a);
select * from t1;
a	b
0	NULL
1	21
2	22
drop table t1, t2;
unknown's avatar
unknown committed
546 547 548 549 550 551 552 553 554 555 556 557
create table t1 (a int NOT NULL, b int, primary key (a));
create table t2 (a int NOT NULL, b int, primary key (a));
insert into t1 values (0, 10),(1, 11),(2, 12);
insert into t2 values (1, 21),(2, 12),(3, 23);
select * from t1;
a	b
0	10
1	11
2	12
select * from t1 where b = (select b from t2 where t1.a = t2.a);
a	b
2	12
558
delete from t1 where b = (select b from t1);
559
ERROR HY000: You can't specify target table 't1' for update in FROM clause
unknown's avatar
unknown committed
560
delete from t1 where b = (select b from t2);
unknown's avatar
unknown committed
561
ERROR 21000: Subquery returns more than 1 row
unknown's avatar
unknown committed
562 563 564 565 566 567
delete from t1 where b = (select b from t2 where t1.a = t2.a);
select * from t1;
a	b
0	10
1	11
drop table t1, t2;
568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583
create table t11 (a int NOT NULL, b int, primary key (a));
create table t12 (a int NOT NULL, b int, primary key (a));
create table t2 (a int NOT NULL, b int, primary key (a));
insert into t11 values (0, 10),(1, 11),(2, 12);
insert into t12 values (33, 10),(22, 11),(2, 12);
insert into t2 values (1, 21),(2, 12),(3, 23);
select * from t11;
a	b
0	10
1	11
2	12
select * from t12;
a	b
33	10
22	11
2	12
584
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
585
ERROR HY000: You can't specify target table 't12' for update in FROM clause
unknown's avatar
unknown committed
586
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
unknown's avatar
unknown committed
587
ERROR 21000: Subquery returns more than 1 row
588 589 590 591 592 593 594 595 596 597
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
select * from t11;
a	b
0	10
1	11
select * from t12;
a	b
33	10
22	11
drop table t11, t12, t2;
598 599
CREATE TABLE t1 (x int);
create table t2 (a int);
600
create table t3 (b int);
601
insert into t2 values (1);
unknown's avatar
unknown committed
602
insert into t3 values (1),(2);
603
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
604
ERROR HY000: You can't specify target table 't1' for update in FROM clause
605
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
unknown's avatar
unknown committed
606
ERROR 21000: Subquery returns more than 1 row
607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
select * from t1;
x
1
insert into t2 values (1);
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
select * from t1;
x
1
2
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
select * from t1;
x
1
2
3
3
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
unknown's avatar
unknown committed
625 626
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
ERROR 42S22: Unknown column 'x' in 'field list'
627
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
628 629 630 631 632 633
select * from t1;
x
1
2
3
3
unknown's avatar
unknown committed
634 635
11
11
636
2
unknown's avatar
unknown committed
637
drop table t1, t2, t3;
638 639
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
unknown's avatar
unknown committed
640
create table t3 (a int);
641
insert into t2 values (1);
unknown's avatar
unknown committed
642
insert into t3 values (1),(2);
643 644
select * from t1;
x	y
645
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
646
ERROR HY000: You can't specify target table 't1' for update in FROM clause
unknown's avatar
unknown committed
647
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
unknown's avatar
unknown committed
648
ERROR 21000: Subquery returns more than 1 row
649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
select * from t1;
x	y
1	2
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
select * from t1;
x	y
1	3
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
select * from t1;
x	y
1	3
4	1
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
select * from t1;
x	y
1	3
4	2
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
select * from t1;
x	y
1	3
4	2
2	1
unknown's avatar
unknown committed
673
drop table t1, t2, t3;
674
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
675
ERROR HY000: No tables used
unknown's avatar
unknown committed
676 677 678
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t2 VALUES (1),(2);
SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
679 680
id
1
unknown's avatar
unknown committed
681
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
682
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
683
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
unknown's avatar
unknown committed
684
Warnings:
unknown's avatar
unknown committed
685
Note	1248	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
686
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
687 688
id
1
unknown's avatar
unknown committed
689
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
690 691
id
2
unknown's avatar
unknown committed
692
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
693
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
694
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
unknown's avatar
unknown committed
695
Warnings:
unknown's avatar
unknown committed
696 697
Note	1248	Select 3 was reduced during optimisation
Note	1248	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
698
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
699
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
700
1	PRIMARY	t2	index	NULL	id	5	NULL	2	Using where; Using index
701
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
702
3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
703
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
unknown's avatar
unknown committed
704
id
unknown's avatar
unknown committed
705
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
unknown's avatar
unknown committed
706 707
id
2
unknown's avatar
unknown committed
708
INSERT INTO t2 VALUES ((SELECT * FROM t2));
709
ERROR HY000: You can't specify target table 't2' for update in FROM clause
710
INSERT INTO t2 VALUES ((SELECT id FROM t2));
711
ERROR HY000: You can't specify target table 't2' for update in FROM clause
unknown's avatar
unknown committed
712
SELECT * FROM t2;
713 714 715
id
1
2
unknown's avatar
unknown committed
716 717
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 values (1),(1);
unknown's avatar
unknown committed
718
UPDATE t2 SET id=(SELECT * FROM t1);
unknown's avatar
unknown committed
719
ERROR 21000: Subquery returns more than 1 row
unknown's avatar
unknown committed
720 721 722 723 724 725 726 727
drop table t2, t1;
create table t1 (a int);
insert into t1 values (1),(2),(3);
select 1 IN (SELECT * from t1);
1 IN (SELECT * from t1)
1
select 10 IN (SELECT * from t1);
10 IN (SELECT * from t1)
728
0
unknown's avatar
unknown committed
729 730
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
731
NULL
unknown's avatar
unknown committed
732 733 734
update t1 set a=NULL where a=2;
select 1 IN (SELECT * from t1);
1 IN (SELECT * from t1)
735
1
unknown's avatar
unknown committed
736 737
select 3 IN (SELECT * from t1);
3 IN (SELECT * from t1)
738
1
unknown's avatar
unknown committed
739 740
select 10 IN (SELECT * from t1);
10 IN (SELECT * from t1)
741
NULL
unknown's avatar
unknown committed
742 743
select 1 > ALL (SELECT * from t1);
1 > ALL (SELECT * from t1)
744
0
unknown's avatar
unknown committed
745 746
select 10 > ALL (SELECT * from t1);
10 > ALL (SELECT * from t1)
747
NULL
unknown's avatar
unknown committed
748 749
select 1 > ANY (SELECT * from t1);
1 > ANY (SELECT * from t1)
750
NULL
unknown's avatar
unknown committed
751 752
select 10 > ANY (SELECT * from t1);
10 > ANY (SELECT * from t1)
753
1
unknown's avatar
unknown committed
754 755 756 757 758
drop table t1;
create table t1 (a varchar(20));
insert into t1 values ('A'),('BC'),('DEF');
select 'A' IN (SELECT * from t1);
'A' IN (SELECT * from t1)
759
1
unknown's avatar
unknown committed
760 761
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
762
0
unknown's avatar
unknown committed
763 764
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
765
NULL
unknown's avatar
unknown committed
766 767 768
update t1 set a=NULL where a='BC';
select 'A' IN (SELECT * from t1);
'A' IN (SELECT * from t1)
769
1
unknown's avatar
unknown committed
770 771
select 'DEF' IN (SELECT * from t1);
'DEF' IN (SELECT * from t1)
772
1
unknown's avatar
unknown committed
773 774
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
775
NULL
unknown's avatar
unknown committed
776 777
select 'A' > ALL (SELECT * from t1);
'A' > ALL (SELECT * from t1)
778
0
unknown's avatar
unknown committed
779 780
select 'XYZS' > ALL (SELECT * from t1);
'XYZS' > ALL (SELECT * from t1)
781
NULL
unknown's avatar
unknown committed
782 783
select 'A' > ANY (SELECT * from t1);
'A' > ANY (SELECT * from t1)
784
NULL
unknown's avatar
unknown committed
785 786
select 'XYZS' > ANY (SELECT * from t1);
'XYZS' > ANY (SELECT * from t1)
787
1
unknown's avatar
unknown committed
788 789 790 791 792
drop table t1;
create table t1 (a float);
insert into t1 values (1.5),(2.5),(3.5);
select 1.5 IN (SELECT * from t1);
1.5 IN (SELECT * from t1)
793
1
unknown's avatar
unknown committed
794 795
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
796
0
unknown's avatar
unknown committed
797 798
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
799
NULL
unknown's avatar
unknown committed
800 801 802
update t1 set a=NULL where a=2.5;
select 1.5 IN (SELECT * from t1);
1.5 IN (SELECT * from t1)
803
1
unknown's avatar
unknown committed
804 805
select 3.5 IN (SELECT * from t1);
3.5 IN (SELECT * from t1)
806
1
unknown's avatar
unknown committed
807 808
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
809
NULL
unknown's avatar
unknown committed
810 811
select 1.5 > ALL (SELECT * from t1);
1.5 > ALL (SELECT * from t1)
812
0
unknown's avatar
unknown committed
813 814
select 10.5 > ALL (SELECT * from t1);
10.5 > ALL (SELECT * from t1)
815
NULL
unknown's avatar
unknown committed
816 817
select 1.5 > ANY (SELECT * from t1);
1.5 > ANY (SELECT * from t1)
818
NULL
unknown's avatar
unknown committed
819 820
select 10.5 > ANY (SELECT * from t1);
10.5 > ANY (SELECT * from t1)
821
1
unknown's avatar
unknown committed
822
explain select (select a+1) from t1;
823
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
824
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
825
Warnings:
unknown's avatar
unknown committed
826
Note	1275	Field or reference 'a' of SELECT #2 was resolved in SELECT #1
unknown's avatar
unknown committed
827
Note	1248	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
828
select (select a+1) from t1;
829 830 831 832
(select a+1)
2.5
NULL
4.5
unknown's avatar
unknown committed
833 834 835 836 837 838 839 840 841 842 843 844 845 846
drop table t1;
CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
CREATE TABLE t2 (a int(11) default '0', INDEX (a));
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1),(2),(3);
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
a	t1.a in (select t2.a from t2)
1	1
2	1
3	1
4	0
explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	Using index
847
2	DEPENDENT SUBQUERY	t2	index_subquery	a	a	5	func	2	Using index
848 849 850 851 852 853 854 855 856 857 858
CREATE TABLE t3 (a int(11) default '0');
INSERT INTO t3 VALUES (1),(2),(3);
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
a	t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
1	1
2	1
3	1
4	0
explain SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	Using index
859
2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	func	2	Using where; Using index
860 861
2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
drop table t1,t2,t3;
unknown's avatar
unknown committed
862 863
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
864
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
unknown's avatar
unknown committed
865
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
866
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
unknown's avatar
unknown committed
867
drop table t1;
868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887
create table t1 (a int, b int, c varchar(10));
create table t2 (a int);
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
insert into t2 values (1),(2),(NULL);
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;
a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a')	(select c from t1 where a=t2.a)
1	1	a
2	0	b
NULL	NULL	NULL
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;
a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b')	(select c from t1 where a=t2.a)
1	0	a
2	1	b
NULL	NULL	NULL
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;
a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c')	(select c from t1 where a=t2.a)
1	0	a
2	0	b
NULL	NULL	NULL
drop table t1,t2;
888 889 890 891 892 893 894
create table t1 (a int, b real, c varchar(10));
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
select ROW(1, 1, 'a') IN (select a,b,c from t1);
ROW(1, 1, 'a') IN (select a,b,c from t1)
1
select ROW(1, 2, 'a') IN (select a,b,c from t1);
ROW(1, 2, 'a') IN (select a,b,c from t1)
unknown's avatar
unknown committed
895
NULL
896 897
select ROW(1, 1, 'a') IN (select b,a,c from t1);
ROW(1, 1, 'a') IN (select b,a,c from t1)
unknown's avatar
unknown committed
898
1
899 900
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null)
unknown's avatar
unknown committed
901
1
902 903
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null)
unknown's avatar
unknown committed
904
0
905 906
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null)
unknown's avatar
unknown committed
907
1
908 909
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
unknown's avatar
unknown committed
910
1
911 912
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
unknown's avatar
unknown committed
913
NULL
914 915
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
unknown's avatar
unknown committed
916
1
917
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
918
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
919 920 921 922
drop table t1;
create table t1 (a int);
insert into t1 values (1);
do @a:=(SELECT a from t1);
unknown's avatar
unknown committed
923 924 925
select @a;
@a
1
unknown's avatar
unknown committed
926
set @a:=2;
unknown's avatar
merge  
unknown committed
927
set @a:=(SELECT a from t1);
unknown's avatar
unknown committed
928 929 930
select @a;
@a
1
931 932
drop table t1;
do (SELECT a from t1);
933
ERROR 42S02: Table 'test.t1' doesn't exist
unknown's avatar
unknown committed
934
set @a:=(SELECT a from t1);
935
ERROR 42S02: Table 'test.t1' doesn't exist
936 937 938
CREATE TABLE t1 (a int, KEY(a));
HANDLER t1 OPEN;
HANDLER t1 READ a=((SELECT 1));
939
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
940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961
HANDLER t1 CLOSE;
drop table t1;
create table t1 (a int);
create table t2 (b int);
insert into t1 values (1),(2);
insert into t2 values (1);
select a from t1 where a in (select a from t1 where a in (select b from t2));
a
1
drop table t1, t2;
create table t1 (a int, b int);
create table t2 like t1;
insert into t1 values (1,2),(1,3),(1,4),(1,5);
insert into t2 values (1,2),(1,3);
select * from t1 where row(a,b) in (select a,b from t2);
a	b
1	2
1	3
drop table t1, t2;
CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY  (`i`)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1);
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
962
ERROR HY000: Invalid use of group function
963
drop table t1;
unknown's avatar
unknown committed
964 965
CREATE TABLE t1 (a int(1));
EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1;
966
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
967
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
968
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
969
EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
970
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
971
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
972
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
973
EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
974
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
975
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
976
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
977
drop table t1;
978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002
CREATE TABLE `t1` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t2` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t3` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
unknown's avatar
unknown committed
1003
Warnings:
unknown's avatar
unknown committed
1004 1005
Warning	1264	Data truncated for column 'date' at row 1
Warning	1264	Data truncated for column 'date' at row 2
1006
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
unknown's avatar
unknown committed
1007
Warnings:
unknown's avatar
unknown committed
1008 1009
Warning	1264	Data truncated for column 'date' at row 1
Warning	1264	Data truncated for column 'date' at row 2
1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024
INSERT INTO t3 VALUES (1,1);
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
numeropost=topic);
topic
2
select * from t1;
mot	topic	date	pseudo
joce	1	0000-00-00	joce
test	2	0000-00-00	test
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
select * from t1;
mot	topic	date	pseudo
joce	1	0000-00-00	joce
drop table t1, t2, t3;
1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
a	(SELECT a)
1	1
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(1) NOT NULL default '0',
  `(SELECT 1)` bigint(1) NOT NULL default '0'
) TYPE=MyISAM CHARSET=latin1
drop table t1;
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(1) NOT NULL default '0',
  `(SELECT a)` bigint(1) NOT NULL default '0'
) TYPE=MyISAM CHARSET=latin1
drop table t1;
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(1) NOT NULL default '0',
  `(SELECT a+0)` bigint(17) NOT NULL default '0'
) TYPE=MyISAM CHARSET=latin1
drop table t1;
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1053 1054 1055
select * from t1;
a
2
1056 1057 1058
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
1059
  `a` bigint(17) NOT NULL default '0'
1060 1061
) TYPE=MyISAM CHARSET=latin1
drop table t1;
1062 1063 1064 1065 1066 1067
create table t1 (a int);
insert into t1 values (1), (2), (3);
explain select a,(select (select rand() from t1 limit 1)  from t1 limit 1)
from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
1068 1069
2	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
3	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
1070
drop table t1;
1071
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);
1072
ERROR 42S02: Table 'test.t1' doesn't exist
unknown's avatar
unknown committed
1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107
CREATE TABLE t1 (
ID int(11) NOT NULL auto_increment,
name char(35) NOT NULL default '',
t2 char(3) NOT NULL default '',
District char(20) NOT NULL default '',
Population int(11) NOT NULL default '0',
PRIMARY KEY  (ID)
) TYPE=MyISAM;
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
CREATE TABLE t2 (
Code char(3) NOT NULL default '',
Name char(52) NOT NULL default '',
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
Region char(26) NOT NULL default '',
SurfaceArea float(10,2) NOT NULL default '0.00',
IndepYear smallint(6) default NULL,
Population int(11) NOT NULL default '0',
LifeExpectancy float(3,1) default NULL,
GNP float(10,2) default NULL,
GNPOld float(10,2) default NULL,
LocalName char(45) NOT NULL default '',
GovernmentForm char(45) NOT NULL default '',
HeadOfState char(60) default NULL,
Capital int(11) default NULL,
Code2 char(2) NOT NULL default '',
PRIMARY KEY  (Code)
) TYPE=MyISAM;
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');
INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azrbaycan','Federal Republic','Heydr liyev',144,'AZ');
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);
Continent	Name	Population
Oceania	Sydney	3276207
drop table t1, t2;
1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120
CREATE TABLE `t1` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`id`),
UNIQUE KEY `pseudo` (`pseudo`),
) TYPE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
INSERT INTO t1 (pseudo) VALUES ('test');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
0
EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
1121
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1122 1123 1124 1125 1126 1127 1128
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
0
EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
1129
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1130
drop table t1;
1131 1132 1133 1134 1135 1136
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
PRIMARY KEY  (`i`)
) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1);
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
1137
ERROR HY000: Invalid use of group function
1138
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1139
ERROR HY000: Invalid use of group function
1140
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1141
ERROR 42S02: Unknown table 't' in field list
1142
drop table t1;
unknown's avatar
unknown committed
1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159
CREATE TABLE t1 (
id int(11) default NULL
) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
CREATE TABLE t2 (
id int(11) default NULL,
name varchar(15) default NULL
) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
select * from t2;
id	name
4	vita
1	lenka
2	lenka
1	lenka
drop table t1,t2;
1160 1161 1162 1163 1164 1165
create table t1 (a int, unique index indexa (a));
insert into t1 values (-1), (-4), (-2), (NULL);
select -10 IN (select a from t1 FORCE INDEX (indexa));
-10 IN (select a from t1 FORCE INDEX (indexa))
NULL
drop table t1;
unknown's avatar
unknown committed
1166 1167 1168 1169 1170 1171 1172
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ref	salary	salary	5	const	1	Using where
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
drop table t1;
1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186
CREATE TABLE t1 (
ID int(10) unsigned NOT NULL auto_increment,
SUB_ID int(3) unsigned NOT NULL default '0',
REF_ID int(10) unsigned default NULL,
REF_SUB int(3) unsigned default '0',
PRIMARY KEY (ID,SUB_ID),
UNIQUE KEY t1_PK (ID,SUB_ID),
KEY t1_FK (REF_ID,REF_SUB),
KEY t1_REFID (REF_ID)
) TYPE=MyISAM CHARSET=cp1251;
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
REF_ID
DROP TABLE t1;
1187 1188 1189 1190 1191 1192
create table t1 (a int, b int);
create table t2 (a int, b int);
insert into t1 values (1,0), (2,0), (3,0);
insert into t2 values (1,1), (2,1), (3,1), (2,2);
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
Warnings:
unknown's avatar
unknown committed
1193
Error	1241	Subquery returns more than 1 row
1194 1195 1196 1197 1198 1199
select * from t1;
a	b
1	1
2	NULL
3	1
drop table t1, t2;
unknown's avatar
unknown committed
1200 1201 1202 1203 1204
create table t1(City VARCHAR(30),Location geometry);
insert into t1 values("Paris",GeomFromText('POINT(2.33 48.87)'));
select City from t1 where (select intersects(GeomFromText(AsText(Location)),GeomFromText('Polygon((2 50, 2.5 50, 2.5 47, 2 47, 2 50))'))=0);
City
drop table t1;
1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218
CREATE TABLE `t1` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`pseudo` varchar(35) NOT NULL default '',
`email` varchar(60) NOT NULL default '',
PRIMARY KEY  (`id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `pseudo` (`pseudo`),
) TYPE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
a	b
test	test
test1	test1
drop table if exists t1;
unknown's avatar
unknown committed
1219 1220 1221
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
a
1
1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246
CREATE TABLE t1
(
FOLDERID VARCHAR(32)BINARY NOT NULL
, FOLDERNAME VARCHAR(255)BINARY NOT NULL
, CREATOR VARCHAR(255)BINARY
, CREATED TIMESTAMP NOT NULL
, DESCRIPTION VARCHAR(255)BINARY
, FOLDERTYPE INTEGER NOT NULL
, MODIFIED TIMESTAMP
, MODIFIER VARCHAR(255)BINARY
, FOLDERSIZE INTEGER NOT NULL
, PARENTID VARCHAR(32)BINARY
, REPID VARCHAR(32)BINARY
, ORIGINATOR INTEGER
, PRIMARY KEY ( FOLDERID )
) TYPE=InnoDB;
CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID);
CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID);
INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1");
INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1");
INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1');
'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1')
0
drop table t1;
1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260
create table t1 (a int not null, b int, primary key (a));
create table t2 (a int not null, primary key (a));
create table t3 (a int not null, b int, primary key (a));
insert into t1 values (1,10), (2,20), (3,30),  (4,40);
insert into t2 values (2), (3), (4), (5);
insert into t3 values (10,3), (20,4), (30,5);
select * from t2 where t2.a in (select a from t1);
a
2
3
4
explain select * from t2 where t2.a in (select a from t1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	4	Using where; Using index
1261
2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
1262 1263 1264 1265 1266 1267 1268
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	4	Using where; Using index
1269
2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index; Using where
1270 1271 1272 1273 1274 1275 1276 1277 1278 1279
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	4	Using where; Using index
2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where
2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using where; Using index
drop table t1, t2, t3;
1280
create table t1 (a int, b int, index a (a,b));
1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293
create table t2 (a int, index a (a));
create table t3 (a int, b int, index a (a));
insert into t1 values (1,10), (2,20), (3,30), (4,40);
insert into t2 values (2), (3), (4), (5);
insert into t3 values (10,3), (20,4), (30,5);
select * from t2 where t2.a in (select a from t1);
a
2
3
4
explain select * from t2 where t2.a in (select a from t1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	index	NULL	a	5	NULL	4	Using where; Using index
1294
2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	1001	Using index
1295 1296 1297 1298 1299 1300 1301
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	index	NULL	a	5	NULL	4	Using where; Using index
1302
2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	1001	Using index; Using where
1303 1304 1305 1306 1307 1308 1309
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	index	NULL	a	5	NULL	4	Using where; Using index
1310
2	DEPENDENT SUBQUERY	t3	index	a	a	5	NULL	3	Using index
1311
2	DEPENDENT SUBQUERY	t1	ref	a	a	10	func,test.t3.a	1000	Using where; Using index
1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
3
4
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
a
2
4
explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	index	NULL	a	5	NULL	4	Using where; Using index
1325
2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	1001	Using index; Using where
1326
drop table t1, t2, t3;
1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338
create table t1 (a int, b int);
create table t2 (a int, b int);
create table t3 (a int, b int);
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
insert into t3 values (3,3), (2,2), (1,1);
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;
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)
3	1
2	2
1	2
drop table t1,t2,t3;
1339 1340 1341 1342 1343 1344
create table t1 (s1 int);
create table t2 (s1 int);
insert into t1 values (1);
insert into t2 values (1);
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
s1
1345
1
1346
drop table t1,t2;
unknown's avatar
merge  
unknown committed
1347 1348 1349 1350
create table t1 (s1 int);
create table t2 (s1 int);
insert into t1 values (1);
insert into t2 values (1);
1351 1352 1353
update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
ERROR 42S02: Unknown table 'x' in field list
DROP TABLE t1, t2;
unknown's avatar
unknown committed
1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364
create table t1 (a int) type=innodb;
create table t2 (a int) type=innodb;
create table t3 (a int) type=innodb;
insert into t1 values (1),(2),(3),(4);
insert into t2 values (10),(20),(30),(40);
insert into t3 values (1),(2),(10),(50);
select a from t3 where t3.a in (select a from t1 where a <= 3 union select * from t2 where a <= 30);
a
1
2
10
1365
drop table t1,t2,t3;
1366 1367 1368 1369 1370 1371
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
s2 CHAR(5) COLLATE latin1_swedish_ci);
INSERT INTO t1 VALUES ('z','?');
select * from t1 where s1 > (select max(s2) from t1);
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
select * from t1 where s1 > any (select max(s2) from t1);
unknown's avatar
merge  
unknown committed
1372
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1373
drop table t1;
1374 1375 1376 1377 1378 1379 1380 1381 1382
create table t1(toid int,rd int);
create table t2(userid int,pmnew int,pmtotal int);
insert into t2 values(1,0,0),(2,0,0);
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);
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);
userid	pmtotal	pmnew	calc_total	calc_new
1	0	0	9	3
2	0	0	4	2
drop table t1, t2;
unknown's avatar
unknown committed
1383 1384
create table t1 (s1 char(5));
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
unknown's avatar
unknown committed
1385
ERROR 21000: Operand should contain 1 column(s)
unknown's avatar
unknown committed
1386 1387 1388 1389
insert into t1 values ('tttt');
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
s1
tttt
unknown's avatar
unknown committed
1390 1391 1392 1393 1394 1395
explain (select * from t1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
(select * from t1);
s1
tttt
unknown's avatar
unknown committed
1396
drop table t1;
1397 1398 1399 1400 1401 1402 1403 1404 1405
create table t1 (s1 char(5), index s1(s1));
create table t2 (s1 char(5), index s1(s1));
insert into t1 values ('a1'),('a2'),('a3');
insert into t2 values ('a1'),('a2');
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
s1	s1 NOT IN (SELECT s1 FROM t2)
a1	0
a2	0
a3	1
unknown's avatar
unknown committed
1406 1407 1408 1409 1410 1411 1412 1413 1414 1415
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
s1	s1 = ANY (SELECT s1 FROM t2)
a1	1
a2	1
a3	0
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
s1	s1 <> ALL (SELECT s1 FROM t2)
a1	0
a2	0
a3	1
1416 1417 1418 1419 1420 1421 1422 1423
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
s1	s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')
a1	0
a2	1
a3	1
explain select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
1424
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
unknown's avatar
unknown committed
1425 1426 1427 1428 1429 1430 1431 1432
explain select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
explain select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
1433 1434 1435
explain select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
1436
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	1	Using index; Using where
1437
drop table t1,t2;
1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449
create table t2 (a int, b int);
create table t3 (a int);
insert into t3 values (6),(7),(3);
select * from t3 where a >= all (select b from t2);
a
6
7
3
explain select * from t3 where a >= all (select b from t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
1450 1451 1452 1453 1454 1455 1456 1457 1458
insert into t2 values (2,2), (2,1), (3,3), (3,1);
select * from t3 where a > all (select max(b) from t2 group by a);
a
6
7
explain select * from t3 where a > all (select max(b) from t2 group by a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
1459
drop table t2, t3;
1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477
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`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
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());
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`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
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);
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`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
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);
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') TYPE=MyISAM CHARSET=latin1;
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
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;
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')
-1	Valid	1
-1	Valid 2	1
-1	Should Not Return	0
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;
dbid	name
-1	Valid
-1	Valid 2
drop table t1,t2,t3,t4;
1478 1479 1480 1481 1482 1483 1484
CREATE TABLE t1 (id int(11) default NULL) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1),(5);
CREATE TABLE t2 (id int(11) default NULL) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t2 VALUES (2),(6);
select * from t1 where (1,2,6) in (select * from t2);
ERROR 21000: Operand should contain 3 column(s)
DROP TABLE t1,t2;
1485 1486 1487 1488 1489 1490 1491
create table t1 (s1 int);
insert into t1 values (1);
insert into t1 values (2);
set sort_buffer_size = (select s1 from t1);
ERROR 21000: Subquery returns more than 1 row
do (select * from t1);
drop table t1;