110
106
select * from t3 where a in (select * from t2);
111
107
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
113
109
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
114
110
insert into t2 values (2,10);
115
111
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
116
112
delete from t2 where a=2 and b=10;
117
113
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
118
create temporary table t5 (a int) ENGINE=MyISAM;
114
create table t5 (a int) ENGINE=MyISAM;
119
115
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
120
116
insert into t5 values (5);
121
117
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
122
118
insert into t5 values (2);
123
119
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
125
120
explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
127
122
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
128
create temporary table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
129
create temporary table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
123
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
124
create table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
130
125
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
131
126
insert into t6 values (1,1),(1,2),(2,2),(1,3);
132
127
select * from t6 where exists (select * from t7 where uq = clinic_uq);
133
128
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
135
--echo # not unique fields
137
132
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
139
--echo # different tipes & group functions
134
# different tipes & group functions
140
135
drop table t1,t2,t3;
142
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
137
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0') ENGINE=MyISAM;
143
138
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
144
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
139
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0') ENGINE=MyISAM;
145
140
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
146
CREATE TABLE t1 (a varchar(20),b date NULL);
141
CREATE TABLE t1 (a varchar(20),b date NULL) ENGINE=MyISAM;
147
142
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
148
143
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
149
144
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
280
274
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
282
276
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
284
277
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
286
278
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
289
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
281
CREATE TABLE t1 (a int) ENGINE=MyISAM;
290
282
INSERT INTO t1 VALUES (1);
291
283
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
294
--echo #update with subselects
295
create table t1 (a int NOT NULL, b int, primary key (a));
296
create table t2 (a int NOT NULL, b int, primary key (a));
286
#update with subselects
287
create table t1 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
288
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
297
289
insert into t1 values (0, 10),(1, 11),(2, 12);
298
290
insert into t2 values (1, 21),(2, 22),(3, 23);
299
291
select * from t1;
320
312
select * from t1;
321
313
drop table t1, t2;
323
--echo #insert with subselects
324
CREATE TABLE t1 (x int);
325
create table t2 (a int);
326
create table t3 (b int);
315
#multi-delete with subselects
317
create table t11 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
318
create table t12 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
319
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
320
insert into t11 values (0, 10),(1, 11),(2, 12);
321
insert into t12 values (33, 10),(22, 11),(2, 12);
322
insert into t2 values (1, 21),(2, 12),(3, 23);
327
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
329
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
330
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
334
drop table t11, t12, t2;
336
#insert with subselects
337
CREATE TABLE t1 (x int) ENGINE=MyISAM;
338
create table t2 (a int) ENGINE=MyISAM;
339
create table t3 (b int) ENGINE=MyISAM;
327
340
insert into t2 values (1);
328
341
insert into t3 values (1),(2);
446
463
select (select a+1) from t1;
450
--echo # Null with keys
453
CREATE TEMPORARY TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a)) ENGINE=MyISAM;
454
CREATE TEMPORARY TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
470
CREATE TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a)) ENGINE=MyISAM;
471
CREATE TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
455
472
INSERT INTO t1 VALUES (1),(2),(3),(4);
456
473
INSERT INTO t2 VALUES (1),(2),(3);
457
474
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
458
475
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
459
CREATE TEMPORARY TABLE t3 (a int default '0') ENGINE=MyISAM;
476
CREATE TABLE t3 (a int default '0') ENGINE=MyISAM;
460
477
INSERT INTO t3 VALUES (1),(2),(3);
461
478
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
462
479
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
463
480
drop table t1,t2,t3;
465
--echo #LIMIT is not supported now
466
--echo #create table t1 (a float) ENGINE=MyISAM;
467
--echo #-- error 1235
468
--echo #select 10.5 IN (SELECT * from t1 LIMIT 1);
469
--echo #-- error 1235
470
--echo #select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
471
--echo #drop table t1;
473
--echo #create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;
474
--echo #create table t2 (a int) ENGINE=MyISAM;
475
--echo #insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
476
--echo #insert into t2 values (1),(2),(NULL);
477
--echo #select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;
478
--echo #select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
479
--echo #select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
480
--echo #drop table t1,t2;
482
--echo #create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;
483
--echo #insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
484
--echo #select ROW(1, 1, 'a') IN (select a,b,c from t1);
485
--echo #select ROW(1, 2, 'a') IN (select a,b,c from t1);
486
--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1);
487
--echo #select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
488
--echo #select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
489
--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
490
--echo #select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
491
--echo #select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
492
--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
493
--echo #-- error 1235
494
--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
495
--echo #drop table t1;
482
#LIMIT is not supported now
483
#create table t1 (a float) ENGINE=MyISAM;
485
#select 10.5 IN (SELECT * from t1 LIMIT 1);
487
#select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
490
#create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;
491
#create table t2 (a int) ENGINE=MyISAM;
492
#insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
493
#insert into t2 values (1),(2),(NULL);
494
#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;
495
#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;
496
#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;
499
#create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;
500
#insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
501
#select ROW(1, 1, 'a') IN (select a,b,c from t1);
502
#select ROW(1, 2, 'a') IN (select a,b,c from t1);
503
#select ROW(1, 1, 'a') IN (select b,a,c from t1);
504
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
505
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
506
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
507
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
508
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
509
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
511
#select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
497
--echo #test of uncacheable subqueries
498
CREATE TABLE t1 (a int);
514
#test of uncacheable subqueries
515
CREATE TABLE t1 (a int) ENGINE=MyISAM;
499
516
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
500
517
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
504
CREATE TEMPORARY TABLE `t1` (
505
522
`mot` varchar(30) NOT NULL default '',
506
523
`topic` bigint NOT NULL default '0',
507
524
`date` date NULL DEFAULT '2009-01-20',
643
659
select * from t1;
647
--echo # correct NULL in <CONSTANT> IN (SELECT ...)
649
create temporary table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
668
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
671
name varchar(15) default NULL
674
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
675
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
680
# correct NULL in <CONSTANT> IN (SELECT ...)
682
create table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
650
683
insert into t1 values (-1), (-4), (-2), (NULL);
651
684
select -10 IN (select a from t1 FORCE INDEX (indexa));
655
--echo # Test optimization for sub selects
657
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
688
# Test optimization for sub selects
690
create table t1 (id int not null auto_increment primary key, salary int, key(salary)) ENGINE=MyISAM;
658
691
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
660
692
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
689
721
drop table t1, t2;
692
--echo # reduced subselect in ORDER BY & GROUP BY clauses
724
# reduced subselect in ORDER BY & GROUP BY clauses
695
CREATE TEMPORARY TABLE `t1` (
696
728
`id` bigint NOT NULL auto_increment,
697
729
`pseudo` varchar(35) NOT NULL default '',
698
730
`email` varchar(60) NOT NULL default '',
699
731
PRIMARY KEY (`id`),
700
732
UNIQUE KEY `email` (`email`),
701
733
UNIQUE KEY `pseudo` (`pseudo`)
702
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
734
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
703
735
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
704
736
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
705
737
drop table if exists t1;
707
739
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
710
--echo # IN subselect optimization test
712
create temporary table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
713
create temporary table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
714
create temporary table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
742
# IN subselect optimization test
744
create table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
745
create table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
746
create table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
715
747
insert into t1 values (1,10), (2,20), (3,30), (4,40);
716
748
insert into t2 values (2), (3), (4), (5);
717
749
insert into t3 values (10,3), (20,4), (30,5);
753
785
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
754
786
drop table t1, t2, t3;
757
--echo # alloc_group_fields() working
759
create temporary table t1 (a int, b int) ENGINE=MyISAM;
760
create temporary table t2 (a int, b int) ENGINE=MyISAM;
761
create temporary table t3 (a int, b int) ENGINE=MyISAM;
789
# alloc_group_fields() working
791
create table t1 (a int, b int) ENGINE=MyISAM;
792
create table t2 (a int, b int) ENGINE=MyISAM;
793
create table t3 (a int, b int) ENGINE=MyISAM;
762
794
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
763
795
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
764
796
insert into t3 values (3,3), (2,2), (1,1);
765
797
select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3;
766
798
drop table t1,t2,t3;
769
--echo # aggregate functions in HAVING test
771
create temporary table t1 (s1 int) ENGINE=MyISAM;
772
create temporary table t2 (s1 int) ENGINE=MyISAM;
801
# aggregate functions in HAVING test
803
create table t1 (s1 int) ENGINE=MyISAM;
804
create table t2 (s1 int) ENGINE=MyISAM;
773
805
insert into t1 values (1);
774
806
insert into t2 values (1);
775
807
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
776
808
drop table t1,t2;
779
--echo # update subquery with wrong field (to force name resolving
780
--echo # in UPDATE name space)
782
create temporary table t1 (s1 int) ENGINE=MyISAM;
783
create temporary table t2 (s1 int) ENGINE=MyISAM;
811
# update subquery with wrong field (to force name resolving
812
# in UPDATE name space)
814
create table t1 (s1 int) ENGINE=MyISAM;
815
create table t2 (s1 int) ENGINE=MyISAM;
784
816
insert into t1 values (1);
785
817
insert into t2 values (1);
787
819
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
788
820
DROP TABLE t1, t2;
791
--echo # collation test
793
--echo #CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
794
--echo # s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;
795
--echo #INSERT INTO t1 VALUES ('z','?');
796
--echo #-- error 1267
797
--echo #select * from t1 where s1 > (select max(s2) from t1);
798
--echo #-- error 1267
799
--echo #select * from t1 where s1 > any (select max(s2) from t1);
800
--echo #drop table t1;
825
#CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
826
# s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;
827
#INSERT INTO t1 VALUES ('z','?');
829
#select * from t1 where s1 > (select max(s2) from t1);
831
#select * from t1 where s1 > any (select max(s2) from t1);
803
--echo # aggregate functions reinitialization
805
create table t1(toid int,rd int);
806
create table t2(userid int,pmnew int,pmtotal int);
835
# aggregate functions reinitialization
837
create table t1(toid int,rd int) ENGINE=MyISAM;
838
create table t2(userid int,pmnew int,pmtotal int) ENGINE=MyISAM;
807
839
insert into t2 values(1,0,0),(2,0,0);
808
840
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);
809
841
select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1);
810
842
drop table t1, t2;
815
create table t1 (s1 char(5));
847
create table t1 (s1 char(5)) ENGINE=MyISAM;
817
849
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
818
850
insert into t1 values ('tttt');
819
851
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
821
852
explain extended (select * from t1);
822
853
(select * from t1);
826
--echo # IN optimisation test results
828
create temporary table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
829
create temporary table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
857
# IN optimisation test results
859
create table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
860
create table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
830
861
insert into t1 values ('a1'),('a2'),('a3');
831
862
insert into t2 values ('a1'),('a2');
832
863
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
861
892
explain extended select * from t3 where NULL >= some (select b from t2);
862
893
select * from t3 where NULL >= some (select b from t2 group by 1);
863
894
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
865
--echo # optimized static ALL/ANY with grouping
896
# optimized static ALL/ANY with grouping
867
898
insert into t2 values (2,2), (2,1), (3,3), (3,1);
868
899
select * from t3 where a > all (select max(b) from t2 group by a);
869
900
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
870
901
drop table t2, t3;
873
--echo # correct used_tables()
904
# correct used_tables()
876
CREATE TEMPORARY TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime, `last_update` datetime, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
907
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 ;
877
908
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
878
CREATE TEMPORARY TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;
909
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;
879
910
INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);
880
CREATE TEMPORARY TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;
911
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 ;
881
912
INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);
882
CREATE TEMPORARY TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
913
CREATE TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
883
914
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
884
915
select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;
885
916
SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid;
886
917
drop table t1,t2,t3,t4;
889
--echo # cardinality check
891
CREATE TEMPORARY TABLE t1 (id int default NULL) ENGINE=MyISAM;
922
CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM;
892
923
INSERT INTO t1 VALUES (1),(5);
893
CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;
924
CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM;
894
925
INSERT INTO t2 VALUES (2),(6);
896
927
select * from t1 where (1,2,6) in (select * from t2);
897
928
DROP TABLE t1,t2;
900
--echo # optimized ALL/ANY with union
902
create table t1 (s1 char);
931
# optimized ALL/ANY with union
933
create table t1 (s1 char) ENGINE=MyISAM;
903
934
insert into t1 values ('e');
904
935
select * from t1 where 'f' > any (select s1 from t1);
905
936
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
907
937
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
911
--echo # filesort in subquery (restoring join_tab)
913
CREATE TEMPORARY TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
941
# filesort in subquery (restoring join_tab)
943
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
914
944
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
915
CREATE TEMPORARY TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
945
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
916
946
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
917
947
select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;
918
948
drop table t1, t2;
921
--echo # unresolved field error
923
create temporary table t1 (s1 int) ENGINE=MyISAM;
924
create temporary table t2 (s1 int) ENGINE=MyISAM;
951
# unresolved field error
953
create table t1 (s1 int) ENGINE=MyISAM;
954
create table t2 (s1 int) ENGINE=MyISAM;
926
956
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
930
960
select count(*) from t2 group by t1.s2;
931
961
drop table t1, t2;
934
--echo # fix_fields() in add_ref_to_table_cond()
936
CREATE TEMPORARY TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
937
CREATE TEMPORARY TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
964
# fix_fields() in add_ref_to_table_cond()
966
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
967
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
938
968
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
939
969
INSERT INTO t2 VALUES (100, 200, 'C');
940
970
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
941
971
DROP TABLE t1, t2;
943
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
973
CREATE TABLE t1 (a int) ENGINE=MyISAM;
944
974
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
945
975
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
952
CREATE TEMPORARY TABLE `t1` (
953
983
`id` int NOT NULL auto_increment,
954
984
`id_cns` int NOT NULL default '0',
955
985
`tipo` enum('','UNO','DUE') NOT NULL default '',
983
1013
SET SQL_SELECT_LIMIT=default;
987
--echo # Bug #3118: subselect + order by
1017
# Bug #3118: subselect + order by
990
CREATE TABLE t1 (a int, b int, INDEX (a));
1020
CREATE TABLE t1 (a int, b int, INDEX (a)) ENGINE=MyISAM;
991
1021
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
992
1022
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
995
--echo # Item_cond fix field
997
create table t1(val varchar(10));
1025
# Item_cond fix field
1027
create table t1(val varchar(10)) ENGINE=MyISAM;
998
1028
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
999
1029
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%');
1003
--echo # ref_or_null replacing with ref
1005
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1033
# ref_or_null replacing with ref
1035
create table t1 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1006
1036
insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');
1007
1037
select * from t1 where id not in (select id from t1 where id < 8);
1008
1038
select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
1009
--replace_column 9 #
1010
1039
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1011
--replace_column 9 #
1012
1040
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
1013
1041
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1014
create temporary table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1042
create table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1015
1043
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
1016
1044
select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
1017
--replace_column 9 #
1018
1045
explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
1019
1046
drop table t1,t2;
1022
--echo # Static tables & rund() in subqueries
1024
create temporary table t1 (a int) ENGINE=MyISAM;
1049
# Static tables & rund() in subqueries
1051
create table t1 (a int) ENGINE=MyISAM;
1025
1052
insert into t1 values (1);
1026
1053
explain select benchmark(1000, (select a from t1 where a=rand()));
1032
create temporary table t1(id int) ENGINE=MyISAM;
1033
create temporary table t2(id int) ENGINE=MyISAM;
1034
create temporary table t3(flag int) ENGINE=MyISAM;
1059
create table t1(id int) ENGINE=MyISAM;
1060
create table t2(id int) ENGINE=MyISAM;
1061
create table t3(flag int) ENGINE=MyISAM;
1036
1063
select (select * from t3 where id not null) from t1, t2;
1037
1064
drop table t1,t2,t3;
1040
--echo # aggregate functions (Bug #3505)
1042
CREATE TABLE t1 (id INT);
1043
CREATE TABLE t2 (id INT);
1067
# aggregate functions (Bug #3505)
1069
CREATE TABLE t1 (id INT) ENGINE=MyISAM;
1070
CREATE TABLE t2 (id INT) ENGINE=MyISAM;
1044
1071
INSERT INTO t1 VALUES (1), (2);
1045
1072
INSERT INTO t2 VALUES (1);
1046
1073
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);
1223
--echo # SELECT(EXISTS * ...)optimisation
1225
create table t1 (a int, b int);
1250
# SELECT(EXISTS * ...)optimisation
1252
create table t1 (a int, b int) ENGINE=MyISAM;
1226
1253
insert into t1 values (1,2),(3,4);
1227
1254
select * from t1 up where exists (select * from t1 where t1.a=up.a);
1228
--replace_column 9 #
1229
1255
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
1233
--echo # Bug #4102: subselect in HAVING
1259
# Bug #4102: subselect in HAVING
1236
CREATE TEMPORARY TABLE t1 (t1_a int) ENGINE=MyISAM;
1262
CREATE TABLE t1 (t1_a int) ENGINE=MyISAM;
1237
1263
INSERT INTO t1 VALUES (1);
1238
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
1264
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b)) ENGINE=MyISAM;
1239
1265
INSERT INTO t2 VALUES (1, 1), (1, 2);
1240
1266
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
1241
1267
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
1242
1268
DROP TABLE t1, t2;
1245
--echo # Test problem with NULL and derived tables (Bug #4097)
1271
# Test problem with NULL and derived tables (Bug #4097)
1248
CREATE TEMPORARY TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
1274
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
1249
1275
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
1250
CREATE TEMPORARY TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
1276
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
1251
1277
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
1252
1278
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
1253
1279
drop table t1,t2;
1256
--echo # Aggregate function comparation with ALL/ANY/SOME subselect
1258
CREATE TEMPORARY TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
1282
# Aggregate function comparation with ALL/ANY/SOME subselect
1284
CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
1259
1285
insert into t1 values (1);
1260
CREATE TEMPORARY TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
1286
CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
1261
1287
insert into t2 values (1,2);
1262
1288
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
1263
1289
drop table t1,t2;
1266
--echo # BUG#5003 - like in subselect
1268
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin);
1292
# BUG#5003 - like in subselect
1294
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;
1269
1295
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
1270
1296
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
1271
1297
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
1303
1329
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
1306
create table t1 (x int);
1332
create table t1 (x int) ENGINE=MyISAM;
1307
1333
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;
1311
--echo # Test of correct maybe_null flag returning by subquwery for temporary table
1314
CREATE TEMPORARY TABLE `t1` ( `master` int NOT NULL default '0', `map` int NOT NULL default '0', `slave` int NOT NULL default '0', `access` int NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`)) ENGINE=MyISAM;
1337
# Test of correct maybe_null flag returning by subquwery for temporary table
1340
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;
1315
1341
INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);
1316
CREATE TEMPORARY TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ENGINE=MyISAM ;
1342
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 ;
1317
1343
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');
1319
1345
SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
1320
1346
SELECT b.ac FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
1321
1347
drop tables t1,t2;
1324
--echo # Subselect in non-select command just after connection
1350
# Subselect in non-select command just after connection
1326
1352
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
1327
1353
connection root;
1328
1354
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
1331
--echo # primary query with temporary table and subquery with groupping
1333
create temporary table t1 (a int, b int) ENGINE=MyISAM;
1334
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1357
# primary query with temporary table and subquery with groupping
1359
create table t1 (a int, b int) ENGINE=MyISAM;
1360
create table t2 (a int, b int) ENGINE=MyISAM;
1335
1361
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
1336
1362
insert into t2 values (1,3),(2,1);
1337
1363
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
1338
1364
drop table t1, t2;
1341
--echo # Equal operation under row and empty subquery
1343
create table t1 (s1 int,s2 int);
1367
# Equal operation under row and empty subquery
1369
create table t1 (s1 int,s2 int) ENGINE=MyISAM;
1344
1370
insert into t1 values (20,15);
1345
1371
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
1349
--echo # ALL/ANY with NULL
1351
create table t1 (s1 int);
1377
create table t1 (s1 int) ENGINE=MyISAM;
1352
1378
insert into t1 values (1),(null);
1353
1379
select * from t1 where s1 < all (select s1 from t1);
1354
1380
select s1, s1 < all (select s1 from t1) from t1;
1358
--echo # reference on changable fields from subquery
1384
# reference on changable fields from subquery
1360
1386
CREATE TABLE t1 (
1361
1387
Code char(3) NOT NULL default '',
1362
1388
Name char(52) NOT NULL default '',
1384
1410
Population < 200);
1388
--echo # Test for BUG#7885: Server crash when 'any' subselect compared to
1389
--echo # non-existant field.
1391
create temporary table t1 (a1 int) ENGINE=MyISAM;
1392
create temporary table t2 (b1 int) ENGINE=MyISAM;
1393
--error ER_BAD_FIELD_ERROR
1414
# Test for BUG#7885: Server crash when 'any' subselect compared to
1415
# non-existant field.
1417
create table t1 (a1 int) ENGINE=MyISAM;
1418
create table t2 (b1 int) ENGINE=MyISAM;
1394
1420
select * from t1 where a2 > any(select b1 from t2);
1395
1421
select * from t1 where a1 > any(select b1 from t2);
1396
1422
drop table t1,t2;
1400
--echo # Comparison subquery with * and row
1402
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
1426
# Comparison subquery with * and row
1428
create table t1 (a integer, b integer) ENGINE=MyISAM;
1403
1429
select (select * from t1) = (select 1,2);
1404
1430
select (select 1,2) = (select * from t1);
1405
--echo # queries whih can be converted to IN
1431
# queries whih can be converted to IN
1406
1432
select row(1,2) = ANY (select * from t1);
1407
1433
select row(1,2) != ALL (select * from t1);
1411
--echo # Comparison subquery and row with nested rows
1413
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
1437
# Comparison subquery and row with nested rows
1439
create table t1 (a integer, b integer) ENGINE=MyISAM;
1415
1441
select row(1,(2,2)) in (select * from t1 );
1565
1591
select (select * from t1) = (1,2,3);
1569
--echo # Item_int_with_ref check (BUG#10020)
1571
--echo #CREATE TABLE `t1` (
1572
--echo # `itemid` bigint NOT NULL auto_increment,
1573
--echo # `sessionid` bigint default NULL,
1574
--echo # `time` int NOT NULL default '0',
1575
--echo # `data` text collate latin1_general_ci NOT NULL,
1576
--echo # PRIMARY KEY (`itemid`)
1578
--echo #INSERT INTO `t1` VALUES (1, 1, 1, '');
1579
--echo #CREATE TABLE `t2` (
1580
--echo # `sessionid` bigint NOT NULL auto_increment,
1581
--echo # `pid` int NOT NULL default '0',
1582
--echo # `date` int NOT NULL default '0',
1583
--echo # `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
1584
--echo # PRIMARY KEY (`sessionid`)
1586
--echo #INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
1587
--echo #SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;
1588
--echo #drop tables t1,t2;
1595
# Item_int_with_ref check (BUG#10020)
1597
#CREATE TABLE `t1` (
1598
# `itemid` bigint NOT NULL auto_increment,
1599
# `sessionid` bigint default NULL,
1600
# `time` int NOT NULL default '0',
1601
# `data` text collate latin1_general_ci NOT NULL,
1602
# PRIMARY KEY (`itemid`)
1604
#INSERT INTO `t1` VALUES (1, 1, 1, '');
1605
#CREATE TABLE `t2` (
1606
# `sessionid` bigint NOT NULL auto_increment,
1607
# `pid` int NOT NULL default '0',
1608
# `date` int NOT NULL default '0',
1609
# `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
1610
# PRIMARY KEY (`sessionid`)
1612
#INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
1613
#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;
1590
--echo # BUG#11821 : Select from subselect using aggregate function on an enum
1592
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
1616
# BUG#11821 : Select from subselect using aggregate function on an enum
1618
create table t1 (fld enum('0','1')) ENGINE=MyISAM;
1593
1619
insert into t1 values ('1');
1594
1620
select * from (select max(fld) from t1) as foo;
1598
--echo # Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
1624
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
1601
CREATE TABLE t1 (one int, two int, flag char(1));
1602
CREATE TABLE t2 (one int, two int, flag char(1));
1627
CREATE TABLE t1 (one int, two int, flag char(1)) ENGINE=MyISAM;
1628
CREATE TABLE t2 (one int, two int, flag char(1)) ENGINE=MyISAM;
1603
1629
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1604
1630
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1625
1651
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
1626
1652
DROP TABLE t1,t2;
1629
--echo # Bug #12392: where cond with IN predicate for rows and NULL values in table
1655
# Bug #12392: where cond with IN predicate for rows and NULL values in table
1632
CREATE TEMPORARY TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
1658
CREATE TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
1633
1659
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
1635
1661
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
1640
--echo # Bug #11479: subquery over left join with an empty inner table
1666
# Bug #11479: subquery over left join with an empty inner table
1643
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
1644
CREATE TEMPORARY TABLE t2 (a int, b int) ENGINE=MyISAM;
1645
CREATE TEMPORARY TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
1669
CREATE TABLE t1 (a int) ENGINE=MyISAM;
1670
CREATE TABLE t2 (a int, b int) ENGINE=MyISAM;
1671
CREATE TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
1646
1672
INSERT INTO t1 VALUES (1), (2), (3), (4);
1647
1673
INSERT INTO t2 VALUES (1,10), (3,30);
1790
1816
DROP TABLE t1,t2;
1793
--echo # Bug #11302: getObject() returns a String for a sub-query of type datetime
1795
CREATE TABLE t1 (a DATETIME);
1819
# Bug #11302: getObject() returns a String for a sub-query of type datetime
1821
CREATE TABLE t1 (a DATETIME) ENGINE=MyISAM;
1796
1822
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
1798
CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT
1824
CREATE TABLE t2 ENGINE=MyISAM AS SELECT
1799
1825
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
1800
1826
FROM t1 WHERE a > '2000-01-01';
1801
1827
SHOW CREATE TABLE t2;
1803
CREATE TEMPORARY TABLE t3 ENGINE=MyISAM AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
1829
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');
1804
1830
SHOW CREATE TABLE t3;
1806
1832
DROP TABLE t1,t2,t3;
1809
--echo # Bug 24653: sorting by expressions containing subselects
1810
--echo # that return more than one row
1835
# Bug 24653: sorting by expressions containing subselects
1836
# that return more than one row
1813
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
1839
CREATE TABLE t1 (a int) ENGINE=MyISAM;
1814
1840
INSERT INTO t1 VALUES (2), (4), (1), (3);
1816
CREATE TABLE t2 (b int, c int);
1842
CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
1817
1843
INSERT INTO t2 VALUES
1818
1844
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
1820
1846
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
1821
--error ER_SUBQUERY_NO_1_ROW
1822
1848
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
1823
1849
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
1824
--error ER_SUBQUERY_NO_1_ROW
1825
1851
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
1827
1853
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
1828
--error ER_SUBQUERY_NO_1_ROW
1829
1855
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
1832
1858
SELECT a FROM t1 GROUP BY a
1833
1859
HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
1834
1860
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
1835
--error ER_SUBQUERY_NO_1_ROW
1836
1862
SELECT a FROM t1 GROUP BY a
1837
1863
HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
1838
1864
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2065
2091
DROP TABLE t1,t2,t3;
2068
--echo # Test for bug #16603: GROUP BY in a row subquery with a quantifier
2069
--echo # when an index is defined on the grouping field
2094
# Test for bug #16603: GROUP BY in a row subquery with a quantifier
2095
# when an index is defined on the grouping field
2071
CREATE TABLE t1 (a varchar(5), b varchar(10));
2097
CREATE TABLE t1 (a varchar(5), b varchar(10)) ENGINE=MyISAM;
2072
2098
INSERT INTO t1 VALUES
2073
2099
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
2074
2100
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
2076
2102
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2077
--replace_column 9 #
2079
2104
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2081
2106
ALTER TABLE t1 ADD INDEX(a);
2083
2108
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2084
--replace_column 9 #
2086
2110
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
2091
--echo # Bug#17366: Unchecked Item_int results in server crash
2093
create table t1( f1 int,f2 int);
2115
# Bug#17366: Unchecked Item_int results in server crash
2117
create table t1( f1 int,f2 int) ENGINE=MyISAM;
2094
2118
insert into t1 values (1,1),(2,2);
2095
2119
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';
2099
--echo # Bug #18306: server crash on delete using subquery.
2123
# Bug #18306: server crash on delete using subquery.
2102
create temporary table t1 (c int, key(c)) ENGINE=MyISAM;
2126
create table t1 (c int, key(c)) ENGINE=MyISAM;
2103
2127
insert into t1 values (1142477582), (1142455969);
2104
create temporary table t2 (a int, b int) ENGINE=MyISAM;
2128
create table t2 (a int, b int) ENGINE=MyISAM;
2105
2129
insert into t2 values (2, 1), (1, 0);
2106
2130
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
2107
2131
drop table t1, t2;
2110
--echo # Bug#19077: A nested materialized derived table is used before being populated.
2112
create table t1 (i int, j bigint);
2134
# Bug#19077: A nested materialized derived table is used before being populated.
2136
create table t1 (i int, j bigint) ENGINE=MyISAM;
2113
2137
insert into t1 values (1, 2), (2, 2), (3, 2);
2114
2138
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
2118
--echo # Bug#19700: subselect returning BIGINT always returned it as SIGNED
2120
CREATE TEMPORARY TABLE t1 (i BIGINT) ENGINE=MyISAM;
2142
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
2144
CREATE TABLE t1 (i BIGINT) ENGINE=MyISAM;
2121
2145
INSERT INTO t1 VALUES (10000000000000000); # > MAX SIGNED BIGINT 9323372036854775807
2122
2146
INSERT INTO t1 VALUES (1);
2124
CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;
2148
CREATE TABLE t2 (i BIGINT) ENGINE=MyISAM;
2125
2149
INSERT INTO t2 VALUES (10000000000000000); # same as first table
2126
2150
INSERT INTO t2 VALUES (1);
2495
2521
DROP table t1,t2;
2498
--echo # Bug#27321: Wrong subquery result in a grouping select
2500
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
2524
# Bug#27321: Wrong subquery result in a grouping select
2526
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)) ENGINE=MyISAM;
2501
2527
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
2502
2528
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
2503
2529
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
2556
2582
DROP TABLE t1,t2;
2559
--echo # Bug #27807: Server crash when executing subquery with EXPLAIN
2561
CREATE TABLE t1 (a int, b int, KEY (a));
2585
# Bug #27807: Server crash when executing subquery with EXPLAIN
2587
CREATE TABLE t1 (a int, b int, KEY (a)) ENGINE=MyISAM;
2562
2588
INSERT INTO t1 VALUES (1,1),(2,1);
2563
2589
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
2567
--echo # Bug #28377: grouping query with a correlated subquery in WHERE condition
2593
# Bug #28377: grouping query with a correlated subquery in WHERE condition
2570
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
2596
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)) ENGINE=MyISAM;
2571
2597
INSERT INTO t1 VALUES
2572
2598
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
2573
CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
2599
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
2574
2600
INSERT INTO t2 VALUES (7), (5), (1), (3);
2576
2602
SELECT id, st FROM t1
2633
2661
DROP TABLE t1,t2;
2637
--echo # Bug #27333: subquery grouped for aggregate of outer query / no aggregate
2638
--echo # of subquery
2640
CREATE TEMPORARY TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
2641
CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;
2665
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
2668
CREATE TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
2669
CREATE TABLE t2 (x INTEGER) ENGINE=MyISAM;
2642
2670
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
2643
2671
INSERT INTO t2 VALUES (1), (2);
2645
--echo # wasn't failing, but should
2673
# wasn't failing, but should
2646
2674
--error ER_SUBQUERY_NO_1_ROW
2647
2675
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
2649
--echo # fails as it should
2677
# fails as it should
2650
2678
--error ER_SUBQUERY_NO_1_ROW
2651
2679
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
2653
2681
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
2654
2682
DROP TABLE t1,t2;
2656
--echo # second test case from 27333
2657
CREATE TABLE t1 (a INT, b INT);
2684
# second test case from 27333
2685
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
2658
2686
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
2660
--echo # returns no rows, when it should
2688
# returns no rows, when it should
2661
2689
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
2662
2690
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
2666
--echo #test cases from 29297
2667
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
2668
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;
2694
#test cases from 29297
2695
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
2696
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
2669
2697
INSERT INTO t1 VALUES (1),(2);
2670
2698
INSERT INTO t2 VALUES (1),(2);
2671
2699
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
2689
2717
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
2690
2718
DROP TABLE t1, t2;
2693
--echo # Bug #30788: Inconsistent retrieval of char/varchar
2721
# Bug #30788: Inconsistent retrieval of char/varchar
2696
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
2724
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
2697
2725
INSERT INTO t1 VALUES ('a', 'aa');
2698
2726
INSERT INTO t1 VALUES ('a', 'aaa');
2699
2727
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2700
2728
CREATE INDEX I1 ON t1 (a);
2701
2729
CREATE INDEX I2 ON t1 (b);
2702
--replace_column 9 #
2703
2730
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2704
2731
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2706
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
2733
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
2707
2734
INSERT INTO t2 SELECT * FROM t1;
2708
2735
CREATE INDEX I1 ON t2 (a);
2709
2736
CREATE INDEX I2 ON t2 (b);
2710
--replace_column 9 #
2711
2737
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2712
2738
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
2713
--replace_column 9 #
2715
2740
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2716
2741
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
2718
2743
DROP TABLE t1,t2;
2721
--echo # Bug #32400: Complex SELECT query returns correct result only on some
2746
# Bug #32400: Complex SELECT query returns correct result only on some
2725
CREATE TABLE t1(a INT, b INT);
2750
CREATE TABLE t1(a INT, b INT) ENGINE=MyISAM;
2726
2751
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
2728
2753
--error ER_BAD_FIELD_ERROR
added
removed
tests/t/subselect.test
