← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect.test

Fixed sql_builtin.cc.in... stupid generated files.

Show diffs side-by-side

added added

removed removed

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