subselect.result 41.8 KB
Newer Older
1
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
2 3 4
select (select 2);
(select 2)
2
5 6 7 8 9
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:
Note	1247	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 19 20
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:
Note	1247	Select 2 was reduced during optimisation
Note	1247	Select 4 was reduced during optimisation
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 30
4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1247	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
38
SELECT (SELECT a) as a;
39
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org 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
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
45
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
46 47 48
1
1
SELECT (SELECT 1), a;
49
ERROR 42S22: Unknown column 'a' in 'field list'
50
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
51 52
a
1
53
SELECT 1 FROM (SELECT (SELECT a) b) c;
54
ERROR 42S22: Unknown column 'a' in 'field list'
55
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
56 57
id
1
58
SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
59
ERROR 21000: Cardinality error (more/less than 1 columns)
60 61 62
SELECT 1 IN (SELECT 1);
1 IN (SELECT 1)
1
63
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
64 65
1
1
66
select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
67
ERROR HY000: Wrong usage of PROCEDURE and subquery
68
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
69
ERROR HY000: Incorrect parameters to procedure 'ANALYSE'
70 71 72 73 74
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
75 76 77 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
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
123
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
124
ERROR 21000: Cardinality error (more/less than 1 columns)
125 126 127
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
a	b	(SELECT b)
1	2	2
128 129 130
create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
131
create table t4 (a int not null, b int not null);
132 133 134
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
135
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
136
ERROR 42S22: Reference 'a1' not supported (forward reference in item list)
137 138 139 140 141 142 143 144
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
145 146 147 148 149 150 151 152 153 154 155 156
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);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
157
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
158 159 160
a	b
1	7
2	7
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
161
(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;
162 163 164 165
a	b
1	7
2	7
3	8
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
166
(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);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
167 168 169 170 171
a	b
1	7
2	7
3	8
4	8
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
172
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);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
173
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
174
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
175
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using filesort
176
3	UNION	t4	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
177
4	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
178 179 180 181
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
182 183 184 185
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
186 187 188
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
189
1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	
190
3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
191
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
192 193 194 195 196 197 198 199 200 201 202
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
203 204
8	4.5000
9	7.5000
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
205 206
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
207
1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
208 209
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
210 211 212 213 214 215 216
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
217 218 219 220 221 222 223
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
224 225 226 227 228 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
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
select * from t3 where a >= all (select b from t2);
a
7
delete from t2 where a=100;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
254
select * from t3 where a in (select a,b from t2);
255
ERROR 21000: Cardinality error (more/less than 1 columns)
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
256
select * from t3 where a in (select * from t2);
257 258 259
ERROR 21000: Cardinality error (more/less than 1 columns)
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);
260
b	ma
261 262 263 264 265 266
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);
267 268
b	ma
7	12
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
269 270 271 272 273 274 275 276 277 278 279 280 281 282 283
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
284 285
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
286
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
287
2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
288
3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
289
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
290
ERROR 21000: Subselect returns more than 1 record
291 292 293 294 295
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);
296 297 298 299
patient_uq	clinic_uq
1	1
1	2
2	2
300 301 302
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
303
2	DEPENDENT SUBQUERY	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.clinic_uq	1	
304
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
305
ERROR 23000: Column: 'a' in field list is ambiguous
306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321
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
322
CREATE TABLE `t8` (
323 324 325 326 327
`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;
328 329 330 331
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');
332
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
333
1	PRIMARY	t8	const	PRIMARY	PRIMARY	35	const	1	
334 335 336
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	
337 338
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
339
ERROR 21000: Cardinality error (more/less than 1 columns)
340
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
341
pseudo='joce');
342
ERROR 21000: Cardinality error (more/less than 1 columns)
343
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
344 345
pseudo
joce
346
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
347
ERROR 21000: Subselect returns more than 1 record
348 349
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
CREATE TABLE `t1` (
350 351 352 353 354 355
`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;
356
INSERT INTO t1 (topic,date,pseudo) VALUES
357
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
358
EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
359
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
360 361
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');
362
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
363
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
364
2	SUBQUERY	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
365
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
366 367
date
2002-08-03
368 369
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
370
2002-08-03
371
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
372 373 374 375
1
1
1
1
376
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
377
ERROR 21000: Subselect returns more than 1 record
378
EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
379
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
380
1	PRIMARY	t1	index	NULL	topic	3	NULL	2	Using index
381
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
382
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
383
drop table t1;
384
CREATE TABLE `t1` (
385 386 387 388 389
`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;
390 391
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
CREATE TABLE `t2` (
392 393 394 395 396 397
`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;
398 399
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);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
400 401
a
40143
402
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;
403 404 405
numeropost	maxnumrep
43506	2
40143	1
406
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
407
ERROR 42S22: Unknown column 'a' in 'having clause'
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
408
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
409
ERROR 42S22: Unknown column 'a' in 'having clause'
410
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
411 412
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
413 414
joce	43506	2002-10-22	joce
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
415 416 417
mot	topic	date	pseudo
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
mot	topic	date	pseudo
418
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
419 420
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
421 422
joce	43506	2002-10-22	joce
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
423 424 425
mot	topic	date	pseudo
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
mot	topic	date	pseudo
426
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
427
mot	topic	date	pseudo
428
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
429 430
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
431
joce	43506	2002-10-22	joce
432 433 434 435
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
436 437 438 439 440 441
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
442 443 444 445 446 447 448 449 450 451 452 453 454
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
455
drop table t1,t2;
456
CREATE TABLE `t1` (
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
457 458 459 460 461
`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;
462 463
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);
464
ERROR 21000: Subselect returns more than 1 record
465
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
466
ERROR 21000: Subselect returns more than 1 record
467
drop table t1;
468 469 470 471 472 473 474 475
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;
476 477
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO t1 VALUES ();
478
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
479
ERROR 21000: Subselect returns more than 1 record
480 481
drop table t1;
CREATE TABLE `t1` (
482 483 484 485 486 487 488
`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;
489
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
490
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
491
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
492
ERROR 42S22: Unknown column 'a' in 'having clause'
493 494 495 496
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');
497
ERROR 21000: Subselect returns more than 1 record
498
EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
499
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
500
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
501
EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
502
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
503
1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	
504
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
505
drop table t1;
506 507
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
508
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
509 510 511
1
1
drop table t1;
512 513 514 515 516 517 518 519 520
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
521
update t1 set b= (select b from t1);
522
ERROR HY000: You can't specify target table 't1' for update in FROM clause
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
523
update t1 set b= (select b from t2);
524
ERROR 21000: Subselect returns more than 1 record
525 526 527 528 529 530 531
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;
532 533 534 535 536 537 538 539 540 541 542 543
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
544
delete from t1 where b = (select b from t1);
545
ERROR HY000: You can't specify target table 't1' for update in FROM clause
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
546
delete from t1 where b = (select b from t2);
547
ERROR 21000: Subselect returns more than 1 record
548 549 550 551 552 553
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;
554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569
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
570
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
571
ERROR HY000: You can't specify target table 't12' for update in FROM clause
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
572
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
573
ERROR 21000: Subselect returns more than 1 record
574 575 576 577 578 579 580 581 582 583
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;
584 585
CREATE TABLE t1 (x int);
create table t2 (a int);
586
create table t3 (b int);
587
insert into t2 values (1);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
588
insert into t3 values (1),(2);
589
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
590
ERROR HY000: You can't specify target table 't1' for update in FROM clause
591
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
592
ERROR 21000: Subselect returns more than 1 record
593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610
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;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
611 612 613
ERROR HY000: You can't specify target table 't1' for update in FROM clause
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
ERROR 42S22: Unknown column 'x' in 'field list'
614
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
615 616 617 618 619 620
select * from t1;
x
1
2
3
3
621
2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
622
drop table t1, t2, t3;
623 624
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
625
create table t3 (a int);
626
insert into t2 values (1);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
627
insert into t3 values (1),(2);
628 629
select * from t1;
x	y
630
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
631
ERROR HY000: You can't specify target table 't1' for update in FROM clause
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
632
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
633
ERROR 21000: Subselect returns more than 1 record
634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
658
drop table t1, t2, t3;
659
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
660
ERROR HY000: No tables used
661 662 663
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);
664 665
id
1
666
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1);
667
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
668
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
669
Warnings:
670
Note	1247	Select 2 was reduced during optimisation
671
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
672 673
id
1
674
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
675 676
id
2
677
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
678
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
679
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
680
3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
681
Warnings:
682
Note	1247	Select 3 was reduced during optimisation
683
Note	1247	Select 2 was reduced during optimisation
684
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
685
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
686
1	PRIMARY	t2	index	NULL	id	5	NULL	2	Using where; Using index
687
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
688
3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
689
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
690
id
691
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
692 693
id
2
694
INSERT INTO t2 VALUES ((SELECT * FROM t2));
695
ERROR HY000: You can't specify target table 't2' for update in FROM clause
696
INSERT INTO t2 VALUES ((SELECT id FROM t2));
697
ERROR HY000: You can't specify target table 't2' for update in FROM clause
698
SELECT * FROM t2;
699 700 701
id
1
2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
702 703
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 values (1),(1);
704
UPDATE t2 SET id=(SELECT * FROM t1);
705
ERROR 21000: Subselect returns more than 1 record
706 707 708 709 710 711 712 713
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)
714
0
715 716
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
717
NULL
718 719 720
update t1 set a=NULL where a=2;
select 1 IN (SELECT * from t1);
1 IN (SELECT * from t1)
721
1
722 723
select 3 IN (SELECT * from t1);
3 IN (SELECT * from t1)
724
1
725 726
select 10 IN (SELECT * from t1);
10 IN (SELECT * from t1)
727
NULL
728 729
select 1 > ALL (SELECT * from t1);
1 > ALL (SELECT * from t1)
730
0
731 732
select 10 > ALL (SELECT * from t1);
10 > ALL (SELECT * from t1)
733
NULL
734 735
select 1 > ANY (SELECT * from t1);
1 > ANY (SELECT * from t1)
736
NULL
737 738
select 10 > ANY (SELECT * from t1);
10 > ANY (SELECT * from t1)
739
1
740 741 742 743 744
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)
745
1
746 747
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
748
0
749 750
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
751
NULL
752 753 754
update t1 set a=NULL where a='BC';
select 'A' IN (SELECT * from t1);
'A' IN (SELECT * from t1)
755
1
756 757
select 'DEF' IN (SELECT * from t1);
'DEF' IN (SELECT * from t1)
758
1
759 760
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
761
NULL
762 763
select 'A' > ALL (SELECT * from t1);
'A' > ALL (SELECT * from t1)
764
0
765 766
select 'XYZS' > ALL (SELECT * from t1);
'XYZS' > ALL (SELECT * from t1)
767
NULL
768 769
select 'A' > ANY (SELECT * from t1);
'A' > ANY (SELECT * from t1)
770
NULL
771 772
select 'XYZS' > ANY (SELECT * from t1);
'XYZS' > ANY (SELECT * from t1)
773
1
774 775 776 777 778
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)
779
1
780 781
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
782
0
783 784
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
785
NULL
786 787 788
update t1 set a=NULL where a=2.5;
select 1.5 IN (SELECT * from t1);
1.5 IN (SELECT * from t1)
789
1
790 791
select 3.5 IN (SELECT * from t1);
3.5 IN (SELECT * from t1)
792
1
793 794
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
795
NULL
796 797
select 1.5 > ALL (SELECT * from t1);
1.5 > ALL (SELECT * from t1)
798
0
799 800
select 10.5 > ALL (SELECT * from t1);
10.5 > ALL (SELECT * from t1)
801
NULL
802 803
select 1.5 > ANY (SELECT * from t1);
1.5 > ANY (SELECT * from t1)
804
NULL
805 806
select 10.5 > ANY (SELECT * from t1);
10.5 > ANY (SELECT * from t1)
807
1
808
explain select (select a+1) from t1;
809
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
810
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
811 812
Warnings:
Note	1247	Select 2 was reduced during optimisation
813
select (select a+1) from t1;
814 815 816 817
(select a+1)
2.5
NULL
4.5
818 819 820 821 822 823 824 825 826 827 828 829 830 831
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
832
2	DEPENDENT SUBQUERY	t2	index	a	a	5	NULL	3	Using where; Using index
833 834 835
drop table t1,t2;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
836
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
837
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
838
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
839
drop table t1;
840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859
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;
860 861 862 863 864 865 866
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)
867
NULL
868 869
select ROW(1, 1, 'a') IN (select b,a,c from t1);
ROW(1, 1, 'a') IN (select b,a,c from t1)
870
1
871 872
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)
873
1
874 875
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)
876
0
877 878
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)
879
1
880 881
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')
882
1
883 884
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')
885
NULL
886 887
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')
888
1
889
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
890
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
891 892 893 894
drop table t1;
create table t1 (a int);
insert into t1 values (1);
do @a:=(SELECT a from t1);
895 896 897
select @a;
@a
1
898
set @a:=2;
monty@mashka.mysql.fi's avatar
merge  
monty@mashka.mysql.fi committed
899
set @a:=(SELECT a from t1);
900 901 902
select @a;
@a
1
903 904
drop table t1;
do (SELECT a from t1);
905
ERROR 42S02: Table 'test.t1' doesn't exist
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
906
set @a:=(SELECT a from t1);
907
ERROR 42S02: Table 'test.t1' doesn't exist
908 909 910
CREATE TABLE t1 (a int, KEY(a));
HANDLER t1 OPEN;
HANDLER t1 READ a=((SELECT 1));
911
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
912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933
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));
934
ERROR HY000: Invalid use of group function
935
drop table t1;
936 937
CREATE TABLE t1 (a int(1));
EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1;
938
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
939
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
940
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
941
EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
942
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
943
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
944
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
945
EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
946
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
947
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
948
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
949
drop table t1;
950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974
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');
venu@myvenu.com's avatar
venu@myvenu.com committed
975 976 977
Warnings:
Warning	1263	Data truncated for column 'date' at row 1
Warning	1263	Data truncated for column 'date' at row 2
978
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
venu@myvenu.com's avatar
venu@myvenu.com committed
979 980 981
Warnings:
Warning	1263	Data truncated for column 'date' at row 1
Warning	1263	Data truncated for column 'date' at row 2
982 983 984 985 986 987 988 989 990 991 992 993 994 995 996
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;
997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027
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;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
1028
  `a` bigint(1) NOT NULL default '0'
1029 1030
) TYPE=MyISAM CHARSET=latin1
drop table t1;
1031 1032 1033 1034 1035 1036
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	
1037 1038
2	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
3	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
1039
drop table t1;
1040
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);
1041
ERROR 42S02: Table 'test.t1' doesn't exist
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076
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;
1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089
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
1090
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1091 1092 1093 1094 1095 1096 1097
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
1098
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1099
drop table t1;
1100 1101 1102 1103 1104 1105
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));
1106
ERROR HY000: Invalid use of group function
1107
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1108
ERROR HY000: Invalid use of group function
1109
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1110
ERROR 42S02: Unknown table 't' in field list
1111
drop table t1;
1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128
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;
1129 1130 1131 1132 1133 1134
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;
1135 1136 1137 1138 1139 1140 1141
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;
1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155
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;
1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169
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;