« back to all changes in this revision
Viewing changes to tests/r/subselect.result
- browse files at revision 1094
- compare with another revision
- compare with revision 2114
- download diff
- download tarball
- view history from revision 1094
- Committer: Brian Aker
- Date: 2009-07-12 00:49:18 UTC
- mfrom: (1063.9.51 brian-tmp-fix)
- Revision ID: brian@gaz-20090712004918-chprmyj387ex6l8a
Merge Stewart
- files removed:
- tests/r/drizzlecheck.result
- files renamed:
- tests/r/rollback.result => tests/r/myisam_rollback.result
- tests/t/rollback.test => tests/t/myisam_rollback.test
- files modified:
- drizzled/base.h
added
removed
tests/r/subselect.result
1
# Initialise
1
2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
2
3
select (select 2);
3
4
(select 2)
131
132
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
132
133
a b (SELECT b)
133
134
1 2 2
134
create table t1 (a int) ENGINE=MyISAM;
135
create table t2 (a int, b int) ENGINE=MyISAM;
136
create table t3 (a int) ENGINE=MyISAM;
137
create table t4 (a int not null, b int not null) ENGINE=MyISAM;
135
create table t1 (a int);
136
create table t2 (a int, b int);
137
create table t3 (a int);
138
create table t4 (a int not null, b int not null);
138
139
insert into t1 values (2);
139
140
insert into t2 values (1,7),(2,7);
140
141
insert into t4 values (4,8),(3,8),(5,9);
177
178
3 8
178
179
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);
179
180
id select_type table type possible_keys key key_len ref rows filtered Extra
180
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
181
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using filesort
182
3 UNION t4 ALL NULL NULL NULL NULL 3 100.00 Using where
183
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
184
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
181
1 PRIMARY t2 ALL NULL NULL NULL NULL # 100.00 Using where
182
2 SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using filesort
183
3 UNION t4 ALL NULL NULL NULL NULL # 100.00 Using where
184
4 SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00
185
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL # NULL
185
186
Warnings:
186
187
Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` AS `a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) AS `max(t2.a)*4` from `test`.`t2`)) order by `a`)
187
188
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
195
196
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
196
197
(select * from t2 where a>1) as tt;
197
198
id select_type table type possible_keys key key_len ref rows filtered Extra
198
1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00
199
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
200
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
199
1 PRIMARY <derived3> system NULL NULL NULL NULL # 100.00
200
3 DERIVED t2 ALL NULL NULL NULL NULL # 100.00 Using where
201
2 SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using where; Using filesort
201
202
Warnings:
202
203
Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
203
204
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);
215
216
9 7.5000
216
217
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
217
218
id select_type table type possible_keys key key_len ref rows filtered Extra
218
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00
219
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
220
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
219
1 PRIMARY t4 ALL NULL NULL NULL NULL # 100.00
220
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00
221
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using where
221
222
Warnings:
222
223
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
223
224
Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
242
243
a
243
244
6
244
245
3
246
# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);
245
247
select * from t3 where a = all (select b from t2);
246
248
a
247
249
7
263
265
7
264
266
explain extended select * from t3 where a >= any (select b from t2);
265
267
id select_type table type possible_keys key key_len ref rows filtered Extra
266
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
267
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
268
1 PRIMARY t3 ALL NULL NULL NULL NULL # 100.00 Using where
269
2 SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00
268
270
Warnings:
269
271
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
270
272
select * from t3 where a >= all (select b from t2);
276
278
select * from t3 where a in (select * from t2);
277
279
ERROR 21000: Operand should contain 1 column(s)
278
280
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
281
# empty set
279
282
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
280
283
b ma
281
284
insert into t2 values (2,10);
286
289
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
287
290
b ma
288
291
7 12
289
create table t5 (a int) ENGINE=MyISAM;
292
create temporary table t5 (a int) ENGINE=MyISAM;
290
293
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
291
294
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
292
295
NULL 1
303
306
2 2
304
307
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;
305
308
id select_type table type possible_keys key key_len ref rows filtered Extra
306
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
307
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
308
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where
309
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
309
1 PRIMARY t2 ALL NULL NULL NULL NULL # 100.00
310
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00 Using where
311
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL # 100.00 Using where
312
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL
310
313
Warnings:
311
314
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
312
315
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
313
Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
316
Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
314
317
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
315
318
ERROR 21000: Subquery returns more than 1 row
316
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
317
create table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
319
create temporary table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
320
create temporary table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
318
321
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
319
322
insert into t6 values (1,1),(1,2),(2,2),(1,3);
320
323
select * from t6 where exists (select * from t7 where uq = clinic_uq);
329
332
Warnings:
330
333
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
331
334
Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
335
# not unique fields
332
336
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
333
337
ERROR 23000: Column 'a' in field list is ambiguous
338
# different tipes & group functions
334
339
drop table t1,t2,t3;
335
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0') ENGINE=MyISAM;
340
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
336
341
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
337
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0') ENGINE=MyISAM;
342
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
338
343
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
339
CREATE TABLE t1 (a varchar(20),b date NULL) ENGINE=MyISAM;
344
CREATE TABLE t1 (a varchar(20),b date NULL);
340
345
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
341
346
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
342
347
a b
352
357
`email` varchar(60) NOT NULL default '',
353
358
PRIMARY KEY (`pseudo`),
354
359
UNIQUE KEY `email` (`email`)
355
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
360
) ROW_FORMAT=DYNAMIC;
361
Warnings:
362
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
363
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
356
364
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
357
365
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
358
366
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
359
367
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');
360
368
id select_type table type possible_keys key key_len ref rows filtered Extra
361
1 PRIMARY t8 const PRIMARY PRIMARY 142 const 1 100.00 Using index
362
4 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index
363
2 SUBQUERY t8 const PRIMARY PRIMARY 142 const 1 100.00
364
3 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index
369
1 PRIMARY t8 const PRIMARY PRIMARY 142 const # 100.00 Using index
370
4 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00 Using index
371
2 SUBQUERY t8 const PRIMARY PRIMARY 142 const # 100.00
372
3 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00 Using index
365
373
Warnings:
366
374
Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))
367
375
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
376
384
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
377
385
ERROR 21000: Subquery returns more than 1 row
378
386
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
379
CREATE TABLE `t1` (
387
#searchconthardwarefr3 forumconthardwarefr7
388
CREATE TEMPORARY TABLE `t1` (
380
389
`topic` bigint NOT NULL default '0',
381
390
`date` date NULL,
382
391
`pseudo` varchar(35) NOT NULL default '',
418
427
Warnings:
419
428
Note 1003 select 1 AS `1` from `test`.`t1` where 1
420
429
drop table t1;
430
#forumconthardwarefr7 searchconthardwarefr7
421
431
CREATE TABLE `t1` (
422
432
`numeropost` bigint NOT NULL auto_increment,
423
433
`maxnumrep` int NOT NULL default '0',
424
434
PRIMARY KEY (`numeropost`),
425
435
UNIQUE KEY `maxnumrep` (`maxnumrep`)
426
) ENGINE=MyISAM ROW_FORMAT=FIXED;
436
) ROW_FORMAT=FIXED;
437
Warnings:
438
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
427
439
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
428
440
CREATE TABLE `t2` (
429
441
`mot` varchar(30) NOT NULL default '',
431
443
`date` date NULL,
432
444
`pseudo` varchar(35) NOT NULL default '',
433
445
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
434
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
446
) ROW_FORMAT=DYNAMIC;
447
Warnings:
448
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
449
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
435
450
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
436
451
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
437
452
a
490
505
joce 40143 2002-10-22 joce 1
491
506
joce 43506 2002-10-22 joce 0
492
507
drop table t1,t2;
508
#forumconthardwarefr7
493
509
CREATE TABLE `t1` (
494
510
`numeropost` bigint NOT NULL auto_increment,
495
511
`maxnumrep` int NOT NULL default '0',
496
512
PRIMARY KEY (`numeropost`),
497
513
UNIQUE KEY `maxnumrep` (`maxnumrep`)
498
) ENGINE=MyISAM ROW_FORMAT=FIXED;
514
) ROW_FORMAT=FIXED;
515
Warnings:
516
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
499
517
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
500
518
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
501
519
ERROR 21000: Subquery returns more than 1 row
502
520
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
503
521
ERROR 21000: Subquery returns more than 1 row
504
522
drop table t1;
505
create table t1 (a int) ENGINE=MyISAM;
523
create table t1 (a int);
506
524
insert into t1 values (1),(2),(3);
507
525
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
508
526
a
510
528
2
511
529
3
512
530
drop table t1;
513
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;
531
#iftest
532
CREATE TEMPORARY TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;
514
533
INSERT INTO t1 VALUES ();
515
534
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
516
535
ERROR 21000: Subquery returns more than 1 row
517
536
drop table t1;
537
# threadhardwarefr7
518
538
CREATE TABLE `t1` (
519
539
`numeropost` bigint NOT NULL default '0',
520
540
`numreponse` int NOT NULL auto_increment,
522
542
PRIMARY KEY (`numeropost`,`numreponse`),
523
543
UNIQUE KEY `numreponse` (`numreponse`),
524
544
KEY `pseudo` (`pseudo`,`numeropost`)
525
) ENGINE=MyISAM;
545
);
526
546
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
527
547
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
528
548
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
534
554
ERROR 21000: Subquery returns more than 1 row
535
555
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
536
556
id select_type table type possible_keys key key_len ref rows filtered Extra
537
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
557
1 SIMPLE NULL NULL NULL NULL NULL NULL # NULL Select tables optimized away
538
558
Warnings:
539
559
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)
540
560
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
541
561
id select_type table type possible_keys key key_len ref rows filtered Extra
542
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 12 const,const 1 100.00 Using index
543
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
562
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 12 const,const # 100.00 Using index
563
2 SUBQUERY NULL NULL NULL NULL NULL NULL # NULL Select tables optimized away
544
564
Warnings:
545
565
Note 1003 select '3' AS `numreponse` from `test`.`t1` where 1
546
566
drop table t1;
547
CREATE TABLE t1 (a int) ENGINE=MyISAM;
567
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
548
568
INSERT INTO t1 VALUES (1);
549
569
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
550
570
1
551
571
1
552
572
drop table t1;
553
create table t1 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
554
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
573
#update with subselects
574
create table t1 (a int NOT NULL, b int, primary key (a));
575
create table t2 (a int NOT NULL, b int, primary key (a));
555
576
insert into t1 values (0, 10),(1, 11),(2, 12);
556
577
insert into t2 values (1, 21),(2, 22),(3, 23);
557
578
select * from t1;
570
591
1 21
571
592
2 22
572
593
drop table t1, t2;
573
create table t1 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
574
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
594
#delete with subselects
595
create table t1 (a int NOT NULL, b int, primary key (a));
596
create table t2 (a int NOT NULL, b int, primary key (a));
575
597
insert into t1 values (0, 10),(1, 11),(2, 12);
576
598
insert into t2 values (1, 21),(2, 12),(3, 23);
577
599
select * from t1;
592
614
0 10
593
615
1 11
594
616
drop table t1, t2;
595
create table t11 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
596
create table t12 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
597
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
617
#multi-delete with subselects
618
create table t11 (a int NOT NULL, b int, primary key (a));
619
create table t12 (a int NOT NULL, b int, primary key (a));
620
create temporary table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
598
621
insert into t11 values (0, 10),(1, 11),(2, 12);
599
622
insert into t12 values (33, 10),(22, 11),(2, 12);
600
623
insert into t2 values (1, 21),(2, 12),(3, 23);
622
645
22 11
623
646
33 10
624
647
drop table t11, t12, t2;
625
CREATE TABLE t1 (x int) ENGINE=MyISAM;
626
create table t2 (a int) ENGINE=MyISAM;
627
create table t3 (b int) ENGINE=MyISAM;
648
#insert with subselects
649
CREATE TABLE t1 (x int);
650
create table t2 (a int);
651
create table t3 (b int);
628
652
insert into t2 values (1);
629
653
insert into t3 values (1),(2);
630
654
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
648
672
2
649
673
3
650
674
3
675
# After this, only data based on old t1 records should have been added.
651
676
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
652
677
select * from t1;
653
678
x
669
694
11
670
695
11
671
696
2
697
#
698
#TODO: should be uncommented after bug 380 fix pushed
699
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
700
#select * from t1;
672
701
drop table t1, t2, t3;
673
CREATE TABLE t1 (x int not null, y int, primary key (x)) ENGINE=MyISAM;
674
create table t2 (a int) ENGINE=MyISAM;
675
create table t3 (a int) ENGINE=MyISAM;
702
#replace with subselects
703
CREATE TABLE t1 (x int not null, y int, primary key (x));
704
create table t2 (a int);
705
create temporary table t3 (a int) ENGINE=MyISAM;
676
706
insert into t2 values (1);
677
707
insert into t3 values (1),(2);
678
708
select * from t1;
708
738
drop table t1, t2, t3;
709
739
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
710
740
ERROR HY000: No tables used
711
CREATE TABLE t2 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
741
CREATE TABLE t2 (id int default NULL, KEY id (id));
712
742
INSERT INTO t2 VALUES (1),(2);
713
743
SELECT * FROM t2 WHERE id IN (SELECT 1);
714
744
id
753
783
id
754
784
1
755
785
2
756
CREATE TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
786
CREATE TEMPORARY TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
757
787
INSERT INTO t1 values (1),(1);
758
788
UPDATE t2 SET id=(SELECT * FROM t1);
759
789
ERROR 21000: Subquery returns more than 1 row
760
790
drop table t2, t1;
761
create table t1 (a int) ENGINE=MyISAM;
791
#NULL test
792
create temporary table t1 (a int) ENGINE=MyISAM;
762
793
insert into t1 values (1),(2),(3);
763
794
select 1 IN (SELECT * from t1);
764
795
1 IN (SELECT * from t1)
792
823
10 > ANY (SELECT * from t1)
793
824
1
794
825
drop table t1;
795
create table t1 (a varchar(20)) ENGINE=MyISAM;
826
create temporary table t1 (a varchar(20)) ENGINE=MyISAM;
796
827
insert into t1 values ('A'),('BC'),('DEF');
797
828
select 'A' IN (SELECT * from t1);
798
829
'A' IN (SELECT * from t1)
826
857
'XYZS' > ANY (SELECT * from t1)
827
858
1
828
859
drop table t1;
829
create table t1 (a float) ENGINE=MyISAM;
860
create temporary table t1 (a float) ENGINE=MyISAM;
830
861
insert into t1 values (1.5),(2.5),(3.5);
831
862
select 1.5 IN (SELECT * from t1);
832
863
1.5 IN (SELECT * from t1)
872
903
4.5
873
904
NULL
874
905
drop table t1;
875
CREATE TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a)) ENGINE=MyISAM;
876
CREATE TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
906
#
907
# Null with keys
908
#
909
CREATE TEMPORARY TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a)) ENGINE=MyISAM;
910
CREATE TEMPORARY TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
877
911
INSERT INTO t1 VALUES (1),(2),(3),(4);
878
912
INSERT INTO t2 VALUES (1),(2),(3);
879
913
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
888
922
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
889
923
Warnings:
890
924
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
891
CREATE TABLE t3 (a int default '0') ENGINE=MyISAM;
925
CREATE TEMPORARY TABLE t3 (a int default '0') ENGINE=MyISAM;
892
926
INSERT INTO t3 VALUES (1),(2),(3);
893
927
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
894
928
a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
904
938
Warnings:
905
939
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
906
940
drop table t1,t2,t3;
907
CREATE TABLE t1 (a int) ENGINE=MyISAM;
941
#LIMIT is not supported now
942
#create table t1 (a float) ENGINE=MyISAM;
943
#-- error 1235
944
#select 10.5 IN (SELECT * from t1 LIMIT 1);
945
#-- error 1235
946
#select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
947
#drop table t1;
948
#
949
#create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;
950
#create table t2 (a int) ENGINE=MyISAM;
951
#insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
952
#insert into t2 values (1),(2),(NULL);
953
#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;
954
#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;
955
#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;
956
#drop table t1,t2;
957
#
958
#create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;
959
#insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
960
#select ROW(1, 1, 'a') IN (select a,b,c from t1);
961
#select ROW(1, 2, 'a') IN (select a,b,c from t1);
962
#select ROW(1, 1, 'a') IN (select b,a,c from t1);
963
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
964
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
965
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
966
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
967
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
968
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
969
#-- error 1235
970
#select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
971
#drop table t1;
972
#test of uncacheable subqueries
973
CREATE TABLE t1 (a int);
908
974
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
909
975
id select_type table type possible_keys key key_len ref rows filtered Extra
910
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
911
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
976
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00
977
2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00
912
978
Warnings:
913
979
Note 1003 select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
914
980
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
915
981
id select_type table type possible_keys key key_len ref rows filtered Extra
916
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
917
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
982
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00
983
2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00
918
984
Warnings:
919
985
Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
920
986
drop table t1;
921
CREATE TABLE `t1` (
987
CREATE TEMPORARY TABLE `t1` (
922
988
`mot` varchar(30) NOT NULL default '',
923
989
`topic` bigint NOT NULL default '0',
924
990
`date` date NULL DEFAULT '2009-01-20',
927
993
KEY `pseudo` (`pseudo`,`date`,`topic`),
928
994
KEY `topic` (`topic`)
929
995
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
930
CREATE TABLE `t2` (
996
CREATE TEMPORARY TABLE `t2` (
931
997
`mot` varchar(30) NOT NULL default '',
932
998
`topic` bigint NOT NULL default '0',
933
999
`date` date NULL default '1997-08-29',
936
1002
KEY `pseudo` (`pseudo`,`date`,`topic`),
937
1003
KEY `topic` (`topic`)
938
1004
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
939
CREATE TABLE `t3` (
1005
CREATE TEMPORARY TABLE `t3` (
940
1006
`numeropost` bigint NOT NULL auto_increment,
941
1007
`maxnumrep` int NOT NULL default '0',
942
1008
PRIMARY KEY (`numeropost`),
962
1028
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
963
1029
a (SELECT a)
964
1030
1 1
965
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
1031
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
966
1032
SHOW CREATE TABLE t1;
967
1033
Table Create Table
968
t1 CREATE TABLE `t1` (
1034
t1 CREATE TEMPORARY TABLE `t1` (
969
1035
`a` int NOT NULL DEFAULT '0',
970
1036
`(SELECT 1)` int NOT NULL DEFAULT '0'
971
1037
) ENGINE=MyISAM
972
1038
drop table t1;
973
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1039
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
974
1040
SHOW CREATE TABLE t1;
975
1041
Table Create Table
976
t1 CREATE TABLE `t1` (
1042
t1 CREATE TEMPORARY TABLE `t1` (
977
1043
`a` int NOT NULL DEFAULT '0',
978
1044
`(SELECT a)` int NOT NULL DEFAULT '0'
979
1045
) ENGINE=MyISAM
980
1046
drop table t1;
981
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
1047
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
982
1048
SHOW CREATE TABLE t1;
983
1049
Table Create Table
984
t1 CREATE TABLE `t1` (
1050
t1 CREATE TEMPORARY TABLE `t1` (
985
1051
`a` int NOT NULL DEFAULT '0',
986
1052
`(SELECT a+0)` int NOT NULL DEFAULT '0'
987
1053
) ENGINE=MyISAM
988
1054
drop table t1;
989
CREATE TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1055
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
990
1056
select * from t1;
991
1057
a
992
1058
2
993
1059
SHOW CREATE TABLE t1;
994
1060
Table Create Table
995
t1 CREATE TABLE `t1` (
1061
t1 CREATE TEMPORARY TABLE `t1` (
996
1062
`a` bigint NOT NULL
997
1063
) ENGINE=MyISAM
998
1064
drop table t1;
999
create table t1 (a int) ENGINE=MyISAM;
1065
create table t1 (a int);
1000
1066
insert into t1 values (1), (2), (3);
1001
1067
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
1002
1068
from t1;
1003
1069
id select_type table type possible_keys key key_len ref rows filtered Extra
1004
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1005
2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1006
3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1070
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00
1071
2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1072
3 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1007
1073
Warnings:
1008
1074
Note 1003 select `test`.`t1`.`a` AS `a`,(select (select rand() AS `rand()` from `test`.`t1` limit 1) AS `(select rand() from t1 limit 1)` from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from `test`.`t1`
1009
1075
drop table t1;
1076
#
1077
# error in IN
1078
#
1010
1079
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);
1011
1080
ERROR 42S02: Table 'test.t1' doesn't exist
1081
#
1082
# complex subquery
1083
#
1012
1084
CREATE TABLE t1 (
1013
1085
ID int NOT NULL auto_increment,
1014
1086
name char(35) NOT NULL default '',
1016
1088
District char(20) NOT NULL default '',
1017
1089
Population int NOT NULL default '0',
1018
1090
PRIMARY KEY (ID)
1019
) ENGINE=MyISAM;
1091
);
1020
1092
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
1021
1093
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
1022
1094
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
1037
1109
Capital int default NULL,
1038
1110
Code2 char(2) NOT NULL default '',
1039
1111
PRIMARY KEY (Code)
1040
) ENGINE=MyISAM;
1112
);
1041
1113
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');
1042
1114
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');
1043
1115
select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent);
1044
1116
Continent Name Population
1045
1117
Oceania Sydney 3276207
1046
1118
drop table t1, t2;
1047
CREATE TABLE `t1` (
1119
#
1120
# constants in IN
1121
#
1122
CREATE TEMPORARY TABLE `t1` (
1048
1123
`id` bigint NOT NULL auto_increment,
1049
1124
`pseudo` varchar(35) NOT NULL default '',
1050
1125
PRIMARY KEY (`id`),
1071
1146
Warnings:
1072
1147
Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
1073
1148
drop table t1;
1074
CREATE TABLE `t1` (
1149
CREATE TEMPORARY TABLE `t1` (
1075
1150
`i` int NOT NULL default '0',
1076
1151
PRIMARY KEY (`i`)
1077
1152
) ENGINE=MyISAM;
1084
1159
i
1085
1160
3
1086
1161
drop table t1;
1162
#
1163
# Multi update test
1164
#
1087
1165
CREATE TABLE t1 (
1088
1166
id int default NULL
1089
) ENGINE=MyISAM;
1167
);
1090
1168
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
1091
CREATE TABLE t2 (
1169
CREATE TEMPORARY TABLE t2 (
1092
1170
id int default NULL,
1093
1171
name varchar(15) default NULL
1094
1172
) ENGINE=MyISAM;
1101
1179
2 lenka
1102
1180
1 lenka
1103
1181
drop table t1,t2;
1104
create table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
1182
#
1183
# correct NULL in <CONSTANT> IN (SELECT ...)
1184
#
1185
create temporary table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
1105
1186
insert into t1 values (-1), (-4), (-2), (NULL);
1106
1187
select -10 IN (select a from t1 FORCE INDEX (indexa));
1107
1188
-10 IN (select a from t1 FORCE INDEX (indexa))
1108
1189
NULL
1109
1190
drop table t1;
1110
create table t1 (id int not null auto_increment primary key, salary int, key(salary)) ENGINE=MyISAM;
1191
#
1192
# Test optimization for sub selects
1193
#
1194
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
1111
1195
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
1112
1196
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
1113
1197
id select_type table type possible_keys key key_len ref rows filtered Extra
1114
1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
1115
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1198
1 PRIMARY t1 ref salary salary 5 const # 100.00 Using where; Using index
1199
2 SUBQUERY NULL NULL NULL NULL NULL NULL # NULL Select tables optimized away
1116
1200
Warnings:
1117
1201
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`))
1118
1202
drop table t1;
1125
1209
UNIQUE KEY t1_PK (ID,SUB_ID),
1126
1210
KEY t1_FK (REF_ID,REF_SUB),
1127
1211
KEY t1_REFID (REF_ID)
1128
) ENGINE=MyISAM;
1212
);
1129
1213
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
1130
1214
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
1131
1215
REF_ID
1132
1216
DROP TABLE t1;
1133
create table t1 (a int, b int) ENGINE=MyISAM;
1134
create table t2 (a int, b int) ENGINE=MyISAM;
1217
#
1218
# uninterruptable update
1219
#
1220
create temporary table t1 (a int, b int) ENGINE=MyISAM;
1221
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1135
1222
insert into t1 values (1,0), (2,0), (3,0);
1136
1223
insert into t2 values (1,1), (2,1), (3,1), (2,2);
1137
1224
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
1143
1230
2 NULL
1144
1231
3 1
1145
1232
drop table t1, t2;
1146
CREATE TABLE `t1` (
1233
#
1234
# reduced subselect in ORDER BY & GROUP BY clauses
1235
#
1236
CREATE TEMPORARY TABLE `t1` (
1147
1237
`id` bigint NOT NULL auto_increment,
1148
1238
`pseudo` varchar(35) NOT NULL default '',
1149
1239
`email` varchar(60) NOT NULL default '',
1160
1250
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
1161
1251
a
1162
1252
1
1163
create table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1164
create table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
1165
create table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1253
#
1254
# IN subselect optimization test
1255
#
1256
create temporary table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1257
create temporary table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
1258
create temporary table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1166
1259
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1167
1260
insert into t2 values (2), (3), (4), (5);
1168
1261
insert into t3 values (10,3), (20,4), (30,5);
1199
1292
Warnings:
1200
1293
Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1` join `test`.`t3`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
1201
1294
drop table t1, t2, t3;
1202
create table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
1203
create table t2 (a int, index a (a)) ENGINE=MyISAM;
1204
create table t3 (a int, b int, index a (a)) ENGINE=MyISAM;
1295
create temporary table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
1296
create temporary table t2 (a int, index a (a)) ENGINE=MyISAM;
1297
create temporary table t3 (a int, b int, index a (a)) ENGINE=MyISAM;
1205
1298
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1299
# making table large enough
1206
1300
insert into t2 values (2), (3), (4), (5);
1207
1301
insert into t3 values (10,3), (20,4), (30,5);
1208
1302
select * from t2 where t2.a in (select a from t1);
1254
1348
Warnings:
1255
1349
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
1256
1350
drop table t1, t2, t3;
1257
create table t1 (a int, b int) ENGINE=MyISAM;
1258
create table t2 (a int, b int) ENGINE=MyISAM;
1259
create table t3 (a int, b int) ENGINE=MyISAM;
1351
#
1352
# alloc_group_fields() working
1353
#
1354
create temporary table t1 (a int, b int) ENGINE=MyISAM;
1355
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1356
create temporary table t3 (a int, b int) ENGINE=MyISAM;
1260
1357
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
1261
1358
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
1262
1359
insert into t3 values (3,3), (2,2), (1,1);
1266
1363
2 2
1267
1364
1 2
1268
1365
drop table t1,t2,t3;
1269
create table t1 (s1 int) ENGINE=MyISAM;
1270
create table t2 (s1 int) ENGINE=MyISAM;
1366
#
1367
# aggregate functions in HAVING test
1368
#
1369
create temporary table t1 (s1 int) ENGINE=MyISAM;
1370
create temporary table t2 (s1 int) ENGINE=MyISAM;
1271
1371
insert into t1 values (1);
1272
1372
insert into t2 values (1);
1273
1373
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
1274
1374
s1
1275
1375
1
1276
1376
drop table t1,t2;
1277
create table t1 (s1 int) ENGINE=MyISAM;
1278
create table t2 (s1 int) ENGINE=MyISAM;
1377
#
1378
# update subquery with wrong field (to force name resolving
1379
# in UPDATE name space)
1380
#
1381
create temporary table t1 (s1 int) ENGINE=MyISAM;
1382
create temporary table t2 (s1 int) ENGINE=MyISAM;
1279
1383
insert into t1 values (1);
1280
1384
insert into t2 values (1);
1281
1385
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1282
1386
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1283
1387
DROP TABLE t1, t2;
1284
create table t1(toid int,rd int) ENGINE=MyISAM;
1285
create table t2(userid int,pmnew int,pmtotal int) ENGINE=MyISAM;
1388
#
1389
# collation test
1390
#
1391
#CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
1392
# s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;
1393
#INSERT INTO t1 VALUES ('z','?');
1394
#-- error 1267
1395
#select * from t1 where s1 > (select max(s2) from t1);
1396
#-- error 1267
1397
#select * from t1 where s1 > any (select max(s2) from t1);
1398
#drop table t1;
1399
#
1400
# aggregate functions reinitialization
1401
#
1402
create table t1(toid int,rd int);
1403
create table t2(userid int,pmnew int,pmtotal int);
1286
1404
insert into t2 values(1,0,0),(2,0,0);
1287
1405
insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2);
1288
1406
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);
1290
1408
1 0 0 9 3
1291
1409
2 0 0 4 2
1292
1410
drop table t1, t2;
1293
create table t1 (s1 char(5)) ENGINE=MyISAM;
1411
#
1412
# row union
1413
#
1414
create table t1 (s1 char(5));
1294
1415
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
1295
1416
ERROR 21000: Operand should contain 1 column(s)
1296
1417
insert into t1 values ('tttt');
1299
1420
tttt
1300
1421
explain extended (select * from t1);
1301
1422
id select_type table type possible_keys key key_len ref rows filtered Extra
1302
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
1423
1 SIMPLE t1 ALL NULL NULL NULL NULL # 100.00
1303
1424
Warnings:
1304
Note 1003 (select 'tttt' AS `s1` from `test`.`t1`)
1425
Note 1003 (select `test`.`t1`.`s1` AS `s1` from `test`.`t1`)
1305
1426
(select * from t1);
1306
1427
s1
1307
1428
tttt
1308
1429
drop table t1;
1309
create table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1310
create table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1430
#
1431
# IN optimisation test results
1432
#
1433
create temporary table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1434
create temporary table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1311
1435
insert into t1 values ('a1'),('a2'),('a3');
1312
1436
insert into t2 values ('a1'),('a2');
1313
1437
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1355
1479
Warnings:
1356
1480
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
1357
1481
drop table t1,t2;
1358
create table t2 (a int, b int) ENGINE=MyISAM;
1359
create table t3 (a int) ENGINE=MyISAM;
1482
#
1483
# correct ALL optimisation
1484
#
1485
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1486
create temporary table t3 (a int) ENGINE=MyISAM;
1360
1487
insert into t3 values (6),(7),(3);
1361
1488
select * from t3 where a >= all (select b from t2);
1362
1489
a
1428
1555
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1429
1556
Warnings:
1430
1557
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1558
#
1559
# optimized static ALL/ANY with grouping
1560
#
1431
1561
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1432
1562
select * from t3 where a > all (select max(b) from t2 group by a);
1433
1563
a
1440
1570
Warnings:
1441
1571
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) AS `max(b)` from `test`.`t2` group by `test`.`t2`.`a`)))
1442
1572
drop table t2, t3;
1443
CREATE 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 ;
1573
#
1574
# correct used_tables()
1575
#
1576
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 ;
1444
1577
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());
1445
CREATE 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;
1578
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;
1446
1579
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);
1447
CREATE 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 ;
1580
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 ;
1448
1581
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);
1449
CREATE TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
1582
CREATE TEMPORARY TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
1450
1583
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1451
1584
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;
1452
1585
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')
1458
1591
-1 Valid
1459
1592
-1 Valid 2
1460
1593
drop table t1,t2,t3,t4;
1461
CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM;
1594
#
1595
# cardinality check
1596
#
1597
CREATE TEMPORARY TABLE t1 (id int default NULL) ENGINE=MyISAM;
1462
1598
INSERT INTO t1 VALUES (1),(5);
1463
CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM;
1599
CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;
1464
1600
INSERT INTO t2 VALUES (2),(6);
1465
1601
select * from t1 where (1,2,6) in (select * from t2);
1466
1602
ERROR 21000: Operand should contain 3 column(s)
1467
1603
DROP TABLE t1,t2;
1468
create table t1 (s1 char) ENGINE=MyISAM;
1604
#
1605
# optimized ALL/ANY with union
1606
#
1607
create table t1 (s1 char);
1469
1608
insert into t1 values ('e');
1470
1609
select * from t1 where 'f' > any (select s1 from t1);
1471
1610
s1
1475
1614
e
1476
1615
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1477
1616
id select_type table type possible_keys key key_len ref rows filtered Extra
1478
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1479
2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
1480
3 UNION t1 system NULL NULL NULL NULL 1 100.00
1481
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1617
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00
1618
2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1619
3 UNION t1 ALL NULL NULL NULL NULL # 100.00
1620
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL
1482
1621
Warnings:
1483
Note 1003 select 'e' AS `s1` from `test`.`t1` where 1
1622
Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1
1484
1623
drop table t1;
1485
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
1624
#
1625
# filesort in subquery (restoring join_tab)
1626
#
1627
CREATE TEMPORARY TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
1486
1628
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
1487
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
1629
CREATE TEMPORARY TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
1488
1630
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
1489
1631
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;
1490
1632
phone code
1493
1635
89356874041 NULL
1494
1636
95895001874 NULL
1495
1637
drop table t1, t2;
1496
create table t1 (s1 int) ENGINE=MyISAM;
1497
create table t2 (s1 int) ENGINE=MyISAM;
1638
#
1639
# unresolved field error
1640
#
1641
create temporary table t1 (s1 int) ENGINE=MyISAM;
1642
create temporary table t2 (s1 int) ENGINE=MyISAM;
1498
1643
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
1499
1644
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
1500
1645
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
1502
1647
select count(*) from t2 group by t1.s2;
1503
1648
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1504
1649
drop table t1, t2;
1505
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
1506
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
1650
#
1651
# fix_fields() in add_ref_to_table_cond()
1652
#
1653
CREATE TEMPORARY TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
1654
CREATE TEMPORARY TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
1507
1655
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
1508
1656
INSERT INTO t2 VALUES (100, 200, 'C');
1509
1657
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
1510
1658
COLC
1511
1659
DROP TABLE t1, t2;
1512
CREATE TABLE t1 (a int) ENGINE=MyISAM;
1660
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
1513
1661
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
1514
1662
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
1515
1663
(SELECT a)
1519
1667
4
1520
1668
5
1521
1669
DROP TABLE t1;
1522
CREATE TABLE `t1` (
1670
#
1671
# Bug 2479
1672
#
1673
CREATE TEMPORARY TABLE `t1` (
1523
1674
`id` int NOT NULL auto_increment,
1524
1675
`id_cns` int NOT NULL default '0',
1525
1676
`tipo` enum('','UNO','DUE') NOT NULL default '',
1532
1683
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`))
1533
1684
ENGINE=MyISAM;
1534
1685
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);
1535
CREATE TABLE `t2` (
1686
CREATE TEMPORARY TABLE `t2` (
1536
1687
`id` int NOT NULL auto_increment,
1537
1688
`max_anno_dep` int NOT NULL default '0',
1538
1689
PRIMARY KEY (`id`)) ENGINE=MyISAM;
1543
1694
50 1990 0
1544
1695
51 1990 NULL
1545
1696
DROP TABLE t1, t2;
1546
create table t1 (a int) ENGINE=MyISAM;
1697
#
1698
# GLOBAL LIMIT
1699
#
1700
create temporary table t1 (a int) ENGINE=MyISAM;
1547
1701
insert into t1 values (1), (2), (3);
1548
1702
SET SQL_SELECT_LIMIT=1;
1549
1703
select sum(a) from (select * from t1) as a;
1554
1708
1
1555
1709
SET SQL_SELECT_LIMIT=default;
1556
1710
drop table t1;
1557
CREATE TABLE t1 (a int, b int, INDEX (a)) ENGINE=MyISAM;
1711
#
1712
# Bug #3118: subselect + order by
1713
#
1714
CREATE TABLE t1 (a int, b int, INDEX (a));
1558
1715
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1559
1716
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1560
1717
a b
1562
1719
1 2
1563
1720
1 3
1564
1721
DROP TABLE t1;
1565
create table t1(val varchar(10)) ENGINE=MyISAM;
1722
# Item_cond fix field
1723
#
1724
create table t1(val varchar(10));
1566
1725
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1567
1726
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%');
1568
1727
count(*)
1569
1728
0
1570
1729
drop table t1;
1571
create table t1 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1730
#
1731
# ref_or_null replacing with ref
1732
#
1733
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1572
1734
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');
1573
1735
select * from t1 where id not in (select id from t1 where id < 8);
1574
1736
id text
1586
1748
12 text12
1587
1749
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1588
1750
id select_type table type possible_keys key key_len ref rows filtered Extra
1589
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
1590
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
1751
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00 Using where
1752
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func # 100.00 Using index; Using where
1591
1753
Warnings:
1592
1754
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
1593
1755
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);
1594
1756
id select_type table type possible_keys key key_len ref rows filtered Extra
1595
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
1596
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
1757
1 PRIMARY tt ALL NULL NULL NULL NULL # 100.00 Using where
1758
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id # 100.00 Using where; Using index
1597
1759
Warnings:
1598
1760
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1599
1761
Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
1600
1762
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1601
create table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1763
create temporary table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1602
1764
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');
1603
1765
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);
1604
1766
id text id text id text
1618
1780
1001 text1001 NULL NULL 1000 text1000
1619
1781
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);
1620
1782
id select_type table type possible_keys key key_len ref rows filtered Extra
1621
1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00
1622
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00
1623
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
1783
1 SIMPLE a ALL NULL NULL NULL NULL # 100.00
1784
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id # 100.00
1785
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func # 100.00 Using where
1624
1786
Warnings:
1625
1787
Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`)
1626
1788
drop table t1,t2;
1627
create table t1 (a int) ENGINE=MyISAM;
1789
#
1790
# Static tables & rund() in subqueries
1791
#
1792
create temporary table t1 (a int) ENGINE=MyISAM;
1628
1793
insert into t1 values (1);
1629
1794
explain select benchmark(1000, (select a from t1 where a=rand()));
1630
1795
id select_type table type possible_keys key key_len ref rows Extra
1631
1796
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
1632
1797
2 SUBQUERY t1 system NULL NULL NULL NULL 1
1633
1798
drop table t1;
1634
create table t1(id int) ENGINE=MyISAM;
1635
create table t2(id int) ENGINE=MyISAM;
1636
create table t3(flag int) ENGINE=MyISAM;
1799
#
1800
# bug 3188
1801
#
1802
create temporary table t1(id int) ENGINE=MyISAM;
1803
create temporary table t2(id int) ENGINE=MyISAM;
1804
create temporary table t3(flag int) ENGINE=MyISAM;
1637
1805
select (select * from t3 where id not null) from t1, t2;
1638
1806
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'null) from t1, t2' at line 1
1639
1807
drop table t1,t2,t3;
1640
CREATE TABLE t1 (id INT) ENGINE=MyISAM;
1641
CREATE TABLE t2 (id INT) ENGINE=MyISAM;
1808
#
1809
# aggregate functions (Bug #3505)
1810
#
1811
CREATE TABLE t1 (id INT);
1812
CREATE TABLE t2 (id INT);
1642
1813
INSERT INTO t1 VALUES (1), (2);
1643
1814
INSERT INTO t2 VALUES (1);
1644
1815
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1658
1829
1 1
1659
1830
2 0
1660
1831
DROP TABLE t1,t2;
1661
CREATE TABLE t1 ( a int, b int ) ENGINE=MyISAM;
1832
#
1833
# ALL/ANY test
1834
#
1835
CREATE TABLE t1 ( a int, b int );
1662
1836
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1663
1837
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1664
1838
a
1702
1876
a
1703
1877
1
1704
1878
3
1879
# with index
1705
1880
ALTER TABLE t1 ADD INDEX (a);
1706
1881
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1707
1882
a
1745
1920
a
1746
1921
1
1747
1922
3
1923
# having clause test
1748
1924
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1749
1925
a
1750
1926
3
1787
1963
a
1788
1964
1
1789
1965
3
1966
# union test
1790
1967
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1791
1968
a
1792
1969
3
1829
2006
a
1830
2007
1
1831
2008
3
2009
# union + having test
1832
2010
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1833
2011
a
1834
2012
3
1871
2049
a
1872
2050
1
1873
2051
3
2052
# row tests
2053
# < > >= <= and = ALL/ <> ANY do not support row operation
1874
2054
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1875
2055
ERROR 21000: Operand should contain 1 column(s)
1876
2056
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1887
2067
ERROR 21000: Operand should contain 1 column(s)
1888
2068
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
1889
2069
ERROR 21000: Operand should contain 1 column(s)
2070
# following should be converted to IN
1890
2071
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
1891
2072
ERROR 21000: Operand should contain 2 column(s)
1892
2073
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1930
2111
a
1931
2112
1
1932
2113
3
2114
# without optimisation
1933
2115
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1934
2116
a
1935
2117
3
1972
2154
a
1973
2155
1
1974
2156
3
2157
# without optimisation + having
1975
2158
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
1976
2159
a
1977
2160
3
2014
2197
a
2015
2198
1
2016
2199
3
2200
# EXISTS in string contence
2017
2201
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
2018
2202
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
2019
2203
0-
2030
2214
1-
2031
2215
0-
2032
2216
DROP TABLE t1;
2033
CREATE TABLE t1 ( a double, b double ) ENGINE=MyISAM;
2217
CREATE TABLE t1 ( a double, b double );
2034
2218
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2035
2219
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
2036
2220
a
2075
2259
1
2076
2260
3
2077
2261
DROP TABLE t1;
2078
CREATE TABLE t1 ( a char(1), b char(1)) ENGINE=MyISAM;
2262
CREATE TABLE t1 ( a char(1), b char(1));
2079
2263
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
2080
2264
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
2081
2265
a
2120
2304
1
2121
2305
3
2122
2306
DROP TABLE t1;
2123
create table t1 (a int, b int) ENGINE=MyISAM;
2307
#
2308
# SELECT(EXISTS * ...)optimisation
2309
#
2310
create table t1 (a int, b int);
2124
2311
insert into t1 values (1,2),(3,4);
2125
2312
select * from t1 up where exists (select * from t1 where t1.a=up.a);
2126
2313
a b
2128
2315
3 4
2129
2316
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
2130
2317
id select_type table type possible_keys key key_len ref rows filtered Extra
2131
1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
2132
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2318
1 PRIMARY up ALL NULL NULL NULL NULL # 100.00 Using where
2319
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00 Using where
2133
2320
Warnings:
2134
2321
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
2135
2322
Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
2136
2323
drop table t1;
2137
CREATE TABLE t1 (t1_a int) ENGINE=MyISAM;
2324
#
2325
# Bug #4102: subselect in HAVING
2326
#
2327
CREATE TEMPORARY TABLE t1 (t1_a int) ENGINE=MyISAM;
2138
2328
INSERT INTO t1 VALUES (1);
2139
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b)) ENGINE=MyISAM;
2329
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
2140
2330
INSERT INTO t2 VALUES (1, 1), (1, 2);
2141
2331
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
2142
2332
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
2143
2333
t1_a t2_a t2_b
2144
2334
1 1 2
2145
2335
DROP TABLE t1, t2;
2146
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
2336
#
2337
# Test problem with NULL and derived tables (Bug #4097)
2338
#
2339
CREATE TEMPORARY TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
2147
2340
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
2148
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
2341
CREATE TEMPORARY TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
2149
2342
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
2150
2343
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
2151
2344
id name id pet
2153
2346
2 Rebecca 2 Spot
2154
2347
3 NULL 3 Felix
2155
2348
drop table t1,t2;
2156
CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
2349
#
2350
# Aggregate function comparation with ALL/ANY/SOME subselect
2351
#
2352
CREATE TEMPORARY TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
2157
2353
insert into t1 values (1);
2158
CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
2354
CREATE TEMPORARY TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
2159
2355
insert into t2 values (1,2);
2160
2356
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2161
2357
a C
2162
2358
1 1
2163
2359
drop table t1,t2;
2164
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) ENGINE=MyISAM;
2360
#
2361
# BUG#5003 - like in subselect
2362
#
2363
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);
2165
2364
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2166
2365
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2167
2366
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
2171
2370
IZAVORGANG_ID
2172
2371
D0000000001
2173
2372
drop table t1;
2174
CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2175
CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2373
#
2374
# Optimized IN with compound index
2375
#
2376
CREATE TEMPORARY TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2377
CREATE TEMPORARY TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2176
2378
insert into t1 values (1,1),(1,2),(2,1),(2,2);
2177
2379
insert into t2 values (1,2),(2,2);
2178
2380
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2192
2394
1 1
2193
2395
2 1
2194
2396
drop table t1,t2;
2195
CREATE TABLE t1 (howmanyvalues bigint, avalue int) ENGINE=MyISAM;
2397
#
2398
# resolving fields of grouped outer SELECT
2399
#
2400
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
2196
2401
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
2197
2402
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
2198
2403
howmanyvalues count(*)
2226
2431
3 1
2227
2432
4 1
2228
2433
drop table t1;
2229
create table t1 (x int) ENGINE=MyISAM;
2434
create table t1 (x int);
2230
2435
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;
2231
2436
(select b.x from t1 as b where b.x=a.x)
2232
2437
drop table t1;
2233
CREATE 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;
2438
#
2439
# Test of correct maybe_null flag returning by subquwery for temporary table
2440
# creation
2441
#
2442
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;
2234
2443
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);
2235
CREATE 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 ;
2444
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 ;
2236
2445
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');
2237
2446
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;
2238
2447
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2241
2450
700
2242
2451
NULL
2243
2452
drop tables t1,t2;
2453
#
2454
# Subselect in non-select command just after connection
2455
#
2244
2456
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
2245
create table t1 (a int, b int) ENGINE=MyISAM;
2246
create table t2 (a int, b int) ENGINE=MyISAM;
2457
#
2458
# primary query with temporary table and subquery with groupping
2459
#
2460
create temporary table t1 (a int, b int) ENGINE=MyISAM;
2461
create temporary table t2 (a int, b int) ENGINE=MyISAM;
2247
2462
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
2248
2463
insert into t2 values (1,3),(2,1);
2249
2464
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
2254
2469
2 4 NULL
2255
2470
2 5 NULL
2256
2471
drop table t1, t2;
2257
create table t1 (s1 int,s2 int) ENGINE=MyISAM;
2472
#
2473
# Equal operation under row and empty subquery
2474
#
2475
create table t1 (s1 int,s2 int);
2258
2476
insert into t1 values (20,15);
2259
2477
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
2260
2478
s1 s2
2261
2479
drop table t1;
2262
create table t1 (s1 int) ENGINE=MyISAM;
2480
#
2481
# ALL/ANY with NULL
2482
#
2483
create table t1 (s1 int);
2263
2484
insert into t1 values (1),(null);
2264
2485
select * from t1 where s1 < all (select s1 from t1);
2265
2486
s1
2268
2489
1 0
2269
2490
NULL NULL
2270
2491
drop table t1;
2492
#
2493
# reference on changable fields from subquery
2494
#
2271
2495
CREATE TABLE t1 (
2272
2496
Code char(3) NOT NULL default '',
2273
2497
Name char(52) NOT NULL default '',
2284
2508
HeadOfState char(60) default NULL,
2285
2509
Capital int default NULL,
2286
2510
Code2 char(2) NOT NULL default ''
2287
) ENGINE=MyISAM;
2511
);
2288
2512
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2289
2513
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');
2290
2514
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');
2291
2515
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
2292
2516
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
2517
Warnings:
2518
Note 1031 Table storage engine for 't1' doesn't have this option
2293
2519
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
2294
2520
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
2295
2521
Population < 200);
2296
2522
c
2297
2523
Oceania
2298
2524
drop table t1;
2299
create table t1 (a1 int) ENGINE=MyISAM;
2300
create table t2 (b1 int) ENGINE=MyISAM;
2525
#
2526
# Test for BUG#7885: Server crash when 'any' subselect compared to
2527
# non-existant field.
2528
#
2529
create temporary table t1 (a1 int) ENGINE=MyISAM;
2530
create temporary table t2 (b1 int) ENGINE=MyISAM;
2301
2531
select * from t1 where a2 > any(select b1 from t2);
2302
2532
ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
2303
2533
select * from t1 where a1 > any(select b1 from t2);
2304
2534
a1
2305
2535
drop table t1,t2;
2306
create table t1 (a integer, b integer) ENGINE=MyISAM;
2536
#
2537
# Comparison subquery with * and row
2538
#
2539
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
2307
2540
select (select * from t1) = (select 1,2);
2308
2541
(select * from t1) = (select 1,2)
2309
2542
NULL
2310
2543
select (select 1,2) = (select * from t1);
2311
2544
(select 1,2) = (select * from t1)
2312
2545
NULL
2546
# queries whih can be converted to IN
2313
2547
select row(1,2) = ANY (select * from t1);
2314
2548
row(1,2) = ANY (select * from t1)
2315
2549
0
2317
2551
row(1,2) != ALL (select * from t1)
2318
2552
1
2319
2553
drop table t1;
2320
create table t1 (a integer, b integer) ENGINE=MyISAM;
2554
#
2555
# Comparison subquery and row with nested rows
2556
#
2557
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
2321
2558
select row(1,(2,2)) in (select * from t1 );
2322
2559
ERROR 21000: Operand should contain 2 column(s)
2323
2560
select row(1,(2,2)) = (select * from t1 );
2325
2562
select (select * from t1) = row(1,(2,2));
2326
2563
ERROR 21000: Operand should contain 1 column(s)
2327
2564
drop table t1;
2565
#
2566
# Forward reference detection
2567
#
2328
2568
create table t1 (a integer);
2329
2569
insert into t1 values (1);
2330
2570
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
2337
2577
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2338
2578
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2339
2579
drop table t1;
2340
CREATE TABLE t1 (
2580
#
2581
# Test for BUG#8218
2582
#
2583
CREATE TEMPORARY TABLE t1 (
2341
2584
categoryId int NOT NULL,
2342
2585
courseId int NOT NULL,
2343
2586
startDate datetime NOT NULL,
2355
2598
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2356
2599
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2357
2600
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
2358
CREATE TABLE t2 (
2601
CREATE TEMPORARY TABLE t2 (
2359
2602
userId int NOT NULL,
2360
2603
courseId int NOT NULL,
2361
2604
date datetime NOT NULL)
2366
2609
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
2367
2610
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
2368
2611
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
2369
CREATE TABLE t3 (
2612
CREATE TEMPORARY TABLE t3 (
2370
2613
groupId int NOT NULL,
2371
2614
parentId int NOT NULL,
2372
2615
startDate datetime NOT NULL,
2376
2619
ordering int)
2377
2620
engine=myisam;
2378
2621
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
2379
CREATE TABLE t4 (
2622
CREATE TEMPORARY TABLE t4 (
2380
2623
id int NOT NULL,
2381
2624
groupTypeId int NOT NULL,
2382
2625
groupKey varchar(50) NOT NULL,
2388
2631
engine=myisam;
2389
2632
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
2390
2633
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
2391
CREATE TABLE t5 (
2634
CREATE TEMPORARY TABLE t5 (
2392
2635
userId int NOT NULL,
2393
2636
groupId int NOT NULL,
2394
2637
createDate datetime NOT NULL,
2436
2679
1 5141 12 group2 12 group2 5 1 2 88 Oct04
2437
2680
1 5141 12 group2 12 group2 5 1 2 89 Oct04
2438
2681
drop table t1, t2, t3, t4, t5;
2439
create table t1 (a int) ENGINE=MyISAM;
2682
#
2683
# Transformation in left expression of subquery (BUG#8888)
2684
#
2685
create temporary table t1 (a int) ENGINE=MyISAM;
2440
2686
insert into t1 values (1), (2), (3);
2441
2687
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
2442
2688
1
2444
2690
1
2445
2691
1
2446
2692
drop table t1;
2447
create table t1 (a int, b int) ENGINE=MyISAM;
2693
#
2694
# single row subqueries and row operations (code covarage improvement)
2695
#
2696
create temporary table t1 (a int, b int) ENGINE=MyISAM;
2448
2697
insert into t1 values (1,2);
2449
2698
select 1 = (select * from t1);
2450
2699
ERROR 21000: Operand should contain 1 column(s)
2459
2708
select (select * from t1) = (1,2,3);
2460
2709
ERROR 21000: Operand should contain 2 column(s)
2461
2710
drop table t1;
2462
create table t1 (fld enum('0','1')) ENGINE=MyISAM;
2711
#
2712
# Item_int_with_ref check (BUG#10020)
2713
#
2714
#CREATE TABLE `t1` (
2715
# `itemid` bigint NOT NULL auto_increment,
2716
# `sessionid` bigint default NULL,
2717
# `time` int NOT NULL default '0',
2718
# `data` text collate latin1_general_ci NOT NULL,
2719
# PRIMARY KEY (`itemid`)
2720
#);
2721
#INSERT INTO `t1` VALUES (1, 1, 1, '');
2722
#CREATE TABLE `t2` (
2723
# `sessionid` bigint NOT NULL auto_increment,
2724
# `pid` int NOT NULL default '0',
2725
# `date` int NOT NULL default '0',
2726
# `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
2727
# PRIMARY KEY (`sessionid`)
2728
#);
2729
#INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
2730
#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;
2731
#drop tables t1,t2;
2732
# BUG#11821 : Select from subselect using aggregate function on an enum
2733
# segfaults:
2734
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
2463
2735
insert into t1 values ('1');
2464
2736
select * from (select max(fld) from t1) as foo;
2465
2737
max(fld)
2466
2738
1
2467
2739
drop table t1;
2468
CREATE TABLE t1 (one int, two int, flag char(1)) ENGINE=MyISAM;
2469
CREATE TABLE t2 (one int, two int, flag char(1)) ENGINE=MyISAM;
2740
#
2741
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
2742
#
2743
CREATE TABLE t1 (one int, two int, flag char(1));
2744
CREATE TABLE t2 (one int, two int, flag char(1));
2470
2745
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2471
2746
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2472
2747
SELECT * FROM t1
2549
2824
Warnings:
2550
2825
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
2551
2826
DROP TABLE t1,t2;
2552
CREATE TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
2827
#
2828
# Bug #12392: where cond with IN predicate for rows and NULL values in table
2829
#
2830
CREATE TEMPORARY TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
2553
2831
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2554
2832
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
2555
2833
a b
2556
2834
aaa aaa
2557
2835
DROP TABLE t1;
2558
CREATE TABLE t1 (a int) ENGINE=MyISAM;
2559
CREATE TABLE t2 (a int, b int) ENGINE=MyISAM;
2560
CREATE TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
2836
#
2837
# Bug #11479: subquery over left join with an empty inner table
2838
#
2839
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
2840
CREATE TEMPORARY TABLE t2 (a int, b int) ENGINE=MyISAM;
2841
CREATE TEMPORARY TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
2561
2842
INSERT INTO t1 VALUES (1), (2), (3), (4);
2562
2843
INSERT INTO t2 VALUES (1,10), (3,30);
2563
2844
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2572
2853
3
2573
2854
4
2574
2855
DROP TABLE t1,t2,t3;
2575
CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
2576
CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
2856
#
2857
# Bug#18503: Queries with a quantified subquery returning empty set may
2858
# return a wrong result.
2859
#
2860
CREATE TEMPORARY TABLE t1 (f1 INT) ENGINE=MyISAM;
2861
CREATE TEMPORARY TABLE t2 (f2 INT) ENGINE=MyISAM;
2577
2862
INSERT INTO t1 VALUES (1);
2578
2863
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
2579
2864
f1
2587
2872
f1
2588
2873
1
2589
2874
DROP TABLE t1, t2;
2590
create table t1 (s1 char) ENGINE=MyISAM;
2875
# BUG#20975 Wrong query results for subqueries within NOT
2876
create table t1 (s1 char);
2591
2877
insert into t1 values (1),(2);
2592
2878
select * from t1 where (s1 < any (select s1 from t1));
2593
2879
s1
2614
2900
s1
2615
2901
2
2616
2902
drop table t1;
2903
#
2904
# Bug #16255: Subquery in where
2905
#
2617
2906
create table t1 (
2618
2907
retailerID varchar(8) NOT NULL,
2619
2908
statusID int NOT NULL,
2620
2909
changed datetime NOT NULL,
2621
UNIQUE KEY retailerID (retailerID, statusID, changed))
2622
ENGINE=MyISAM;
2910
UNIQUE KEY retailerID (retailerID, statusID, changed));
2623
2911
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
2624
2912
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
2625
2913
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
2636
2924
0048 1 2006-01-06 12:37:50
2637
2925
0059 1 2006-01-06 12:37:50
2638
2926
drop table t1;
2639
create table t1(a int, primary key (a)) ENGINE=MyISAM;
2927
#
2928
# Bug #21180: Subselect with index for both WHERE and ORDER BY
2929
# produces empty result
2930
#
2931
create table t1(a int, primary key (a));
2640
2932
insert into t1 values (10);
2641
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)) ENGINE=MyISAM;
2933
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
2642
2934
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
2643
2935
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2644
2936
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2645
2937
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2646
2938
id select_type table type possible_keys key key_len ref rows Extra
2647
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2648
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2649
2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where
2939
1 PRIMARY t1 const PRIMARY PRIMARY 4 const # Using index
2940
1 PRIMARY r const PRIMARY PRIMARY 4 const #
2941
2 DEPENDENT SUBQUERY t2 ref b b 5 # Using where; Using index
2650
2942
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2651
2943
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2652
2944
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2656
2948
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2657
2949
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2658
2950
id select_type table type possible_keys key key_len ref rows Extra
2659
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2951
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index
2660
2952
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2661
2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where; Using MRR
2953
2 DEPENDENT SUBQUERY t2 ref b b 5 1 Using where; Using index
2662
2954
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2663
2955
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2664
2956
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2665
2957
a a b
2666
2958
10 1 359
2667
2959
drop table t1,t2;
2668
CREATE TABLE t1 (
2960
#
2961
# Bug #21853: assert failure for a grouping query with
2962
# an ALL/ANY quantified subquery in HAVING
2963
#
2964
CREATE TEMPORARY TABLE t1 (
2669
2965
field1 int NOT NULL,
2670
2966
field2 int NOT NULL,
2671
2967
field3 int NOT NULL,
2672
2968
PRIMARY KEY (field1,field2,field3))
2673
2969
ENGINE=MyISAM;
2674
CREATE TABLE t2 (
2970
CREATE TEMPORARY TABLE t2 (
2675
2971
fieldA int NOT NULL,
2676
2972
fieldB int NOT NULL,
2677
2973
PRIMARY KEY (fieldA,fieldB))
2701
2997
1 1
2702
2998
1 3
2703
2999
DROP TABLE t1, t2;
2704
CREATE TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
3000
#
3001
# Bug #23478: not top-level IN subquery returning a non-empty result set
3002
# with possible NULL values by index access from the outer query
3003
#
3004
CREATE TEMPORARY TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
2705
3005
INSERT INTO t1 VALUES (1), (3), (5), (7);
2706
3006
INSERT INTO t1 VALUES (NULL);
2707
CREATE TABLE t2(a int) ENGINE=MyISAM;
3007
CREATE TEMPORARY TABLE t2(a int) ENGINE=MyISAM;
2708
3008
INSERT INTO t2 VALUES (1),(2),(3);
2709
3009
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
2710
3010
id select_type table type possible_keys key key_len ref rows Extra
2716
3016
2 NULL
2717
3017
3 1
2718
3018
DROP TABLE t1,t2;
2719
CREATE TABLE t1 (a DATETIME) ENGINE=MyISAM;
3019
#
3020
# Bug #11302: getObject() returns a String for a sub-query of type datetime
3021
#
3022
CREATE TABLE t1 (a DATETIME);
2720
3023
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
2721
CREATE TABLE t2 ENGINE=MyISAM AS SELECT
3024
CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT
2722
3025
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
2723
3026
FROM t1 WHERE a > '2000-01-01';
2724
3027
SHOW CREATE TABLE t2;
2725
3028
Table Create Table
2726
t2 CREATE TABLE `t2` (
3029
t2 CREATE TEMPORARY TABLE `t2` (
2727
3030
`sub_a` datetime DEFAULT NULL
2728
3031
) ENGINE=MyISAM
2729
CREATE TABLE t3 ENGINE=MyISAM AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
3032
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');
2730
3033
SHOW CREATE TABLE t3;
2731
3034
Table Create Table
2732
t3 CREATE TABLE `t3` (
3035
t3 CREATE TEMPORARY TABLE `t3` (
2733
3036
`a` datetime DEFAULT NULL
2734
3037
) ENGINE=MyISAM
2735
3038
DROP TABLE t1,t2,t3;
2736
CREATE TABLE t1 (a int) ENGINE=MyISAM;
3039
#
3040
# Bug 24653: sorting by expressions containing subselects
3041
# that return more than one row
3042
#
3043
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
2737
3044
INSERT INTO t1 VALUES (2), (4), (1), (3);
2738
CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
3045
CREATE TABLE t2 (b int, c int);
2739
3046
INSERT INTO t2 VALUES
2740
3047
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
2741
3048
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
2807
3114
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2808
3115
ERROR 21000: Subquery returns more than 1 row
2809
3116
DROP TABLE t1,t2;
2810
create table t1 (df decimal(5,1)) ENGINE=MyISAM;
3117
# End of 4.1 tests
3118
#
3119
#decimal-related tests
3120
#
3121
create table t1 (df decimal(5,1));
2811
3122
insert into t1 values(1.1);
2812
3123
insert into t1 values(2.2);
2813
3124
select * from t1 where df <= all (select avg(df) from t1 group by df);
2817
3128
df
2818
3129
2.2
2819
3130
drop table t1;
2820
create table t1 (df decimal(5,1)) ENGINE=MyISAM;
3131
create temporary table t1 (df decimal(5,1)) ENGINE=MyISAM;
2821
3132
insert into t1 values(1.1);
2822
3133
select 1.1 * exists(select * from t1);
2823
3134
1.1 * exists(select * from t1)
2824
3135
1.1
2825
3136
drop table t1;
2826
CREATE TABLE t1 (
3137
CREATE TEMPORARY TABLE t1 (
2827
3138
grp int default NULL,
2828
3139
a decimal(10,2) default NULL) ENGINE=MyISAM;
2829
3140
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
2843
3154
2.00
2844
3155
4.00
2845
3156
drop table t1;
2846
CREATE table t1 ( c1 integer ) ENGINE=MyISAM;
3157
#
3158
# Test for bug #9338: lame substitution of c1 instead of c2
3159
#
3160
CREATE temporary table t1 ( c1 integer ) ENGINE=MyISAM;
2847
3161
INSERT INTO t1 VALUES ( 1 );
2848
3162
INSERT INTO t1 VALUES ( 2 );
2849
3163
INSERT INTO t1 VALUES ( 3 );
2850
CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;
3164
CREATE TABLE t2 ( c2 integer );
2851
3165
INSERT INTO t2 VALUES ( 1 );
2852
3166
INSERT INTO t2 VALUES ( 4 );
2853
3167
INSERT INTO t2 VALUES ( 5 );
2859
3173
c1 c2
2860
3174
1 1
2861
3175
DROP TABLE t1,t2;
2862
CREATE TABLE t1 ( c1 integer ) ENGINE=MyISAM;
3176
#
3177
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ
3178
#
3179
CREATE TEMPORARY TABLE t1 ( c1 integer ) ENGINE=MyISAM;
2863
3180
INSERT INTO t1 VALUES ( 1 );
2864
3181
INSERT INTO t1 VALUES ( 2 );
2865
3182
INSERT INTO t1 VALUES ( 3 );
2866
3183
INSERT INTO t1 VALUES ( 6 );
2867
CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;
3184
CREATE TEMPORARY TABLE t2 ( c2 integer ) ENGINE=MyISAM;
2868
3185
INSERT INTO t2 VALUES ( 1 );
2869
3186
INSERT INTO t2 VALUES ( 4 );
2870
3187
INSERT INTO t2 VALUES ( 5 );
2871
3188
INSERT INTO t2 VALUES ( 6 );
2872
CREATE TABLE t3 ( c3 integer ) ENGINE=MyISAM;
3189
CREATE TEMPORARY TABLE t3 ( c3 integer ) ENGINE=MyISAM;
2873
3190
INSERT INTO t3 VALUES ( 7 );
2874
3191
INSERT INTO t3 VALUES ( 8 );
2875
3192
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
2878
3195
2 NULL
2879
3196
3 NULL
2880
3197
DROP TABLE t1,t2,t3;
2881
CREATE TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;
2882
CREATE TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;
3198
#
3199
# Correct building of equal fields list (do not include outer
3200
# fields) (BUG#6384)
3201
#
3202
CREATE TEMPORARY TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;
3203
CREATE TEMPORARY TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;
2883
3204
INSERT INTO t1 VALUES ('E1'),('E2');
2884
3205
INSERT INTO t2 VALUES ('E1');
2885
3206
DELETE FROM t1
2891
3212
EMPNUM
2892
3213
E1
2893
3214
DROP TABLE t1,t2;
2894
CREATE TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
3215
#
3216
# Test for bug #11487: range access in a subquery
3217
#
3218
CREATE TEMPORARY TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
2895
3219
INSERT INTO t1 VALUES (1, 1);
2896
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
3220
CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT,
2897
3221
PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;
2898
3222
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
2899
3223
SELECT values_id FROM t1
2912
3236
values_id
2913
3237
1
2914
3238
DROP TABLE t1, t2;
2915
create table t1 (fld enum('0','1')) ENGINE=MyISAM;
3239
# BUG#11821 : Select from subselect using aggregate function on an enum
3240
# segfaults:
3241
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
2916
3242
insert into t1 values ('1');
2917
3243
select * from (select max(fld) from t1) as foo;
2918
3244
max(fld)
2919
3245
1
2920
3246
drop table t1;
2921
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
2922
CREATE TABLE t2 (c int, d int) ENGINE=MyISAM;
2923
CREATE TABLE t3 (e int) ENGINE=MyISAM;
3247
#
3248
# Test for bug #11762: subquery with an aggregate function in HAVING
3249
#
3250
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3251
CREATE TEMPORARY TABLE t2 (c int, d int) ENGINE=MyISAM;
3252
CREATE TEMPORARY TABLE t3 (e int) ENGINE=MyISAM;
2924
3253
INSERT INTO t1 VALUES
2925
3254
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
2926
3255
INSERT INTO t2 VALUES
3061
3390
3 20
3062
3391
4 40
3063
3392
DROP TABLE t1,t2,t3;
3064
CREATE TABLE t1 (a varchar(5), b varchar(10)) ENGINE=MyISAM;
3393
#
3394
# Test for bug #16603: GROUP BY in a row subquery with a quantifier
3395
# when an index is defined on the grouping field
3396
CREATE TABLE t1 (a varchar(5), b varchar(10));
3065
3397
INSERT INTO t1 VALUES
3066
3398
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
3067
3399
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
3073
3405
EXPLAIN
3074
3406
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3075
3407
id select_type table type possible_keys key key_len ref rows Extra
3076
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3077
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3408
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
3409
2 SUBQUERY t1 ALL NULL NULL NULL NULL # Using temporary; Using filesort
3078
3410
ALTER TABLE t1 ADD INDEX(a);
3079
3411
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3080
3412
a b
3084
3416
EXPLAIN
3085
3417
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3086
3418
id select_type table type possible_keys key key_len ref rows Extra
3087
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3088
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3419
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
3420
2 SUBQUERY t1 index NULL a 23 NULL #
3089
3421
DROP TABLE t1;
3090
create table t1( f1 int,f2 int) ENGINE=MyISAM;
3422
#
3423
# Bug#17366: Unchecked Item_int results in server crash
3424
#
3425
create table t1( f1 int,f2 int);
3091
3426
insert into t1 values (1,1),(2,2);
3092
3427
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';
3093
3428
t
3094
3429
crash1
3095
3430
crash1
3096
3431
drop table t1;
3097
create table t1 (c int, key(c)) ENGINE=MyISAM;
3432
#
3433
# Bug #18306: server crash on delete using subquery.
3434
#
3435
create temporary table t1 (c int, key(c)) ENGINE=MyISAM;
3098
3436
insert into t1 values (1142477582), (1142455969);
3099
create table t2 (a int, b int) ENGINE=MyISAM;
3437
create temporary table t2 (a int, b int) ENGINE=MyISAM;
3100
3438
insert into t2 values (2, 1), (1, 0);
3101
3439
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
3102
3440
drop table t1, t2;
3103
create table t1 (i int, j bigint) ENGINE=MyISAM;
3441
#
3442
# Bug#19077: A nested materialized derived table is used before being populated.
3443
#
3444
create table t1 (i int, j bigint);
3104
3445
insert into t1 values (1, 2), (2, 2), (3, 2);
3105
3446
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
3106
3447
min(i)
3107
3448
1
3108
3449
drop table t1;
3109
CREATE TABLE t1 (i BIGINT) ENGINE=MyISAM;
3450
#
3451
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
3452
#
3453
CREATE TEMPORARY TABLE t1 (i BIGINT) ENGINE=MyISAM;
3110
3454
INSERT INTO t1 VALUES (10000000000000000);
3111
3455
INSERT INTO t1 VALUES (1);
3112
CREATE TABLE t2 (i BIGINT) ENGINE=MyISAM;
3456
CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;
3113
3457
INSERT INTO t2 VALUES (10000000000000000);
3114
3458
INSERT INTO t2 VALUES (1);
3115
3459
/* simple test */
3127
3471
10000000000000000
3128
3472
DROP TABLE t1;
3129
3473
DROP TABLE t2;
3130
CREATE TABLE t1 (
3474
#
3475
# Bug#20519: subselect with LIMIT M, N
3476
#
3477
CREATE TEMPORARY TABLE t1 (
3131
3478
id bigint NOT NULL auto_increment,
3132
3479
name varchar(255) NOT NULL,
3133
3480
PRIMARY KEY (id))
3138
3485
id bigint NOT NULL auto_increment,
3139
3486
mid bigint NOT NULL,
3140
3487
date date NOT NULL,
3141
PRIMARY KEY (id))
3142
ENGINE=MyISAM;
3488
PRIMARY KEY (id));
3143
3489
INSERT INTO t2 VALUES
3144
3490
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
3145
3491
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
3172
3518
2 Joe 2006-04-20 2006-04-06
3173
3519
3 Frank 2006-04-13 NULL
3174
3520
DROP TABLE t1,t2;
3521
#
3522
# Bug#20869: subselect with range access by DESC
3523
#
3175
3524
CREATE TABLE t1 (
3176
3525
i1 int NOT NULL default '0',
3177
3526
i2 int NOT NULL default '0',
3178
3527
t datetime,
3179
PRIMARY KEY (i1,i2,t))
3180
ENGINE=MyISAM;
3528
PRIMARY KEY (i1,i2,t));
3181
3529
INSERT INTO t1 VALUES
3182
3530
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
3183
3531
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
3189
3537
i1 int NOT NULL default '0',
3190
3538
i2 int NOT NULL default '0',
3191
3539
t datetime default NULL,
3192
PRIMARY KEY (i1))
3193
ENGINE=MyISAM;
3540
PRIMARY KEY (i1));
3194
3541
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
3195
3542
EXPLAIN
3196
3543
SELECT * FROM t1,t2
3198
3545
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3199
3546
ORDER BY t1.t DESC LIMIT 1);
3200
3547
id select_type table type possible_keys key key_len ref rows Extra
3201
1 PRIMARY t2 system NULL NULL NULL NULL 1
3202
1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
3203
2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
3548
1 PRIMARY t2 ALL NULL NULL NULL NULL 1
3549
1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index; Using join buffer
3550
2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 5 Using where; Using index; Using filesort
3204
3551
SELECT * FROM t1,t2
3205
3552
WHERE t1.t = (SELECT t1.t FROM t1
3206
3553
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3208
3555
i1 i2 t i1 i2 t
3209
3556
24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40
3210
3557
DROP TABLE t1, t2;
3211
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
3558
#
3559
# Bug#14654 : Cannot select from the same table twice within a UNION
3560
# statement
3561
#
3562
CREATE TABLE t1 (i INT);
3212
3563
(SELECT i FROM t1) UNION (SELECT i FROM t1);
3213
3564
i
3214
3565
SELECT * FROM t1 WHERE NOT EXISTS
3217
3568
(SELECT i FROM t1)
3218
3569
);
3219
3570
i
3571
#TODO:not supported
3220
3572
SELECT * FROM t1
3221
3573
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3222
3574
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1
3575
#TODO:not supported
3223
3576
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3224
3577
from t1;
3225
3578
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'union (select t12.i from t1 t12))
3227
3580
explain select * from t1 where not exists
3228
3581
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3229
3582
id select_type table type possible_keys key key_len ref rows Extra
3230
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
3231
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3232
3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3233
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
3583
1 PRIMARY t1 ALL NULL NULL NULL NULL #
3584
2 SUBQUERY t11 ALL NULL NULL NULL NULL #
3585
3 UNION t12 ALL NULL NULL NULL NULL #
3586
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL #
3234
3587
DROP TABLE t1;
3235
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
3236
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
3588
#
3589
# Bug #21540: Subqueries with no from and aggregate functions return
3590
# wrong results
3591
CREATE TABLE t1 (a INT, b INT);
3592
CREATE TABLE t2 (a INT);
3237
3593
INSERT INTO t2 values (1);
3238
3594
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
3239
3595
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
3272
3628
2
3273
3629
2
3274
3630
DROP TABLE t1,t2;
3275
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;
3276
CREATE TABLE t2 (x int auto_increment, y int, z int,
3631
#
3632
# Bug #21727: Correlated subquery that requires filesort:
3633
# slow with big sort_buffer_size
3634
#
3635
CREATE TEMPORARY TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;
3636
CREATE TEMPORARY TABLE t2 (x int auto_increment, y int, z int,
3277
3637
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;
3278
3638
SET SESSION sort_buffer_size = 32 * 1024;
3279
3639
Warnings:
3290
3650
COUNT(*)
3291
3651
3000
3292
3652
DROP TABLE t2,t1;
3293
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int) ENGINE=MyISAM;
3294
CREATE TABLE t2 (c int) ENGINE=MyISAM;
3653
#
3654
# Bug #25219: EXIST subquery with UNION over a mix of
3655
# correlated and uncorrelated selects
3656
#
3657
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
3658
CREATE TABLE t2 (c int);
3295
3659
INSERT INTO t1 VALUES ('aa', 1);
3296
3660
INSERT INTO t2 VALUES (1);
3297
3661
SELECT * FROM t1
3311
3675
cc 3
3312
3676
dd 1
3313
3677
INSERT INTO t2 VALUES (2);
3314
CREATE TABLE t3 (c int) ENGINE=MyISAM;
3678
CREATE TEMPORARY TABLE t3 (c int) ENGINE=MyISAM;
3315
3679
INSERT INTO t3 VALUES (1);
3316
3680
SELECT * FROM t1
3317
3681
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
3323
3687
cc 3
3324
3688
dd 1
3325
3689
DROP TABLE t1,t2,t3;
3690
#
3691
# Bug#21904 (parser problem when using IN with a double "(())")
3692
#
3326
3693
DROP TABLE IF EXISTS t1;
3327
3694
DROP TABLE IF EXISTS t2;
3328
3695
DROP TABLE IF EXISTS t1xt2;
3329
CREATE TABLE t1 (
3696
CREATE TEMPORARY TABLE t1 (
3330
3697
id_1 int NOT NULL,
3331
3698
t varchar(4) DEFAULT NULL)
3332
3699
ENGINE=MyISAM;
3333
CREATE TABLE t2 (
3700
CREATE TEMPORARY TABLE t2 (
3334
3701
id_2 int NOT NULL,
3335
3702
t varchar(4) DEFAULT NULL)
3336
3703
ENGINE=MyISAM;
3337
CREATE TABLE t1xt2 (
3704
CREATE TEMPORARY TABLE t1xt2 (
3338
3705
id_1 int NOT NULL,
3339
3706
id_2 int NOT NULL)
3340
3707
ENGINE=MyISAM;
3341
3708
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
3342
3709
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
3343
3710
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
3711
# subselect returns 0 rows
3344
3712
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3345
3713
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3346
3714
id_1
3372
3740
3
3373
3741
4
3374
3742
insert INTO t1xt2 VALUES (1, 12);
3743
# subselect returns 1 row
3375
3744
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3376
3745
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3377
3746
id_1
3403
3772
3
3404
3773
4
3405
3774
insert INTO t1xt2 VALUES (2, 12);
3775
# subselect returns more than 1 row
3406
3776
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3407
3777
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3408
3778
id_1
3436
3806
DROP TABLE t1;
3437
3807
DROP TABLE t2;
3438
3808
DROP TABLE t1xt2;
3439
CREATE TABLE t1 (a int) ENGINE=MyISAM;
3809
#
3810
# Bug #26728: derived table with concatanation of literals in select list
3811
#
3812
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
3440
3813
INSERT INTO t1 VALUES (3), (1), (2);
3441
3814
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
3442
3815
col1 col2
3449
3822
this is a test. 1
3450
3823
this is a test. 2
3451
3824
DROP table t1;
3452
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
3453
CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;
3825
#
3826
# Bug #27257: COUNT(*) aggregated in outer query
3827
#
3828
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3829
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
3454
3830
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3455
3831
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3456
3832
SELECT COUNT(*), a,
3474
3850
2 2
3475
3851
3 3
3476
3852
DROP TABLE t1,t2;
3477
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
3478
CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;
3853
#
3854
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument
3855
#
3856
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3857
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
3479
3858
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3480
3859
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3481
3860
SELECT COUNT(*) c, a,
3493
3872
3 3 4
3494
3873
1 4 2,2
3495
3874
DROP table t1,t2;
3496
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)) ENGINE=MyISAM;
3875
#
3876
# Bug#27321: Wrong subquery result in a grouping select
3877
#
3878
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
3497
3879
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
3498
3880
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
3499
3881
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
3558
3940
2 o
3559
3941
3 p
3560
3942
DROP TABLE t1;
3561
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
3943
#
3944
# Bug #27363: nested aggregates in outer, subquery / sum(select
3945
# count(outer))
3946
#
3947
CREATE TABLE t1 (a INT);
3562
3948
INSERT INTO t1 values (1),(1),(1),(1);
3563
CREATE TABLE t2 (x INT) ENGINE=MyISAM;
3949
CREATE TEMPORARY TABLE t2 (x INT) ENGINE=MyISAM;
3564
3950
INSERT INTO t1 values (1000),(1001),(1002);
3565
3951
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
3566
3952
ERROR HY000: Invalid use of group function
3575
3961
FROM t1;
3576
3962
ERROR HY000: Invalid use of group function
3577
3963
DROP TABLE t1,t2;
3578
CREATE TABLE t1 (a int, b int, KEY (a)) ENGINE=MyISAM;
3964
#
3965
# Bug #27807: Server crash when executing subquery with EXPLAIN
3966
#
3967
CREATE TABLE t1 (a int, b int, KEY (a));
3579
3968
INSERT INTO t1 VALUES (1,1),(2,1);
3580
3969
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
3581
3970
id select_type table type possible_keys key key_len ref rows Extra
3582
3971
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
3583
3972
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3584
3973
DROP TABLE t1;
3585
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)) ENGINE=MyISAM;
3974
#
3975
# Bug #28377: grouping query with a correlated subquery in WHERE condition
3976
#
3977
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
3586
3978
INSERT INTO t1 VALUES
3587
3979
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
3588
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
3980
CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
3589
3981
INSERT INTO t2 VALUES (7), (5), (1), (3);
3590
3982
SELECT id, st FROM t1
3591
3983
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3612
4004
2 GA
3613
4005
4 FL
3614
4006
DROP TABLE t1,t2;
3615
CREATE TABLE t1 (a int) ENGINE=MyISAM;
4007
#
4008
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
4009
# over a grouping subselect
4010
#
4011
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
3616
4012
INSERT INTO t1 VALUES (1), (2);
3617
4013
EXPLAIN EXTENDED
3618
4014
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
3622
4018
Warnings:
3623
4019
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
3624
4020
DROP TABLE t1;
4021
#
4022
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1
4023
#
3625
4024
CREATE TABLE t1 (
3626
4025
a varchar(255) default NULL,
3627
4026
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3628
INDEX idx(a,b))
3629
ENGINE=MyISAM;
4027
INDEX idx(a,b));
4028
Warnings:
4029
Warning 1071 Specified key was too long; max key length is 767 bytes
3630
4030
CREATE TABLE t2 (
3631
a varchar(255) default NULL)
3632
ENGINE=MyISAM;
4031
a varchar(255) default NULL);
3633
4032
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
3634
4033
INSERT INTO t1 SELECT * FROM t1;
3635
4034
INSERT INTO t1 SELECT * FROM t1;
3650
4049
1
3651
4050
1
3652
4051
DROP TABLE t1,t2;
3653
CREATE TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
3654
CREATE TABLE t2 (x INTEGER) ENGINE=MyISAM;
4052
#
4053
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
4054
# of subquery
4055
#
4056
CREATE TEMPORARY TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
4057
CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;
3655
4058
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3656
4059
INSERT INTO t2 VALUES (1), (2);
4060
# wasn't failing, but should
3657
4061
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
3658
4062
ERROR 21000: Subquery returns more than 1 row
4063
# fails as it should
3659
4064
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
3660
4065
ERROR 21000: Subquery returns more than 1 row
3661
4066
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
3662
4067
(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
3663
4068
3.3333
3664
4069
DROP TABLE t1,t2;
3665
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
4070
# second test case from 27333
4071
CREATE TABLE t1 (a INT, b INT);
3666
4072
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
4073
# returns no rows, when it should
3667
4074
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
3668
4075
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
3669
4076
GROUP BY a1.a;
3670
4077
a COUNT(*)
3671
4078
1 3
3672
4079
DROP TABLE t1;
3673
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
3674
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
4080
#test cases from 29297
4081
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
4082
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;
3675
4083
INSERT INTO t1 VALUES (1),(2);
3676
4084
INSERT INTO t2 VALUES (1),(2);
3677
4085
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
3683
4091
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
3684
4092
3
3685
4093
DROP TABLE t1,t2;
3686
CREATE TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
3687
CREATE TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
4094
#
4095
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
4096
#
4097
CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
4098
CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
3688
4099
INSERT INTO t1 VALUES (100, 200);
3689
4100
INSERT INTO t1 VALUES (101, 201);
3690
4101
INSERT INTO t2 VALUES (101, 201);
3694
4105
0
3695
4106
0
3696
4107
DROP TABLE t1, t2;
3697
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
4108
#
4109
# Bug #30788: Inconsistent retrieval of char/varchar
4110
#
4111
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
3698
4112
INSERT INTO t1 VALUES ('a', 'aa');
3699
4113
INSERT INTO t1 VALUES ('a', 'aaa');
3700
4114
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3703
4117
CREATE INDEX I2 ON t1 (b);
3704
4118
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3705
4119
id select_type table type possible_keys key key_len ref rows Extra
3706
1 PRIMARY t1 index I1 I1 7 NULL 2 Using index; LooseScan
3707
1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where
4120
1 PRIMARY t1 index I1 I1 7 NULL # Using index; LooseScan
4121
1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where
3708
4122
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3709
4123
a b
3710
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
4124
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
3711
4125
INSERT INTO t2 SELECT * FROM t1;
3712
4126
CREATE INDEX I1 ON t2 (a);
3713
4127
CREATE INDEX I2 ON t2 (b);
3714
4128
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3715
4129
id select_type table type possible_keys key key_len ref rows Extra
3716
1 PRIMARY t2 index I1 I1 7 NULL 2 Using index; LooseScan
3717
1 PRIMARY t2 ref I2 I2 43 test.t2.a 2 Using where
4130
1 PRIMARY t2 index I1 I1 7 NULL # Using index; LooseScan
4131
1 PRIMARY t2 ref I2 I2 43 test.t2.a # Using where
3718
4132
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3719
4133
a b
3720
4134
EXPLAIN
3721
4135
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3722
4136
id select_type table type possible_keys key key_len ref rows Extra
3723
1 PRIMARY t1 index I1 I1 7 NULL 2 Using where; Using index; LooseScan
3724
1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where
4137
1 PRIMARY t1 index I1 I1 7 NULL # Using where; Using index; LooseScan
4138
1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where
3725
4139
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3726
4140
a b
3727
4141
DROP TABLE t1,t2;
3728
CREATE TABLE t1(a INT, b INT) ENGINE=MyISAM;
4142
#
4143
# Bug #32400: Complex SELECT query returns correct result only on some
4144
# occasions
4145
#
4146
CREATE TABLE t1(a INT, b INT);
3729
4147
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
3730
4148
EXPLAIN
3731
4149
SELECT a AS out_a, MIN(b) FROM t1
3741
4159
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
3742
4160
GROUP BY a;
3743
4161
id select_type table type possible_keys key key_len ref rows Extra
3744
1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
3745
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
4162
1 PRIMARY t1_outer ALL NULL NULL NULL NULL # Using where; Using temporary; Using filesort
4163
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where
3746
4164
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
3747
4165
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
3748
4166
GROUP BY a;
3750
4168
1 2
3751
4169
2 4
3752
4170
DROP TABLE t1;
3753
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
3754
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
4171
#
4172
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
4173
#
4174
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
4175
CREATE TABLE t2 (a INT);
3755
4176
INSERT INTO t1 VALUES (1),(2);
3756
4177
INSERT INTO t2 VALUES (1),(2);
3757
4178
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
3770
4191
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
3771
4192
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
3772
4193
id select_type table type possible_keys key key_len ref rows filtered Extra
3773
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3774
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3775
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3776
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
4194
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00 Using where
4195
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00 Using where
4196
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL # 100.00 Using where
4197
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL
3777
4198
Warnings:
3778
4199
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
3779
4200
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
3780
4201
Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
3781
4202
DROP TABLE t1,t2;
3782
CREATE TABLE t4 (
4203
#
4204
# BUG#33794 "MySQL crashes executing specific query on specific dump"
4205
#
4206
CREATE TEMPORARY TABLE t4 (
3783
4207
f7 varchar(32) collate utf8_bin NOT NULL default '',
3784
4208
f10 varchar(32) collate utf8_bin default NULL,
3785
4209
PRIMARY KEY (f7))
3790
4214
f2 varchar(50) collate utf8_bin default NULL,
3791
4215
f3 varchar(10) collate utf8_bin default NULL,
3792
4216
PRIMARY KEY (f4),
3793
UNIQUE KEY uk1 (f2))
3794
ENGINE=MyISAM;
4217
UNIQUE KEY uk1 (f2));
3795
4218
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
3796
CREATE TABLE t1 (
4219
CREATE TEMPORARY TABLE t1 (
3797
4220
f8 varchar(32) collate utf8_bin NOT NULL default '',
3798
4221
f1 varchar(10) collate utf8_bin default NULL,
3799
4222
f9 varchar(32) collate utf8_bin default NULL,
3800
4223
PRIMARY KEY (f8))
3801
4224
ENGINE=MyISAM;
3802
4225
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
3803
CREATE TABLE t3 (
4226
CREATE TEMPORARY TABLE t3 (
3804
4227
f6 varchar(32) collate utf8_bin NOT NULL default '',
3805
4228
f5 varchar(50) collate utf8_bin default NULL,
3806
4229
PRIMARY KEY (f6))
3828
4251
2 NULL NULL
3829
4252
DROP TABLE t1, t2, t3, t4;
3830
4253
End of 5.0 tests.
3831
create table t_out (subcase char(3),
4254
#
4255
# Test [NOT] IN truth table (both as top-level and general predicate).
4256
#
4257
create temporary table t_out (subcase char(3),
3832
4258
a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;
3833
create table t_in (a2 char(2), b2 char(2), c2 char(2)) ENGINE=MyISAM;
4259
create table t_in (a2 char(2), b2 char(2), c2 char(2));
3834
4260
insert into t_out values ('A.1','2a', NULL, '2a');
4261
#------------------------- A.2 - impossible
3835
4262
insert into t_out values ('A.3', '2a', NULL, '2a');
3836
4263
insert into t_out values ('A.4', '2a', NULL, 'xx');
3837
4264
insert into t_out values ('B.1', '2a', '2a', '2a');
3931
4358
NOT((a1, b1, c1) IN (select * from t_in));
3932
4359
not_pred_in
3933
4360
F
4361
# test non-top level result indirectly
3934
4362
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
3935
4363
where subcase = 'A.3' and
3936
4364
((a1, b1, c1) IN (select * from t_in)) is NULL and
4001
4429
NOT((a1, b1, c1) IN (select * from t_in));
4002
4430
not_pred_in
4003
4431
F
4432
# test non-top level result indirectly
4004
4433
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4005
4434
where subcase = 'B.3' and
4006
4435
((a1, b1, c1) IN (select * from t_in)) is NULL and
4025
4454
T
4026
4455
drop table t_out;
4027
4456
drop table t_in;
4028
CREATE TABLE t1 (s1 char(1)) ENGINE=MyISAM;
4457
#
4458
# Bug#20835 (literal string with =any values)
4459
#
4460
CREATE TABLE t1 (s1 char(1));
4029
4461
INSERT INTO t1 VALUES ('a');
4030
4462
SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
4031
4463
s1
4032
4464
a
4033
4465
DROP TABLE t1;
4034
CREATE TABLE t1( a INT ) ENGINE=MyISAM;
4466
#
4467
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
4468
#
4469
CREATE TABLE t1( a INT );
4035
4470
INSERT INTO t1 VALUES (1),(2);
4036
CREATE TABLE t2( a INT, b INT ) ENGINE=MyISAM;
4471
CREATE TABLE t2( a INT, b INT );
4037
4472
SELECT *
4038
4473
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
4039
4474
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2
4084
4519
a
4085
4520
1
4086
4521
2
4522
# This was not allowed previously. Possibly, it should be allowed on the future.
4523
# For now, the intent is to keep the fix as non-intrusive as possible.
4087
4524
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
4088
4525
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
4089
4526
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
4122
4559
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
4123
4560
a
4124
4561
1
4562
# Test of rule
4563
# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias
4564
# UNION should not be allowed inside the parentheses, nor should
4565
# aliases after.
4566
#
4125
4567
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
4126
4568
a 1
4127
4569
1 1
4172
4614
a a
4173
4615
1 1
4174
4616
2 1
4617
# For the join, TABLE_LIST::select_lex == NULL
4618
# Check that we handle this.
4175
4619
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
4176
4620
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a2' at line 1
4177
4621
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
4219
4663
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4220
4664
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4221
4665
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4666
# Make sure context is popped when we leave the nested select
4222
4667
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4223
4668
( SELECT a FROM t1 WHERE a = 1 ) a
4224
4669
1 1
4227
4672
( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a
4228
4673
1 1
4229
4674
1 2
4675
# Make sure we have feature F561 (see .yy file)
4230
4676
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
4231
4677
a b
4678
# Make sure the parser does not allow nested UNIONs anywhere
4232
4679
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
4233
4680
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4234
4681
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1