diff options
Diffstat (limited to 'mysql-test/t/order_by.test')
| -rw-r--r-- | mysql-test/t/order_by.test | 167 |
1 files changed, 167 insertions, 0 deletions
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test new file mode 100644 index 00000000000..9e878b21f93 --- /dev/null +++ b/mysql-test/t/order_by.test @@ -0,0 +1,167 @@ +# +# Bug with order by +# + +drop table if exists t1,t2,t3; + +CREATE TABLE t1 ( + id int(6) DEFAULT '0' NOT NULL, + idservice int(5), + clee char(20) NOT NULL, + flag char(1), + KEY id (id), + PRIMARY KEY (clee) +); + + +INSERT INTO t1 VALUES (2,4,'6067169d','Y'); +INSERT INTO t1 VALUES (2,5,'606716d1','Y'); +INSERT INTO t1 VALUES (2,1,'606717c1','Y'); +INSERT INTO t1 VALUES (3,1,'6067178d','Y'); +INSERT INTO t1 VALUES (2,6,'60671515','Y'); +INSERT INTO t1 VALUES (2,7,'60671569','Y'); +INSERT INTO t1 VALUES (2,3,'dd','Y'); + +CREATE TABLE t2 ( + id int(6) DEFAULT '0' NOT NULL auto_increment, + description varchar(40) NOT NULL, + idform varchar(40), + ordre int(6) unsigned DEFAULT '0' NOT NULL, + image varchar(60), + PRIMARY KEY (id), + KEY id (id,ordre) +); + +# +# Dumping data for table 't2' +# + +INSERT INTO t2 VALUES (1,'Emettre un appel d''offres','en_construction.html',10,'emettre.gif'); +INSERT INTO t2 VALUES (2,'Emettre des soumissions','en_construction.html',20,'emettre.gif'); +INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif'); +INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif'); +INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif'); +INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif'); +INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); +INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif'); +INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif'); +INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif'); + + +select t1.id,t1.idservice,t2.ordre,t2.description from t1, t2 where t1.id = 2 and t1.idservice = t2.id order by t2.ordre; + +drop table t1,t2; + +# +# Test of ORDER BY on concat() result +# + +create table t1 (first char(10),last char(10)); +insert into t1 values ("Michael","Widenius"); +insert into t1 values ("Allan","Larsson"); +insert into t1 values ("David","Axmark"); +select concat(first," ",last) as name from t1 order by name; +select concat(last," ",first) as name from t1 order by name; +drop table t1; + +# +# bug in distinct + order by +# + +create table t1 (i int); +insert into t1 values(1),(2),(1),(2),(1),(2),(3); +select distinct i from t1; +select distinct i from t1 order by rand(5); +select distinct i from t1 order by i desc; +select distinct i from t1 order by 1-i; +select distinct i from t1 order by mod(i,2); +drop table t1; + +# +# Order by on first index part +# + +create table t1 (id int not null,col1 int not null,col2 int not null,index(col1)); +insert into t1 values(1,2,2),(2,2,1),(3,1,2),(4,1,1),(5,1,4),(6,2,3),(7,3,1),(8,2,4); +select * from t1 order by col1,col2; +select col1 from t1 order by id; +select col1 as id from t1 order by t1.id; +select concat(col1) as id from t1 order by t1.id; +drop table t1; + +# +# Test of order by on field() +# + +CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta timestamp); +insert into t1 (aika) values ('Keskiviikko'); +insert into t1 (aika) values ('Tiistai'); +insert into t1 (aika) values ('Maanantai'); +insert into t1 (aika) values ('Sunnuntai'); + +SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test; +drop table t1; + +# +# Test of ORDER BY on IF +# + +CREATE TABLE t1 +( + a int unsigned NOT NULL, + b int unsigned NOT NULL, + c int unsigned NOT NULL, + UNIQUE(a), + INDEX(b), + INDEX(c) +); + +CREATE TABLE t2 +( + c int unsigned NOT NULL, + i int unsigned NOT NULL, + INDEX(c) +); + +CREATE TABLE t3 +( + c int unsigned NOT NULL, + v varchar(64), + INDEX(c) +); + +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (2,1,2); +INSERT INTO t1 VALUES (3,2,1); +INSERT INTO t1 VALUES (4,2,2); +INSERT INTO t2 VALUES (1,50); +INSERT INTO t2 VALUES (2,25); +INSERT INTO t3 VALUES (1,'123 Park Place'); +INSERT INTO t3 VALUES (2,'453 Boardwalk'); + +SELECT a,b,if(b = 1,i,if(b = 2,v,'')) +FROM t1 +LEFT JOIN t2 USING(c) +LEFT JOIN t3 ON t3.c = t1.c; + +SELECT a,b,if(b = 1,i,if(b = 2,v,'')) +FROM t1 +LEFT JOIN t2 USING(c) +LEFT JOIN t3 ON t3.c = t1.c +ORDER BY a; + +drop table t1,t2,t3; + +# +# Test of ORDER BY (Bug found by Dean Edmonds) +# + +create table t1 (ID int not null primary key, TransactionID int not null); +insert into t1 (ID, TransactionID) values (1, 87), (2, 89), (3, 92), (4, 94), (5, 486), (6, 490), (7, 753), (9, 828), (10, 832), (11, 834), (12, 840); +create table t2 (ID int not null primary key, GroupID int not null); + insert into t2 (ID, GroupID) values (87, 87), (89, 89), (92, 92), (94, 94), (486, 486), (490, 490),(753, 753), (828, 828), (832, 832), (834, 834), (840, 840); +create table t3 (ID int not null primary key, DateOfAction date not null); +insert into t3 (ID, DateOfAction) values (87, '1999-07-19'), (89, '1999-07-19'), (92, '1999-07-19'), (94, '1999-07-19'), (486, '1999-07-18'), (490, '2000-03-27'), (753, '2000-03-28'), (828, '1999-07-27'), (832, '1999-07-27'),(834, '1999-07-27'), (840, '1999-07-27'); +select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t3.DateOfAction, t1.TransactionID; +select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t1.TransactionID,t3.DateOfAction; +drop table t1,t2,t3; |