← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect.test

  • Committer: Mats Kindahl
  • Date: 2008-08-26 07:32:59 UTC
  • mto: (489.1.2 codestyle)
  • mto: This revision was merged to the branch mainline in revision 491.
  • Revision ID: mats@mysql.com-20080826073259-9k4evtajgldgolli
Replaced use of thd_proc_info() macro with calls to
set_proc_info() and get_proc_info() internally.  Introduced
functions set_thd_proc_info() and get_thd_proc_info() for
external users, i.e., plug-ins.

The set_thd_proc_info() accepted callers info that can be used to
print debug output, but the information was not used. The return
value was changed to void and the old value is not fetched any
more. To be able to get the value of proc_info for external
users, the function get_thd_proc_info() was introduced.

The thd_proc_info() macro called set_thd_proc_info() but almost
never used the return value of set_thd_proc_info() so the macro
was replaced with a call of THD::set_proc_info().

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
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;
109
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
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
 
130
# not unique fields
136
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
158
-- error 1241
165
159
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
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 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;
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 unsigned NOT NULL auto_increment,
 
193
  `maxnumrep` int 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 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);
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 unsigned NOT NULL auto_increment,
 
236
  `maxnumrep` int 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
242
-- error 1242
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
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 unsigned NOT NULL default '0',
 
263
  `numreponse` int 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
 
);
 
268
) ENGINE=MyISAM;
275
269
-- error 1247
276
270
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
277
271
-- error 1054
280
274
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
281
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);
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);
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);
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);
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
355
-- error 1054
344
 
INSERT INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
345
 
INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
 
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;
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
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 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);
393
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 default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
397
410
INSERT INTO t1 values (1),(1);
398
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 NOT NULL default '0', PRIMARY KEY  (a));
 
466
CREATE TABLE t2 (a int 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 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 1235
468
 
--echo #select 10.5 IN (SELECT * from t1 LIMIT 1);
469
 
--echo #-- error 1235
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 1235
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
 
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
498
510
CREATE TABLE t1 (a int);
499
511
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
500
512
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
501
513
drop table t1;
502
514
 
503
515
 
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',
 
516
CREATE TABLE `t1` (
 
517
  `mot` varchar(30) character set latin1 NOT NULL default '',
 
518
  `topic` bigint unsigned NOT NULL default '0',
 
519
  `date` date NOT NULL default '0000-00-00',
 
520
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
 
521
  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
 
522
  KEY `pseudo` (`pseudo`,`date`,`topic`),
 
523
  KEY `topic` (`topic`)
 
524
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
525
 
 
526
CREATE TABLE `t2` (
 
527
  `mot` varchar(30) character set latin1 NOT NULL default '',
 
528
  `topic` bigint unsigned NOT NULL default '0',
 
529
  `date` date NOT NULL default '0000-00-00',
 
530
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
 
531
  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
 
532
  KEY `pseudo` (`pseudo`,`date`,`topic`),
 
533
  KEY `topic` (`topic`)
 
534
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
535
 
 
536
CREATE TABLE `t3` (
 
537
  `numeropost` bigint unsigned NOT NULL auto_increment,
 
538
  `maxnumrep` int unsigned NOT NULL default '0',
527
539
  PRIMARY KEY  (`numeropost`),
528
540
  UNIQUE KEY `maxnumrep` (`maxnumrep`)
529
 
) ENGINE=MyISAM;
 
541
) ENGINE=MyISAM CHARSET=latin1;
530
542
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
531
543
 
532
544
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
543
555
drop table t1, t2, t3;
544
556
 
545
557
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;
 
558
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
 
559
SHOW CREATE TABLE t1;
 
560
drop table t1;
 
561
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
 
562
SHOW CREATE TABLE t1;
 
563
drop table t1;
 
564
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
 
565
SHOW CREATE TABLE t1;
 
566
drop table t1;
 
567
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
556
568
select * from t1;
557
569
SHOW CREATE TABLE t1;
558
570
drop table t1;
559
571
 
560
572
create table t1 (a int);
561
573
insert into t1 values (1), (2), (3);
562
 
--replace_column 9 #
563
574
explain extended select a,(select (select rand() from t1 limit 1)  from t1 limit 1)
564
575
from t1;
565
576
drop table t1;
566
577
 
567
 
--echo #
568
 
--echo # error in IN
569
 
--echo #
 
578
#
 
579
# error in IN
 
580
#
570
581
-- error 1146
571
582
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
583
 
573
 
--echo #
574
 
--echo # complex subquery
575
 
--echo #
 
584
#
 
585
# complex subquery
 
586
#
576
587
 
577
588
CREATE TABLE t1 (
578
589
  ID int NOT NULL auto_increment,
581
592
  District char(20) NOT NULL default '',
582
593
  Population int NOT NULL default '0',
583
594
  PRIMARY KEY  (ID)
584
 
);
 
595
) ENGINE=MyISAM;
585
596
 
586
597
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
587
598
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
593
604
  Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
594
605
  Region char(26) NOT NULL default '',
595
606
  SurfaceArea float(10,2) NOT NULL default '0.00',
596
 
  IndepYear int default NULL,
 
607
  IndepYear smallint default NULL,
597
608
  Population int NOT NULL default '0',
598
609
  LifeExpectancy float(3,1) default NULL,
599
610
  GNP float(10,2) default NULL,
604
615
  Capital int default NULL,
605
616
  Code2 char(2) NOT NULL default '',
606
617
  PRIMARY KEY  (Code)
607
 
);
 
618
) ENGINE=MyISAM;
608
619
 
609
620
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
621
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
624
 
614
625
drop table t1, t2;
615
626
 
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 '',
 
627
#
 
628
# constants in IN
 
629
#
 
630
CREATE TABLE `t1` (
 
631
  `id` bigint unsigned NOT NULL auto_increment,
 
632
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
622
633
  PRIMARY KEY  (`id`),
623
634
  UNIQUE KEY `pseudo` (`pseudo`)
624
 
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
635
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
625
636
INSERT INTO t1 (pseudo) VALUES ('test');
626
637
SELECT 0 IN (SELECT 1 FROM t1 a);
627
638
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
630
641
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
631
642
drop table t1;
632
643
 
633
 
CREATE TEMPORARY TABLE `t1` (
 
644
CREATE TABLE `t1` (
634
645
  `i` int NOT NULL default '0',
635
646
  PRIMARY KEY  (`i`)
636
 
) ENGINE=MyISAM;
 
647
) ENGINE=MyISAM CHARSET=latin1;
637
648
 
638
649
INSERT INTO t1 VALUES (1);
639
650
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
643
654
select * from t1;
644
655
drop table t1;
645
656
 
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; 
 
657
#
 
658
# Multi update test
 
659
#
 
660
CREATE TABLE t1 (
 
661
  id int default NULL
 
662
) ENGINE=MyISAM CHARSET=latin1;
 
663
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
 
664
CREATE TABLE t2 (
 
665
  id int default NULL,
 
666
  name varchar(15) default NULL
 
667
) ENGINE=MyISAM CHARSET=latin1;
 
668
 
 
669
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
 
670
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
 
671
select * from t2;
 
672
drop table t1,t2;
 
673
 
 
674
#
 
675
# correct NULL in <CONSTANT> IN (SELECT ...)
 
676
#
 
677
create table t1 (a int, unique index indexa (a)); 
650
678
insert into t1 values (-1), (-4), (-2), (NULL); 
651
679
select -10 IN (select a from t1 FORCE INDEX (indexa)); 
652
680
drop table t1;
653
681
 
654
 
--echo #
655
 
--echo # Test optimization for sub selects
656
 
--echo #
 
682
#
 
683
# Test optimization for sub selects
 
684
#
657
685
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
658
686
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
659
 
--replace_column 9 #
660
687
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
661
688
drop table t1;
662
689
 
663
690
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',
 
691
  ID int unsigned NOT NULL auto_increment,
 
692
  SUB_ID int unsigned NOT NULL default '0',
 
693
  REF_ID int unsigned default NULL,
 
694
  REF_SUB int unsigned default '0',
668
695
  PRIMARY KEY (ID,SUB_ID),
669
696
  UNIQUE KEY t1_PK (ID,SUB_ID),
670
697
  KEY t1_FK (REF_ID,REF_SUB),
671
698
  KEY t1_REFID (REF_ID)
672
 
);
 
699
) ENGINE=MyISAM CHARSET=cp1251;
673
700
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
674
701
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
675
702
DROP TABLE t1;
676
703
 
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;
 
704
#
 
705
# uninterruptable update
 
706
#
 
707
create table t1 (a int, b int);
 
708
create table t2 (a int, b int);
682
709
 
683
710
insert into t1 values (1,0), (2,0), (3,0);
684
711
insert into t2 values (1,1), (2,1), (3,1), (2,2);
688
715
 
689
716
drop table t1, t2;
690
717
 
691
 
--echo #
692
 
--echo # reduced subselect in ORDER BY & GROUP BY clauses
693
 
--echo #
 
718
#
 
719
# reduced subselect in ORDER BY & GROUP BY clauses
 
720
#
694
721
 
695
 
CREATE TEMPORARY TABLE `t1` (
696
 
  `id` bigint NOT NULL auto_increment,
 
722
CREATE TABLE `t1` (
 
723
  `id` bigint unsigned NOT NULL auto_increment,
697
724
  `pseudo` varchar(35) NOT NULL default '',
698
725
  `email` varchar(60) NOT NULL default '',
699
726
  PRIMARY KEY  (`id`),
700
727
  UNIQUE KEY `email` (`email`),
701
728
  UNIQUE KEY `pseudo` (`pseudo`)
702
 
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
729
) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
703
730
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
704
731
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
705
732
drop table if exists t1;
706
733
 
707
734
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
708
735
 
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;
 
736
#
 
737
# IN subselect optimization test
 
738
#
 
739
create table t1 (a int not null, b int, primary key (a));
 
740
create table t2 (a int not null, primary key (a));
 
741
create table t3 (a int not null, b int, primary key (a));
715
742
insert into t1 values (1,10), (2,20), (3,30),  (4,40);
716
743
insert into t2 values (2), (3), (4), (5);
717
744
insert into t3 values (10,3), (20,4), (30,5);
722
749
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
723
750
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
724
751
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;
 
752
create table t1 (a int, b int, index a (a,b));
 
753
create table t2 (a int, index a (a));
 
754
create table t3 (a int, b int, index a (a));
728
755
insert into t1 values (1,10), (2,20), (3,30), (4,40);
729
756
disable_query_log;
730
 
--echo # making table large enough
 
757
# making table large enough
731
758
set autocommit=0;
732
759
begin;
733
760
let $1 = 10000;
753
780
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
754
781
drop table t1, t2, t3;
755
782
 
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;
 
783
#
 
784
# alloc_group_fields() working
 
785
#
 
786
create table t1 (a int, b int);
 
787
create table t2 (a int, b int);
 
788
create table t3 (a int, b int);
762
789
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
763
790
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
764
791
insert into t3 values (3,3), (2,2), (1,1); 
765
792
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
793
drop table t1,t2,t3;
767
794
 
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;
 
795
#
 
796
# aggregate functions in HAVING test
 
797
#
 
798
create table t1 (s1 int);
 
799
create table t2 (s1 int);
773
800
insert into t1 values (1);
774
801
insert into t2 values (1);
775
802
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
776
803
drop table t1,t2;
777
804
 
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;
 
805
#
 
806
# update subquery with wrong field (to force name resolving
 
807
# in UPDATE name space)
 
808
#
 
809
create table t1 (s1 int);
 
810
create table t2 (s1 int);
784
811
insert into t1 values (1);
785
812
insert into t2 values (1);
786
813
-- error 1054
787
814
update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
788
815
DROP TABLE t1, t2;
789
816
 
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 1267
797
 
--echo #select * from t1 where s1 > (select max(s2) from t1);
798
 
--echo #-- error 1267
799
 
--echo #select * from t1 where s1 > any (select max(s2) from t1);
800
 
--echo #drop table t1;
 
817
#
 
818
# collation test
 
819
#
 
820
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
 
821
                 s2 CHAR(5) COLLATE latin1_swedish_ci);
 
822
INSERT INTO t1 VALUES ('z','?');
 
823
-- error 1267
 
824
select * from t1 where s1 > (select max(s2) from t1);
 
825
-- error 1267
 
826
select * from t1 where s1 > any (select max(s2) from t1);
 
827
drop table t1;
801
828
 
802
 
--echo #
803
 
--echo # aggregate functions reinitialization
804
 
--echo #
 
829
#
 
830
# aggregate functions reinitialization
 
831
#
805
832
create table t1(toid int,rd int);
806
833
create table t2(userid int,pmnew int,pmtotal int);
807
834
insert into t2 values(1,0,0),(2,0,0);
809
836
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
837
drop table t1, t2;
811
838
 
812
 
--echo #
813
 
--echo # row union
814
 
--echo #
 
839
#
 
840
# row union
 
841
#
815
842
create table t1 (s1 char(5));
816
843
-- error 1241
817
844
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
818
845
insert into t1 values ('tttt');
819
846
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
820
 
--replace_column 9 #
821
847
explain extended (select * from t1);
822
848
(select * from t1);
823
849
drop table t1;
824
850
 
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;
 
851
#
 
852
# IN optimisation test results
 
853
#
 
854
create table t1 (s1 char(5), index s1(s1));
 
855
create table t2 (s1 char(5), index s1(s1));
830
856
insert into t1 values ('a1'),('a2'),('a3');
831
857
insert into t2 values ('a1'),('a2');
832
858
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
839
865
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
840
866
drop table t1,t2;
841
867
 
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;
 
868
#
 
869
# correct ALL optimisation
 
870
#
 
871
create table t2 (a int, b int);
 
872
create table t3 (a int);
847
873
insert into t3 values (6),(7),(3);
848
874
select * from t3 where a >= all (select b from t2);
849
875
explain extended select * from t3 where a >= all (select b from t2);
861
887
explain extended select * from t3 where NULL >= some (select b from t2);
862
888
select * from t3 where NULL >= some (select b from t2 group by 1);
863
889
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 #
 
890
#
 
891
# optimized static ALL/ANY with grouping
 
892
#
867
893
insert into t2 values (2,2), (2,1), (3,3), (3,1);
868
894
select * from t3 where a > all (select max(b) from t2 group by a);
869
895
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
870
896
drop table t2, t3;
871
897
 
872
 
--echo #
873
 
--echo # correct used_tables()
874
 
--echo #
 
898
#
 
899
# correct used_tables()
 
900
#
875
901
 
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 ;
 
902
CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int 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
903
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;
 
904
CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint NOT NULL default '0',`secondary_uid` smallint NOT NULL default '0',PRIMARY KEY  (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
879
905
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 ;
 
906
CREATE TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` tinyint NOT NULL default '1',`tues` tinyint NOT NULL default '1',`wed` tinyint NOT NULL default '1',`thur` tinyint NOT NULL default '1',`fri` tinyint NOT NULL default '1',`sat` tinyint NOT NULL default '0',`sun` tinyint NOT NULL default '0',`how_often` smallint NOT NULL default '1',`userid` smallint NOT NULL default '0',`active` tinyint NOT NULL default '1',PRIMARY KEY  (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
881
907
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;
 
908
CREATE TABLE `t4` (`task_id` smallint NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
883
909
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
884
910
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
911
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
912
drop table t1,t2,t3,t4;
887
913
 
888
 
--echo #
889
 
--echo # cardinality check
890
 
--echo #
891
 
CREATE TEMPORARY TABLE t1 (id int default NULL) ENGINE=MyISAM;
 
914
#
 
915
# cardinality check
 
916
#
 
917
CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM CHARSET=latin1;
892
918
INSERT INTO t1 VALUES (1),(5);
893
 
CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;
 
919
CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM CHARSET=latin1;
894
920
INSERT INTO t2 VALUES (2),(6);
895
921
-- error 1241
896
922
select * from t1 where (1,2,6) in (select * from t2);
897
923
DROP TABLE t1,t2;
898
924
 
899
 
--echo #
900
 
--echo # optimized ALL/ANY with union
901
 
--echo #
 
925
#
 
926
# optimized ALL/ANY with union
 
927
#
902
928
create table t1 (s1 char);
903
929
insert into t1 values ('e');
904
930
select * from t1 where 'f' > any (select s1 from t1);
905
931
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
906
 
--replace_column 9 #
907
932
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
908
933
drop table t1;
909
934
 
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;
 
935
#
 
936
# filesort in subquery (restoring join_tab)
 
937
#
 
938
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
914
939
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;
 
940
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
916
941
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
917
942
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
943
drop table t1, t2;
919
944
 
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;
 
945
#
 
946
# unresolved field error
 
947
#
 
948
create table t1 (s1 int); 
 
949
create table t2 (s1 int);
925
950
-- error 1054
926
951
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
927
952
-- error 1054
930
955
select count(*) from t2 group by t1.s2;
931
956
drop table t1, t2;
932
957
 
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;
 
958
#
 
959
# fix_fields() in add_ref_to_table_cond()
 
960
#
 
961
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
 
962
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
938
963
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
939
964
INSERT INTO t2 VALUES (100, 200, 'C');
940
965
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
941
966
DROP TABLE t1, t2;
942
967
 
943
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
968
CREATE TABLE t1 (a int);
944
969
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
945
970
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
946
971
DROP TABLE t1;
947
972
 
948
 
--echo #
949
 
--echo # Bug 2479
950
 
--echo #
 
973
#
 
974
# Bug 2479
 
975
#
951
976
 
952
 
CREATE TEMPORARY TABLE `t1` (
 
977
CREATE TABLE `t1` (
953
978
  `id` int NOT NULL auto_increment,
954
 
  `id_cns` int NOT NULL default '0',
 
979
  `id_cns` tinyint unsigned NOT NULL default '0',
955
980
  `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',
 
981
  `anno_dep` smallint unsigned NOT NULL default '0',
 
982
  `particolare` bigint unsigned NOT NULL default '0',
 
983
  `generale` bigint unsigned NOT NULL default '0',
 
984
  `bis` tinyint unsigned NOT NULL default '0',
960
985
  PRIMARY KEY  (`id`),
961
986
  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;
 
987
  UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
 
988
);
964
989
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;
 
990
CREATE TABLE `t2` (
 
991
  `id` tinyint unsigned NOT NULL auto_increment,
 
992
  `max_anno_dep` smallint unsigned NOT NULL default '0',
 
993
  PRIMARY KEY  (`id`)
 
994
);
969
995
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
970
996
 
971
997
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
998
 
973
999
DROP TABLE t1, t2;
974
1000
 
975
 
--echo #
976
 
--echo # GLOBAL LIMIT
977
 
--echo #
978
 
create temporary table t1 (a int) ENGINE=MyISAM;
 
1001
#
 
1002
# GLOBAL LIMIT
 
1003
#
 
1004
create table t1 (a int);
979
1005
insert into t1 values (1), (2), (3);
980
1006
SET SQL_SELECT_LIMIT=1;
981
1007
select sum(a) from (select * from t1) as a;
983
1009
SET SQL_SELECT_LIMIT=default;
984
1010
drop table t1;
985
1011
 
986
 
--echo #
987
 
--echo # Bug #3118: subselect + order by
988
 
--echo #
 
1012
#
 
1013
# Bug #3118: subselect + order by
 
1014
#
989
1015
 
990
1016
CREATE TABLE t1 (a int, b int, INDEX (a));
991
1017
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
992
1018
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
993
1019
DROP TABLE t1;
994
1020
 
995
 
--echo # Item_cond fix field
996
 
--echo #
 
1021
# Item_cond fix field
 
1022
#
997
1023
create table t1(val varchar(10));
998
1024
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
999
1025
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
1026
drop table t1;
1001
1027
 
1002
 
--echo #
1003
 
--echo # ref_or_null replacing with ref
1004
 
--echo #
 
1028
#
 
1029
# ref_or_null replacing with ref
 
1030
#
1005
1031
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1006
1032
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
1033
select * from t1 where id not in (select id from t1 where id < 8);
1008
1034
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
1035
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1011
 
--replace_column 9 #
1012
1036
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
1037
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;
 
1038
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
1015
1039
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
1040
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
1041
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
1042
drop table t1,t2;
1020
1043
 
1021
 
--echo #
1022
 
--echo # Static tables & rund() in subqueries
1023
 
--echo #
1024
 
create temporary table t1 (a int) ENGINE=MyISAM;
 
1044
#
 
1045
# Static tables & rund() in subqueries
 
1046
#
 
1047
create table t1 (a int);
1025
1048
insert into t1 values (1);
1026
1049
explain select benchmark(1000, (select a from t1 where a=rand()));
1027
1050
drop table t1;
1028
1051
 
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;
 
1052
#
 
1053
# bug 3188
 
1054
#
 
1055
create table t1(id int);
 
1056
create table t2(id int);
 
1057
create table t3(flag int);
1035
1058
-- error 1064
1036
1059
select (select * from t3 where id not null) from t1, t2;
1037
1060
drop table t1,t2,t3;
1038
1061
 
1039
 
--echo #
1040
 
--echo # aggregate functions (Bug #3505)
1041
 
--echo #
 
1062
#
 
1063
# aggregate functions (Bug #3505)
 
1064
#
1042
1065
CREATE TABLE t1 (id INT);
1043
1066
CREATE TABLE t2 (id INT);
1044
1067
INSERT INTO t1 VALUES (1), (2);
1049
1072
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
1073
DROP TABLE t1,t2;
1051
1074
 
1052
 
--echo #
1053
 
--echo # ALL/ANY test
1054
 
--echo #
 
1075
#
 
1076
# ALL/ANY test
 
1077
#
1055
1078
CREATE TABLE t1 ( a int, b int );
1056
1079
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1057
1080
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1066
1089
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1067
1090
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1068
1091
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1069
 
--echo # with index
 
1092
# with index
1070
1093
ALTER TABLE t1 ADD INDEX (a);
1071
1094
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1072
1095
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1080
1103
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1081
1104
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1082
1105
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1083
 
--echo # having clause test
 
1106
# having clause test
1084
1107
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1085
1108
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
1086
1109
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
1093
1116
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
1094
1117
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
1095
1118
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
1096
 
--echo # union test
 
1119
# union test
1097
1120
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1098
1121
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1099
1122
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1106
1129
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1107
1130
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1108
1131
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
 
1132
# union + having test
1110
1133
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1111
1134
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1112
1135
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1119
1142
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1120
1143
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1121
1144
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
 
1145
# row tests
 
1146
# < > >= <= and = ALL/ <> ANY do not support row operation
1124
1147
-- error 1241
1125
1148
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1126
1149
-- error 1241
1137
1160
SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
1138
1161
-- error 1241
1139
1162
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
1140
 
--echo # following should be converted to IN
 
1163
# following should be converted to IN
1141
1164
-- error 1241
1142
1165
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
1143
1166
-- error 1241
1156
1179
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
1180
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
1181
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
 
1182
# without optimisation
1160
1183
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1161
1184
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1162
1185
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1169
1192
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1170
1193
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1171
1194
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1172
 
--echo # without optimisation + having
 
1195
# without optimisation + having
1173
1196
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
1174
1197
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
1175
1198
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
1182
1205
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
1183
1206
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
1184
1207
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
1185
 
--echo # EXISTS in string contence
 
1208
# EXISTS in string contence
1186
1209
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
1187
1210
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
1188
1211
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
1219
1242
DROP TABLE t1;
1220
1243
 
1221
1244
 
1222
 
--echo #
1223
 
--echo # SELECT(EXISTS * ...)optimisation
1224
 
--echo #
 
1245
#
 
1246
# SELECT(EXISTS * ...)optimisation
 
1247
#
1225
1248
create table t1 (a int, b int);
1226
1249
insert into t1 values (1,2),(3,4);
1227
1250
select * from t1 up where exists (select * from t1 where t1.a=up.a);
1228
 
--replace_column 9 #
1229
1251
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
1230
1252
drop table t1;
1231
1253
 
1232
 
--echo #
1233
 
--echo # Bug #4102: subselect in HAVING
1234
 
--echo #
 
1254
#
 
1255
# Bug #4102: subselect in HAVING
 
1256
#
1235
1257
 
1236
 
CREATE TEMPORARY TABLE t1 (t1_a int) ENGINE=MyISAM;
 
1258
CREATE TABLE t1 (t1_a int);
1237
1259
INSERT INTO t1 VALUES (1);
1238
1260
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
1239
1261
INSERT INTO t2 VALUES (1, 1), (1, 2);
1241
1263
  HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
1242
1264
DROP TABLE t1, t2;
1243
1265
 
1244
 
--echo #
1245
 
--echo # Test problem with NULL and derived tables (Bug #4097)
1246
 
--echo #
 
1266
#
 
1267
# Test problem with NULL and derived tables (Bug #4097)
 
1268
#
1247
1269
 
1248
 
CREATE TEMPORARY TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
 
1270
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL);
1249
1271
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;
 
1272
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL);
1251
1273
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
1252
1274
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
1253
1275
drop table t1,t2;
1254
1276
 
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;
 
1277
#
 
1278
# Aggregate function comparation with ALL/ANY/SOME subselect
 
1279
#
 
1280
CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1259
1281
insert into t1 values (1);
1260
 
CREATE TEMPORARY TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
 
1282
CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1261
1283
insert into t2 values (1,2);
1262
1284
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
1285
drop table t1,t2;
1264
1286
 
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);
 
1287
#
 
1288
# BUG#5003 - like in subselect
 
1289
#
 
1290
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
1291
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
1270
1292
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
1271
1293
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
1274
1296
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
1275
1297
drop table t1;
1276
1298
 
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;
 
1299
#
 
1300
# Optimized IN with compound index
 
1301
#
 
1302
CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
 
1303
CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
1282
1304
insert into t1 values (1,1),(1,2),(2,1),(2,2);
1283
1305
insert into t2 values (1,2),(2,2);
1284
1306
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1290
1312
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1291
1313
drop table t1,t2;
1292
1314
 
1293
 
--echo #
1294
 
--echo # resolving fields of grouped outer SELECT
1295
 
--echo #
 
1315
#
 
1316
# resolving fields of grouped outer SELECT
 
1317
#
1296
1318
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
1297
1319
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
1320
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
1307
1329
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
1330
drop table t1;
1309
1331
 
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;
 
1332
#
 
1333
# Test of correct maybe_null flag returning by subquwery for temporary table
 
1334
# creation
 
1335
#
 
1336
CREATE TABLE `t1` ( `master` int unsigned NOT NULL default '0', `map` smallint unsigned NOT NULL default '0', `slave` int unsigned NOT NULL default '0', `access` int unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
1315
1337
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 ;
 
1338
CREATE TABLE `t2` ( `id` int unsigned NOT NULL default '0', `pid` int unsigned NOT NULL default '0', `map` smallint unsigned NOT NULL default '0', `level` tinyint unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY  (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
1317
1339
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
1340
-- error 1054
1319
1341
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
1342
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
1343
drop tables t1,t2;
1322
1344
 
1323
 
--echo #
1324
 
--echo # Subselect in non-select command just after connection
1325
 
--echo #
 
1345
#
 
1346
# Subselect in non-select command just after connection
 
1347
#
1326
1348
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
1327
1349
connection root;
1328
1350
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
1329
1351
 
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;
 
1352
#
 
1353
# primary query with temporary table and subquery with groupping
 
1354
#
 
1355
create table t1 (a int, b int);
 
1356
create table t2 (a int, b int);
1335
1357
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
1336
1358
insert into t2 values (1,3),(2,1);
1337
1359
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
1338
1360
drop table t1, t2;
1339
1361
 
1340
 
--echo #
1341
 
--echo # Equal operation under row and empty subquery
1342
 
--echo #
 
1362
#
 
1363
# Equal operation under row and empty subquery
 
1364
#
1343
1365
create table t1 (s1 int,s2 int);
1344
1366
insert into t1 values (20,15);
1345
1367
select * from t1 where  (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
1346
1368
drop table t1;
1347
1369
 
1348
 
--echo #
1349
 
--echo # ALL/ANY with NULL
1350
 
--echo #
 
1370
#
 
1371
# ALL/ANY with NULL
 
1372
#
1351
1373
create table t1 (s1 int);
1352
1374
insert into t1 values (1),(null);
1353
1375
select * from t1 where s1 < all (select s1 from t1);
1354
1376
select s1, s1 < all (select s1 from t1) from t1;
1355
1377
drop table t1;
1356
1378
 
1357
 
--echo #
1358
 
--echo # reference on changable fields from subquery
1359
 
--echo #
 
1379
#
 
1380
# reference on changable fields from subquery
 
1381
#
1360
1382
CREATE TABLE t1 (
1361
1383
  Code char(3) NOT NULL default '',
1362
1384
  Name char(52) NOT NULL default '',
1363
1385
  Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1364
1386
  Region char(26) NOT NULL default '',
1365
1387
  SurfaceArea float(10,2) NOT NULL default '0.00',
1366
 
  IndepYear int default NULL,
 
1388
  IndepYear smallint default NULL,
1367
1389
  Population int NOT NULL default '0',
1368
1390
  LifeExpectancy float(3,1) default NULL,
1369
1391
  GNP float(10,2) default NULL,
1373
1395
  HeadOfState char(60) default NULL,
1374
1396
  Capital int default NULL,
1375
1397
  Code2 char(2) NOT NULL default ''
1376
 
);
 
1398
) ENGINE=MyISAM;
1377
1399
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
1378
1400
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
1401
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
1406
  Population < 200);
1385
1407
drop table t1;
1386
1408
 
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
 
1409
#
 
1410
# Test for BUG#7885: Server crash when 'any' subselect compared to
 
1411
# non-existant field.
 
1412
#
 
1413
create table t1 (a1 int);
 
1414
create table t2 (b1 int);
 
1415
--error 1054
1394
1416
select * from t1 where a2 > any(select b1 from t2);
1395
1417
select * from t1 where a1 > any(select b1 from t2);
1396
1418
drop table t1,t2;
1397
1419
 
1398
1420
 
1399
 
--echo #
1400
 
--echo # Comparison subquery with * and row
1401
 
--echo #
1402
 
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
 
1421
#
 
1422
# Comparison subquery with * and row
 
1423
#
 
1424
create table t1 (a integer, b integer);
1403
1425
select (select * from t1) = (select 1,2);
1404
1426
select (select 1,2) = (select * from t1);
1405
 
--echo # queries whih can be converted to IN
 
1427
# queries whih can be converted to IN
1406
1428
select  row(1,2) = ANY (select * from t1);
1407
1429
select  row(1,2) != ALL (select * from t1);
1408
1430
drop table t1;
1409
1431
 
1410
 
--echo #
1411
 
--echo # Comparison subquery and row with nested rows
1412
 
--echo #
1413
 
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
 
1432
#
 
1433
# Comparison subquery and row with nested rows
 
1434
#
 
1435
create table t1 (a integer, b integer);
1414
1436
-- error 1241
1415
1437
select row(1,(2,2)) in (select * from t1 );
1416
1438
-- error 1241
1419
1441
select (select * from t1) = row(1,(2,2));
1420
1442
drop table t1;
1421
1443
 
1422
 
--echo #
1423
 
--echo # Forward reference detection
1424
 
--echo #
 
1444
#
 
1445
# Forward reference detection
 
1446
#
1425
1447
create  table t1 (a integer);
1426
1448
insert into t1 values (1);
1427
1449
-- error 1247
1433
1455
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
1434
1456
drop table t1;
1435
1457
 
1436
 
--echo #
1437
 
--echo # Test for BUG#8218
1438
 
--echo #
1439
 
CREATE TEMPORARY TABLE t1 (
 
1458
#
 
1459
# Test for BUG#8218
 
1460
#
 
1461
CREATE TABLE t1 (
1440
1462
  categoryId int NOT NULL,
1441
1463
  courseId int NOT NULL,
1442
1464
  startDate datetime NOT NULL,
1443
1465
  endDate datetime NOT NULL,
1444
1466
  createDate datetime NOT NULL,
1445
1467
  modifyDate timestamp NOT NULL,
1446
 
  attributes text NOT NULL)
1447
 
  engine=myisam;
1448
 
 
 
1468
  attributes text NOT NULL
 
1469
);
1449
1470
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
1450
1471
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1451
1472
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1456
1477
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
1457
1478
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
1458
1479
 
1459
 
CREATE TEMPORARY TABLE t2 (
 
1480
CREATE TABLE t2 (
1460
1481
  userId int NOT NULL,
1461
1482
  courseId int NOT NULL,
1462
 
  date datetime NOT NULL)
1463
 
  engine=myisam;
1464
 
 
 
1483
  date datetime NOT NULL
 
1484
);
1465
1485
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
1466
1486
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
1467
1487
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
1470
1490
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
1471
1491
 
1472
1492
 
1473
 
CREATE TEMPORARY TABLE t3 (
 
1493
CREATE TABLE t3 (
1474
1494
  groupId int NOT NULL,
1475
1495
  parentId int NOT NULL,
1476
1496
  startDate datetime NOT NULL,
1477
1497
  endDate datetime NOT NULL,
1478
1498
  createDate datetime NOT NULL,
1479
1499
  modifyDate timestamp NOT NULL,
1480
 
  ordering int)
1481
 
  engine=myisam;
1482
 
 
 
1500
  ordering int
 
1501
);
1483
1502
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
1484
1503
 
1485
 
CREATE TEMPORARY TABLE t4 (
 
1504
CREATE TABLE t4 (
1486
1505
  id int NOT NULL,
1487
1506
  groupTypeId int NOT NULL,
1488
1507
  groupKey varchar(50) NOT NULL,
1490
1509
  ordering int,
1491
1510
  description text,
1492
1511
  createDate datetime NOT NULL,
1493
 
  modifyDate timestamp NOT NULL)
1494
 
  engine=myisam;
1495
 
 
 
1512
  modifyDate timestamp NOT NULL
 
1513
);
1496
1514
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
1497
1515
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
1498
1516
 
1499
 
CREATE TEMPORARY TABLE t5 (
 
1517
CREATE TABLE t5 (
1500
1518
  userId int NOT NULL,
1501
1519
  groupId int NOT NULL,
1502
1520
  createDate datetime NOT NULL,
1503
 
  modifyDate timestamp NOT NULL) ENGINE=MyISAM;
1504
 
 
 
1521
  modifyDate timestamp NOT NULL
 
1522
);
1505
1523
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
1506
1524
 
1507
1525
select
1538
1556
 
1539
1557
drop table t1, t2, t3, t4, t5;
1540
1558
 
1541
 
--echo #
1542
 
--echo # Transformation in left expression of subquery (BUG#8888)
1543
 
--echo #
1544
 
create temporary table t1 (a int) ENGINE=MyISAM;
 
1559
#
 
1560
# Transformation in left expression of subquery (BUG#8888)
 
1561
#
 
1562
create table t1 (a int);
1545
1563
insert into t1 values (1), (2), (3);
1546
1564
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
1547
1565
drop table t1;
1548
1566
 
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;
 
1567
#
 
1568
# single row subqueries and row operations (code covarage improvement)
 
1569
#
 
1570
create table t1 (a int, b int);
1553
1571
insert into t1 values (1,2);
1554
1572
-- error 1241
1555
1573
select 1 = (select * from t1);
1565
1583
select (select * from t1) = (1,2,3);
1566
1584
drop table t1;
1567
1585
 
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;
 
1586
#
 
1587
# Item_int_with_ref check (BUG#10020)
 
1588
#
 
1589
CREATE TABLE `t1` (
 
1590
  `itemid` bigint unsigned NOT NULL auto_increment,
 
1591
  `sessionid` bigint unsigned default NULL,
 
1592
  `time` int unsigned NOT NULL default '0',
 
1593
  `data` text collate latin1_general_ci NOT NULL,
 
1594
  PRIMARY KEY  (`itemid`)
 
1595
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
1596
INSERT INTO `t1` VALUES (1, 1, 1, '');
 
1597
CREATE TABLE `t2` (
 
1598
  `sessionid` bigint unsigned NOT NULL auto_increment,
 
1599
  `pid` int unsigned NOT NULL default '0',
 
1600
  `date` int unsigned NOT NULL default '0',
 
1601
  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
 
1602
  PRIMARY KEY  (`sessionid`)
 
1603
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
1604
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
 
1605
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;
 
1606
drop tables t1,t2;
1589
1607
 
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;
 
1608
# BUG#11821 : Select from subselect using aggregate function on an enum
 
1609
# segfaults:
 
1610
create table t1 (fld enum('0','1'));
1593
1611
insert into t1 values ('1');
1594
1612
select * from (select max(fld) from t1) as foo;
1595
1613
drop table t1;
1596
1614
 
1597
 
--echo #
1598
 
--echo # Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
1599
 
--echo #
 
1615
#
 
1616
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
 
1617
#
1600
1618
 
1601
1619
CREATE TABLE t1 (one int, two int, flag char(1));
1602
1620
CREATE TABLE t2 (one int, two int, flag char(1));
1625
1643
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
1644
DROP TABLE t1,t2;
1627
1645
 
1628
 
--echo #
1629
 
--echo # Bug #12392: where cond with IN predicate for rows and NULL values in table 
1630
 
--echo #
 
1646
#
 
1647
# Bug #12392: where cond with IN predicate for rows and NULL values in table 
 
1648
#
1631
1649
 
1632
 
CREATE TEMPORARY TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
 
1650
CREATE TABLE t1 (a char(5), b char(5));
1633
1651
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
1634
1652
 
1635
1653
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
1636
1654
 
1637
1655
DROP TABLE t1;
1638
1656
 
1639
 
--echo #
1640
 
--echo # Bug #11479: subquery over left join with an empty inner table 
1641
 
--echo #
 
1657
#
 
1658
# Bug #11479: subquery over left join with an empty inner table 
 
1659
#
1642
1660
 
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;
 
1661
CREATE TABLE t1 (a int);
 
1662
CREATE TABLE t2 (a int, b int);
 
1663
CREATE TABLE t3 (b int NOT NULL);
1646
1664
INSERT INTO t1 VALUES (1), (2), (3), (4);
1647
1665
INSERT INTO t2 VALUES (1,10), (3,30);
1648
1666
 
1654
1672
 
1655
1673
DROP TABLE t1,t2,t3;
1656
1674
 
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;
 
1675
#
 
1676
# Bug#18503: Queries with a quantified subquery returning empty set may
 
1677
# return a wrong result. 
 
1678
#
 
1679
CREATE TABLE t1 (f1 INT);
 
1680
CREATE TABLE t2 (f2 INT);
1663
1681
INSERT INTO t1 VALUES (1);
1664
1682
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
1665
1683
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
1668
1686
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
1669
1687
DROP TABLE t1, t2;
1670
1688
 
1671
 
--echo # BUG#20975 Wrong query results for subqueries within NOT
 
1689
# BUG#20975 Wrong query results for subqueries within NOT
1672
1690
create table t1 (s1 char);
1673
1691
insert into t1 values (1),(2);
1674
1692
 
1685
1703
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
1686
1704
drop table t1;
1687
1705
 
1688
 
--echo #
1689
 
--echo # Bug #16255: Subquery in where
1690
 
--echo #
 
1706
#
 
1707
# Bug #16255: Subquery in where
 
1708
#
1691
1709
create table t1 (
1692
1710
  retailerID varchar(8) NOT NULL,
1693
 
  statusID   int NOT NULL,
 
1711
  statusID   int unsigned NOT NULL,
1694
1712
  changed    datetime NOT NULL,
1695
 
  UNIQUE KEY retailerID (retailerID, statusID, changed));
 
1713
  UNIQUE KEY retailerID (retailerID, statusID, changed)
 
1714
);
1696
1715
 
1697
1716
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
1698
1717
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
1707
1726
          group by r2.retailerId);
1708
1727
drop table t1;
1709
1728
 
1710
 
--echo #
1711
 
--echo # Bug #21180: Subselect with index for both WHERE and ORDER BY 
1712
 
--echo #             produces empty result
1713
 
--echo #
 
1729
#
 
1730
# Bug #21180: Subselect with index for both WHERE and ORDER BY 
 
1731
#             produces empty result
 
1732
#
1714
1733
create table t1(a int, primary key (a));
1715
1734
insert into t1 values (10);
1716
1735
 
1717
1736
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
1718
1737
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
1719
1738
 
1720
 
--replace_column 9 #
1721
1739
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
1722
1740
  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
1723
1741
             ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
1734
1752
 
1735
1753
drop table t1,t2;
1736
1754
 
1737
 
--echo #
1738
 
--echo # Bug #21853: assert failure for a grouping query with
1739
 
--echo #             an ALL/ANY quantified subquery in HAVING 
1740
 
--echo #
 
1755
#
 
1756
# Bug #21853: assert failure for a grouping query with
 
1757
#             an ALL/ANY quantified subquery in HAVING 
 
1758
#
1741
1759
 
1742
 
CREATE TEMPORARY TABLE t1 (                  
 
1760
CREATE TABLE t1 (                  
1743
1761
  field1 int NOT NULL,                 
1744
1762
  field2 int NOT NULL,                 
1745
1763
  field3 int NOT NULL,                 
1746
 
  PRIMARY KEY  (field1,field2,field3))
1747
 
  ENGINE=MyISAM;
1748
 
 
1749
 
CREATE TEMPORARY TABLE t2 (             
 
1764
  PRIMARY KEY  (field1,field2,field3)  
 
1765
);
 
1766
CREATE TABLE t2 (             
1750
1767
  fieldA int NOT NULL,            
1751
1768
  fieldB int NOT NULL,            
1752
 
  PRIMARY KEY  (fieldA,fieldB))
1753
 
  ENGINE=MyISAM;
 
1769
  PRIMARY KEY  (fieldA,fieldB)     
 
1770
); 
1754
1771
 
1755
1772
INSERT INTO t1 VALUES
1756
1773
  (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
1772
1789
 
1773
1790
DROP TABLE t1, t2;
1774
1791
 
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 #
 
1792
#
 
1793
# Bug #23478: not top-level IN subquery returning a non-empty result set
 
1794
#             with possible NULL values by index access from the outer query
 
1795
#
1779
1796
 
1780
 
CREATE TEMPORARY TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
 
1797
CREATE TABLE t1(a int, INDEX (a));
1781
1798
INSERT INTO t1 VALUES (1), (3), (5), (7);
1782
1799
INSERT INTO t1 VALUES (NULL);
1783
1800
 
1784
 
CREATE TEMPORARY TABLE t2(a int) ENGINE=MyISAM;
 
1801
CREATE TABLE t2(a int);
1785
1802
INSERT INTO t2 VALUES (1),(2),(3);
1786
1803
 
1787
1804
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
1789
1806
 
1790
1807
DROP TABLE t1,t2;
1791
1808
 
1792
 
--echo #
1793
 
--echo # Bug #11302: getObject() returns a String for a sub-query of type datetime
1794
 
--echo #
 
1809
#
 
1810
# Bug #11302: getObject() returns a String for a sub-query of type datetime
 
1811
#
1795
1812
CREATE TABLE t1 (a DATETIME);
1796
1813
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
1797
1814
 
1798
 
CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT 
 
1815
CREATE TABLE t2 AS SELECT 
1799
1816
  (SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a 
1800
1817
   FROM t1 WHERE a > '2000-01-01';
1801
1818
SHOW CREATE TABLE t2;
1802
1819
 
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'); 
 
1820
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01'); 
1804
1821
SHOW CREATE TABLE t3;
1805
1822
 
1806
1823
DROP TABLE t1,t2,t3;
1807
1824
 
1808
 
--echo #
1809
 
--echo # Bug 24653: sorting by expressions containing subselects
1810
 
--echo #            that return more than one row
1811
 
--echo #
 
1825
#
 
1826
# Bug 24653: sorting by expressions containing subselects
 
1827
#            that return more than one row
 
1828
#
1812
1829
 
1813
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
1830
CREATE TABLE t1 (a int);
1814
1831
INSERT INTO t1 VALUES (2), (4), (1), (3);
1815
1832
 
1816
1833
CREATE TABLE t2 (b int, c int);
1818
1835
  (2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
1819
1836
 
1820
1837
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
1821
 
--error ER_SUBQUERY_NO_1_ROW
 
1838
--error 1242
1822
1839
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
1823
1840
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
1824
 
--error ER_SUBQUERY_NO_1_ROW
 
1841
--error 1242
1825
1842
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
1826
1843
 
1827
1844
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
1828
 
--error ER_SUBQUERY_NO_1_ROW
 
1845
--error 1242
1829
1846
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
1830
1847
 
1831
1848
 
1832
1849
SELECT a FROM t1 GROUP BY a
1833
1850
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
1834
1851
                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1835
 
--error ER_SUBQUERY_NO_1_ROW
 
1852
--error 1242
1836
1853
SELECT a FROM t1 GROUP BY a
1837
1854
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
1838
1855
                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1840
1857
SELECT a FROM t1 GROUP BY a
1841
1858
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
1842
1859
                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1843
 
--error ER_SUBQUERY_NO_1_ROW
 
1860
--error 1242
1844
1861
SELECT a FROM t1 GROUP BY a
1845
1862
  HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
1846
1863
                (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
1848
1865
SELECT a FROM t1
1849
1866
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
1850
1867
                  (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
1851
 
--error ER_SUBQUERY_NO_1_ROW
 
1868
--error 1242
1852
1869
SELECT a FROM t1
1853
1870
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
1854
1871
                  (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
1856
1873
SELECT a FROM t1
1857
1874
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
1858
1875
                  (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
1859
 
--error ER_SUBQUERY_NO_1_ROW
 
1876
--error 1242
1860
1877
SELECT a FROM t1
1861
1878
  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
1862
1879
                  (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
1863
1880
 
1864
1881
DROP TABLE t1,t2;
1865
1882
 
1866
 
--echo # End of 4.1 tests
 
1883
# End of 4.1 tests
1867
1884
 
1868
 
--echo #
1869
 
--echo #decimal-related tests
1870
 
--echo #
 
1885
#
 
1886
#decimal-related tests
 
1887
#
1871
1888
create table t1 (df decimal(5,1));
1872
1889
insert into t1 values(1.1);
1873
1890
insert into t1 values(2.2);
1876
1893
select * from t1 where df >= all (select avg(df) from t1 group by df);
1877
1894
drop table t1;
1878
1895
 
1879
 
create temporary table t1 (df decimal(5,1)) ENGINE=MyISAM;
 
1896
create table t1 (df decimal(5,1));
1880
1897
insert into t1 values(1.1);
1881
1898
select 1.1 * exists(select * from t1);
1882
1899
drop table t1;
1883
1900
 
1884
 
CREATE TEMPORARY TABLE t1 (
 
1901
CREATE TABLE t1 (
1885
1902
  grp int default NULL,
1886
 
  a decimal(10,2) default NULL) ENGINE=MyISAM;
 
1903
  a decimal(10,2) default NULL);
1887
1904
 
1888
1905
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
1889
1906
select * from t1;
1890
1907
select min(a) from t1 group by grp;
1891
1908
drop table t1;
1892
1909
 
1893
 
--echo #
1894
 
--echo # Test for bug #9338: lame substitution of c1 instead of c2 
1895
 
--echo #
 
1910
#
 
1911
# Test for bug #9338: lame substitution of c1 instead of c2 
 
1912
#
1896
1913
 
1897
 
CREATE temporary table t1 ( c1 integer ) ENGINE=MyISAM;
 
1914
CREATE table t1 ( c1 integer );
1898
1915
INSERT INTO t1 VALUES ( 1 );
1899
1916
INSERT INTO t1 VALUES ( 2 );
1900
1917
INSERT INTO t1 VALUES ( 3 );
1911
1928
 
1912
1929
DROP TABLE t1,t2;
1913
1930
 
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;
 
1931
#
 
1932
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ 
 
1933
#
 
1934
CREATE TABLE t1 ( c1 integer );
1918
1935
INSERT INTO t1 VALUES ( 1 );
1919
1936
INSERT INTO t1 VALUES ( 2 );
1920
1937
INSERT INTO t1 VALUES ( 3 );
1921
1938
INSERT INTO t1 VALUES ( 6 ); 
1922
1939
 
1923
 
CREATE TEMPORARY TABLE t2 ( c2 integer ) ENGINE=MyISAM;
 
1940
CREATE TABLE t2 ( c2 integer );
1924
1941
INSERT INTO t2 VALUES ( 1 );
1925
1942
INSERT INTO t2 VALUES ( 4 );
1926
1943
INSERT INTO t2 VALUES ( 5 );
1927
1944
INSERT INTO t2 VALUES ( 6 );
1928
1945
 
1929
 
CREATE TEMPORARY TABLE t3 ( c3 integer ) ENGINE=MyISAM;
 
1946
CREATE TABLE t3 ( c3 integer );
1930
1947
INSERT INTO t3 VALUES ( 7 );
1931
1948
INSERT INTO t3 VALUES ( 8 );
1932
1949
 
1935
1952
 
1936
1953
DROP TABLE t1,t2,t3;
1937
1954
 
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;
 
1955
#
 
1956
# Correct building of equal fields list (do not include outer
 
1957
# fields) (BUG#6384)
 
1958
#
 
1959
CREATE TABLE t1 (EMPNUM   CHAR(3));
 
1960
CREATE TABLE t2 (EMPNUM   CHAR(3) );
1944
1961
INSERT INTO t1 VALUES ('E1'),('E2');
1945
1962
INSERT INTO t2 VALUES ('E1');
1946
1963
DELETE FROM t1
1951
1968
select * from t1;
1952
1969
DROP TABLE t1,t2;
1953
1970
 
1954
 
--echo #
1955
 
--echo # Test for bug #11487: range access in a subquery
1956
 
--echo #
 
1971
#
 
1972
# Test for bug #11487: range access in a subquery
 
1973
#
1957
1974
 
1958
 
CREATE TEMPORARY TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
 
1975
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
1959
1976
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;
 
1977
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, 
 
1978
                 PRIMARY KEY(select_id,values_id));
1962
1979
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
1963
1980
 
1964
1981
SELECT values_id FROM t1 
1973
1990
 
1974
1991
DROP TABLE t1, t2;
1975
1992
 
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;
 
1993
# BUG#11821 : Select from subselect using aggregate function on an enum
 
1994
# segfaults:
 
1995
create table t1 (fld enum('0','1'));
1979
1996
insert into t1 values ('1');
1980
1997
select * from (select max(fld) from t1) as foo;
1981
1998
drop table t1;
1982
1999
 
1983
 
--echo #
1984
 
--echo # Test for bug #11762: subquery with an aggregate function in HAVING
1985
 
--echo #
 
2000
#
 
2001
# Test for bug #11762: subquery with an aggregate function in HAVING
 
2002
#
1986
2003
 
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;
 
2004
CREATE TABLE t1 (a int, b int);
 
2005
CREATE TABLE t2 (c int, d int);
 
2006
CREATE TABLE t3 (e int);
1990
2007
 
1991
2008
INSERT INTO t1 VALUES 
1992
2009
  (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
2064
2081
 
2065
2082
DROP TABLE t1,t2,t3;
2066
2083
 
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
 
2084
#
 
2085
# Test for bug #16603: GROUP BY in a row subquery with a quantifier 
 
2086
#                      when an index is defined on the grouping field
2070
2087
 
2071
2088
CREATE TABLE t1 (a varchar(5), b varchar(10));
2072
2089
INSERT INTO t1 VALUES
2074
2091
  ('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
2075
2092
 
2076
2093
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2077
 
--replace_column 9 #
2078
2094
EXPLAIN
2079
2095
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2080
2096
 
2081
2097
ALTER TABLE t1 ADD INDEX(a);
2082
2098
 
2083
2099
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2084
 
--replace_column 9 #
2085
2100
EXPLAIN
2086
2101
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2087
2102
 
2088
2103
DROP TABLE t1;
2089
2104
 
2090
 
--echo #
2091
 
--echo # Bug#17366: Unchecked Item_int results in server crash
2092
 
--echo #
 
2105
#
 
2106
# Bug#17366: Unchecked Item_int results in server crash
 
2107
#
2093
2108
create table t1( f1 int,f2 int);
2094
2109
insert into t1 values (1,1),(2,2);
2095
2110
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
2111
drop table t1;
2097
2112
 
2098
 
--echo #
2099
 
--echo # Bug #18306: server crash on delete using subquery.
2100
 
--echo #
 
2113
#
 
2114
# Bug #18306: server crash on delete using subquery.
 
2115
#
2101
2116
 
2102
 
create temporary table t1 (c int, key(c)) ENGINE=MyISAM;                              
 
2117
create table t1 (c int, key(c));                              
2103
2118
insert into t1 values (1142477582), (1142455969);
2104
 
create temporary table t2 (a int, b int) ENGINE=MyISAM;
 
2119
create table t2 (a int, b int);
2105
2120
insert into t2 values (2, 1), (1, 0);
2106
2121
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
2107
2122
drop table t1, t2;
2108
2123
 
2109
 
--echo #
2110
 
--echo # Bug#19077: A nested materialized derived table is used before being populated.
2111
 
--echo #
 
2124
#
 
2125
# Bug#19077: A nested materialized derived table is used before being populated.
 
2126
#
2112
2127
create table t1 (i int, j bigint);
2113
2128
insert into t1 values (1, 2), (2, 2), (3, 2);
2114
2129
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
2115
2130
drop table t1;
2116
2131
 
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
 
2132
# 
 
2133
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
 
2134
#
 
2135
CREATE TABLE t1 (i BIGINT UNSIGNED);
 
2136
INSERT INTO t1 VALUES (10000000000000000000); # > MAX SIGNED BIGINT 9323372036854775807
2122
2137
INSERT INTO t1 VALUES (1);
2123
2138
 
2124
 
CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;
2125
 
INSERT INTO t2 VALUES (10000000000000000); # same as first table
 
2139
CREATE TABLE t2 (i BIGINT UNSIGNED);
 
2140
INSERT INTO t2 VALUES (10000000000000000000); # same as first table
2126
2141
INSERT INTO t2 VALUES (1);
2127
2142
 
2128
2143
/* simple test */
2132
2147
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
2133
2148
 
2134
2149
/* subquery test with cast*/
2135
 
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
 
2150
SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);
2136
2151
 
2137
2152
DROP TABLE t1;
2138
2153
DROP TABLE t2;
2139
2154
 
2140
 
--echo # 
2141
 
--echo # Bug#20519: subselect with LIMIT M, N
2142
 
--echo #
 
2155
# 
 
2156
# Bug#20519: subselect with LIMIT M, N
 
2157
#
2143
2158
 
2144
 
CREATE TEMPORARY TABLE t1 (
2145
 
  id bigint NOT NULL auto_increment,
 
2159
CREATE TABLE t1 (
 
2160
  id bigint unsigned NOT NULL auto_increment,
2146
2161
  name varchar(255) NOT NULL,
2147
 
  PRIMARY KEY  (id))
2148
 
  ENGINE=MyISAM;
2149
 
 
 
2162
  PRIMARY KEY  (id)
 
2163
);
2150
2164
INSERT INTO t1 VALUES
2151
2165
  (1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
2152
2166
 
2153
2167
CREATE TABLE t2 (
2154
 
  id bigint NOT NULL auto_increment,
2155
 
  mid bigint NOT NULL,
 
2168
  id bigint unsigned NOT NULL auto_increment,
 
2169
  mid bigint unsigned NOT NULL,
2156
2170
  date date NOT NULL,
2157
 
  PRIMARY KEY  (id));
2158
 
 
 
2171
  PRIMARY KEY  (id)
 
2172
);
2159
2173
INSERT INTO t2 VALUES 
2160
2174
  (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
2161
2175
  (4, 2, '2006-04-20'), (5, 1, '2006-05-01');
2178
2192
  FROM t1;
2179
2193
DROP TABLE t1,t2;
2180
2194
 
2181
 
--echo #
2182
 
--echo # Bug#20869: subselect with range access by DESC
2183
 
--echo #
 
2195
#
 
2196
# Bug#20869: subselect with range access by DESC
 
2197
#
2184
2198
 
2185
2199
CREATE TABLE t1 (
2186
2200
  i1 int NOT NULL default '0',
2187
2201
  i2 int NOT NULL default '0',
2188
 
  t datetime,
2189
 
  PRIMARY KEY  (i1,i2,t));
2190
 
 
 
2202
  t datetime NOT NULL default '0000-00-00 00:00:00',
 
2203
  PRIMARY KEY  (i1,i2,t)
 
2204
);
2191
2205
INSERT INTO t1 VALUES 
2192
2206
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
2193
2207
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
2200
2214
  i1 int NOT NULL default '0',
2201
2215
  i2 int NOT NULL default '0',
2202
2216
  t datetime default NULL,
2203
 
  PRIMARY KEY  (i1));
2204
 
 
 
2217
  PRIMARY KEY  (i1)
 
2218
);
2205
2219
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
2206
2220
 
2207
2221
EXPLAIN
2216
2230
 
2217
2231
DROP TABLE t1, t2;
2218
2232
 
2219
 
--echo #
2220
 
--echo # Bug#14654 : Cannot select from the same table twice within a UNION
2221
 
--echo # statement 
2222
 
--echo #
 
2233
#
 
2234
# Bug#14654 : Cannot select from the same table twice within a UNION
 
2235
# statement 
 
2236
#
2223
2237
CREATE TABLE t1 (i INT);
2224
2238
 
2225
2239
(SELECT i FROM t1) UNION (SELECT i FROM t1);
2229
2243
   (SELECT i FROM t1)
2230
2244
  );
2231
2245
 
2232
 
--echo #TODO:not supported
 
2246
#TODO:not supported
2233
2247
--error ER_PARSE_ERROR
2234
2248
SELECT * FROM t1 
2235
2249
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
2236
2250
 
2237
 
--echo #TODO:not supported
2238
 
--error ER_PARSE_ERROR
 
2251
#TODO:not supported
 
2252
--error 1064
2239
2253
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
2240
2254
  from t1;
2241
2255
 
2242
 
--replace_column 9 #
2243
2256
explain select * from t1 where not exists 
2244
2257
  ((select t11.i from t1 t11) union (select t12.i from t1 t12));
2245
2258
 
2246
2259
DROP TABLE t1;
2247
2260
 
2248
 
--echo #
2249
 
--echo # Bug #21540: Subqueries with no from and aggregate functions return 
2250
 
--echo #              wrong results
 
2261
#
 
2262
# Bug #21540: Subqueries with no from and aggregate functions return 
 
2263
#              wrong results
2251
2264
CREATE TABLE t1 (a INT, b INT);
2252
2265
CREATE TABLE t2 (a INT);
2253
2266
INSERT INTO t2 values (1);
2267
2280
GROUP BY t2.a;
2268
2281
DROP TABLE t1,t2;  
2269
2282
 
2270
 
--echo #
2271
 
--echo # Bug #21727: Correlated subquery that requires filesort:
2272
 
--echo #             slow with big sort_buffer_size 
2273
 
--echo #
 
2283
#
 
2284
# Bug #21727: Correlated subquery that requires filesort:
 
2285
#             slow with big sort_buffer_size 
 
2286
#
2274
2287
 
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;
 
2288
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
 
2289
CREATE TABLE t2 (x int auto_increment, y int, z int,
 
2290
                 PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
2278
2291
disable_query_log;
2279
2292
set autocommit=0;
2280
2293
begin;
2307
2320
 
2308
2321
DROP TABLE t2,t1;
2309
2322
 
2310
 
--echo #
2311
 
--echo # Bug #25219: EXIST subquery with UNION over a mix of
2312
 
--echo #             correlated and uncorrelated selects
2313
 
--echo #
 
2323
#
 
2324
# Bug #25219: EXIST subquery with UNION over a mix of
 
2325
#             correlated and uncorrelated selects
 
2326
#
2314
2327
 
2315
2328
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
2316
2329
CREATE TABLE t2 (c int);
2331
2344
                SELECT c from t2 WHERE c=t1.c);
2332
2345
 
2333
2346
INSERT INTO t2 VALUES (2);
2334
 
CREATE TEMPORARY TABLE t3 (c int) ENGINE=MyISAM;
 
2347
CREATE TABLE t3 (c int);
2335
2348
INSERT INTO t3 VALUES (1);
2336
2349
 
2337
2350
SELECT * FROM t1
2341
2354
 
2342
2355
DROP TABLE t1,t2,t3;
2343
2356
 
2344
 
--echo #
2345
 
--echo # Bug#21904 (parser problem when using IN with a double "(())")
2346
 
--echo #
 
2357
#
 
2358
# Bug#21904 (parser problem when using IN with a double "(())")
 
2359
#
2347
2360
 
2348
2361
--disable_warnings
2349
2362
DROP TABLE IF EXISTS t1;
2351
2364
DROP TABLE IF EXISTS t1xt2;
2352
2365
--enable_warnings
2353
2366
 
2354
 
CREATE TEMPORARY TABLE t1 (
 
2367
CREATE TABLE t1 (
2355
2368
  id_1 int NOT NULL,
2356
 
  t varchar(4) DEFAULT NULL)
2357
 
  ENGINE=MyISAM;
 
2369
  t varchar(4) DEFAULT NULL
 
2370
);
2358
2371
 
2359
 
CREATE TEMPORARY TABLE t2 (
 
2372
CREATE TABLE t2 (
2360
2373
  id_2 int NOT NULL,
2361
 
  t varchar(4) DEFAULT NULL)
2362
 
  ENGINE=MyISAM;
 
2374
  t varchar(4) DEFAULT NULL
 
2375
);
2363
2376
 
2364
 
CREATE TEMPORARY TABLE t1xt2 (
 
2377
CREATE TABLE t1xt2 (
2365
2378
  id_1 int NOT NULL,
2366
 
  id_2 int NOT NULL)
2367
 
  ENGINE=MyISAM;
 
2379
  id_2 int NOT NULL
 
2380
);
2368
2381
 
2369
2382
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
2370
2383
 
2372
2385
 
2373
2386
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
2374
2387
 
2375
 
--echo # subselect returns 0 rows
 
2388
# subselect returns 0 rows
2376
2389
 
2377
2390
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2378
2391
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2394
2407
 
2395
2408
insert INTO t1xt2 VALUES (1, 12);
2396
2409
 
2397
 
--echo # subselect returns 1 row
 
2410
# subselect returns 1 row
2398
2411
 
2399
2412
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2400
2413
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2416
2429
 
2417
2430
insert INTO t1xt2 VALUES (2, 12);
2418
2431
 
2419
 
--echo # subselect returns more than 1 row
 
2432
# subselect returns more than 1 row
2420
2433
 
2421
2434
SELECT DISTINCT t1.id_1 FROM t1 WHERE
2422
2435
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
2440
2453
DROP TABLE t2;
2441
2454
DROP TABLE t1xt2;
2442
2455
 
2443
 
--echo #
2444
 
--echo # Bug #26728: derived table with concatanation of literals in select list
2445
 
--echo #  
 
2456
#
 
2457
# Bug #26728: derived table with concatanation of literals in select list
 
2458
#  
2446
2459
 
2447
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
2460
CREATE TABLE t1 (a int);
2448
2461
INSERT INTO t1 VALUES (3), (1), (2);           
2449
2462
 
2450
2463
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
2452
2465
 
2453
2466
DROP table t1;
2454
2467
 
2455
 
--echo #
2456
 
--echo # Bug #27257: COUNT(*) aggregated in outer query
2457
 
--echo #  
 
2468
#
 
2469
# Bug #27257: COUNT(*) aggregated in outer query
 
2470
#  
2458
2471
 
2459
 
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
2460
 
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
 
2472
CREATE TABLE t1 (a int, b int);
 
2473
CREATE TABLE t2 (m int, n int);
2461
2474
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2462
2475
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2463
2476
 
2475
2488
 
2476
2489
DROP TABLE t1,t2;
2477
2490
 
2478
 
--echo #
2479
 
--echo # Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument 
2480
 
--echo #  
 
2491
#
 
2492
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument 
 
2493
#  
2481
2494
 
2482
 
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
2483
 
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
 
2495
CREATE TABLE t1 (a int, b int);
 
2496
CREATE TABLE t2 (m int, n int);
2484
2497
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2485
2498
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2486
2499
 
2494
2507
 
2495
2508
DROP table t1,t2;
2496
2509
 
2497
 
--echo #
2498
 
--echo # Bug#27321: Wrong subquery result in a grouping select
2499
 
--echo #
 
2510
#
 
2511
# Bug#27321: Wrong subquery result in a grouping select
 
2512
#
2500
2513
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
2501
2514
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
2502
2515
(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
2544
 
2532
2545
DROP TABLE t1;
2533
2546
 
2534
 
--echo #
2535
 
--echo # Bug #27363: nested aggregates in outer, subquery / sum(select
2536
 
--echo # count(outer))
2537
 
--echo #
 
2547
#
 
2548
# Bug #27363: nested aggregates in outer, subquery / sum(select
 
2549
# count(outer))
 
2550
#
2538
2551
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);
 
2552
CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
2540
2553
 
2541
2554
--error ER_INVALID_GROUP_FUNC_USE
2542
2555
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
2555
2568
 
2556
2569
DROP TABLE t1,t2;
2557
2570
 
2558
 
--echo #
2559
 
--echo # Bug #27807: Server crash when executing subquery with EXPLAIN
2560
 
--echo #  
 
2571
#
 
2572
# Bug #27807: Server crash when executing subquery with EXPLAIN
 
2573
#  
2561
2574
CREATE TABLE t1 (a int, b int, KEY (a)); 
2562
2575
INSERT INTO t1 VALUES (1,1),(2,1);
2563
2576
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
2564
2577
DROP TABLE t1;
2565
2578
 
2566
 
--echo #
2567
 
--echo # Bug #28377: grouping query with a correlated subquery in WHERE condition
2568
 
--echo #  
 
2579
#
 
2580
# Bug #28377: grouping query with a correlated subquery in WHERE condition
 
2581
#  
2569
2582
 
2570
2583
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
2571
2584
INSERT INTO t1 VALUES
2572
2585
  (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;
 
2586
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
2574
2587
INSERT INTO t2 VALUES (7), (5), (1), (3);
2575
2588
 
2576
2589
SELECT id, st FROM t1 
2587
2600
 
2588
2601
DROP TABLE t1,t2;
2589
2602
 
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 # 
 
2603
#
 
2604
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
 
2605
#             over a grouping subselect
 
2606
# 
2594
2607
 
2595
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
2608
CREATE TABLE t1 (a int);
2596
2609
 
2597
2610
INSERT INTO t1 VALUES (1), (2);
2598
2611
 
2601
2614
 
2602
2615
DROP TABLE t1;
2603
2616
 
2604
 
--echo #
2605
 
--echo # Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1 
2606
 
--echo #
 
2617
#
 
2618
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1 
 
2619
#
2607
2620
 
2608
2621
CREATE TABLE t1 (
2609
2622
  a varchar(255) default NULL,
2610
2623
  b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2611
 
  INDEX idx(a,b));
2612
 
 
 
2624
  INDEX idx(a,b)
 
2625
);
2613
2626
CREATE TABLE t2 (
2614
 
  a varchar(255) default NULL);
 
2627
  a varchar(255) default NULL
 
2628
);
2615
2629
 
2616
2630
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
2617
2631
INSERT INTO t1 SELECT * FROM t1;
2633
2647
DROP TABLE t1,t2;
2634
2648
 
2635
2649
 
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;
 
2650
#
 
2651
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
 
2652
# of subquery
 
2653
#
 
2654
CREATE TABLE t1 (a INTEGER, b INTEGER);
 
2655
CREATE TABLE t2 (x INTEGER);
2642
2656
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
2643
2657
INSERT INTO t2 VALUES (1), (2);
2644
2658
 
2645
 
--echo # wasn't failing, but should
 
2659
# wasn't failing, but should
2646
2660
--error ER_SUBQUERY_NO_1_ROW
2647
2661
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
2648
2662
 
2649
 
--echo # fails as it should
 
2663
# fails as it should
2650
2664
--error ER_SUBQUERY_NO_1_ROW
2651
2665
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
2652
2666
 
2653
2667
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
2654
2668
DROP TABLE t1,t2;
2655
2669
 
2656
 
--echo # second test case from 27333
 
2670
# second test case from 27333
2657
2671
CREATE TABLE t1 (a INT, b INT);
2658
2672
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
2659
2673
 
2660
 
--echo # returns no rows, when it should
 
2674
# returns no rows, when it should
2661
2675
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
2662
2676
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
2663
2677
GROUP BY a1.a;
2664
2678
DROP TABLE t1;
2665
2679
 
2666
 
--echo #test cases from 29297
2667
 
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
2668
 
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;
 
2680
#test cases from 29297
 
2681
CREATE TABLE t1 (a INT);
 
2682
CREATE TABLE t2 (a INT);
2669
2683
INSERT INTO t1 VALUES (1),(2);
2670
2684
INSERT INTO t2 VALUES (1),(2);
2671
2685
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
2674
2688
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
2675
2689
DROP TABLE t1,t2;
2676
2690
 
2677
 
--echo #
2678
 
--echo # Bug #31884: Assertion + crash in subquery in the SELECT clause.
2679
 
--echo #
 
2691
#
 
2692
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
 
2693
#
2680
2694
 
2681
 
CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
2682
 
CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
 
2695
CREATE TABLE t1 (a1 INT, a2 INT);
 
2696
CREATE TABLE t2 (b1 INT, b2 INT);
2683
2697
 
2684
2698
INSERT INTO t1 VALUES (100, 200);
2685
2699
INSERT INTO t1 VALUES (101, 201);
2689
2703
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
2690
2704
DROP TABLE t1, t2;
2691
2705
 
2692
 
--echo #
2693
 
--echo # Bug #30788: Inconsistent retrieval of char/varchar
2694
 
--echo #
 
2706
#
 
2707
# Bug #30788: Inconsistent retrieval of char/varchar
 
2708
#
2695
2709
 
2696
2710
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
2697
2711
INSERT INTO t1 VALUES ('a', 'aa');
2699
2713
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2700
2714
CREATE INDEX I1 ON t1 (a);
2701
2715
CREATE INDEX I2 ON t1 (b);
2702
 
--replace_column 9 #
2703
2716
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2704
2717
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2705
2718
 
2707
2720
INSERT INTO t2 SELECT * FROM t1;
2708
2721
CREATE INDEX I1 ON t2 (a);
2709
2722
CREATE INDEX I2 ON t2 (b);
2710
 
--replace_column 9 #
2711
2723
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2712
2724
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2713
 
--replace_column 9 #
2714
2725
EXPLAIN
2715
2726
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2716
2727
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2717
2728
 
2718
2729
DROP TABLE t1,t2;
2719
2730
 
2720
 
--echo #
2721
 
--echo # Bug #32400: Complex SELECT query returns correct result only on some
2722
 
--echo # occasions
2723
 
--echo #
 
2731
#
 
2732
# Bug #32400: Complex SELECT query returns correct result only on some
 
2733
# occasions
 
2734
#
2724
2735
 
2725
2736
CREATE TABLE t1(a INT, b INT);
2726
2737
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
2736
2747
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
2737
2748
GROUP BY a;
2738
2749
 
2739
 
--replace_column 9 #
2740
2750
EXPLAIN 
2741
2751
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
2742
2752
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
2749
2759
DROP TABLE t1;
2750
2760
 
2751
2761
 
2752
 
--echo #
2753
 
--echo # Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
2754
 
--echo #
 
2762
#
 
2763
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
 
2764
#
2755
2765
 
2756
 
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
 
2766
CREATE TABLE t1 (a INT);
2757
2767
CREATE TABLE t2 (a INT);
2758
2768
 
2759
2769
INSERT INTO t1 VALUES (1),(2);
2764
2774
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
2765
2775
 
2766
2776
 
2767
 
--replace_column 9 #
2768
2777
EXPLAIN EXTENDED
2769
2778
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION 
2770
2779
                               (SELECT 1 FROM t2 WHERE t1.a = t2.a));
2771
2780
 
2772
2781
DROP TABLE t1,t2;
2773
2782
 
2774
 
--echo #
2775
 
--echo # BUG#33794 "MySQL crashes executing specific query on specific dump"
2776
 
--echo #
2777
 
CREATE TEMPORARY TABLE t4 (
 
2783
#
 
2784
# BUG#33794 "MySQL crashes executing specific query on specific dump"
 
2785
#
 
2786
CREATE TABLE t4 (
2778
2787
  f7 varchar(32) collate utf8_bin NOT NULL default '',
2779
2788
  f10 varchar(32) collate utf8_bin default NULL,
2780
 
  PRIMARY KEY  (f7))
2781
 
  ENGINE=MyISAM;
2782
 
 
 
2789
  PRIMARY KEY  (f7)
 
2790
);
2783
2791
INSERT INTO t4 VALUES(1,1), (2,null);
2784
2792
 
2785
2793
CREATE TABLE t2 (
2787
2795
  f2 varchar(50) collate utf8_bin default NULL,
2788
2796
  f3 varchar(10) collate utf8_bin default NULL,
2789
2797
  PRIMARY KEY  (f4),
2790
 
  UNIQUE KEY uk1 (f2));
2791
 
 
 
2798
  UNIQUE KEY uk1 (f2)
 
2799
);
2792
2800
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
2793
2801
 
2794
 
CREATE TEMPORARY TABLE t1  (
 
2802
CREATE TABLE t1 (
2795
2803
  f8 varchar(32) collate utf8_bin NOT NULL default '',
2796
2804
  f1 varchar(10) collate utf8_bin default NULL,
2797
2805
  f9 varchar(32) collate utf8_bin default NULL,
2798
 
  PRIMARY KEY  (f8))
2799
 
  ENGINE=MyISAM;
2800
 
 
 
2806
  PRIMARY KEY  (f8)
 
2807
);
2801
2808
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
2802
2809
 
2803
 
CREATE TEMPORARY TABLE t3 (
 
2810
CREATE TABLE t3 (
2804
2811
  f6 varchar(32) collate utf8_bin NOT NULL default '',
2805
2812
  f5 varchar(50) collate utf8_bin default NULL,
2806
 
  PRIMARY KEY (f6))
2807
 
  ENGINE=MyISAM;
2808
 
 
 
2813
  PRIMARY KEY (f6)
 
2814
);
2809
2815
INSERT INTO t3 VALUES (1,null), (2,null);
2810
2816
 
2811
2817
SELECT
2830
2836
 
2831
2837
--echo End of 5.0 tests.
2832
2838
 
2833
 
--echo #
2834
 
--echo #  Test [NOT] IN truth table (both as top-level and general predicate).
2835
 
--echo #
 
2839
#
 
2840
#  Test [NOT] IN truth table (both as top-level and general predicate).
 
2841
#
2836
2842
 
2837
 
create temporary table t_out (subcase char(3),
2838
 
                    a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;
 
2843
create table t_out (subcase char(3),
 
2844
                    a1 char(2), b1 char(2), c1 char(2));
2839
2845
create table t_in  (a2 char(2), b2 char(2), c2 char(2));
2840
2846
 
2841
2847
insert into t_out values ('A.1','2a', NULL, '2a');
2842
 
--echo #------------------------- A.2 - impossible
 
2848
#------------------------- A.2 - impossible
2843
2849
insert into t_out values ('A.3', '2a', NULL, '2a');
2844
2850
insert into t_out values ('A.4', '2a', NULL, 'xx');
2845
2851
insert into t_out values ('B.1', '2a', '2a', '2a');
2927
2933
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2928
2934
where subcase = 'A.3' and
2929
2935
      NOT((a1, b1, c1) IN (select * from t_in));
2930
 
--echo # test non-top level result indirectly
 
2936
# test non-top level result indirectly
2931
2937
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
2932
2938
where subcase = 'A.3' and
2933
2939
      ((a1, b1, c1) IN (select * from t_in)) is NULL and
2984
2990
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
2985
2991
where subcase = 'B.3' and
2986
2992
      NOT((a1, b1, c1) IN (select * from t_in));
2987
 
--echo # test non-top level result indirectly
 
2993
# test non-top level result indirectly
2988
2994
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
2989
2995
where subcase = 'B.3' and
2990
2996
      ((a1, b1, c1) IN (select * from t_in)) is NULL and
3007
3013
drop table t_in;
3008
3014
 
3009
3015
 
3010
 
--echo #
3011
 
--echo # Bug#20835 (literal string with =any values)
3012
 
--echo #
 
3016
#
 
3017
# Bug#20835 (literal string with =any values)
 
3018
#
3013
3019
CREATE TABLE t1 (s1 char(1));
3014
3020
INSERT INTO t1 VALUES ('a');
3015
 
SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
 
3021
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
3016
3022
DROP TABLE t1;
3017
3023
 
3018
 
--echo #
3019
 
--echo # Bug#33204: INTO is allowed in subselect, causing inconsistent results
3020
 
--echo #
 
3024
#
 
3025
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
 
3026
#
3021
3027
CREATE TABLE t1( a INT );
3022
3028
INSERT INTO t1 VALUES (1),(2);
3023
3029
 
3070
3076
  SELECT a FROM t1 WHERE a = 2 
3071
3077
) t1a;
3072
3078
 
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.
 
3079
# This was not allowed previously. Possibly, it should be allowed on the future.
 
3080
# For now, the intent is to keep the fix as non-intrusive as possible.
3075
3081
--error ER_PARSE_ERROR
3076
3082
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
3077
3083
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
3102
3108
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
3103
3109
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
3104
3110
 
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 # 
 
3111
# Test of rule
 
3112
# table_factor:  '(' get_select_lex query_expression_body ')' opt_table_alias
 
3113
# UNION should not be allowed inside the parentheses, nor should
 
3114
# aliases after.
 
3115
# 
3110
3116
SELECT * FROM t1 JOIN  (SELECT 1 UNION SELECT 1) alias ON 1;
3111
3117
--error ER_PARSE_ERROR
3112
3118
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
3119
3125
--error ER_PARSE_ERROR
3120
3126
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
3121
3127
 
3122
 
--sorted_result
3123
3128
SELECT * FROM t1 JOIN  (t1 t1a)  ON 1;
3124
3129
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
3125
3130
 
3132
3137
SELECT * FROM t1 JOIN  (SELECT 1 a)  a ON 1;
3133
3138
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
3134
3139
 
3135
 
--echo # For the join, TABLE_LIST::select_lex == NULL
3136
 
--echo # Check that we handle this.
 
3140
# For the join, TABLE_LIST::select_lex == NULL
 
3141
# Check that we handle this.
3137
3142
--error ER_PARSE_ERROR
3138
3143
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
3139
3144
 
3178
3183
--error ER_PARSE_ERROR
3179
3184
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
3180
3185
 
3181
 
--echo # Make sure context is popped when we leave the nested select
 
3186
# Make sure context is popped when we leave the nested select
3182
3187
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
3183
3188
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
3184
3189
 
3185
 
--echo # Make sure we have feature F561 (see .yy file)
 
3190
# Make sure we have feature F561 (see .yy file)
3186
3191
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
3187
3192
 
3188
 
--echo # Make sure the parser does not allow nested UNIONs anywhere
 
3193
# Make sure the parser does not allow nested UNIONs anywhere
3189
3194
 
3190
3195
--error ER_PARSE_ERROR
3191
3196
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );