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