subselect.result 51.9 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
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:
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi 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:
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
19 20
Note	1248	Select 2 was reduced during optimisation
Note	1248	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
4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi 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
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
45
Warnings:
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
48
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
49 50 51
1
1
SELECT (SELECT 1), a;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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);
59 60
id
1
61
SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
62
ERROR 21000: Cardinality error (more/less than 1 columns)
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));
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'
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
126
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
127
ERROR 21000: Cardinality error (more/less than 1 columns)
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);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
170 171 172 173 174
a	b
1	7
2	7
3	8
4	8
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
176
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
177
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
178
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using filesort
179
3	UNION	t4	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
180
4	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
192
1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
206 207
8	4.5000
9	7.5000
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
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:
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
214
Note	1275	Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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 255 256 257 258
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
259
select * from t3 where a in (select a,b from t2);
260
ERROR 21000: Cardinality error (more/less than 1 columns)
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
261
select * from t3 where a in (select * from t2);
262 263 264
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);
265
b	ma
266 267 268 269 270 271
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);
272 273
b	ma
7	12
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
274 275 276 277 278 279 280 281 282 283 284 285 286 287 288
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
289 290
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
291
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
292
2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
293
3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
294
Warnings:
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
295 296
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
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
297
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
298
ERROR 21000: Subselect returns more than 1 record
299 300 301 302 303
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);
304 305 306 307
patient_uq	clinic_uq
1	1
1	2
2	2
308 309 310
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
311
2	DEPENDENT SUBQUERY	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.clinic_uq	1	
312
Warnings:
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
313
Note	1275	Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
314
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
315
ERROR 23000: Column: 'a' in field list is ambiguous
316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331
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
332
CREATE TABLE `t8` (
333 334 335 336 337
`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;
338 339 340 341
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');
342
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
343
1	PRIMARY	t8	const	PRIMARY	PRIMARY	35	const	1	
344 345 346
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	
347 348
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
349
ERROR 21000: Cardinality error (more/less than 1 columns)
350
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
351
pseudo='joce');
352
ERROR 21000: Cardinality error (more/less than 1 columns)
353
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
354 355
pseudo
joce
356
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
357
ERROR 21000: Subselect returns more than 1 record
358 359
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
CREATE TABLE `t1` (
360 361 362 363 364 365
`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;
366
INSERT INTO t1 (topic,date,pseudo) VALUES
367
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
368
EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
369
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
370 371
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');
372
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
373
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
374
2	SUBQUERY	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
375
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
376 377
date
2002-08-03
378 379
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
380
2002-08-03
381
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
382 383 384 385
1
1
1
1
386
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
387
ERROR 21000: Subselect returns more than 1 record
388
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
389
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
390
1	PRIMARY	t1	index	NULL	topic	3	NULL	2	Using index
391
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
392
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
393
drop table t1;
394
CREATE TABLE `t1` (
395 396 397 398 399
`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;
400 401
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
CREATE TABLE `t2` (
402 403 404 405 406 407
`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;
408 409
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
410 411
a
40143
412
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;
413 414 415
numeropost	maxnumrep
43506	2
40143	1
416
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
417
ERROR 42S22: Unknown column 'a' in 'having clause'
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
418
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
419
ERROR 42S22: Unknown column 'a' in 'having clause'
420
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
421 422
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
423 424
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
425 426 427
mot	topic	date	pseudo
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
mot	topic	date	pseudo
428
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
429 430
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
431 432
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
433 434 435
mot	topic	date	pseudo
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
mot	topic	date	pseudo
436
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
437
mot	topic	date	pseudo
438
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
439 440
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
441
joce	43506	2002-10-22	joce
442 443 444 445
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
446 447 448 449 450 451
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
452 453 454 455 456 457 458 459 460 461 462 463 464
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
465
drop table t1,t2;
466
CREATE TABLE `t1` (
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
467 468 469 470 471
`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;
472 473
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);
474
ERROR 21000: Subselect returns more than 1 record
475
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
476
ERROR 21000: Subselect returns more than 1 record
477
drop table t1;
478 479 480 481 482 483 484 485
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;
486 487
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO t1 VALUES ();
488
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
489
ERROR 21000: Subselect returns more than 1 record
490 491
drop table t1;
CREATE TABLE `t1` (
492 493 494 495 496 497 498
`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;
499
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
500
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
501
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
502
ERROR 42S22: Unknown column 'a' in 'having clause'
503 504 505 506
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');
507
ERROR 21000: Subselect returns more than 1 record
508
EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
509
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
510
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
511
EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
512
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
513
1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	
514
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
515
drop table t1;
516 517
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
518
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
519 520 521
1
1
drop table t1;
522 523 524 525 526 527 528 529 530
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
531
update t1 set b= (select b from t1);
532
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
533
update t1 set b= (select b from t2);
534
ERROR 21000: Subselect returns more than 1 record
535 536 537 538 539 540 541
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;
542 543 544 545 546 547 548 549 550 551 552 553
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
554
delete from t1 where b = (select b from t1);
555
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
556
delete from t1 where b = (select b from t2);
557
ERROR 21000: Subselect returns more than 1 record
558 559 560 561 562 563
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;
564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579
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
580
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
581
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
582
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
583
ERROR 21000: Subselect returns more than 1 record
584 585 586 587 588 589 590 591 592 593
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;
594 595
CREATE TABLE t1 (x int);
create table t2 (a int);
596
create table t3 (b int);
597
insert into t2 values (1);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
598
insert into t3 values (1),(2);
599
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
600
ERROR HY000: You can't specify target table 't1' for update in FROM clause
601
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
602
ERROR 21000: Subselect returns more than 1 record
603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620
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
621 622
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
ERROR 42S22: Unknown column 'x' in 'field list'
623
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
624 625 626 627 628 629
select * from t1;
x
1
2
3
3
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
630 631
11
11
632
2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
633
drop table t1, t2, t3;
634 635
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
636
create table t3 (a int);
637
insert into t2 values (1);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
638
insert into t3 values (1),(2);
639 640
select * from t1;
x	y
641
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
642
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
643
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
644
ERROR 21000: Subselect returns more than 1 record
645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668
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
669
drop table t1, t2, t3;
670
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
671
ERROR HY000: No tables used
672 673 674
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);
675 676
id
1
677
EXPLAIN SELECT * FROM t2 WHERE id IN (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
Warnings:
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
681
Note	1248	Select 2 was reduced during optimisation
682
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
683 684
id
1
685
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
686 687
id
2
688
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
689
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
690
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
691
Warnings:
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
692 693
Note	1248	Select 3 was reduced during optimisation
Note	1248	Select 2 was reduced during optimisation
694
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
695
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
696
1	PRIMARY	t2	index	NULL	id	5	NULL	2	Using where; Using index
697
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
698
3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
699
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
700
id
701
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
702 703
id
2
704
INSERT INTO t2 VALUES ((SELECT * FROM t2));
705
ERROR HY000: You can't specify target table 't2' for update in FROM clause
706
INSERT INTO t2 VALUES ((SELECT id FROM t2));
707
ERROR HY000: You can't specify target table 't2' for update in FROM clause
708
SELECT * FROM t2;
709 710 711
id
1
2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
712 713
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 values (1),(1);
714
UPDATE t2 SET id=(SELECT * FROM t1);
715
ERROR 21000: Subselect returns more than 1 record
716 717 718 719 720 721 722 723
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)
724
0
725 726
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
727
NULL
728 729 730
update t1 set a=NULL where a=2;
select 1 IN (SELECT * from t1);
1 IN (SELECT * from t1)
731
1
732 733
select 3 IN (SELECT * from t1);
3 IN (SELECT * from t1)
734
1
735 736
select 10 IN (SELECT * from t1);
10 IN (SELECT * from t1)
737
NULL
738 739
select 1 > ALL (SELECT * from t1);
1 > ALL (SELECT * from t1)
740
0
741 742
select 10 > ALL (SELECT * from t1);
10 > ALL (SELECT * from t1)
743
NULL
744 745
select 1 > ANY (SELECT * from t1);
1 > ANY (SELECT * from t1)
746
NULL
747 748
select 10 > ANY (SELECT * from t1);
10 > ANY (SELECT * from t1)
749
1
750 751 752 753 754
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)
755
1
756 757
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
758
0
759 760
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
761
NULL
762 763 764
update t1 set a=NULL where a='BC';
select 'A' IN (SELECT * from t1);
'A' IN (SELECT * from t1)
765
1
766 767
select 'DEF' IN (SELECT * from t1);
'DEF' IN (SELECT * from t1)
768
1
769 770
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
771
NULL
772 773
select 'A' > ALL (SELECT * from t1);
'A' > ALL (SELECT * from t1)
774
0
775 776
select 'XYZS' > ALL (SELECT * from t1);
'XYZS' > ALL (SELECT * from t1)
777
NULL
778 779
select 'A' > ANY (SELECT * from t1);
'A' > ANY (SELECT * from t1)
780
NULL
781 782
select 'XYZS' > ANY (SELECT * from t1);
'XYZS' > ANY (SELECT * from t1)
783
1
784 785 786 787 788
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)
789
1
790 791
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
792
0
793 794
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
795
NULL
796 797 798
update t1 set a=NULL where a=2.5;
select 1.5 IN (SELECT * from t1);
1.5 IN (SELECT * from t1)
799
1
800 801
select 3.5 IN (SELECT * from t1);
3.5 IN (SELECT * from t1)
802
1
803 804
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
805
NULL
806 807
select 1.5 > ALL (SELECT * from t1);
1.5 > ALL (SELECT * from t1)
808
0
809 810
select 10.5 > ALL (SELECT * from t1);
10.5 > ALL (SELECT * from t1)
811
NULL
812 813
select 1.5 > ANY (SELECT * from t1);
1.5 > ANY (SELECT * from t1)
814
NULL
815 816
select 10.5 > ANY (SELECT * from t1);
10.5 > ANY (SELECT * from t1)
817
1
818
explain select (select a+1) from t1;
819
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
820
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
821
Warnings:
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
822
Note	1275	Field or reference 'a' of SELECT #2 was resolved in SELECT #1
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
823
Note	1248	Select 2 was reduced during optimisation
824
select (select a+1) from t1;
825 826 827 828
(select a+1)
2.5
NULL
4.5
829 830 831 832 833 834 835 836 837 838 839 840 841 842
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
843
2	DEPENDENT SUBQUERY	t2	index_subquery	a	a	5	func	2	Using index
844 845 846 847 848 849 850 851 852 853 854
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
855
2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	func	2	Using where; Using index
856 857
2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
drop table t1,t2,t3;
858 859
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
860
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
861
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
862
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
863
drop table t1;
864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883
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;
884 885 886 887 888 889 890
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)
891
NULL
892 893
select ROW(1, 1, 'a') IN (select b,a,c from t1);
ROW(1, 1, 'a') IN (select b,a,c from t1)
894
1
895 896
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)
897
1
898 899
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)
900
0
901 902
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)
903
1
904 905
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')
906
1
907 908
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')
909
NULL
910 911
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')
912
1
913
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
914
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
915 916 917 918
drop table t1;
create table t1 (a int);
insert into t1 values (1);
do @a:=(SELECT a from t1);
919 920 921
select @a;
@a
1
922
set @a:=2;
monty@mashka.mysql.fi's avatar
merge  
monty@mashka.mysql.fi committed
923
set @a:=(SELECT a from t1);
924 925 926
select @a;
@a
1
927 928
drop table t1;
do (SELECT a from t1);
929
ERROR 42S02: Table 'test.t1' doesn't exist
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
930
set @a:=(SELECT a from t1);
931
ERROR 42S02: Table 'test.t1' doesn't exist
932 933 934
CREATE TABLE t1 (a int, KEY(a));
HANDLER t1 OPEN;
HANDLER t1 READ a=((SELECT 1));
935
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
936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957
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));
958
ERROR HY000: Invalid use of group function
959
drop table t1;
960 961
CREATE TABLE t1 (a int(1));
EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1;
962
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
963
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
964
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
965
EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
966
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
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
969
EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
970
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
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
973
drop table t1;
974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998
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
999
Warnings:
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
1000 1001
Warning	1264	Data truncated for column 'date' at row 1
Warning	1264	Data truncated for column 'date' at row 2
1002
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
venu@myvenu.com's avatar
venu@myvenu.com committed
1003
Warnings:
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
1004 1005
Warning	1264	Data truncated for column 'date' at row 1
Warning	1264	Data truncated for column 'date' at row 2
1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020
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;
1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048
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;
1049 1050 1051
select * from t1;
a
2
1052 1053 1054
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
1055
  `a` bigint(17) NOT NULL default '0'
1056 1057
) TYPE=MyISAM CHARSET=latin1
drop table t1;
1058 1059 1060 1061 1062 1063
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	
1064 1065
2	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
3	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
1066
drop table t1;
1067
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);
1068
ERROR 42S02: Table 'test.t1' doesn't exist
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
1069 1070 1071 1072 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
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;
1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116
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
1117
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1118 1119 1120 1121 1122 1123 1124
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
1125
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1126
drop table t1;
1127 1128 1129 1130 1131 1132
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));
1133
ERROR HY000: Invalid use of group function
1134
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1135
ERROR HY000: Invalid use of group function
1136
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1137
ERROR 42S02: Unknown table 't' in field list
1138
drop table t1;
1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155
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;
1156 1157 1158 1159 1160 1161
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;
1162 1163 1164 1165 1166 1167 1168
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;
1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182
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;
1183 1184 1185 1186 1187 1188
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:
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
1189
Error	1241	Subselect returns more than 1 record
1190 1191 1192 1193 1194 1195
select * from t1;
a	b
1	1
2	NULL
3	1
drop table t1, t2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
1196 1197 1198 1199 1200
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;
1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214
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;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
1215 1216 1217
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
a
1
1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242
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;
1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256
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
1257
2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
1258 1259 1260 1261 1262 1263 1264
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
1265
2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index; Using where
1266 1267 1268 1269 1270 1271 1272 1273 1274 1275
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;
1276
create table t1 (a int, b int, index a (a,b));
1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289
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
1290
2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	1001	Using index
1291 1292 1293 1294 1295 1296 1297
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
1298
2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	1001	Using index; Using where
1299 1300 1301 1302 1303 1304 1305
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
1306
2	DEPENDENT SUBQUERY	t3	index	a	a	5	NULL	3	Using index
1307
2	DEPENDENT SUBQUERY	t1	ref	a	a	10	func,test.t3.a	1000	Using where; Using index
1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320
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
1321
2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	1001	Using index; Using where
1322
drop table t1, t2, t3;
1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334
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;
1335 1336 1337 1338 1339 1340 1341
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
drop table t1,t2;
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1342 1343 1344 1345
create table t1 (s1 int);
create table t2 (s1 int);
insert into t1 values (1);
insert into t2 values (1);
1346 1347 1348
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;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360
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
drop table t1,t2;
1361 1362 1363 1364 1365 1366
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);
bell@laptop.sanja.is.com.ua's avatar
merge  
bell@laptop.sanja.is.com.ua committed
1367
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1368
drop table t1;
1369 1370 1371 1372 1373 1374 1375 1376 1377
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;
1378 1379 1380 1381 1382 1383 1384
create table t1 (s1 char(5));
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
ERROR 21000: Cardinality error (more/less than 1 columns)
insert into t1 values ('tttt');
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
s1
tttt
1385 1386 1387 1388 1389 1390
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
1391
drop table t1;
1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408
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
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
1409
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
1410 1411 1412
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
1413
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	1	Using index; Using where
1414
drop table t1,t2;