subselect.result 49.4 KB
Newer Older
1
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
unknown's avatar
unknown committed
2 3 4
select (select 2);
(select 2)
2
5 6 7 8
explain select (select 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
9
Note	1248	Select 2 was reduced during optimisation
10 11 12 13
SELECT (SELECT 1) UNION SELECT (SELECT 2);
(SELECT 1)
1
2
14 15 16 17 18
explain SELECT (SELECT 1) UNION SELECT (SELECT 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
19 20
Note	1248	Select 2 was reduced during optimisation
Note	1248	Select 4 was reduced during optimisation
unknown's avatar
unknown committed
21 22 23
SELECT (SELECT (SELECT 0 UNION SELECT 0));
(SELECT (SELECT 0 UNION SELECT 0))
0
24 25 26
explain SELECT (SELECT (SELECT 0 UNION SELECT 0));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
27
3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
28 29
4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
30
Note	1248	Select 2 was reduced during optimisation
31
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
32
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
33
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b;
34
ERROR 42S22: Reference 'b' not supported (forward reference in item list)
35
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
36 37
(SELECT 1)	MAX(1)
1	1
unknown's avatar
unknown committed
38
SELECT (SELECT a) as a;
39
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
unknown's avatar
unknown committed
40
EXPLAIN SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
41 42
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
43
3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
44
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
45
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
46 47 48
1
1
SELECT (SELECT 1), a;
unknown's avatar
unknown committed
49
ERROR 42S22: Unknown column 'a' in 'checking transformed subquery'
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);
unknown's avatar
unknown committed
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)
unknown's avatar
unknown committed
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));
unknown's avatar
unknown committed
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'
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
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);
unknown's avatar
unknown 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
unknown's avatar
unknown 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
unknown's avatar
unknown 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);
unknown's avatar
unknown committed
167 168 169 170 171
a	b
1	7
2	7
3	8
4	8
unknown's avatar
unknown 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);
unknown's avatar
unknown committed
173
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
174
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
175
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using filesort
unknown's avatar
unknown committed
176
3	UNION	t4	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
177
4	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
unknown's avatar
unknown 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
unknown's avatar
unknown 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
unknown's avatar
unknown 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
unknown's avatar
unknown committed
189
1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	
unknown's avatar
unknown committed
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
unknown's avatar
unknown 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
unknown's avatar
unknown committed
203 204
8	4.5000
9	7.5000
unknown's avatar
unknown 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
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
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
unknown's avatar
unknown 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
unknown's avatar
unknown 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;
unknown's avatar
unknown committed
254
select * from t3 where a in (select a,b from t2);
255
ERROR 21000: Cardinality error (more/less than 1 columns)
unknown's avatar
unknown 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);
unknown's avatar
unknown committed
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);
unknown's avatar
unknown committed
267 268
b	ma
7	12
unknown's avatar
unknown 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
unknown's avatar
unknown 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
unknown's avatar
unknown committed
286
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
287
2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
unknown's avatar
unknown committed
288
3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
unknown's avatar
unknown 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
unknown's avatar
unknown committed
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` (
unknown's avatar
unknown committed
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');
unknown's avatar
unknown committed
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
unknown's avatar
unknown 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` (
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
357
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
358
EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
unknown's avatar
unknown committed
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');
unknown's avatar
unknown committed
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';
unknown's avatar
unknown committed
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')
unknown's avatar
unknown committed
370
2002-08-03
371
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
unknown's avatar
unknown 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);
unknown's avatar
unknown 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
unknown's avatar
unknown committed
383
drop table t1;
384
CREATE TABLE `t1` (
unknown's avatar
unknown committed
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` (
unknown's avatar
unknown committed
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);
unknown's avatar
unknown 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;
unknown's avatar
unknown committed
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'
unknown's avatar
unknown 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);
unknown's avatar
unknown 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);
unknown's avatar
unknown 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);
unknown's avatar
unknown 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);
unknown's avatar
unknown 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);
unknown's avatar
unknown committed
427
mot	topic	date	pseudo
428
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
unknown's avatar
unknown 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
unknown's avatar
unknown 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
unknown's avatar
unknown committed
455
drop table t1,t2;
456
CREATE TABLE `t1` (
unknown's avatar
unknown 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` (
unknown's avatar
unknown committed
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';
unknown's avatar
unknown committed
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');
unknown's avatar
unknown committed
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);
unknown's avatar
unknown 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
unknown's avatar
unknown 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;
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
546
delete from t1 where b = (select b from t2);
547
ERROR 21000: Subselect returns more than 1 record
unknown's avatar
unknown committed
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
unknown's avatar
unknown 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);
unknown's avatar
unknown 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;
unknown's avatar
unknown committed
611 612
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
ERROR 42S22: Unknown column 'x' in 'field list'
613
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
614 615 616 617 618 619
select * from t1;
x
1
2
3
3
unknown's avatar
unknown committed
620 621
11
11
622
2
unknown's avatar
unknown committed
623
drop table t1, t2, t3;
624 625
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
unknown's avatar
unknown committed
626
create table t3 (a int);
627
insert into t2 values (1);
unknown's avatar
unknown committed
628
insert into t3 values (1),(2);
629 630
select * from t1;
x	y
631
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
632
ERROR HY000: You can't specify target table 't1' for update in FROM clause
unknown's avatar
unknown committed
633
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
634
ERROR 21000: Subselect returns more than 1 record
635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
select * from t1;
x	y
1	2
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
select * from t1;
x	y
1	3
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
select * from t1;
x	y
1	3
4	1
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
select * from t1;
x	y
1	3
4	2
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
select * from t1;
x	y
1	3
4	2
2	1
unknown's avatar
unknown committed
659
drop table t1, t2, t3;
660
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
661
ERROR HY000: No tables used
unknown's avatar
unknown committed
662 663 664
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t2 VALUES (1),(2);
SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
665 666
id
1
unknown's avatar
unknown committed
667
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
668
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
669
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
unknown's avatar
unknown committed
670
Warnings:
unknown's avatar
unknown committed
671
Note	1248	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
672
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
673 674
id
1
unknown's avatar
unknown committed
675
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
676 677
id
2
unknown's avatar
unknown committed
678
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
679
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
680
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
unknown's avatar
unknown committed
681
Warnings:
unknown's avatar
unknown committed
682 683
Note	1248	Select 3 was reduced during optimisation
Note	1248	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
684
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
685
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
688
3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
689
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
unknown's avatar
unknown committed
690
id
unknown's avatar
unknown committed
691
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
unknown's avatar
unknown committed
692 693
id
2
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
698
SELECT * FROM t2;
699 700 701
id
1
2
unknown's avatar
unknown committed
702 703
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 values (1),(1);
unknown's avatar
unknown committed
704
UPDATE t2 SET id=(SELECT * FROM t1);
705
ERROR 21000: Subselect returns more than 1 record
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
715 716
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
717
NULL
unknown's avatar
unknown committed
718 719 720
update t1 set a=NULL where a=2;
select 1 IN (SELECT * from t1);
1 IN (SELECT * from t1)
721
1
unknown's avatar
unknown committed
722 723
select 3 IN (SELECT * from t1);
3 IN (SELECT * from t1)
724
1
unknown's avatar
unknown committed
725 726
select 10 IN (SELECT * from t1);
10 IN (SELECT * from t1)
727
NULL
unknown's avatar
unknown committed
728 729
select 1 > ALL (SELECT * from t1);
1 > ALL (SELECT * from t1)
730
0
unknown's avatar
unknown committed
731 732
select 10 > ALL (SELECT * from t1);
10 > ALL (SELECT * from t1)
733
NULL
unknown's avatar
unknown committed
734 735
select 1 > ANY (SELECT * from t1);
1 > ANY (SELECT * from t1)
736
NULL
unknown's avatar
unknown committed
737 738
select 10 > ANY (SELECT * from t1);
10 > ANY (SELECT * from t1)
739
1
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
746 747
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
748
0
unknown's avatar
unknown committed
749 750
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
751
NULL
unknown's avatar
unknown committed
752 753 754
update t1 set a=NULL where a='BC';
select 'A' IN (SELECT * from t1);
'A' IN (SELECT * from t1)
755
1
unknown's avatar
unknown committed
756 757
select 'DEF' IN (SELECT * from t1);
'DEF' IN (SELECT * from t1)
758
1
unknown's avatar
unknown committed
759 760
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
761
NULL
unknown's avatar
unknown committed
762 763
select 'A' > ALL (SELECT * from t1);
'A' > ALL (SELECT * from t1)
764
0
unknown's avatar
unknown committed
765 766
select 'XYZS' > ALL (SELECT * from t1);
'XYZS' > ALL (SELECT * from t1)
767
NULL
unknown's avatar
unknown committed
768 769
select 'A' > ANY (SELECT * from t1);
'A' > ANY (SELECT * from t1)
770
NULL
unknown's avatar
unknown committed
771 772
select 'XYZS' > ANY (SELECT * from t1);
'XYZS' > ANY (SELECT * from t1)
773
1
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
780 781
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
782
0
unknown's avatar
unknown committed
783 784
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
785
NULL
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
790 791
select 3.5 IN (SELECT * from t1);
3.5 IN (SELECT * from t1)
792
1
unknown's avatar
unknown committed
793 794
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
795
NULL
unknown's avatar
unknown committed
796 797
select 1.5 > ALL (SELECT * from t1);
1.5 > ALL (SELECT * from t1)
798
0
unknown's avatar
unknown committed
799 800
select 10.5 > ALL (SELECT * from t1);
10.5 > ALL (SELECT * from t1)
801
NULL
unknown's avatar
unknown committed
802 803
select 1.5 > ANY (SELECT * from t1);
1.5 > ANY (SELECT * from t1)
804
NULL
unknown's avatar
unknown committed
805 806
select 10.5 > ANY (SELECT * from t1);
10.5 > ANY (SELECT * from t1)
807
1
unknown's avatar
unknown committed
808
explain select (select a+1) from t1;
809
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
810
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
811
Warnings:
unknown's avatar
unknown committed
812
Note	1248	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
813
select (select a+1) from t1;
814 815 816 817
(select a+1)
2.5
NULL
4.5
unknown's avatar
unknown committed
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_in	a	a	5	func	2	Using where; Using index
833 834 835 836 837 838 839 840 841 842 843
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
844
2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	func	2	Using where; Using index
845 846
2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
drop table t1,t2,t3;
unknown's avatar
unknown committed
847 848
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
849
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
unknown's avatar
unknown committed
850
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
851
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
unknown's avatar
unknown committed
852
drop table t1;
853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872
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;
873 874 875 876 877 878 879
create table t1 (a int, b real, c varchar(10));
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
select ROW(1, 1, 'a') IN (select a,b,c from t1);
ROW(1, 1, 'a') IN (select a,b,c from t1)
1
select ROW(1, 2, 'a') IN (select a,b,c from t1);
ROW(1, 2, 'a') IN (select a,b,c from t1)
unknown's avatar
unknown committed
880
NULL
881 882
select ROW(1, 1, 'a') IN (select b,a,c from t1);
ROW(1, 1, 'a') IN (select b,a,c from t1)
unknown's avatar
unknown committed
883
1
884 885
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null)
unknown's avatar
unknown committed
886
1
887 888
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null)
unknown's avatar
unknown committed
889
0
890 891
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null)
unknown's avatar
unknown committed
892
1
893 894
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
unknown's avatar
unknown committed
895
1
896 897
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
unknown's avatar
unknown committed
898
NULL
899 900
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
unknown's avatar
unknown committed
901
1
902
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
903
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
904 905 906 907
drop table t1;
create table t1 (a int);
insert into t1 values (1);
do @a:=(SELECT a from t1);
unknown's avatar
unknown committed
908 909 910
select @a;
@a
1
unknown's avatar
unknown committed
911
set @a:=2;
unknown's avatar
merge  
unknown committed
912
set @a:=(SELECT a from t1);
unknown's avatar
unknown committed
913 914 915
select @a;
@a
1
916 917
drop table t1;
do (SELECT a from t1);
918
ERROR 42S02: Table 'test.t1' doesn't exist
unknown's avatar
unknown committed
919
set @a:=(SELECT a from t1);
920
ERROR 42S02: Table 'test.t1' doesn't exist
921 922 923
CREATE TABLE t1 (a int, KEY(a));
HANDLER t1 OPEN;
HANDLER t1 READ a=((SELECT 1));
924
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
925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946
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));
947
ERROR HY000: Invalid use of group function
948
drop table t1;
unknown's avatar
unknown committed
949 950
CREATE TABLE t1 (a int(1));
EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1;
951
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
952
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
953
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
954
EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
955
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
956
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
957
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
958
EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
959
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
960
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
961
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
962
drop table t1;
963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987
CREATE TABLE `t1` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t2` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t3` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
unknown's avatar
unknown committed
988
Warnings:
unknown's avatar
unknown committed
989 990
Warning	1264	Data truncated for column 'date' at row 1
Warning	1264	Data truncated for column 'date' at row 2
991
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
unknown's avatar
unknown committed
992
Warnings:
unknown's avatar
unknown committed
993 994
Warning	1264	Data truncated for column 'date' at row 1
Warning	1264	Data truncated for column 'date' at row 2
995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009
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;
1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040
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` (
1041
  `a` bigint(17) NOT NULL default '0'
1042 1043
) TYPE=MyISAM CHARSET=latin1
drop table t1;
1044 1045 1046 1047 1048 1049
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	
1050 1051
2	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
3	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
1052
drop table t1;
1053
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);
1054
ERROR 42S02: Table 'test.t1' doesn't exist
unknown's avatar
unknown committed
1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089
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;
1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102
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
1103
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1104 1105 1106 1107 1108 1109 1110
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
1111
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1112
drop table t1;
1113 1114 1115 1116 1117 1118
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));
1119
ERROR HY000: Invalid use of group function
1120
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1121
ERROR HY000: Invalid use of group function
1122
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1123
ERROR 42S02: Unknown table 't' in field list
1124
drop table t1;
unknown's avatar
unknown committed
1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141
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;
1142 1143 1144 1145 1146 1147
create table t1 (a int, unique index indexa (a));
insert into t1 values (-1), (-4), (-2), (NULL);
select -10 IN (select a from t1 FORCE INDEX (indexa));
-10 IN (select a from t1 FORCE INDEX (indexa))
NULL
drop table t1;
unknown's avatar
unknown committed
1148 1149 1150 1151 1152 1153 1154
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;
1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168
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;
1169 1170 1171 1172 1173 1174
create table t1 (a int, b int);
create table t2 (a int, b int);
insert into t1 values (1,0), (2,0), (3,0);
insert into t2 values (1,1), (2,1), (3,1), (2,2);
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
Warnings:
unknown's avatar
unknown committed
1175
Error	1241	Subselect returns more than 1 record
1176 1177 1178 1179 1180 1181
select * from t1;
a	b
1	1
2	NULL
3	1
drop table t1, t2;
unknown's avatar
unknown committed
1182 1183 1184 1185 1186
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;
1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200
CREATE TABLE `t1` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`pseudo` varchar(35) NOT NULL default '',
`email` varchar(60) NOT NULL default '',
PRIMARY KEY  (`id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `pseudo` (`pseudo`),
) TYPE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
a	b
test	test
test1	test1
drop table if exists t1;
unknown's avatar
unknown committed
1201 1202 1203
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
a
1
1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228
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;
1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242
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
1243 1244 1245 1246 1247 1248 1249 1250 1251
2	DEPENDENT SUBQUERY	t1	simple_in	PRIMARY	PRIMARY	4	func	1	Using index
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
2	DEPENDENT SUBQUERY	t1	simple_in	PRIMARY	PRIMARY	4	func	1	Using index; Using where
1252 1253 1254 1255 1256 1257 1258 1259 1260 1261
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;
1262
create table t1 (a int, b int, index a (a,b));
1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275
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
1276
2	DEPENDENT SUBQUERY	t1	index_in	a	a	5	func	1001	Using index
1277 1278 1279 1280 1281 1282 1283
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
1284
2	DEPENDENT SUBQUERY	t1	index_in	a	a	5	func	1001	Using index; Using where
1285 1286 1287 1288 1289 1290 1291
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
1292
2	DEPENDENT SUBQUERY	t3	index	a	a	5	NULL	3	Using index
1293
2	DEPENDENT SUBQUERY	t1	ref	a	a	10	func,test.t3.a	1000	Using where; Using index
1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306
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
1307
2	DEPENDENT SUBQUERY	t1	index_in	a	a	5	func	1001	Using index; Using where
1308
drop table t1, t2, t3;
1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320
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;
1321 1322 1323 1324 1325 1326 1327
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;
unknown's avatar
merge  
unknown committed
1328 1329 1330 1331
create table t1 (s1 int);
create table t2 (s1 int);
insert into t1 values (1);
insert into t2 values (1);
1332 1333 1334
update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
ERROR 42S02: Unknown table 'x' in field list
DROP TABLE t1, t2;
unknown's avatar
unknown committed
1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346
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;
1347 1348 1349 1350 1351 1352 1353
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);
drop table t1;