~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/t/subselect.test

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

Lines of Context:
 
1
# Initialise
 
2
--disable_warnings
 
3
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
 
4
--enable_warnings
 
5
select (select 2);
 
6
explain extended select (select 2);
 
7
SELECT (SELECT 1) UNION SELECT (SELECT 2);
 
8
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
 
9
SELECT (SELECT (SELECT 0 UNION SELECT 0));
 
10
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
 
11
-- error 1247
 
12
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
 
13
-- error 1247
 
14
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;
 
15
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
 
16
-- error 1247
 
17
SELECT (SELECT a) as a;
 
18
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
 
19
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 
20
-- error 1054
 
21
SELECT (SELECT 1), a;
 
22
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
 
23
-- error 1054
 
24
SELECT 1 FROM (SELECT (SELECT a) b) c;
 
25
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
 
26
-- error 1241
 
27
SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
 
28
SELECT 1 IN (SELECT 1);
 
29
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 
30
-- error ER_BAD_FIELD_ERROR
 
31
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
 
32
-- error ER_BAD_FIELD_ERROR
 
33
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
 
34
SELECT (SELECT 1,2,3) = ROW(1,2,3);
 
35
SELECT (SELECT 1,2,3) = ROW(1,2,1);
 
36
SELECT (SELECT 1,2,3) < ROW(1,2,1);
 
37
SELECT (SELECT 1,2,3) > ROW(1,2,1);
 
38
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
 
39
SELECT ROW(1,2,3) = (SELECT 1,2,3);
 
40
SELECT ROW(1,2,3) = (SELECT 1,2,1);
 
41
SELECT ROW(1,2,3) < (SELECT 1,2,1);
 
42
SELECT ROW(1,2,3) > (SELECT 1,2,1);
 
43
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
 
44
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
 
45
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
 
46
SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
 
47
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
 
48
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
 
49
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 
50
 
 
51
-- error 1241
 
52
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
 
53
 
 
54
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
 
55
 
 
56
create table t1 (a int);
 
57
create table t2 (a int, b int);
 
58
create table t3 (a int);
 
59
create table t4 (a int not null, b int not null);
 
60
insert into t1 values (2);
 
61
insert into t2 values (1,7),(2,7);
 
62
insert into t4 values (4,8),(3,8),(5,9);
 
63
-- error 1247
 
64
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
 
65
select (select a from t1 where t1.a=t2.a), a from t2;
 
66
select (select a from t1 where t1.a=t2.b), a from t2;
 
67
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
 
68
select (select a from t3), a from t2;
 
69
select * from t2 where t2.a=(select a from t1);
 
70
insert into t3 values (6),(7),(3);
 
71
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
 
72
(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;
 
73
(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);
 
74
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);
 
75
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
 
76
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
 
77
(select * from t2 where a>1) as tt;
 
78
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
 
79
(select * from t2 where a>1) as tt;
 
80
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);
 
81
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);
 
82
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);
 
83
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
 
84
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
 
85
select * from t3 where exists (select * from t2 where t2.b=t3.a);
 
86
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
 
87
select * from t3 where a in (select b from t2);
 
88
select * from t3 where a not in (select b from t2);
 
89
select * from t3 where a = some (select b from t2);
 
90
select * from t3 where a <> any (select b from t2);
 
91
 
 
92
# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);
 
93
select * from t3 where a = all (select b from t2);
 
94
 
 
95
select * from t3 where a <> all (select b from t2);
 
96
insert into t2 values (100, 5);
 
97
select * from t3 where a < any (select b from t2);
 
98
select * from t3 where a < all (select b from t2);
 
99
select * from t3 where a >= any (select b from t2);
 
100
explain extended select * from t3 where a >= any (select b from t2);
 
101
select * from t3 where a >= all (select b from t2);
 
102
delete from t2 where a=100;
 
103
-- error 1241
 
104
select * from t3 where a in (select a,b from t2);
 
105
-- error 1241
 
106
select * from t3 where a in (select * from t2);
 
107
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
 
108
# empty set
 
109
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
 
110
insert into t2 values (2,10);
 
111
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
 
112
delete from t2 where a=2 and b=10;
 
113
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
 
114
create table t5 (a int);
 
115
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
 
116
insert into t5 values (5);
 
117
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
 
118
insert into t5 values (2);
 
119
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
 
120
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;
 
121
-- error 1242
 
122
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
 
123
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
 
124
create table t7( uq int primary key, name char(25));
 
125
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
 
126
insert into t6 values (1,1),(1,2),(2,2),(1,3);
 
127
select * from t6 where exists (select * from t7 where uq = clinic_uq);
 
128
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
 
129
 
 
130
# not unique fields
 
131
-- error 1052
 
132
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
 
133
 
 
134
# different tipes & group functions
 
135
drop table t1,t2,t3;
 
136
 
 
137
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
 
138
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
 
139
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
 
140
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
 
141
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
 
142
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
 
143
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
 
144
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
 
145
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
 
146
 
 
147
CREATE TABLE `t8` (
 
148
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
 
149
  `email` varchar(60) character set latin1 NOT NULL default '',
 
150
  PRIMARY KEY  (`pseudo`),
 
151
  UNIQUE KEY `email` (`email`)
 
152
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
153
 
 
154
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
 
155
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
 
156
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
 
157
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');
 
158
-- error 1241
 
159
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
 
160
t8 WHERE pseudo='joce');
 
161
-- error 1241
 
162
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
 
163
pseudo='joce');
 
164
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 
165
-- error 1242
 
166
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
 
167
 
 
168
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
 
169
 
 
170
#searchconthardwarefr3 forumconthardwarefr7
 
171
CREATE TABLE `t1` (
 
172
  `topic` mediumint(8) unsigned NOT NULL default '0',
 
173
  `date` date NOT NULL default '0000-00-00',
 
174
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
 
175
  PRIMARY KEY  (`pseudo`,`date`,`topic`),
 
176
  KEY `topic` (`topic`)
 
177
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
178
INSERT INTO t1 (topic,date,pseudo) VALUES
 
179
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
 
180
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
 
181
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
 
182
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
 
183
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
 
184
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
 
185
-- error 1242
 
186
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
 
187
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
 
188
drop table t1;
 
189
 
 
190
#forumconthardwarefr7 searchconthardwarefr7
 
191
CREATE TABLE `t1` (
 
192
  `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
 
193
  `maxnumrep` int(10) unsigned NOT NULL default '0',
 
194
  PRIMARY KEY  (`numeropost`),
 
195
  UNIQUE KEY `maxnumrep` (`maxnumrep`)
 
196
) ENGINE=MyISAM ROW_FORMAT=FIXED;
 
197
 
 
198
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
 
199
 
 
200
CREATE TABLE `t2` (
 
201
      `mot` varchar(30) NOT NULL default '',
 
202
      `topic` mediumint(8) unsigned NOT NULL default '0',
 
203
      `date` date NOT NULL default '0000-00-00',
 
204
      `pseudo` varchar(35) NOT NULL default '',
 
205
       PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`)
 
206
    ) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
207
 
 
208
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
 
209
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
 
210
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;
 
211
-- error 1054
 
212
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
 
213
-- error 1054
 
214
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
 
215
 
 
216
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
 
217
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
 
218
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
 
219
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
 
220
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
 
221
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
 
222
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
 
223
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
 
224
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
 
225
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
 
226
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
 
227
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
 
228
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
 
229
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
 
230
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
 
231
drop table t1,t2;
 
232
 
 
233
#forumconthardwarefr7
 
234
CREATE TABLE `t1` (
 
235
  `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
 
236
  `maxnumrep` int(10) unsigned NOT NULL default '0',
 
237
  PRIMARY KEY  (`numeropost`),
 
238
  UNIQUE KEY `maxnumrep` (`maxnumrep`)
 
239
) ENGINE=MyISAM ROW_FORMAT=FIXED;
 
240
 
 
241
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
 
242
-- error 1242
 
243
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
 
244
-- error 1242
 
245
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
 
246
drop table t1;
 
247
 
 
248
create table t1 (a int);
 
249
insert into t1 values (1),(2),(3);
 
250
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
 
251
drop table t1;
 
252
 
 
253
#iftest
 
254
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
 
255
INSERT INTO t1 VALUES ();
 
256
-- error 1242
 
257
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
 
258
drop table t1;
 
259
 
 
260
# threadhardwarefr7
 
261
CREATE TABLE `t1` (
 
262
  `numeropost` mediumint(8) unsigned NOT NULL default '0',
 
263
  `numreponse` int(10) unsigned NOT NULL auto_increment,
 
264
  `pseudo` varchar(35) NOT NULL default '',
 
265
  PRIMARY KEY  (`numeropost`,`numreponse`),
 
266
  UNIQUE KEY `numreponse` (`numreponse`),
 
267
  KEY `pseudo` (`pseudo`,`numeropost`)
 
268
) ENGINE=MyISAM;
 
269
-- error 1247
 
270
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
 
271
-- error 1054
 
272
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
 
273
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
 
274
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
 
275
-- error 1242
 
276
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
 
277
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
 
278
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 
279
drop table t1;
 
280
 
 
281
CREATE TABLE t1 (a int(1));
 
282
INSERT INTO t1 VALUES (1);
 
283
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
 
284
drop table t1;
 
285
 
 
286
#update with subselects
 
287
create table t1 (a int NOT NULL, b int, primary key (a));
 
288
create table t2 (a int NOT NULL, b int, primary key (a));
 
289
insert into t1 values (0, 10),(1, 11),(2, 12);
 
290
insert into t2 values (1, 21),(2, 22),(3, 23);
 
291
select * from t1;
 
292
-- error 1093
 
293
update t1 set b= (select b from t1);
 
294
-- error 1242
 
295
update t1 set b= (select b from t2);
 
296
update t1 set b= (select b from t2 where t1.a = t2.a);
 
297
select * from t1;
 
298
drop table t1, t2;
 
299
 
 
300
#delete with subselects
 
301
create table t1 (a int NOT NULL, b int, primary key (a));
 
302
create table t2 (a int NOT NULL, b int, primary key (a));
 
303
insert into t1 values (0, 10),(1, 11),(2, 12);
 
304
insert into t2 values (1, 21),(2, 12),(3, 23);
 
305
select * from t1;
 
306
select * from t1 where b = (select b from t2 where t1.a = t2.a);
 
307
-- error 1093
 
308
delete from t1 where b = (select b from t1);
 
309
-- error 1242
 
310
delete from t1 where b = (select b from t2);
 
311
delete from t1 where b = (select b from t2 where t1.a = t2.a);
 
312
select * from t1;
 
313
drop table t1, t2;
 
314
 
 
315
#multi-delete with subselects
 
316
 
 
317
create table t11 (a int NOT NULL, b int, primary key (a));
 
318
create table t12 (a int NOT NULL, b int, primary key (a));
 
319
create table t2 (a int NOT NULL, b int, primary key (a));
 
320
insert into t11 values (0, 10),(1, 11),(2, 12);
 
321
insert into t12 values (33, 10),(22, 11),(2, 12);
 
322
insert into t2 values (1, 21),(2, 12),(3, 23);
 
323
select * from t11;
 
324
select * from t12;
 
325
-- error 1093
 
326
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 
327
-- error 1242
 
328
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 
329
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
 
330
select * from t11;
 
331
select * from t12;
 
332
drop table t11, t12, t2;
 
333
 
 
334
#insert with subselects
 
335
CREATE TABLE t1 (x int);
 
336
create table t2 (a int);
 
337
create table t3 (b int);
 
338
insert into t2 values (1);
 
339
insert into t3 values (1),(2);
 
340
-- error 1093
 
341
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
 
342
-- error 1242
 
343
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
 
344
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
 
345
select * from t1;
 
346
insert into t2 values (1);
 
347
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
 
348
-- sleep 1
 
349
select * from t1;
 
350
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
 
351
select * from t1;
 
352
# After this, only data based on old t1 records should have been added.
 
353
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
 
354
select * from t1;
 
355
-- error 1054
 
356
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
 
357
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
 
358
-- sleep 1
 
359
select * from t1;
 
360
#
 
361
#TODO: should be uncommented after bug 380 fix pushed
 
362
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
 
363
#select * from t1;
 
364
drop table t1, t2, t3;
 
365
 
 
366
#replace with subselects
 
367
CREATE TABLE t1 (x int not null, y int, primary key (x));
 
368
create table t2 (a int);
 
369
create table t3 (a int);
 
370
insert into t2 values (1);
 
371
insert into t3 values (1),(2);
 
372
select * from t1;
 
373
-- error 1093
 
374
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
 
375
-- error 1242
 
376
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
 
377
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
 
378
select * from t1;
 
379
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
 
380
select * from t1;
 
381
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
 
382
-- sleep 1
 
383
select * from t1;
 
384
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
 
385
-- sleep 1
 
386
select * from t1;
 
387
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
 
388
select * from t1;
 
389
drop table t1, t2, t3;
 
390
 
 
391
-- error 1096
 
392
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
 
393
 
 
394
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
 
395
INSERT INTO t2 VALUES (1),(2);
 
396
SELECT * FROM t2 WHERE id IN (SELECT 1);
 
397
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
 
398
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 
399
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
 
400
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
 
401
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 
402
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
 
403
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
 
404
-- error 1093
 
405
INSERT INTO t2 VALUES ((SELECT * FROM t2));
 
406
-- error 1093
 
407
INSERT INTO t2 VALUES ((SELECT id FROM t2));
 
408
SELECT * FROM t2;
 
409
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
 
410
INSERT INTO t1 values (1),(1);
 
411
-- error 1242
 
412
UPDATE t2 SET id=(SELECT * FROM t1);
 
413
drop table t2, t1;
 
414
 
 
415
#NULL test
 
416
create table t1 (a int);
 
417
insert into t1 values (1),(2),(3);
 
418
select 1 IN (SELECT * from t1);
 
419
select 10 IN (SELECT * from t1);
 
420
select NULL IN (SELECT * from t1);
 
421
update t1 set a=NULL where a=2;
 
422
select 1 IN (SELECT * from t1);
 
423
select 3 IN (SELECT * from t1);
 
424
select 10 IN (SELECT * from t1);
 
425
select 1 > ALL (SELECT * from t1);
 
426
select 10 > ALL (SELECT * from t1);
 
427
select 1 > ANY (SELECT * from t1);
 
428
select 10 > ANY (SELECT * from t1);
 
429
drop table t1;
 
430
create table t1 (a varchar(20));
 
431
insert into t1 values ('A'),('BC'),('DEF');
 
432
select 'A' IN (SELECT * from t1);
 
433
select 'XYZS' IN (SELECT * from t1);
 
434
select NULL IN (SELECT * from t1);
 
435
update t1 set a=NULL where a='BC';
 
436
select 'A' IN (SELECT * from t1);
 
437
select 'DEF' IN (SELECT * from t1);
 
438
select 'XYZS' IN (SELECT * from t1);
 
439
select 'A' > ALL (SELECT * from t1);
 
440
select 'XYZS' > ALL (SELECT * from t1);
 
441
select 'A' > ANY (SELECT * from t1);
 
442
select 'XYZS' > ANY (SELECT * from t1);
 
443
drop table t1;
 
444
create table t1 (a float);
 
445
insert into t1 values (1.5),(2.5),(3.5);
 
446
select 1.5 IN (SELECT * from t1);
 
447
select 10.5 IN (SELECT * from t1);
 
448
select NULL IN (SELECT * from t1);
 
449
update t1 set a=NULL where a=2.5;
 
450
select 1.5 IN (SELECT * from t1);
 
451
select 3.5 IN (SELECT * from t1);
 
452
select 10.5 IN (SELECT * from t1);
 
453
select 1.5 > ALL (SELECT * from t1);
 
454
select 10.5 > ALL (SELECT * from t1);
 
455
select 1.5 > ANY (SELECT * from t1);
 
456
select 10.5 > ANY (SELECT * from t1);
 
457
explain extended select (select a+1) from t1;
 
458
select (select a+1) from t1;
 
459
drop table t1;
 
460
 
 
461
#
 
462
# Null with keys
 
463
#
 
464
 
 
465
CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 
466
CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 
467
INSERT INTO t1 VALUES (1),(2),(3),(4);
 
468
INSERT INTO t2 VALUES (1),(2),(3);
 
469
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
 
470
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
 
471
CREATE TABLE t3 (a int(11) default '0');
 
472
INSERT INTO t3 VALUES (1),(2),(3);
 
473
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
 
474
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
 
475
drop table t1,t2,t3;
 
476
 
 
477
#LIMIT is not supported now
 
478
create table t1 (a float);
 
479
-- error 1235
 
480
select 10.5 IN (SELECT * from t1 LIMIT 1);
 
481
-- error 1235
 
482
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
 
483
drop table t1;
 
484
 
 
485
create table t1 (a int, b int, c varchar(10));
 
486
create table t2 (a int);
 
487
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
 
488
insert into t2 values (1),(2),(NULL);
 
489
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;
 
490
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;
 
491
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;
 
492
drop table t1,t2;
 
493
 
 
494
create table t1 (a int, b real, c varchar(10));
 
495
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
 
496
select ROW(1, 1, 'a') IN (select a,b,c from t1);
 
497
select ROW(1, 2, 'a') IN (select a,b,c from t1);
 
498
select ROW(1, 1, 'a') IN (select b,a,c from t1);
 
499
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
 
500
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
 
501
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
 
502
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
 
503
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
 
504
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
 
505
-- error 1235
 
506
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
 
507
drop table t1;
 
508
 
 
509
#test of uncacheable subqueries
 
510
CREATE TABLE t1 (a int(1));
 
511
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
 
512
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
 
513
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
 
514
drop table t1;
 
515
 
 
516
 
 
517
CREATE TABLE `t1` (
 
518
  `mot` varchar(30) character set latin1 NOT NULL default '',
 
519
  `topic` mediumint(8) unsigned NOT NULL default '0',
 
520
  `date` date NOT NULL default '0000-00-00',
 
521
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
 
522
  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
 
523
  KEY `pseudo` (`pseudo`,`date`,`topic`),
 
524
  KEY `topic` (`topic`)
 
525
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
526
 
 
527
CREATE TABLE `t2` (
 
528
  `mot` varchar(30) character set latin1 NOT NULL default '',
 
529
  `topic` mediumint(8) unsigned NOT NULL default '0',
 
530
  `date` date NOT NULL default '0000-00-00',
 
531
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
 
532
  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
 
533
  KEY `pseudo` (`pseudo`,`date`,`topic`),
 
534
  KEY `topic` (`topic`)
 
535
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
536
 
 
537
CREATE TABLE `t3` (
 
538
  `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
 
539
  `maxnumrep` int(10) unsigned NOT NULL default '0',
 
540
  PRIMARY KEY  (`numeropost`),
 
541
  UNIQUE KEY `maxnumrep` (`maxnumrep`)
 
542
) ENGINE=MyISAM CHARSET=latin1;
 
543
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
 
544
 
 
545
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
 
546
 
 
547
INSERT INTO t3 VALUES (1,1);
 
548
 
 
549
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
 
550
numeropost=topic);
 
551
select * from t1;
 
552
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
 
553
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
 
554
select * from t1;
 
555
 
 
556
drop table t1, t2, t3;
 
557
 
 
558
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
 
559
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
 
560
SHOW CREATE TABLE t1;
 
561
drop table t1;
 
562
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
 
563
SHOW CREATE TABLE t1;
 
564
drop table t1;
 
565
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
 
566
SHOW CREATE TABLE t1;
 
567
drop table t1;
 
568
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
 
569
select * from t1;
 
570
SHOW CREATE TABLE t1;
 
571
drop table t1;
 
572
 
 
573
create table t1 (a int);
 
574
insert into t1 values (1), (2), (3);
 
575
explain extended select a,(select (select rand() from t1 limit 1)  from t1 limit 1)
 
576
from t1;
 
577
drop table t1;
 
578
 
 
579
#
 
580
# error in IN
 
581
#
 
582
-- error 1146
 
583
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);
 
584
 
 
585
#
 
586
# complex subquery
 
587
#
 
588
 
 
589
CREATE TABLE t1 (
 
590
  ID int(11) NOT NULL auto_increment,
 
591
  name char(35) NOT NULL default '',
 
592
  t2 char(3) NOT NULL default '',
 
593
  District char(20) NOT NULL default '',
 
594
  Population int(11) NOT NULL default '0',
 
595
  PRIMARY KEY  (ID)
 
596
) ENGINE=MyISAM;
 
597
 
 
598
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
 
599
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
 
600
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
 
601
 
 
602
CREATE TABLE t2 (
 
603
  Code char(3) NOT NULL default '',
 
604
  Name char(52) NOT NULL default '',
 
605
  Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
 
606
  Region char(26) NOT NULL default '',
 
607
  SurfaceArea float(10,2) NOT NULL default '0.00',
 
608
  IndepYear smallint(6) default NULL,
 
609
  Population int(11) NOT NULL default '0',
 
610
  LifeExpectancy float(3,1) default NULL,
 
611
  GNP float(10,2) default NULL,
 
612
  GNPOld float(10,2) default NULL,
 
613
  LocalName char(45) NOT NULL default '',
 
614
  GovernmentForm char(45) NOT NULL default '',
 
615
  HeadOfState char(60) default NULL,
 
616
  Capital int(11) default NULL,
 
617
  Code2 char(2) NOT NULL default '',
 
618
  PRIMARY KEY  (Code)
 
619
) ENGINE=MyISAM;
 
620
 
 
621
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');
 
622
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');
 
623
 
 
624
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); 
 
625
 
 
626
drop table t1, t2;
 
627
 
 
628
#
 
629
# constants in IN
 
630
#
 
631
CREATE TABLE `t1` (
 
632
  `id` mediumint(8) unsigned NOT NULL auto_increment,
 
633
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
 
634
  PRIMARY KEY  (`id`),
 
635
  UNIQUE KEY `pseudo` (`pseudo`)
 
636
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
 
637
INSERT INTO t1 (pseudo) VALUES ('test');
 
638
SELECT 0 IN (SELECT 1 FROM t1 a);
 
639
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
 
640
INSERT INTO t1 (pseudo) VALUES ('test1');
 
641
SELECT 0 IN (SELECT 1 FROM t1 a);
 
642
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
 
643
drop table t1;
 
644
 
 
645
CREATE TABLE `t1` (
 
646
  `i` int(11) NOT NULL default '0',
 
647
  PRIMARY KEY  (`i`)
 
648
) ENGINE=MyISAM CHARSET=latin1;
 
649
 
 
650
INSERT INTO t1 VALUES (1);
 
651
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
 
652
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
 
653
-- error 1054
 
654
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
 
655
select * from t1;
 
656
drop table t1;
 
657
 
 
658
#
 
659
# Multi update test
 
660
#
 
661
CREATE TABLE t1 (
 
662
  id int(11) default NULL
 
663
) ENGINE=MyISAM CHARSET=latin1;
 
664
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
 
665
CREATE TABLE t2 (
 
666
  id int(11) default NULL,
 
667
  name varchar(15) default NULL
 
668
) ENGINE=MyISAM CHARSET=latin1;
 
669
 
 
670
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
 
671
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
 
672
select * from t2;
 
673
drop table t1,t2;
 
674
 
 
675
#
 
676
# correct NULL in <CONSTANT> IN (SELECT ...)
 
677
#
 
678
create table t1 (a int, unique index indexa (a)); 
 
679
insert into t1 values (-1), (-4), (-2), (NULL); 
 
680
select -10 IN (select a from t1 FORCE INDEX (indexa)); 
 
681
drop table t1;
 
682
 
 
683
#
 
684
# Test optimization for sub selects
 
685
#
 
686
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
 
687
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 
688
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 
689
drop table t1;
 
690
 
 
691
CREATE TABLE t1 (
 
692
  ID int(10) unsigned NOT NULL auto_increment,
 
693
  SUB_ID int(3) unsigned NOT NULL default '0',
 
694
  REF_ID int(10) unsigned default NULL,
 
695
  REF_SUB int(3) unsigned default '0',
 
696
  PRIMARY KEY (ID,SUB_ID),
 
697
  UNIQUE KEY t1_PK (ID,SUB_ID),
 
698
  KEY t1_FK (REF_ID,REF_SUB),
 
699
  KEY t1_REFID (REF_ID)
 
700
) ENGINE=MyISAM CHARSET=cp1251;
 
701
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
 
702
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
 
703
DROP TABLE t1;
 
704
 
 
705
#
 
706
# uninterruptable update
 
707
#
 
708
create table t1 (a int, b int);
 
709
create table t2 (a int, b int);
 
710
 
 
711
insert into t1 values (1,0), (2,0), (3,0);
 
712
insert into t2 values (1,1), (2,1), (3,1), (2,2);
 
713
 
 
714
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
 
715
select * from t1;
 
716
 
 
717
drop table t1, t2;
 
718
 
 
719
#
 
720
# reduced subselect in ORDER BY & GROUP BY clauses
 
721
#
 
722
 
 
723
CREATE TABLE `t1` (
 
724
  `id` mediumint(8) unsigned NOT NULL auto_increment,
 
725
  `pseudo` varchar(35) NOT NULL default '',
 
726
  `email` varchar(60) NOT NULL default '',
 
727
  PRIMARY KEY  (`id`),
 
728
  UNIQUE KEY `email` (`email`),
 
729
  UNIQUE KEY `pseudo` (`pseudo`)
 
730
) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
 
731
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
 
732
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
 
733
drop table if exists t1;
 
734
 
 
735
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
 
736
 
 
737
#
 
738
# IN subselect optimization test
 
739
#
 
740
create table t1 (a int not null, b int, primary key (a));
 
741
create table t2 (a int not null, primary key (a));
 
742
create table t3 (a int not null, b int, primary key (a));
 
743
insert into t1 values (1,10), (2,20), (3,30),  (4,40);
 
744
insert into t2 values (2), (3), (4), (5);
 
745
insert into t3 values (10,3), (20,4), (30,5);
 
746
select * from t2 where t2.a in (select a from t1);
 
747
explain extended select * from t2 where t2.a in (select a from t1);
 
748
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 
749
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 
750
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 
751
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 
752
drop table t1, t2, t3;
 
753
create table t1 (a int, b int, index a (a,b));
 
754
create table t2 (a int, index a (a));
 
755
create table t3 (a int, b int, index a (a));
 
756
insert into t1 values (1,10), (2,20), (3,30), (4,40);
 
757
disable_query_log;
 
758
# making table large enough
 
759
let $1 = 10000;
 
760
while ($1)
 
761
 {
 
762
  eval insert into t1 values (rand()*100000+200,rand()*100000); 
 
763
  dec $1;
 
764
 }
 
765
enable_query_log;
 
766
insert into t2 values (2), (3), (4), (5);
 
767
insert into t3 values (10,3), (20,4), (30,5);
 
768
select * from t2 where t2.a in (select a from t1);
 
769
explain extended select * from t2 where t2.a in (select a from t1);
 
770
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 
771
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 
772
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 
773
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 
774
insert into t1 values (3,31);
 
775
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 
776
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
 
777
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 
778
drop table t1, t2, t3;
 
779
 
 
780
#
 
781
# alloc_group_fields() working
 
782
#
 
783
create table t1 (a int, b int);
 
784
create table t2 (a int, b int);
 
785
create table t3 (a int, b int);
 
786
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
 
787
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
 
788
insert into t3 values (3,3), (2,2), (1,1); 
 
789
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;
 
790
drop table t1,t2,t3;
 
791
 
 
792
#
 
793
# aggregate functions in HAVING test
 
794
#
 
795
create table t1 (s1 int);
 
796
create table t2 (s1 int);
 
797
insert into t1 values (1);
 
798
insert into t2 values (1);
 
799
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
 
800
drop table t1,t2;
 
801
 
 
802
#
 
803
# update subquery with wrong field (to force name resolving
 
804
# in UPDATE name space)
 
805
#
 
806
create table t1 (s1 int);
 
807
create table t2 (s1 int);
 
808
insert into t1 values (1);
 
809
insert into t2 values (1);
 
810
-- error 1054
 
811
update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
 
812
DROP TABLE t1, t2;
 
813
 
 
814
#
 
815
# collation test
 
816
#
 
817
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
 
818
                 s2 CHAR(5) COLLATE latin1_swedish_ci);
 
819
INSERT INTO t1 VALUES ('z','?');
 
820
-- error 1267
 
821
select * from t1 where s1 > (select max(s2) from t1);
 
822
-- error 1267
 
823
select * from t1 where s1 > any (select max(s2) from t1);
 
824
drop table t1;
 
825
 
 
826
#
 
827
# aggregate functions reinitialization
 
828
#
 
829
create table t1(toid int,rd int);
 
830
create table t2(userid int,pmnew int,pmtotal int);
 
831
insert into t2 values(1,0,0),(2,0,0);
 
832
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);
 
833
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);
 
834
drop table t1, t2;
 
835
 
 
836
#
 
837
# row union
 
838
#
 
839
create table t1 (s1 char(5));
 
840
-- error 1241
 
841
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
 
842
insert into t1 values ('tttt');
 
843
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
 
844
explain extended (select * from t1);
 
845
(select * from t1);
 
846
drop table t1;
 
847
 
 
848
#
 
849
# IN optimisation test results
 
850
#
 
851
create table t1 (s1 char(5), index s1(s1));
 
852
create table t2 (s1 char(5), index s1(s1));
 
853
insert into t1 values ('a1'),('a2'),('a3');
 
854
insert into t2 values ('a1'),('a2');
 
855
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
 
856
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
 
857
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
 
858
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
 
859
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
 
860
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
 
861
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
 
862
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
 
863
drop table t1,t2;
 
864
 
 
865
#
 
866
# correct ALL optimisation
 
867
#
 
868
create table t2 (a int, b int);
 
869
create table t3 (a int);
 
870
insert into t3 values (6),(7),(3);
 
871
select * from t3 where a >= all (select b from t2);
 
872
explain extended select * from t3 where a >= all (select b from t2);
 
873
select * from t3 where a >= some (select b from t2);
 
874
explain extended select * from t3 where a >= some (select b from t2);
 
875
select * from t3 where a >= all (select b from t2 group by 1);
 
876
explain extended select * from t3 where a >= all (select b from t2 group by 1);
 
877
select * from t3 where a >= some (select b from t2 group by 1);
 
878
explain extended select * from t3 where a >= some (select b from t2 group by 1);
 
879
select * from t3 where NULL >= any (select b from t2);
 
880
explain extended select * from t3 where NULL >= any (select b from t2);
 
881
select * from t3 where NULL >= any (select b from t2 group by 1);
 
882
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
 
883
select * from t3 where NULL >= some (select b from t2);
 
884
explain extended select * from t3 where NULL >= some (select b from t2);
 
885
select * from t3 where NULL >= some (select b from t2 group by 1);
 
886
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
 
887
#
 
888
# optimized static ALL/ANY with grouping
 
889
#
 
890
insert into t2 values (2,2), (2,1), (3,3), (3,1);
 
891
select * from t3 where a > all (select max(b) from t2 group by a);
 
892
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
 
893
drop table t2, t3;
 
894
 
 
895
#
 
896
# correct used_tables()
 
897
#
 
898
 
 
899
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 ;
 
900
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());
 
901
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`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
 
902
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);
 
903
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 ;
 
904
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);
 
905
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 
906
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
 
907
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;
 
908
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;
 
909
drop table t1,t2,t3,t4;
 
910
 
 
911
#
 
912
# cardinality check
 
913
#
 
914
CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
 
915
INSERT INTO t1 VALUES (1),(5);
 
916
CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
 
917
INSERT INTO t2 VALUES (2),(6);
 
918
-- error 1241
 
919
select * from t1 where (1,2,6) in (select * from t2);
 
920
DROP TABLE t1,t2;
 
921
 
 
922
#
 
923
# optimized ALL/ANY with union
 
924
#
 
925
create table t1 (s1 char);
 
926
insert into t1 values ('e');
 
927
select * from t1 where 'f' > any (select s1 from t1);
 
928
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
 
929
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
 
930
drop table t1;
 
931
 
 
932
#
 
933
# filesort in subquery (restoring join_tab)
 
934
#
 
935
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 
936
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
 
937
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
 
938
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
 
939
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;
 
940
drop table t1, t2;
 
941
 
 
942
#
 
943
# unresolved field error
 
944
#
 
945
create table t1 (s1 int); 
 
946
create table t2 (s1 int);
 
947
-- error 1054
 
948
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
 
949
-- error 1054
 
950
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
 
951
-- error 1054
 
952
select count(*) from t2 group by t1.s2;
 
953
drop table t1, t2;
 
954
 
 
955
#
 
956
# fix_fields() in add_ref_to_table_cond()
 
957
#
 
958
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
 
959
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
 
960
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
 
961
INSERT INTO t2 VALUES (100, 200, 'C');
 
962
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
 
963
DROP TABLE t1, t2;
 
964
 
 
965
CREATE TABLE t1 (a int(1));
 
966
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
 
967
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
 
968
DROP TABLE t1;
 
969
 
 
970
#
 
971
# Bug 2479
 
972
#
 
973
 
 
974
CREATE TABLE `t1` (
 
975
  `id` int(11) NOT NULL auto_increment,
 
976
  `id_cns` tinyint(3) unsigned NOT NULL default '0',
 
977
  `tipo` enum('','UNO','DUE') NOT NULL default '',
 
978
  `anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
 
979
  `particolare` mediumint(8) unsigned NOT NULL default '0',
 
980
  `generale` mediumint(8) unsigned NOT NULL default '0',
 
981
  `bis` tinyint(3) unsigned NOT NULL default '0',
 
982
  PRIMARY KEY  (`id`),
 
983
  UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
 
984
  UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
 
985
);
 
986
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);
 
987
CREATE TABLE `t2` (
 
988
  `id` tinyint(3) unsigned NOT NULL auto_increment,
 
989
  `max_anno_dep` smallint(6) unsigned NOT NULL default '0',
 
990
  PRIMARY KEY  (`id`)
 
991
);
 
992
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
 
993
 
 
994
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;
 
995
 
 
996
DROP TABLE t1, t2;
 
997
 
 
998
#
 
999
# GLOBAL LIMIT
 
1000
#
 
1001
create table t1 (a int);
 
1002
insert into t1 values (1), (2), (3);
 
1003
SET SQL_SELECT_LIMIT=1;
 
1004
select sum(a) from (select * from t1) as a;
 
1005
select 2 in (select * from t1);
 
1006
SET SQL_SELECT_LIMIT=default;
 
1007
drop table t1;
 
1008
 
 
1009
#
 
1010
# Bug #3118: subselect + order by
 
1011
#
 
1012
 
 
1013
CREATE TABLE t1 (a int, b int, INDEX (a));
 
1014
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
 
1015
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
 
1016
DROP TABLE t1;
 
1017
 
 
1018
# Item_cond fix field
 
1019
#
 
1020
create table t1(val varchar(10));
 
1021
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
 
1022
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%');
 
1023
drop table t1;
 
1024
 
 
1025
#
 
1026
# ref_or_null replacing with ref
 
1027
#
 
1028
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
 
1029
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');
 
1030
select * from t1 where id not in (select id from t1 where id < 8);
 
1031
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);
 
1032
explain extended select * from t1 where id not in (select id from t1 where id < 8);
 
1033
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);
 
1034
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
 
1035
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
 
1036
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');
 
1037
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);
 
1038
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);
 
1039
drop table t1,t2;
 
1040
 
 
1041
#
 
1042
# Static tables & rund() in subqueries
 
1043
#
 
1044
create table t1 (a int);
 
1045
insert into t1 values (1);
 
1046
explain select benchmark(1000, (select a from t1 where a=rand()));
 
1047
drop table t1;
 
1048
 
 
1049
#
 
1050
# bug 3188
 
1051
#
 
1052
create table t1(id int);
 
1053
create table t2(id int);
 
1054
create table t3(flag int);
 
1055
-- error 1064
 
1056
select (select * from t3 where id not null) from t1, t2;
 
1057
drop table t1,t2,t3;
 
1058
 
 
1059
#
 
1060
# aggregate functions (Bug #3505)
 
1061
#
 
1062
CREATE TABLE t1 (id INT);
 
1063
CREATE TABLE t2 (id INT);
 
1064
INSERT INTO t1 VALUES (1), (2);
 
1065
INSERT INTO t2 VALUES (1);
 
1066
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);
 
1067
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
 
1068
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;
 
1069
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;
 
1070
DROP TABLE t1,t2;
 
1071
 
 
1072
#
 
1073
# ALL/ANY test
 
1074
#
 
1075
CREATE TABLE t1 ( a int, b int );
 
1076
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
 
1077
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1078
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1079
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1080
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1081
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1082
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1083
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1084
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1085
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1086
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1087
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1088
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1089
# with index
 
1090
ALTER TABLE t1 ADD INDEX (a);
 
1091
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1092
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1093
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1094
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1095
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1096
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
 
1097
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1098
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1099
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1100
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1101
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1102
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
 
1103
# having clause test
 
1104
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
 
1105
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
 
1106
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
 
1107
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
 
1108
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
 
1109
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
 
1110
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
 
1111
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
 
1112
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
 
1113
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
 
1114
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
 
1115
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
 
1116
# union test
 
1117
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1118
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1119
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1120
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1121
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1122
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1123
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1124
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1125
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1126
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1127
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1128
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
 
1129
# union + having test
 
1130
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1131
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1132
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1133
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1134
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1135
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1136
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1137
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1138
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1139
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1140
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1141
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
 
1142
# row tests
 
1143
# < > >= <= and = ALL/ <> ANY do not support row operation
 
1144
-- error 1241
 
1145
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
 
1146
-- error 1241
 
1147
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
 
1148
-- error 1241
 
1149
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
 
1150
-- error 1241
 
1151
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
 
1152
-- error 1241
 
1153
SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
 
1154
-- error 1241
 
1155
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
 
1156
-- error 1241
 
1157
SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
 
1158
-- error 1241
 
1159
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
 
1160
# following should be converted to IN
 
1161
-- error 1241
 
1162
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
 
1163
-- error 1241
 
1164
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
 
1165
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
 
1166
-- error 1241
 
1167
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
 
1168
-- error 1241
 
1169
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
 
1170
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
 
1171
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
 
1172
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
 
1173
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
 
1174
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
 
1175
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);
 
1176
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);
 
1177
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);
 
1178
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);
 
1179
# without optimisation
 
1180
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
 
1181
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
 
1182
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
 
1183
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
 
1184
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
 
1185
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
 
1186
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
 
1187
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
 
1188
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
 
1189
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
 
1190
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
 
1191
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
 
1192
# without optimisation + having
 
1193
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
 
1194
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
 
1195
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
 
1196
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
 
1197
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
 
1198
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
 
1199
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
 
1200
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
 
1201
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
 
1202
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
 
1203
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
 
1204
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
 
1205
# EXISTS in string contence
 
1206
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
 
1207
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
 
1208
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
 
1209
DROP TABLE t1;
 
1210
CREATE TABLE t1 ( a double, b double );
 
1211
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
 
1212
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
 
1213
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
 
1214
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
 
1215
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
 
1216
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
 
1217
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
 
1218
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
 
1219
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
 
1220
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
 
1221
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
 
1222
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
 
1223
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
 
1224
DROP TABLE t1;
 
1225
CREATE TABLE t1 ( a char(1), b char(1));
 
1226
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
 
1227
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
 
1228
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
 
1229
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
 
1230
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
 
1231
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
 
1232
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
 
1233
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
 
1234
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
 
1235
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
 
1236
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
 
1237
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
 
1238
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
 
1239
DROP TABLE t1;
 
1240
 
 
1241
 
 
1242
#
 
1243
# SELECT(EXISTS * ...)optimisation
 
1244
#
 
1245
create table t1 (a int, b int);
 
1246
insert into t1 values (1,2),(3,4);
 
1247
select * from t1 up where exists (select * from t1 where t1.a=up.a);
 
1248
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
 
1249
drop table t1;
 
1250
 
 
1251
#
 
1252
# Bug #4102: subselect in HAVING
 
1253
#
 
1254
 
 
1255
CREATE TABLE t1 (t1_a int);
 
1256
INSERT INTO t1 VALUES (1);
 
1257
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
 
1258
INSERT INTO t2 VALUES (1, 1), (1, 2);
 
1259
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
 
1260
  HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
 
1261
DROP TABLE t1, t2;
 
1262
 
 
1263
#
 
1264
# Test problem with NULL and derived tables (Bug #4097)
 
1265
#
 
1266
 
 
1267
CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL);
 
1268
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
 
1269
CREATE TABLE t2 (id int(11) default NULL, pet varchar(10) default NULL);
 
1270
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
 
1271
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
 
1272
drop table t1,t2;
 
1273
 
 
1274
#
 
1275
# Aggregate function comparation with ALL/ANY/SOME subselect
 
1276
#
 
1277
CREATE TABLE `t1` ( `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
1278
insert into t1 values (1);
 
1279
CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
1280
insert into t2 values (1,2);
 
1281
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
 
1282
drop table t1,t2;
 
1283
 
 
1284
#
 
1285
# BUG#5003 - like in subselect
 
1286
#
 
1287
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);
 
1288
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
 
1289
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
 
1290
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
 
1291
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
 
1292
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
 
1293
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
 
1294
drop table t1;
 
1295
 
 
1296
#
 
1297
# Optimized IN with compound index
 
1298
#
 
1299
CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
 
1300
CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
 
1301
insert into t1 values (1,1),(1,2),(2,1),(2,2);
 
1302
insert into t2 values (1,2),(2,2);
 
1303
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
 
1304
alter table t2 drop primary key;
 
1305
alter table t2 add key KEY1 (aid, bid);
 
1306
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
 
1307
alter table t2 drop key KEY1;
 
1308
alter table t2 add primary key (bid, aid);
 
1309
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
 
1310
drop table t1,t2;
 
1311
 
 
1312
#
 
1313
# resolving fields of grouped outer SELECT
 
1314
#
 
1315
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
 
1316
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
 
1317
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
 
1318
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
 
1319
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
 
1320
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
 
1321
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
 
1322
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
 
1323
drop table t1;
 
1324
 
 
1325
create table t1 (x int);
 
1326
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;
 
1327
drop table t1;
 
1328
 
 
1329
#
 
1330
# Test of correct maybe_null flag returning by subquwery for temporary table
 
1331
# creation
 
1332
#
 
1333
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`));
 
1334
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);
 
1335
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`)) ;
 
1336
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');
 
1337
-- error 1054
 
1338
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;
 
1339
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;
 
1340
drop tables t1,t2;
 
1341
 
 
1342
#
 
1343
# Subselect in non-select command just after connection
 
1344
#
 
1345
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
 
1346
connection root;
 
1347
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
 
1348
 
 
1349
#
 
1350
# primary query with temporary table and subquery with groupping
 
1351
#
 
1352
create table t1 (a int, b int);
 
1353
create table t2 (a int, b int);
 
1354
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
 
1355
insert into t2 values (1,3),(2,1);
 
1356
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
 
1357
drop table t1, t2;
 
1358
 
 
1359
#
 
1360
# Equal operation under row and empty subquery
 
1361
#
 
1362
create table t1 (s1 int,s2 int);
 
1363
insert into t1 values (20,15);
 
1364
select * from t1 where  (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
 
1365
drop table t1;
 
1366
 
 
1367
#
 
1368
# ALL/ANY with NULL
 
1369
#
 
1370
create table t1 (s1 int);
 
1371
insert into t1 values (1),(null);
 
1372
select * from t1 where s1 < all (select s1 from t1);
 
1373
select s1, s1 < all (select s1 from t1) from t1;
 
1374
drop table t1;
 
1375
 
 
1376
#
 
1377
# reference on changable fields from subquery
 
1378
#
 
1379
CREATE TABLE t1 (
 
1380
  Code char(3) NOT NULL default '',
 
1381
  Name char(52) NOT NULL default '',
 
1382
  Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
 
1383
  Region char(26) NOT NULL default '',
 
1384
  SurfaceArea float(10,2) NOT NULL default '0.00',
 
1385
  IndepYear smallint(6) default NULL,
 
1386
  Population int(11) NOT NULL default '0',
 
1387
  LifeExpectancy float(3,1) default NULL,
 
1388
  GNP float(10,2) default NULL,
 
1389
  GNPOld float(10,2) default NULL,
 
1390
  LocalName char(45) NOT NULL default '',
 
1391
  GovernmentForm char(45) NOT NULL default '',
 
1392
  HeadOfState char(60) default NULL,
 
1393
  Capital int(11) default NULL,
 
1394
  Code2 char(2) NOT NULL default ''
 
1395
) ENGINE=MyISAM;
 
1396
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
 
1397
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');
 
1398
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');
 
1399
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');
 
1400
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
 
1401
SELECT DISTINCT Continent AS c FROM t1 outr WHERE 
 
1402
  Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND 
 
1403
  Population < 200);
 
1404
drop table t1;
 
1405
 
 
1406
#
 
1407
# Test for BUG#7885: Server crash when 'any' subselect compared to
 
1408
# non-existant field.
 
1409
#
 
1410
create table t1 (a1 int);
 
1411
create table t2 (b1 int);
 
1412
--error 1054
 
1413
select * from t1 where a2 > any(select b1 from t2);
 
1414
select * from t1 where a1 > any(select b1 from t2);
 
1415
drop table t1,t2;
 
1416
 
 
1417
 
 
1418
#
 
1419
# Comparison subquery with * and row
 
1420
#
 
1421
create table t1 (a integer, b integer);
 
1422
select (select * from t1) = (select 1,2);
 
1423
select (select 1,2) = (select * from t1);
 
1424
# queries whih can be converted to IN
 
1425
select  row(1,2) = ANY (select * from t1);
 
1426
select  row(1,2) != ALL (select * from t1);
 
1427
drop table t1;
 
1428
 
 
1429
#
 
1430
# Comparison subquery and row with nested rows
 
1431
#
 
1432
create table t1 (a integer, b integer);
 
1433
-- error 1241
 
1434
select row(1,(2,2)) in (select * from t1 );
 
1435
-- error 1241
 
1436
select row(1,(2,2)) = (select * from t1 );
 
1437
-- error 1241
 
1438
select (select * from t1) = row(1,(2,2));
 
1439
drop table t1;
 
1440
 
 
1441
#
 
1442
# Forward reference detection
 
1443
#
 
1444
create  table t1 (a integer);
 
1445
insert into t1 values (1);
 
1446
-- error 1247
 
1447
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
 
1448
-- error 1247
 
1449
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
 
1450
select 1 as xx, 1 = ALL (  select 1 from t1 where 1 = xx );
 
1451
-- error 1247
 
1452
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
 
1453
drop table t1;
 
1454
 
 
1455
#
 
1456
# Test for BUG#8218
 
1457
#
 
1458
CREATE TABLE t1 (
 
1459
  categoryId int(11) NOT NULL,
 
1460
  courseId int(11) NOT NULL,
 
1461
  startDate datetime NOT NULL,
 
1462
  endDate datetime NOT NULL,
 
1463
  createDate datetime NOT NULL,
 
1464
  modifyDate timestamp NOT NULL,
 
1465
  attributes text NOT NULL
 
1466
);
 
1467
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
 
1468
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
 
1469
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
 
1470
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
 
1471
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
 
1472
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
 
1473
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
 
1474
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
 
1475
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
 
1476
 
 
1477
CREATE TABLE t2 (
 
1478
  userId int(11) NOT NULL,
 
1479
  courseId int(11) NOT NULL,
 
1480
  date datetime NOT NULL
 
1481
);
 
1482
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
 
1483
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
 
1484
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
 
1485
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
 
1486
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
 
1487
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
 
1488
 
 
1489
 
 
1490
CREATE TABLE t3 (
 
1491
  groupId int(11) NOT NULL,
 
1492
  parentId int(11) NOT NULL,
 
1493
  startDate datetime NOT NULL,
 
1494
  endDate datetime NOT NULL,
 
1495
  createDate datetime NOT NULL,
 
1496
  modifyDate timestamp NOT NULL,
 
1497
  ordering int(11)
 
1498
);
 
1499
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
 
1500
 
 
1501
CREATE TABLE t4 (
 
1502
  id int(11) NOT NULL,
 
1503
  groupTypeId int(11) NOT NULL,
 
1504
  groupKey varchar(50) NOT NULL,
 
1505
  name text,
 
1506
  ordering int(11),
 
1507
  description text,
 
1508
  createDate datetime NOT NULL,
 
1509
  modifyDate timestamp NOT NULL
 
1510
);
 
1511
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
 
1512
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
 
1513
 
 
1514
CREATE TABLE t5 (
 
1515
  userId int(11) NOT NULL,
 
1516
  groupId int(11) NOT NULL,
 
1517
  createDate datetime NOT NULL,
 
1518
  modifyDate timestamp NOT NULL
 
1519
);
 
1520
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
 
1521
 
 
1522
select
 
1523
  count(distinct t2.userid) pass,
 
1524
  groupstuff.*,
 
1525
  count(t2.courseid) crse,
 
1526
  t1.categoryid, 
 
1527
  t2.courseid,
 
1528
  date_format(date, '%b%y') as colhead
 
1529
from t2   
 
1530
join t1 on t2.courseid=t1.courseid  
 
1531
join
 
1532
(
 
1533
  select 
 
1534
    t5.userid,  
 
1535
    parentid,  
 
1536
    parentgroup,  
 
1537
    childid,  
 
1538
    groupname,  
 
1539
    grouptypeid  
 
1540
  from t5 
 
1541
  join 
 
1542
  (
 
1543
     select t4.id as parentid,  
 
1544
       t4.name as parentgroup,  
 
1545
       t4.id as childid,  
 
1546
       t4.name as groupname,  
 
1547
       t4.grouptypeid  
 
1548
     from   t4  
 
1549
  ) as gin on t5.groupid=gin.childid 
 
1550
) as groupstuff on t2.userid = groupstuff.userid 
 
1551
group by 
 
1552
  groupstuff.groupname, colhead , t2.courseid;
 
1553
 
 
1554
drop table t1, t2, t3, t4, t5;
 
1555
 
 
1556
#
 
1557
# Transformation in left expression of subquery (BUG#8888)
 
1558
#
 
1559
create table t1 (a int);
 
1560
insert into t1 values (1), (2), (3);
 
1561
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
 
1562
drop table t1;
 
1563
 
 
1564
#
 
1565
# single row subqueries and row operations (code covarage improvement)
 
1566
#
 
1567
create table t1 (a int, b int);
 
1568
insert into t1 values (1,2);
 
1569
-- error 1241
 
1570
select 1 = (select * from t1);
 
1571
-- error 1241
 
1572
select (select * from t1) = 1;
 
1573
-- error 1241
 
1574
select (1,2) = (select a from t1);
 
1575
-- error 1241
 
1576
select (select a from t1) = (1,2);
 
1577
-- error 1241
 
1578
select (1,2,3) = (select * from t1);
 
1579
-- error 1241
 
1580
select (select * from t1) = (1,2,3);
 
1581
drop table t1;
 
1582
 
 
1583
#
 
1584
# Item_int_with_ref check (BUG#10020)
 
1585
#
 
1586
CREATE TABLE `t1` (
 
1587
  `itemid` bigint(20) unsigned NOT NULL auto_increment,
 
1588
  `sessionid` bigint(20) unsigned default NULL,
 
1589
  `time` int(10) unsigned NOT NULL default '0',
 
1590
  `type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
 
1591
NULL default '',
 
1592
  `data` text collate latin1_general_ci NOT NULL,
 
1593
  PRIMARY KEY  (`itemid`)
 
1594
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
1595
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
 
1596
CREATE TABLE `t2` (
 
1597
  `sessionid` bigint(20) unsigned NOT NULL auto_increment,
 
1598
  `pid` int(10) unsigned NOT NULL default '0',
 
1599
  `date` int(10) unsigned NOT NULL default '0',
 
1600
  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
 
1601
  PRIMARY KEY  (`sessionid`)
 
1602
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
1603
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
 
1604
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;
 
1605
drop tables t1,t2;
 
1606
 
 
1607
# BUG#11821 : Select from subselect using aggregate function on an enum
 
1608
# segfaults:
 
1609
create table t1 (fld enum('0','1'));
 
1610
insert into t1 values ('1');
 
1611
select * from (select max(fld) from t1) as foo;
 
1612
drop table t1;
 
1613
 
 
1614
#
 
1615
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
 
1616
#
 
1617
 
 
1618
CREATE TABLE t1 (one int, two int, flag char(1));
 
1619
CREATE TABLE t2 (one int, two int, flag char(1));
 
1620
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
 
1621
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
 
1622
 
 
1623
SELECT * FROM t1
 
1624
  WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
 
1625
SELECT * FROM t1
 
1626
  WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
 
1627
 
 
1628
insert into t2 values (null,null,'N');
 
1629
insert into t2 values (null,3,'0');
 
1630
insert into t2 values (null,5,'0');
 
1631
insert into t2 values (10,null,'0');
 
1632
insert into t1 values (10,3,'0');
 
1633
insert into t1 values (10,5,'0');
 
1634
insert into t1 values (10,10,'0');
 
1635
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
 
1636
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
 
1637
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
 
1638
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
 
1639
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
 
1640
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
 
1641
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
 
1642
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;
 
1643
DROP TABLE t1,t2;
 
1644
 
 
1645
#
 
1646
# Bug #12392: where cond with IN predicate for rows and NULL values in table 
 
1647
#
 
1648
 
 
1649
CREATE TABLE t1 (a char(5), b char(5));
 
1650
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
 
1651
 
 
1652
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
 
1653
 
 
1654
DROP TABLE t1;
 
1655
 
 
1656
#
 
1657
# Bug #11479: subquery over left join with an empty inner table 
 
1658
#
 
1659
 
 
1660
CREATE TABLE t1 (a int);
 
1661
CREATE TABLE t2 (a int, b int);
 
1662
CREATE TABLE t3 (b int NOT NULL);
 
1663
INSERT INTO t1 VALUES (1), (2), (3), (4);
 
1664
INSERT INTO t2 VALUES (1,10), (3,30);
 
1665
 
 
1666
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
 
1667
  WHERE t3.b IS NOT NULL OR t2.a > 10;
 
1668
SELECT * FROM t1
 
1669
  WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
 
1670
                       WHERE t3.b IS NOT NULL OR t2.a > 10);
 
1671
 
 
1672
DROP TABLE t1,t2,t3;
 
1673
 
 
1674
#
 
1675
# Bug#18503: Queries with a quantified subquery returning empty set may
 
1676
# return a wrong result. 
 
1677
#
 
1678
CREATE TABLE t1 (f1 INT);
 
1679
CREATE TABLE t2 (f2 INT);
 
1680
INSERT INTO t1 VALUES (1);
 
1681
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
 
1682
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
 
1683
INSERT INTO t2 VALUES (1);
 
1684
INSERT INTO t2 VALUES (2);
 
1685
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
 
1686
DROP TABLE t1, t2;
 
1687
 
 
1688
# BUG#20975 Wrong query results for subqueries within NOT
 
1689
create table t1 (s1 char);
 
1690
insert into t1 values (1),(2);
 
1691
 
 
1692
select * from t1 where (s1 < any (select s1 from t1));
 
1693
select * from t1 where not (s1 < any (select s1 from t1));
 
1694
 
 
1695
select * from t1 where (s1 < ALL (select s1+1 from t1));
 
1696
select * from t1 where not(s1 < ALL (select s1+1 from t1));
 
1697
 
 
1698
select * from t1 where (s1+1 = ANY (select s1 from t1));
 
1699
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
 
1700
 
 
1701
select * from t1 where (s1 = ALL (select s1/s1 from t1));
 
1702
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
 
1703
drop table t1;
 
1704
 
 
1705
#
 
1706
# Bug #16255: Subquery in where
 
1707
#
 
1708
create table t1 (
 
1709
  retailerID varchar(8) NOT NULL,
 
1710
  statusID   int(10) unsigned NOT NULL,
 
1711
  changed    datetime NOT NULL,
 
1712
  UNIQUE KEY retailerID (retailerID, statusID, changed)
 
1713
);
 
1714
 
 
1715
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
 
1716
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
 
1717
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
 
1718
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
 
1719
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
 
1720
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
 
1721
 
 
1722
select * from t1 r1 
 
1723
  where (r1.retailerID,(r1.changed)) in 
 
1724
         (SELECT r2.retailerId,(max(changed)) from t1 r2 
 
1725
          group by r2.retailerId);
 
1726
drop table t1;
 
1727
 
 
1728
#
 
1729
# Bug #21180: Subselect with index for both WHERE and ORDER BY 
 
1730
#             produces empty result
 
1731
#
 
1732
create table t1(a int, primary key (a));
 
1733
insert into t1 values (10);
 
1734
 
 
1735
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
 
1736
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
 
1737
 
 
1738
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 
1739
  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
 
1740
             ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 
1741
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 
1742
  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
 
1743
            ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 
1744
 
 
1745
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 
1746
  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
 
1747
            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 
1748
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 
1749
  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
 
1750
            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 
1751
 
 
1752
drop table t1,t2;
 
1753
 
 
1754
#
 
1755
# Bug #21853: assert failure for a grouping query with
 
1756
#             an ALL/ANY quantified subquery in HAVING 
 
1757
#
 
1758
 
 
1759
CREATE TABLE t1 (                  
 
1760
  field1 int NOT NULL,                 
 
1761
  field2 int NOT NULL,                 
 
1762
  field3 int NOT NULL,                 
 
1763
  PRIMARY KEY  (field1,field2,field3)  
 
1764
);
 
1765
CREATE TABLE t2 (             
 
1766
  fieldA int NOT NULL,            
 
1767
  fieldB int NOT NULL,            
 
1768
  PRIMARY KEY  (fieldA,fieldB)     
 
1769
); 
 
1770
 
 
1771
INSERT INTO t1 VALUES
 
1772
  (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
 
1773
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
 
1774
 
 
1775
SELECT field1, field2, COUNT(*)
 
1776
  FROM t1 GROUP BY field1, field2;
 
1777
 
 
1778
SELECT field1, field2
 
1779
  FROM  t1
 
1780
    GROUP BY field1, field2
 
1781
      HAVING COUNT(*) >= ALL (SELECT fieldB 
 
1782
                                FROM t2 WHERE fieldA = field1);
 
1783
SELECT field1, field2
 
1784
  FROM  t1
 
1785
    GROUP BY field1, field2
 
1786
      HAVING COUNT(*) < ANY (SELECT fieldB 
 
1787
                               FROM t2 WHERE fieldA = field1);
 
1788
 
 
1789
DROP TABLE t1, t2;
 
1790
 
 
1791
#
 
1792
# Bug #23478: not top-level IN subquery returning a non-empty result set
 
1793
#             with possible NULL values by index access from the outer query
 
1794
#
 
1795
 
 
1796
CREATE TABLE t1(a int, INDEX (a));
 
1797
INSERT INTO t1 VALUES (1), (3), (5), (7);
 
1798
INSERT INTO t1 VALUES (NULL);
 
1799
 
 
1800
CREATE TABLE t2(a int);
 
1801
INSERT INTO t2 VALUES (1),(2),(3);
 
1802
 
 
1803
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
 
1804
SELECT a, a IN (SELECT a FROM t1) FROM t2;
 
1805
 
 
1806
DROP TABLE t1,t2;
 
1807
 
 
1808
#
 
1809
# Bug #11302: getObject() returns a String for a sub-query of type datetime
 
1810
#
 
1811
CREATE TABLE t1 (a DATETIME);
 
1812
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
 
1813
 
 
1814
CREATE TABLE t2 AS SELECT 
 
1815
  (SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a 
 
1816
   FROM t1 WHERE a > '2000-01-01';
 
1817
SHOW CREATE TABLE t2;
 
1818
 
 
1819
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01'); 
 
1820
SHOW CREATE TABLE t3;
 
1821
 
 
1822
DROP TABLE t1,t2,t3;
 
1823
 
 
1824
#
 
1825
# Bug 24653: sorting by expressions containing subselects
 
1826
#            that return more than one row
 
1827
#
 
1828
 
 
1829
CREATE TABLE t1 (a int);
 
1830
INSERT INTO t1 VALUES (2), (4), (1), (3);
 
1831
 
 
1832
CREATE TABLE t2 (b int, c int);
 
1833
INSERT INTO t2 VALUES
 
1834
  (2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
 
1835
 
 
1836
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
 
1837
--error 1242
 
1838
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
 
1839
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
 
1840
--error 1242
 
1841
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
 
1842
 
 
1843
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
 
1844
--error 1242
 
1845
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
 
1846
 
 
1847
 
 
1848
SELECT a FROM t1 GROUP BY a
 
1849
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
 
1850
                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
 
1851
--error 1242
 
1852
SELECT a FROM t1 GROUP BY a
 
1853
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
 
1854
                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
 
1855
 
 
1856
SELECT a FROM t1 GROUP BY a
 
1857
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
 
1858
                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
 
1859
--error 1242
 
1860
SELECT a FROM t1 GROUP BY a
 
1861
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
 
1862
                (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
 
1863
 
 
1864
SELECT a FROM t1
 
1865
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
 
1866
                  (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
 
1867
--error 1242
 
1868
SELECT a FROM t1
 
1869
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
 
1870
                  (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
 
1871
 
 
1872
SELECT a FROM t1
 
1873
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
 
1874
                  (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
 
1875
--error 1242
 
1876
SELECT a FROM t1
 
1877
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
 
1878
                  (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
 
1879
 
 
1880
DROP TABLE t1,t2;
 
1881
 
 
1882
# End of 4.1 tests
 
1883
 
 
1884
#
 
1885
#decimal-related tests
 
1886
#
 
1887
create table t1 (df decimal(5,1));
 
1888
insert into t1 values(1.1);
 
1889
insert into t1 values(2.2);
 
1890
 
 
1891
select * from t1 where df <= all (select avg(df) from t1 group by df);
 
1892
select * from t1 where df >= all (select avg(df) from t1 group by df);
 
1893
drop table t1;
 
1894
 
 
1895
create table t1 (df decimal(5,1));
 
1896
insert into t1 values(1.1);
 
1897
select 1.1 * exists(select * from t1);
 
1898
drop table t1;
 
1899
 
 
1900
CREATE TABLE t1 (
 
1901
  grp int(11) default NULL,
 
1902
  a decimal(10,2) default NULL);
 
1903
 
 
1904
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
 
1905
select * from t1;
 
1906
select min(a) from t1 group by grp;
 
1907
drop table t1;
 
1908
 
 
1909
#
 
1910
# Test for bug #9338: lame substitution of c1 instead of c2 
 
1911
#
 
1912
 
 
1913
CREATE table t1 ( c1 integer );
 
1914
INSERT INTO t1 VALUES ( 1 );
 
1915
INSERT INTO t1 VALUES ( 2 );
 
1916
INSERT INTO t1 VALUES ( 3 );
 
1917
 
 
1918
CREATE TABLE t2 ( c2 integer );
 
1919
INSERT INTO t2 VALUES ( 1 );
 
1920
INSERT INTO t2 VALUES ( 4 );
 
1921
INSERT INTO t2 VALUES ( 5 );
 
1922
 
 
1923
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
 
1924
 
 
1925
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
 
1926
  WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
 
1927
 
 
1928
DROP TABLE t1,t2;
 
1929
 
 
1930
#
 
1931
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ 
 
1932
#
 
1933
CREATE TABLE t1 ( c1 integer );
 
1934
INSERT INTO t1 VALUES ( 1 );
 
1935
INSERT INTO t1 VALUES ( 2 );
 
1936
INSERT INTO t1 VALUES ( 3 );
 
1937
INSERT INTO t1 VALUES ( 6 ); 
 
1938
 
 
1939
CREATE TABLE t2 ( c2 integer );
 
1940
INSERT INTO t2 VALUES ( 1 );
 
1941
INSERT INTO t2 VALUES ( 4 );
 
1942
INSERT INTO t2 VALUES ( 5 );
 
1943
INSERT INTO t2 VALUES ( 6 );
 
1944
 
 
1945
CREATE TABLE t3 ( c3 integer );
 
1946
INSERT INTO t3 VALUES ( 7 );
 
1947
INSERT INTO t3 VALUES ( 8 );
 
1948
 
 
1949
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 
 
1950
  WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
 
1951
 
 
1952
DROP TABLE t1,t2,t3;
 
1953
 
 
1954
#
 
1955
# Item_int_with_ref check (BUG#10020)
 
1956
#
 
1957
CREATE TABLE `t1` (
 
1958
  `itemid` bigint(20) unsigned NOT NULL auto_increment,
 
1959
  `sessionid` bigint(20) unsigned default NULL,
 
1960
  `time` int(10) unsigned NOT NULL default '0',
 
1961
  `type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
 
1962
NULL default '',
 
1963
  `data` text collate latin1_general_ci NOT NULL,
 
1964
  PRIMARY KEY  (`itemid`)
 
1965
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
1966
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
 
1967
CREATE TABLE `t2` (
 
1968
  `sessionid` bigint(20) unsigned NOT NULL auto_increment,
 
1969
  `pid` int(10) unsigned NOT NULL default '0',
 
1970
  `date` int(10) unsigned NOT NULL default '0',
 
1971
  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
 
1972
  PRIMARY KEY  (`sessionid`)
 
1973
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
1974
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
 
1975
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;
 
1976
drop tables t1,t2;
 
1977
 
 
1978
#
 
1979
# Correct building of equal fields list (do not include outer
 
1980
# fields) (BUG#6384)
 
1981
#
 
1982
CREATE TABLE t1 (EMPNUM   CHAR(3));
 
1983
CREATE TABLE t2 (EMPNUM   CHAR(3) );
 
1984
INSERT INTO t1 VALUES ('E1'),('E2');
 
1985
INSERT INTO t2 VALUES ('E1');
 
1986
DELETE FROM t1
 
1987
WHERE t1.EMPNUM NOT IN
 
1988
      (SELECT t2.EMPNUM
 
1989
       FROM t2
 
1990
       WHERE t1.EMPNUM = t2.EMPNUM);
 
1991
select * from t1;
 
1992
DROP TABLE t1,t2;
 
1993
 
 
1994
#
 
1995
# Test for bug #11487: range access in a subquery
 
1996
#
 
1997
 
 
1998
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
 
1999
INSERT INTO t1 VALUES (1, 1);
 
2000
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, 
 
2001
                 PRIMARY KEY(select_id,values_id));
 
2002
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
 
2003
 
 
2004
SELECT values_id FROM t1 
 
2005
WHERE values_id IN (SELECT values_id FROM t2
 
2006
                    WHERE select_id IN (1, 0));
 
2007
SELECT values_id FROM t1 
 
2008
WHERE values_id IN (SELECT values_id FROM t2
 
2009
                    WHERE select_id BETWEEN 0 AND 1);
 
2010
SELECT values_id FROM t1 
 
2011
WHERE values_id IN (SELECT values_id FROM t2
 
2012
                    WHERE select_id = 0 OR select_id = 1);
 
2013
 
 
2014
DROP TABLE t1, t2;
 
2015
 
 
2016
# BUG#11821 : Select from subselect using aggregate function on an enum
 
2017
# segfaults:
 
2018
create table t1 (fld enum('0','1'));
 
2019
insert into t1 values ('1');
 
2020
select * from (select max(fld) from t1) as foo;
 
2021
drop table t1;
 
2022
 
 
2023
#
 
2024
# Test for bug #11762: subquery with an aggregate function in HAVING
 
2025
#
 
2026
 
 
2027
CREATE TABLE t1 (a int, b int);
 
2028
CREATE TABLE t2 (c int, d int);
 
2029
CREATE TABLE t3 (e int);
 
2030
 
 
2031
INSERT INTO t1 VALUES 
 
2032
  (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
 
2033
INSERT INTO t2 VALUES
 
2034
  (2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
 
2035
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
 
2036
 
 
2037
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
 
2038
SELECT * FROM t2;
 
2039
SELECT * FROM t3;
 
2040
 
 
2041
SELECT a FROM t1 GROUP BY a
 
2042
  HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
 
2043
SELECT a FROM t1 GROUP BY a
 
2044
  HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
 
2045
SELECT a FROM t1 GROUP BY a
 
2046
  HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
 
2047
SELECT a FROM t1 GROUP BY a
 
2048
  HAVING a IN (SELECT c FROM t2
 
2049
                 WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
 
2050
SELECT a FROM t1 GROUP BY a
 
2051
  HAVING a IN (SELECT c FROM t2
 
2052
                 WHERE  EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
 
2053
SELECT a FROM t1 GROUP BY a
 
2054
  HAVING a IN (SELECT c FROM t2
 
2055
                 WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
 
2056
SELECT a FROM t1 GROUP BY a
 
2057
  HAVING a IN (SELECT c FROM t2
 
2058
                 WHERE  EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
 
2059
SELECT a FROM t1 GROUP BY a
 
2060
  HAVING a IN (SELECT c FROM t2
 
2061
                 WHERE MIN(b) < d AND 
 
2062
                       EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
 
2063
 
 
2064
SELECT a, SUM(a) FROM t1 GROUP BY a;
 
2065
 
 
2066
SELECT a FROM t1
 
2067
   WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
 
2068
SELECT a FROM t1 GROUP BY a
 
2069
   HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
 
2070
 
 
2071
SELECT a FROM t1
 
2072
   WHERE a < 3 AND
 
2073
         EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
 
2074
SELECT a FROM t1
 
2075
   WHERE a < 3 AND
 
2076
         EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
 
2077
 
 
2078
SELECT t1.a FROM t1 GROUP BY t1.a
 
2079
  HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
 
2080
                       HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
 
2081
                                       HAVING SUM(t1.a+t2.c) < t3.e/4));
 
2082
SELECT t1.a FROM t1 GROUP BY t1.a
 
2083
       HAVING t1.a > ALL(SELECT t2.c FROM t2
 
2084
                           WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
 
2085
                                          HAVING SUM(t1.a+t2.c) < t3.e/4));
 
2086
-- error 1111
 
2087
SELECT t1.a FROM t1 GROUP BY t1.a
 
2088
       HAVING t1.a > ALL(SELECT t2.c FROM t2
 
2089
                           WHERE EXISTS(SELECT t3.e FROM t3 
 
2090
                                          WHERE SUM(t1.a+t2.c) < t3.e/4));
 
2091
-- error 1111 
 
2092
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
 
2093
 
 
2094
SELECT t1.a FROM t1 GROUP BY t1.a
 
2095
  HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
 
2096
                    HAVING AVG(t2.c+SUM(t1.b)) > 20);
 
2097
SELECT t1.a FROM t1 GROUP BY t1.a
 
2098
  HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
 
2099
                    HAVING AVG(SUM(t1.b)) > 20);
 
2100
 
 
2101
SELECT t1.a, SUM(b) AS sum  FROM t1 GROUP BY t1.a
 
2102
  HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
 
2103
                    HAVING t2.c+sum > 20);
 
2104
 
 
2105
DROP TABLE t1,t2,t3;
 
2106
 
 
2107
#
 
2108
# Test for bug #16603: GROUP BY in a row subquery with a quantifier 
 
2109
#                      when an index is defined on the grouping field
 
2110
 
 
2111
CREATE TABLE t1 (a varchar(5), b varchar(10));
 
2112
INSERT INTO t1 VALUES
 
2113
  ('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
 
2114
  ('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
 
2115
 
 
2116
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 
2117
EXPLAIN
 
2118
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 
2119
 
 
2120
ALTER TABLE t1 ADD INDEX(a);
 
2121
 
 
2122
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 
2123
EXPLAIN
 
2124
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 
2125
 
 
2126
DROP TABLE t1;
 
2127
 
 
2128
#
 
2129
# Bug#17366: Unchecked Item_int results in server crash
 
2130
#
 
2131
create table t1( f1 int,f2 int);
 
2132
insert into t1 values (1,1),(2,2);
 
2133
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';
 
2134
drop table t1;
 
2135
 
 
2136
#
 
2137
# Bug #18306: server crash on delete using subquery.
 
2138
#
 
2139
 
 
2140
create table t1 (c int, key(c));                              
 
2141
insert into t1 values (1142477582), (1142455969);
 
2142
create table t2 (a int, b int);
 
2143
insert into t2 values (2, 1), (1, 0);
 
2144
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
 
2145
drop table t1, t2;
 
2146
 
 
2147
#
 
2148
# Bug#19077: A nested materialized derived table is used before being populated.
 
2149
#
 
2150
create table t1 (i int, j bigint);
 
2151
insert into t1 values (1, 2), (2, 2), (3, 2);
 
2152
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
 
2153
drop table t1;
 
2154
 
 
2155
 
2156
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
 
2157
#
 
2158
CREATE TABLE t1 (i BIGINT UNSIGNED);
 
2159
INSERT INTO t1 VALUES (10000000000000000000); # > MAX SIGNED BIGINT 9323372036854775807
 
2160
INSERT INTO t1 VALUES (1);
 
2161
 
 
2162
CREATE TABLE t2 (i BIGINT UNSIGNED);
 
2163
INSERT INTO t2 VALUES (10000000000000000000); # same as first table
 
2164
INSERT INTO t2 VALUES (1);
 
2165
 
 
2166
/* simple test */
 
2167
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
 
2168
 
 
2169
/* subquery test */
 
2170
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
 
2171
 
 
2172
/* subquery test with cast*/
 
2173
SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);
 
2174
 
 
2175
DROP TABLE t1;
 
2176
DROP TABLE t2;
 
2177
 
 
2178
 
2179
# Bug#20519: subselect with LIMIT M, N
 
2180
#
 
2181
 
 
2182
CREATE TABLE t1 (
 
2183
  id bigint(20) unsigned NOT NULL auto_increment,
 
2184
  name varchar(255) NOT NULL,
 
2185
  PRIMARY KEY  (id)
 
2186
);
 
2187
INSERT INTO t1 VALUES
 
2188
  (1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
 
2189
 
 
2190
CREATE TABLE t2 (
 
2191
  id bigint(20) unsigned NOT NULL auto_increment,
 
2192
  mid bigint(20) unsigned NOT NULL,
 
2193
  date date NOT NULL,
 
2194
  PRIMARY KEY  (id)
 
2195
);
 
2196
INSERT INTO t2 VALUES 
 
2197
  (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
 
2198
  (4, 2, '2006-04-20'), (5, 1, '2006-05-01');
 
2199
 
 
2200
SELECT *,
 
2201
      (SELECT date FROM t2 WHERE mid = t1.id
 
2202
         ORDER BY date DESC LIMIT 0, 1) AS date_last,
 
2203
      (SELECT date FROM t2 WHERE mid = t1.id
 
2204
         ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
 
2205
  FROM t1;
 
2206
SELECT *,
 
2207
      (SELECT COUNT(*) FROM t2 WHERE mid = t1.id
 
2208
         ORDER BY date DESC LIMIT 1, 1) AS date_count
 
2209
  FROM t1;
 
2210
SELECT *,
 
2211
      (SELECT date FROM t2 WHERE mid = t1.id
 
2212
         ORDER BY date DESC LIMIT 0, 1) AS date_last,
 
2213
      (SELECT date FROM t2 WHERE mid = t1.id
 
2214
         ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
 
2215
  FROM t1;
 
2216
DROP TABLE t1,t2;
 
2217
 
 
2218
#
 
2219
# Bug#20869: subselect with range access by DESC
 
2220
#
 
2221
 
 
2222
CREATE TABLE t1 (
 
2223
  i1 int(11) NOT NULL default '0',
 
2224
  i2 int(11) NOT NULL default '0',
 
2225
  t datetime NOT NULL default '0000-00-00 00:00:00',
 
2226
  PRIMARY KEY  (i1,i2,t)
 
2227
);
 
2228
INSERT INTO t1 VALUES 
 
2229
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
 
2230
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
 
2231
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
 
2232
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
 
2233
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
 
2234
(24,2,'2005-05-27 12:40:06');
 
2235
 
 
2236
CREATE TABLE t2 (
 
2237
  i1 int(11) NOT NULL default '0',
 
2238
  i2 int(11) NOT NULL default '0',
 
2239
  t datetime default NULL,
 
2240
  PRIMARY KEY  (i1)
 
2241
);
 
2242
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
 
2243
 
 
2244
EXPLAIN
 
2245
SELECT * FROM t1,t2
 
2246
  WHERE t1.t = (SELECT t1.t FROM t1 
 
2247
                  WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
 
2248
                    ORDER BY t1.t DESC LIMIT 1);
 
2249
SELECT * FROM t1,t2
 
2250
  WHERE t1.t = (SELECT t1.t FROM t1 
 
2251
                  WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
 
2252
                    ORDER BY t1.t DESC LIMIT 1);
 
2253
 
 
2254
DROP TABLE t1, t2;
 
2255
 
 
2256
#
 
2257
# Bug#14654 : Cannot select from the same table twice within a UNION
 
2258
# statement 
 
2259
#
 
2260
CREATE TABLE t1 (i INT);
 
2261
 
 
2262
(SELECT i FROM t1) UNION (SELECT i FROM t1);
 
2263
SELECT * FROM t1 WHERE NOT EXISTS 
 
2264
  (
 
2265
   (SELECT i FROM t1) UNION 
 
2266
   (SELECT i FROM t1)
 
2267
  );
 
2268
 
 
2269
#TODO:not supported
 
2270
--error ER_PARSE_ERROR
 
2271
SELECT * FROM t1 
 
2272
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
 
2273
 
 
2274
#TODO:not supported
 
2275
--error 1064
 
2276
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
 
2277
  from t1;
 
2278
 
 
2279
explain select * from t1 where not exists 
 
2280
  ((select t11.i from t1 t11) union (select t12.i from t1 t12));
 
2281
 
 
2282
DROP TABLE t1;
 
2283
 
 
2284
#
 
2285
# Bug#21798: memory leak during query execution with subquery in column 
 
2286
#             list using a function
 
2287
#
 
2288
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
 
2289
insert into t1 (a) values (FLOOR(rand() * 100));
 
2290
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2291
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2292
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2293
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2294
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2295
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2296
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2297
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2298
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2299
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2300
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2301
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2302
insert into t1 (a) select FLOOR(rand() * 100) from t1;
 
2303
 
 
2304
SELECT a, 
 
2305
       (SELECT REPEAT(' ',250) FROM t1 i1 
 
2306
        WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a 
 
2307
FROM t1 ORDER BY a LIMIT 5;
 
2308
DROP TABLE t1;
 
2309
 
 
2310
#
 
2311
# Bug #21540: Subqueries with no from and aggregate functions return 
 
2312
#              wrong results
 
2313
CREATE TABLE t1 (a INT, b INT);
 
2314
CREATE TABLE t2 (a INT);
 
2315
INSERT INTO t2 values (1);
 
2316
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
 
2317
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
 
2318
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
 
2319
  FROM t1 GROUP BY t1.a;
 
2320
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
 
2321
SELECT (
 
2322
  SELECT (
 
2323
      SELECT (
 
2324
        SELECT COUNT(DISTINCT t1.b)
 
2325
      )
 
2326
  ) 
 
2327
  FROM t1 GROUP BY t1.a LIMIT 1) 
 
2328
FROM t1 t2
 
2329
GROUP BY t2.a;
 
2330
DROP TABLE t1,t2;  
 
2331
 
 
2332
#
 
2333
# Bug #21727: Correlated subquery that requires filesort:
 
2334
#             slow with big sort_buffer_size 
 
2335
#
 
2336
 
 
2337
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
 
2338
CREATE TABLE t2 (x int auto_increment, y int, z int,
 
2339
                 PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
 
2340
 
 
2341
disable_query_log;
 
2342
let $1=3000;
 
2343
while ($1)
 
2344
{
 
2345
  eval INSERT INTO t1(a) VALUES(RAND()*1000);
 
2346
  eval SELECT MAX(b) FROM t1 INTO @id;
 
2347
  let $2=10;
 
2348
  while ($2)
 
2349
  {
 
2350
    eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000);
 
2351
    dec $2;
 
2352
  } 
 
2353
  dec $1;
 
2354
}
 
2355
enable_query_log;
 
2356
 
 
2357
SET SESSION sort_buffer_size = 32 * 1024;
 
2358
SELECT COUNT(*) 
 
2359
  FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
 
2360
          FROM t1) t;
 
2361
 
 
2362
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
 
2363
SELECT COUNT(*) 
 
2364
  FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
 
2365
          FROM t1) t;
 
2366
 
 
2367
DROP TABLE t1,t2;
 
2368
 
 
2369
#
 
2370
# Bug #25219: EXIST subquery with UNION over a mix of
 
2371
#             correlated and uncorrelated selects
 
2372
#
 
2373
 
 
2374
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
 
2375
CREATE TABLE t2 (c int);
 
2376
 
 
2377
INSERT INTO t1 VALUES ('aa', 1);
 
2378
INSERT INTO t2 VALUES (1);
 
2379
 
 
2380
SELECT * FROM t1
 
2381
  WHERE EXISTS (SELECT c FROM t2 WHERE c=1
 
2382
                UNION
 
2383
                SELECT c from t2 WHERE c=t1.c);
 
2384
 
 
2385
INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
 
2386
 
 
2387
SELECT * FROM t1
 
2388
  WHERE EXISTS (SELECT c FROM t2 WHERE c=1
 
2389
                UNION
 
2390
                SELECT c from t2 WHERE c=t1.c);
 
2391
 
 
2392
INSERT INTO t2 VALUES (2);
 
2393
CREATE TABLE t3 (c int);
 
2394
INSERT INTO t3 VALUES (1);
 
2395
 
 
2396
SELECT * FROM t1
 
2397
  WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
 
2398
                UNION
 
2399
                SELECT c from t2 WHERE c=t1.c);
 
2400
 
 
2401
DROP TABLE t1,t2,t3;
 
2402
 
 
2403
#
 
2404
# Bug#21904 (parser problem when using IN with a double "(())")
 
2405
#
 
2406
 
 
2407
--disable_warnings
 
2408
DROP TABLE IF EXISTS t1;
 
2409
DROP TABLE IF EXISTS t2;
 
2410
DROP TABLE IF EXISTS t1xt2;
 
2411
--enable_warnings
 
2412
 
 
2413
CREATE TABLE t1 (
 
2414
  id_1 int(5) NOT NULL,
 
2415
  t varchar(4) DEFAULT NULL
 
2416
);
 
2417
 
 
2418
CREATE TABLE t2 (
 
2419
  id_2 int(5) NOT NULL,
 
2420
  t varchar(4) DEFAULT NULL
 
2421
);
 
2422
 
 
2423
CREATE TABLE t1xt2 (
 
2424
  id_1 int(5) NOT NULL,
 
2425
  id_2 int(5) NOT NULL
 
2426
);
 
2427
 
 
2428
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
 
2429
 
 
2430
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
 
2431
 
 
2432
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
 
2433
 
 
2434
# subselect returns 0 rows
 
2435
 
 
2436
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2437
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
 
2438
 
 
2439
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2440
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
 
2441
 
 
2442
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2443
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
 
2444
 
 
2445
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2446
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
 
2447
 
 
2448
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2449
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
 
2450
 
 
2451
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2452
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
 
2453
 
 
2454
insert INTO t1xt2 VALUES (1, 12);
 
2455
 
 
2456
# subselect returns 1 row
 
2457
 
 
2458
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2459
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
 
2460
 
 
2461
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2462
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
 
2463
 
 
2464
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2465
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
 
2466
 
 
2467
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2468
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
 
2469
 
 
2470
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2471
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
 
2472
 
 
2473
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2474
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
 
2475
 
 
2476
insert INTO t1xt2 VALUES (2, 12);
 
2477
 
 
2478
# subselect returns more than 1 row
 
2479
 
 
2480
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2481
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
 
2482
 
 
2483
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2484
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
 
2485
 
 
2486
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2487
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
 
2488
 
 
2489
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2490
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
 
2491
 
 
2492
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2493
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
 
2494
 
 
2495
SELECT DISTINCT t1.id_1 FROM t1 WHERE
 
2496
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
 
2497
 
 
2498
DROP TABLE t1;
 
2499
DROP TABLE t2;
 
2500
DROP TABLE t1xt2;
 
2501
 
 
2502
#
 
2503
# Bug #26728: derived table with concatanation of literals in select list
 
2504
#  
 
2505
 
 
2506
CREATE TABLE t1 (a int);
 
2507
INSERT INTO t1 VALUES (3), (1), (2);           
 
2508
 
 
2509
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
 
2510
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
 
2511
 
 
2512
DROP table t1;
 
2513
 
 
2514
#
 
2515
# Bug #27257: COUNT(*) aggregated in outer query
 
2516
#  
 
2517
 
 
2518
CREATE TABLE t1 (a int, b int);
 
2519
CREATE TABLE t2 (m int, n int);
 
2520
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
 
2521
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
 
2522
 
 
2523
SELECT COUNT(*), a,
 
2524
       (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
 
2525
  FROM t1 GROUP BY a;
 
2526
 
 
2527
SELECT COUNT(*), a,
 
2528
       (SELECT MIN(m) FROM t2 WHERE m = count(*))
 
2529
  FROM t1 GROUP BY a;
 
2530
 
 
2531
SELECT COUNT(*), a       
 
2532
  FROM t1 GROUP BY a
 
2533
    HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
 
2534
 
 
2535
DROP TABLE t1,t2;
 
2536
 
 
2537
#
 
2538
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument 
 
2539
#  
 
2540
 
 
2541
CREATE TABLE t1 (a int, b int);
 
2542
CREATE TABLE t2 (m int, n int);
 
2543
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
 
2544
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
 
2545
 
 
2546
SELECT COUNT(*) c, a,
 
2547
       (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
 
2548
  FROM t1 GROUP BY a;
 
2549
 
 
2550
SELECT COUNT(*) c, a,
 
2551
       (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
 
2552
  FROM t1 GROUP BY a;
 
2553
 
 
2554
DROP table t1,t2;
 
2555
 
 
2556
#
 
2557
# Bug#27321: Wrong subquery result in a grouping select
 
2558
#
 
2559
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
 
2560
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
 
2561
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
 
2562
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
 
2563
 
 
2564
SELECT a, MAX(b),
 
2565
  (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test 
 
2566
  FROM t1 GROUP BY a;
 
2567
SELECT a x, MAX(b),
 
2568
  (SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
 
2569
  FROM t1 GROUP BY a;
 
2570
SELECT a, AVG(b),
 
2571
  (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
 
2572
  FROM t1 WHERE t1.d=0 GROUP BY a;
 
2573
 
 
2574
SELECT tt.a,
 
2575
 (SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
 
2576
  LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test 
 
2577
  FROM t1 as tt;
 
2578
 
 
2579
SELECT tt.a,
 
2580
 (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
 
2581
  LIMIT 1)
 
2582
  FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test 
 
2583
  FROM t1 as tt GROUP BY tt.a;
 
2584
 
 
2585
SELECT tt.a, MAX(
 
2586
 (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
 
2587
  LIMIT 1)
 
2588
  FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test 
 
2589
  FROM t1 as tt GROUP BY tt.a;
 
2590
 
 
2591
DROP TABLE t1;
 
2592
 
 
2593
#
 
2594
# Bug #27363: nested aggregates in outer, subquery / sum(select
 
2595
# count(outer))
 
2596
#
 
2597
CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
 
2598
CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
 
2599
 
 
2600
--error ER_INVALID_GROUP_FUNC_USE
 
2601
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
 
2602
--error ER_INVALID_GROUP_FUNC_USE
 
2603
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
 
2604
 
 
2605
--error ER_INVALID_GROUP_FUNC_USE
 
2606
SELECT 
 
2607
  SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
 
2608
FROM t1;
 
2609
 
 
2610
--error ER_INVALID_GROUP_FUNC_USE
 
2611
SELECT t1.a as XXA, 
 
2612
   SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
 
2613
FROM t1;
 
2614
 
 
2615
DROP TABLE t1,t2;
 
2616
 
 
2617
#
 
2618
# Bug #27807: Server crash when executing subquery with EXPLAIN
 
2619
#  
 
2620
CREATE TABLE t1 (a int, b int, KEY (a)); 
 
2621
INSERT INTO t1 VALUES (1,1),(2,1);
 
2622
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 
2623
DROP TABLE t1;
 
2624
 
 
2625
#
 
2626
# Bug #28377: grouping query with a correlated subquery in WHERE condition
 
2627
#  
 
2628
 
 
2629
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 
2630
INSERT INTO t1 VALUES
 
2631
  (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
 
2632
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
 
2633
INSERT INTO t2 VALUES (7), (5), (1), (3);
 
2634
 
 
2635
SELECT id, st FROM t1 
 
2636
  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
 
2637
SELECT id, st FROM t1 
 
2638
  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
 
2639
    GROUP BY id;
 
2640
 
 
2641
SELECT id, st FROM t1 
 
2642
  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
 
2643
SELECT id, st FROM t1 
 
2644
  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
 
2645
    GROUP BY id;
 
2646
 
 
2647
DROP TABLE t1,t2;
 
2648
 
 
2649
#
 
2650
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
 
2651
#             over a grouping subselect
 
2652
 
2653
 
 
2654
CREATE TABLE t1 (a int);
 
2655
 
 
2656
INSERT INTO t1 VALUES (1), (2);
 
2657
 
 
2658
EXPLAIN EXTENDED
 
2659
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
 
2660
 
 
2661
DROP TABLE t1;
 
2662
 
 
2663
#
 
2664
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1 
 
2665
#
 
2666
 
 
2667
CREATE TABLE t1 (
 
2668
  a varchar(255) default NULL,
 
2669
  b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 
2670
  INDEX idx(a,b)
 
2671
);
 
2672
CREATE TABLE t2 (
 
2673
  a varchar(255) default NULL
 
2674
);
 
2675
 
 
2676
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
 
2677
INSERT INTO t1 SELECT * FROM t1;
 
2678
INSERT INTO t1 SELECT * FROM t1;
 
2679
INSERT INTO t1 SELECT * FROM t1;
 
2680
INSERT INTO t1 SELECT * FROM t1;
 
2681
INSERT INTO t1 SELECT * FROM t1;
 
2682
INSERT INTO t1 SELECT * FROM t1;
 
2683
INSERT INTO t1 SELECT * FROM t1;
 
2684
INSERT INTO t1 SELECT * FROM t1;
 
2685
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
 
2686
INSERT INTO `t2` VALUES ('abcdefghijk');
 
2687
INSERT INTO `t2` VALUES ('asdf');
 
2688
 
 
2689
SET session sort_buffer_size=8192;
 
2690
 
 
2691
SELECT (SELECT 1 FROM  t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
 
2692
 
 
2693
DROP TABLE t1,t2;
 
2694
 
 
2695
 
 
2696
#
 
2697
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
 
2698
# of subquery
 
2699
#
 
2700
CREATE TABLE t1 (a INTEGER, b INTEGER);
 
2701
CREATE TABLE t2 (x INTEGER);
 
2702
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
 
2703
INSERT INTO t2 VALUES (1), (2);
 
2704
 
 
2705
# wasn't failing, but should
 
2706
--error ER_SUBQUERY_NO_1_ROW
 
2707
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
 
2708
 
 
2709
# fails as it should
 
2710
--error ER_SUBQUERY_NO_1_ROW
 
2711
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
 
2712
 
 
2713
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
 
2714
DROP TABLE t1,t2;
 
2715
 
 
2716
# second test case from 27333
 
2717
CREATE TABLE t1 (a INT, b INT);
 
2718
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
 
2719
 
 
2720
# returns no rows, when it should
 
2721
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
 
2722
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
 
2723
GROUP BY a1.a;
 
2724
DROP TABLE t1;
 
2725
 
 
2726
#test cases from 29297
 
2727
CREATE TABLE t1 (a INT);
 
2728
CREATE TABLE t2 (a INT);
 
2729
INSERT INTO t1 VALUES (1),(2);
 
2730
INSERT INTO t2 VALUES (1),(2);
 
2731
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
 
2732
--error ER_SUBQUERY_NO_1_ROW
 
2733
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
 
2734
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
 
2735
DROP TABLE t1,t2;
 
2736
 
 
2737
#
 
2738
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
 
2739
#
 
2740
 
 
2741
CREATE TABLE t1 (a1 INT, a2 INT);
 
2742
CREATE TABLE t2 (b1 INT, b2 INT);
 
2743
 
 
2744
INSERT INTO t1 VALUES (100, 200);
 
2745
INSERT INTO t1 VALUES (101, 201);
 
2746
INSERT INTO t2 VALUES (101, 201);
 
2747
INSERT INTO t2 VALUES (103, 203);
 
2748
 
 
2749
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
 
2750
DROP TABLE t1, t2;
 
2751
 
 
2752
#
 
2753
# Bug #28076: inconsistent binary/varbinary comparison
 
2754
#
 
2755
 
 
2756
CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
 
2757
INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
 
2758
 
 
2759
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
 
2760
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
 
2761
 
 
2762
CREATE INDEX I1 ON t1 (s1);
 
2763
CREATE INDEX I2 ON t1 (s2);
 
2764
 
 
2765
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
 
2766
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
 
2767
 
 
2768
TRUNCATE t1;
 
2769
INSERT INTO t1 VALUES (0x41,0x41);
 
2770
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
 
2771
 
 
2772
DROP TABLE t1;
 
2773
 
 
2774
CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
 
2775
CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
 
2776
CREATE TABLE t3 (a3 BINARY(2) default '0');
 
2777
INSERT INTO t1 VALUES (1),(2),(3),(4);
 
2778
INSERT INTO t2 VALUES (1),(2),(3);
 
2779
INSERT INTO t3 VALUES (1),(2),(3);
 
2780
SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
 
2781
SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
 
2782
DROP TABLE t1,t2,t3;
 
2783
 
 
2784
CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
 
2785
CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
 
2786
CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
 
2787
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
 
2788
INSERT INTO t2 VALUES (2), (3), (4), (5);
 
2789
INSERT INTO t3 VALUES (10), (20), (30);
 
2790
SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
 
2791
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
 
2792
DROP TABLE t1, t2, t3;
 
2793
 
 
2794
#
 
2795
# Bug #30788: Inconsistent retrieval of char/varchar
 
2796
#
 
2797
 
 
2798
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
 
2799
INSERT INTO t1 VALUES ('a', 'aa');
 
2800
INSERT INTO t1 VALUES ('a', 'aaa');
 
2801
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
 
2802
CREATE INDEX I1 ON t1 (a);
 
2803
CREATE INDEX I2 ON t1 (b);
 
2804
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
 
2805
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
 
2806
 
 
2807
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
 
2808
INSERT INTO t2 SELECT * FROM t1;
 
2809
CREATE INDEX I1 ON t2 (a);
 
2810
CREATE INDEX I2 ON t2 (b);
 
2811
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
 
2812
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
 
2813
EXPLAIN
 
2814
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
 
2815
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
 
2816
 
 
2817
DROP TABLE t1,t2;
 
2818
 
 
2819
#
 
2820
# Bug #32400: Complex SELECT query returns correct result only on some
 
2821
# occasions
 
2822
#
 
2823
 
 
2824
CREATE TABLE t1(a INT, b INT);
 
2825
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
 
2826
 
 
2827
--error ER_BAD_FIELD_ERROR
 
2828
EXPLAIN 
 
2829
SELECT a AS out_a, MIN(b) FROM t1
 
2830
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
 
2831
GROUP BY a;
 
2832
 
 
2833
--error ER_BAD_FIELD_ERROR
 
2834
SELECT a AS out_a, MIN(b) FROM t1
 
2835
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
 
2836
GROUP BY a;
 
2837
 
 
2838
EXPLAIN 
 
2839
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
 
2840
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
 
2841
GROUP BY a;
 
2842
 
 
2843
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
 
2844
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
 
2845
GROUP BY a;
 
2846
 
 
2847
DROP TABLE t1;
 
2848
 
 
2849
 
 
2850
#
 
2851
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
 
2852
#
 
2853
 
 
2854
CREATE TABLE t1 (a INT);
 
2855
CREATE TABLE t2 (a INT);
 
2856
 
 
2857
INSERT INTO t1 VALUES (1),(2);
 
2858
INSERT INTO t2 VALUES (1),(2);
 
2859
 
 
2860
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 
2861
EXPLAIN EXTENDED
 
2862
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 
2863
 
 
2864
 
 
2865
EXPLAIN EXTENDED
 
2866
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION 
 
2867
                               (SELECT 1 FROM t2 WHERE t1.a = t2.a));
 
2868
 
 
2869
DROP TABLE t1,t2;
 
2870
 
 
2871
#
 
2872
# BUG#33794 "MySQL crashes executing specific query on specific dump"
 
2873
#
 
2874
CREATE TABLE t4 (
 
2875
  f7 varchar(32) collate utf8_bin NOT NULL default '',
 
2876
  f10 varchar(32) collate utf8_bin default NULL,
 
2877
  PRIMARY KEY  (f7)
 
2878
);
 
2879
INSERT INTO t4 VALUES(1,1), (2,null);
 
2880
 
 
2881
CREATE TABLE t2 (
 
2882
  f4 varchar(32) collate utf8_bin NOT NULL default '',
 
2883
  f2 varchar(50) collate utf8_bin default NULL,
 
2884
  f3 varchar(10) collate utf8_bin default NULL,
 
2885
  PRIMARY KEY  (f4),
 
2886
  UNIQUE KEY uk1 (f2)
 
2887
);
 
2888
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
 
2889
 
 
2890
CREATE TABLE t1 (
 
2891
  f8 varchar(32) collate utf8_bin NOT NULL default '',
 
2892
  f1 varchar(10) collate utf8_bin default NULL,
 
2893
  f9 varchar(32) collate utf8_bin default NULL,
 
2894
  PRIMARY KEY  (f8)
 
2895
);
 
2896
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
 
2897
 
 
2898
CREATE TABLE t3 (
 
2899
  f6 varchar(32) collate utf8_bin NOT NULL default '',
 
2900
  f5 varchar(50) collate utf8_bin default NULL,
 
2901
  PRIMARY KEY (f6)
 
2902
);
 
2903
INSERT INTO t3 VALUES (1,null), (2,null);
 
2904
 
 
2905
SELECT
 
2906
  IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
 
2907
  IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
 
2908
  SUM(
 
2909
    IF(
 
2910
      (SELECT VPC.f2
 
2911
       FROM t2 VPC, t4 a2, t2 a3
 
2912
       WHERE
 
2913
         VPC.f4 = a2.f10 AND a3.f2 = a4
 
2914
       LIMIT 1) IS NULL, 
 
2915
       0, 
 
2916
       t3.f5
 
2917
    )
 
2918
  ) AS a6
 
2919
FROM 
 
2920
  t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
 
2921
GROUP BY a4;
 
2922
 
 
2923
DROP TABLE t1, t2, t3, t4;
 
2924
 
 
2925
--echo End of 5.0 tests.
 
2926
 
 
2927
#
 
2928
#  Test [NOT] IN truth table (both as top-level and general predicate).
 
2929
#
 
2930
 
 
2931
create table t_out (subcase char(3),
 
2932
                    a1 char(2), b1 char(2), c1 char(2));
 
2933
create table t_in  (a2 char(2), b2 char(2), c2 char(2));
 
2934
 
 
2935
insert into t_out values ('A.1','2a', NULL, '2a');
 
2936
#------------------------- A.2 - impossible
 
2937
insert into t_out values ('A.3', '2a', NULL, '2a');
 
2938
insert into t_out values ('A.4', '2a', NULL, 'xx');
 
2939
insert into t_out values ('B.1', '2a', '2a', '2a');
 
2940
insert into t_out values ('B.2', '2a', '2a', '2a');
 
2941
insert into t_out values ('B.3', '3a', 'xx', '3a');
 
2942
insert into t_out values ('B.4', 'xx', '3a', '3a');
 
2943
 
 
2944
insert into t_in values ('1a', '1a', '1a');
 
2945
insert into t_in values ('2a', '2a', '2a');
 
2946
insert into t_in values (NULL, '2a', '2a');
 
2947
insert into t_in values ('3a', NULL, '3a');
 
2948
-- echo 
 
2949
-- echo Test general IN semantics (not top-level)
 
2950
-- echo 
 
2951
-- echo case A.1
 
2952
select subcase,
 
2953
       (a1, b1, c1)     IN (select * from t_in where a2 = 'no_match') pred_in,
 
2954
       (a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
 
2955
from t_out where subcase = 'A.1';
 
2956
 
 
2957
-- echo case A.2 - impossible
 
2958
 
 
2959
-- echo case A.3
 
2960
select subcase,
 
2961
       (a1, b1, c1)     IN (select * from t_in) pred_in,
 
2962
       (a1, b1, c1) NOT IN (select * from t_in) pred_not_in
 
2963
from t_out where subcase = 'A.3';
 
2964
 
 
2965
-- echo case A.4
 
2966
select subcase,
 
2967
       (a1, b1, c1)     IN (select * from t_in) pred_in,
 
2968
       (a1, b1, c1) NOT IN (select * from t_in) pred_not_in
 
2969
from t_out where subcase = 'A.4';
 
2970
 
 
2971
-- echo case B.1
 
2972
select subcase,
 
2973
       (a1, b1, c1)     IN (select * from t_in where a2 = 'no_match') pred_in,
 
2974
       (a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
 
2975
from t_out where subcase = 'B.1';
 
2976
 
 
2977
-- echo case B.2
 
2978
select subcase,
 
2979
       (a1, b1, c1)     IN (select * from t_in) pred_in,
 
2980
       (a1, b1, c1) NOT IN (select * from t_in) pred_not_in
 
2981
from t_out where subcase = 'B.2';
 
2982
 
 
2983
-- echo case B.3
 
2984
select subcase,
 
2985
       (a1, b1, c1)     IN (select * from t_in) pred_in,
 
2986
       (a1, b1, c1) NOT IN (select * from t_in) pred_not_in
 
2987
from t_out where subcase = 'B.3';
 
2988
 
 
2989
-- echo case B.4
 
2990
select subcase,
 
2991
       (a1, b1, c1)     IN (select * from t_in) pred_in,
 
2992
       (a1, b1, c1) NOT IN (select * from t_in) pred_not_in
 
2993
from t_out where subcase = 'B.4';
 
2994
 
 
2995
-- echo 
 
2996
-- echo Test IN as top-level predicate, and
 
2997
-- echo as non-top level for cases A.3, B.3 (the only cases with NULL result).
 
2998
-- echo 
 
2999
-- echo case A.1
 
3000
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
 
3001
where subcase = 'A.1' and
 
3002
      (a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
 
3003
 
 
3004
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
 
3005
where subcase = 'A.1' and
 
3006
      (a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
 
3007
 
 
3008
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
 
3009
where subcase = 'A.1' and
 
3010
      NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
 
3011
 
 
3012
-- echo case A.3
 
3013
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
 
3014
where subcase = 'A.3' and
 
3015
      (a1, b1, c1) IN (select * from t_in);
 
3016
 
 
3017
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
 
3018
where subcase = 'A.3' and
 
3019
      (a1, b1, c1) NOT IN (select * from t_in);
 
3020
 
 
3021
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
 
3022
where subcase = 'A.3' and
 
3023
      NOT((a1, b1, c1) IN (select * from t_in));
 
3024
# test non-top level result indirectly
 
3025
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
 
3026
where subcase = 'A.3' and
 
3027
      ((a1, b1, c1) IN (select * from t_in)) is NULL and
 
3028
      ((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
 
3029
 
 
3030
-- echo case A.4
 
3031
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
 
3032
where subcase = 'A.4' and
 
3033
      (a1, b1, c1) IN (select * from t_in);
 
3034
 
 
3035
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
 
3036
where subcase = 'A.4' and
 
3037
      (a1, b1, c1) NOT IN (select * from t_in);
 
3038
 
 
3039
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
 
3040
where subcase = 'A.4' and
 
3041
      NOT((a1, b1, c1) IN (select * from t_in));
 
3042
 
 
3043
-- echo case B.1
 
3044
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
 
3045
where subcase = 'B.1' and
 
3046
      (a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
 
3047
 
 
3048
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
 
3049
where subcase = 'B.1' and
 
3050
      (a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
 
3051
 
 
3052
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
 
3053
where subcase = 'B.1' and
 
3054
      NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
 
3055
 
 
3056
-- echo case B.2
 
3057
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
 
3058
where subcase = 'B.2' and
 
3059
      (a1, b1, c1) IN (select * from t_in);
 
3060
 
 
3061
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
 
3062
where subcase = 'B.2' and
 
3063
      (a1, b1, c1) NOT IN (select * from t_in);
 
3064
 
 
3065
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
 
3066
where subcase = 'B.2' and
 
3067
      NOT((a1, b1, c1) IN (select * from t_in));
 
3068
 
 
3069
-- echo case B.3
 
3070
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
 
3071
where subcase = 'B.3' and
 
3072
      (a1, b1, c1) IN (select * from t_in);
 
3073
 
 
3074
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
 
3075
where subcase = 'B.3' and
 
3076
      (a1, b1, c1) NOT IN (select * from t_in);
 
3077
 
 
3078
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
 
3079
where subcase = 'B.3' and
 
3080
      NOT((a1, b1, c1) IN (select * from t_in));
 
3081
# test non-top level result indirectly
 
3082
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
 
3083
where subcase = 'B.3' and
 
3084
      ((a1, b1, c1) IN (select * from t_in)) is NULL and
 
3085
      ((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
 
3086
 
 
3087
-- echo case B.4
 
3088
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
 
3089
where subcase = 'B.4' and
 
3090
      (a1, b1, c1) IN (select * from t_in);
 
3091
 
 
3092
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
 
3093
where subcase = 'B.4' and
 
3094
      (a1, b1, c1) NOT IN (select * from t_in);
 
3095
 
 
3096
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
 
3097
where subcase = 'B.4' and
 
3098
      NOT((a1, b1, c1) IN (select * from t_in));
 
3099
 
 
3100
drop table t_out;
 
3101
drop table t_in;
 
3102
 
 
3103
 
 
3104
#
 
3105
# Bug#20835 (literal string with =any values)
 
3106
#
 
3107
CREATE TABLE t1 (s1 char(1));
 
3108
INSERT INTO t1 VALUES ('a');
 
3109
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
 
3110
DROP TABLE t1;
 
3111
 
 
3112
#
 
3113
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
 
3114
#
 
3115
CREATE TABLE t1( a INT );
 
3116
INSERT INTO t1 VALUES (1),(2);
 
3117
 
 
3118
CREATE TABLE t2( a INT, b INT );
 
3119
 
 
3120
--error ER_PARSE_ERROR
 
3121
SELECT * 
 
3122
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
 
3123
--error ER_PARSE_ERROR
 
3124
SELECT * 
 
3125
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
 
3126
--error ER_PARSE_ERROR
 
3127
SELECT * 
 
3128
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
 
3129
 
 
3130
--error ER_PARSE_ERROR
 
3131
SELECT * FROM ( 
 
3132
  SELECT 1 a 
 
3133
  UNION 
 
3134
  SELECT a INTO @var FROM t1 WHERE a = 2 
 
3135
) t1a;
 
3136
 
 
3137
--error ER_PARSE_ERROR
 
3138
SELECT * FROM ( 
 
3139
  SELECT 1 a 
 
3140
  UNION 
 
3141
  SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2 
 
3142
) t1a;
 
3143
 
 
3144
--error ER_PARSE_ERROR
 
3145
SELECT * FROM ( 
 
3146
  SELECT 1 a 
 
3147
  UNION 
 
3148
  SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2 
 
3149
) t1a;
 
3150
 
 
3151
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
 
3152
 
 
3153
SELECT * FROM ( 
 
3154
  SELECT a FROM t1 WHERE a = 2 
 
3155
  UNION 
 
3156
  SELECT a FROM t1 WHERE a = 2 
 
3157
) t1a;
 
3158
 
 
3159
SELECT * FROM ( 
 
3160
  SELECT 1 a 
 
3161
  UNION 
 
3162
  SELECT a FROM t1 WHERE a = 2 
 
3163
  UNION 
 
3164
  SELECT a FROM t1 WHERE a = 2 
 
3165
) t1a;
 
3166
 
 
3167
# This was not allowed previously. Possibly, it should be allowed on the future.
 
3168
# For now, the intent is to keep the fix as non-intrusive as possible.
 
3169
--error ER_PARSE_ERROR
 
3170
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
 
3171
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
 
3172
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
 
3173
--error ER_PARSE_ERROR
 
3174
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
 
3175
--error ER_PARSE_ERROR
 
3176
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
 
3177
--error ER_PARSE_ERROR
 
3178
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
 
3179
 
 
3180
--error ER_PARSE_ERROR
 
3181
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
 
3182
--error ER_PARSE_ERROR
 
3183
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
 
3184
--error ER_PARSE_ERROR
 
3185
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
 
3186
 
 
3187
--error ER_PARSE_ERROR
 
3188
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
 
3189
--error ER_PARSE_ERROR
 
3190
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
 
3191
--error ER_PARSE_ERROR
 
3192
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
 
3193
 
 
3194
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
 
3195
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;
 
3196
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
 
3197
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
 
3198
 
 
3199
# Test of rule
 
3200
# table_factor:  '(' get_select_lex query_expression_body ')' opt_table_alias
 
3201
# UNION should not be allowed inside the parentheses, nor should
 
3202
# aliases after.
 
3203
 
3204
SELECT * FROM t1 JOIN  (SELECT 1 UNION SELECT 1) alias ON 1;
 
3205
--error ER_PARSE_ERROR
 
3206
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
 
3207
--error ER_PARSE_ERROR
 
3208
SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 1;
 
3209
--error ER_PARSE_ERROR
 
3210
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
 
3211
--error ER_PARSE_ERROR
 
3212
SELECT * FROM t1 JOIN  (t1 t1a)  t1a ON 1;
 
3213
--error ER_PARSE_ERROR
 
3214
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
 
3215
 
 
3216
SELECT * FROM t1 JOIN  (t1 t1a)  ON 1;
 
3217
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
 
3218
 
 
3219
SELECT * FROM (t1 t1a);
 
3220
SELECT * FROM ((t1 t1a));
 
3221
 
 
3222
SELECT * FROM t1 JOIN  (SELECT 1 t1a) alias ON 1;
 
3223
SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;
 
3224
 
 
3225
SELECT * FROM t1 JOIN  (SELECT 1 a)  a ON 1;
 
3226
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
 
3227
 
 
3228
# For the join, TABLE_LIST::select_lex == NULL
 
3229
# Check that we handle this.
 
3230
--error ER_PARSE_ERROR
 
3231
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
 
3232
 
 
3233
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
 
3234
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );
 
3235
SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );
 
3236
 
 
3237
--error ER_PARSE_ERROR
 
3238
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
 
3239
--error ER_PARSE_ERROR
 
3240
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
 
3241
--error ER_PARSE_ERROR
 
3242
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
 
3243
 
 
3244
SELECT * FROM t1 WHERE a = ( SELECT 1 );
 
3245
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );
 
3246
--error ER_PARSE_ERROR
 
3247
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
 
3248
--error ER_PARSE_ERROR
 
3249
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
 
3250
--error ER_PARSE_ERROR
 
3251
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
 
3252
 
 
3253
--error ER_PARSE_ERROR
 
3254
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
 
3255
--error ER_PARSE_ERROR
 
3256
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
 
3257
--error ER_PARSE_ERROR
 
3258
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
 
3259
 
 
3260
--error ER_PARSE_ERROR
 
3261
SELECT ( SELECT 1 INTO @v );
 
3262
--error ER_PARSE_ERROR
 
3263
SELECT ( SELECT 1 INTO OUTFILE 'file' );
 
3264
--error ER_PARSE_ERROR
 
3265
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
 
3266
 
 
3267
--error ER_PARSE_ERROR
 
3268
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
 
3269
--error ER_PARSE_ERROR
 
3270
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
 
3271
--error ER_PARSE_ERROR
 
3272
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
 
3273
 
 
3274
# Make sure context is popped when we leave the nested select
 
3275
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
 
3276
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
 
3277
 
 
3278
# Make sure we have feature F561 (see .yy file)
 
3279
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
 
3280
 
 
3281
# Make sure the parser does not allow nested UNIONs anywhere
 
3282
 
 
3283
--error ER_PARSE_ERROR
 
3284
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
 
3285
--error ER_PARSE_ERROR
 
3286
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
 
3287
 
 
3288
--error ER_PARSE_ERROR
 
3289
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 
3290
--error ER_PARSE_ERROR
 
3291
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
 
3292
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 
3293
SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
 
3294
 
 
3295
--error ER_PARSE_ERROR
 
3296
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 
3297
--error ER_PARSE_ERROR
 
3298
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
 
3299
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
 
3300
 
 
3301
--error ER_PARSE_ERROR
 
3302
SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 
3303
--error ER_PARSE_ERROR
 
3304
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 
3305
--error ER_PARSE_ERROR
 
3306
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 
3307
--error ER_PARSE_ERROR
 
3308
SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
 
3309
 
 
3310
--error ER_PARSE_ERROR
 
3311
SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 
3312
--error ER_PARSE_ERROR
 
3313
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 
3314
--error ER_PARSE_ERROR
 
3315
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 
3316
--error ER_PARSE_ERROR
 
3317
SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
 
3318
 
 
3319
SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 
3320
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 
3321
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 
3322
SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
 
3323
 
 
3324
--error ER_PARSE_ERROR
 
3325
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
 
3326
SELECT EXISTS(SELECT 1+1);
 
3327
--error ER_PARSE_ERROR
 
3328
SELECT EXISTS(SELECT 1+1 INTO @test);
 
3329
--error ER_PARSE_ERROR
 
3330
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
 
3331
 
 
3332
--error ER_PARSE_ERROR
 
3333
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
 
3334
--error ER_PARSE_ERROR
 
3335
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
 
3336
 
 
3337
DROP TABLE t1, t2;