diff options
Diffstat (limited to 'mysql-test/t/select.test')
| -rw-r--r-- | mysql-test/t/select.test | 162 |
1 files changed, 152 insertions, 10 deletions
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 2caadd99dbb..7cb157f194e 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -6,8 +6,6 @@ # Simple select test # -use test; - drop table if exists t1,t2,t3,t4; CREATE TABLE t1 ( @@ -42,6 +40,7 @@ CREATE TABLE t2 ( # Populate table # +--disable_query_log INSERT INTO t2 VALUES (1,000001,00,'Omaha','teethe','neat',''); INSERT INTO t2 VALUES (2,011401,37,'breaking','dreaded','Steinberg','W'); INSERT INTO t2 VALUES (3,011402,37,'Romans','scholastics','jarring',''); @@ -1241,6 +1240,7 @@ INSERT INTO t2 VALUES (1190,123304,00,'bruises','Medicare','backer',''); INSERT INTO t2 VALUES (1191,068504,00,'bonfire','corresponds','positively',''); INSERT INTO t2 VALUES (1192,068305,00,'Colombo','hardware','colicky',''); INSERT INTO t2 VALUES (1193,000000,00,'nondecreasing','implant','thrillingly',''); +--enable_query_log # # Search with a key @@ -1366,6 +1366,7 @@ create table t3 ( key (name) ); +--disable_query_log INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1001,"Iranizes",37,5987435,234724); INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1002,"violinist",37,28357832,8723648); INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1003,"extramarital",37,39654943,235872); @@ -1376,6 +1377,7 @@ INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1007,"hand",154,9835 INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1008,"tucked",311,234298,3275892); INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1009,"gems",447,2374834,9872392); INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1010,"clinker",512,786542,76234234); +--enable_query_log create temporary table tmp type = myisam select * from t3; @@ -1405,9 +1407,9 @@ drop table tmp; # big table done -SET OPTION SQL_BIG_TABLES=1; +SET SQL_BIG_TABLES=1; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; select distinct fld5 from t2 limit 10; @@ -1416,9 +1418,9 @@ select distinct fld5 from t2 limit 10; # select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; -SET OPTION SQL_BIG_TABLES=1; # Force use of MyISAM +SET SQL_BIG_TABLES=1; # Force use of MyISAM select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; # @@ -1440,6 +1442,14 @@ select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr orde explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; # +# Some test with ORDER BY and limit +# + +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; +explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; + +# # Search with a constant table. # @@ -1490,6 +1500,7 @@ create table t4 ( UNIQUE KEY companyname(companyname) ) TYPE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; +--disable_query_log INSERT INTO t4 (companynr, companyname) VALUES (29,'company 1'); INSERT INTO t4 (companynr, companyname) VALUES (34,'company 2'); INSERT INTO t4 (companynr, companyname) VALUES (36,'company 3'); @@ -1502,6 +1513,7 @@ INSERT INTO t4 (companynr, companyname) VALUES (65,'company 9'); INSERT INTO t4 (companynr, companyname) VALUES (68,'company 10'); INSERT INTO t4 (companynr, companyname) VALUES (50,'company 11'); INSERT INTO t4 (companynr, companyname) VALUES (00,'Unknown'); +--enable_query_log # # Test of stright join to force a full join. @@ -1521,10 +1533,24 @@ select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 25 # # Test of left join. # +insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; +select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; +delete from t2 where fld1=999999; + +# +# Test left join optimization + +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; +# Following can't be optimized +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; # # Joins with forms. @@ -1712,11 +1738,19 @@ show full columns from t2 from test like 'f%'; show full columns from t2 from test like 's%'; show keys from t2; +drop table t4, t3, t2, t1; + +# +# Test of DO +# + +DO 1; +DO benchmark(100,1+1),1,1; + # # random in WHERE clause # -drop table t1; CREATE TABLE t1 ( id mediumint(8) unsigned NOT NULL auto_increment, pseudo varchar(35) NOT NULL default '', @@ -1725,10 +1759,118 @@ CREATE TABLE t1 ( ); INSERT INTO t1 (pseudo) VALUES ('test'); INSERT INTO t1 (pseudo) VALUES ('test1'); -SELECT 1 from t1 where rand() > 2; +SELECT 1 as rnd1 from t1 where rand() > 2; +DROP TABLE t1; # -# Drop the test tables +# Test of bug with SUM(CASE...) # -drop table t4, t3, t2, t1; +CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp(14) NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) TYPE=MyISAM; +INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); +CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) TYPE=MyISAM; +INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); +SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'NULL' AND b.sampletime < 'NULL' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; +DROP TABLE t1,t2; + +# +# Test of bigint comparision +# + +create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0'); +INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093); +select wss_type from t1 where wss_type ='102935229216544106'; +select wss_type from t1 where wss_type ='102935229216544105'; +select wss_type from t1 where wss_type ='102935229216544104'; +select wss_type from t1 where wss_type ='102935229216544093'; +select wss_type from t1 where wss_type =102935229216544093; +drop table t1; + +# +# Test of removing redundant braces in the FROM part +# (We test each construct with the braced join to the left and right; +# the latter case used to cause a syntax errors.) +# + +create table t1 (a int not null auto_increment primary key); +insert into t1 values (); +insert into t1 values (); +insert into t1 values (); +# , +select * from (t1 as t2 left join t1 as t3 using (a)), t1; +select * from t1, (t1 as t2 left join t1 as t3 using (a)); +# stright_join +select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1; +select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a)); +# inner join on +select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1; +select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; +# inner join using +select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a ); +select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a ); +# left [outer] join on +select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1; +select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; +# left join using +select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a ); +select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a ); +# natural left join +select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; +select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a)); +# right join on +select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; +select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; +# right [outer] joing using +select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a ); +select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a ); +# natural right join +select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1; +select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a)); +# natural join +select * from t1 natural join (t1 as t2 left join t1 as t3 using (a)); +select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1; +drop table t1; + +CREATE TABLE t1 ( aa char(2), id int(11) NOT NULL auto_increment, t2_id int(11) NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) TYPE=MyISAM; +INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522); +CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) TYPE=MyISAM; +INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); +select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; +drop table t1,t2; + +# +# outer join, impossible on condition, where, and usable key for range +# +create table t1 (id1 int NOT NULL); +create table t2 (id2 int NOT NULL); +create table t3 (id3 int NOT NULL); +create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); + +insert into t1 values (1); +insert into t1 values (2); +insert into t2 values (1); +insert into t4 values (1,1); + +explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; +select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; + +drop table t1,t2,t3,t4; + +# +# Bug #2298 +# + +create table t1(s varchar(10) not null); +create table t2(s varchar(10) not null primary key); +create table t3(s varchar(10) not null primary key); +insert into t1 values ('one\t'), ('two\t'); +insert into t2 values ('one\r'), ('two\t'); +insert into t3 values ('one '), ('two\t'); +select * from t1 where s = 'one'; +select * from t2 where s = 'one'; +select * from t3 where s = 'one'; +select * from t1,t2 where t1.s = t2.s; +select * from t2,t3 where t2.s = t3.s; +drop table t1, t2, t3; |