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
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 '',
267
geboortedatum date NOT NULL default '0000-00-00',
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)
282
) ENGINE=MyISAM PACK_KEYS=1;
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;
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;
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);
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,
500
`date` datetime NOT NULL default '0000-00-00 00:00:00',
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 0000-00-00 00:00:00 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 0000-00-00 00:00:00 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 0000-00-00 00:00:00 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 0000-00-00 00:00:00 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 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;
1638
Table Op Msg_type Msg_text
1639
test.t1 optimize status OK
1641
insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10;
1642
select * from t1 where a=1 and b in (1) order by c, b, a;
1648
select * from t1 where a=1 and b in (1);
1655
create table t1 (col1 int, col int);
1656
create table t2 (col2 int, col int);
1657
insert into t1 values (1,1),(2,2),(3,3);
1658
insert into t2 values (1,3),(2,2),(3,1);
1659
select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2)
1665
select col1 as col, col from t1 order by col;
1666
ERROR 23000: Column 'col' in order clause is ambiguous
1667
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
1669
ERROR 23000: Column 'col' in order clause is ambiguous
1670
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
1672
ERROR 23000: Column 'col' in order clause is ambiguous
1673
select col1 from t1, t2 where t1.col1=t2.col2 order by col;
1674
ERROR 23000: Column 'col' in order clause is ambiguous
1675
select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2
1677
ERROR 23000: Column 'col' in order clause is ambiguous
1678
select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2
1684
select col2 as c, col as c from t2 order by col;
1689
select col2 as col, col as col2 from t2 order by col;
1694
select t2.col2, t2.col, t2.col from t2 order by col;
1699
select t2.col2 as col from t2 order by t2.col;
1704
select t2.col2 as col, t2.col from t2 order by t2.col;
1709
select t2.col2, t2.col, t2.col from t2 order by t2.col;
1715
create table t1 (a char(25));
1716
insert into t1 set a = repeat('x', 20);
1717
insert into t1 set a = concat(repeat('x', 19), 'z');
1718
insert into t1 set a = concat(repeat('x', 19), 'ab');
1719
insert into t1 set a = concat(repeat('x', 19), 'aa');
1720
set max_sort_length=20;
1721
select a from t1 order by a;
1723
xxxxxxxxxxxxxxxxxxxx
1724
xxxxxxxxxxxxxxxxxxxz
1725
xxxxxxxxxxxxxxxxxxxab
1726
xxxxxxxxxxxxxxxxxxxaa
1729
`sid` decimal(8,0) default null,
1730
`wnid` varchar(11) not null default '',
1731
key `wnid14` (`wnid`(4)),
1734
insert into t1 (`sid`, `wnid`) values
1735
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
1736
('39560','01019090000'),('37989','01019000000'),('37990','01019011000'),
1737
('37991','01019011000'),('37992','01019019000'),('37993','01019030000'),
1738
('37994','01019090000'),('475','02070000000'),('25253','02071100000'),
1739
('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),
1740
('25259','02071190000'),('25260','02071200000'),('25261','02071210000'),
1741
('25262','02071290000'),('25263','02071300000'),('25264','02071310000'),
1742
('25265','02071310000'),('25266','02071320000'),('25267','02071320000'),
1743
('25269','02071330000'),('25270','02071340000'),('25271','02071350000'),
1744
('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),
1745
('25282','02071391000'),('25283','02071399000'),('25284','02071400000'),
1746
('25285','02071410000'),('25286','02071410000'),('25287','02071420000'),
1747
('25288','02071420000'),('25291','02071430000'),('25290','02071440000'),
1748
('25292','02071450000'),('25293','02071460000'),('25294','02071470000'),
1749
('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
1750
explain select * from t1 where wnid like '0101%' order by wnid;
1751
id select_type table type possible_keys key key_len ref rows Extra
1752
1 SIMPLE t1 range wnid14,wnid wnid 46 NULL 10 Using where
1753
select * from t1 where wnid like '0101%' order by wnid;
1766
CREATE TABLE t1 (a int);
1767
INSERT INTO t1 VALUES (2), (1), (1), (2), (1);
1768
SELECT a FROM t1 ORDER BY a;
1775
(SELECT a FROM t1) ORDER BY a;
1783
CREATE TABLE t1 (a int, b int);
1784
INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10);
1785
(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a;
1793
(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC;
1801
(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b;
1809
(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b;
1815
CREATE TABLE t1 (a INT);
1816
INSERT INTO t1 VALUES (1),(2);
1817
SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
1821
SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
1825
SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
1829
SELECT a + 1 AS num FROM t1 HAVING 30 - num;
1833
SELECT a + 1 AS num, num + 1 FROM t1;
1834
ERROR 42S22: Unknown column 'num' in 'field list'
1835
SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
1836
num (select num + 2 FROM t1 LIMIT 1)
1839
SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
1840
ERROR 42S22: Unknown column 'num' in 'on clause'
1842
CREATE TABLE bug25126 (
1843
val int NOT NULL AUTO_INCREMENT PRIMARY KEY
1845
UPDATE bug25126 SET MissingCol = MissingCol;
1846
ERROR 42S22: Unknown column 'MissingCol' in 'field list'
1847
UPDATE bug25126 SET val = val ORDER BY MissingCol;
1848
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1849
UPDATE bug25126 SET val = val ORDER BY val;
1850
UPDATE bug25126 SET val = 1 ORDER BY val;
1851
UPDATE bug25126 SET val = 1 ORDER BY MissingCol;
1852
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1853
UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol;
1854
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1855
UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol;
1856
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1857
UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol;
1858
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1859
UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol;
1860
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1861
UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol;
1862
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1863
UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol;
1864
ERROR 42S22: Unknown column 'MissingCol' in 'order clause'
1865
DROP TABLE bug25126;
1866
CREATE TABLE t1 (a int);
1867
SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1;
1869
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val;
1870
ERROR 23000: Column 'val' in order clause is ambiguous
1871
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1;
1872
ERROR 23000: Column 'val' in order clause is ambiguous
1874
CREATE TABLE t1 (a int);
1875
INSERT INTO t1 VALUES (3), (2), (4), (1);
1876
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
1877
ORDER BY IF(a IN (2,3), a, a+10);
1878
a IF(a IN (2,3), a, a+10)
1883
SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1
1884
ORDER BY IF(a NOT IN (2,3), a, a+10);
1885
a IF(a NOT IN (2,3), a, a+10)
1890
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
1891
ORDER BY IF(a NOT IN (2,3), a, a+10);
1892
a IF(a IN (2,3), a, a+10)
1897
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
1898
ORDER BY IF(a BETWEEN 2 AND 3, a, a+10);
1899
a IF(a BETWEEN 2 AND 3, a, a+10)
1904
SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1
1905
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);
1906
a IF(a NOT BETWEEN 2 AND 3, a, a+10)
1911
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
1912
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);
1913
a IF(a BETWEEN 2 AND 3, a, a+10)
1918
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
1919
FROM t1 GROUP BY x1, x2;
1925
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
1926
FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, '');
1932
SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2);
1938
SELECT a FROM t1 ORDER BY a IN (1,2);
1944
SELECT a+10 FROM t1 ORDER BY a IN (1,2);
1950
SELECT a, IF(a IN (1,2), a, a+10) FROM t1
1951
ORDER BY IF(a IN (3,4), a, a+10);
1952
a IF(a IN (1,2), a, a+10)
1958
create table t1 (a int not null, b int not null, c int not null);
1959
insert t1 values (1,1,1),(1,1,2),(1,2,1);
1960
select a, b from t1 group by a, b order by sum(c);
1965
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
1966
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
1967
explain SELECT t1.b as a, t2.b as c FROM
1968
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
1970
id select_type table type possible_keys key key_len ref rows Extra
1971
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
1972
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
1973
SELECT t2.b as c FROM
1974
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
1980
explain SELECT t1.b as a, t2.b as c FROM
1981
t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
1983
id select_type table type possible_keys key key_len ref rows Extra
1984
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1985
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
1986
CREATE TABLE t2 LIKE t1;
1987
INSERT INTO t2 SELECT * from t1;
1988
CREATE TABLE t3 LIKE t1;
1989
INSERT INTO t3 SELECT * from t1;
1990
CREATE TABLE t4 LIKE t1;
1991
INSERT INTO t4 SELECT * from t1;
1992
INSERT INTO t1 values (0,0),(4,4);
1993
SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
1994
ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
2001
DROP TABLE t1,t2,t3,t4;
2002
create table t1 (a int, b int, c int);
2003
insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
2004
select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
2010
CREATE TABLE t1 (a INT NOT NULL, b TIME);
2011
INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
2012
UPDATE t1 SET b = SEC_TO_TIME(a);
2013
SELECT a, b FROM t1 ORDER BY b DESC;
2022
SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
2032
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
2033
INSERT INTO t1 VALUES (1,1),(2,2);
2034
CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
2035
INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
2036
SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
2047
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
2048
INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
2049
INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
2050
INSERT INTO t1 SELECT a +16, b +16 FROM t1;
2051
INSERT INTO t1 SELECT a +32, b +32 FROM t1;
2052
INSERT INTO t1 SELECT a +64, b +64 FROM t1;
2053
SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
2183
SELECT @tmp_tables_after = @tmp_tables_before ;
2184
@tmp_tables_after = @tmp_tables_before
2186
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
2316
SELECT @tmp_tables_after = @tmp_tables_before;
2317
@tmp_tables_after = @tmp_tables_before
2321
# Bug#31590: Wrong error message on sort buffer being too small.
2323
create table t1(a int, b text);
2324
insert into t1 values (1,2),(3,2);
2325
set session sort_buffer_size= 30000;
2327
Warning 1292 Truncated incorrect sort_buffer_size value: '30000'
2328
set session max_sort_length= 2180;
2329
select * from t1 order by b;
2334
CREATE TABLE t2 (a varchar(32), b int, c float, d double,
2335
UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c));
2336
CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));
2337
CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b));
2338
INSERT INTO t3 SELECT * FROM t1;
2339
SELECT d FROM t1, t2
2340
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
2341
ORDER BY t2.c LIMIT 1;
2344
SELECT d FROM t3 AS t1, t2 AS t2
2345
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
2346
ORDER BY t2.c LIMIT 1;
2349
DROP TABLE t1,t2,t3;
added
removed
tests/r/order_by.result
