subselect.result 74.1 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
unknown's avatar
unknown committed
5
explain extended select (select 2);
6 7 8
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	1249	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
10
Note	1003	select 2 AS `(select 2)`
11 12 13 14
SELECT (SELECT 1) UNION SELECT (SELECT 2);
(SELECT 1)
1
2
unknown's avatar
unknown committed
15
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
16 17 18
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
unknown's avatar
unknown committed
19
NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	
20
Warnings:
unknown's avatar
unknown committed
21 22
Note	1249	Select 2 was reduced during optimisation
Note	1249	Select 4 was reduced during optimisation
unknown's avatar
unknown committed
23
Note	1003	select 1 AS `(SELECT 1)` union select 2 AS `(SELECT 2)`
unknown's avatar
unknown committed
24 25 26
SELECT (SELECT (SELECT 0 UNION SELECT 0));
(SELECT (SELECT 0 UNION SELECT 0))
0
unknown's avatar
unknown committed
27
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
28 29
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
30
3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
31
4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
32
NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
33
Warnings:
unknown's avatar
unknown committed
34
Note	1249	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
35
Note	1003	select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))`
36
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
37
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
38
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;
39
ERROR 42S22: Reference 'b' not supported (forward reference in item list)
40
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
41 42
(SELECT 1)	MAX(1)
1	1
unknown's avatar
unknown committed
43
SELECT (SELECT a) as a;
44
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
unknown's avatar
unknown committed
45
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
46 47
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
48
3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
49
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
50
Warnings:
unknown's avatar
unknown committed
51 52
Note	1276	Field or reference 'a' of SELECT #3 was resolved in SELECT #1
Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
unknown's avatar
unknown committed
53
Note	1003	select 1 AS `1` from (select 1 AS `a`) b having ((select b.a AS `a`) = 1)
unknown's avatar
unknown committed
54
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
55 56 57
1
1
SELECT (SELECT 1), a;
unknown's avatar
unknown committed
58
ERROR 42S22: Unknown column 'a' in 'checking transformed subquery'
59
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
60 61
a
1
62
SELECT 1 FROM (SELECT (SELECT a) b) c;
63
ERROR 42S22: Unknown column 'a' in 'field list'
64
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
65 66
id
1
67
SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
unknown's avatar
unknown committed
68
ERROR 21000: Operand should contain 1 column(s)
unknown's avatar
unknown committed
69 70 71
SELECT 1 IN (SELECT 1);
1 IN (SELECT 1)
1
72
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
unknown's avatar
unknown committed
73 74
1
1
75
select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
76
ERROR HY000: Wrong usage of PROCEDURE and subquery
77
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
78
ERROR HY000: Incorrect parameters to procedure 'ANALYSE'
unknown's avatar
unknown committed
79 80 81 82 83
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
84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131
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
132
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
unknown's avatar
unknown committed
133
ERROR 21000: Operand should contain 1 column(s)
134 135 136
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
a	b	(SELECT b)
1	2	2
137 138 139
create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
140
create table t4 (a int not null, b int not null);
141 142 143
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
144
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
145
ERROR 42S22: Reference 'a1' not supported (forward reference in item list)
146 147 148 149 150 151 152 153
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
unknown's avatar
unknown committed
154 155 156 157
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
(select a from t1)	a	(select 1 union select 2 limit 1)
2	1	1
2	2	1
158 159 160 161 162 163 164 165
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
166
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
167 168 169
a	b
1	7
2	7
unknown's avatar
unknown committed
170
(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;
171 172 173 174
a	b
1	7
2	7
3	8
unknown's avatar
unknown committed
175
(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
unknown's avatar
unknown committed
176 177 178 179 180
a	b
1	7
2	7
3	8
4	8
unknown's avatar
unknown committed
181
explain extended (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
182
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
183
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
184
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using filesort
unknown's avatar
unknown committed
185
3	UNION	t4	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
186
4	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
unknown's avatar
unknown committed
187
NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	
unknown's avatar
unknown committed
188
Warnings:
unknown's avatar
unknown committed
189
Note	1003	(select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 where (test.t2.b = (select test.t3.a AS `a` from test.t3 order by test.t3.a desc limit 1))) union (select test.t4.a AS `a`,test.t4.b AS `b` from test.t4 where (test.t4.b = (select (max(test.t2.a) * 4) AS `max(t2.a)*4` from test.t2)) order by test.t4.a)
unknown's avatar
unknown committed
190 191 192 193
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
194 195 196 197
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
198
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
unknown's avatar
unknown committed
199 200
(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
201
1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	
unknown's avatar
unknown committed
202
3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
203
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
unknown's avatar
unknown committed
204
Warnings:
unknown's avatar
unknown committed
205
Note	1003	select (select test.t3.a AS `a` from test.t3 where (test.t3.a < 8) order by test.t3.a desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,tt.a AS `a` from (select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 where (test.t2.a > 1)) tt
unknown's avatar
unknown committed
206 207 208 209 210 211 212 213 214 215 216
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
217 218
8	4.5000
9	7.5000
unknown's avatar
unknown committed
219
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
unknown's avatar
unknown committed
220
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
221
1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
222 223
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
224
Warnings:
unknown's avatar
unknown committed
225
Note	1276	Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
unknown's avatar
unknown committed
226
Note	1003	select test.t4.b AS `b`,(select avg((test.t2.a + (select min(test.t3.a) AS `min(t3.a)` from test.t3 where (test.t3.a >= test.t4.a)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from test.t2) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from test.t4
unknown's avatar
unknown committed
227 228 229 230 231 232 233
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
234 235 236 237 238 239 240
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
241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266
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
unknown's avatar
unknown committed
267
explain extended select * from t3 where a >= any (select b from t2);
268 269 270
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	
unknown's avatar
unknown committed
271
Warnings:
unknown's avatar
unknown committed
272
Note	1003	select test.t3.a AS `a` from test.t3 where (test.t3.a >= (select min(test.t2.b) from test.t2))
unknown's avatar
unknown committed
273 274 275 276
select * from t3 where a >= all (select b from t2);
a
7
delete from t2 where a=100;
unknown's avatar
unknown committed
277
select * from t3 where a in (select a,b from t2);
unknown's avatar
unknown committed
278
ERROR 21000: Operand should contain 1 column(s)
unknown's avatar
unknown committed
279
select * from t3 where a in (select * from t2);
unknown's avatar
unknown committed
280
ERROR 21000: Operand should contain 1 column(s)
281 282
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
283
b	ma
284 285 286 287 288 289
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
290 291
b	ma
7	12
unknown's avatar
unknown committed
292 293 294 295 296 297 298 299 300 301 302 303 304 305 306
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
307
explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
unknown's avatar
unknown committed
308
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
309
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
310
2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
unknown's avatar
unknown committed
311
3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
unknown's avatar
unknown committed
312
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
313
Warnings:
unknown's avatar
unknown committed
314 315
Note	1276	Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1
unknown's avatar
unknown committed
316
Note	1003	select (select test.t1.a AS `a` from test.t1 where (test.t1.a = test.t2.a) union select test.t5.a AS `a` from test.t5 where (test.t5.a = test.t2.a)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,test.t2.a AS `a` from test.t2
unknown's avatar
unknown committed
317
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
unknown's avatar
unknown committed
318
ERROR 21000: Subquery returns more than 1 row
319 320 321 322 323
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);
324 325 326 327
patient_uq	clinic_uq
1	1
1	2
2	2
unknown's avatar
unknown committed
328
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
unknown's avatar
unknown committed
329 330
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t6	ALL	NULL	NULL	NULL	NULL	4	Using where
331
2	DEPENDENT SUBQUERY	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.clinic_uq	1	
332
Warnings:
unknown's avatar
unknown committed
333
Note	1276	Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
unknown's avatar
unknown committed
334
Note	1003	select test.t6.patient_uq AS `patient_uq`,test.t6.clinic_uq AS `clinic_uq` from test.t6 where exists(select test.t7.uq AS `uq`,test.t7.name AS `name` from test.t7 where (test.t7.uq = test.t6.clinic_uq))
335
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
336
ERROR 23000: Column: 'a' in field list is ambiguous
337
drop table t1,t2,t3;
338 339 340 341 342 343 344 345 346 347 348 349 350 351 352
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
353
CREATE TABLE `t8` (
unknown's avatar
unknown committed
354 355 356 357
`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`)
unknown's avatar
unknown committed
358
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
359 360 361
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
unknown's avatar
unknown committed
362
EXPLAIN EXTENDED 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
363
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
364 365
1	PRIMARY	t8	const	PRIMARY	PRIMARY	35	const	1	Using index
4	SUBQUERY	t8	const	PRIMARY	PRIMARY	35		1	Using index
366
2	SUBQUERY	t8	const	PRIMARY	PRIMARY	35	const	1	
367
3	SUBQUERY	t8	const	PRIMARY	PRIMARY	35		1	Using index
unknown's avatar
unknown committed
368
Warnings:
unknown's avatar
unknown committed
369
Note	1003	select test.t8.pseudo AS `pseudo`,(select test.t8.email AS `email` from test.t8 where (test.t8.pseudo = (select test.t8.pseudo AS `pseudo` from test.t8 where (test.t8.pseudo = _latin1'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from test.t8 where (test.t8.pseudo = (select test.t8.pseudo AS `pseudo` from test.t8 where (test.t8.pseudo = _latin1'joce')))
370 371
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
unknown's avatar
unknown committed
372
ERROR 21000: Operand should contain 1 column(s)
373
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
unknown's avatar
unknown committed
374
pseudo='joce');
unknown's avatar
unknown committed
375
ERROR 21000: Operand should contain 1 column(s)
376
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
377 378
pseudo
joce
379
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
unknown's avatar
unknown committed
380
ERROR 21000: Subquery returns more than 1 row
381 382
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
CREATE TABLE `t1` (
unknown's avatar
unknown committed
383 384 385 386 387
`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`)
unknown's avatar
unknown committed
388
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
389
INSERT INTO t1 (topic,date,pseudo) VALUES
unknown's avatar
unknown committed
390
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
unknown's avatar
unknown committed
391
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
unknown's avatar
unknown committed
392
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
393
1	SIMPLE	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
unknown's avatar
unknown committed
394
Warnings:
unknown's avatar
unknown committed
395
Note	1003	select distinct test.t1.date AS `date` from test.t1 where (test.t1.date = 20020803)
unknown's avatar
unknown committed
396
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
unknown's avatar
unknown committed
397
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
398
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
399
2	SUBQUERY	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
unknown's avatar
unknown committed
400
Warnings:
unknown's avatar
unknown committed
401
Note	1003	select (select distinct test.t1.date AS `date` from test.t1 where (test.t1.date = 20020803)) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
402
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
unknown's avatar
unknown committed
403 404
date
2002-08-03
405 406
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
407
2002-08-03
408
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
unknown's avatar
unknown committed
409 410 411 412
1
1
1
1
413
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
unknown's avatar
unknown committed
414
ERROR 21000: Subquery returns more than 1 row
unknown's avatar
unknown committed
415
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
unknown's avatar
unknown committed
416
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
417
1	PRIMARY	t1	index	NULL	topic	3	NULL	2	Using index
418
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
419
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
420
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
unknown's avatar
unknown committed
421
Warnings:
unknown's avatar
unknown committed
422
Note	1003	select 1 AS `1` from test.t1
unknown's avatar
unknown committed
423
drop table t1;
424
CREATE TABLE `t1` (
unknown's avatar
unknown committed
425 426 427 428
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
unknown's avatar
unknown committed
429
) ENGINE=MyISAM ROW_FORMAT=FIXED;
430 431
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
CREATE TABLE `t2` (
unknown's avatar
unknown committed
432 433 434 435 436
`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`)
unknown's avatar
unknown committed
437
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
438 439
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
440 441
a
40143
442
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
443 444 445
numeropost	maxnumrep
43506	2
40143	1
446
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
447
ERROR 42S22: Unknown column 'a' in 'having clause'
unknown's avatar
unknown committed
448
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
449
ERROR 42S22: Unknown column 'a' in 'having clause'
450
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
unknown's avatar
unknown committed
451 452
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
453 454
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
455 456 457
mot	topic	date	pseudo
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
mot	topic	date	pseudo
458
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
unknown's avatar
unknown committed
459 460
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
461 462
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
463 464 465
mot	topic	date	pseudo
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
mot	topic	date	pseudo
466
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
unknown's avatar
unknown committed
467
mot	topic	date	pseudo
468
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
unknown's avatar
unknown committed
469 470
mot	topic	date	pseudo
joce	40143	2002-10-22	joce
471
joce	43506	2002-10-22	joce
472 473 474 475
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
476 477 478 479 480 481
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
482 483 484 485 486 487 488 489 490 491 492 493 494
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
495
drop table t1,t2;
496
CREATE TABLE `t1` (
unknown's avatar
unknown committed
497 498 499 500
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
unknown's avatar
unknown committed
501
) ENGINE=MyISAM ROW_FORMAT=FIXED;
502 503
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
unknown's avatar
unknown committed
504
ERROR 21000: Subquery returns more than 1 row
505
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
unknown's avatar
unknown committed
506
ERROR 21000: Subquery returns more than 1 row
507
drop table t1;
508 509 510 511 512 513 514 515
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;
516 517
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO t1 VALUES ();
518
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
unknown's avatar
unknown committed
519
ERROR 21000: Subquery returns more than 1 row
520 521
drop table t1;
CREATE TABLE `t1` (
unknown's avatar
unknown committed
522 523 524 525 526 527
`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`)
unknown's avatar
unknown committed
528
) ENGINE=MyISAM;
529
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
530
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
531
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
532
ERROR 42S22: Unknown column 'a' in 'having clause'
533 534 535
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');
unknown's avatar
unknown committed
536
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
unknown's avatar
unknown committed
537
ERROR 21000: Subquery returns more than 1 row
unknown's avatar
unknown committed
538
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
unknown's avatar
unknown committed
539
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
540
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
unknown's avatar
unknown committed
541
Warnings:
unknown's avatar
unknown committed
542
Note	1003	select max(test.t1.numreponse) AS `MAX(numreponse)` from test.t1 where (test.t1.numeropost = _latin1'1')
unknown's avatar
unknown committed
543
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
unknown's avatar
unknown committed
544
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
545
1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	Using index
546
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
unknown's avatar
unknown committed
547
Warnings:
unknown's avatar
unknown committed
548
Note	1003	select test.t1.numreponse AS `numreponse` from test.t1 where ((test.t1.numeropost = _latin1'1') and (test.t1.numreponse = 3))
549
drop table t1;
550 551
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
unknown's avatar
unknown committed
552
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
553 554 555
1
1
drop table t1;
556 557 558 559 560 561 562 563 564
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
565
update t1 set b= (select b from t1);
566
ERROR HY000: You can't specify target table 't1' for update in FROM clause
unknown's avatar
unknown committed
567
update t1 set b= (select b from t2);
unknown's avatar
unknown committed
568
ERROR 21000: Subquery returns more than 1 row
569 570 571 572 573 574 575
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
576 577 578 579 580 581 582 583 584 585 586 587
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
588
delete from t1 where b = (select b from t1);
589
ERROR HY000: You can't specify target table 't1' for update in FROM clause
unknown's avatar
unknown committed
590
delete from t1 where b = (select b from t2);
unknown's avatar
unknown committed
591
ERROR 21000: Subquery returns more than 1 row
unknown's avatar
unknown committed
592 593 594 595 596 597
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;
598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613
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
614
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
615
ERROR HY000: You can't specify target table 't12' for update in FROM clause
unknown's avatar
unknown committed
616
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
unknown's avatar
unknown committed
617
ERROR 21000: Subquery returns more than 1 row
618 619 620 621 622 623 624 625 626 627
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;
628 629
CREATE TABLE t1 (x int);
create table t2 (a int);
630
create table t3 (b int);
631
insert into t2 values (1);
unknown's avatar
unknown committed
632
insert into t3 values (1),(2);
633
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
634
ERROR HY000: You can't specify target table 't1' for update in FROM clause
635
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
unknown's avatar
unknown committed
636
ERROR 21000: Subquery returns more than 1 row
637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654
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
655 656
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
ERROR 42S22: Unknown column 'x' in 'field list'
657
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
658 659 660 661 662 663
select * from t1;
x
1
2
3
3
unknown's avatar
unknown committed
664 665
11
11
666
2
unknown's avatar
unknown committed
667
drop table t1, t2, t3;
668 669
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
unknown's avatar
unknown committed
670
create table t3 (a int);
671
insert into t2 values (1);
unknown's avatar
unknown committed
672
insert into t3 values (1),(2);
673 674
select * from t1;
x	y
675
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
676
ERROR HY000: You can't specify target table 't1' for update in FROM clause
unknown's avatar
unknown committed
677
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
unknown's avatar
unknown committed
678
ERROR 21000: Subquery returns more than 1 row
679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702
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
703
drop table t1, t2, t3;
704
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
705
ERROR HY000: No tables used
unknown's avatar
unknown committed
706
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
unknown's avatar
unknown committed
707 708
INSERT INTO t2 VALUES (1),(2);
SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
709 710
id
1
unknown's avatar
unknown committed
711
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
712
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
713
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
unknown's avatar
unknown committed
714
Warnings:
unknown's avatar
unknown committed
715
Note	1249	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
716
Note	1003	select test.t2.id AS `id` from test.t2 where (test.t2.id = 1)
unknown's avatar
unknown committed
717
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
718 719
id
1
unknown's avatar
unknown committed
720
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
721 722
id
2
unknown's avatar
unknown committed
723
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
724
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
725
1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
unknown's avatar
unknown committed
726
Warnings:
unknown's avatar
unknown committed
727 728
Note	1249	Select 3 was reduced during optimisation
Note	1249	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
729
Note	1003	select test.t2.id AS `id` from test.t2 where (test.t2.id = (1 + 1))
unknown's avatar
unknown committed
730
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
731
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
732
1	PRIMARY	t2	index	NULL	id	5	NULL	2	Using where; Using index
733
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
734
3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
unknown's avatar
unknown committed
735
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
unknown's avatar
unknown committed
736
Warnings:
unknown's avatar
unknown committed
737
Note	1003	select test.t2.id AS `id` from test.t2 where <in_optimizer>(test.t2.id,<exists>(select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(1)) union select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(3))))
unknown's avatar
unknown committed
738
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
unknown's avatar
unknown committed
739
id
unknown's avatar
unknown committed
740
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
unknown's avatar
unknown committed
741 742
id
2
unknown's avatar
unknown committed
743
INSERT INTO t2 VALUES ((SELECT * FROM t2));
744
ERROR HY000: You can't specify target table 't2' for update in FROM clause
745
INSERT INTO t2 VALUES ((SELECT id FROM t2));
746
ERROR HY000: You can't specify target table 't2' for update in FROM clause
unknown's avatar
unknown committed
747
SELECT * FROM t2;
748 749 750
id
1
2
unknown's avatar
unknown committed
751
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
unknown's avatar
unknown committed
752
INSERT INTO t1 values (1),(1);
unknown's avatar
unknown committed
753
UPDATE t2 SET id=(SELECT * FROM t1);
unknown's avatar
unknown committed
754
ERROR 21000: Subquery returns more than 1 row
unknown's avatar
unknown committed
755 756 757 758 759 760 761 762
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)
763
0
unknown's avatar
unknown committed
764 765
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
766
NULL
unknown's avatar
unknown committed
767 768 769
update t1 set a=NULL where a=2;
select 1 IN (SELECT * from t1);
1 IN (SELECT * from t1)
770
1
unknown's avatar
unknown committed
771 772
select 3 IN (SELECT * from t1);
3 IN (SELECT * from t1)
773
1
unknown's avatar
unknown committed
774 775
select 10 IN (SELECT * from t1);
10 IN (SELECT * from t1)
776
NULL
unknown's avatar
unknown committed
777 778
select 1 > ALL (SELECT * from t1);
1 > ALL (SELECT * from t1)
779
0
unknown's avatar
unknown committed
780 781
select 10 > ALL (SELECT * from t1);
10 > ALL (SELECT * from t1)
782
NULL
unknown's avatar
unknown committed
783 784
select 1 > ANY (SELECT * from t1);
1 > ANY (SELECT * from t1)
785
NULL
unknown's avatar
unknown committed
786 787
select 10 > ANY (SELECT * from t1);
10 > ANY (SELECT * from t1)
788
1
unknown's avatar
unknown committed
789 790 791 792 793
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)
794
1
unknown's avatar
unknown committed
795 796
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
797
0
unknown's avatar
unknown committed
798 799
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
800
NULL
unknown's avatar
unknown committed
801 802 803
update t1 set a=NULL where a='BC';
select 'A' IN (SELECT * from t1);
'A' IN (SELECT * from t1)
804
1
unknown's avatar
unknown committed
805 806
select 'DEF' IN (SELECT * from t1);
'DEF' IN (SELECT * from t1)
807
1
unknown's avatar
unknown committed
808 809
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
810
NULL
unknown's avatar
unknown committed
811 812
select 'A' > ALL (SELECT * from t1);
'A' > ALL (SELECT * from t1)
813
0
unknown's avatar
unknown committed
814 815
select 'XYZS' > ALL (SELECT * from t1);
'XYZS' > ALL (SELECT * from t1)
816
NULL
unknown's avatar
unknown committed
817 818
select 'A' > ANY (SELECT * from t1);
'A' > ANY (SELECT * from t1)
819
NULL
unknown's avatar
unknown committed
820 821
select 'XYZS' > ANY (SELECT * from t1);
'XYZS' > ANY (SELECT * from t1)
822
1
unknown's avatar
unknown committed
823 824 825 826 827
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)
828
1
unknown's avatar
unknown committed
829 830
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
831
0
unknown's avatar
unknown committed
832 833
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
834
NULL
unknown's avatar
unknown committed
835 836 837
update t1 set a=NULL where a=2.5;
select 1.5 IN (SELECT * from t1);
1.5 IN (SELECT * from t1)
838
1
unknown's avatar
unknown committed
839 840
select 3.5 IN (SELECT * from t1);
3.5 IN (SELECT * from t1)
841
1
unknown's avatar
unknown committed
842 843
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
844
NULL
unknown's avatar
unknown committed
845 846
select 1.5 > ALL (SELECT * from t1);
1.5 > ALL (SELECT * from t1)
847
0
unknown's avatar
unknown committed
848 849
select 10.5 > ALL (SELECT * from t1);
10.5 > ALL (SELECT * from t1)
850
NULL
unknown's avatar
unknown committed
851 852
select 1.5 > ANY (SELECT * from t1);
1.5 > ANY (SELECT * from t1)
853
NULL
unknown's avatar
unknown committed
854 855
select 10.5 > ANY (SELECT * from t1);
10.5 > ANY (SELECT * from t1)
856
1
unknown's avatar
unknown committed
857
explain extended select (select a+1) from t1;
858
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
859
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
860
Warnings:
unknown's avatar
unknown committed
861 862
Note	1276	Field or reference 'a' of SELECT #2 was resolved in SELECT #1
Note	1249	Select 2 was reduced during optimisation
unknown's avatar
unknown committed
863
Note	1003	select (test.t1.a + 1) AS `(select a+1)` from test.t1
unknown's avatar
unknown committed
864
select (select a+1) from t1;
865 866 867 868
(select a+1)
2.5
NULL
4.5
unknown's avatar
unknown committed
869 870 871 872 873 874 875 876 877 878 879
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
unknown's avatar
unknown committed
880
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
unknown's avatar
unknown committed
881 882
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	Using index
883
2	DEPENDENT SUBQUERY	t2	index_subquery	a	a	5	func	2	Using index
unknown's avatar
unknown committed
884
Warnings:
unknown's avatar
unknown committed
885
Note	1003	select test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(<index_lookup>(<cache>(test.t1.a) in t2 on a chicking NULL))) AS `t1.a in (select t2.a from t2)` from test.t1
886 887 888 889 890 891 892 893
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
unknown's avatar
unknown committed
894
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
895 896
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	Using index
897
2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	func	2	Using where; Using index
898
2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
unknown's avatar
unknown committed
899
Warnings:
unknown's avatar
unknown committed
900
Note	1003	select test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(select 1 AS `Not_used` from test.t2 join test.t3 where ((test.t3.a = test.t2.a) and ((<cache>(test.t1.a) = test.t2.a) or isnull(test.t2.a))) having <is_not_null_test>(test.t2.a))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from test.t1
901
drop table t1,t2,t3;
unknown's avatar
unknown committed
902 903
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
904
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
unknown's avatar
unknown committed
905
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
906
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
unknown's avatar
unknown committed
907
drop table t1;
908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927
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;
928 929 930 931 932 933 934
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
935
NULL
936 937
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
938
1
939 940
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
941
1
942 943
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
944
0
945 946
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
947
1
948 949
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
950
1
951 952
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
953
NULL
954 955
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
956
1
957
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
958
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
959 960 961 962
drop table t1;
create table t1 (a int);
insert into t1 values (1);
do @a:=(SELECT a from t1);
unknown's avatar
unknown committed
963 964 965
select @a;
@a
1
unknown's avatar
unknown committed
966
set @a:=2;
unknown's avatar
merge  
unknown committed
967
set @a:=(SELECT a from t1);
unknown's avatar
unknown committed
968 969 970
select @a;
@a
1
971 972
drop table t1;
do (SELECT a from t1);
973
ERROR 42S02: Table 'test.t1' doesn't exist
unknown's avatar
unknown committed
974
set @a:=(SELECT a from t1);
975
ERROR 42S02: Table 'test.t1' doesn't exist
976 977 978
CREATE TABLE t1 (a int, KEY(a));
HANDLER t1 OPEN;
HANDLER t1 READ a=((SELECT 1));
979
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 near 'SELECT 1))' at line 1
980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998
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;
unknown's avatar
unknown committed
999
CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY  (`i`)) ENGINE=MyISAM CHARSET=latin1;
1000 1001
INSERT INTO t1 VALUES (1);
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1002
ERROR HY000: Invalid use of group function
1003
drop table t1;
unknown's avatar
unknown committed
1004
CREATE TABLE t1 (a int(1));
unknown's avatar
unknown committed
1005
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
1006
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
1007
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
1008
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
1009
Warnings:
unknown's avatar
unknown committed
1010
Note	1003	select sql_no_cache (select sql_no_cache rand() AS `RAND()` from test.t1) AS `(SELECT RAND() FROM t1)` from test.t1
unknown's avatar
unknown committed
1011
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
1012
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
1013
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
1014
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
1015
Warnings:
unknown's avatar
unknown committed
1016
Note	1003	select sql_no_cache (select sql_no_cache ecrypt(_latin1'test') AS `ENCRYPT('test')` from test.t1) AS `(SELECT ENCRYPT('test') FROM t1)` from test.t1
unknown's avatar
unknown committed
1017
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
1018
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
1019
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
1020
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
1021
Warnings:
unknown's avatar
unknown committed
1022
Note	1003	select sql_no_cache (select sql_no_cache benchmark(1,1) AS `BENCHMARK(1,1)` from test.t1) AS `(SELECT BENCHMARK(1,1) FROM t1)` from test.t1
unknown's avatar
unknown committed
1023
drop table t1;
1024 1025 1026 1027 1028 1029 1030 1031
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`)
unknown's avatar
unknown committed
1032
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
1033 1034 1035 1036 1037 1038 1039 1040
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`)
unknown's avatar
unknown committed
1041
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
1042 1043 1044 1045 1046
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`)
unknown's avatar
unknown committed
1047
) ENGINE=MyISAM CHARSET=latin1;
1048
INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
unknown's avatar
unknown committed
1049
Warnings:
unknown's avatar
unknown committed
1050 1051
Warning	1265	Data truncated for column 'date' at row 1
Warning	1265	Data truncated for column 'date' at row 2
1052
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
unknown's avatar
unknown committed
1053
Warnings:
unknown's avatar
unknown committed
1054 1055
Warning	1265	Data truncated for column 'date' at row 1
Warning	1265	Data truncated for column 'date' at row 2
1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070
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;
1071 1072 1073 1074 1075 1076 1077 1078 1079
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'
unknown's avatar
unknown committed
1080
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1081 1082 1083 1084 1085 1086 1087
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'
unknown's avatar
unknown committed
1088
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1089 1090 1091 1092 1093 1094 1095
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'
unknown's avatar
unknown committed
1096
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1097 1098
drop table t1;
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1099 1100 1101
select * from t1;
a
2
1102 1103 1104
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
1105
  `a` bigint(17) NOT NULL default '0'
unknown's avatar
unknown committed
1106
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1107
drop table t1;
1108 1109
create table t1 (a int);
insert into t1 values (1), (2), (3);
unknown's avatar
unknown committed
1110
explain extended select a,(select (select rand() from t1 limit 1)  from t1 limit 1)
1111 1112 1113
from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
1114 1115
2	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
3	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
unknown's avatar
unknown committed
1116
Warnings:
unknown's avatar
unknown committed
1117
Note	1003	select sql_no_cache test.t1.a AS `a`,(select sql_no_cache (select sql_no_cache rand() AS `rand()` from test.t1 limit 1) AS `(select rand() from t1 limit 1)` from test.t1 limit 1) AS `(select (select rand() from t1 limit 1)  from t1 limit 1)` from test.t1
1118
drop table t1;
1119
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);
1120
ERROR 42S02: Table 'test.t1' doesn't exist
unknown's avatar
unknown committed
1121 1122 1123 1124 1125 1126 1127
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)
unknown's avatar
unknown committed
1128
) ENGINE=MyISAM;
unknown's avatar
unknown committed
1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148
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)
unknown's avatar
unknown committed
1149
) ENGINE=MyISAM;
unknown's avatar
unknown committed
1150 1151 1152 1153 1154 1155
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;
1156 1157 1158 1159
CREATE TABLE `t1` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`id`),
unknown's avatar
unknown committed
1160
UNIQUE KEY `pseudo` (`pseudo`)
unknown's avatar
unknown committed
1161
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1162 1163 1164 1165
INSERT INTO t1 (pseudo) VALUES ('test');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
0
unknown's avatar
unknown committed
1166
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1167 1168
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
unknown's avatar
unknown committed
1169
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
unknown's avatar
unknown committed
1170
Warnings:
unknown's avatar
unknown committed
1171
Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a)) AS `0 IN (SELECT 1 FROM t1 a)`
1172 1173 1174 1175
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
0
unknown's avatar
unknown committed
1176
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1177 1178
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
unknown's avatar
unknown committed
1179
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
unknown's avatar
unknown committed
1180
Warnings:
unknown's avatar
unknown committed
1181
Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a)) AS `0 IN (SELECT 1 FROM t1 a)`
1182
drop table t1;
1183 1184 1185
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
PRIMARY KEY  (`i`)
unknown's avatar
unknown committed
1186
) ENGINE=MyISAM CHARSET=latin1;
1187 1188
INSERT INTO t1 VALUES (1);
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
1189
ERROR HY000: Invalid use of group function
1190
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1191
ERROR HY000: Invalid use of group function
1192
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1193
ERROR 42S02: Unknown table 't' in field list
1194
drop table t1;
unknown's avatar
unknown committed
1195 1196
CREATE TABLE t1 (
id int(11) default NULL
unknown's avatar
unknown committed
1197
) ENGINE=MyISAM CHARSET=latin1;
unknown's avatar
unknown committed
1198 1199 1200 1201
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
CREATE TABLE t2 (
id int(11) default NULL,
name varchar(15) default NULL
unknown's avatar
unknown committed
1202
) ENGINE=MyISAM CHARSET=latin1;
unknown's avatar
unknown committed
1203 1204 1205 1206 1207 1208 1209 1210 1211
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;
1212 1213 1214 1215 1216 1217
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
1218 1219
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);
unknown's avatar
unknown committed
1220
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
unknown's avatar
unknown committed
1221 1222 1223
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
unknown's avatar
unknown committed
1224
Warnings:
unknown's avatar
unknown committed
1225
Note	1003	select test.t1.id AS `id` from test.t1 where (test.t1.salary = (select max(test.t1.salary) AS `MAX(salary)` from test.t1))
unknown's avatar
unknown committed
1226
drop table t1;
1227 1228 1229 1230 1231 1232 1233 1234 1235
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)
unknown's avatar
unknown committed
1236
) ENGINE=MyISAM CHARSET=cp1251;
1237 1238 1239 1240
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;
1241 1242 1243 1244 1245 1246
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
1247
Error	1242	Subquery returns more than 1 row
1248 1249 1250 1251 1252 1253
select * from t1;
a	b
1	1
2	NULL
3	1
drop table t1, t2;
unknown's avatar
unknown committed
1254 1255 1256 1257 1258
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;
1259 1260 1261 1262 1263 1264
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`),
unknown's avatar
unknown committed
1265
UNIQUE KEY `pseudo` (`pseudo`)
unknown's avatar
unknown committed
1266
) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1267 1268 1269 1270 1271 1272
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
1273 1274 1275
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
a
1
1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286
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
unknown's avatar
unknown committed
1287
explain extended select * from t2 where t2.a in (select a from t1);
1288 1289
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
1290
2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
unknown's avatar
unknown committed
1291
Warnings:
unknown's avatar
unknown committed
1292
Note	1003	select test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<primary_index_lookup>(<cache>(test.t2.a) in t1 on PRIMARY)))
1293 1294 1295 1296
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
unknown's avatar
unknown committed
1297
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1298 1299
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
1300
2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index; Using where
unknown's avatar
unknown committed
1301
Warnings:
unknown's avatar
unknown committed
1302
Note	1003	select test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<primary_index_lookup>(<cache>(test.t2.a) in t1 on PRIMARY where (test.t1.b <> 30))))
1303 1304 1305 1306
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
unknown's avatar
unknown committed
1307
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1308 1309 1310 1311
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
unknown's avatar
unknown committed
1312
Warnings:
unknown's avatar
unknown committed
1313
Note	1003	select test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a))))
1314
drop table t1, t2, t3;
1315
create table t1 (a int, b int, index a (a,b));
1316 1317 1318 1319 1320 1321 1322 1323 1324 1325
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
unknown's avatar
unknown committed
1326
explain extended select * from t2 where t2.a in (select a from t1);
1327 1328
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
1329
2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	1001	Using index
unknown's avatar
unknown committed
1330
Warnings:
unknown's avatar
unknown committed
1331
Note	1003	select test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<index_lookup>(<cache>(test.t2.a) in t1 on a)))
1332 1333 1334 1335
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
unknown's avatar
unknown committed
1336
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1337 1338
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
1339
2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	1001	Using index; Using where
unknown's avatar
unknown committed
1340
Warnings:
unknown's avatar
unknown committed
1341
Note	1003	select test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<index_lookup>(<cache>(test.t2.a) in t1 on a where (test.t1.b <> 30))))
1342 1343 1344 1345
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
unknown's avatar
unknown committed
1346
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1347 1348
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
unknown's avatar
unknown committed
1349 1350
2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	1001	Using where; Using index
2	DEPENDENT SUBQUERY	t3	index	a	a	5	NULL	3	Using where; Using index
unknown's avatar
unknown committed
1351
Warnings:
unknown's avatar
unknown committed
1352
Note	1003	select test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a))))
1353 1354 1355 1356 1357 1358 1359 1360 1361 1362
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
unknown's avatar
unknown committed
1363
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1364 1365
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
1366
2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	1001	Using index; Using where
unknown's avatar
unknown committed
1367
Warnings:
unknown's avatar
unknown committed
1368
Note	1003	select test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<index_lookup>(<cache>(test.t2.a) in t1 on a where (test.t1.b <> 30))))
1369
drop table t1, t2, t3;
1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381
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;
1382 1383 1384 1385 1386 1387
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
1388
1
1389
drop table t1,t2;
unknown's avatar
merge  
unknown committed
1390 1391 1392 1393
create table t1 (s1 int);
create table t2 (s1 int);
insert into t1 values (1);
insert into t2 values (1);
1394
update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1395
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1396
DROP TABLE t1, t2;
1397 1398 1399 1400 1401 1402
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
s2 CHAR(5) COLLATE latin1_swedish_ci);
INSERT INTO t1 VALUES ('z','?');
select * from t1 where s1 > (select max(s2) from t1);
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
select * from t1 where s1 > any (select max(s2) from t1);
unknown's avatar
merge  
unknown committed
1403
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1404
drop table t1;
1405 1406 1407 1408 1409 1410 1411 1412 1413
create table t1(toid int,rd int);
create table t2(userid int,pmnew int,pmtotal int);
insert into t2 values(1,0,0),(2,0,0);
insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2);
select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1);
userid	pmtotal	pmnew	calc_total	calc_new
1	0	0	9	3
2	0	0	4	2
drop table t1, t2;
unknown's avatar
unknown committed
1414 1415
create table t1 (s1 char(5));
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
unknown's avatar
unknown committed
1416
ERROR 21000: Operand should contain 1 column(s)
unknown's avatar
unknown committed
1417 1418 1419 1420
insert into t1 values ('tttt');
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
s1
tttt
unknown's avatar
unknown committed
1421
explain extended (select * from t1);
unknown's avatar
unknown committed
1422 1423
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
unknown's avatar
unknown committed
1424
Warnings:
unknown's avatar
unknown committed
1425
Note	1003	(select test.t1.s1 AS `s1` from test.t1)
unknown's avatar
unknown committed
1426 1427 1428
(select * from t1);
s1
tttt
unknown's avatar
unknown committed
1429
drop table t1;
1430 1431 1432 1433 1434 1435 1436 1437 1438
create table t1 (s1 char(5), index s1(s1));
create table t2 (s1 char(5), index s1(s1));
insert into t1 values ('a1'),('a2'),('a3');
insert into t2 values ('a1'),('a2');
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
s1	s1 NOT IN (SELECT s1 FROM t2)
a1	0
a2	0
a3	1
unknown's avatar
unknown committed
1439 1440 1441 1442 1443 1444 1445 1446 1447 1448
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
s1	s1 = ANY (SELECT s1 FROM t2)
a1	1
a2	1
a3	0
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
s1	s1 <> ALL (SELECT s1 FROM t2)
a1	0
a2	0
a3	1
1449 1450 1451 1452 1453
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
s1	s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')
a1	0
a2	1
a3	1
unknown's avatar
unknown committed
1454
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1455 1456
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
1457
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
1458
Warnings:
unknown's avatar
unknown committed
1459
Note	1003	select test.t1.s1 AS `s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from test.t1
1460
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
unknown's avatar
unknown committed
1461 1462 1463
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
1464
Warnings:
unknown's avatar
unknown committed
1465
Note	1003	select test.t1.s1 AS `s1`,<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from test.t1
1466
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
unknown's avatar
unknown committed
1467 1468 1469
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
unknown's avatar
unknown committed
1470
Warnings:
unknown's avatar
unknown committed
1471
Note	1003	select test.t1.s1 AS `s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from test.t1
unknown's avatar
unknown committed
1472
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1473 1474
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
1475
2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	1	Using index; Using where
unknown's avatar
unknown committed
1476
Warnings:
unknown's avatar
unknown committed
1477
Note	1003	select test.t1.s1 AS `s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL where (test.t2.s1 < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from test.t1
1478
drop table t1,t2;
1479 1480 1481 1482 1483 1484 1485 1486
create table t2 (a int, b int);
create table t3 (a int);
insert into t3 values (6),(7),(3);
select * from t3 where a >= all (select b from t2);
a
6
7
3
unknown's avatar
unknown committed
1487
explain extended select * from t3 where a >= all (select b from t2);
1488 1489 1490
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
unknown's avatar
unknown committed
1491
Warnings:
unknown's avatar
unknown committed
1492
Note	1003	select test.t3.a AS `a` from test.t3 where <not>((test.t3.a < (select max(test.t2.b) from test.t2)))
1493 1494 1495 1496 1497
insert into t2 values (2,2), (2,1), (3,3), (3,1);
select * from t3 where a > all (select max(b) from t2 group by a);
a
6
7
unknown's avatar
unknown committed
1498
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
1499 1500 1501
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
unknown's avatar
unknown committed
1502
Warnings:
unknown's avatar
unknown committed
1503
Note	1003	select test.t3.a AS `a` from test.t3 where <not>((test.t3.a <= <max>(select max(test.t2.b) AS `max(b)` from test.t2 group by test.t2.a)))
1504
drop table t2, t3;
unknown's avatar
unknown committed
1505
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
1506
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
unknown's avatar
unknown committed
1507
CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY  (`db_id`),UNIQUE KEY `name_2` (`name`),FULLTEXT KEY `name` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
1508
INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);
unknown's avatar
unknown committed
1509
CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY  (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
1510
INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);
unknown's avatar
unknown committed
1511
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
select  dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4  WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;
dbid	name	(date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')
-1	Valid	1
-1	Valid 2	1
-1	Should Not Return	0
SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid;
dbid	name
-1	Valid
-1	Valid 2
drop table t1,t2,t3,t4;
unknown's avatar
unknown committed
1523
CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
1524
INSERT INTO t1 VALUES (1),(5);
unknown's avatar
unknown committed
1525
CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
1526 1527 1528 1529
INSERT INTO t2 VALUES (2),(6);
select * from t1 where (1,2,6) in (select * from t2);
ERROR 21000: Operand should contain 3 column(s)
DROP TABLE t1,t2;
1530 1531 1532 1533 1534 1535 1536
create table t1 (s1 int);
insert into t1 values (1);
insert into t1 values (2);
set sort_buffer_size = (select s1 from t1);
ERROR 21000: Subquery returns more than 1 row
do (select * from t1);
drop table t1;
1537 1538 1539 1540 1541 1542 1543 1544
create table t1 (s1 char);
insert into t1 values ('e');
select * from t1 where 'f' > any (select s1 from t1);
s1
e
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
s1
e
1545
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1546 1547 1548 1549
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
3	UNION	t1	system	NULL	NULL	NULL	NULL	1	
unknown's avatar
unknown committed
1550
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
1551
Warnings:
unknown's avatar
unknown committed
1552
Note	1003	select test.t1.s1 AS `s1` from test.t1
1553
drop table t1;
unknown's avatar
unknown committed
1554
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1555
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
unknown's avatar
unknown committed
1556
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
1557 1558 1559 1560 1561 1562 1563 1564
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;
phone	code
69294728265	6
18621828126	1862
89356874041	NULL
95895001874	NULL
drop table t1, t2;
1565 1566 1567 1568 1569 1570 1571 1572 1573
create table t1 (s1 int);
create table t2 (s1 int);
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
select count(*) from t2 group by t1.s2;
ERROR 42S02: Unknown table 't1' in group statement
drop table t1, t2;
unknown's avatar
unknown committed
1574 1575 1576 1577 1578 1579 1580
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
INSERT INTO t2 VALUES (100, 200, 'C');
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
COLC
DROP TABLE t1, t2;
unknown's avatar
unknown committed
1581 1582 1583 1584 1585 1586 1587 1588 1589 1590
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
(SELECT a)
1
2
3
4
5
DROP TABLE t1;
1591 1592 1593 1594 1595 1596 1597 1598 1599
create table t1 (a int, b decimal(13, 3));
insert into t1 values (1, 0.123);
select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1;
delete from t1;
load data infile "subselect.out.file.1" into table t1;
select * from t1;
a	b
1	0.123
drop table t1;
unknown's avatar
unknown committed
1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610
CREATE TABLE `t1` (
`id` int(11) NOT NULL auto_increment,
`id_cns` tinyint(3) unsigned NOT NULL default '0',
`tipo` enum('','UNO','DUE') NOT NULL default '',
`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
`particolare` mediumint(8) unsigned NOT NULL default '0',
`generale` mediumint(8) unsigned NOT NULL default '0',
`bis` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY  (`id`),
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
1611
);
unknown's avatar
unknown committed
1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623
INSERT INTO `t1` VALUES (1,16,'UNO',1987,2048,9681,0),(2,50,'UNO',1987,1536,13987,0),(3,16,'UNO',1987,2432,14594,0),(4,16,'UNO',1987,1792,13422,0),(5,16,'UNO',1987,1025,10240,0),(6,16,'UNO',1987,1026,7089,0);
CREATE TABLE `t2` (
`id` tinyint(3) unsigned NOT NULL auto_increment,
`max_anno_dep` smallint(6) unsigned NOT NULL default '0',
PRIMARY KEY  (`id`)
);
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
SELECT cns.id, cns.max_anno_dep, cns.max_anno_dep = (SELECT s.anno_dep FROM t1 AS s WHERE s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM t2 AS cns;
id	max_anno_dep	PIPPO
16	1987	1
50	1990	0
51	1990	NULL
unknown's avatar
unknown committed
1624
DROP TABLE t1, t2;
1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635
create table t1 (a int);
insert into t1 values (1), (2), (3);
SET SQL_SELECT_LIMIT=1;
select sum(a) from (select * from t1) as a;
sum(a)
6
select 2 in (select * from t1);
2 in (select * from t1)
1
SET SQL_SELECT_LIMIT=default;
drop table t1;
1636 1637 1638 1639 1640 1641 1642 1643
CREATE TABLE t1 (a int, b int, INDEX (a));
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
a	b
1	1
1	2
1	3
DROP TABLE t1;
1644 1645 1646 1647 1648
create table t1(val varchar(10));
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');
count(*)
0
unknown's avatar
unknown committed
1649
drop table t1;
1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');
select * from t1 where id not in (select id from t1 where id < 8);
id	text
8	text8
9	text9
10	text10
11	text11
12	text12
select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id	text
8	text8
9	text9
10	text10
11	text11
12	text12
explain extended select * from t1 where id not in (select id from t1 where id < 8);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index; Using where
Warnings:
unknown's avatar
unknown committed
1671
Note	1003	select test.t1.id AS `id`,test.t1.text AS `text` from test.t1 where not(<in_optimizer>(test.t1.id,<exists>(<primary_index_lookup>(<cache>(test.t1.id) in t1 on PRIMARY where (test.t1.id < 8)))))
1672 1673 1674 1675 1676
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	tt	ALL	NULL	NULL	NULL	NULL	12	Using where
2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.tt.id	7	Using where; Using index
Warnings:
unknown's avatar
unknown committed
1677
Note	1276	Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
unknown's avatar
unknown committed
1678
Note	1003	select test.tt.id AS `id`,test.tt.text AS `text` from test.t1 tt where not(exists(select test.t1.id AS `id` from test.t1 where ((test.t1.id < 8) and ((test.t1.id = test.tt.id) or isnull(test.t1.id))) having (test.t1.id is not null)))
1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
id	text	id	text	id	text
1	text1	1	text1	1	text1
2	text2	2	text2	2	text2
3	text3	3	text3	3	text3
4	text4	4	text4	4	text4
5	text5	5	text5	5	text5
6	text6	6	text6	6	text6
7	text7	7	text7	7	text7
8	text8	8	text8	8	text8
9	text9	9	text9	9	text9
10	text10	10	text10	10	text10
11	text11	11	text1	11	text11
12	text12	12	text2	12	text12
1000	text1000	NULL	NULL	1000	text1000
1001	text1001	NULL	NULL	1000	text1000
explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	14	
1	SIMPLE	b	eq_ref	PRIMARY	PRIMARY	4	test.a.id	2	
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where
Warnings:
unknown's avatar
unknown committed
1704
Note	1003	select test.a.id AS `id`,test.a.text AS `text`,test.b.id AS `id`,test.b.text AS `text`,test.c.id AS `id`,test.c.text AS `text` from test.t1 a left join test.t2 b on(((test.a.id = test.b.id) or isnull(test.b.id))) join test.t1 c where (if(isnull(test.b.id),1000,test.b.id) = test.c.id)
1705
drop table t1,t2;
unknown's avatar
merge  
unknown committed
1706
create table t1 (a int);
1707 1708 1709 1710 1711 1712
insert into t1 values (1);
explain select benchmark(1000, (select a from t1 where a=sha(rand())));
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
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
drop table t1;
unknown's avatar
unknown committed
1713 1714 1715 1716 1717
create table t1(id int);
create table t2(id int);
create table t3(flag int);
select (select * from t3 where id not null) from t1, t2;
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 near 'null) from t1, t2' at line 1
1718
drop table t1,t2,t3;
1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1);
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
id	c
1	1
2	0
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;
id	c
1	1
2	0
1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819
DROP TABLE t1,t2;
CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
a
3
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
a
1
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
a
2
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
a
2
3
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
a
1
2
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
a
1
3
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
a
3
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
a
1
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
a
2
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
a
2
3
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
a
1
2
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
a
1
3
ALTER TABLE t1 ADD INDEX (a);
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
a
3
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
a
1
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
a
2
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
a
2
3
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
a
1
2
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
a
1
3
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
a
3
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
a
1
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
a
2
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
a
2
3
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
a
1
2
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
a
1
3
DROP TABLE t1;