subselect.result 46.6 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 9
explain select (select 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1247	Select 2 was reduced during optimisation
10 11 12 13
SELECT (SELECT 1) UNION SELECT (SELECT 2);
(SELECT 1)
1
2
14 15 16 17 18 19 20
explain SELECT (SELECT 1) UNION SELECT (SELECT 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1247	Select 2 was reduced during optimisation
Note	1247	Select 4 was reduced during optimisation
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 30
4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1247	Select 2 was reduced during optimisation
31
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
32
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
33
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b;
34
ERROR 42S22: Reference 'b' not supported (forward reference in item list)
35
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
36 37
(SELECT 1)	MAX(1)
1	1
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 613
ERROR HY000: You can't specify target table 't1' for update in FROM clause
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
ERROR 42S22: Unknown column 'x' in 'field list'
614
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
615 616 617 618 619 620
select * from t1;
x
1
2
3
3
621
2
unknown's avatar
unknown committed
622
drop table t1, t2, t3;
623 624
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
unknown's avatar
unknown committed
625
create table t3 (a int);
626
insert into t2 values (1);
unknown's avatar
unknown committed
627
insert into t3 values (1),(2);
628 629
select * from t1;
x	y
630
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
631
ERROR HY000: You can't specify target table 't1' for update in FROM clause
unknown's avatar
unknown committed
632
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
633
ERROR 21000: Subselect returns more than 1 record
634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
select * from t1;
x	y
1	2
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
select * from t1;
x	y
1	3
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
select * from t1;
x	y
1	3
4	1
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
select * from t1;
x	y
1	3
4	2
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
select * from t1;
x	y
1	3
4	2
2	1
unknown's avatar
unknown committed
658
drop table t1, t2, t3;
659
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
660
ERROR HY000: No tables used
unknown's avatar
unknown committed
661 662 663
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t2 VALUES (1),(2);
SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
664 665
id
1
unknown's avatar
unknown committed
666
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
667
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
668
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
unknown's avatar
unknown committed
669
Warnings:
670
Note	1247	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
671
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
672 673
id
1
unknown's avatar
unknown committed
674
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
675 676
id
2
unknown's avatar
unknown committed
677
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
678
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
679
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
unknown's avatar
unknown committed
680
Warnings:
681
Note	1247	Select 3 was reduced during optimisation
682
Note	1247	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
683
EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
684
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
685
1	PRIMARY	t2	index	NULL	id	5	NULL	2	Using where; Using index
686
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
687
3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
688
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
unknown's avatar
unknown committed
689
id
unknown's avatar
unknown committed
690
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
unknown's avatar
unknown committed
691 692
id
2
unknown's avatar
unknown committed
693
INSERT INTO t2 VALUES ((SELECT * FROM t2));
694
ERROR HY000: You can't specify target table 't2' for update in FROM clause
695
INSERT INTO t2 VALUES ((SELECT id FROM t2));
696
ERROR HY000: You can't specify target table 't2' for update in FROM clause
unknown's avatar
unknown committed
697
SELECT * FROM t2;
698 699 700
id
1
2
unknown's avatar
unknown committed
701 702
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
703
UPDATE t2 SET id=(SELECT * FROM t1);
704
ERROR 21000: Subselect returns more than 1 record
unknown's avatar
unknown committed
705 706 707 708 709 710 711 712
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)
713
0
unknown's avatar
unknown committed
714 715
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
716
NULL
unknown's avatar
unknown committed
717 718 719
update t1 set a=NULL where a=2;
select 1 IN (SELECT * from t1);
1 IN (SELECT * from t1)
720
1
unknown's avatar
unknown committed
721 722
select 3 IN (SELECT * from t1);
3 IN (SELECT * from t1)
723
1
unknown's avatar
unknown committed
724 725
select 10 IN (SELECT * from t1);
10 IN (SELECT * from t1)
726
NULL
unknown's avatar
unknown committed
727 728
select 1 > ALL (SELECT * from t1);
1 > ALL (SELECT * from t1)
729
0
unknown's avatar
unknown committed
730 731
select 10 > ALL (SELECT * from t1);
10 > ALL (SELECT * from t1)
732
NULL
unknown's avatar
unknown committed
733 734
select 1 > ANY (SELECT * from t1);
1 > ANY (SELECT * from t1)
735
NULL
unknown's avatar
unknown committed
736 737
select 10 > ANY (SELECT * from t1);
10 > ANY (SELECT * from t1)
738
1
unknown's avatar
unknown committed
739 740 741 742 743
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)
744
1
unknown's avatar
unknown committed
745 746
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
747
0
unknown's avatar
unknown committed
748 749
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
750
NULL
unknown's avatar
unknown committed
751 752 753
update t1 set a=NULL where a='BC';
select 'A' IN (SELECT * from t1);
'A' IN (SELECT * from t1)
754
1
unknown's avatar
unknown committed
755 756
select 'DEF' IN (SELECT * from t1);
'DEF' IN (SELECT * from t1)
757
1
unknown's avatar
unknown committed
758 759
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
760
NULL
unknown's avatar
unknown committed
761 762
select 'A' > ALL (SELECT * from t1);
'A' > ALL (SELECT * from t1)
763
0
unknown's avatar
unknown committed
764 765
select 'XYZS' > ALL (SELECT * from t1);
'XYZS' > ALL (SELECT * from t1)
766
NULL
unknown's avatar
unknown committed
767 768
select 'A' > ANY (SELECT * from t1);
'A' > ANY (SELECT * from t1)
769
NULL
unknown's avatar
unknown committed
770 771
select 'XYZS' > ANY (SELECT * from t1);
'XYZS' > ANY (SELECT * from t1)
772
1
unknown's avatar
unknown committed
773 774 775 776 777
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)
778
1
unknown's avatar
unknown committed
779 780
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
781
0
unknown's avatar
unknown committed
782 783
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
784
NULL
unknown's avatar
unknown committed
785 786 787
update t1 set a=NULL where a=2.5;
select 1.5 IN (SELECT * from t1);
1.5 IN (SELECT * from t1)
788
1
unknown's avatar
unknown committed
789 790
select 3.5 IN (SELECT * from t1);
3.5 IN (SELECT * from t1)
791
1
unknown's avatar
unknown committed
792 793
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
794
NULL
unknown's avatar
unknown committed
795 796
select 1.5 > ALL (SELECT * from t1);
1.5 > ALL (SELECT * from t1)
797
0
unknown's avatar
unknown committed
798 799
select 10.5 > ALL (SELECT * from t1);
10.5 > ALL (SELECT * from t1)
800
NULL
unknown's avatar
unknown committed
801 802
select 1.5 > ANY (SELECT * from t1);
1.5 > ANY (SELECT * from t1)
803
NULL
unknown's avatar
unknown committed
804 805
select 10.5 > ANY (SELECT * from t1);
10.5 > ANY (SELECT * from t1)
806
1
unknown's avatar
unknown committed
807
explain select (select a+1) from t1;
808
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
809
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
810 811
Warnings:
Note	1247	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
812
select (select a+1) from t1;
813 814 815 816
(select a+1)
2.5
NULL
4.5
unknown's avatar
unknown committed
817 818 819 820 821 822 823 824 825 826 827 828 829 830
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
831
2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	const	2	Using where; Using index
unknown's avatar
unknown committed
832 833 834
drop table t1,t2;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
835
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
unknown's avatar
unknown committed
836
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
837
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
unknown's avatar
unknown committed
838
drop table t1;
839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858
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;
859 860 861 862 863 864 865
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
866
NULL
867 868
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
869
1
870 871
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
872
1
873 874
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
875
0
876 877
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
878
1
879 880
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
881
1
882 883
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
884
NULL
885 886
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
887
1
888
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
889
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
890 891 892 893
drop table t1;
create table t1 (a int);
insert into t1 values (1);
do @a:=(SELECT a from t1);
unknown's avatar
unknown committed
894 895 896
select @a;
@a
1
unknown's avatar
unknown committed
897
set @a:=2;
unknown's avatar
merge  
unknown committed
898
set @a:=(SELECT a from t1);
unknown's avatar
unknown committed
899 900 901
select @a;
@a
1
902 903
drop table t1;
do (SELECT a from t1);
904
ERROR 42S02: Table 'test.t1' doesn't exist
unknown's avatar
unknown committed
905
set @a:=(SELECT a from t1);
906
ERROR 42S02: Table 'test.t1' doesn't exist
907 908 909
CREATE TABLE t1 (a int, KEY(a));
HANDLER t1 OPEN;
HANDLER t1 READ a=((SELECT 1));
910
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
911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932
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));
933
ERROR HY000: Invalid use of group function
934
drop table t1;
unknown's avatar
unknown committed
935 936
CREATE TABLE t1 (a int(1));
EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1;
937
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
938
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
939
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
940
EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
941
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
942
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
943
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
944
EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
945
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
946
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
947
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
948
drop table t1;
949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973
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
974 975 976
Warnings:
Warning	1263	Data truncated for column 'date' at row 1
Warning	1263	Data truncated for column 'date' at row 2
977
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
unknown's avatar
unknown committed
978 979 980
Warnings:
Warning	1263	Data truncated for column 'date' at row 1
Warning	1263	Data truncated for column 'date' at row 2
981 982 983 984 985 986 987 988 989 990 991 992 993 994 995
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;
996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026
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` (
1027
  `a` bigint(17) NOT NULL default '0'
1028 1029
) TYPE=MyISAM CHARSET=latin1
drop table t1;
1030 1031 1032 1033 1034 1035
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	
1036 1037
2	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
3	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
1038
drop table t1;
1039
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);
1040
ERROR 42S02: Table 'test.t1' doesn't exist
unknown's avatar
unknown committed
1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075
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;
1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088
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
1089
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1090 1091 1092 1093 1094 1095 1096
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
1097
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
1098
drop table t1;
1099 1100 1101 1102 1103 1104
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));
1105
ERROR HY000: Invalid use of group function
1106
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1107
ERROR HY000: Invalid use of group function
1108
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1109
ERROR 42S02: Unknown table 't' in field list
1110
drop table t1;
unknown's avatar
unknown committed
1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127
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;
1128 1129 1130 1131 1132 1133
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
1134 1135 1136 1137 1138 1139 1140
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;
1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154
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;
1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167
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:
Error	1240	Subselect returns more than 1 record
select * from t1;
a	b
1	1
2	NULL
3	1
drop table t1, t2;
unknown's avatar
unknown committed
1168 1169 1170 1171 1172
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;
1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186
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
1187 1188 1189
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
a
1
1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214
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;
1215 1216 1217 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 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264
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
2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where; Using index
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;
create table t1 (a int, b int, index a (a));
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
2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	10	Using where; Using index
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
2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	10	Using where
2	DEPENDENT SUBQUERY	t3	index	a	a	5	NULL	3	Using where; Using index
drop table t1, t2, t3;
1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276
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;