1
drop table if exists t1,t2,t3;
3
id int DEFAULT '0' NOT NULL,
5
clee varchar(20) NOT NULL,
10
INSERT INTO t1 VALUES (2,4,'6067169d','Y');
11
INSERT INTO t1 VALUES (2,5,'606716d1','Y');
12
INSERT INTO t1 VALUES (2,1,'606717c1','Y');
13
INSERT INTO t1 VALUES (3,1,'6067178d','Y');
14
INSERT INTO t1 VALUES (2,6,'60671515','Y');
15
INSERT INTO t1 VALUES (2,7,'60671569','Y');
16
INSERT INTO t1 VALUES (2,3,'dd','Y');
18
id int NOT NULL auto_increment,
19
description varchar(40) NOT NULL,
21
ordre int DEFAULT '0' NOT NULL,
26
INSERT INTO t2 VALUES (1,'Emettre un appel d''offres','en_construction.html',10,'emettre.gif');
27
INSERT INTO t2 VALUES (2,'Emettre des soumissions','en_construction.html',20,'emettre.gif');
28
INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif');
29
INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif');
30
INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif');
31
INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif');
32
INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif');
33
INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif');
34
INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif');
35
INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif');
36
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;
37
id idservice ordre description
38
2 1 10 Emettre un appel d'offres
39
2 3 40000 Créer une fiche de client
40
2 4 40010 Modifier des clients
41
2 5 40020 Effacer des clients
42
2 6 51050 Ajouter un service
43
2 7 51060 Liste des t2
45
create table t1 (first char(10),last char(10));
46
insert into t1 values ("Michael","Widenius");
47
insert into t1 values ("Allan","Larsson");
48
insert into t1 values ("David","Axmark");
49
select concat(first," ",last) as name from t1 order by name;
54
select concat(last," ",first) as name from t1 order by name;
60
create table t1 (i int);
61
insert into t1 values(1),(2),(1),(2),(1),(2),(3);
62
select distinct i from t1;
67
select distinct i from t1 order by rand(5);
72
select distinct i from t1 order by i desc;
77
select distinct i from t1 order by 1-i;
82
select distinct i from t1 order by mod(i,2),i;
88
create table t1 ( pk int primary key, name varchar(255) not null, number varchar(255) not null);
89
insert into t1 values (1, 'Gamma', '123'), (2, 'Gamma Ext', '123a'), (3, 'Alpha', '001'), (4, 'Beta', '200c');
90
select distinct t1.name as 'Building Name',t1.number as 'Building Number' from t1 order by t1.name asc;
91
Building Name Building Number
97
create table t1 (id int not null,col1 int not null,col2 int not null,index(col1));
98
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);
99
select * from t1 order by col1,col2;
109
select col1 from t1 order by id;
119
select col1 as id from t1 order by id;
129
select concat(col1) as id from t1 order by id;
140
CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta timestamp);
141
insert into t1 (aika) values ('Keskiviikko');
142
insert into t1 (aika) values ('Tiistai');
143
insert into t1 (aika) values ('Maanantai');
144
insert into t1 (aika) values ('Sunnuntai');
145
SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test;
173
INSERT INTO t1 VALUES (1,1,1);
174
INSERT INTO t1 VALUES (2,1,2);
175
INSERT INTO t1 VALUES (3,2,1);
176
INSERT INTO t1 VALUES (4,2,2);
177
INSERT INTO t2 VALUES (1,50);
178
INSERT INTO t2 VALUES (2,25);
179
INSERT INTO t3 VALUES (1,'123 Park Place');
180
INSERT INTO t3 VALUES (2,'453 Boardwalk');
181
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
183
LEFT JOIN t2 USING(c)
184
LEFT JOIN t3 ON t3.c = t1.c;
185
a b if(b = 1,i,if(b = 2,v,''))
190
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
192
LEFT JOIN t2 ON t1.c = t2.c
193
LEFT JOIN t3 ON t3.c = t1.c;
194
a b if(b = 1,i,if(b = 2,v,''))
199
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
201
LEFT JOIN t2 USING(c)
202
LEFT JOIN t3 ON t3.c = t1.c
204
a b if(b = 1,i,if(b = 2,v,''))
209
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
211
LEFT JOIN t2 ON t1.c = t2.c
212
LEFT JOIN t3 ON t3.c = t1.c
214
a b if(b = 1,i,if(b = 2,v,''))
220
create table t1 (ID int not null primary key, TransactionID int not null);
221
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);
222
create table t2 (ID int not null primary key, GroupID int not null);
223
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);
224
create table t3 (ID int not null primary key, DateOfAction date not null);
225
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');
226
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;
227
DateOfAction TransactionID
239
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;
240
DateOfAction TransactionID
253
CREATE TEMPORARY TABLE t1 (
254
member_id int NOT NULL auto_increment,
255
inschrijf_datum varchar(20) NOT NULL default '',
256
lastchange_datum varchar(20) NOT NULL default '',
257
nickname varchar(20) NOT NULL default '',
258
password varchar(8) NOT NULL default '',
259
voornaam varchar(30) NOT NULL default '',
260
tussenvoegsels varchar(10) NOT NULL default '',
261
achternaam varchar(50) NOT NULL default '',
262
straat varchar(100) NOT NULL default '',
263
postcode varchar(10) NOT NULL default '',
264
wijk varchar(40) NOT NULL default '',
265
plaats varchar(50) NOT NULL default '',
266
telefoon varchar(10) NOT NULL default '',
268
geslacht varchar(5) NOT NULL default '',
269
email varchar(80) NOT NULL default '',
270
uin varchar(15) NOT NULL default '',
271
homepage varchar(100) NOT NULL default '',
272
internet varchar(15) NOT NULL default '',
273
scherk varchar(30) NOT NULL default '',
274
favo_boek varchar(50) NOT NULL default '',
275
favo_tijdschrift varchar(50) NOT NULL default '',
276
favo_tv varchar(50) NOT NULL default '',
277
favo_eten varchar(50) NOT NULL default '',
278
favo_muziek varchar(30) NOT NULL default '',
279
info text NOT NULL default '',
280
ipnr varchar(30) NOT NULL default '',
281
PRIMARY KEY (member_id)
283
insert into t1 (member_id) values (1),(2),(3);
284
select member_id, nickname, voornaam FROM t1
285
ORDER by lastchange_datum DESC LIMIT 2;
286
member_id nickname voornaam
290
create table t1 (a int not null, b int, c varchar(10), key (a, b, c));
291
insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
292
explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
293
id select_type table type possible_keys key key_len ref rows Extra
294
1 SIMPLE t1 index a a 52 NULL 11 Using where; Using index
295
select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
298
explain select * from t1 where a >= 1 and a < 3 order by a desc;
299
id select_type table type possible_keys key key_len ref rows Extra
300
1 SIMPLE t1 range a a 4 NULL 5 Using where; Using index
301
select * from t1 where a >= 1 and a < 3 order by a desc;
314
explain select * from t1 where a = 1 order by a desc, b desc;
315
id select_type table type possible_keys key key_len ref rows Extra
316
1 SIMPLE t1 ref a a 4 const 5 Using where; Using index
317
select * from t1 where a = 1 order by a desc, b desc;
325
explain select * from t1 where a = 1 and b is null order by a desc, b desc;
326
id select_type table type possible_keys key key_len ref rows Extra
327
1 SIMPLE t1 ref a a 9 const,const 2 Using where; Using index; Using filesort
328
select * from t1 where a = 1 and b is null order by a desc, b desc;
332
explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc;
333
id select_type table type possible_keys key key_len ref rows Extra
334
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index
335
explain select * from t1 where a = 2 and b >0 order by a desc,b desc;
336
id select_type table type possible_keys key key_len ref rows Extra
337
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index
338
explain select * from t1 where a = 2 and b is null order by a desc,b desc;
339
id select_type table type possible_keys key key_len ref rows Extra
340
1 SIMPLE t1 ref a a 9 const,const 1 Using where; Using index; Using filesort
341
explain select * from t1 where a = 2 and (b is null or b > 0) order by a
343
id select_type table type possible_keys key key_len ref rows Extra
344
1 SIMPLE t1 range a a 9 NULL 6 Using where; Using index
345
explain select * from t1 where a = 2 and b > 0 order by a desc,b desc;
346
id select_type table type possible_keys key key_len ref rows Extra
347
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index
348
explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
349
id select_type table type possible_keys key key_len ref rows Extra
350
1 SIMPLE t1 range a a 9 NULL 1 Using where; Using index
351
explain select * from t1 where a = 1 order by b desc;
352
id select_type table type possible_keys key key_len ref rows Extra
353
1 SIMPLE t1 ref a a 4 const 5 Using where; Using index
354
select * from t1 where a = 1 order by b desc;
362
delete from t1 WHERE b IS NULL OR c IS NULL;
363
alter table t1 modify b bigint not null, modify c varchar(100) not null;
364
explain select * from t1 order by a, b, c;
365
id select_type table type possible_keys key key_len ref rows Extra
366
1 SIMPLE t1 index NULL a 414 NULL 8 Using index
367
select * from t1 order by a, b, c;
377
explain select * from t1 order by a desc, b desc, c desc;
378
id select_type table type possible_keys key key_len ref rows Extra
379
1 SIMPLE t1 index NULL a 414 NULL 8 Using index
380
select * from t1 order by a desc, b desc, c desc;
390
explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
391
id select_type table type possible_keys key key_len ref rows Extra
392
1 SIMPLE t1 range a a 414 NULL 3 Using where; Using index
393
select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
397
explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
398
id select_type table type possible_keys key key_len ref rows Extra
399
1 SIMPLE t1 range a a 4 NULL 3 Using where; Using index
400
select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
404
select count(*) from t1 where a < 5 and b > 0;
407
select * from t1 where a < 5 and b > 0 order by a desc,b desc;
417
explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
418
id select_type table type possible_keys key key_len ref rows Extra
419
1 SIMPLE t1 range a a 12 NULL 4 Using where; Using index
420
select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
426
explain select * from t1 where a between 0 and 1 order by a desc, b desc;
427
id select_type table type possible_keys key key_len ref rows Extra
428
1 SIMPLE t1 range a a 4 NULL 3 Using where; Using index
429
select * from t1 where a between 0 and 1 order by a desc, b desc;
435
CREATE TEMPORARY TABLE t1 (
436
gid int NOT NULL auto_increment,
437
cid int NOT NULL default '0',
439
KEY component_id (cid)
441
INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108);
442
ALTER TABLE t1 add skr int not null;
443
CREATE TEMPORARY TABLE t2 (
444
gid int NOT NULL default '0',
445
uid int NOT NULL default '1',
446
sid int NOT NULL default '1',
451
INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5);
452
CREATE TEMPORARY TABLE t3 (
453
uid int NOT NULL auto_increment,
456
INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250);
457
ALTER TABLE t3 add skr int not null;
458
select t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
466
select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
474
select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;
482
SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
484
SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
492
SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
494
SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
498
`titre` char(80) NOT NULL default '',
499
`numeropost` int NOT NULL auto_increment,
501
`auteur` char(35) NOT NULL default '',
502
`icone` int NOT NULL default '0',
503
`lastauteur` char(35) NOT NULL default '',
504
`nbrep` int NOT NULL default '0',
505
`dest` char(35) NOT NULL default '',
506
`lu` int NOT NULL default '0',
507
`vue` int NOT NULL default '0',
508
`ludest` int NOT NULL default '0',
509
`ouvert` int NOT NULL default '1',
510
PRIMARY KEY (`numeropost`),
512
KEY `dest` (`dest`,`ludest`),
513
KEY `auteur` (`auteur`,`lu`),
514
KEY `auteur_2` (`auteur`,`date`),
515
KEY `dest_2` (`dest`,`date`)
518
`numeropost` int NOT NULL default '0',
519
`pseudo` char(35) NOT NULL default '',
520
PRIMARY KEY (`numeropost`,`pseudo`),
521
KEY `pseudo` (`pseudo`)
523
INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
524
INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
525
SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
526
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest
527
test 1 joce 0 0 0 NULL 0 1 bug
528
SELECT titre,numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
529
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest
530
test 1 joce 0 0 0 NULL 0 1 bug
531
SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
532
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest
533
test 1 joce 0 0 0 NULL 0 1 bug
534
SELECT titre,numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
535
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest
536
test 1 joce 0 0 0 NULL 0 1 bug
538
CREATE TABLE t1 (a int, b int);
539
INSERT INTO t1 VALUES (1, 2);
540
INSERT INTO t1 VALUES (3, 4);
541
INSERT INTO t1 VALUES (5, NULL);
542
SELECT * FROM t1 ORDER BY b;
547
SELECT * FROM t1 ORDER BY b DESC;
552
SELECT * FROM t1 ORDER BY (a + b);
557
SELECT * FROM t1 ORDER BY (a + b) DESC;
563
create table t1(id int not null auto_increment primary key, t char(12));
564
select id,t from t1 force index (primary) order by id;
1568
FieldKey varchar(36) NOT NULL default '',
1569
LongVal bigint default NULL,
1571
KEY FieldKey (FieldKey),
1572
KEY LongField (FieldKey,LongVal),
1573
KEY StringField (FieldKey,StringVal(32))
1575
INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3');
1576
SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
1577
FieldKey LongVal StringVal
1581
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
1582
FieldKey LongVal StringVal
1586
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
1587
FieldKey LongVal StringVal
1592
CREATE TABLE t1 (a INT, b INT);
1594
UPDATE t1 SET a=0 ORDER BY (a=@id), b;
1596
CREATE TEMPORARY TABLE t1 ( id int NOT NULL default '0', menu int NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM;
1597
INSERT INTO t1 VALUES (11384, 2),(11392, 2);
1598
SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
1602
create table t1(a int, b int, index(b));
1603
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
1604
explain select * from t1 where b=1 or b is null order by a;
1605
id select_type table type possible_keys key key_len ref rows Extra
1606
1 SIMPLE t1 ALL b NULL NULL NULL 6 Using where; Using filesort
1607
select * from t1 where b=1 or b is null order by a;
1613
explain select * from t1 where b=2 or b is null order by a;
1614
id select_type table type possible_keys key key_len ref rows Extra
1615
1 SIMPLE t1 ALL b NULL NULL NULL 6 Using where; Using filesort
1616
select * from t1 where b=2 or b is null order by a;
1623
create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null,
1624
key(a,b,d), key(c,b,a));
1625
create table t2 like t1;
1626
insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3);
1627
insert into t2 select null, b, c, d from t1;
1628
insert into t1 select null, b, c, d from t2;
1629
insert into t2 select null, b, c, d from t1;
1630
insert into t1 select null, b, c, d from t2;
1631
insert into t2 select null, b, c, d from t1;
1632
insert into t1 select null, b, c, d from t2;
1633
insert into t2 select null, b, c, d from t1;
1634
insert into t1 select null, b, c, d from t2;
1635
insert into t2 select null, b, c, d from t1;
1636
insert into t1 select null, b, c, d from t2;
1637
alter table t1 engine="default";
1639
insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10;
1640
select * from t1 where a=1 and b in (1) order by c, b, a;
1646
select * from t1 where a=1 and b in (1);
1653
create table t1 (col1 int, col int);
1654
create table t2 (col2 int, col int);
1655
insert into t1 values (1,1),(2,2),(3,3);
1656
insert into t2 values (1,3),(2,2),(3,1);
1657
select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2)
1663
select col1 as col, col from t1 order by col;
1664
ERROR 23000: Column 'col' in order clause is ambiguous
1665
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
1667
ERROR 23000: Column 'col' in order clause is ambiguous
1668
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
1670
ERROR 23000: Column 'col' in order clause is ambiguous
1671
select col1 from t1, t2 where t1.col1=t2.col2 order by col;
1672
ERROR 23000: Column 'col' in order clause is ambiguous
1673
select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2
1675
ERROR 23000: Column 'col' in order clause is ambiguous
1676
select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2
1682
select col2 as c, col as c from t2 order by col;
1687
select col2 as col, col as col2 from t2 order by col;
1692
select t2.col2, t2.col, t2.col from t2 order by col;
1697
select t2.col2 as col from t2 order by t2.col;
1702
select t2.col2 as col, t2.col from t2 order by t2.col;
1707
select t2.col2, t2.col, t2.col from t2 order by t2.col;
1713
create table t1 (a char(25));
1714
insert into t1 set a = repeat('x', 20);
1715
insert into t1 set a = concat(repeat('x', 19), 'z');
1716
insert into t1 set a = concat(repeat('x', 19), 'ab');
1717
insert into t1 set a = concat(repeat('x', 19), 'aa');
1718
set max_sort_length=20;
1719
select a from t1 order by a;
1721
xxxxxxxxxxxxxxxxxxxx
1722
xxxxxxxxxxxxxxxxxxxz
1723
xxxxxxxxxxxxxxxxxxxab
1724
xxxxxxxxxxxxxxxxxxxaa
1726
create temporary table t1 (
1727
`sid` decimal(8,0) default null,
1728
`wnid` varchar(11) not null default '',
1729
key `wnid14` (`wnid`(4)),
1732
insert into t1 (`sid`, `wnid`) values
1733
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
1734
('39560','01019090000'),('37989','01019000000'),('37990','01019011000'),
1735
('37991','01019011000'),('37992','01019019000'),('37993','01019030000'),
1736
('37994','01019090000'),('475','02070000000'),('25253','02071100000'),
1737
('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),
1738
('25259','02071190000'),('25260','02071200000'),('25261','02071210000'),
1739
('25262','02071290000'),('25263','02071300000'),('25264','02071310000'),
1740
('25265','02071310000'),('25266','02071320000'),('25267','02071320000'),
1741
('25269','02071330000'),('25270','02071340000'),('25271','02071350000'),
1742
('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),
1743
('25282','02071391000'),('25283','02071399000'),('25284','02071400000'),
1744
('25285','02071410000'),('25286','02071410000'),('25287','02071420000'),
1745
('25288','02071420000'),('25291','02071430000'),('25290','02071440000'),
1746
('25292','02071450000'),('25293','02071460000'),('25294','02071470000'),
1747
('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
1748
explain select * from t1 where wnid like '0101%' order by wnid;
1749
id select_type table type possible_keys key key_len ref rows Extra
1750
1 SIMPLE t1 range wnid14,wnid wnid 46 NULL 10 Using where
1751
select * from t1 where wnid like '0101%' order by wnid;
1764
CREATE TABLE t1 (a int);
1765
INSERT INTO t1 VALUES (2), (1), (1), (2), (1);
1766
SELECT a FROM t1 ORDER BY a;
1773
(SELECT a FROM t1) ORDER BY a;
1781
CREATE TABLE t1 (a int, b int);
1782
INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10);
1783
(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a;
1791
(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC;
1799
(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b;
1807
(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b;
1813
CREATE TABLE t1 (a INT);
1814
INSERT INTO t1 VALUES (1),(2);
1815
SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
1819
SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
1823
SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
1827
SELECT a + 1 AS num FROM t1 HAVING 30 - num;
1831
SELECT a + 1 AS num, num + 1 FROM t1;
1832
ERROR 42S22: Unknown column 'num' in 'field list'
1833
SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
1834
num (select num + 2 FROM t1 LIMIT 1)
1837
SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
1838
ERROR 42S22: Unknown column 'num' in 'on clause'
1840
CREATE TABLE bug25126 (
1841
val int NOT NULL AUTO_INCREMENT PRIMARY KEY
1843
UPDATE bug25126 SET MissingCol = MissingCol;
1844
ERROR 42S22: Unknown column 'MissingCol' in 'field list'
1845
UPDATE bug25126 SET val = val ORDER BY MissingCol;
1846
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1847
UPDATE bug25126 SET val = val ORDER BY val;
1848
UPDATE bug25126 SET val = 1 ORDER BY val;
1849
UPDATE bug25126 SET val = 1 ORDER BY MissingCol;
1850
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1851
UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol;
1852
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1853
UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol;
1854
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1855
UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol;
1856
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1857
UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol;
1858
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1859
UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol;
1860
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1861
UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol;
1862
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1863
DROP TABLE bug25126;
1864
CREATE TABLE t1 (a int);
1865
SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1;
1867
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val;
1868
ERROR 23000: Column 'val' in order clause is ambiguous
1869
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1;
1870
ERROR 23000: Column 'val' in order clause is ambiguous
1872
CREATE TABLE t1 (a int);
1873
INSERT INTO t1 VALUES (3), (2), (4), (1);
1874
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
1875
ORDER BY IF(a IN (2,3), a, a+10);
1876
a IF(a IN (2,3), a, a+10)
1881
SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1
1882
ORDER BY IF(a NOT IN (2,3), a, a+10);
1883
a IF(a NOT IN (2,3), a, a+10)
1888
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
1889
ORDER BY IF(a NOT IN (2,3), a, a+10);
1890
a IF(a IN (2,3), a, a+10)
1895
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
1896
ORDER BY IF(a BETWEEN 2 AND 3, a, a+10);
1897
a IF(a BETWEEN 2 AND 3, a, a+10)
1902
SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1
1903
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);
1904
a IF(a NOT BETWEEN 2 AND 3, a, a+10)
1909
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
1910
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);
1911
a IF(a BETWEEN 2 AND 3, a, a+10)
1916
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
1917
FROM t1 GROUP BY x1, x2;
1923
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
1924
FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, '');
1930
SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2);
1936
SELECT a FROM t1 ORDER BY a IN (1,2);
1942
SELECT a+10 FROM t1 ORDER BY a IN (1,2);
1948
SELECT a, IF(a IN (1,2), a, a+10) FROM t1
1949
ORDER BY IF(a IN (3,4), a, a+10);
1950
a IF(a IN (1,2), a, a+10)
1956
create table t1 (a int not null, b int not null, c int not null);
1957
insert t1 values (1,1,1),(1,1,2),(1,2,1);
1958
select a, b from t1 group by a, b order by sum(c);
1963
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
1964
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
1965
explain SELECT t1.b as a, t2.b as c FROM
1966
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
1968
id select_type table type possible_keys key key_len ref rows Extra
1969
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
1970
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
1971
SELECT t2.b as c FROM
1972
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
1978
explain SELECT t1.b as a, t2.b as c FROM
1979
t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
1981
id select_type table type possible_keys key key_len ref rows Extra
1982
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1983
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
1984
CREATE TABLE t2 LIKE t1;
1985
INSERT INTO t2 SELECT * from t1;
1986
CREATE TABLE t3 LIKE t1;
1987
INSERT INTO t3 SELECT * from t1;
1988
CREATE TABLE t4 LIKE t1;
1989
INSERT INTO t4 SELECT * from t1;
1990
INSERT INTO t1 values (0,0),(4,4);
1991
SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
1992
ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
1999
DROP TABLE t1,t2,t3,t4;
2000
create table t1 (a int, b int, c int);
2001
insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
2002
select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
2008
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
2009
INSERT INTO t1 VALUES (1,1),(2,2);
2010
CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
2011
INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
2012
SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
2023
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
2024
INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
2025
INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
2026
INSERT INTO t1 SELECT a +16, b +16 FROM t1;
2027
INSERT INTO t1 SELECT a +32, b +32 FROM t1;
2028
INSERT INTO t1 SELECT a +64, b +64 FROM t1;
2029
SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
2159
SELECT @tmp_tables_after = @tmp_tables_before ;
2160
@tmp_tables_after = @tmp_tables_before
2162
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
2292
SELECT @tmp_tables_after = @tmp_tables_before;
2293
@tmp_tables_after = @tmp_tables_before
2297
# Bug#31590: Wrong error message on sort buffer being too small.
2299
create table t1(a int, b text);
2300
insert into t1 values (1,2),(3,2);
2301
set session sort_buffer_size= 30000;
2303
Error 1292 Truncated incorrect sort_buffer_size value: '30000'
2304
set session max_sort_length= 2180;
2305
select * from t1 order by b;
2310
CREATE TABLE t2 (a varchar(32), b int, c float, d double,
2311
UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c));
2312
CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));
2313
CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b));
2314
INSERT INTO t3 SELECT * FROM t1;
2315
SELECT d FROM t1, t2
2316
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
2317
ORDER BY t2.c LIMIT 1;
2320
SELECT d FROM t3 AS t1, t2 AS t2
2321
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
2322
ORDER BY t2.c LIMIT 1;
2325
DROP TABLE t1,t2,t3;
added
removed
tests/r/order_by.result
