subselect.result 13.4 KB
Newer Older
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
1 2 3
select (select 2);
(select 2)
2
4 5 6 7
SELECT (SELECT 1) UNION SELECT (SELECT 2);
(SELECT 1)
1
2
8 9 10
SELECT (SELECT (SELECT 0 UNION SELECT 0));
(SELECT (SELECT 0 UNION SELECT 0))
0
11
SELECT (SELECT 1 FROM (SELECT 1) HAVING a=1) as a;
12
Reference 'a' not supported (forward reference in item list)
13
SELECT (SELECT 1 FROM (SELECT 1) HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) HAVING a=1) as b;
14
Reference 'b' not supported (forward reference in item list)
15
drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit;
16 17 18 19 20 21 22
create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
create table t4 (a int, b int);
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
23 24
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
Reference 'a1' not supported (forward reference in item list)
25 26 27 28 29 30 31 32
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
33 34 35 36 37 38 39 40 41 42 43 44
select (select a from t1), a from t2;
(select a from t1)	a
2	1
2	2
select (select a from t3), a from t2;
(select a from t3)	a
NULL	1
NULL	2
select * from t2 where t2.a=(select a from t1);
a	b
2	7
insert into t3 values (6),(7),(3);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
45
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
46 47 48
a	b
1	7
2	7
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
49
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
50 51 52 53 54
union (select * from t4 order by a limit 2) limit 3;
a	b
1	7
2	7
3	8
55
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
56 57 58 59 60 61
union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
a	b
1	7
2	7
3	8
4	8
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
62 63 64
explain select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
65
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	where used
66
2	SUBSELECT	t3	ALL	NULL	NULL	NULL	NULL	3	Using filesort
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
67 68
3	UNION	t4	ALL	NULL	NULL	NULL	NULL	3	where used; Using filesort
4	SUBSELECT	t2	ALL	NULL	NULL	NULL	NULL	2	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
69 70 71 72
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
(select a from t3 where a<t2.a*4 order by 1 desc limit 1)	a
3	1
7	2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
73 74 75 76
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
(select * from t2 where a>1) as tt;
(select t3.a from t3 where a<8 order by 1 desc limit 1)	a
7	2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
77 78 79
explain select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
(select * from t2 where a>1) as tt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
80
1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	
81
3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	where used
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
82
2	SUBSELECT	t3	ALL	NULL	NULL	NULL	NULL	3	where used; Using filesort
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
83 84 85 86 87 88 89 90 91 92 93
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
94 95
8	4.5000
9	7.5000
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
96 97
explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
98
1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
99 100
2	DEPENDENT SUBSELECT	t2	ALL	NULL	NULL	NULL	NULL	2	
3	DEPENDENT SUBSELECT	t3	ALL	NULL	NULL	NULL	NULL	3	where used
101 102 103 104 105 106 107
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
108 109 110 111 112 113 114 115
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9);
select b,max(a) as ma from t4 group by b having b < (select max(t2.a)
from t2 where t2.b=t4.b);
b	ma
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a)
from t2 where t2.b=t4.b);
b	ma
7	12
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
116 117 118 119 120 121 122 123 124 125 126 127 128 129 130
create table t5 (a int);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)	a
NULL	1
2	2
insert into t5 values (5);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)	a
NULL	1
2	2
insert into t5 values (2);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)	a
NULL	1
2	2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
131 132
explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
133
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
134 135
2	DEPENDENT SUBSELECT	t1	system	NULL	NULL	NULL	NULL	1	where used
3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	where used
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
136
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
137
Subselect returns more than 1 record
138 139 140 141 142 143 144 145 146
create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq));
create table clinic( uq int primary key, name char(25));
insert into clinic values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
insert into attend values (1,1),(1,2),(2,2),(1,3);
select * from attend where exists (select * from clinic where uq = clinic_uq);
patient_uq	clinic_uq
1	1
1	2
2	2
147 148
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
Column: 'a' in field list is ambiguous
149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164
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
165 166 167 168 169 170 171 172 173 174
drop table if exists inscrit;
CREATE TABLE `inscrit` (
`pseudo` varchar(35) character set latin1 NOT NULL default '',
`email` varchar(60) character set latin1 NOT NULL default '',
PRIMARY KEY  (`pseudo`),
UNIQUE KEY `email` (`email`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
INSERT INTO inscrit (pseudo,email) VALUES ('joce','test');
INSERT INTO inscrit (pseudo,email) VALUES ('joce1','test1');
INSERT INTO inscrit (pseudo,email) VALUES ('2joce1','2test1');
175
EXPLAIN SELECT pseudo,(SELECT email FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce')) FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce');
176 177 178 179 180
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	inscrit	const	PRIMARY	PRIMARY	35	const	1	
4	SUBSELECT	inscrit	const	PRIMARY	PRIMARY	35	const	1	
2	SUBSELECT	inscrit	const	PRIMARY	PRIMARY	35	const	1	
3	SUBSELECT	inscrit	const	PRIMARY	PRIMARY	35	const	1	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
181 182 183 184 185 186
SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM
inscrit WHERE pseudo='joce');
Subselect returns more than 1 field
SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit WHERE
pseudo='joce');
Subselect returns more than 1 field
187 188 189
SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce');
pseudo
joce
190 191
SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo LIKE '%joce%');
Subselect returns more than 1 record
192
drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit;
193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209
drop table if exists searchconthardwarefr3;
CREATE TABLE `searchconthardwarefr3` (
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
INSERT INTO searchconthardwarefr3 (topic,date,pseudo) VALUES
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
EXPLAIN SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	searchconthardwarefr3	index	NULL	PRIMARY	41	NULL	2	where used; Using index
EXPLAIN SELECT (SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY								No tables used
2	SUBSELECT	searchconthardwarefr3	index	NULL	PRIMARY	41	NULL	2	where used; Using index
210 211 212 213 214 215
SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03';
date
2002-08-03
SELECT (SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03');
(SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03')
2002-08-03
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
216 217 218 219 220 221 222 223 224 225 226 227
SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
1
1
1
1
SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
Subselect returns more than 1 record
EXPLAIN SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION SELECT 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	searchconthardwarefr3	index	NULL	topic	3	NULL	2	Using index
2	SUBSELECT								No tables used
3	UNION								No tables used
228
drop table searchconthardwarefr3;
229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244
drop table if exists forumconthardwarefr7, searchconthardwarefr7;
CREATE TABLE `forumconthardwarefr7` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM ROW_FORMAT=FIXED;
INSERT INTO forumconthardwarefr7 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
CREATE TABLE `searchconthardwarefr7` (
`mot` varchar(30) NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) NOT NULL default '',
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
INSERT INTO searchconthardwarefr7 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
245 246 247
select numeropost as a FROM forumconthardwarefr7 GROUP BY (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
a
40143
248 249 250 251
SELECT numeropost,maxnumrep FROM forumconthardwarefr7 WHERE exists (SELECT 1 FROM searchconthardwarefr7 WHERE (mot='joce') AND date >= '2002-10-21' AND forumconthardwarefr7.numeropost = searchconthardwarefr7.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
numeropost	maxnumrep
43506	2
40143	1
252 253
SELECT (SELECT 1) as a FROM (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
Unknown column 'a' in 'having clause'
254
drop table forumconthardwarefr7, searchconthardwarefr7;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
255 256 257 258 259 260 261 262 263 264
drop table if exists forumconthardwarefr7;
CREATE TABLE `forumconthardwarefr7` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM ROW_FORMAT=FIXED;
INSERT INTO forumconthardwarefr7 (numeropost,maxnumrep) VALUES (1,0),(2,1);
select numeropost as a FROM forumconthardwarefr7 GROUP BY (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
Subselect returns more than 1 record
265 266
select numeropost as a FROM forumconthardwarefr7 ORDER BY (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
Subselect returns more than 1 record
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
267
drop table if exists forumconthardwarefr7;
268 269 270 271 272 273
drop table if exists iftest;
CREATE TABLE iftest (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO iftest VALUES ();
SELECT field FROM iftest WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) HAVING field='b');
Subselect returns more than 1 record
drop table iftest;
274 275 276 277 278 279 280 281 282
drop table if exists threadhardwarefr7;
CREATE TABLE `threadhardwarefr7` (
`numeropost` mediumint(8) unsigned NOT NULL default '0',
`numreponse` int(10) unsigned NOT NULL auto_increment,
`pseudo` varchar(35) NOT NULL default '',
PRIMARY KEY  (`numeropost`,`numreponse`),
UNIQUE KEY `numreponse` (`numreponse`),
KEY `pseudo` (`pseudo`,`numeropost`)
) TYPE=MyISAM;
283 284 285 286 287 288
SELECT (SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=a),numreponse FROM (SELECT * FROM threadhardwarefr7) as a;
Reference 'numreponse' not supported (forward reference in item list)
SELECT numreponse, (SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=a) FROM (SELECT * FROM threadhardwarefr7) as a;
Unknown column 'a' in 'having clause'
SELECT numreponse, (SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=1) FROM (SELECT * FROM threadhardwarefr7) as a;
numreponse	(SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=1)
289
INSERT INTO threadhardwarefr7 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
290 291
EXPLAIN SELECT numreponse FROM threadhardwarefr7 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM threadhardwarefr7 WHERE numeropost='1');
Subselect returns more than 1 record
292 293 294 295 296 297 298 299
EXPLAIN SELECT MAX(numreponse) FROM threadhardwarefr7 WHERE numeropost='1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE								Select tables optimized away
EXPLAIN SELECT numreponse FROM threadhardwarefr7 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM threadhardwarefr7 WHERE numeropost='1');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	threadhardwarefr7	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	
2	SUBSELECT								Select tables optimized away
drop table if exists threadhardwarefrtest7;