subselect.test 123 KB
Newer Older
1 2 3 4 5
# Initialise
--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
--enable_warnings

unknown's avatar
unknown committed
6
select (select 2);
unknown's avatar
unknown committed
7
explain extended select (select 2);
8
SELECT (SELECT 1) UNION SELECT (SELECT 2);
unknown's avatar
unknown committed
9
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
unknown's avatar
unknown committed
10
SELECT (SELECT (SELECT 0 UNION SELECT 0));
unknown's avatar
unknown committed
11
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
Matthias Leich's avatar
Matthias Leich committed
12
-- error ER_ILLEGAL_REFERENCE
13
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
Matthias Leich's avatar
Matthias Leich committed
14
-- error ER_ILLEGAL_REFERENCE
15 16
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;
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
Matthias Leich's avatar
Matthias Leich committed
17
-- error ER_ILLEGAL_REFERENCE
unknown's avatar
unknown committed
18
SELECT (SELECT a) as a;
unknown's avatar
unknown committed
19
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
unknown's avatar
unknown committed
20
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
Matthias Leich's avatar
Matthias Leich committed
21
-- error ER_BAD_FIELD_ERROR
22
SELECT (SELECT 1), a;
23
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
Matthias Leich's avatar
Matthias Leich committed
24
-- error ER_BAD_FIELD_ERROR
25
SELECT 1 FROM (SELECT (SELECT a) b) c;
26
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
Matthias Leich's avatar
Matthias Leich committed
27
-- error ER_OPERAND_COLUMNS
28
SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
unknown's avatar
unknown committed
29
SELECT 1 IN (SELECT 1);
unknown's avatar
unknown committed
30
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
Matthias Leich's avatar
Matthias Leich committed
31
-- error ER_WRONG_USAGE
32
select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
33
-- error ER_WRONG_USAGE
34
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
35
-- error ER_BAD_FIELD_ERROR
unknown's avatar
unknown committed
36
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
37
-- error ER_BAD_FIELD_ERROR
unknown's avatar
unknown committed
38
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
39 40 41 42 43 44 45 46 47 48 49 50
SELECT (SELECT 1,2,3) = ROW(1,2,3);
SELECT (SELECT 1,2,3) = ROW(1,2,1);
SELECT (SELECT 1,2,3) < ROW(1,2,1);
SELECT (SELECT 1,2,3) > ROW(1,2,1);
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
SELECT ROW(1,2,3) = (SELECT 1,2,3);
SELECT ROW(1,2,3) = (SELECT 1,2,1);
SELECT ROW(1,2,3) < (SELECT 1,2,1);
SELECT ROW(1,2,3) > (SELECT 1,2,1);
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
51
SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
52
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
53
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
54
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
55

Matthias Leich's avatar
Matthias Leich committed
56
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
57 58
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);

59 60
SELECT 1 as a,(SELECT a+a) b,(SELECT b);

61 62 63
create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
64
create table t4 (a int not null, b int not null);
65 66 67
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
Matthias Leich's avatar
Matthias Leich committed
68
-- error ER_ILLEGAL_REFERENCE
69
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
70 71
select (select a from t1 where t1.a=t2.a), a from t2;
select (select a from t1 where t1.a=t2.b), a from t2;
unknown's avatar
unknown committed
72
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
73 74 75
select (select a from t3), a from t2;
select * from t2 where t2.a=(select a from t1);
insert into t3 values (6),(7),(3);
unknown's avatar
unknown committed
76
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
unknown's avatar
unknown committed
77 78
(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;
(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
79
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
80
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
81
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
unknown's avatar
unknown committed
82
(select * from t2 where a>1) as tt;
83
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
unknown's avatar
unknown committed
84
(select * from t2 where a>1) as tt;
unknown's avatar
unknown committed
85 86 87 88
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);
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);
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);
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
89
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
90 91
select * from t3 where exists (select * from t2 where t2.b=t3.a);
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
unknown's avatar
unknown committed
92 93
select * from t3 where a in (select b from t2);
select * from t3 where a not in (select b from t2);
unknown's avatar
unknown committed
94 95
select * from t3 where a = some (select b from t2);
select * from t3 where a <> any (select b from t2);
96 97

# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);
unknown's avatar
unknown committed
98
select * from t3 where a = all (select b from t2);
99

unknown's avatar
unknown committed
100 101 102 103 104
select * from t3 where a <> all (select b from t2);
insert into t2 values (100, 5);
select * from t3 where a < any (select b from t2);
select * from t3 where a < all (select b from t2);
select * from t3 where a >= any (select b from t2);
unknown's avatar
unknown committed
105
explain extended select * from t3 where a >= any (select b from t2);
unknown's avatar
unknown committed
106 107
select * from t3 where a >= all (select b from t2);
delete from t2 where a=100;
Matthias Leich's avatar
Matthias Leich committed
108
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
109
select * from t3 where a in (select a,b from t2);
Matthias Leich's avatar
Matthias Leich committed
110
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
111
select * from t3 where a in (select * from t2);
112
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
unknown's avatar
unknown committed
113
# empty set
114 115 116 117 118
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
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);
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
119 120 121 122 123 124
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;
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;
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;
unknown's avatar
unknown committed
125
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;
Matthias Leich's avatar
Matthias Leich committed
126
-- error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
127
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
128 129 130 131 132
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);
unknown's avatar
unknown committed
133
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
134

135
# not unique fields
Matthias Leich's avatar
Matthias Leich committed
136
-- error ER_NON_UNIQ_ERROR
137 138
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);

139
# different tipes & group functions
140
drop table t1,t2,t3;
141 142 143 144 145 146 147 148 149 150 151

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);
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);

152
CREATE TABLE `t8` (
unknown's avatar
unknown committed
153 154 155 156
  `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
157
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
unknown's avatar
unknown committed
158

159 160 161
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
162
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');
Matthias Leich's avatar
Matthias Leich committed
163
-- error ER_OPERAND_COLUMNS
164 165
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
Matthias Leich's avatar
Matthias Leich committed
166
-- error ER_OPERAND_COLUMNS
167
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
unknown's avatar
unknown committed
168
pseudo='joce');
169
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
Matthias Leich's avatar
Matthias Leich committed
170
-- error ER_SUBQUERY_NO_1_ROW
171
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
unknown's avatar
unknown committed
172

173
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
unknown's avatar
unknown committed
174

unknown's avatar
unknown committed
175
#searchconthardwarefr3 forumconthardwarefr7
176
CREATE TABLE `t1` (
unknown's avatar
unknown committed
177 178 179 180 181
  `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
182
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
183
INSERT INTO t1 (topic,date,pseudo) VALUES
unknown's avatar
unknown committed
184
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
unknown's avatar
unknown committed
185 186
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
187 188 189
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
Matthias Leich's avatar
Matthias Leich committed
190
-- error ER_SUBQUERY_NO_1_ROW
191
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
unknown's avatar
unknown committed
192
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
193
drop table t1;
unknown's avatar
unknown committed
194

195 196
#forumconthardwarefr7 searchconthardwarefr7
CREATE TABLE `t1` (
unknown's avatar
unknown committed
197 198 199 200
  `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
201
) ENGINE=MyISAM ROW_FORMAT=FIXED;
unknown's avatar
unknown committed
202

203
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
unknown's avatar
unknown committed
204

205
CREATE TABLE `t2` (
unknown's avatar
unknown committed
206 207 208 209 210
      `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
211
    ) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
unknown's avatar
unknown committed
212

213 214 215
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);
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;
Matthias Leich's avatar
Matthias Leich committed
216
-- error ER_BAD_FIELD_ERROR
217
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
Matthias Leich's avatar
Matthias Leich committed
218
-- error ER_BAD_FIELD_ERROR
unknown's avatar
unknown committed
219
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
220 221 222

SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
unknown's avatar
unknown committed
223
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
224 225
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
unknown's avatar
unknown committed
226
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
227 228 229
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
unknown's avatar
unknown committed
230 231
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
232 233 234 235
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
unknown's avatar
unknown committed
236
drop table t1,t2;
unknown's avatar
unknown committed
237

238 239
#forumconthardwarefr7
CREATE TABLE `t1` (
unknown's avatar
unknown committed
240 241 242 243
  `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
244
) ENGINE=MyISAM ROW_FORMAT=FIXED;
unknown's avatar
unknown committed
245

246
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
Matthias Leich's avatar
Matthias Leich committed
247
-- error ER_SUBQUERY_NO_1_ROW
248
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
Matthias Leich's avatar
Matthias Leich committed
249
-- error ER_SUBQUERY_NO_1_ROW
250 251
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
drop table t1;
252

253 254 255 256 257
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);
drop table t1;

258 259 260
#iftest
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO t1 VALUES ();
Matthias Leich's avatar
Matthias Leich committed
261
-- error ER_SUBQUERY_NO_1_ROW
262
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
263
drop table t1;
unknown's avatar
unknown committed
264

265 266
# threadhardwarefr7
CREATE TABLE `t1` (
unknown's avatar
unknown committed
267 268 269 270 271 272
  `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
273
) ENGINE=MyISAM;
Matthias Leich's avatar
Matthias Leich committed
274
-- error ER_ILLEGAL_REFERENCE
275
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
Matthias Leich's avatar
Matthias Leich committed
276
-- error ER_BAD_FIELD_ERROR
277 278 279
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
Matthias Leich's avatar
Matthias Leich committed
280
-- error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
281 282 283
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='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
284
drop table t1;
unknown's avatar
unknown committed
285

286 287
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
unknown's avatar
unknown committed
288
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
unknown's avatar
Merge  
unknown committed
289
drop table t1;
290 291 292 293 294 295 296

#update with subselects
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;
Matthias Leich's avatar
Matthias Leich committed
297
-- error ER_UPDATE_TABLE_USED
298
update t1 set b= (select b from t1);
Matthias Leich's avatar
Matthias Leich committed
299
-- error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
300
update t1 set b= (select b from t2);
301 302 303 304
update t1 set b= (select b from t2 where t1.a = t2.a);
select * from t1;
drop table t1, t2;

unknown's avatar
unknown committed
305 306 307 308 309 310 311
#delete with subselects
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;
select * from t1 where b = (select b from t2 where t1.a = t2.a);
Matthias Leich's avatar
Matthias Leich committed
312
-- error ER_UPDATE_TABLE_USED
313
delete from t1 where b = (select b from t1);
Matthias Leich's avatar
Matthias Leich committed
314
-- error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
315
delete from t1 where b = (select b from t2);
unknown's avatar
unknown committed
316 317 318
delete from t1 where b = (select b from t2 where t1.a = t2.a);
select * from t1;
drop table t1, t2;
319

320
#multi-delete with subselects
321

322 323 324 325 326 327 328 329
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;
select * from t12;
Matthias Leich's avatar
Matthias Leich committed
330
-- error ER_UPDATE_TABLE_USED
331
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
Matthias Leich's avatar
Matthias Leich committed
332
-- error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
333
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
334 335 336 337 338
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;
select * from t12;
drop table t11, t12, t2;

339 340 341
#insert with subselects
CREATE TABLE t1 (x int);
create table t2 (a int);
342
create table t3 (b int);
343
insert into t2 values (1);
unknown's avatar
unknown committed
344
insert into t3 values (1),(2);
Matthias Leich's avatar
Matthias Leich committed
345
-- error ER_UPDATE_TABLE_USED
346
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
Matthias Leich's avatar
Matthias Leich committed
347
-- error ER_SUBQUERY_NO_1_ROW
348
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
349 350 351
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
select * from t1;
insert into t2 values (1);
Matthias Leich's avatar
Matthias Leich committed
352
let $row_count_before= `SELECT COUNT(*) FROM t1`;
353
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
Matthias Leich's avatar
Matthias Leich committed
354 355
let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1;
--source include/wait_condition.inc
356 357 358
select * from t1;
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
select * from t1;
359
# After this, only data based on old t1 records should have been added.
360
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
361
select * from t1;
Matthias Leich's avatar
Matthias Leich committed
362
-- error ER_BAD_FIELD_ERROR
363
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
Matthias Leich's avatar
Matthias Leich committed
364
let $row_count_before= `SELECT COUNT(*) FROM t1`;
365
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
Matthias Leich's avatar
Matthias Leich committed
366 367
let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1;
--source include/wait_condition.inc
368
select * from t1;
369
#
Matthias Leich's avatar
Matthias Leich committed
370
#TODO: should be uncommented after Bug#380 fix pushed
371 372
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
#select * from t1;
unknown's avatar
unknown committed
373
drop table t1, t2, t3;
374 375 376 377

#replace with subselects
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
unknown's avatar
unknown committed
378
create table t3 (a int);
379
insert into t2 values (1);
unknown's avatar
unknown committed
380
insert into t3 values (1),(2);
381
select * from t1;
Matthias Leich's avatar
Matthias Leich committed
382
-- error ER_UPDATE_TABLE_USED
383
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
Matthias Leich's avatar
Matthias Leich committed
384
-- error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
385
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
386 387 388 389
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
select * from t1;
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
select * from t1;
Matthias Leich's avatar
Matthias Leich committed
390
let $row_count_before= `SELECT COUNT(*) FROM t1`;
391
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
Matthias Leich's avatar
Matthias Leich committed
392 393 394
# We get one additional row
let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1;
--source include/wait_condition.inc
395
select * from t1;
Matthias Leich's avatar
Matthias Leich committed
396
let $row_count_before= `SELECT COUNT(*) FROM t1 WHERE y = 2`;
397
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
Matthias Leich's avatar
Matthias Leich committed
398 399
let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1 WHERE y = 2;
--source include/wait_condition.inc
400 401 402
select * from t1;
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
select * from t1;
unknown's avatar
unknown committed
403
drop table t1, t2, t3;
unknown's avatar
unknown committed
404

Matthias Leich's avatar
Matthias Leich committed
405
-- error ER_NO_TABLES_USED
406
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
407

unknown's avatar
unknown committed
408
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
unknown's avatar
unknown committed
409 410
INSERT INTO t2 VALUES (1),(2);
SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
411
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
unknown's avatar
unknown committed
412 413
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
414 415
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
416 417
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
Matthias Leich's avatar
Matthias Leich committed
418
-- error ER_UPDATE_TABLE_USED
unknown's avatar
unknown committed
419
INSERT INTO t2 VALUES ((SELECT * FROM t2));
Matthias Leich's avatar
Matthias Leich committed
420
-- error ER_UPDATE_TABLE_USED
unknown's avatar
unknown committed
421
INSERT INTO t2 VALUES ((SELECT id FROM t2));
unknown's avatar
unknown committed
422
SELECT * FROM t2;
unknown's avatar
unknown committed
423
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
unknown's avatar
unknown committed
424
INSERT INTO t1 values (1),(1);
Matthias Leich's avatar
Matthias Leich committed
425
-- error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
426 427
UPDATE t2 SET id=(SELECT * FROM t1);
drop table t2, t1;
428 429

#NULL test
unknown's avatar
unknown committed
430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470
create table t1 (a int);
insert into t1 values (1),(2),(3);
select 1 IN (SELECT * from t1);
select 10 IN (SELECT * from t1);
select NULL IN (SELECT * from t1);
update t1 set a=NULL where a=2;
select 1 IN (SELECT * from t1);
select 3 IN (SELECT * from t1);
select 10 IN (SELECT * from t1);
select 1 > ALL (SELECT * from t1);
select 10 > ALL (SELECT * from t1);
select 1 > ANY (SELECT * from t1);
select 10 > ANY (SELECT * from t1);
drop table t1;
create table t1 (a varchar(20));
insert into t1 values ('A'),('BC'),('DEF');
select 'A' IN (SELECT * from t1);
select 'XYZS' IN (SELECT * from t1);
select NULL IN (SELECT * from t1);
update t1 set a=NULL where a='BC';
select 'A' IN (SELECT * from t1);
select 'DEF' IN (SELECT * from t1);
select 'XYZS' IN (SELECT * from t1);
select 'A' > ALL (SELECT * from t1);
select 'XYZS' > ALL (SELECT * from t1);
select 'A' > ANY (SELECT * from t1);
select 'XYZS' > ANY (SELECT * from t1);
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);
select 10.5 IN (SELECT * from t1);
select NULL IN (SELECT * from t1);
update t1 set a=NULL where a=2.5;
select 1.5 IN (SELECT * from t1);
select 3.5 IN (SELECT * from t1);
select 10.5 IN (SELECT * from t1);
select 1.5 > ALL (SELECT * from t1);
select 10.5 > ALL (SELECT * from t1);
select 1.5 > ANY (SELECT * from t1);
select 10.5 > ANY (SELECT * from t1);
unknown's avatar
unknown committed
471
explain extended select (select a+1) from t1;
unknown's avatar
unknown committed
472 473 474 475 476 477 478 479 480 481 482 483
select (select a+1) from t1;
drop table t1;

#
# Null with keys
#

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;
unknown's avatar
unknown committed
484
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
485 486 487
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;
unknown's avatar
unknown committed
488
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
489
drop table t1,t2,t3;
490 491

#LIMIT is not supported now
unknown's avatar
unknown committed
492
create table t1 (a float);
Matthias Leich's avatar
Matthias Leich committed
493
-- error ER_NOT_SUPPORTED_YET
unknown's avatar
unknown committed
494
select 10.5 IN (SELECT * from t1 LIMIT 1);
Matthias Leich's avatar
Matthias Leich committed
495
-- error ER_NOT_SUPPORTED_YET
unknown's avatar
unknown committed
496 497
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
drop table t1;
498

499 500 501 502 503 504 505 506
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;
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;
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;
drop table t1,t2;
unknown's avatar
unknown committed
507

508 509 510 511 512 513 514 515 516 517 518
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);
select ROW(1, 2, 'a') IN (select a,b,c from t1);
select ROW(1, 1, 'a') IN (select b,a,c from t1);
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
Matthias Leich's avatar
Matthias Leich committed
519
-- error ER_NOT_SUPPORTED_YET
520 521
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
drop table t1;
unknown's avatar
unknown committed
522

523 524 525
#
# DO & SET
#
526 527 528
create table t1 (a int);
insert into t1 values (1);
do @a:=(SELECT a from t1);
unknown's avatar
unknown committed
529
select @a;
unknown's avatar
unknown committed
530
set @a:=2;
unknown's avatar
merge  
unknown committed
531
set @a:=(SELECT a from t1);
unknown's avatar
unknown committed
532
select @a;
533
drop table t1;
Matthias Leich's avatar
Matthias Leich committed
534
-- error ER_NO_SUCH_TABLE
535
do (SELECT a from t1);
Matthias Leich's avatar
Matthias Leich committed
536
-- error ER_NO_SUCH_TABLE
unknown's avatar
merge  
unknown committed
537
set @a:=(SELECT a from t1);
538

539
CREATE TABLE t1 (a int, KEY(a));
540
HANDLER t1 OPEN;
Matthias Leich's avatar
Matthias Leich committed
541
-- error ER_PARSE_ERROR
542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559
HANDLER t1 READ a=((SELECT 1));
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));
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);
drop table t1, t2;

unknown's avatar
unknown committed
560
CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY  (`i`)) ENGINE=MyISAM CHARSET=latin1;
561 562
INSERT INTO t1 VALUES (1);
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
563
select * from t1;
564 565 566
drop table t1;

#test of uncacheable subqueries
unknown's avatar
unknown committed
567
CREATE TABLE t1 (a int(1));
unknown's avatar
unknown committed
568 569 570
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
unknown's avatar
unknown committed
571
drop table t1;
572 573 574 575 576 577 578 579 580 581


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
582
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
583 584 585 586 587 588 589 590 591

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
592
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
593 594 595 596 597 598

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
599
) ENGINE=MyISAM CHARSET=latin1;
600 601 602 603 604 605 606 607 608 609 610 611 612
INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');

INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');

INSERT INTO t3 VALUES (1,1);

SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
numeropost=topic);
select * from t1;
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
select * from t1;

613 614 615 616 617 618 619 620 621 622 623 624 625
drop table t1, t2, t3;

SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
SHOW CREATE TABLE t1;
drop table t1;
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
SHOW CREATE TABLE t1;
drop table t1;
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
SHOW CREATE TABLE t1;
drop table t1;
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
626
select * from t1;
627 628
SHOW CREATE TABLE t1;
drop table t1;
629 630 631

create table t1 (a int);
insert into t1 values (1), (2), (3);
unknown's avatar
unknown committed
632
explain extended select a,(select (select rand() from t1 limit 1)  from t1 limit 1)
633 634
from t1;
drop table t1;
635 636 637 638

#
# error in IN
#
Matthias Leich's avatar
Matthias Leich committed
639
-- error ER_NO_SUCH_TABLE
640
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);
unknown's avatar
unknown committed
641 642 643 644 645 646 647 648 649 650 651 652

#
# complex subquery
#

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
653
) ENGINE=MyISAM;
unknown's avatar
unknown committed
654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675

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
676
) ENGINE=MyISAM;
unknown's avatar
unknown committed
677 678

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');
679
INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');
unknown's avatar
unknown committed
680

681
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);
unknown's avatar
unknown committed
682 683

drop table t1, t2;
684 685 686 687 688 689 690 691

#
# constants in IN
#
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
692
  UNIQUE KEY `pseudo` (`pseudo`)
unknown's avatar
unknown committed
693
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
694 695
INSERT INTO t1 (pseudo) VALUES ('test');
SELECT 0 IN (SELECT 1 FROM t1 a);
unknown's avatar
unknown committed
696
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
697 698
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
unknown's avatar
unknown committed
699
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
700
drop table t1;
unknown's avatar
Merge  
unknown committed
701

702 703 704
CREATE TABLE `t1` (
  `i` int(11) NOT NULL default '0',
  PRIMARY KEY  (`i`)
unknown's avatar
unknown committed
705
) ENGINE=MyISAM CHARSET=latin1;
706 707 708 709

INSERT INTO t1 VALUES (1);
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
Matthias Leich's avatar
Matthias Leich committed
710
-- error ER_BAD_FIELD_ERROR
711
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
712
select * from t1;
713
drop table t1;
unknown's avatar
unknown committed
714 715 716 717 718 719

#
# Multi update test
#
CREATE TABLE t1 (
  id int(11) default NULL
unknown's avatar
unknown committed
720
) ENGINE=MyISAM CHARSET=latin1;
unknown's avatar
unknown committed
721 722 723 724
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
725
) ENGINE=MyISAM CHARSET=latin1;
unknown's avatar
unknown committed
726 727 728 729 730

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;
drop table t1,t2;
731 732 733 734

#
# correct NULL in <CONSTANT> IN (SELECT ...)
#
735 736 737
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));
738
drop table t1;
unknown's avatar
unknown committed
739 740 741 742 743 744

#
# Test optimization for sub selects
#
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
745
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
unknown's avatar
unknown committed
746
drop table t1;
747 748 749 750 751 752 753 754 755 756

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
757
) ENGINE=MyISAM CHARSET=cp1251;
758 759 760
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);
DROP TABLE t1;
unknown's avatar
unknown committed
761

762 763 764 765 766 767 768 769 770 771 772 773 774
#
# uninterruptable update
#
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);
select * from t1;

drop table t1, t2;
unknown's avatar
unknown committed
775

776 777 778 779 780 781 782 783 784 785
#
# reduced subselect in ORDER BY & GROUP BY clauses
#

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
786
  UNIQUE KEY `pseudo` (`pseudo`)
unknown's avatar
unknown committed
787
) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
788 789 790
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);
drop table if exists t1;
unknown's avatar
unknown committed
791 792

(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
unknown's avatar
unknown committed
793

794 795 796 797 798 799 800 801 802 803
#
# IN subselect optimization test
#
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);
unknown's avatar
unknown committed
804
explain extended select * from t2 where t2.a in (select a from t1);
805
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
unknown's avatar
unknown committed
806
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
807
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
unknown's avatar
unknown committed
808
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
809
drop table t1, t2, t3;
810
create table t1 (a int, b int, index a (a,b));
811 812 813 814 815 816 817 818
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);
disable_query_log;
# making table large enough
let $1 = 10000;
while ($1)
 {
819
  eval insert into t1 values (rand()*100000+200,rand()*100000);
820 821 822 823 824 825
  dec $1;
 }
enable_query_log;
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);
unknown's avatar
unknown committed
826
explain extended select * from t2 where t2.a in (select a from t1);
827
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
unknown's avatar
unknown committed
828
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
829
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
unknown's avatar
unknown committed
830
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
831 832 833
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
unknown's avatar
unknown committed
834
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
835
drop table t1, t2, t3;
836

837 838 839 840 841 842 843 844
#
# alloc_group_fields() working
#
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);
845
insert into t3 values (3,3), (2,2), (1,1);
846
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;
847 848
drop table t1,t2,t3;

849 850 851 852 853 854 855 856 857
#
# aggregate functions in HAVING test
#
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);
drop table t1,t2;
858

859 860 861 862 863 864 865 866
#
# update subquery with wrong field (to force name resolving
# in UPDATE name space)
#
create table t1 (s1 int);
create table t2 (s1 int);
insert into t1 values (1);
insert into t2 values (1);
Matthias Leich's avatar
Matthias Leich committed
867
-- error ER_BAD_FIELD_ERROR
868 869 870
update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
DROP TABLE t1, t2;

871 872 873 874 875 876
#
# collation test
#
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
                 s2 CHAR(5) COLLATE latin1_swedish_ci);
INSERT INTO t1 VALUES ('z','?');
Matthias Leich's avatar
Matthias Leich committed
877
-- error ER_CANT_AGGREGATE_2COLLATIONS
878
select * from t1 where s1 > (select max(s2) from t1);
Matthias Leich's avatar
Matthias Leich committed
879
-- error ER_CANT_AGGREGATE_2COLLATIONS
880 881
select * from t1 where s1 > any (select max(s2) from t1);
drop table t1;
882 883 884 885 886 887 888 889 890 891

#
# aggregate functions reinitialization
#
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);
drop table t1, t2;
unknown's avatar
unknown committed
892 893 894 895 896

#
# row union
#
create table t1 (s1 char(5));
Matthias Leich's avatar
Matthias Leich committed
897
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
898 899 900
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
insert into t1 values ('tttt');
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
unknown's avatar
unknown committed
901
explain extended (select * from t1);
unknown's avatar
unknown committed
902
(select * from t1);
unknown's avatar
unknown committed
903
drop table t1;
904 905 906 907

#
# IN optimisation test results
#
908
create table t1 (s1 char(5), index s1(s1));
909 910 911 912
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;
unknown's avatar
unknown committed
913 914
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
915
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
unknown's avatar
unknown committed
916
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
unknown's avatar
merge  
unknown committed
917 918
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
unknown's avatar
unknown committed
919
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
920
drop table t1,t2;
921

922 923 924 925 926 927 928
#
# correct ALL optimisation
#
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);
unknown's avatar
unknown committed
929
explain extended select * from t3 where a >= all (select b from t2);
930 931 932 933 934 935 936 937 938 939 940 941 942 943
select * from t3 where a >= some (select b from t2);
explain extended select * from t3 where a >= some (select b from t2);
select * from t3 where a >= all (select b from t2 group by 1);
explain extended select * from t3 where a >= all (select b from t2 group by 1);
select * from t3 where a >= some (select b from t2 group by 1);
explain extended select * from t3 where a >= some (select b from t2 group by 1);
select * from t3 where NULL >= any (select b from t2);
explain extended select * from t3 where NULL >= any (select b from t2);
select * from t3 where NULL >= any (select b from t2 group by 1);
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
select * from t3 where NULL >= some (select b from t2);
explain extended select * from t3 where NULL >= some (select b from t2);
select * from t3 where NULL >= some (select b from t2 group by 1);
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
944
#
945
# optimized static ALL/ANY with grouping
946
#
947 948
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);
unknown's avatar
unknown committed
949
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
950
drop table t2, t3;
951

952 953 954 955
#
# correct used_tables()
#

unknown's avatar
unknown committed
956
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 ;
957
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
958
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;
959
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
960
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 ;
961
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
962
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
963 964 965 966
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;
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;
drop table t1,t2,t3,t4;
unknown's avatar
Merge  
unknown committed
967

968 969 970
#
# cardinality check
#
unknown's avatar
unknown committed
971
CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
972
INSERT INTO t1 VALUES (1),(5);
unknown's avatar
unknown committed
973
CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
974
INSERT INTO t2 VALUES (2),(6);
Matthias Leich's avatar
Matthias Leich committed
975
-- error ER_OPERAND_COLUMNS
976 977
select * from t1 where (1,2,6) in (select * from t2);
DROP TABLE t1,t2;
unknown's avatar
Merge  
unknown committed
978

979 980 981 982 983 984
#
# DO and SET with errors
#
create table t1 (s1 int);
insert into t1 values (1);
insert into t1 values (2);
Matthias Leich's avatar
Matthias Leich committed
985
-- error ER_SUBQUERY_NO_1_ROW
986 987 988
set sort_buffer_size = (select s1 from t1);
do (select * from t1);
drop table t1;
989 990 991 992 993 994 995 996

#
# optimized ALL/ANY with union
#
create table t1 (s1 char);
insert into t1 values ('e');
select * from t1 where 'f' > any (select s1 from t1);
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
997
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
998
drop table t1;
999 1000 1001 1002

#
# filesort in subquery (restoring join_tab)
#
unknown's avatar
unknown committed
1003
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1004
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
unknown's avatar
unknown committed
1005
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
1006 1007 1008
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;
drop table t1, t2;
1009 1010 1011 1012

#
# unresolved field error
#
1013
create table t1 (s1 int);
1014
create table t2 (s1 int);
Matthias Leich's avatar
Matthias Leich committed
1015
-- error ER_BAD_FIELD_ERROR
1016
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
Matthias Leich's avatar
Matthias Leich committed
1017
-- error ER_BAD_FIELD_ERROR
1018
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
Matthias Leich's avatar
Matthias Leich committed
1019
-- error ER_BAD_FIELD_ERROR
1020
select count(*) from t2 group by t1.s2;
unknown's avatar
merge  
unknown committed
1021
drop table t1, t2;
unknown's avatar
merge  
unknown committed
1022

unknown's avatar
unknown committed
1023 1024 1025 1026 1027 1028 1029 1030
#
# fix_fields() in add_ref_to_table_cond()
#
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);
1031
DROP TABLE t1, t2;
1032

unknown's avatar
unknown committed
1033 1034 1035 1036
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;
DROP TABLE t1;
unknown's avatar
unknown committed
1037

1038

1039
#
1040
# Bug#2198 SELECT INTO OUTFILE (with Sub-Select) Problem
1041 1042
#

1043
create table t1 (a int, b decimal(13, 3));
1044
insert into t1 values (1, 0.123);
Matthias Leich's avatar
Matthias Leich committed
1045 1046 1047
let $outfile = $MYSQLTEST_VARDIR/master-data/test/subselect.out.file.1;
--error 0,1
--remove_file $outfile
1048 1049 1050
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;
Matthias Leich's avatar
Matthias Leich committed
1051
--remove_file $outfile
1052 1053
select * from t1;
drop table t1;
1054

1055

1056
#
1057
# Bug#2479 dependant subquery with limit crash
1058 1059
#

unknown's avatar
unknown committed
1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070
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`)
1071
);
unknown's avatar
unknown committed
1072 1073 1074 1075 1076 1077 1078
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);
1079

unknown's avatar
unknown committed
1080
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;
1081

unknown's avatar
unknown committed
1082
DROP TABLE t1, t2;
1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093

#
# GLOBAL LIMIT
#
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;
select 2 in (select * from t1);
SET SQL_SELECT_LIMIT=default;
drop table t1;
unknown's avatar
unknown committed
1094

1095

1096
#
1097
# Bug#3118 subselect + order by
1098 1099 1100 1101 1102 1103
#

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;
DROP TABLE t1;
unknown's avatar
merge  
unknown committed
1104

1105 1106 1107 1108 1109
# Item_cond fix field
#
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%');
unknown's avatar
unknown committed
1110 1111
drop table t1;

1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126
#
# ref_or_null replacing with ref
#
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);
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);
explain extended select * from t1 where id not in (select id from t1 where 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);
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);
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);
drop table t1,t2;
unknown's avatar
merge  
unknown committed
1127

1128 1129 1130 1131 1132 1133 1134
#
# Static tables & rund() in subqueries
#
create table t1 (a int);
insert into t1 values (1);
explain select benchmark(1000, (select a from t1 where a=sha(rand())));
drop table t1;
unknown's avatar
unknown committed
1135

1136

unknown's avatar
unknown committed
1137
#
1138
# Bug#3188 Ambiguous Column in Subselect crashes server
unknown's avatar
unknown committed
1139 1140 1141 1142
#
create table t1(id int);
create table t2(id int);
create table t3(flag int);
Matthias Leich's avatar
Matthias Leich committed
1143
-- error ER_PARSE_ERROR
unknown's avatar
unknown committed
1144
select (select * from t3 where id not null) from t1, t2;
1145
drop table t1,t2,t3;
1146

1147

1148
#
1149
# aggregate functions (Bug#3505 Wrong results on use of ORDER BY with subqueries)
1150 1151 1152 1153 1154
#
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1);
1155
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);
unknown's avatar
unknown committed
1156
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1157
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;
unknown's avatar
unknown committed
1158
SELECT 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 id;
1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177
DROP TABLE t1,t2;

#
# ALL/ANY test
#
CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
unknown's avatar
unknown committed
1178
# with index
1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191
ALTER TABLE t1 ADD INDEX (a);
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
unknown's avatar
unknown committed
1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232
# having clause test
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
# union test
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
# union + having test
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
# row tests
# < > >= <= and = ALL/ <> ANY do not support row operation
Matthias Leich's avatar
Matthias Leich committed
1233
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1234
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1235
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1236
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1237
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1238
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1239
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1240
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1241
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1242
SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1243
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1244
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1245
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1246
SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1247
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1248 1249
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
# following should be converted to IN
Matthias Leich's avatar
Matthias Leich committed
1250
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1251
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1252
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1253 1254
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1255
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1256
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
Matthias Leich's avatar
Matthias Leich committed
1257
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
# without optimisation
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
# without optimisation + having
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
# EXISTS in string contence
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
1298
DROP TABLE t1;
unknown's avatar
unknown committed
1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327
CREATE TABLE t1 ( a double, b double );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
DROP TABLE t1;
CREATE TABLE t1 ( a char(1), b char(1));
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
1328
DROP TABLE t1;
unknown's avatar
unknown committed
1329 1330 1331 1332 1333 1334 1335 1336 1337 1338


#
# SELECT(EXISTS * ...)optimisation
#
create table t1 (a int, b int);
insert into t1 values (1,2),(3,4);
select * from t1 up where exists (select * from t1 where t1.a=up.a);
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
drop table t1;
1339

1340

1341
#
1342
# Bug#4102 subselect in HAVING
1343 1344 1345 1346 1347 1348 1349 1350 1351
#

CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
INSERT INTO t2 VALUES (1, 1), (1, 2);
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
  HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
DROP TABLE t1, t2;
unknown's avatar
unknown committed
1352

1353

1354
#
1355 1356
# Test problem with NULL and derived tables
# (Bug#4097 JOIN with subquery causes entire column to report NULL)
1357 1358 1359 1360 1361 1362 1363 1364
#

CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL);
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
CREATE TABLE t2 (id int(11) default NULL, pet varchar(10) default NULL);
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
drop table t1,t2;
unknown's avatar
Merge  
unknown committed
1365

1366

1367 1368 1369 1370 1371 1372
#
# outer fields resolving in INSERT/REPLACE and CRETE with SELECT
#
CREATE TABLE t1 ( a int, b int );
CREATE TABLE t2 ( c int, d int );
INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
1373
SELECT a AS abc, b FROM t1 outr WHERE b =
1374
  (SELECT MIN(b) FROM t1 WHERE a=outr.a);
1375
INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b =
1376
  (SELECT MIN(b) FROM t1 WHERE a=outr.a);
1377
select * from t2;
1378
CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b =
1379
  (SELECT MIN(b) FROM t1 WHERE a=outr.a);
1380
select * from t3;
1381
prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
unknown's avatar
unknown committed
1382 1383 1384 1385
execute stmt1;
deallocate prepare stmt1;
select * from t2;
drop table t3;
1386
prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
unknown's avatar
unknown committed
1387 1388 1389
execute stmt1;
select * from t3;
deallocate prepare stmt1;
1390
DROP TABLE t1, t2, t3;
1391 1392 1393 1394 1395 1396 1397 1398 1399 1400

#
# Aggregate function comparation with ALL/ANY/SOME subselect
#
CREATE TABLE `t1` ( `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
insert into t1 values (1);
CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
insert into t2 values (1,2);
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
drop table t1,t2;
unknown's avatar
unknown committed
1401

1402

unknown's avatar
unknown committed
1403
#
1404
# Bug#4769 - fulltext in subselect
unknown's avatar
unknown committed
1405
#
1406 1407 1408 1409 1410 1411
create table t1 (a int not null auto_increment primary key, b varchar(40), fulltext(b));
insert into t1 (b) values ('ball'),('ball games'), ('games'), ('foo'), ('foobar'), ('Serg'), ('Sergei'),('Georg'), ('Patrik'),('Hakan');
create table t2 (a int);
insert into t2 values (1),(3),(2),(7);
select a,b from t1 where match(b) against ('Ball') > 0;
select a from t2 where a in (select a from t1 where match(b) against ('Ball') > 0);
unknown's avatar
unknown committed
1412 1413
drop table t1,t2;

1414

1415
#
1416
# Bug#5003 - like in subselect
1417 1418 1419 1420 1421 1422 1423 1424 1425 1426
#
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin);
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
drop table t1;

1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441
#
# Optimized IN with compound index
#
CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
insert into t1 values (1,1),(1,2),(2,1),(2,2);
insert into t2 values (1,2),(2,2);
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
alter table t2 drop primary key;
alter table t2 add key KEY1 (aid, bid);
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
alter table t2 drop key KEY1;
alter table t2 add primary key (bid, aid);
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
drop table t1,t2;
unknown's avatar
unknown committed
1442

1443 1444 1445 1446 1447 1448 1449 1450 1451
#
# resolving fields of grouped outer SELECT
#
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
unknown's avatar
unknown committed
1452 1453
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
1454
drop table t1;
1455 1456 1457 1458

create table t1 (x int);
select  (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x;
drop table t1;
1459 1460 1461 1462 1463 1464 1465 1466 1467

#
# Test of correct maybe_null flag returning by subquwery for temporary table
# creation
#
CREATE TABLE `t1` ( `master` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `slave` int(10) unsigned NOT NULL default '0', `access` int(10) unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);
CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL default '0', `pid` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `level` tinyint(4) unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY  (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a');
Matthias Leich's avatar
Matthias Leich committed
1468
-- error ER_BAD_FIELD_ERROR
1469 1470 1471
SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
SELECT b.ac FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
drop tables t1,t2;
1472

1473

1474
#
1475 1476
# Test for Bug#6462 Same request on same data returns different results
# a.k.a. "Proper cleanup of subqueries is missing for SET and DO statements".
1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490
#
create table t1 (a int not null, b int not null, c int, primary key (a,b));
insert into t1 values (1,1,1), (2,2,2), (3,3,3);
set @b:= 0;
# Let us check that subquery will use covering index
explain select sum(a) from t1 where b > @b;
# This should not crash -debug server due to failing assertion
set @a:= (select sum(a) from t1 where b > @b);
# And this should not falsely report index usage
explain select a from t1 where c=2;
# Same for DO statement
do @a:= (select sum(a) from t1 where b > @b);
explain select a from t1 where c=2;
drop table t1;
unknown's avatar
Merge  
unknown committed
1491

1492 1493 1494 1495 1496 1497
#
# Subselect in non-select command just after connection
#
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connection root;
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
1498 1499
connection default;
disconnect root;
1500 1501

#
1502
# primary query with temporary table and subquery with grouping
1503 1504 1505 1506 1507 1508 1509
#
create table t1 (a int, b int);
create table t2 (a int, b int);
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
insert into t2 values (1,3),(2,1);
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
drop table t1, t2;
1510 1511 1512 1513 1514 1515 1516 1517 1518 1519

#
# subqueries with full text search
#
create table t1 (id int);
create table t2 (id int, body text, fulltext (body));
insert into t1 values(1),(2),(3);
insert into t2 values (1,'test'), (2,'mysql'), (3,'test'), (4,'test');
select count(distinct id) from t1 where id in (select id from t2 where match(body) against ('mysql' in boolean mode));
drop table t2,t1;
unknown's avatar
merge  
unknown committed
1520

1521 1522 1523 1524 1525 1526 1527
#
# Equal operation under row and empty subquery
#
create table t1 (s1 int,s2 int);
insert into t1 values (20,15);
select * from t1 where  (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
drop table t1;
unknown's avatar
merge  
unknown committed
1528

1529 1530 1531 1532 1533 1534 1535 1536
#
# ALL/ANY with NULL
#
create table t1 (s1 int);
insert into t1 values (1),(null);
select * from t1 where s1 < all (select s1 from t1);
select s1, s1 < all (select s1 from t1) from t1;
drop table t1;
1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556

#
# reference on changable fields from subquery
#
CREATE TABLE t1 (
  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 ''
unknown's avatar
unknown committed
1557
) ENGINE=MyISAM;
1558 1559
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS');
1560
INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
1561 1562
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
1563 1564
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
  Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
1565
  Population < 200);
1566
drop table t1;
unknown's avatar
unknown committed
1567

1568

1569
#
1570 1571
# Test for Bug#7885 Server crash when 'any' subselect compared to
#                   non-existant field.
1572
#
unknown's avatar
unknown committed
1573 1574
create table t1 (a1 int);
create table t2 (b1 int);
Matthias Leich's avatar
Matthias Leich committed
1575
--error ER_BAD_FIELD_ERROR
unknown's avatar
unknown committed
1576 1577 1578 1579
select * from t1 where a2 > any(select b1 from t2);
select * from t1 where a1 > any(select b1 from t2);
drop table t1,t2;

1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590

#
# Comparison subquery with * and row
#
create table t1 (a integer, b integer);
select (select * from t1) = (select 1,2);
select (select 1,2) = (select * from t1);
# queries whih can be converted to IN
select  row(1,2) = ANY (select * from t1);
select  row(1,2) != ALL (select * from t1);
drop table t1;
1591 1592 1593 1594 1595

#
# Comparison subquery and row with nested rows
#
create table t1 (a integer, b integer);
Matthias Leich's avatar
Matthias Leich committed
1596
-- error ER_OPERAND_COLUMNS
1597
select row(1,(2,2)) in (select * from t1 );
Matthias Leich's avatar
Matthias Leich committed
1598
-- error ER_OPERAND_COLUMNS
1599
select row(1,(2,2)) = (select * from t1 );
Matthias Leich's avatar
Matthias Leich committed
1600
-- error ER_OPERAND_COLUMNS
1601 1602
select (select * from t1) = row(1,(2,2));
drop table t1;
1603 1604 1605 1606 1607 1608

#
# Forward reference detection
#
create  table t1 (a integer);
insert into t1 values (1);
Matthias Leich's avatar
Matthias Leich committed
1609
-- error ER_ILLEGAL_REFERENCE
1610
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
Matthias Leich's avatar
Matthias Leich committed
1611
-- error ER_ILLEGAL_REFERENCE
1612 1613
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
select 1 as xx, 1 = ALL (  select 1 from t1 where 1 = xx );
Matthias Leich's avatar
Matthias Leich committed
1614
-- error ER_ILLEGAL_REFERENCE
1615
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
Matthias Leich's avatar
Matthias Leich committed
1616
-- error ER_ILLEGAL_REFERENCE
1617 1618
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL;
drop table t1;
unknown's avatar
unknown committed
1619

1620

1621
#
1622
# Test for Bug#8218 Join does not pass string from right table
1623
#
unknown's avatar
unknown committed
1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 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 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691
CREATE TABLE t1 (
  categoryId int(11) NOT NULL,
  courseId int(11) NOT NULL,
  startDate datetime NOT NULL,
  endDate datetime NOT NULL,
  createDate datetime NOT NULL,
  modifyDate timestamp NOT NULL,
  attributes text NOT NULL
);
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');

CREATE TABLE t2 (
  userId int(11) NOT NULL,
  courseId int(11) NOT NULL,
  date datetime NOT NULL
);
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');


CREATE TABLE t3 (
  groupId int(11) NOT NULL,
  parentId int(11) NOT NULL,
  startDate datetime NOT NULL,
  endDate datetime NOT NULL,
  createDate datetime NOT NULL,
  modifyDate timestamp NOT NULL,
  ordering int(11)
);
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);

CREATE TABLE t4 (
  id int(11) NOT NULL,
  groupTypeId int(11) NOT NULL,
  groupKey varchar(50) NOT NULL,
  name text,
  ordering int(11),
  description text,
  createDate datetime NOT NULL,
  modifyDate timestamp NOT NULL
);
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');

CREATE TABLE t5 (
  userId int(11) NOT NULL,
  groupId int(11) NOT NULL,
  createDate datetime NOT NULL,
  modifyDate timestamp NOT NULL
);
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');

select
  count(distinct t2.userid) pass,
  groupstuff.*,
  count(t2.courseid) crse,
1692
  t1.categoryid,
unknown's avatar
unknown committed
1693 1694
  t2.courseid,
  date_format(date, '%b%y') as colhead
1695 1696
from t2
join t1 on t2.courseid=t1.courseid
unknown's avatar
unknown committed
1697 1698
join
(
1699 1700 1701 1702 1703 1704 1705 1706 1707
  select
    t5.userid,
    parentid,
    parentgroup,
    childid,
    groupname,
    grouptypeid
  from t5
  join
unknown's avatar
unknown committed
1708
  (
1709 1710 1711 1712 1713 1714 1715 1716 1717
     select t4.id as parentid,
       t4.name as parentgroup,
       t4.id as childid,
       t4.name as groupname,
       t4.grouptypeid
     from t4
  ) as gin on t5.groupid=gin.childid
) as groupstuff on t2.userid = groupstuff.userid
group by
unknown's avatar
unknown committed
1718 1719
  groupstuff.groupname, colhead , t2.courseid;

1720
drop table t1, t2, t3, t4, t5;
unknown's avatar
unknown committed
1721

1722

1723
#
1724
# Transformation in left expression of subquery (Bug#8888)
1725 1726 1727 1728 1729
#
create table t1 (a int);
insert into t1 values (1), (2), (3);
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
drop table t1;
1730

unknown's avatar
unknown committed
1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754
#
# subselect into HAVING clause (code covarage improvement)
#
create table t1 (a int);
create table t2 (a int);
insert into t1 values (1),(2);
insert into t2 values (0),(1),(2),(3);
select a from t2 where a in (select a from t1);
select a from t2 having a in (select a from t1);
prepare stmt1 from "select a from t2 where a in (select a from t1)";
execute stmt1;
execute stmt1;
deallocate prepare stmt1;
prepare stmt1 from "select a from t2 having a in (select a from t1)";
execute stmt1;
execute stmt1;
deallocate prepare stmt1;
drop table t1, t2;

#
# single row subqueries and row operations (code covarage improvement)
#
create table t1 (a int, b int);
insert into t1 values (1,2);
Matthias Leich's avatar
Matthias Leich committed
1755
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1756
select 1 = (select * from t1);
Matthias Leich's avatar
Matthias Leich committed
1757
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1758
select (select * from t1) = 1;
Matthias Leich's avatar
Matthias Leich committed
1759
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1760
select (1,2) = (select a from t1);
Matthias Leich's avatar
Matthias Leich committed
1761
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1762
select (select a from t1) = (1,2);
Matthias Leich's avatar
Matthias Leich committed
1763
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1764
select (1,2,3) = (select * from t1);
Matthias Leich's avatar
Matthias Leich committed
1765
-- error ER_OPERAND_COLUMNS
unknown's avatar
unknown committed
1766
select (select * from t1) = (1,2,3);
unknown's avatar
unknown committed
1767
drop table t1;
1768

1769

1770
#
1771
# Item_int_with_ref check (Bug#10020)
1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792
#
CREATE TABLE `t1` (
  `itemid` bigint(20) unsigned NOT NULL auto_increment,
  `sessionid` bigint(20) unsigned default NULL,
  `time` int(10) unsigned NOT NULL default '0',
  `type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
NULL default '',
  `data` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`itemid`)
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
CREATE TABLE `t2` (
  `sessionid` bigint(20) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL default '0',
  `date` int(10) unsigned NOT NULL default '0',
  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (`sessionid`)
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;
drop tables t1,t2;
1793

1794 1795

# Bug#11821 Select from subselect using aggregate function on an enum segfaults
1796 1797 1798
create table t1 (fld enum('0','1'));
insert into t1 values ('1');
select * from (select max(fld) from t1) as foo;
unknown's avatar
unknown committed
1799
drop table t1;
unknown's avatar
unknown committed
1800

1801

unknown's avatar
unknown committed
1802
#
1803
# Bug#11867 queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
unknown's avatar
unknown committed
1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815
#

CREATE TABLE t1 (one int, two int, flag char(1));
CREATE TABLE t2 (one int, two int, flag char(1));
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');

SELECT * FROM t1
  WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
SELECT * FROM t1
  WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');

unknown's avatar
unknown committed
1816
insert into t2 values (null,null,'N');
1817 1818 1819 1820 1821 1822
insert into t2 values (null,3,'0');
insert into t2 values (null,5,'0');
insert into t2 values (10,null,'0');
insert into t1 values (10,3,'0');
insert into t1 values (10,5,'0');
insert into t1 values (10,10,'0');
unknown's avatar
unknown committed
1823
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
1824
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
unknown's avatar
unknown committed
1825
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
1826 1827 1828 1829 1830
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
unknown's avatar
unknown committed
1831
DROP TABLE t1,t2;
1832

1833

unknown's avatar
unknown committed
1834
#
1835
# Bug#12392 where cond with IN predicate for rows and NULL values in table
unknown's avatar
unknown committed
1836 1837 1838 1839 1840 1841 1842 1843 1844
#

CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');

SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));

DROP TABLE t1;

1845

unknown's avatar
unknown committed
1846
#
1847
# Bug#11479 subquery over left join with an empty inner table
unknown's avatar
unknown committed
1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861
#

CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int, b int);
CREATE TABLE t3 (b int NOT NULL);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t2 VALUES (1,10), (3,30);

SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
  WHERE t3.b IS NOT NULL OR t2.a > 10;
SELECT * FROM t1
  WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
                       WHERE t3.b IS NOT NULL OR t2.a > 10);

unknown's avatar
unknown committed
1862 1863
DROP TABLE t1,t2,t3;

1864

1865
#
1866 1867
# Bug#18503 Queries with a quantified subquery returning empty set may
#           return a wrong result.
1868 1869 1870 1871 1872 1873 1874 1875 1876 1877
#
CREATE TABLE t1 (f1 INT);
CREATE TABLE t2 (f2 INT);
INSERT INTO t1 VALUES (1);
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
DROP TABLE t1, t2;
1878

1879

1880
#
1881
# Bug#16302 Quantified subquery without any tables gives wrong results
1882 1883 1884 1885 1886 1887 1888
#
select 1 from dual where 1 < any (select 2);
select 1 from dual where 1 < all (select 2);
select 1 from dual where 2 > any (select 1);
select 1 from dual where 2 > all (select 1);
select 1 from dual where 1 < any (select 2 from dual);
select 1 from dual where 1 < all (select 2 from dual where 1!=1);
1889

1890 1891

# Bug#20975 Wrong query results for subqueries within NOT
1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905
create table t1 (s1 char);
insert into t1 values (1),(2);

select * from t1 where (s1 < any (select s1 from t1));
select * from t1 where not (s1 < any (select s1 from t1));

select * from t1 where (s1 < ALL (select s1+1 from t1));
select * from t1 where not(s1 < ALL (select s1+1 from t1));

select * from t1 where (s1+1 = ANY (select s1 from t1));
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));

select * from t1 where (s1 = ALL (select s1/s1 from t1));
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
unknown's avatar
unknown committed
1906
drop table t1;
1907

1908

1909
#
1910
# Bug#16255 Subquery in where
1911
#
1912 1913 1914 1915 1916 1917
create table t1 (
  retailerID varchar(8) NOT NULL,
  statusID   int(10) unsigned NOT NULL,
  changed    datetime NOT NULL,
  UNIQUE KEY retailerID (retailerID, statusID, changed)
);
1918

1919 1920 1921 1922 1923 1924 1925
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");

1926 1927 1928
select * from t1 r1
  where (r1.retailerID,(r1.changed)) in
         (SELECT r2.retailerId,(max(changed)) from t1 r2
1929 1930
          group by r2.retailerId);
drop table t1;
1931

1932

1933
#
1934 1935
# Bug#21180 Subselect with index for both WHERE and ORDER BY
#           produces empty result
1936 1937 1938 1939 1940 1941 1942
#
create table t1(a int, primary key (a));
insert into t1 values (10);

create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');

1943 1944
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1945
             ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
1946 1947
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1948 1949
            ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

1950 1951
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1952
            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
1953 1954
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
1955 1956 1957
            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

drop table t1,t2;
1958

1959

1960
#
1961 1962
# Bug#21853 assert failure for a grouping query with
#           an ALL/ANY quantified subquery in HAVING
1963 1964
#

1965 1966 1967 1968 1969 1970 1971 1972 1973 1974
CREATE TABLE t1 (
  field1 int NOT NULL,
  field2 int NOT NULL,
  field3 int NOT NULL,
  PRIMARY KEY  (field1,field2,field3)
);
CREATE TABLE t2 (
  fieldA int NOT NULL,
  fieldB int NOT NULL,
  PRIMARY KEY  (fieldA,fieldB)
1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986
);

INSERT INTO t1 VALUES
  (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);

SELECT field1, field2, COUNT(*)
  FROM t1 GROUP BY field1, field2;

SELECT field1, field2
  FROM  t1
    GROUP BY field1, field2
1987
      HAVING COUNT(*) >= ALL (SELECT fieldB
1988 1989 1990 1991
                                FROM t2 WHERE fieldA = field1);
SELECT field1, field2
  FROM  t1
    GROUP BY field1, field2
1992
      HAVING COUNT(*) < ANY (SELECT fieldB
1993 1994 1995 1996
                               FROM t2 WHERE fieldA = field1);

DROP TABLE t1, t2;

1997

unknown's avatar
unknown committed
1998
#
1999 2000
# Bug#23478 not top-level IN subquery returning a non-empty result set
#           with possible NULL values by index access from the outer query
unknown's avatar
unknown committed
2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014
#

CREATE TABLE t1(a int, INDEX (a));
INSERT INTO t1 VALUES (1), (3), (5), (7);
INSERT INTO t1 VALUES (NULL);

CREATE TABLE t2(a int);
INSERT INTO t2 VALUES (1),(2),(3);

EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
SELECT a, a IN (SELECT a FROM t1) FROM t2;

DROP TABLE t1,t2;

2015

2016
#
2017
# Bug#11302 getObject() returns a String for a sub-query of type datetime
2018 2019 2020 2021
#
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');

2022 2023
CREATE TABLE t2 AS SELECT
  (SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
2024 2025 2026
   FROM t1 WHERE a > '2000-01-01';
SHOW CREATE TABLE t2;

2027
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
2028 2029 2030 2031
SHOW CREATE TABLE t3;

DROP TABLE t1,t2,t3;

2032

2033
#
Matthias Leich's avatar
Matthias Leich committed
2034
# Bug#24670 subquery witout tables but with a WHERE clause
2035 2036 2037 2038 2039 2040 2041 2042 2043 2044
#

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1), (2);

SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;

DROP TABLE t1;
unknown's avatar
unknown committed
2045

2046

unknown's avatar
unknown committed
2047
#
2048 2049
# Bug#24653 sorting by expressions containing subselects
#           that return more than one row
unknown's avatar
unknown committed
2050 2051 2052 2053 2054 2055 2056 2057 2058 2059
#

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (2), (4), (1), (3);

CREATE TABLE t2 (b int, c int);
INSERT INTO t2 VALUES
  (2,1), (1,3), (2,1), (4,4), (2,2), (1,4);

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
Matthias Leich's avatar
Matthias Leich committed
2060
--error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
2061 2062
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
Matthias Leich's avatar
Matthias Leich committed
2063
--error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
2064 2065 2066
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
Matthias Leich's avatar
Matthias Leich committed
2067
--error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
2068 2069 2070 2071 2072 2073
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);


SELECT a FROM t1 GROUP BY a
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
Matthias Leich's avatar
Matthias Leich committed
2074
--error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
2075 2076 2077 2078 2079 2080 2081
SELECT a FROM t1 GROUP BY a
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

SELECT a FROM t1 GROUP BY a
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
Matthias Leich's avatar
Matthias Leich committed
2082
--error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
2083 2084 2085 2086 2087 2088 2089
SELECT a FROM t1 GROUP BY a
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
                (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;

SELECT a FROM t1
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
                  (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
Matthias Leich's avatar
Matthias Leich committed
2090
--error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
2091 2092 2093 2094 2095 2096 2097
SELECT a FROM t1
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
                  (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

SELECT a FROM t1
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
                  (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
Matthias Leich's avatar
Matthias Leich committed
2098
--error ER_SUBQUERY_NO_1_ROW
unknown's avatar
unknown committed
2099 2100 2101 2102 2103 2104
SELECT a FROM t1
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
                  (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

DROP TABLE t1,t2;

2105
# End of 4.1 tests
unknown's avatar
unknown committed
2106

unknown's avatar
unknown committed
2107
#
2108
#decimal-related tests
unknown's avatar
unknown committed
2109
#
2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130
create table t1 (df decimal(5,1));
insert into t1 values(1.1);
insert into t1 values(2.2);

select * from t1 where df <= all (select avg(df) from t1 group by df);
select * from t1 where df >= all (select avg(df) from t1 group by df);
drop table t1;

create table t1 (df decimal(5,1));
insert into t1 values(1.1);
select 1.1 * exists(select * from t1);
drop table t1;

CREATE TABLE t1 (
  grp int(11) default NULL,
  a decimal(10,2) default NULL);

insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
select * from t1;
select min(a) from t1 group by grp;
drop table t1;
unknown's avatar
unknown committed
2131

2132

unknown's avatar
unknown committed
2133
#
2134
# Test for Bug#9338 lame substitution of c1 instead of c2
unknown's avatar
unknown committed
2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152
#

CREATE table t1 ( c1 integer );
INSERT INTO t1 VALUES ( 1 );
INSERT INTO t1 VALUES ( 2 );
INSERT INTO t1 VALUES ( 3 );

CREATE TABLE t2 ( c2 integer );
INSERT INTO t2 VALUES ( 1 );
INSERT INTO t2 VALUES ( 4 );
INSERT INTO t2 VALUES ( 5 );

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
  WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );

DROP TABLE t1,t2;
unknown's avatar
unknown committed
2153

2154

unknown's avatar
unknown committed
2155
#
2156
# Test for Bug#9516 wrong evaluation of not_null_tables attribute in SQ
unknown's avatar
unknown committed
2157 2158 2159 2160 2161
#
CREATE TABLE t1 ( c1 integer );
INSERT INTO t1 VALUES ( 1 );
INSERT INTO t1 VALUES ( 2 );
INSERT INTO t1 VALUES ( 3 );
2162 2163
INSERT INTO t1 VALUES ( 6 );

unknown's avatar
unknown committed
2164 2165 2166 2167 2168 2169 2170 2171 2172 2173
CREATE TABLE t2 ( c2 integer );
INSERT INTO t2 VALUES ( 1 );
INSERT INTO t2 VALUES ( 4 );
INSERT INTO t2 VALUES ( 5 );
INSERT INTO t2 VALUES ( 6 );

CREATE TABLE t3 ( c3 integer );
INSERT INTO t3 VALUES ( 7 );
INSERT INTO t3 VALUES ( 8 );

2174
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
unknown's avatar
unknown committed
2175 2176 2177 2178
  WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );

DROP TABLE t1,t2,t3;

2179

2180
#
2181
# Item_int_with_ref check (Bug#10020)
2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202
#
CREATE TABLE `t1` (
  `itemid` bigint(20) unsigned NOT NULL auto_increment,
  `sessionid` bigint(20) unsigned default NULL,
  `time` int(10) unsigned NOT NULL default '0',
  `type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
NULL default '',
  `data` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`itemid`)
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
CREATE TABLE `t2` (
  `sessionid` bigint(20) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL default '0',
  `date` int(10) unsigned NOT NULL default '0',
  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (`sessionid`)
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;
drop tables t1,t2;
2203

2204

2205 2206
#
# Correct building of equal fields list (do not include outer
2207
# fields) (Bug#6384)
2208 2209 2210
#
CREATE TABLE t1 (EMPNUM   CHAR(3));
CREATE TABLE t2 (EMPNUM   CHAR(3) );
unknown's avatar
unknown committed
2211
INSERT INTO t1 VALUES ('E1'),('E2');
2212 2213 2214 2215 2216 2217
INSERT INTO t2 VALUES ('E1');
DELETE FROM t1
WHERE t1.EMPNUM NOT IN
      (SELECT t2.EMPNUM
       FROM t2
       WHERE t1.EMPNUM = t2.EMPNUM);
unknown's avatar
unknown committed
2218
select * from t1;
2219
DROP TABLE t1,t2;
unknown's avatar
unknown committed
2220

2221

unknown's avatar
unknown committed
2222
#
2223
# Test for Bug#11487 range access in a subquery
unknown's avatar
unknown committed
2224 2225 2226 2227
#

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
INSERT INTO t1 VALUES (1, 1);
2228
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
unknown's avatar
unknown committed
2229 2230 2231
                 PRIMARY KEY(select_id,values_id));
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);

2232
SELECT values_id FROM t1
unknown's avatar
unknown committed
2233 2234
WHERE values_id IN (SELECT values_id FROM t2
                    WHERE select_id IN (1, 0));
2235
SELECT values_id FROM t1
unknown's avatar
unknown committed
2236 2237
WHERE values_id IN (SELECT values_id FROM t2
                    WHERE select_id BETWEEN 0 AND 1);
2238
SELECT values_id FROM t1
unknown's avatar
unknown committed
2239 2240 2241 2242
WHERE values_id IN (SELECT values_id FROM t2
                    WHERE select_id = 0 OR select_id = 1);

DROP TABLE t1, t2;
unknown's avatar
unknown committed
2243

2244 2245

# Bug#11821 Select from subselect using aggregate function on an enum segfaults
2246 2247 2248 2249 2250
create table t1 (fld enum('0','1'));
insert into t1 values ('1');
select * from (select max(fld) from t1) as foo;
drop table t1;

2251

unknown's avatar
unknown committed
2252
#
2253
# Test for Bug#11762 subquery with an aggregate function in HAVING
unknown's avatar
unknown committed
2254 2255 2256 2257 2258 2259
#

CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (c int, d int);
CREATE TABLE t3 (e int);

2260
INSERT INTO t1 VALUES
unknown's avatar
unknown committed
2261 2262 2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289
  (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
INSERT INTO t2 VALUES
  (2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
INSERT INTO t3 VALUES (10), (30), (10), (20) ;

SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
SELECT * FROM t2;
SELECT * FROM t3;

SELECT a FROM t1 GROUP BY a
  HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
SELECT a FROM t1 GROUP BY a
  HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
SELECT a FROM t1 GROUP BY a
  HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
SELECT a FROM t1 GROUP BY a
  HAVING a IN (SELECT c FROM t2
                 WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
SELECT a FROM t1 GROUP BY a
  HAVING a IN (SELECT c FROM t2
                 WHERE  EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
SELECT a FROM t1 GROUP BY a
  HAVING a IN (SELECT c FROM t2
                 WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
SELECT a FROM t1 GROUP BY a
  HAVING a IN (SELECT c FROM t2
                 WHERE  EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
SELECT a FROM t1 GROUP BY a
  HAVING a IN (SELECT c FROM t2
2290
                 WHERE MIN(b) < d AND
unknown's avatar
unknown committed
2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314
                       EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

SELECT a, SUM(a) FROM t1 GROUP BY a;

SELECT a FROM t1
   WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
SELECT a FROM t1 GROUP BY a
   HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);

SELECT a FROM t1
   WHERE a < 3 AND
         EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
SELECT a FROM t1
   WHERE a < 3 AND
         EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);

SELECT t1.a FROM t1 GROUP BY t1.a
  HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
                       HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
                                       HAVING SUM(t1.a+t2.c) < t3.e/4));
SELECT t1.a FROM t1 GROUP BY t1.a
       HAVING t1.a > ALL(SELECT t2.c FROM t2
                           WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
                                          HAVING SUM(t1.a+t2.c) < t3.e/4));
Matthias Leich's avatar
Matthias Leich committed
2315
-- error ER_INVALID_GROUP_FUNC_USE
unknown's avatar
unknown committed
2316 2317
SELECT t1.a FROM t1 GROUP BY t1.a
       HAVING t1.a > ALL(SELECT t2.c FROM t2
2318
                           WHERE EXISTS(SELECT t3.e FROM t3
unknown's avatar
unknown committed
2319
                                          WHERE SUM(t1.a+t2.c) < t3.e/4));
2320
-- error ER_INVALID_GROUP_FUNC_USE
unknown's avatar
unknown committed
2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;

SELECT t1.a FROM t1 GROUP BY t1.a
  HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
                    HAVING AVG(t2.c+SUM(t1.b)) > 20);
SELECT t1.a FROM t1 GROUP BY t1.a
  HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
                    HAVING AVG(SUM(t1.b)) > 20);

SELECT t1.a, SUM(b) AS sum  FROM t1 GROUP BY t1.a
  HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
                    HAVING t2.c+sum > 20);

DROP TABLE t1,t2,t3;
unknown's avatar
unknown committed
2335

2336

unknown's avatar
unknown committed
2337
#
2338 2339
# Test for Bug#16603 GROUP BY in a row subquery with a quantifier
#                    when an index is defined on the grouping field
unknown's avatar
unknown committed
2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357

CREATE TABLE t1 (a varchar(5), b varchar(10));
INSERT INTO t1 VALUES
  ('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
  ('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

ALTER TABLE t1 ADD INDEX(a);

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

DROP TABLE t1;

2358

2359
#
2360
# Bug#17366 Unchecked Item_int results in server crash
2361 2362 2363 2364 2365
#
create table t1( f1 int,f2 int);
insert into t1 values (1,1),(2,2);
select tt.t from (select 'crash1' as t, f2 from t1) as tt left join t1 on tt.t = 'crash2' and tt.f2 = t1.f2 where tt.t = 'crash1';
drop table t1;
2366

2367

2368
#
2369
# Bug#18306 server crash on delete using subquery.
2370 2371
#

2372
create table t1 (c int, key(c));
2373 2374 2375 2376 2377
insert into t1 values (1142477582), (1142455969);
create table t2 (a int, b int);
insert into t2 values (2, 1), (1, 0);
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
drop table t1, t2;
2378

2379

2380
#
2381
# Bug#7549 Missing error message for invalid view selection with subquery
2382 2383 2384 2385
#

CREATE TABLE t1 (a INT);

Matthias Leich's avatar
Matthias Leich committed
2386
--error ER_BAD_FIELD_ERROR
2387
CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1);
Matthias Leich's avatar
Matthias Leich committed
2388
--error ER_BAD_FIELD_ERROR
2389
CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1);
Matthias Leich's avatar
Matthias Leich committed
2390
--error ER_BAD_FIELD_ERROR
2391 2392 2393
SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1);

DROP TABLE t1;
2394

2395

2396
#
2397
# Bug#19077 A nested materialized derived table is used before being populated.
2398 2399 2400 2401 2402 2403
#
create table t1 (i int, j bigint);
insert into t1 values (1, 2), (2, 2), (3, 2);
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
drop table t1;

2404 2405 2406

#
# Bug#19700 subselect returning BIGINT always returned it as SIGNED
2407 2408
#
CREATE TABLE t1 (i BIGINT UNSIGNED);
unknown's avatar
unknown committed
2409
INSERT INTO t1 VALUES (10000000000000000000); # > MAX SIGNED BIGINT 9323372036854775807
2410 2411 2412
INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (i BIGINT UNSIGNED);
unknown's avatar
unknown committed
2413
INSERT INTO t2 VALUES (10000000000000000000); # same as first table
2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426
INSERT INTO t2 VALUES (1);

/* simple test */
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;

/* subquery test */
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

/* subquery test with cast*/
SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);

DROP TABLE t1;
DROP TABLE t2;
unknown's avatar
unknown committed
2427

2428 2429 2430

#
# Bug#20519 subselect with LIMIT M, N
unknown's avatar
unknown committed
2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446
#

CREATE TABLE t1 (
  id bigint(20) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  PRIMARY KEY  (id)
);
INSERT INTO t1 VALUES
  (1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

CREATE TABLE t2 (
  id bigint(20) unsigned NOT NULL auto_increment,
  mid bigint(20) unsigned NOT NULL,
  date date NOT NULL,
  PRIMARY KEY  (id)
);
2447
INSERT INTO t2 VALUES
unknown's avatar
unknown committed
2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467
  (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
  (4, 2, '2006-04-20'), (5, 1, '2006-05-01');

SELECT *,
      (SELECT date FROM t2 WHERE mid = t1.id
         ORDER BY date DESC LIMIT 0, 1) AS date_last,
      (SELECT date FROM t2 WHERE mid = t1.id
         ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
  FROM t1;
SELECT *,
      (SELECT COUNT(*) FROM t2 WHERE mid = t1.id
         ORDER BY date DESC LIMIT 1, 1) AS date_count
  FROM t1;
SELECT *,
      (SELECT date FROM t2 WHERE mid = t1.id
         ORDER BY date DESC LIMIT 0, 1) AS date_last,
      (SELECT date FROM t2 WHERE mid = t1.id
         ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
  FROM t1;
DROP TABLE t1,t2;
2468

2469

2470
#
2471
# Bug#20869 subselect with range access by DESC
unknown's avatar
unknown committed
2472 2473 2474 2475 2476 2477 2478 2479
#

CREATE TABLE t1 (
  i1 int(11) NOT NULL default '0',
  i2 int(11) NOT NULL default '0',
  t datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (i1,i2,t)
);
2480
INSERT INTO t1 VALUES
unknown's avatar
unknown committed
2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
(24,2,'2005-05-27 12:40:06');

CREATE TABLE t2 (
  i1 int(11) NOT NULL default '0',
  i2 int(11) NOT NULL default '0',
  t datetime default NULL,
  PRIMARY KEY  (i1)
);
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

EXPLAIN
SELECT * FROM t1,t2
2498
  WHERE t1.t = (SELECT t1.t FROM t1
unknown's avatar
unknown committed
2499 2500 2501
                  WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
                    ORDER BY t1.t DESC LIMIT 1);
SELECT * FROM t1,t2
2502
  WHERE t1.t = (SELECT t1.t FROM t1
unknown's avatar
unknown committed
2503 2504 2505 2506
                  WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
                    ORDER BY t1.t DESC LIMIT 1);

DROP TABLE t1, t2;
2507

2508

2509
#
2510
# Bug#14654 Cannot select from the same table twice within a UNION statement
2511 2512 2513 2514
#
CREATE TABLE t1 (i INT);

(SELECT i FROM t1) UNION (SELECT i FROM t1);
2515 2516
#TODO:not supported
--error ER_PARSE_ERROR
2517
SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS
2518
  (
2519
   (SELECT i FROM t1) UNION
2520 2521 2522
   (SELECT i FROM t1)
  );

2523 2524
#TODO:not supported
--error ER_PARSE_ERROR
2525
SELECT * FROM t1
2526 2527 2528
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

#TODO:not supported
Matthias Leich's avatar
Matthias Leich committed
2529
--error ER_PARSE_ERROR
2530 2531
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
  from t1;
2532 2533 2534

#TODO:not supported
--error ER_PARSE_ERROR
2535
explain select * from t1 where not exists
2536 2537 2538
  ((select t11.i from t1 t11) union (select t12.i from t1 t12));

DROP TABLE t1;
2539

2540

2541
#
2542 2543
# Bug#21798 memory leak during query execution with subquery in column
#           list using a function
2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560
#
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
insert into t1 (a) values (FLOOR(rand() * 100));
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;
insert into t1 (a) select FLOOR(rand() * 100) from t1;

2561 2562 2563
SELECT a,
       (SELECT REPEAT(' ',250) FROM t1 i1
        WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a
2564 2565
FROM t1 ORDER BY a LIMIT 5;
DROP TABLE t1;
2566

2567

2568
#
2569
# Bug#21540 Subqueries with no from and aggregate functions return
Matthias Leich's avatar
Matthias Leich committed
2570
#           wrong results
2571 2572 2573 2574 2575 2576 2577 2578
CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 (a INT);
INSERT INTO t2 values (1);
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
  FROM t1 GROUP BY t1.a;
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
2579
SELECT COUNT(DISTINCT t1.b),
2580 2581 2582 2583 2584 2585
       (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
  FROM t1 GROUP BY t1.a;
SELECT (
    SELECT (
      SELECT COUNT(DISTINCT t1.b)
    )
2586
)
2587 2588 2589 2590 2591 2592
FROM t1 GROUP BY t1.a;
SELECT (
  SELECT (
      SELECT (
        SELECT COUNT(DISTINCT t1.b)
      )
2593 2594
  )
  FROM t1 GROUP BY t1.a LIMIT 1)
2595 2596
FROM t1 t2
GROUP BY t2.a;
2597 2598
DROP TABLE t1,t2;

unknown's avatar
unknown committed
2599 2600

#
2601 2602
# Bug#21727 Correlated subquery that requires filesort:
#           slow with big sort_buffer_size
unknown's avatar
unknown committed
2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619
#

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
CREATE TABLE t2 (x int auto_increment, y int, z int,
                 PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));

disable_query_log;
let $1=3000;
while ($1)
{
  eval INSERT INTO t1(a) VALUES(RAND()*1000);
  eval SELECT MAX(b) FROM t1 INTO @id;
  let $2=10;
  while ($2)
  {
    eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000);
    dec $2;
2620
  }
unknown's avatar
unknown committed
2621 2622 2623 2624 2625
  dec $1;
}
enable_query_log;

SET SESSION sort_buffer_size = 32 * 1024;
2626
SELECT SQL_NO_CACHE COUNT(*)
unknown's avatar
unknown committed
2627 2628 2629 2630
  FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
          FROM t1) t;

SET SESSION sort_buffer_size = 8 * 1024 * 1024;
2631
SELECT SQL_NO_CACHE COUNT(*)
unknown's avatar
unknown committed
2632 2633 2634 2635
  FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
          FROM t1) t;

DROP TABLE t1,t2;
2636

2637

unknown's avatar
unknown committed
2638
#
2639 2640
# Bug#25219 EXIST subquery with UNION over a mix of
#           correlated and uncorrelated selects
unknown's avatar
unknown committed
2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671
#

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
CREATE TABLE t2 (c int);

INSERT INTO t1 VALUES ('aa', 1);
INSERT INTO t2 VALUES (1);

SELECT * FROM t1
  WHERE EXISTS (SELECT c FROM t2 WHERE c=1
                UNION
                SELECT c from t2 WHERE c=t1.c);

INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);

SELECT * FROM t1
  WHERE EXISTS (SELECT c FROM t2 WHERE c=1
                UNION
                SELECT c from t2 WHERE c=t1.c);

INSERT INTO t2 VALUES (2);
CREATE TABLE t3 (c int);
INSERT INTO t3 VALUES (1);

SELECT * FROM t1
  WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
                UNION
                SELECT c from t2 WHERE c=t1.c);

DROP TABLE t1,t2,t3;

2672 2673 2674 2675 2676

#
# Bug#23800 Outer fields in correlated subqueries is used in a temporary
#           table created for sorting.
#
2677 2678 2679 2680 2681 2682 2683 2684 2685 2686
CREATE TABLE t1(f1 int);
CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
INSERT INTO t1 VALUES (1),(1),(2),(2);
INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11");
SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1;
SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2;
PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1';
EXECUTE stmt1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
2687
SELECT f2, AVG(f21),
2688 2689
      (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test
  FROM t2 GROUP BY f2;
2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700
DROP TABLE t1,t2;
CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL);
INSERT INTO t1 VALUES
  (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
  (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),
  (3,2,'k'), (3,1,'l'), (1,9,'m');
SELECT a, MAX(b),
      (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test
  FROM t1 GROUP BY a;
DROP TABLE t1;

2701

2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 2775 2776 2777 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800
#
# Bug#21904 (parser problem when using IN with a double "(())")
#

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1xt2;
--enable_warnings

CREATE TABLE t1 (
  id_1 int(5) NOT NULL,
  t varchar(4) DEFAULT NULL
);

CREATE TABLE t2 (
  id_2 int(5) NOT NULL,
  t varchar(4) DEFAULT NULL
);

CREATE TABLE t1xt2 (
  id_1 int(5) NOT NULL,
  id_2 int(5) NOT NULL
);

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

# subselect returns 0 rows

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));

insert INTO t1xt2 VALUES (1, 12);

# subselect returns 1 row

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

insert INTO t1xt2 VALUES (2, 12);

# subselect returns more than 1 row

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

SELECT DISTINCT t1.id_1 FROM t1 WHERE
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t1xt2;

2801 2802 2803

#
# Bug#26728 derived table with concatanation of literals in select list
2804 2805 2806
#

CREATE TABLE t1 (a int);
2807
INSERT INTO t1 VALUES (3), (1), (2);
2808 2809 2810 2811 2812 2813

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;

DROP table t1;

2814 2815 2816

#
# Bug#27257 COUNT(*) aggregated in outer query
2817 2818 2819 2820 2821 2822
#

CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (m int, n int);
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2823

2824 2825 2826 2827 2828 2829 2830 2831
SELECT COUNT(*), a,
       (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
  FROM t1 GROUP BY a;

SELECT COUNT(*), a,
       (SELECT MIN(m) FROM t2 WHERE m = count(*))
  FROM t1 GROUP BY a;

2832
SELECT COUNT(*), a
2833 2834 2835 2836
  FROM t1 GROUP BY a
    HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;

DROP TABLE t1,t2;
2837

2838 2839 2840

#
# Bug#27229 GROUP_CONCAT in subselect with COUNT() as an argument
2841 2842 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856
#

CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (m int, n int);
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

SELECT COUNT(*) c, a,
       (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
  FROM t1 GROUP BY a;

SELECT COUNT(*) c, a,
       (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
  FROM t1 GROUP BY a;

DROP table t1,t2;
unknown's avatar
unknown committed
2857

2858

2859
#
2860
# Bug#27321 Wrong subquery result in a grouping select
2861 2862 2863 2864 2865 2866 2867
#
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');

SELECT a, MAX(b),
2868
  (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
2869 2870 2871 2872 2873 2874 2875 2876 2877 2878
  FROM t1 GROUP BY a;
SELECT a x, MAX(b),
  (SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
  FROM t1 GROUP BY a;
SELECT a, AVG(b),
  (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
  FROM t1 WHERE t1.d=0 GROUP BY a;

SELECT tt.a,
 (SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2879
  LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2880 2881 2882 2883 2884
  FROM t1 as tt;

SELECT tt.a,
 (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
  LIMIT 1)
2885
  FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2886 2887 2888 2889 2890
  FROM t1 as tt GROUP BY tt.a;

SELECT tt.a, MAX(
 (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
  LIMIT 1)
2891
  FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
2892 2893
  FROM t1 as tt GROUP BY tt.a;

unknown's avatar
unknown committed
2894
DROP TABLE t1;
2895 2896 2897 2898


#
# Bug#27348 SET FUNCTION used in a subquery from WHERE condition
unknown's avatar
unknown committed
2899 2900 2901 2902 2903 2904 2905 2906 2907 2908 2909 2910
#

CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (2,22),(1,11),(2,22);

SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;

SELECT a FROM t1 t0
  WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;

SET @@sql_mode='ansi';
Matthias Leich's avatar
Matthias Leich committed
2911
--error ER_INVALID_GROUP_FUNC_USE
unknown's avatar
unknown committed
2912
SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
Matthias Leich's avatar
Matthias Leich committed
2913
--error ER_INVALID_GROUP_FUNC_USE
unknown's avatar
unknown committed
2914 2915
SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;

Matthias Leich's avatar
Matthias Leich committed
2916
--error ER_INVALID_GROUP_FUNC_USE
unknown's avatar
unknown committed
2917 2918 2919 2920 2921 2922
SELECT a FROM t1 t0
  WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;

SET @@sql_mode=default;

DROP TABLE t1;
unknown's avatar
unknown committed
2923

2924

unknown's avatar
unknown committed
2925
#
2926
# Bug#27363 nested aggregates in outer, subquery / sum(select count(outer))
unknown's avatar
unknown committed
2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940
#
CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);

--error ER_INVALID_GROUP_FUNC_USE
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
--error ER_INVALID_GROUP_FUNC_USE
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
SELECT COUNT(1) FROM DUAL;

--error ER_INVALID_GROUP_FUNC_USE
SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;

--error ER_INVALID_GROUP_FUNC_USE
2941
SELECT
unknown's avatar
unknown committed
2942 2943 2944 2945
  SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
FROM t1;

--error ER_INVALID_GROUP_FUNC_USE
2946
SELECT t1.a as XXA,
unknown's avatar
unknown committed
2947 2948 2949 2950 2951
   SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
FROM t1;

DROP TABLE t1,t2;

2952

unknown's avatar
unknown committed
2953
#
2954
# Bug#27807 Server crash when executing subquery with EXPLAIN
unknown's avatar
unknown committed
2955
#
2956
CREATE TABLE t1 (a int, b int, KEY (a));
unknown's avatar
unknown committed
2957 2958 2959 2960
INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
DROP TABLE t1;

2961 2962 2963

#
# Bug#28377 grouping query with a correlated subquery in WHERE condition
2964 2965 2966 2967 2968 2969 2970 2971
#

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
  (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
INSERT INTO t2 VALUES (7), (5), (1), (3);

2972
SELECT id, st FROM t1
2973
  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
2974
SELECT id, st FROM t1
2975 2976 2977
  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
    GROUP BY id;

2978
SELECT id, st FROM t1
2979
  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
2980
SELECT id, st FROM t1
2981 2982 2983 2984 2985
  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
    GROUP BY id;

DROP TABLE t1,t2;

2986 2987 2988 2989

#
# Bug#28728 crash with EXPLAIN EXTENDED for a query with a derived table
#           over a grouping subselect
2990 2991 2992 2993 2994 2995 2996 2997 2998 2999 3000
#

CREATE TABLE t1 (a int);

INSERT INTO t1 VALUES (1), (2);

EXPLAIN EXTENDED
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;

DROP TABLE t1;

3001

3002
#
3003
# Bug#28811 crash for query containing subquery with ORDER BY and LIMIT 1
3004
#
3005

3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 3030 3031 3032 3033
CREATE TABLE t1 (
  a varchar(255) default NULL,
  b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  INDEX idx(a,b)
);
CREATE TABLE t2 (
  a varchar(255) default NULL
);

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
INSERT INTO `t2` VALUES ('abcdefghijk');
INSERT INTO `t2` VALUES ('asdf');

SET session sort_buffer_size=8192;

SELECT (SELECT 1 FROM  t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;

DROP TABLE t1,t2;

3034 3035

#
3036 3037
# Bug#27333 subquery grouped for aggregate of outer query / no aggregate
#           of subquery
3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058
#
CREATE TABLE t1 (a INTEGER, b INTEGER);
CREATE TABLE t2 (x INTEGER);
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
INSERT INTO t2 VALUES (1), (2);

# wasn't failing, but should
--error ER_SUBQUERY_NO_1_ROW
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

# fails as it should
--error ER_SUBQUERY_NO_1_ROW
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;

SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
DROP TABLE t1,t2;

# second test case from 27333
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);

3059
# returns no rows, when it should
3060 3061 3062 3063 3064 3065 3066 3067 3068 3069 3070 3071 3072 3073 3074 3075
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
GROUP BY a1.a;
DROP TABLE t1;

#test cases from 29297
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
INSERT INTO t1 VALUES (1),(2);
INSERT INTO t2 VALUES (1),(2);
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
--error ER_SUBQUERY_NO_1_ROW
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
DROP TABLE t1,t2;

3076

3077
#
3078
# Bug31048 Many nested subqueries may cause server crash.
3079 3080 3081 3082
#
create table t1(a int,b int,key(a),key(b));
insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5),
  (6,7),(7,4),(5,3);
3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 3093 3094 3095 3096 3097 3098 3099 3100 3101 3102 3103 3104 3105 3106 3107 3108

let $nesting= 26;
let $should_work_nesting= 5;
let $start= select sum(a),a from t1 where a> ( select sum(a) from t1 ;
let $end= )group by a ;
let $start_app= where a> ( select sum(a) from t1 ;
let $end_pre= )group by b limit 1 ;

--disable_result_log
--disable_query_log
# At least 4 level nesting should work without errors
while ($should_work_nesting)
{
--echo $should_work_nesting
  eval $start $end;
  eval explain $start $end;
  let $start= $start
  $start_app;
  let $end= $end_pre
  $end;
  dec $should_work_nesting;
}
# Other may fail with the 'stack overrun error'
while ($nesting)
{
--echo $nesting
Matthias Leich's avatar
Matthias Leich committed
3109
--error 0,ER_STACK_OVERRUN_NEED_MORE
3110
  eval $start $end;
Matthias Leich's avatar
Matthias Leich committed
3111
--error 0,ER_STACK_OVERRUN_NEED_MORE
3112 3113 3114 3115 3116 3117 3118 3119 3120
  eval explain $start $end;
  let $start= $start
  $start_app;
  let $end= $end_pre
  $end;
  dec $nesting;
}
--enable_result_log
--enable_query_log
3121
drop table t1;
3122

3123

3124
#
3125
# Bug#31884 Assertion + crash in subquery in the SELECT clause.
3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138
#

CREATE TABLE t1 (a1 INT, a2 INT);
CREATE TABLE t2 (b1 INT, b2 INT);

INSERT INTO t1 VALUES (100, 200);
INSERT INTO t1 VALUES (101, 201);
INSERT INTO t2 VALUES (101, 201);
INSERT INTO t2 VALUES (103, 203);

SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
DROP TABLE t1, t2;

3139
#
3140
# Bug#28076 inconsistent binary/varbinary comparison
3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179 3180
#

CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

CREATE INDEX I1 ON t1 (s1);
CREATE INDEX I2 ON t1 (s2);

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

TRUNCATE t1;
INSERT INTO t1 VALUES (0x41,0x41);
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);

DROP TABLE t1;

CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
CREATE TABLE t3 (a3 BINARY(2) default '0');
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1),(2),(3);
INSERT INTO t3 VALUES (1),(2),(3);
SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
DROP TABLE t1,t2,t3;

CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
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), (20), (30);
SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
DROP TABLE t1, t2, t3;

3181

3182
#
3183
# Bug#30788 Inconsistent retrieval of char/varchar
3184 3185 3186 3187 3188 3189 3190 3191 3192 3193 3194 3195 3196 3197 3198 3199 3200 3201 3202 3203 3204 3205 3206
#

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
INSERT INTO t1 VALUES ('a', 'aa');
INSERT INTO t1 VALUES ('a', 'aaa');
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
CREATE INDEX I1 ON t1 (a);
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
INSERT INTO t2 SELECT * FROM t1;
CREATE INDEX I1 ON t2 (a);
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
EXPLAIN
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

DROP TABLE t1,t2;

3207

3208
#
3209
# Bug#32400 Complex SELECT query returns correct result only on some occasions
3210 3211 3212 3213 3214 3215
#

CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);

--error ER_BAD_FIELD_ERROR
3216
EXPLAIN
3217 3218 3219 3220 3221 3222 3223 3224 3225
SELECT a AS out_a, MIN(b) FROM t1
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
GROUP BY a;

--error ER_BAD_FIELD_ERROR
SELECT a AS out_a, MIN(b) FROM t1
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
GROUP BY a;

3226
EXPLAIN
3227 3228 3229 3230 3231 3232 3233 3234 3235 3236
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
GROUP BY a;

SELECT a AS out_a, MIN(b) FROM t1 t1_outer
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
GROUP BY a;

DROP TABLE t1;

3237

3238
#
3239
# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
3240 3241 3242 3243 3244 3245 3246 3247 3248 3249 3250 3251 3252 3253 3254 3255
#

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);

INSERT INTO t1 VALUES (1),(2);
INSERT INTO t2 VALUES (1),(2);

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));


#TODO:not supported
--error ER_PARSE_ERROR
EXPLAIN EXTENDED
3256
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
3257 3258 3259 3260
                               (SELECT 1 FROM t2 WHERE t1.a = t2.a));

DROP TABLE t1,t2;

3261

3262
#
3263 3264
# Bug#33675 Usage of an uninitialized memory by filesort in a subquery
#           caused server crash.
3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279
#
create table t1(f11 int, f12 int);
create table t2(f21 int unsigned not null, f22 int, f23 varchar(10));
insert into t1 values(1,1),(2,2), (3, 3);
let $i=10000;
--disable_query_log
--disable_warnings
while ($i)
{
  eval insert into t2 values (-1 , $i/5000 + 1, '$i');
  dec $i;
}
--enable_warnings
--enable_query_log
set session sort_buffer_size= 33*1024;
3280
select count(*) from t1 where f12 =
3281 3282 3283
(select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1);

drop table t1,t2;
3284

3285

3286
#
3287
# Bug#33794 "MySQL crashes executing specific query on specific dump"
3288 3289 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 3308 3309 3310 3311 3312 3313 3314 3315 3316 3317 3318 3319 3320 3321 3322 3323 3324 3325 3326 3327 3328
#
CREATE TABLE t4 (
  f7 varchar(32) collate utf8_bin NOT NULL default '',
  f10 varchar(32) collate utf8_bin default NULL,
  PRIMARY KEY  (f7)
);
INSERT INTO t4 VALUES(1,1), (2,null);

CREATE TABLE t2 (
  f4 varchar(32) collate utf8_bin NOT NULL default '',
  f2 varchar(50) collate utf8_bin default NULL,
  f3 varchar(10) collate utf8_bin default NULL,
  PRIMARY KEY  (f4),
  UNIQUE KEY uk1 (f2)
);
INSERT INTO t2 VALUES(1,1,null), (2,2,null);

CREATE TABLE t1 (
  f8 varchar(32) collate utf8_bin NOT NULL default '',
  f1 varchar(10) collate utf8_bin default NULL,
  f9 varchar(32) collate utf8_bin default NULL,
  PRIMARY KEY  (f8)
);
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);

CREATE TABLE t3 (
  f6 varchar(32) collate utf8_bin NOT NULL default '',
  f5 varchar(50) collate utf8_bin default NULL,
  PRIMARY KEY (f6)
);
INSERT INTO t3 VALUES (1,null), (2,null);

SELECT
  IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
  IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
  SUM(
    IF(
      (SELECT VPC.f2
       FROM t2 VPC, t4 a2, t2 a3
       WHERE
         VPC.f4 = a2.f10 AND a3.f2 = a4
3329 3330
       LIMIT 1) IS NULL,
       0,
3331 3332 3333
       t3.f5
    )
  ) AS a6
3334
FROM
3335 3336 3337
  t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
GROUP BY a4;

3338 3339
DROP TABLE t1, t2, t3, t4;

3340

3341
#
3342
# Bug#36139 "float, zerofill, crash with subquery"
3343 3344 3345 3346
#
create table t1 (a float(5,4) zerofill);
create table t2 (a float(5,4),b float(2,0));

3347
select t1.a from t1 where
3348 3349 3350
  t1.a= (select b from t2 limit 1) and not
  t1.a= (select a from t2 limit 1) ;

unknown's avatar
unknown committed
3351
drop table t1, t2;
3352

3353

3354
#
3355 3356
# Bug#36011 Server crash with explain extended on query with dependent
#           subqueries
3357 3358 3359 3360 3361 3362 3363 3364
#

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
DROP TABLE t1;

3365

Igor Babaev's avatar
Igor Babaev committed
3366
#
3367
# Bug#38191 Server crash with subquery containing DISTINCT and ORDER BY
Igor Babaev's avatar
Igor Babaev committed
3368 3369 3370 3371 3372 3373 3374 3375 3376 3377
#

CREATE TABLE t1(pk int PRIMARY KEY, a int, INDEX idx(a));
INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20);
CREATE TABLE t2(pk int PRIMARY KEY, a int, b int, INDEX idxa(a));
INSERT INTO t2 VALUES (2, 20, 700), (1, 10, 200), (4, 10, 100);
SELECT * FROM t1
   WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
DROP TABLE t1,t2;

3378 3379 3380 3381 3382 3383 3384
#
# Bug#20835 (literal string with =any values)
#
CREATE TABLE t1 (s1 char(1));
INSERT INTO t1 VALUES ('a');
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
DROP TABLE t1;
3385

3386 3387 3388 3389 3390 3391 3392 3393 3394 3395
#
# Bug#40519 Subselect query using bigint fails
#
CREATE TABLE t1(id BIGINT);
CREATE TABLE t2(id1 BIGINT, id2 BIGINT);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2,1),(3,1);
SELECT * FROM t1 i WHERE 1 IN (SELECT l.id2 FROM t2 l WHERE i.id=l.id1);
DROP TABLE t1, t2;

3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 3409 3410 3411 3412 3413 3414 3415 3416
#
# Bug#37460 Assertion failed:
# !table->file || table->file->inited == handler::NONE
#
CREATE TABLE t1 (id int);
CREATE TABLE t2 (id int, c int);

INSERT INTO t1 (id) VALUES (1);
INSERT INTO t2 (id) VALUES (1);
INSERT INTO t1 (id) VALUES (1);
INSERT INTO t2 (id) VALUES (1);

CREATE VIEW v1 AS
  SELECT t2.c AS c FROM t1, t2
 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
UPDATE v1 SET c=1;

CREATE VIEW v2 (a,b) AS
  SELECT t2.id, t2.c AS c FROM t1, t2
 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;

3417
--error ER_VIEW_CHECK_FAILED
3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430
INSERT INTO v2(a,b) VALUES (2,2);
INSERT INTO v2(a,b) VALUES (1,2);
SELECT * FROM v1;

CREATE VIEW v3 AS
  SELECT t2.c AS c FROM t2
 WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION;

DELETE FROM v3;

DROP VIEW v1,v2,v3;
DROP TABLE t1,t2;

3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443
--echo #
--echo # Bug#45061: Incorrectly market field caused wrong result.
--echo #
CREATE TABLE `C` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  KEY `int_key` (`int_key`)
);

INSERT INTO `C` VALUES (9,9), (0,0), (8,6), (3,6), (7,6), (0,4),
(1,7), (9,4), (0,8), (9,4), (0,7), (5,5), (0,0), (8,5), (8,7),
(5,2), (1,8), (7,0), (0,9), (9,5);

3444
--disable_warnings
3445 3446
SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
3447
--enable_warnings
3448 3449 3450 3451 3452

DROP TABLE C;
--echo # End of test for bug#45061.


3453 3454 3455 3456 3457 3458 3459 3460 3461 3462 3463 3464 3465 3466 3467 3468 3469 3470 3471 3472 3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483
--echo #
--echo # Bug #46749: Segfault in add_key_fields() with outer subquery level 
--echo #   field references
--echo #

CREATE TABLE t1 (
  a int,
  b int,
  UNIQUE (a), KEY (b)
);
INSERT INTO t1 VALUES (1,1), (2,1);

CREATE TABLE st1 like t1;
INSERT INTO st1 VALUES (1,1), (2,1);

CREATE TABLE st2 like t1;
INSERT INTO st2 VALUES (1,1), (2,1);

# should have "impossible where"
EXPLAIN
SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
FROM t1 
WHERE a = 230;

# should not crash
SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
FROM t1 
WHERE a = 230;

DROP TABLE t1, st1, st2;

3484
--echo End of 5.0 tests.