1call mtr.add_suppression("Sort aborted.*"); 2drop table if exists t1,t2,t3; 3call mtr.add_suppression("Out of sort memory; increase server sort buffer size"); 4CREATE TABLE t1 ( 5id int(6) DEFAULT '0' NOT NULL, 6idservice int(5), 7clee char(20) NOT NULL, 8flag char(1), 9KEY id (id), 10PRIMARY KEY (clee) 11); 12INSERT INTO t1 VALUES (2,4,'6067169d','Y'); 13INSERT INTO t1 VALUES (2,5,'606716d1','Y'); 14INSERT INTO t1 VALUES (2,1,'606717c1','Y'); 15INSERT INTO t1 VALUES (3,1,'6067178d','Y'); 16INSERT INTO t1 VALUES (2,6,'60671515','Y'); 17INSERT INTO t1 VALUES (2,7,'60671569','Y'); 18INSERT INTO t1 VALUES (2,3,'dd','Y'); 19CREATE TABLE t2 ( 20id int(6) NOT NULL auto_increment, 21description varchar(40) NOT NULL, 22idform varchar(40), 23ordre int(6) unsigned DEFAULT '0' NOT NULL, 24image varchar(60), 25PRIMARY KEY (id), 26KEY id (id,ordre) 27); 28INSERT INTO t2 VALUES (1,'Emettre un appel d''offres','en_construction.html',10,'emettre.gif'); 29INSERT INTO t2 VALUES (2,'Emettre des soumissions','en_construction.html',20,'emettre.gif'); 30INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif'); 31INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif'); 32INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif'); 33INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif'); 34INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); 35INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif'); 36INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif'); 37INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif'); 38select t1.id,t1.idservice,t2.ordre,t2.description from t1, t2 where t1.id = 2 and t1.idservice = t2.id order by t2.ordre; 39id idservice ordre description 402 1 10 Emettre un appel d'offres 412 3 40000 Créer une fiche de client 422 4 40010 Modifier des clients 432 5 40020 Effacer des clients 442 6 51050 Ajouter un service 452 7 51060 Liste des t2 46drop table t1,t2; 47create table t1 (first char(10),last char(10)); 48insert into t1 values ("Michael","Widenius"); 49insert into t1 values ("Allan","Larsson"); 50insert into t1 values ("David","Axmark"); 51select concat(first," ",last) as name from t1 order by name; 52name 53Allan Larsson 54David Axmark 55Michael Widenius 56select concat(last," ",first) as name from t1 order by name; 57name 58Axmark David 59Larsson Allan 60Widenius Michael 61drop table t1; 62create table t1 (i int); 63insert into t1 values(1),(2),(1),(2),(1),(2),(3); 64select distinct i from t1; 65i 661 672 683 69select distinct i from t1 order by rand(5); 70i 711 723 732 74select distinct i from t1 order by i desc; 75i 763 772 781 79select distinct i from t1 order by 1-i; 80i 813 822 831 84select distinct i from t1 order by mod(i,2),i; 85i 862 871 883 89drop table t1; 90create table t1 ( pk int primary key, name varchar(255) not null, number varchar(255) not null); 91insert into t1 values (1, 'Gamma', '123'), (2, 'Gamma Ext', '123a'), (3, 'Alpha', '001'), (4, 'Beta', '200c'); 92select distinct t1.name as 'Building Name',t1.number as 'Building Number' from t1 order by t1.name asc; 93Building Name Building Number 94Alpha 001 95Beta 200c 96Gamma 123 97Gamma Ext 123a 98drop table t1; 99create table t1 (id int not null,col1 int not null,col2 int not null,index(col1)); 100insert 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); 101select * from t1 order by col1,col2; 102id col1 col2 1034 1 1 1043 1 2 1055 1 4 1062 2 1 1071 2 2 1086 2 3 1098 2 4 1107 3 1 111select col1 from t1 order by id; 112col1 1132 1142 1151 1161 1171 1182 1193 1202 121select col1 as id from t1 order by id; 122id 1231 1241 1251 1262 1272 1282 1292 1303 131select concat(col1) as id from t1 order by id; 132id 1331 1341 1351 1362 1372 1382 1392 1403 141drop table t1; 142CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta timestamp); 143insert into t1 (aika) values ('Keskiviikko'); 144insert into t1 (aika) values ('Tiistai'); 145insert into t1 (aika) values ('Maanantai'); 146insert into t1 (aika) values ('Sunnuntai'); 147SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test; 148test 1491 1502 1513 1527 153drop table t1; 154CREATE TABLE t1 155( 156a int unsigned NOT NULL, 157b int unsigned NOT NULL, 158c int unsigned NOT NULL, 159UNIQUE(a), 160INDEX(b), 161INDEX(c) 162); 163CREATE TABLE t2 164( 165c int unsigned NOT NULL, 166i int unsigned NOT NULL, 167INDEX(c) 168); 169CREATE TABLE t3 170( 171c int unsigned NOT NULL, 172v varchar(64), 173INDEX(c) 174); 175INSERT INTO t1 VALUES (1,1,1); 176INSERT INTO t1 VALUES (2,1,2); 177INSERT INTO t1 VALUES (3,2,1); 178INSERT INTO t1 VALUES (4,2,2); 179INSERT INTO t2 VALUES (1,50); 180INSERT INTO t2 VALUES (2,25); 181INSERT INTO t3 VALUES (1,'123 Park Place'); 182INSERT INTO t3 VALUES (2,'453 Boardwalk'); 183SET @save_optimizer_switch=@@optimizer_switch; 184SET optimizer_switch='outer_join_with_cache=off'; 185SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 186FROM t1 187LEFT JOIN t2 USING(c) 188LEFT JOIN t3 ON t3.c = t1.c; 189a b if(b = 1,i,if(b = 2,v,'')) 1901 1 50 1912 1 25 1923 2 123 Park Place 1934 2 453 Boardwalk 194SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 195FROM t1 196LEFT JOIN t2 ON t1.c = t2.c 197LEFT JOIN t3 ON t3.c = t1.c; 198a b if(b = 1,i,if(b = 2,v,'')) 1991 1 50 2002 1 25 2013 2 123 Park Place 2024 2 453 Boardwalk 203SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 204FROM t1 205LEFT JOIN t2 USING(c) 206LEFT JOIN t3 ON t3.c = t1.c 207ORDER BY a; 208a b if(b = 1,i,if(b = 2,v,'')) 2091 1 50 2102 1 25 2113 2 123 Park Place 2124 2 453 Boardwalk 213SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 214FROM t1 215LEFT JOIN t2 ON t1.c = t2.c 216LEFT JOIN t3 ON t3.c = t1.c 217ORDER BY a; 218a b if(b = 1,i,if(b = 2,v,'')) 2191 1 50 2202 1 25 2213 2 123 Park Place 2224 2 453 Boardwalk 223SET optimizer_switch=@save_optimizer_switch; 224drop table t1,t2,t3; 225create table t1 (ID int not null primary key, TransactionID int not null); 226insert 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); 227create table t2 (ID int not null primary key, GroupID int not null); 228insert 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); 229create table t3 (ID int not null primary key, DateOfAction date not null); 230insert 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'); 231select 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; 232DateOfAction TransactionID 2331999-07-18 486 2341999-07-19 87 2351999-07-19 89 2361999-07-19 92 2371999-07-19 94 2381999-07-27 828 2391999-07-27 832 2401999-07-27 834 2411999-07-27 840 2422000-03-27 490 2432000-03-28 753 244select 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; 245DateOfAction TransactionID 2461999-07-19 87 2471999-07-19 89 2481999-07-19 92 2491999-07-19 94 2501999-07-18 486 2512000-03-27 490 2522000-03-28 753 2531999-07-27 828 2541999-07-27 832 2551999-07-27 834 2561999-07-27 840 257drop table t1,t2,t3; 258CREATE TABLE t1 ( 259member_id int(11) NOT NULL auto_increment, 260inschrijf_datum varchar(20) NOT NULL default '', 261lastchange_datum varchar(20) NOT NULL default '', 262nickname varchar(20) NOT NULL default '', 263password varchar(8) NOT NULL default '', 264voornaam varchar(30) NOT NULL default '', 265tussenvoegsels varchar(10) NOT NULL default '', 266achternaam varchar(50) NOT NULL default '', 267straat varchar(100) NOT NULL default '', 268postcode varchar(10) NOT NULL default '', 269wijk varchar(40) NOT NULL default '', 270plaats varchar(50) NOT NULL default '', 271telefoon varchar(10) NOT NULL default '', 272geboortedatum date NOT NULL default '0000-00-00', 273geslacht varchar(5) NOT NULL default '', 274email varchar(80) NOT NULL default '', 275uin varchar(15) NOT NULL default '', 276homepage varchar(100) NOT NULL default '', 277internet varchar(15) NOT NULL default '', 278scherk varchar(30) NOT NULL default '', 279favo_boek varchar(50) NOT NULL default '', 280favo_tijdschrift varchar(50) NOT NULL default '', 281favo_tv varchar(50) NOT NULL default '', 282favo_eten varchar(50) NOT NULL default '', 283favo_muziek varchar(30) NOT NULL default '', 284info text NOT NULL default '', 285ipnr varchar(30) NOT NULL default '', 286PRIMARY KEY (member_id) 287) ENGINE=MyISAM PACK_KEYS=1; 288insert into t1 (member_id) values (1),(2),(3); 289select member_id, nickname, voornaam FROM t1 290ORDER by lastchange_datum DESC LIMIT 2; 291member_id nickname voornaam 2921 2932 294drop table t1; 295create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); 296insert 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'); 297insert into t1 select * from t1; 298explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; 299id select_type table type possible_keys key key_len ref rows Extra 3001 SIMPLE t1 range a a 22 NULL 3 Using where; Using index 301select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; 302a b c 3031 NULL b 3041 NULL b 305explain select * from t1 where a >= 1 and a < 3 order by a desc; 306id select_type table type possible_keys key key_len ref rows Extra 3071 SIMPLE t1 range a a 4 NULL 22 Using where; Using index 308select * from t1 where a >= 1 and a < 3 order by a desc; 309a b c 3102 3 c 3112 3 c 3122 2 b 3132 2 b 3142 2 a 3152 2 a 3162 1 b 3172 1 b 3182 1 a 3192 1 a 3201 3 b 3211 3 b 3221 1 b 3231 1 b 3241 1 b 3251 1 b 3261 1 NULL 3271 1 NULL 3281 NULL b 3291 NULL b 3301 NULL NULL 3311 NULL NULL 332explain select * from t1 where a = 1 order by a desc, b desc; 333id select_type table type possible_keys key key_len ref rows Extra 3341 SIMPLE t1 ref a a 4 const 12 Using where; Using index 335select * from t1 where a = 1 order by a desc, b desc; 336a b c 3371 3 b 3381 3 b 3391 1 b 3401 1 b 3411 1 b 3421 1 b 3431 1 NULL 3441 1 NULL 3451 NULL b 3461 NULL b 3471 NULL NULL 3481 NULL NULL 349explain select * from t1 where a = 1 and b is null order by a desc, b desc; 350id select_type table type possible_keys key key_len ref rows Extra 3511 SIMPLE t1 ref a a 9 const,const 4 Using where; Using index; Using filesort 352select * from t1 where a = 1 and b is null order by a desc, b desc; 353a b c 3541 NULL NULL 3551 NULL NULL 3561 NULL b 3571 NULL b 358explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc; 359id select_type table type possible_keys key key_len ref rows Extra 3601 SIMPLE t1 range a a 9 NULL 18 Using where; Using index 361explain select * from t1 where a = 2 and b >0 order by a desc,b desc; 362id select_type table type possible_keys key key_len ref rows Extra 3631 SIMPLE t1 range a a 9 NULL 10 Using where; Using index 364explain select * from t1 where a = 2 and b is null order by a desc,b desc; 365id select_type table type possible_keys key key_len ref rows Extra 3661 SIMPLE t1 ref a a 9 const,const 1 Using where; Using index; Using filesort 367explain select * from t1 where a = 2 and (b is null or b > 0) order by a 368desc,b desc; 369id select_type table type possible_keys key key_len ref rows Extra 3701 SIMPLE t1 range a a 9 NULL 11 Using where; Using index 371explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; 372id select_type table type possible_keys key key_len ref rows Extra 3731 SIMPLE t1 range a a 9 NULL 10 Using where; Using index 374explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; 375id select_type table type possible_keys key key_len ref rows Extra 3761 SIMPLE t1 range a a 9 NULL 4 Using where; Using index 377explain select * from t1 where a = 1 order by b desc; 378id select_type table type possible_keys key key_len ref rows Extra 3791 SIMPLE t1 ref a a 4 const 12 Using where; Using index 380explain select * from t1 where a = 2 and b > 0 order by a desc,b desc,b,a; 381id select_type table type possible_keys key key_len ref rows Extra 3821 SIMPLE t1 range a a 9 NULL 10 Using where; Using index 383explain select * from t1 where a = 2 and b < 2 order by a desc,a,b desc,a,b; 384id select_type table type possible_keys key key_len ref rows Extra 3851 SIMPLE t1 range a a 9 NULL 4 Using where; Using index 386select * from t1 where a = 1 order by b desc; 387a b c 3881 3 b 3891 3 b 3901 1 b 3911 1 b 3921 1 b 3931 1 b 3941 1 NULL 3951 1 NULL 3961 NULL b 3971 NULL b 3981 NULL NULL 3991 NULL NULL 400SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 401alter table t1 modify b int not null, modify c varchar(10) not null; 402Warnings: 403Warning 1265 Data truncated for column 'b' at row 1 404Warning 1265 Data truncated for column 'c' at row 1 405Warning 1265 Data truncated for column 'b' at row 2 406Warning 1265 Data truncated for column 'c' at row 3 407Warning 1265 Data truncated for column 'b' at row 12 408Warning 1265 Data truncated for column 'c' at row 12 409Warning 1265 Data truncated for column 'b' at row 13 410Warning 1265 Data truncated for column 'c' at row 14 411explain select * from t1 order by a, b, c; 412id select_type table type possible_keys key key_len ref rows Extra 4131 SIMPLE t1 index NULL a 20 NULL 22 Using index 414select * from t1 order by a, b, c; 415a b c 4161 0 4171 0 4181 0 b 4191 0 b 4201 1 4211 1 4221 1 b 4231 1 b 4241 1 b 4251 1 b 4261 3 b 4271 3 b 4282 1 a 4292 1 a 4302 1 b 4312 1 b 4322 2 a 4332 2 a 4342 2 b 4352 2 b 4362 3 c 4372 3 c 438explain select * from t1 order by a desc, b desc, c desc; 439id select_type table type possible_keys key key_len ref rows Extra 4401 SIMPLE t1 index NULL a 20 NULL 22 Using index 441select * from t1 order by a desc, b desc, c desc; 442a b c 4432 3 c 4442 3 c 4452 2 b 4462 2 b 4472 2 a 4482 2 a 4492 1 b 4502 1 b 4512 1 a 4522 1 a 4531 3 b 4541 3 b 4551 1 b 4561 1 b 4571 1 b 4581 1 b 4591 1 4601 1 4611 0 b 4621 0 b 4631 0 4641 0 465explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; 466id select_type table type possible_keys key key_len ref rows Extra 4671 SIMPLE t1 range a a 20 NULL 5 Using where; Using index 468select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; 469a b c 4701 1 b 4711 1 b 4721 1 b 4731 1 b 474explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc; 475id select_type table type possible_keys key key_len ref rows Extra 4761 SIMPLE t1 range a a 4 NULL 12 Using where; Using index 477select * from t1 where a < 2 and b <= 1 order by a desc, b desc; 478a b c 4791 1 b 4801 1 b 4811 1 b 4821 1 b 4831 1 4841 1 4851 0 b 4861 0 b 4871 0 4881 0 489select count(*) from t1 where a < 5 and b > 0; 490count(*) 49118 492select * from t1 where a < 5 and b > 0 order by a desc,b desc; 493a b c 4942 3 c 4952 3 c 4962 2 b 4972 2 b 4982 2 a 4992 2 a 5002 1 b 5012 1 b 5022 1 a 5032 1 a 5041 3 b 5051 3 b 5061 1 b 5071 1 b 5081 1 b 5091 1 b 5101 1 5111 1 512explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; 513id select_type table type possible_keys key key_len ref rows Extra 5141 SIMPLE t1 range a a 8 NULL 22 Using where; Using index 515select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; 516a b c 5172 1 b 5182 1 b 5192 1 a 5202 1 a 5211 1 b 5221 1 b 5231 1 b 5241 1 b 5251 1 5261 1 5271 0 b 5281 0 b 5291 0 5301 0 531explain select * from t1 where a between 0 and 1 order by a desc, b desc; 532id select_type table type possible_keys key key_len ref rows Extra 5331 SIMPLE t1 range a a 4 NULL 12 Using where; Using index 534select * from t1 where a between 0 and 1 order by a desc, b desc; 535a b c 5361 3 b 5371 3 b 5381 1 b 5391 1 b 5401 1 b 5411 1 b 5421 1 5431 1 5441 0 b 5451 0 b 5461 0 5471 0 548drop table t1; 549CREATE TABLE t1 ( 550gid int(10) unsigned NOT NULL auto_increment, 551cid smallint(5) unsigned NOT NULL default '0', 552PRIMARY KEY (gid), 553KEY component_id (cid) 554) ENGINE=MyISAM; 555INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108); 556ALTER TABLE t1 add skr int(10) not null; 557CREATE TABLE t2 ( 558gid int(10) unsigned NOT NULL default '0', 559uid smallint(5) unsigned NOT NULL default '1', 560sid tinyint(3) unsigned NOT NULL default '1', 561PRIMARY KEY (gid), 562KEY uid (uid), 563KEY status_id (sid) 564) ENGINE=MyISAM; 565INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5); 566CREATE TABLE t3 ( 567uid smallint(6) NOT NULL auto_increment, 568PRIMARY KEY (uid) 569) ENGINE=MyISAM; 570INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250); 571ALTER TABLE t3 add skr int(10) not null; 572select 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; 573gid sid uid 574104620 5 15 575103867 5 27 576103962 5 27 577104619 5 75 578104505 5 117 579103853 5 250 580select 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; 581gid sid uid 582104620 5 15 583103867 5 27 584103962 5 27 585104619 5 75 586104505 5 117 587103853 5 250 588EXPLAIN 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; 589id select_type table type possible_keys key key_len ref rows Extra 5901 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort 5911 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index 5921 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index 593EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr; 594id select_type table type possible_keys key key_len ref rows Extra 5951 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort 5961 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index 597EXPLAIN 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; 598id select_type table type possible_keys key key_len ref rows Extra 5991 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort 6001 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index 6011 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index 602EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid; 603id select_type table type possible_keys key key_len ref rows Extra 6041 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort 6051 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index 606EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; 607id select_type table type possible_keys key key_len ref rows Extra 6081 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 6091 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition 610drop table t1,t2,t3; 611CREATE TABLE t1 ( 612`titre` char(80) NOT NULL default '', 613`numeropost` mediumint(8) unsigned NOT NULL auto_increment, 614`date` datetime NOT NULL default '0000-00-00 00:00:00', 615`auteur` char(35) NOT NULL default '', 616`icone` tinyint(2) unsigned NOT NULL default '0', 617`lastauteur` char(35) NOT NULL default '', 618`nbrep` smallint(6) unsigned NOT NULL default '0', 619`dest` char(35) NOT NULL default '', 620`lu` tinyint(1) unsigned NOT NULL default '0', 621`vue` mediumint(8) unsigned NOT NULL default '0', 622`ludest` tinyint(1) unsigned NOT NULL default '0', 623`ouvert` tinyint(1) unsigned NOT NULL default '1', 624PRIMARY KEY (`numeropost`), 625KEY `date` (`date`), 626KEY `dest` (`dest`,`ludest`), 627KEY `auteur` (`auteur`,`lu`), 628KEY `auteur_2` (`auteur`,`date`), 629KEY `dest_2` (`dest`,`date`) 630) CHECKSUM=1; 631CREATE TABLE t2 ( 632`numeropost` mediumint(8) unsigned NOT NULL default '0', 633`pseudo` char(35) NOT NULL default '', 634PRIMARY KEY (`numeropost`,`pseudo`), 635KEY `pseudo` (`pseudo`) 636); 637INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug'); 638INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug'); 639SELECT 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; 640titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest 641test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug 642SELECT 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; 643titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest 644test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug 645SELECT 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; 646titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest 647test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug 648SELECT 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; 649titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest 650test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug 651drop table t1,t2; 652CREATE TABLE t1 (a int, b int); 653INSERT INTO t1 VALUES (1, 2); 654INSERT INTO t1 VALUES (3, 4); 655INSERT INTO t1 VALUES (5, NULL); 656SELECT * FROM t1 ORDER BY b; 657a b 6585 NULL 6591 2 6603 4 661SELECT * FROM t1 ORDER BY b DESC; 662a b 6633 4 6641 2 6655 NULL 666SELECT * FROM t1 ORDER BY (a + b); 667a b 6685 NULL 6691 2 6703 4 671SELECT * FROM t1 ORDER BY (a + b) DESC; 672a b 6733 4 6741 2 6755 NULL 676DROP TABLE t1; 677create table t1(id int not null auto_increment primary key, t char(12)); 678explain select id,t from t1 order by id; 679id select_type table type possible_keys key key_len ref rows Extra 6801 SIMPLE t1 ALL NULL NULL NULL NULL 1000 Using filesort 681explain select id,t from t1 force index (primary) order by id; 682id select_type table type possible_keys key key_len ref rows Extra 6831 SIMPLE t1 index NULL PRIMARY 4 NULL 1000 684drop table t1; 685CREATE TABLE t1 ( 686FieldKey varchar(36) NOT NULL default '', 687LongVal bigint(20) default NULL, 688StringVal mediumtext, 689KEY FieldKey (FieldKey), 690KEY LongField (FieldKey,LongVal), 691KEY StringField (FieldKey,StringVal(32)) 692); 693INSERT 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'); 694EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; 695id select_type table type possible_keys key key_len ref rows Extra 6961 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using where 697SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; 698FieldKey LongVal StringVal 6991 0 2 7001 1 3 7011 2 1 702DS-MRR: use two IGNORE INDEX queries, otherwise we get cost races, because 703DS-MRR: records_in_range/read_time return the same numbers for all three indexes 704EXPLAIN SELECT * FROM t1 IGNORE INDEX (LongField, StringField) WHERE FieldKey > '2' ORDER BY LongVal; 705id select_type table type possible_keys key key_len ref rows Extra 7061 SIMPLE t1 range FieldKey FieldKey 38 NULL 3 Using index condition; Using filesort 707EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; 708id select_type table type possible_keys key key_len ref rows Extra 7091 SIMPLE t1 range StringField StringField 38 NULL 3 Using where; Using filesort 710SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; 711FieldKey LongVal StringVal 7123 1 2 7133 2 1 7143 3 3 715EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; 716id select_type table type possible_keys key key_len ref rows Extra 7171 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 3 Using where 718SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; 719FieldKey LongVal StringVal 7203 1 2 7213 2 1 7223 3 3 723DROP TABLE t1; 724CREATE TABLE t1 (a INT, b INT); 725SET @id=0; 726UPDATE t1 SET a=0 ORDER BY (a=@id), b; 727DROP TABLE t1; 728CREATE TABLE t1 ( id smallint(6) unsigned NOT NULL default '0', menu tinyint(4) NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM; 729INSERT INTO t1 VALUES (11384, 2),(11392, 2); 730SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ; 731id 73211392 733drop table t1; 734create table t1(a int, b int, index(b)); 735insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); 736insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12); 737explain select * from t1 where b=1 or b is null order by a; 738id select_type table type possible_keys key key_len ref rows Extra 7391 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort 740select * from t1 where b=1 or b is null order by a; 741a b 7421 1 7432 1 7443 NULL 7454 NULL 746explain select * from t1 where b=2 or b is null order by a; 747id select_type table type possible_keys key key_len ref rows Extra 7481 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort 749select * from t1 where b=2 or b is null order by a; 750a b 7513 NULL 7524 NULL 7535 2 7546 2 755drop table t1; 756create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null, 757key(a,b,d), key(c,b,a)); 758create table t2 like t1; 759insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3); 760insert into t2 select null, b, c, d from t1; 761insert into t1 select null, b, c, d from t2; 762insert into t2 select null, b, c, d from t1; 763insert into t1 select null, b, c, d from t2; 764insert into t2 select null, b, c, d from t1; 765insert into t1 select null, b, c, d from t2; 766insert into t2 select null, b, c, d from t1; 767insert into t1 select null, b, c, d from t2; 768insert into t2 select null, b, c, d from t1; 769insert into t1 select null, b, c, d from t2; 770optimize table t1; 771Table Op Msg_type Msg_text 772test.t1 optimize status OK 773set @row=10; 774insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10; 775select * from t1 where a=1 and b in (1) order by c, b, a; 776a b c d 7771 1 2 0 7781 1 12 -1 7791 1 52 -5 7801 1 92 -9 781select * from t1 where a=1 and b in (1); 782a b c d 7831 1 92 -9 7841 1 52 -5 7851 1 12 -1 7861 1 2 0 787drop table t1, t2; 788create table t1 (col1 int, col int); 789create table t2 (col2 int, col int); 790insert into t1 values (1,1),(2,2),(3,3); 791insert into t2 values (1,3),(2,2),(3,1); 792select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2) 793order by col; 794col1 col t2_col 7951 1 3 7962 2 2 7973 3 1 798select col1 as col, col from t1 order by col; 799ERROR 23000: Column 'col' in order clause is ambiguous 800select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 801order by col; 802ERROR 23000: Column 'col' in order clause is ambiguous 803select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 804order by col; 805ERROR 23000: Column 'col' in order clause is ambiguous 806select col1 from t1, t2 where t1.col1=t2.col2 order by col; 807ERROR 23000: Column 'col' in order clause is ambiguous 808select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2 809order by col; 810ERROR 23000: Column 'col' in order clause is ambiguous 811select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2 812order by col; 813t1_col col 8143 1 8152 2 8161 3 817select col2 as c, col as c from t2 order by col; 818c c 8193 1 8202 2 8211 3 822select col2 as col, col as col2 from t2 order by col; 823col col2 8241 3 8252 2 8263 1 827select t2.col2, t2.col, t2.col from t2 order by col; 828col2 col col 8293 1 1 8302 2 2 8311 3 3 832select t2.col2 as col from t2 order by t2.col; 833col 8343 8352 8361 837select t2.col2 as col, t2.col from t2 order by t2.col; 838col col 8393 1 8402 2 8411 3 842select t2.col2, t2.col, t2.col from t2 order by t2.col; 843col2 col col 8443 1 1 8452 2 2 8461 3 3 847drop table t1, t2; 848create table t1 (a char(70)); 849insert into t1 set a = repeat('x', 20); 850insert into t1 set a = concat(repeat('x', 63), 'z'); 851insert into t1 set a = concat(repeat('x', 63), 'ab'); 852insert into t1 set a = concat(repeat('x', 63), 'aa'); 853set max_sort_length=20; 854Warnings: 855Warning 1292 Truncated incorrect max_sort_length value: '20' 856select a from t1 order by a; 857a 858xxxxxxxxxxxxxxxxxxxx 859xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxab 860xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxaa 861xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz 862drop table t1; 863create table t1 ( 864`sid` decimal(8,0) default null, 865`wnid` varchar(11) not null default '', 866key `wnid14` (`wnid`(4)), 867key `wnid` (`wnid`) 868) engine=myisam default charset=latin1; 869insert into t1 (`sid`, `wnid`) values 870('10100','01019000000'),('37986','01019000000'),('37987','01019010000'), 871('37994','01019090000'),('475','02070000000'),('25253','02071100000'), 872('25255','02071100000'),('25256','02071110000'),('25258','02071130000'), 873('25259','02071190000'),('25260','02071200000'),('25261','02071210000'), 874('25262','02071290000'),('25263','02071300000'),('25264','02071310000'), 875('25265','02071310000'),('25266','02071320000'),('25267','02071320000'), 876('25269','02071330000'),('25270','02071340000'),('25271','02071350000'), 877('25272','02071360000'),('25273','02071370000'),('25281','02071391000'), 878('25282','02071391000'),('25283','02071399000'),('25284','02071400000'), 879('25285','02071410000'),('25286','02071410000'),('25287','02071420000'), 880('25288','02071420000'),('25291','02071430000'),('25290','02071440000'), 881('25292','02071450000'),('25293','02071460000'),('25294','02071470000'), 882('25295','02071491000'),('25296','02071491000'),('25297','02071499000'); 883explain select * from t1 where wnid like '0101%' order by wnid; 884id select_type table type possible_keys key key_len ref rows Extra 8851 SIMPLE t1 range wnid14,wnid wnid 13 NULL 4 Using where 886select * from t1 where wnid like '0101%' order by wnid; 887sid wnid 88810100 01019000000 88937986 01019000000 89037987 01019010000 89137994 01019090000 892drop table t1; 893CREATE TABLE t1 (a int); 894INSERT INTO t1 VALUES (2), (1), (1), (2), (1); 895SELECT a FROM t1 ORDER BY a; 896a 8971 8981 8991 9002 9012 902(SELECT a FROM t1) ORDER BY a; 903a 9041 9051 9061 9072 9082 909DROP TABLE t1; 910CREATE TABLE t1 (a int, b int); 911INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10); 912(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a; 913b a 91410 1 91510 2 91620 1 91720 2 91830 1 91930 2 920(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC; 921b 92210 92310 92420 92520 92630 92730 928(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b; 929b a 93010 1 93120 1 93230 1 93310 2 93420 2 93530 2 936(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b; 937b a 93810 1 93920 1 94010 2 941DROP TABLE t1; 942CREATE TABLE t1 (a INT); 943INSERT INTO t1 VALUES (1),(2); 944SELECT a + 1 AS num FROM t1 ORDER BY 30 - num; 945num 9463 9472 948SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str); 949str 950test1 951test2 952SELECT a + 1 AS num FROM t1 GROUP BY 30 - num; 953num 9543 9552 956SELECT a + 1 AS num FROM t1 HAVING 30 - num; 957num 9582 9593 960SELECT a + 1 AS num, num + 1 FROM t1; 961ERROR 42S22: Unknown column 'num' in 'field list' 962SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; 963num (select num + 2 FROM t1 LIMIT 1) 9642 4 9653 5 966SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; 967ERROR 42S22: Unknown column 'num' in 'on clause' 968DROP TABLE t1; 969CREATE TABLE bug25126 ( 970val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY 971); 972UPDATE bug25126 SET MissingCol = MissingCol; 973ERROR 42S22: Unknown column 'MissingCol' in 'field list' 974UPDATE bug25126 SET val = val ORDER BY MissingCol; 975ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 976UPDATE bug25126 SET val = val ORDER BY val; 977UPDATE bug25126 SET val = 1 ORDER BY val; 978UPDATE bug25126 SET val = 1 ORDER BY MissingCol; 979ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 980UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol; 981ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 982UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol; 983ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 984UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol; 985ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 986UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol; 987ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 988UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol; 989ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 990UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol; 991ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 992DROP TABLE bug25126; 993CREATE TABLE t1 (a int); 994SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1; 995val val1 996SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; 997ERROR 23000: Column 'val' in order clause is ambiguous 998SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; 999ERROR 23000: Column 'val' in order clause is ambiguous 1000DROP TABLE t1; 1001CREATE TABLE t1 (a int); 1002INSERT INTO t1 VALUES (3), (2), (4), (1); 1003SELECT a, IF(a IN (2,3), a, a+10) FROM t1 1004ORDER BY IF(a IN (2,3), a, a+10); 1005a IF(a IN (2,3), a, a+10) 10062 2 10073 3 10081 11 10094 14 1010SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1 1011ORDER BY IF(a NOT IN (2,3), a, a+10); 1012a IF(a NOT IN (2,3), a, a+10) 10131 1 10144 4 10152 12 10163 13 1017SELECT a, IF(a IN (2,3), a, a+10) FROM t1 1018ORDER BY IF(a NOT IN (2,3), a, a+10); 1019a IF(a IN (2,3), a, a+10) 10201 11 10214 14 10222 2 10233 3 1024SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 1025ORDER BY IF(a BETWEEN 2 AND 3, a, a+10); 1026a IF(a BETWEEN 2 AND 3, a, a+10) 10272 2 10283 3 10291 11 10304 14 1031SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1 1032ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); 1033a IF(a NOT BETWEEN 2 AND 3, a, a+10) 10341 1 10354 4 10362 12 10373 13 1038SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 1039ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); 1040a IF(a BETWEEN 2 AND 3, a, a+10) 10411 11 10424 14 10432 2 10443 3 1045SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 1046FROM t1 GROUP BY x1, x2; 1047x1 x2 1048 3 1049 4 10501 10512 1052SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 1053FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, ''); 1054x1 x2 1055 3 1056 4 10571 10582 1059SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2); 1060a a IN (1,2) 10613 0 10624 0 10632 1 10641 1 1065SELECT a FROM t1 ORDER BY a IN (1,2); 1066a 10673 10684 10692 10701 1071SELECT a+10 FROM t1 ORDER BY a IN (1,2); 1072a+10 107313 107414 107512 107611 1077SELECT a, IF(a IN (1,2), a, a+10) FROM t1 1078ORDER BY IF(a IN (3,4), a, a+10); 1079a IF(a IN (1,2), a, a+10) 10803 13 10814 14 10821 1 10832 2 1084DROP TABLE t1; 1085create table t1 (a int not null, b int not null, c int not null); 1086insert t1 values (1,1,1),(1,1,2),(1,2,1); 1087select a, b from t1 group by a, b order by sum(c); 1088a b 10891 2 10901 1 1091drop table t1; 1092CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); 1093INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 1094explain SELECT t1.b as a, t2.b as c FROM 1095t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 1096ORDER BY c; 1097id select_type table type possible_keys key key_len ref rows Extra 10981 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort 10991 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where 1100SELECT t2.b as c FROM 1101t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 1102ORDER BY c; 1103c 1104NULL 1105NULL 11062 1107explain SELECT t1.b as a, t2.b as c FROM 1108t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 1109ORDER BY c; 1110id select_type table type possible_keys key key_len ref rows Extra 11111 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 11121 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 1113CREATE TABLE t2 LIKE t1; 1114INSERT INTO t2 SELECT * from t1; 1115CREATE TABLE t3 LIKE t1; 1116INSERT INTO t3 SELECT * from t1; 1117CREATE TABLE t4 LIKE t1; 1118INSERT INTO t4 SELECT * from t1; 1119INSERT INTO t1 values (0,0),(4,4); 1120SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a) 1121ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b; 1122b 1123NULL 1124NULL 11251 11262 11273 1128DROP TABLE t1,t2,t3,t4; 1129create table t1 (a int, b int, c int); 1130insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9); 1131select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc; 1132a ratio 11331 0.5000 113419 1.3333 11359 2.6667 1136drop table t1; 1137CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME); 1138INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10); 1139UPDATE t1 SET b = SEC_TO_TIME(a); 1140SELECT a, b FROM t1 ORDER BY b DESC; 1141a b 11421000000 277:46:40 1143100000 27:46:40 114410000 02:46:40 11451000 00:16:40 1146100 00:01:40 114710 00:00:10 11480 00:00:00 1149SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC; 1150a b 11511000000 277:46:40 1152100000 27:46:40 115310000 02:46:40 11541000 00:16:40 1155100 00:01:40 115610 00:00:10 11570 00:00:00 1158DROP TABLE t1; 1159CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b)); 1160INSERT INTO t1 VALUES (1,1),(2,2); 1161CREATE TABLE t2 (a INT, b INT, KEY a (a,b)); 1162INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); 1163EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; 1164id select_type table type possible_keys key key_len ref rows Extra 11651 SIMPLE t1 const PRIMARY,b b 5 const 1 11661 SIMPLE t2 ref a a 5 const 2 Using where; Using index 1167DROP TABLE t1,t2; 1168CREATE TABLE t1( 1169id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3)); 1170INSERT INTO t1 (c2,c3) VALUES 1171(31,34),(35,38),(34,31),(32,35),(31,39), 1172(11,14),(15,18),(14,11),(12,15),(11,19); 1173INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1174INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1175INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1176INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1177INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1178INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1179INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1180INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1181INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1182INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1183INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1184INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1185UPDATE t1 SET c2=20 WHERE id%100 = 0; 1186SELECT COUNT(*) FROM t1; 1187COUNT(*) 118840960 1189CREATE TABLE t2 LIKE t1; 1190INSERT INTO t2 SELECT * FROM t1 ORDER BY id; 1191EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; 1192id select_type table type possible_keys key key_len ref rows Extra 11931 SIMPLE t2 index k2 k3 5 NULL 111 Using where 1194EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; 1195id select_type table type possible_keys key key_len ref rows Extra 11961 SIMPLE t2 index k2 k3 5 NULL 22318 Using where 1197EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; 1198id select_type table type possible_keys key key_len ref rows Extra 11991 SIMPLE t2 index k2 k3 5 NULL 73 Using where 1200EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; 1201id select_type table type possible_keys key key_len ref rows Extra 12021 SIMPLE t2 range k2 k2 5 NULL 386 Using index condition; Using filesort 1203SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; 1204id c3 12056 14 120616 14 120726 14 120836 14 120946 14 121056 14 121166 14 121276 14 121386 14 121496 14 1215106 14 1216116 14 1217126 14 1218136 14 1219146 14 1220156 14 1221166 14 1222176 14 1223186 14 1224196 14 1225DROP TABLE t1,t2; 1226CREATE TABLE t1 ( 1227a INT, 1228b INT, 1229PRIMARY KEY (a), 1230KEY ab(a, b) 1231); 1232INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); 1233INSERT INTO t1 SELECT a + 4, b + 4 FROM t1; 1234INSERT INTO t1 SELECT a + 8, b + 8 FROM t1; 1235INSERT INTO t1 SELECT a +16, b +16 FROM t1; 1236INSERT INTO t1 SELECT a +32, b +32 FROM t1; 1237INSERT INTO t1 SELECT a +64, b +64 FROM t1; 1238EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; 1239id select_type table type possible_keys key key_len ref rows Extra 12401 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index 1241SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; 1242a 12431 12442 12453 12464 12475 12486 12497 12508 12519 125210 125311 125412 125513 125614 125715 125816 125917 126018 126119 126220 126321 126422 126523 126624 126725 126826 126927 127028 127129 127230 127331 127432 127533 127634 127735 127836 127937 128038 128139 128240 128341 128442 128543 128644 128745 128846 128947 129048 129149 129250 129351 129452 129553 129654 129755 129856 129957 130058 130159 130260 130361 130462 130563 130664 130765 130866 130967 131068 131169 131270 131371 131472 131573 131674 131775 131876 131977 132078 132179 132280 132381 132482 132583 132684 132785 132886 132987 133088 133189 133290 133391 133492 133593 133694 133795 133896 133997 134098 134199 1342100 1343101 1344102 1345103 1346104 1347105 1348106 1349107 1350108 1351109 1352110 1353111 1354112 1355113 1356114 1357115 1358116 1359117 1360118 1361119 1362120 1363121 1364122 1365123 1366124 1367125 1368126 1369127 1370128 1371SELECT @tmp_tables_after = @tmp_tables_before ; 1372@tmp_tables_after = @tmp_tables_before 13731 1374EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; 1375id select_type table type possible_keys key key_len ref rows Extra 13761 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index 1377SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; 1378a 13791 13802 13813 13824 13835 13846 13857 13868 13879 138810 138911 139012 139113 139214 139315 139416 139517 139618 139719 139820 139921 140022 140123 140224 140325 140426 140527 140628 140729 140830 140931 141032 141133 141234 141335 141436 141537 141638 141739 141840 141941 142042 142143 142244 142345 142446 142547 142648 142749 142850 142951 143052 143153 143254 143355 143456 143557 143658 143759 143860 143961 144062 144163 144264 144365 144466 144567 144668 144769 144870 144971 145072 145173 145274 145375 145476 145577 145678 145779 145880 145981 146082 146183 146284 146385 146486 146587 146688 146789 146890 146991 147092 147193 147294 147395 147496 147597 147698 147799 1478100 1479101 1480102 1481103 1482104 1483105 1484106 1485107 1486108 1487109 1488110 1489111 1490112 1491113 1492114 1493115 1494116 1495117 1496118 1497119 1498120 1499121 1500122 1501123 1502124 1503125 1504126 1505127 1506128 1507SELECT @tmp_tables_after = @tmp_tables_before; 1508@tmp_tables_after = @tmp_tables_before 15091 1510DROP TABLE t1; 1511# 1512# Bug#31590: Wrong error message on sort buffer being too small. 1513# 1514create table t1(a int, b tinytext); 1515insert into t1 values (1,2),(3,2); 1516set session sort_buffer_size= 1000; 1517Warnings: 1518Warning 1292 Truncated incorrect sort_buffer_size value: '1000' 1519set session max_sort_length= 2180; 1520CALL mtr.add_suppression("Out of sort memory"); 1521select * from t1 order by b; 1522ERROR HY001: Out of sort memory, consider increasing server sort buffer size 1523drop table t1; 1524set session sort_buffer_size= 30000; 1525# 1526# Bug #39844: Query Crash Mysql Server 5.0.67 1527# 1528CREATE TABLE t1 (a INT PRIMARY KEY); 1529CREATE TABLE t2 (a INT PRIMARY KEY, b INT); 1530CREATE TABLE t3 (c INT); 1531INSERT INTO t1 (a) VALUES (1), (2); 1532INSERT INTO t2 (a,b) VALUES (1,2), (2,3); 1533INSERT INTO t3 (c) VALUES (1), (2); 1534SELECT 1535(SELECT t1.a FROM t1, t2 WHERE t1.a = t2.b AND t2.a = t3.c ORDER BY t1.a) 1536FROM t3; 1537(SELECT t1.a FROM t1, t2 WHERE t1.a = t2.b AND t2.a = t3.c ORDER BY t1.a) 15382 1539NULL 1540DROP TABLE t1, t2, t3; 1541# 1542# Bug #42760: Select doesn't return desired results when we have null 1543# values 1544# 1545CREATE TABLE t1 ( 1546a INT, 1547c INT, 1548UNIQUE KEY a_c (a,c), 1549KEY (a)); 1550INSERT INTO t1 VALUES (1, 10), (2, NULL); 1551# Must use ref-or-null on the a_c index 1552EXPLAIN 1553SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 1554id select_type table type possible_keys key key_len ref rows Extra 15551 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 2 Using where; Using index; Using filesort 1556# Must return 1 row 1557SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 1558col 15591 1560# Must use ref-or-null on the a_c index 1561EXPLAIN 1562SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 1563id select_type table type possible_keys key key_len ref rows Extra 1564x x x ref_or_null a_c,a x x x x x 1565# Must return 1 row 1566SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 1567col 15681 1569DROP TABLE t1; 1570End of 5.0 tests 1571CREATE TABLE t2 (a varchar(32), b int(11), c float, d double, 1572UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c)); 1573CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b)); 1574CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b)); 1575INSERT INTO t3 SELECT * FROM t1; 1576EXPLAIN 1577SELECT d FROM t1, t2 1578WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1579ORDER BY t2.c LIMIT 1; 1580id select_type table type possible_keys key key_len ref rows Extra 15811 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort 15821 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition 1583SELECT d FROM t1, t2 1584WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1585ORDER BY t2.c LIMIT 1; 1586d 158752.5 1588EXPLAIN 1589SELECT d FROM t3 AS t1, t2 AS t2 1590WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1591ORDER BY t2.c LIMIT 1; 1592id select_type table type possible_keys key key_len ref rows Extra 15931 SIMPLE t2 range a,b,c c 5 NULL 420 Using where 15941 SIMPLE t1 eq_ref a a 39 test.t2.a,const 1 Using where; Using index 1595SELECT d FROM t3 AS t1, t2 AS t2 1596WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1597ORDER BY t2.c LIMIT 1; 1598d 159952.5 1600SELECT t1.*,t2.* FROM t1, t2 1601WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1602ORDER BY t2.c LIMIT 5; 1603a b a b c d 1604ppfcz1 DE ppfcz1 14 6 52.5 1605ppfcz1 DE ppfcz1 14 7 55.5 1606ppfcz1 DE ppfcz1 14 8 57.5 1607ppfcz1 DE ppfcz1 14 9 59.5 1608ppfcz1 DE ppfcz1 14 10 61.5 1609SELECT t1.*, t2.* FROM t3 AS t1, t2 AS t2 1610WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1611ORDER BY t2.c LIMIT 5; 1612a b a b c d 1613ppfcz1 DE ppfcz1 14 6 52.5 1614ppfcz1 DE ppfcz1 14 7 55.5 1615ppfcz1 DE ppfcz1 14 8 57.5 1616ppfcz1 DE ppfcz1 14 9 59.5 1617ppfcz1 DE ppfcz1 14 10 61.5 1618DROP TABLE t1,t2,t3; 1619# 1620# WL#1393 - Optimizing filesort with small limit 1621# 1622CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200)); 1623INSERT INTO t1(f1, f2) VALUES 1624(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), 1625(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), 1626(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), 1627(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), 1628(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), 1629(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), 1630(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), 1631(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), 1632(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), 1633(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), 1634(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), 1635(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), 1636(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), 1637(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), 1638(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), 1639(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), 1640(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), 1641(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), 1642(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), 1643(96,"96"),(97,"97"),(98,"98"),(99,"99"); 1644SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100; 1645f0 f1 f2 16461 0 0 16472 1 1 16483 2 2 16494 3 3 16505 4 4 16516 5 5 16527 6 6 16538 7 7 16549 8 8 165510 9 9 165611 10 10 165712 11 11 165813 12 12 165914 13 13 166015 14 14 166116 15 15 166217 16 16 166318 17 17 166419 18 18 166520 19 19 166621 20 20 166722 21 21 166823 22 22 166924 23 23 167025 24 24 167126 25 25 167227 26 26 167328 27 27 167429 28 28 167530 29 29 167631 30 30 167732 31 31 167833 32 32 167934 33 33 168035 34 34 168136 35 35 168237 36 36 168338 37 37 168439 38 38 168540 39 39 168641 40 40 168742 41 41 168843 42 42 168944 43 43 169045 44 44 169146 45 45 169247 46 46 169348 47 47 169449 48 48 169550 49 49 169651 50 50 169752 51 51 169853 52 52 169954 53 53 170055 54 54 170156 55 55 170257 56 56 170358 57 57 170459 58 58 170560 59 59 170661 60 60 170762 61 61 170863 62 62 170964 63 63 171065 64 64 171166 65 65 171267 66 66 171368 67 67 171469 68 68 171570 69 69 171671 70 70 171772 71 71 171873 72 72 171974 73 73 172075 74 74 172176 75 75 172277 76 76 172378 77 77 172479 78 78 172580 79 79 172681 80 80 172782 81 81 172883 82 82 172984 83 83 173085 84 84 173186 85 85 173287 86 86 173388 87 87 173489 88 88 173590 89 89 173691 90 90 173792 91 91 173893 92 92 173994 93 93 174095 94 94 174196 95 95 174297 96 96 174398 97 97 174499 98 98 1745100 99 99 1746SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; 1747f0 f1 f2 17481 0 0 17492 1 1 17503 2 2 17514 3 3 17525 4 4 17536 5 5 17547 6 6 17558 7 7 17569 8 8 175710 9 9 175811 10 10 175912 11 11 176013 12 12 176114 13 13 176215 14 14 176316 15 15 176417 16 16 176518 17 17 176619 18 18 176720 19 19 176821 20 20 176922 21 21 177023 22 22 177124 23 23 177225 24 24 177326 25 25 177427 26 26 177528 27 27 177629 28 28 177730 29 29 1778SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; 1779f0 f1 f2 1780SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; 1781f0 f1 f2 1782100 99 99 178399 98 98 178498 97 97 178597 96 96 178696 95 95 178795 94 94 178894 93 93 178993 92 92 179092 91 91 179191 90 90 179210 9 9 179390 89 89 179489 88 88 179588 87 87 179687 86 86 179786 85 85 179885 84 84 179984 83 83 180083 82 82 180182 81 81 180281 80 80 18039 8 8 180480 79 79 180579 78 78 180678 77 77 180777 76 76 180876 75 75 180975 74 74 181074 73 73 181173 72 72 1812SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; 1813f0 f1 f2 1814SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; 1815f0 f1 f2 181612 11 11 181713 12 12 181814 13 13 181915 14 14 182016 15 15 182117 16 16 182218 17 17 182319 18 18 182420 19 19 182521 20 20 182622 21 21 182723 22 22 182824 23 23 182925 24 24 183026 25 25 183127 26 26 183228 27 27 183329 28 28 183430 29 29 183531 30 30 1836SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; 1837f0 f1 f2 1838SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; 1839f0 f1 f2 184022 21 21 184123 22 22 184224 23 23 184325 24 24 184426 25 25 184527 26 26 184628 27 27 184729 28 28 184830 29 29 184931 30 30 1850SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; 1851f0 f1 f2 1852set sort_buffer_size= 32768; 1853CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)); 1854INSERT INTO tmp SELECT f1, f2 FROM t1; 1855INSERT INTO t1(f1, f2) SELECT * FROM tmp; 1856INSERT INTO tmp SELECT f1, f2 FROM t1; 1857INSERT INTO t1(f1, f2) SELECT * FROM tmp; 1858SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; 1859f0 f1 f2 18601 0 0 1861101 0 0 1862201 0 0 1863301 0 0 1864401 0 0 18652 1 1 1866102 1 1 1867202 1 1 1868302 1 1 1869402 1 1 18703 2 2 1871103 2 2 1872203 2 2 1873303 2 2 1874403 2 2 18754 3 3 1876104 3 3 1877204 3 3 1878304 3 3 1879404 3 3 18805 4 4 1881105 4 4 1882205 4 4 1883305 4 4 1884405 4 4 18856 5 5 1886106 5 5 1887206 5 5 1888306 5 5 1889406 5 5 1890SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; 1891f0 f1 f2 1892SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; 1893f0 f1 f2 1894100 99 99 1895200 99 99 1896300 99 99 1897400 99 99 1898500 99 99 189999 98 98 1900199 98 98 1901299 98 98 1902399 98 98 1903499 98 98 190498 97 97 1905198 97 97 1906298 97 97 1907398 97 97 1908498 97 97 190997 96 96 1910197 96 96 1911297 96 96 1912397 96 96 1913497 96 96 191496 95 95 1915196 95 95 1916296 95 95 1917396 95 95 1918496 95 95 191995 94 94 1920195 94 94 1921295 94 94 1922395 94 94 1923495 94 94 1924SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; 1925f0 f1 f2 1926SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; 1927f0 f1 f2 192812 11 11 1929112 11 11 1930212 11 11 1931312 11 11 1932412 11 11 193313 12 12 1934113 12 12 1935213 12 12 1936313 12 12 1937413 12 12 193814 13 13 1939114 13 13 1940214 13 13 1941314 13 13 1942414 13 13 194315 14 14 1944115 14 14 1945215 14 14 1946315 14 14 1947415 14 14 1948SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; 1949f0 f1 f2 1950SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; 1951f0 f1 f2 195214 13 13 1953114 13 13 1954214 13 13 1955314 13 13 1956414 13 13 195715 14 14 1958115 14 14 1959215 14 14 1960315 14 14 1961415 14 14 1962SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; 1963f0 f1 f2 1964set sort_buffer_size= 32768; 1965SELECT SQL_CALC_FOUND_ROWS * FROM t1 1966ORDER BY f1, f0 LIMIT 30; 1967f0 f1 f2 19681 0 0 1969101 0 0 1970201 0 0 1971301 0 0 1972401 0 0 19732 1 1 1974102 1 1 1975202 1 1 1976302 1 1 1977402 1 1 19783 2 2 1979103 2 2 1980203 2 2 1981303 2 2 1982403 2 2 19834 3 3 1984104 3 3 1985204 3 3 1986304 3 3 1987404 3 3 19885 4 4 1989105 4 4 1990205 4 4 1991305 4 4 1992405 4 4 19936 5 5 1994106 5 5 1995206 5 5 1996306 5 5 1997406 5 5 1998SELECT FOUND_ROWS(); 1999FOUND_ROWS() 2000500 2001SELECT SQL_CALC_FOUND_ROWS * FROM t1 2002ORDER BY f1, f0 LIMIT 0; 2003f0 f1 f2 2004SELECT FOUND_ROWS(); 2005FOUND_ROWS() 2006500 2007SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 2008ORDER BY f2, f0 LIMIT 20; 2009f0 f1 f2 201012 11 11 2011112 11 11 2012212 11 11 2013312 11 11 2014412 11 11 201513 12 12 2016113 12 12 2017213 12 12 2018313 12 12 2019413 12 12 202014 13 13 2021114 13 13 2022214 13 13 2023314 13 13 2024414 13 13 202515 14 14 2026115 14 14 2027215 14 14 2028315 14 14 2029415 14 14 2030SELECT FOUND_ROWS(); 2031FOUND_ROWS() 2032445 2033SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 2034ORDER BY f2, f0 LIMIT 0; 2035f0 f1 f2 2036SELECT FOUND_ROWS(); 2037FOUND_ROWS() 2038445 2039SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 2040ORDER BY f2, f0 LIMIT 10 OFFSET 10; 2041f0 f1 f2 204214 13 13 2043114 13 13 2044214 13 13 2045314 13 13 2046414 13 13 204715 14 14 2048115 14 14 2049215 14 14 2050315 14 14 2051415 14 14 2052SELECT FOUND_ROWS(); 2053FOUND_ROWS() 2054445 2055SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 2056ORDER BY f2, f0 LIMIT 0 OFFSET 10; 2057f0 f1 f2 2058SELECT FOUND_ROWS(); 2059FOUND_ROWS() 2060445 2061set sort_buffer_size= 327680; 2062SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 2063ORDER BY tmp.f1, f0 LIMIT 30; 2064f0 f1 f2 f1 f2 20651 0 0 0 0 20661 0 0 0 0 20671 0 0 0 0 2068101 0 0 0 0 2069101 0 0 0 0 2070101 0 0 0 0 2071201 0 0 0 0 2072201 0 0 0 0 2073201 0 0 0 0 2074301 0 0 0 0 2075301 0 0 0 0 2076301 0 0 0 0 2077401 0 0 0 0 2078401 0 0 0 0 2079401 0 0 0 0 20802 1 1 1 1 20812 1 1 1 1 20822 1 1 1 1 2083102 1 1 1 1 2084102 1 1 1 1 2085102 1 1 1 1 2086202 1 1 1 1 2087202 1 1 1 1 2088202 1 1 1 1 2089302 1 1 1 1 2090302 1 1 1 1 2091302 1 1 1 1 2092402 1 1 1 1 2093402 1 1 1 1 2094402 1 1 1 1 2095SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 2096ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; 2097f0 f1 f2 f1 f2 20983 2 2 2 2 20993 2 2 2 2 21003 2 2 2 2 2101103 2 2 2 2 2102103 2 2 2 2 2103103 2 2 2 2 2104203 2 2 2 2 2105203 2 2 2 2 2106203 2 2 2 2 2107303 2 2 2 2 2108303 2 2 2 2 2109303 2 2 2 2 2110403 2 2 2 2 2111403 2 2 2 2 2112403 2 2 2 2 21134 3 3 3 3 21144 3 3 3 3 21154 3 3 3 3 2116104 3 3 3 3 2117104 3 3 3 3 2118104 3 3 3 3 2119204 3 3 3 3 2120204 3 3 3 3 2121204 3 3 3 3 2122304 3 3 3 3 2123304 3 3 3 3 2124304 3 3 3 3 2125404 3 3 3 3 2126404 3 3 3 3 2127404 3 3 3 3 2128SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 2129ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; 2130f0 f1 f2 f1 f2 21313 2 2 2 2 21323 2 2 2 2 21333 2 2 2 2 2134103 2 2 2 2 2135103 2 2 2 2 2136103 2 2 2 2 2137203 2 2 2 2 2138203 2 2 2 2 2139203 2 2 2 2 2140303 2 2 2 2 2141303 2 2 2 2 2142303 2 2 2 2 2143403 2 2 2 2 2144403 2 2 2 2 2145403 2 2 2 2 21464 3 3 3 3 21474 3 3 3 3 21484 3 3 3 3 2149104 3 3 3 3 2150104 3 3 3 3 2151104 3 3 3 3 2152204 3 3 3 3 2153204 3 3 3 3 2154204 3 3 3 3 2155304 3 3 3 3 2156304 3 3 3 3 2157304 3 3 3 3 2158404 3 3 3 3 2159404 3 3 3 3 2160404 3 3 3 3 2161SELECT FOUND_ROWS(); 2162FOUND_ROWS() 21631500 2164SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 2165WHERE t1.f2>20 2166ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; 2167f0 f1 f2 f1 f2 216824 23 23 23 23 216924 23 23 23 23 217024 23 23 23 23 2171124 23 23 23 23 2172124 23 23 23 23 2173124 23 23 23 23 2174224 23 23 23 23 2175224 23 23 23 23 2176224 23 23 23 23 2177324 23 23 23 23 2178324 23 23 23 23 2179324 23 23 23 23 2180424 23 23 23 23 2181424 23 23 23 23 2182424 23 23 23 23 218325 24 24 24 24 218425 24 24 24 24 218525 24 24 24 24 2186125 24 24 24 24 2187125 24 24 24 24 2188125 24 24 24 24 2189225 24 24 24 24 2190225 24 24 24 24 2191225 24 24 24 24 2192325 24 24 24 24 2193325 24 24 24 24 2194325 24 24 24 24 2195425 24 24 24 24 2196425 24 24 24 24 2197425 24 24 24 24 2198SELECT FOUND_ROWS(); 2199FOUND_ROWS() 22001185 2201CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30; 2202SELECT * FROM v1; 2203f0 f1 f2 22041 0 0 2205101 0 0 2206201 0 0 2207301 0 0 2208401 0 0 22092 1 1 2210102 1 1 2211202 1 1 2212302 1 1 2213402 1 1 22143 2 2 2215103 2 2 2216203 2 2 2217303 2 2 2218403 2 2 22194 3 3 2220104 3 3 2221204 3 3 2222304 3 3 2223404 3 3 22245 4 4 2225105 4 4 2226205 4 4 2227305 4 4 2228405 4 4 22296 5 5 2230106 5 5 2231206 5 5 2232306 5 5 2233406 5 5 2234drop view v1; 2235CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100; 2236SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; 2237f0 f1 f2 22381 0 0 2239101 0 0 2240201 0 0 2241301 0 0 2242401 0 0 22432 1 1 2244102 1 1 2245202 1 1 2246302 1 1 2247402 1 1 224811 10 10 2249111 10 10 2250211 10 10 2251311 10 10 2252411 10 10 225312 11 11 2254112 11 11 2255212 11 11 2256312 11 11 2257412 11 11 225813 12 12 2259113 12 12 2260213 12 12 2261313 12 12 2262413 12 12 226314 13 13 2264114 13 13 2265214 13 13 2266314 13 13 2267414 13 13 2268CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100; 2269SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0 2270LIMIT 30; 2271f0 f1 f2 f0 f1 f2 22721 0 0 1 0 0 22731 0 0 101 0 0 22741 0 0 201 0 0 22751 0 0 301 0 0 22761 0 0 401 0 0 2277101 0 0 1 0 0 2278101 0 0 101 0 0 2279101 0 0 201 0 0 2280101 0 0 301 0 0 2281101 0 0 401 0 0 2282201 0 0 1 0 0 2283201 0 0 101 0 0 2284201 0 0 201 0 0 2285201 0 0 301 0 0 2286201 0 0 401 0 0 2287301 0 0 1 0 0 2288301 0 0 101 0 0 2289301 0 0 201 0 0 2290301 0 0 301 0 0 2291301 0 0 401 0 0 2292401 0 0 1 0 0 2293401 0 0 101 0 0 2294401 0 0 201 0 0 2295401 0 0 301 0 0 2296401 0 0 401 0 0 22972 1 1 2 1 1 22982 1 1 102 1 1 22992 1 1 202 1 1 23002 1 1 302 1 1 23012 1 1 402 1 1 2302SELECT floor(f1/10) f3, count(f2) FROM t1 2303GROUP BY 1 ORDER BY 2,1 LIMIT 5; 2304f3 count(f2) 23050 50 23061 50 23072 50 23083 50 23094 50 2310SELECT floor(f1/10) f3, count(f2) FROM t1 2311GROUP BY 1 ORDER BY 2,1 LIMIT 0; 2312f3 count(f2) 2313CREATE PROCEDURE wl1393_sp_test() 2314BEGIN 2315SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30; 2316SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15; 2317SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 2318ORDER BY f2, f0 LIMIT 15 OFFSET 15; 2319SELECT FOUND_ROWS(); 2320SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; 2321END| 2322CALL wl1393_sp_test()| 2323f0 f1 f2 232412 11 11 2325112 11 11 2326212 11 11 2327312 11 11 2328412 11 11 232913 12 12 2330113 12 12 2331213 12 12 2332313 12 12 2333413 12 12 233414 13 13 2335114 13 13 2336214 13 13 2337314 13 13 2338414 13 13 233915 14 14 2340115 14 14 2341215 14 14 2342315 14 14 2343415 14 14 234416 15 15 2345116 15 15 2346216 15 15 2347316 15 15 2348416 15 15 234917 16 16 2350117 16 16 2351217 16 16 2352317 16 16 2353417 16 16 2354f0 f1 f2 235515 14 14 2356115 14 14 2357215 14 14 2358315 14 14 2359415 14 14 236016 15 15 2361116 15 15 2362216 15 15 2363316 15 15 2364416 15 15 236517 16 16 2366117 16 16 2367217 16 16 2368317 16 16 2369417 16 16 2370f0 f1 f2 237115 14 14 2372115 14 14 2373215 14 14 2374315 14 14 2375415 14 14 237616 15 15 2377116 15 15 2378216 15 15 2379316 15 15 2380416 15 15 238117 16 16 2382117 16 16 2383217 16 16 2384317 16 16 2385417 16 16 2386FOUND_ROWS() 2387445 2388f0 f1 f2 23891 0 0 2390101 0 0 2391201 0 0 2392301 0 0 2393401 0 0 23942 1 1 2395102 1 1 2396202 1 1 2397302 1 1 2398402 1 1 239911 10 10 2400111 10 10 2401211 10 10 2402311 10 10 2403411 10 10 240412 11 11 2405112 11 11 2406212 11 11 2407312 11 11 2408412 11 11 240913 12 12 2410113 12 12 2411213 12 12 2412313 12 12 2413413 12 12 241414 13 13 2415114 13 13 2416214 13 13 2417314 13 13 2418414 13 13 2419DROP PROCEDURE wl1393_sp_test| 2420SELECT d1.f1, d1.f2 FROM t1 2421LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1 2422ORDER BY d1.f2 DESC LIMIT 30; 2423f1 f2 24245 5 24255 5 24265 5 24275 5 24285 5 24295 5 24305 5 24315 5 24325 5 24335 5 24345 5 24355 5 24365 5 24375 5 24385 5 24395 5 24405 5 24415 5 24425 5 24435 5 24445 5 24455 5 24465 5 24475 5 24485 5 24494 4 24504 4 24514 4 24524 4 24534 4 2454SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1); 2455f0 f1 f2 24561 0 0 2457101 0 0 2458201 0 0 2459301 0 0 2460401 0 0 2461SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2); 2462ERROR 21000: Subquery returns more than 1 row 2463DROP TABLE t1, tmp; 2464DROP VIEW v1, v2; 2465# end of WL#1393 - Optimizing filesort with small limit 2466# 2467# Bug #58761 2468# Crash in Field::is_null in field.h on subquery in WHERE clause 2469# 2470CREATE TABLE t1 ( 2471pk INT NOT NULL AUTO_INCREMENT, 2472col_int_key INT DEFAULT NULL, 2473col_varchar_key VARCHAR(1) DEFAULT NULL, 2474PRIMARY KEY (pk), 2475KEY col_varchar_key (col_varchar_key,col_int_key) 2476); 2477INSERT INTO t1 VALUES (27,7,'x'); 2478INSERT INTO t1 VALUES (28,6,'m'); 2479INSERT INTO t1 VALUES (29,4,'c'); 2480CREATE TABLE where_subselect 2481SELECT DISTINCT `pk` AS field1 , `pk` AS field2 2482FROM t1 AS alias1 2483WHERE alias1 . `col_int_key` > 229 2484OR alias1 . `col_varchar_key` IS NOT NULL 2485GROUP BY field1, field2 2486; 2487SELECT * 2488FROM where_subselect 2489WHERE (field1, field2) IN ( 2490SELECT DISTINCT `pk` AS field1 , `pk` AS field2 2491FROM t1 AS alias1 2492WHERE alias1 . `col_int_key` > 229 2493OR alias1 . `col_varchar_key` IS NOT NULL 2494GROUP BY field1, field2 2495); 2496field1 field2 249727 27 249828 28 249929 29 2500DROP TABLE t1; 2501DROP TABLE where_subselect; 2502# End of Bug #58761 2503CREATE TABLE t1 ( 2504id1 INT NULL, 2505id2 INT NOT NULL, 2506junk INT NOT NULL, 2507PRIMARY KEY (id1, id2, junk), 2508INDEX id2_j_id1 (id2, junk, id1) 2509); 2510INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4); 2511INSERT INTO t1 VALUES (5, 2, 1), (6, 2, 2), (7, 2, 3), (8, 2, 4); 2512INSERT INTO t1 VALUES (9, 3, 1), (10, 3, 2), (11, 3, 3), (12, 3, 4); 2513INSERT INTO t1 VALUES (13, 4, 1), (14, 4, 2), (15, 4, 3), (16, 4, 4); 2514INSERT INTO t1 VALUES (17, 5, 1), (18, 5, 2), (19, 5, 3), (20, 5, 4); 2515INSERT INTO t1 VALUES (21, 6, 1), (22, 6, 2), (23, 6, 3), (24, 6, 4); 2516INSERT INTO t1 VALUES (25, 7, 1), (26, 7, 2), (27, 7, 3), (28, 7, 4); 2517INSERT INTO t1 VALUES (29, 8, 1), (30, 8, 2), (31, 8, 3), (32, 8, 4); 2518INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4); 2519EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; 2520id select_type table type possible_keys key key_len ref rows Extra 25211 SIMPLE t1 ref id2_j_id1 id2_j_id1 4 const 4 Using where; Using index; Using filesort 2522SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; 2523id1 252413 252514 252615 252716 2528DROP TABLE t1; 2529CREATE TABLE t1 ( 2530a INT, 2531b INT NOT NULL, 2532c char(100), 2533KEY (b, c), 2534KEY (b, a, c) 2535) 2536DEFAULT CHARSET = utf8; 2537INSERT INTO t1 VALUES 2538(1, 1, 1), 2539(2, 2, 2), 2540(3, 3, 3), 2541(4, 4, 4), 2542(5, 5, 5), 2543(6, 6, 6), 2544(7, 7, 7), 2545(8, 8, 8), 2546(9, 9, 9); 2547INSERT INTO t1 SELECT a + 10, b, c FROM t1; 2548INSERT INTO t1 SELECT a + 20, b, c FROM t1; 2549INSERT INTO t1 SELECT a + 40, b, c FROM t1; 2550INSERT INTO t1 SELECT a + 80, b, c FROM t1; 2551INSERT INTO t1 SELECT a + 160, b, c FROM t1; 2552INSERT INTO t1 SELECT a + 320, b, c FROM t1; 2553INSERT INTO t1 SELECT a + 640, b, c FROM t1; 2554INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; 2555EXPLAIN 2556SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; 2557id select_type table type possible_keys key key_len ref rows Extra 25581 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where 2559SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; 2560a 25612071 25622061 25632051 25642041 25652031 25662021 25672011 25682001 25691991 2570EXPLAIN 2571SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; 2572id select_type table type possible_keys key key_len ref rows Extra 25731 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where; Using temporary 2574SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; 2575a 25762071 25772061 25782051 25792041 25802031 25812021 25822011 25832001 25841991 2585DROP TABLE t1; 2586# 2587# Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering 2588# is used 2589# 2590CREATE TABLE t1 (a INT, b INT, KEY (a)); 2591INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL); 2592INSERT INTO t1 SELECT a+4, b FROM t1; 2593INSERT INTO t1 SELECT a+8, b FROM t1; 2594CREATE TABLE t2 (a INT, b INT); 2595INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL); 2596INSERT INTO t2 SELECT a+4, b FROM t2; 2597# shouldn't have "using filesort" 2598EXPLAIN 2599SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; 2600id select_type table type possible_keys key key_len ref rows Extra 26011 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using temporary; Using filesort 26021 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2603# should have "using filesort" 2604EXPLAIN 2605SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; 2606id select_type table type possible_keys key key_len ref rows Extra 26071 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using temporary; Using filesort 26081 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2609# should have "using filesort" 2610EXPLAIN 2611SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; 2612id select_type table type possible_keys key key_len ref rows Extra 26131 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using temporary; Using filesort 26141 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2615DROP TABLE t1, t2; 2616# 2617# Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and 2618# ORDER BY computed col 2619# 2620CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, KEY( a, b ) ); 2621INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 2622INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; 2623CREATE TABLE t2( a INT PRIMARY KEY, b INT ); 2624INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 2625INSERT INTO t2 SELECT a + 5, b + 5 FROM t2; 2626EXPLAIN 2627SELECT count(*) AS c, t1.a 2628FROM t1 JOIN t2 ON t1.b = t2.a 2629WHERE t2.b = 1 2630GROUP BY t1.a 2631ORDER by c 2632LIMIT 2; 2633id select_type table type possible_keys key key_len ref rows Extra 26341 SIMPLE t1 index NULL a 8 NULL 10 Using index; Using temporary; Using filesort 26351 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where 2636DROP TABLE t1, t2; 2637# 2638# Bug #707848: WHERE condition with OR + ORDER BY + field substitution 2639# 2640CREATE TABLE t1 (a int PRIMARY KEY); 2641INSERT INTO t1 VALUES 2642(9), (7), (11), (15), (2), (4), (1), (5), (14), (54), (3), (8); 2643EXPLAIN EXTENDED 2644SELECT * FROM t1 r JOIN t1 s ON r.a = s.a 2645WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0 2646ORDER BY 1 LIMIT 10; 2647id select_type table type possible_keys key key_len ref rows filtered Extra 26481 SIMPLE r range PRIMARY PRIMARY 4 NULL 12 100.00 Using where; Using index 26491 SIMPLE s eq_ref PRIMARY PRIMARY 4 test.r.a 1 100.00 Using index 2650Warnings: 2651Note 1003 select `test`.`r`.`a` AS `a`,`test`.`s`.`a` AS `a` from `test`.`t1` `r` join `test`.`t1` `s` where `test`.`s`.`a` = `test`.`r`.`a` and (`test`.`r`.`a` in (2,9) or `test`.`r`.`a` < 100 and `test`.`r`.`a` <> 0) order by 1 limit 10 2652SELECT * FROM t1 r JOIN t1 s ON r.a = s.a 2653WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0 2654ORDER BY 1 LIMIT 10; 2655a a 26561 1 26572 2 26583 3 26594 4 26605 5 26617 7 26628 8 26639 9 266411 11 266514 14 2666DROP TABLE t1; 2667# 2668# Bug #59110: Memory leak of QUICK_SELECT_I allocated memory 2669# and 2670# Bug #59308: Incorrect result for 2671SELECT DISTINCT <col>... ORDER BY <col> DESC 2672 2673# Use Valgrind to detect #59110! 2674# 2675CREATE TABLE t1 (a INT,KEY (a)); 2676INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 2677EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; 2678id select_type table type possible_keys key key_len ref rows Extra 26791 SIMPLE t1 range a a 5 NULL 10 Using where; Using index 2680SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; 2681a 1 268210 1 26839 1 26848 1 26857 1 26866 1 26875 1 26884 1 26893 1 26902 1 2691DROP TABLE t1; 2692# 2693# Bug#11765255 58201: 2694# VALGRIND/CRASH WHEN ORDERING BY MULTIPLE AGGREGATE FUNCTIONS 2695# 2696select 1 order by max(1) + min(1); 26971 26981 2699End of 5.1 tests 2700# 2701# Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY 2702# when it should use index 2703# 2704CREATE TABLE t1 (i1 integer NOT NULL PRIMARY KEY); 2705CREATE TABLE t2 (i2 integer NOT NULL PRIMARY KEY); 2706CREATE TABLE t3 (i3 integer); 2707INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12); 2708INSERT INTO t2 SELECT * FROM t1; 2709EXPLAIN EXTENDED 2710SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 2711LEFT JOIN t3 ON t2.i2 = t3.i3 2712ORDER BY t1.i1 LIMIT 5; 2713id select_type table type possible_keys key key_len ref rows filtered Extra 27141 SIMPLE t3 system NULL NULL NULL NULL 0 0.00 Const row not found 27151 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 100.00 Using index 27161 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 100.00 Using index 2717Warnings: 2718Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`i2` = `test`.`t1`.`i1` order by `test`.`t1`.`i1` limit 5 2719SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 2720LEFT JOIN t3 ON t2.i2 = t3.i3 2721ORDER BY t1.i1 LIMIT 5; 2722i1 i2 27231 1 27242 2 27253 3 27264 4 27275 5 2728DROP TABLE t1, t2, t3; 2729# 2730# Fix of LP BUG#793589 Wrong result with double ORDER BY 2731# 2732CREATE TABLE t1 ( b int) ; 2733INSERT INTO t1 VALUES (8),(9); 2734CREATE TABLE t2 ( a int, b int, PRIMARY KEY (a)) ; 2735INSERT INTO t2 VALUES (6,7),(7,7),(8,1),(9,7),(10,1),(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5); 2736SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1; 2737field1 27381 27397 2740SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b; 2741b b 27421 8 27437 9 2744SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b; 2745b b 27461 8 27477 9 2748SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b; 2749b 27501 27517 2752# field1 removed from ORDER BY 2753explain extended 2754SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1; 2755id select_type table type possible_keys key key_len ref rows filtered Extra 27561 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort 27571 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 2758Warnings: 2759Note 1003 select `test`.`t2`.`b` AS `field1` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b` 2760explain extended 2761SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b; 2762id select_type table type possible_keys key key_len ref rows filtered Extra 27631 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort 27641 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 2765Warnings: 2766Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b` 2767explain extended 2768SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b; 2769id select_type table type possible_keys key key_len ref rows filtered Extra 27701 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort 27711 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 2772Warnings: 2773Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b` 2774explain extended 2775SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b; 2776id select_type table type possible_keys key key_len ref rows filtered Extra 27771 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort 27781 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 2779Warnings: 2780Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b` 2781drop table t1,t2; 2782End of 5.2 tests 2783# 2784# Bug mdev-449: ORDER BY with small sort_buffer_size 2785# 2786CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200)); 2787INSERT INTO t1(f1, f2) VALUES 2788(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), 2789(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), 2790(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), 2791(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), 2792(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), 2793(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), 2794(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), 2795(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), 2796(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), 2797(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), 2798(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), 2799(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), 2800(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), 2801(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), 2802(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), 2803(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), 2804(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), 2805(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), 2806(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), 2807(96,"96"),(97,"97"),(98,"98"),(99,"99"); 2808set @save_sort_buffer_size= @@sort_buffer_size; 2809set sort_buffer_size= 2000; 2810SELECT * FROM t1 ORDER BY f1 DESC, f0; 2811f0 f1 f2 2812100 99 99 281399 98 98 281498 97 97 281597 96 96 281696 95 95 281795 94 94 281894 93 93 281993 92 92 282092 91 91 282191 90 90 282290 89 89 282389 88 88 282488 87 87 282587 86 86 282686 85 85 282785 84 84 282884 83 83 282983 82 82 283082 81 81 283181 80 80 283280 79 79 283379 78 78 283478 77 77 283577 76 76 283676 75 75 283775 74 74 283874 73 73 283973 72 72 284072 71 71 284171 70 70 284270 69 69 284369 68 68 284468 67 67 284567 66 66 284666 65 65 284765 64 64 284864 63 63 284963 62 62 285062 61 61 285161 60 60 285260 59 59 285359 58 58 285458 57 57 285557 56 56 285656 55 55 285755 54 54 285854 53 53 285953 52 52 286052 51 51 286151 50 50 286250 49 49 286349 48 48 286448 47 47 286547 46 46 286646 45 45 286745 44 44 286844 43 43 286943 42 42 287042 41 41 287141 40 40 287240 39 39 287339 38 38 287438 37 37 287537 36 36 287636 35 35 287735 34 34 287834 33 33 287933 32 32 288032 31 31 288131 30 30 288230 29 29 288329 28 28 288428 27 27 288527 26 26 288626 25 25 288725 24 24 288824 23 23 288923 22 22 289022 21 21 289121 20 20 289220 19 19 289319 18 18 289418 17 17 289517 16 16 289616 15 15 289715 14 14 289814 13 13 289913 12 12 290012 11 11 290111 10 10 290210 9 9 29039 8 8 29048 7 7 29057 6 6 29066 5 5 29075 4 4 29084 3 3 29093 2 2 29102 1 1 29111 0 0 2912set sort_buffer_size= @save_sort_buffer_size; 2913DROP TABLE t1; 2914End of 5.3 tests 2915# 2916# Bug 54599: discarded fast range scan for query with 2917# GROUP BY + ORDER BY + LIMIT 2918# 2919create table t0 (a int); 2920insert into t0 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); 2921create table t1 (a int, b int, index idx1(a,b), index idx2(b,a)); 2922insert into t1 2923select 1000*s4.a+100*s3.a+10*s2.a + s1.a, 1000*s4.a+100*s3.a+10*s2.a+s1.a 2924from t0 s1, t0 s2, t0 s3, t0 s4; 2925analyze table t1; 2926explain 2927select b, count(*) num_cnt from t1 2928where a > 9750 group by b order by num_cnt; 2929id select_type table type possible_keys key key_len ref rows Extra 29301 SIMPLE t1 range idx1 idx1 5 NULL 503 Using where; Using index; Using temporary; Using filesort 2931flush status; 2932select b, count(*) num_cnt from t1 2933where a > 9750 group by b order by num_cnt; 2934show status like '%Handler_read%'; 2935Variable_name Value 2936Handler_read_first 0 2937Handler_read_key 250 2938Handler_read_last 0 2939Handler_read_next 249 2940Handler_read_prev 0 2941Handler_read_retry 0 2942Handler_read_rnd 249 2943Handler_read_rnd_deleted 0 2944Handler_read_rnd_next 250 2945explain 2946select b, count(*) num_cnt from t1 2947where a > 9750 group by b order by num_cnt limit 1; 2948id select_type table type possible_keys key key_len ref rows Extra 29491 SIMPLE t1 range idx1 idx1 5 NULL 503 Using where; Using index; Using temporary; Using filesort 2950flush status; 2951select b, count(*) num_cnt from t1 2952where a > 9750 group by b order by num_cnt limit 1; 2953show status like '%Handler_read%'; 2954Variable_name Value 2955Handler_read_first 0 2956Handler_read_key 250 2957Handler_read_last 0 2958Handler_read_next 249 2959Handler_read_prev 0 2960Handler_read_retry 0 2961Handler_read_rnd 1 2962Handler_read_rnd_deleted 0 2963Handler_read_rnd_next 250 2964drop table t0, t1; 2965# 2966# LP bug #1002508 : the number of expected rows to be examined is off 2967# (bug #13528826) 2968# 2969CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam; 2970INSERT INTO t1 VALUES 2971(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); 2972CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam; 2973INSERT INTO t2 VALUES 2974(103, 7), (109, 3), (102, 3), (108, 1), (106, 3), 2975(107, 7), (105, 1), (101, 3), (100, 7), (110, 1); 2976EXPLAIN 2977SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a; 2978id select_type table type possible_keys key key_len ref rows Extra 29791 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index 29801 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index 2981EXPLAIN 2982SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8; 2983id select_type table type possible_keys key key_len ref rows Extra 29841 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index 29851 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index 2986EXPLAIN 2987SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 100; 2988id select_type table type possible_keys key key_len ref rows Extra 29891 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index 29901 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index 2991DROP TABLE t1,t2; 2992# 2993# MDEV-4974 memory leak in 5.5.32-MariaDB-1~wheezy-log 2994# 2995set sort_buffer_size=default; 2996set max_sort_length=default; 2997create table t1(a int); 2998insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2999create table t2 (b int, 3000col1 varchar(255), 3001col2 varchar(255) 3002) character set utf8; 3003insert into t2 select 3004A.a+10*B.a, 3005concat('wow-wow-col1-value-', A.a+10*B.a+100*C.a), 3006concat('wow-wow-col2-value-', A.a+10*B.a+100*C.a) 3007from 3008t1 A, t1 B, t1 C where C.a < 8; 3009create table t3 as 3010select distinct A.col1 as XX, B.col1 as YY 3011from 3012t2 A, t2 B 3013where A.b = B.b 3014order by A.col2, B.col2 limit 10, 1000000; 3015drop table t1,t2,t3; 3016# 3017# mdev-10705 : long order by list that can be skipped 3018# 3019SELECT 1 3020UNION 3021( SELECT 2 3022ORDER BY NULL, @a0 := 3, @a1 := 3, @a2 := 3, @a3 := 3, @a4 := 3, 3023@a5 := 3, @a6 := 3, @a7 := 3, @a8 := 3, @a9 := 3, @a10 := 3 ); 30241 30251 30262 3027# 3028# mdev-6706: semi-join with duplicate weedout + ORDER BY 3029# 3030CREATE TABLE t1 (f1 VARCHAR(3)) ENGINE=MyISAM; 3031INSERT INTO t1 VALUES ('foo'); 3032CREATE TABLE t2 (f2 VARCHAR(3)) ENGINE=MyISAM; 3033INSERT INTO t2 VALUES ('bar'),('baz'); 3034CREATE TABLE t3 3035(i3_key INT, f3_key VARCHAR(3), f3 VARCHAR(3), KEY(f3_key,i3_key)) 3036ENGINE=MyISAM; 3037INSERT INTO t3 VALUES (0,'qux','qux'),(8,'bar','bar'); 3038SELECT CONCAT( f1, f2 ) AS field FROM t1, t2 3039WHERE f1 = ANY ( SELECT f1 3040FROM t1 3041LEFT JOIN ( t3 AS t3a, t3 AS t3b ) 3042ON ( t3b.f3_key = t3a.f3 ) 3043WHERE t3a.f3 < f1 OR t3b.f3 != f1 ); 3044field 3045foobar 3046foobaz 3047SELECT CONCAT( f1, f2 ) AS field FROM t1, t2 3048WHERE f1 = ANY ( SELECT f1 3049FROM t1 3050LEFT JOIN ( t3 AS t3a, t3 AS t3b ) 3051ON ( t3b.f3_key = t3a.f3 ) 3052WHERE t3a.f3 < f1 OR t3b.f3 != f1 ) 3053ORDER BY field; 3054field 3055foobar 3056foobaz 3057EXPLAIN EXTENDED SELECT CONCAT( f1, f2 ) AS field FROM t1, t2 3058WHERE f1 = ANY ( SELECT f1 3059FROM t1 3060LEFT JOIN ( t3 AS t3a, t3 AS t3b ) 3061ON ( t3b.f3_key = t3a.f3 ) 3062WHERE t3a.f3 < f1 OR t3b.f3 != f1 ) 3063ORDER BY field; 3064id select_type table type possible_keys key key_len ref rows filtered Extra 30651 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 30661 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 30671 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 30681 PRIMARY t3a ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary 30691 PRIMARY t3b ref f3_key f3_key 6 test.t3a.f3 1 100.00 Using where; End temporary 3070Warnings: 3071Note 1003 select concat('foo',`test`.`t2`.`f2`) AS `field` from `test`.`t2` semi join ((`test`.`t3` `t3a` join `test`.`t3` `t3b`)) where `test`.`t3a`.`f3` < 'foo' or `test`.`t3b`.`f3` <> 'foo' order by concat('foo',`test`.`t2`.`f2`) 3072DROP TABLE t1,t2,t3; 3073End of 5.5 tests 3074# 3075# MDEV-5884: EXPLAIN UPDATE ... ORDER BY LIMIT shows wrong #rows 3076# 3077create table t2(a int); 3078insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3079create table t1 (key1 int, col1 int, key(key1)); 3080insert into t1 3081select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C; 3082# Should show rows=2, not rows=100 3083explain update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2; 3084id select_type table type possible_keys key key_len ref rows Extra 30851 SIMPLE t1 range key1 key1 5 NULL 2 Using where; Using buffer 3086drop table t1,t2; 3087# 3088# MDEV-465: Optimizer : wrong index choice, leading to strong performances issues 3089# 3090CREATE TABLE t1 ( 3091id1 int(10) unsigned NOT NULL auto_increment, 3092id2 tinyint(3) unsigned NOT NULL default '0', 3093id3 tinyint(3) unsigned NOT NULL default '0', 3094id4 int(10) unsigned NOT NULL default '0', 3095date timestamp NOT NULL default CURRENT_TIMESTAMP, 3096PRIMARY KEY (id1), 3097KEY id_234_date (id2,id3,id4,date), 3098KEY id_23_date (id2,id3,date) 3099) ENGINE=MyISAM DEFAULT CHARSET=latin1; 3100# t1 has "bad" index declaration order.. 3101CREATE TABLE t2 ( 3102id1 int(10) unsigned NOT NULL auto_increment, 3103id2 tinyint(3) unsigned NOT NULL default '0', 3104id3 tinyint(3) unsigned NOT NULL default '0', 3105id4 int(10) unsigned NOT NULL default '0', 3106date timestamp NOT NULL default CURRENT_TIMESTAMP, 3107PRIMARY KEY (id1), 3108KEY id_23_date (id2,id3,date), 3109KEY id_234_date (id2,id3,id4,date) 3110) ENGINE=MyISAM DEFAULT CHARSET=latin1; 3111# t2 has a "good" index declaration order 3112INSERT INTO t1 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1); 3113INSERT INTO t1 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1); 3114INSERT INTO t2 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1); 3115INSERT INTO t2 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1); 3116# The following two must both use id_23_date and no "using filesort": 3117EXPLAIN SELECT id1 FROM t1 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4; 3118id select_type table type possible_keys key key_len ref rows Extra 31191 SIMPLE t1 range id_234_date,id_23_date id_23_date 2 NULL 8 Using where 3120# See above query 3121EXPLAIN SELECT id1 FROM t2 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4; 3122id select_type table type possible_keys key key_len ref rows Extra 31231 SIMPLE t2 range id_23_date,id_234_date id_23_date 2 NULL 8 Using where 3124drop table t1,t2; 3125# 3126# MDEV-8989: ORDER BY optimizer ignores equality propagation 3127# 3128set @tmp_8989=@@optimizer_switch; 3129set optimizer_switch='orderby_uses_equalities=on'; 3130create table t0(a int); 3131insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3132create table t1(a int); 3133insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; 3134create table t2 ( 3135pk int primary key, 3136a int, b int, 3137filler char(200), 3138key(a) 3139); 3140insert into t2 select a, 1000-a, 1000-a, repeat('abc-',50) from t1 where a<200 limit 200; 3141create table t3 ( 3142pk int primary key, 3143a int, b int, 3144filler char(200), 3145key(a) 3146); 3147insert into t3 select a, 1000-a, 1000-a, repeat('abc-',50) from t1; 3148insert into t3 select a+1000, 1000+a, 1000+a, repeat('abc-',50) from t1; 3149# The optimizer produces an order of 't2,t3' for this join 3150# 3151# Case #1 (from the bug report): 3152# Q1 can take advantage of t2.a to resolve ORDER BY limit w/o sorting 3153explain 3154select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 3155from t2, t3 where t2.a=t3.a order by t2.a limit 5; 3156id select_type table type possible_keys key key_len ref rows Extra 31571 SIMPLE t2 index a a 5 NULL 5 Using where 31581 SIMPLE t3 ref a a 5 test.t2.a 1 3159# 3160# This is Q2 which used to have "Using temporary; using filesort" but 3161# has the same query plan as Q1: 3162# 3163explain 3164select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 3165from t2, t3 where t2.a=t3.a order by t3.a limit 5; 3166id select_type table type possible_keys key key_len ref rows Extra 31671 SIMPLE t2 index a a 5 NULL 5 Using where 31681 SIMPLE t3 ref a a 5 test.t2.a 1 3169select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 3170from t2, t3 where t2.a=t3.a order by t2.a limit 5; 3171pk a b pk a b 3172199 801 801 199 801 801 3173198 802 802 198 802 802 3174197 803 803 197 803 803 3175196 804 804 196 804 804 3176195 805 805 195 805 805 3177select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 3178from t2, t3 where t2.a=t3.a order by t3.a limit 5; 3179pk a b pk a b 3180199 801 801 199 801 801 3181198 802 802 198 802 802 3182197 803 803 197 803 803 3183196 804 804 196 804 804 3184195 805 805 195 805 805 3185# 3186# Case #2: here, only "Using temporary" is removed. "Using filesort" remains. 3187# 3188explain 3189select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 3190from t2, t3 where t2.a=t3.a order by t2.a limit 25; 3191id select_type table type possible_keys key key_len ref rows Extra 31921 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using filesort 31931 SIMPLE t3 ref a a 5 test.t2.a 1 3194explain 3195select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 3196from t2, t3 where t2.a=t3.a order by t3.a limit 25; 3197id select_type table type possible_keys key key_len ref rows Extra 31981 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using filesort 31991 SIMPLE t3 ref a a 5 test.t2.a 1 3200select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 3201from t2, t3 where t2.a=t3.a order by t2.a limit 25; 3202pk a b pk a b 3203199 801 801 199 801 801 3204198 802 802 198 802 802 3205197 803 803 197 803 803 3206196 804 804 196 804 804 3207195 805 805 195 805 805 3208194 806 806 194 806 806 3209193 807 807 193 807 807 3210192 808 808 192 808 808 3211191 809 809 191 809 809 3212190 810 810 190 810 810 3213189 811 811 189 811 811 3214188 812 812 188 812 812 3215187 813 813 187 813 813 3216186 814 814 186 814 814 3217185 815 815 185 815 815 3218184 816 816 184 816 816 3219183 817 817 183 817 817 3220182 818 818 182 818 818 3221181 819 819 181 819 819 3222180 820 820 180 820 820 3223179 821 821 179 821 821 3224178 822 822 178 822 822 3225177 823 823 177 823 823 3226176 824 824 176 824 824 3227175 825 825 175 825 825 3228select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 3229from t2, t3 where t2.a=t3.a order by t3.a limit 25; 3230pk a b pk a b 3231199 801 801 199 801 801 3232198 802 802 198 802 802 3233197 803 803 197 803 803 3234196 804 804 196 804 804 3235195 805 805 195 805 805 3236194 806 806 194 806 806 3237193 807 807 193 807 807 3238192 808 808 192 808 808 3239191 809 809 191 809 809 3240190 810 810 190 810 810 3241189 811 811 189 811 811 3242188 812 812 188 812 812 3243187 813 813 187 813 813 3244186 814 814 186 814 814 3245185 815 815 185 815 815 3246184 816 816 184 816 816 3247183 817 817 183 817 817 3248182 818 818 182 818 818 3249181 819 819 181 819 819 3250180 820 820 180 820 820 3251179 821 821 179 821 821 3252178 822 822 178 822 822 3253177 823 823 177 823 823 3254176 824 824 176 824 824 3255175 825 825 175 825 825 3256# 3257# Case #3: single table access (the code that decides whether we need 3258# "Using temporary" is not invoked) 3259# 3260explain select * from t3 where b=a order by a limit 10; 3261id select_type table type possible_keys key key_len ref rows Extra 32621 SIMPLE t3 index NULL a 5 NULL 10 Using where 3263# This must not use filesort. The query plan should be like the query above: 3264explain select * from t3 where b=a order by b limit 10; 3265id select_type table type possible_keys key key_len ref rows Extra 32661 SIMPLE t3 index NULL a 5 NULL 10 Using where 3267drop table t0,t1,t2,t3; 3268set @@optimizer_switch=@tmp_8989; 3269set optimizer_switch='orderby_uses_equalities=on'; 3270# 3271# MDEV-10880: Assertions `keypart_map' or 3272# `prebuilt->search_tuple->n_fields > 0' fail on DISTINCT and 3273# GROUP BY constant 3274# 3275CREATE TABLE t1 (pk INT PRIMARY KEY); 3276INSERT INTO t1 VALUES (1),(2),(3); 3277SELECT DISTINCT pk FROM t1 GROUP BY 'foo'; 3278pk 32791 3280SELECT DISTINCT pk FROM t1; 3281pk 32821 32832 32843 3285DROP TABLE t1; 3286# 3287# MDEV-21044: Wrong result when using a smaller size for sort buffer 3288# 3289create table t1(a varchar(765),b int); 3290insert into t1 values ("a",1),("b",2),("c",3),("e",4); 3291insert into t1 values ("d",5),("f",6),("g",7),("h",8); 3292insert into t1 values ("k",11),("l",12),("i",9),("j",10); 3293insert into t1 values ("m",13),("n",14),("o",15),("p",16); 3294set @save_sort_buffer_size= @@sort_buffer_size; 3295set sort_buffer_size=1024; 3296select * from t1 order by b; 3297a b 3298a 1 3299b 2 3300c 3 3301e 4 3302d 5 3303f 6 3304g 7 3305h 8 3306i 9 3307j 10 3308k 11 3309l 12 3310m 13 3311n 14 3312o 15 3313p 16 3314set @@sort_buffer_size= @save_sort_buffer_size; 3315drop table t1; 3316# 3317# MDEV-22715: SIGSEGV in radixsort_for_str_ptr and in native_compare/my_qsort2 (optimized builds) 3318# 3319SET @save_sort_buffer_size= @@sort_buffer_size; 3320SET @save_max_sort_length= @@max_sort_length; 3321SET max_sort_length=8; 3322Warnings: 3323Warning 1292 Truncated incorrect max_sort_length value: '8' 3324SET sort_buffer_size=1024; 3325CREATE TABLE t1(a INT, b DECIMAL(65), c BLOB); 3326INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_25; 3327INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_25; 3328SELECT * FROM t1 ORDER BY a,b; 3329a b c 33301 1 1 33311 1 1 33322 2 2 33332 2 2 33343 3 3 33353 3 3 33364 4 4 33374 4 4 33385 5 5 33395 5 5 33406 6 6 33416 6 6 33427 7 7 33437 7 7 33448 8 8 33458 8 8 33469 9 9 33479 9 9 334810 10 10 334910 10 10 335011 11 11 335111 11 11 335212 12 12 335312 12 12 335413 13 13 335513 13 13 335614 14 14 335714 14 14 335815 15 15 335915 15 15 336016 16 16 336116 16 16 336217 17 17 336317 17 17 336418 18 18 336518 18 18 336619 19 19 336719 19 19 336820 20 20 336920 20 20 337021 21 21 337121 21 21 337222 22 22 337322 22 22 337423 23 23 337523 23 23 337624 24 24 337724 24 24 337825 25 25 337925 25 25 3380SET @@sort_buffer_size= @save_sort_buffer_size; 3381SET @@max_sort_length= @save_max_sort_length; 3382DROP TABLE t1; 3383# 3384# MDEV-22390: Assertion `m_next_rec_ptr >= m_rawmem' failed in Filesort_buffer::spaceleft | 3385# SIGSEGV in __memmove_avx_unaligned_erms from my_b_write (on optimized) 3386# 3387SET @save_max_sort_length= @@max_sort_length; 3388SET @save_sort_buffer_size= @@sort_buffer_size; 3389SET @save_max_length_for_sort_data= @@max_length_for_sort_data; 3390SET max_sort_length=8; 3391Warnings: 3392Warning 1292 Truncated incorrect max_sort_length value: '8' 3393SET sort_buffer_size=1024; 3394SET max_length_for_sort_data=7000; 3395CREATE TABLE t1(a VARCHAR(64), b VARCHAR(2048))DEFAULT CHARSET=utf8; 3396INSERT INTO t1 SELECT seq,seq from seq_1_to_100; 3397ANALYZE FORMAT=JSON SELECT * FROM t1 ORDER BY a LIMIT 5; 3398ANALYZE 3399{ 3400 "query_block": { 3401 "select_id": 1, 3402 "r_loops": 1, 3403 "r_total_time_ms": "REPLACED", 3404 "read_sorted_file": { 3405 "r_rows": 5, 3406 "filesort": { 3407 "sort_key": "t1.a", 3408 "r_loops": 1, 3409 "r_total_time_ms": "REPLACED", 3410 "r_limit": 5, 3411 "r_used_priority_queue": false, 3412 "r_output_rows": 100, 3413 "r_buffer_size": "REPLACED", 3414 "r_sort_mode": "sort_key,packed_addon_fields", 3415 "table": { 3416 "table_name": "t1", 3417 "access_type": "ALL", 3418 "r_loops": 1, 3419 "rows": 100, 3420 "r_rows": 100, 3421 "r_table_time_ms": "REPLACED", 3422 "r_other_time_ms": "REPLACED", 3423 "filtered": 100, 3424 "r_filtered": 100 3425 } 3426 } 3427 } 3428 } 3429} 3430SELECT * FROM t1 ORDER BY a LIMIT 5; 3431a b 34321 1 343310 10 3434100 100 343511 11 343612 12 3437SET max_sort_length= @save_max_sort_length; 3438SET sort_buffer_size= @save_sort_buffer_size; 3439SET max_length_for_sort_data= @save_max_length_for_sort_data; 3440DROP TABLE t1; 3441# 3442# MDEV-23596: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row 3443# 3444CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b)); 3445INSERT INTO t1 VALUES (0, 1),(1, 2); 3446CREATE TABLE t2 SELECT * FROM t1; 3447EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; 3448id select_type table type possible_keys key key_len ref rows Extra 34491 PRIMARY t2 ALL NULL NULL NULL NULL 2 34502 DEPENDENT SUBQUERY t1 index PRIMARY b 5 NULL 1 Using where 3451SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; 3452c 34531 3454NULL 3455DROP TABLE t1,t2; 3456# end of 10.1 tests 3457# 3458# MDEV-13994: Bad join results with orderby_uses_equalities=on 3459# 3460CREATE TABLE books ( 3461id int(16) NOT NULL AUTO_INCREMENT, 3462library_id int(16) NOT NULL DEFAULT 0, 3463wings_id int(12) NOT NULL DEFAULT 0, 3464scheduled_for_removal int(1) DEFAULT 0, 3465PRIMARY KEY (id), 3466KEY library_idx (library_id) 3467) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 3468INSERT INTO books VALUES (32625,8663,707,0),(32624,8663,505,1); 3469CREATE TABLE wings ( 3470id int(11) NOT NULL AUTO_INCREMENT, 3471department_id int(11) DEFAULT NULL, 3472PRIMARY KEY (id) 3473) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 3474INSERT INTO wings VALUES (505,11745),(707,11768); 3475SET @save_optimizer_switch=@@optimizer_switch; 3476SET optimizer_switch='orderby_uses_equalities=off'; 3477SELECT wings.id as wing_id, wings.department_id FROM wings 3478WHERE wings.id IN ( SELECT books.wings_id FROM books 3479WHERE books.library_id = 8663 AND 3480books.scheduled_for_removal=0 ) 3481ORDER BY wings.id; 3482wing_id department_id 3483707 11768 3484SET optimizer_switch='orderby_uses_equalities=on'; 3485SELECT wings.id as wing_id, wings.department_id FROM wings 3486WHERE wings.id IN ( SELECT books.wings_id FROM books 3487WHERE books.library_id = 8663 AND 3488books.scheduled_for_removal=0 ) 3489ORDER BY wings.id; 3490wing_id department_id 3491707 11768 3492explain extended SELECT wings.id as wing_id, wings.department_id FROM wings 3493WHERE wings.id IN ( SELECT books.wings_id FROM books 3494WHERE books.library_id = 8663 AND 3495books.scheduled_for_removal=0 ) 3496ORDER BY wings.id; 3497id select_type table type possible_keys key key_len ref rows filtered Extra 34981 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort 34991 PRIMARY wings eq_ref PRIMARY PRIMARY 4 test.books.wings_id 1 100.00 35002 MATERIALIZED books ref library_idx library_idx 4 const 2 100.00 Using where 3501Warnings: 3502Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id` 3503set optimizer_switch= @save_optimizer_switch; 3504DROP TABLE books, wings; 3505# 3506# MDEV-17796: query with DISTINCT, GROUP BY and ORDER BY 3507# 3508CREATE TABLE t1 (id int, gr int, v1 varchar(10)); 3509INSERT INTO t1 VALUES (1,1,'A'), (2,2,'B'), (3,3,NULL), (4,4,'C'); 3510SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) FROM t1 3511WHERE gr in (4,2) 3512GROUP BY id 3513ORDER BY id+1 DESC; 3514NULLIF(GROUP_CONCAT(v1), null) 3515C 3516B 3517DROP TABLE t1; 3518# 3519# MDEV-24033: SIGSEGV in __memcmp_avx2_movbe from queue_insert | SIGSEGV in __memcmp_avx2_movbe from native_compare 3520# 3521SET @save_max_length_for_sort_data=@@max_length_for_sort_data; 3522SET @save_max_sort_length= @@max_sort_length; 3523SET @save_sql_select_limit= @@sql_select_limit; 3524CREATE TABLE t1 (a DECIMAL(64,0), b INT); 3525INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 3526SET max_length_for_sort_data= 30; 3527SET sql_select_limit = 3; 3528SET max_sort_length=8; 3529Warnings: 3530Warning 1292 Truncated incorrect max_sort_length value: '8' 3531SELECT * FROM t1 ORDER BY a+1; 3532a b 35331 1 35342 2 35353 3 3536SET max_length_for_sort_data=@save_max_length_for_sort_data; 3537SET max_sort_length= @save_max_sort_length; 3538SET sql_select_limit= @save_sql_select_limit; 3539DROP TABLE t1; 3540# 3541# MDEV-25682: EXPLAIN for SELECT with ORDER BY after [ORDER BY] LIMIT 3542# 3543create table t1 (a int); 3544insert into t1 values (3), (7), (1); 3545explain (select a from t1 limit 2) order by a desc; 3546id select_type table type possible_keys key key_len ref rows Extra 35471 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using filesort 35482 DERIVED t1 ALL NULL NULL NULL NULL 3 3549(select a from t1 limit 2) order by a desc; 3550a 35517 35523 3553create table t2 (a int, b int); 3554insert into t2 values (3,70), (7,10), (1,40), (4,30); 3555explain (select b,a from t2 order by a limit 3) order by b desc; 3556id select_type table type possible_keys key key_len ref rows Extra 35571 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using filesort 35582 DERIVED t2 ALL NULL NULL NULL NULL 4 Using filesort 3559(select b,a from t2 order by a limit 3) order by b desc; 3560b a 356170 3 356240 1 356330 4 3564drop table t1,t2; 3565# 3566# MDEV-22256 Assertion `length == pack_length()' failed in Field_timestamp_with_dec::sort_string 3567# 3568SET sql_mode=''; 3569SET @@SESSION.max_sort_length=4; 3570Warnings: 3571Warning 1292 Truncated incorrect max_sort_length value: '4' 3572CREATE TABLE t1 (c TIMESTAMP(1)); 3573INSERT INTO t1 VALUES(0); 3574DELETE FROM t1 ORDER BY c; 3575DROP TABLE t1; 3576SET @@SESSION.max_sort_length=DEFAULT; 3577SET sql_mode=DEFAULT; 3578SET sql_mode=''; 3579SET @@SESSION.max_sort_length=1; 3580Warnings: 3581Warning 1292 Truncated incorrect max_sort_length value: '1' 3582CREATE TEMPORARY TABLE t1 (c DATETIME); 3583INSERT INTO t1 VALUES(0); 3584DELETE FROM t1 ORDER BY c; 3585DROP TABLE t1; 3586SET @@SESSION.max_sort_length=DEFAULT; 3587SET sql_mode=DEFAULT; 3588# End of 10.2 tests 3589# 3590# MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY 3591# 3592create table t1(a int); 3593insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3594create table t2( 3595id int primary key, 3596key1 int,key2 int, 3597col1 int, 3598key(key1), key(key2) 3599); 3600insert into t2 3601select 3602A.a + B.a*10 + C.a*100, 3603A.a + 10*B.a, A.a + 10*B.a, 3604123456 3605from t1 A, t1 B, t1 C; 3606# here type should show ref not index 3607explain select 3608(SELECT concat(id, '-', key1, '-', col1) 3609FROM t2 3610WHERE 3611t2.key1 = t1.a and t2.key1 IS NOT NULL 3612ORDER BY 3613t2.key2 ASC 3614LIMIT 1) 3615from t1; 3616id select_type table type possible_keys key key_len ref rows Extra 36171 PRIMARY t1 ALL NULL NULL NULL NULL 10 36182 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a 10 Using index condition; Using where; Using filesort 3619select 3620(SELECT concat(id, '-', key1, '-', col1) 3621FROM t2 3622WHERE 3623t2.key1 = t1.a and t2.key1 IS NOT NULL 3624ORDER BY 3625t2.key2 ASC 3626LIMIT 1) 3627from t1; 3628(SELECT concat(id, '-', key1, '-', col1) 3629FROM t2 3630WHERE 3631t2.key1 = t1.a and t2.key1 IS NOT NULL 3632ORDER BY 3633t2.key2 ASC 3634LIMIT 1) 3635900-0-123456 3636901-1-123456 3637902-2-123456 3638903-3-123456 3639904-4-123456 3640905-5-123456 3641906-6-123456 3642907-7-123456 3643908-8-123456 3644909-9-123456 3645drop table t1,t2; 3646# End of 10.3 tests 3647# 3648# MDEV-17761: Odd optimizer choice with ORDER BY LIMIT and condition selectivity 3649# 3650create table t1(a int); 3651insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3652create table t2(a int); 3653insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; 3654create table t3(a int); 3655insert into t3 select A.a + 1000 *B.a from t2 A, t1 B; 3656create table t4 ( 3657a int, 3658b int, 3659c int, 3660filler1 char(255), 3661filler2 char(255), 3662key(a) 3663); 3664insert into t4 select a,a,a, a,a from t3; 3665set @tmp_h=@@histogram_size, @tmp_ht=@@histogram_type, @tmp_u=@@use_stat_tables, 3666@tmp_o=@@optimizer_use_condition_selectivity; 3667set histogram_size=100, histogram_type='single_prec_hb'; 3668set use_stat_tables=preferably; 3669set optimizer_use_condition_selectivity=4; 3670analyze table t4 persistent for columns(b) indexes (); 3671Table Op Msg_type Msg_text 3672test.t4 analyze status Engine-independent statistics collected 3673test.t4 analyze status Table is already up to date 3674# rows must be around 1200, not 600: 3675explain extended 3676select * from t4 where b < 5000 order by a limit 600; 3677id select_type table type possible_keys key key_len ref rows filtered Extra 36781 SIMPLE t4 index NULL a 5 NULL 1188 100.00 Using where 3679Warnings: 3680Note 1003 select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`filler1` AS `filler1`,`test`.`t4`.`filler2` AS `filler2` from `test`.`t4` where `test`.`t4`.`b` < 5000 order by `test`.`t4`.`a` limit 600 3681set histogram_size=@tmp_h, histogram_type=@tmp_ht, use_stat_tables=@tmp_u, 3682optimizer_use_condition_selectivity=@tmp_o; 3683drop table t1,t2,t3,t4; 3684# End of 10.4 tests 3685# 3686# MDEV-21655: Server crashes in my_qsort2 / Filesort_buffer::sort_buffer 3687# 3688set @save_sql_mode= @@sql_mode; 3689set sql_mode= 'PAD_CHAR_TO_FULL_LENGTH'; 3690CREATE TABLE t1 ( a CHAR(255), b CHAR(255), c TEXT); 3691INSERT INTO t1 VALUES 3692('1','a', 'a'), ('2','b', 'b'), ('3','c', 'c'), ('4','d','d'), 3693('5','e', 'e'), ('6','f', 'f'), ('7','g','g'), ('8','h','h'), 3694('9','i', 'i'), ('10','j','j'), ('11','k','k'), ('12','l','l'), 3695('13','m','m'), ('14','n','n'), ('15','o','o'); 3696set sort_buffer_size=524*15; 3697select c from t1 order by a,b; 3698c 3699a 3700j 3701k 3702l 3703m 3704n 3705o 3706b 3707c 3708d 3709e 3710f 3711g 3712h 3713i 3714set sort_buffer_size= default; 3715set sql_mode= @save_sql_mode; 3716drop table t1; 3717# 3718# MDEV-21580: Allow packed sort keys in sort buffer 3719# 3720# 3721# This example should not pack sort keys 3722# all fields are fixed-size fields in the ORDER BY clause 3723# 3724create table t1 (a bigint, b bigint, c bigint); 3725insert into t1 select seq, seq, seq from seq_1_to_100; 3726# in r_sort_mode it should show sort_key and not packed_sort_key 3727ANALYZE FORMAT=JSON select * from t1 order by a,b,c; 3728ANALYZE 3729{ 3730 "query_block": { 3731 "select_id": 1, 3732 "r_loops": 1, 3733 "r_total_time_ms": "REPLACED", 3734 "read_sorted_file": { 3735 "r_rows": 100, 3736 "filesort": { 3737 "sort_key": "t1.a, t1.b, t1.c", 3738 "r_loops": 1, 3739 "r_total_time_ms": "REPLACED", 3740 "r_used_priority_queue": false, 3741 "r_output_rows": 100, 3742 "r_buffer_size": "REPLACED", 3743 "r_sort_mode": "sort_key,packed_addon_fields", 3744 "table": { 3745 "table_name": "t1", 3746 "access_type": "ALL", 3747 "r_loops": 1, 3748 "rows": 100, 3749 "r_rows": 100, 3750 "r_table_time_ms": "REPLACED", 3751 "r_other_time_ms": "REPLACED", 3752 "filtered": 100, 3753 "r_filtered": 100 3754 } 3755 } 3756 } 3757 } 3758} 3759select * from t1 order by a,b,c; 3760a b c 37611 1 1 37622 2 2 37633 3 3 37644 4 4 37655 5 5 37666 6 6 37677 7 7 37688 8 8 37699 9 9 377010 10 10 377111 11 11 377212 12 12 377313 13 13 377414 14 14 377515 15 15 377616 16 16 377717 17 17 377818 18 18 377919 19 19 378020 20 20 378121 21 21 378222 22 22 378323 23 23 378424 24 24 378525 25 25 378626 26 26 378727 27 27 378828 28 28 378929 29 29 379030 30 30 379131 31 31 379232 32 32 379333 33 33 379434 34 34 379535 35 35 379636 36 36 379737 37 37 379838 38 38 379939 39 39 380040 40 40 380141 41 41 380242 42 42 380343 43 43 380444 44 44 380545 45 45 380646 46 46 380747 47 47 380848 48 48 380949 49 49 381050 50 50 381151 51 51 381252 52 52 381353 53 53 381454 54 54 381555 55 55 381656 56 56 381757 57 57 381858 58 58 381959 59 59 382060 60 60 382161 61 61 382262 62 62 382363 63 63 382464 64 64 382565 65 65 382666 66 66 382767 67 67 382868 68 68 382969 69 69 383070 70 70 383171 71 71 383272 72 72 383373 73 73 383474 74 74 383575 75 75 383676 76 76 383777 77 77 383878 78 78 383979 79 79 384080 80 80 384181 81 81 384282 82 82 384383 83 83 384484 84 84 384585 85 85 384686 86 86 384787 87 87 384888 88 88 384989 89 89 385090 90 90 385191 91 91 385292 92 92 385393 93 93 385494 94 94 385595 95 95 385696 96 96 385797 97 97 385898 98 98 385999 99 99 3860100 100 100 3861drop table t1; 3862# 3863# Test with Binary columns (using suffix length to determine ordering) 3864# Should show packed_sortkey in the r_sort_mode 3865# 3866create table t1 (a int, b blob); 3867set @save_max_sort_length= @@max_sort_length; 3868insert into t1 select 1, CONCAT(repeat('a', @save_max_sort_length), 'A'); 3869insert into t1 select 2, CONCAT(repeat('a', @save_max_sort_length), 'AB'); 3870insert into t1 select 3, CONCAT(repeat('a', @save_max_sort_length), 'ABE'); 3871insert into t1 select 4, CONCAT(repeat('a', @save_max_sort_length), 'APBX'); 3872insert into t1 select 5, CONCAT(repeat('a', @save_max_sort_length), 'ABAAX'); 3873show variables like '%sort_buffer_size'; 3874Variable_name Value 3875aria_sort_buffer_size 268434432 3876myisam_sort_buffer_size 134216704 3877sort_buffer_size 262144 3878select a, substr(b, @save_max_sort_length+1) from t1 order by b desc; 3879a substr(b, @save_max_sort_length+1) 38805 ABAAX 38814 APBX 38823 ABE 38832 AB 38841 A 3885analyze format=json 3886select a, substr(b, @save_max_sort_length+1) from t1 order by b desc; 3887ANALYZE 3888{ 3889 "query_block": { 3890 "select_id": 1, 3891 "r_loops": 1, 3892 "r_total_time_ms": "REPLACED", 3893 "read_sorted_file": { 3894 "r_rows": 5, 3895 "filesort": { 3896 "sort_key": "t1.b desc", 3897 "r_loops": 1, 3898 "r_total_time_ms": "REPLACED", 3899 "r_used_priority_queue": false, 3900 "r_output_rows": 5, 3901 "r_buffer_size": "REPLACED", 3902 "r_sort_mode": "packed_sort_key,rowid", 3903 "table": { 3904 "table_name": "t1", 3905 "access_type": "ALL", 3906 "r_loops": 1, 3907 "rows": 5, 3908 "r_rows": 5, 3909 "r_table_time_ms": "REPLACED", 3910 "r_other_time_ms": "REPLACED", 3911 "filtered": 100, 3912 "r_filtered": 100 3913 } 3914 } 3915 } 3916 } 3917} 3918drop table t1; 3919# 3920# Packing sort keys with complex collations 3921# 3922create table t1(a varchar(255) charset utf8, b int, c decimal); 3923insert into t1 values ('abc', 1, 1) , ('bcd', 2, 2), ('cde',3, 3); 3924insert into t1 values ('def', 4, 4) , ('efg', 5, 5), ('fgh', 6, 6); 3925# 3926# Should show packed_sortkey in the r_sort_mode 3927# 3928ANALYZE FORMAT=JSON select a, b, c from t1 order by a, b; 3929ANALYZE 3930{ 3931 "query_block": { 3932 "select_id": 1, 3933 "r_loops": 1, 3934 "r_total_time_ms": "REPLACED", 3935 "read_sorted_file": { 3936 "r_rows": 6, 3937 "filesort": { 3938 "sort_key": "t1.a, t1.b", 3939 "r_loops": 1, 3940 "r_total_time_ms": "REPLACED", 3941 "r_used_priority_queue": false, 3942 "r_output_rows": 6, 3943 "r_buffer_size": "REPLACED", 3944 "r_sort_mode": "packed_sort_key,rowid", 3945 "table": { 3946 "table_name": "t1", 3947 "access_type": "ALL", 3948 "r_loops": 1, 3949 "rows": 6, 3950 "r_rows": 6, 3951 "r_table_time_ms": "REPLACED", 3952 "r_other_time_ms": "REPLACED", 3953 "filtered": 100, 3954 "r_filtered": 100 3955 } 3956 } 3957 } 3958 } 3959} 3960select a, b, c from t1 order by a, b; 3961a b c 3962abc 1 1 3963bcd 2 2 3964cde 3 3 3965def 4 4 3966efg 5 5 3967fgh 6 6 3968set @save_max_sort_length= @@max_sort_length; 3969set max_sort_length=5; 3970Warnings: 3971Warning 1292 Truncated incorrect max_sort_length value: '5' 3972# 3973# should show sortkey in r_sort_mode as the collation is complex and 3974# truncation is not possible 3975# 3976ANALYZE FORMAT=JSON select a, b, c from t1 order by a, b; 3977ANALYZE 3978{ 3979 "query_block": { 3980 "select_id": 1, 3981 "r_loops": 1, 3982 "r_total_time_ms": "REPLACED", 3983 "read_sorted_file": { 3984 "r_rows": 6, 3985 "filesort": { 3986 "sort_key": "t1.a, t1.b", 3987 "r_loops": 1, 3988 "r_total_time_ms": "REPLACED", 3989 "r_used_priority_queue": false, 3990 "r_output_rows": 6, 3991 "r_buffer_size": "REPLACED", 3992 "r_sort_mode": "sort_key,packed_addon_fields", 3993 "table": { 3994 "table_name": "t1", 3995 "access_type": "ALL", 3996 "r_loops": 1, 3997 "rows": 6, 3998 "r_rows": 6, 3999 "r_table_time_ms": "REPLACED", 4000 "r_other_time_ms": "REPLACED", 4001 "filtered": 100, 4002 "r_filtered": 100 4003 } 4004 } 4005 } 4006 } 4007} 4008select a, b, c from t1 order by a, b; 4009a b c 4010abc 1 1 4011bcd 2 2 4012cde 3 3 4013def 4 4 4014efg 5 5 4015fgh 6 6 4016set max_sort_length= @save_max_sort_length; 4017drop table t1; 4018# 4019# MDEV-21922: Allow packing addon fields even if they don't honour max_length_for_sort_data 4020# 4021create table t1 (a varchar(200) character set utf8, b int); 4022insert into t1 select seq, seq from seq_1_to_10; 4023select * from t1 order by a; 4024a b 40251 1 402610 10 40272 2 40283 3 40294 4 40305 5 40316 6 40327 7 40338 8 40349 9 4035# 4036# r_sort_mode should show both packed_sort_key and packed_addon_fields 4037# 4038analyze format=json select * from t1 order by a; 4039ANALYZE 4040{ 4041 "query_block": { 4042 "select_id": 1, 4043 "r_loops": 1, 4044 "r_total_time_ms": "REPLACED", 4045 "read_sorted_file": { 4046 "r_rows": 10, 4047 "filesort": { 4048 "sort_key": "t1.a", 4049 "r_loops": 1, 4050 "r_total_time_ms": "REPLACED", 4051 "r_used_priority_queue": false, 4052 "r_output_rows": 10, 4053 "r_buffer_size": "REPLACED", 4054 "r_sort_mode": "packed_sort_key,packed_addon_fields", 4055 "table": { 4056 "table_name": "t1", 4057 "access_type": "ALL", 4058 "r_loops": 1, 4059 "rows": 10, 4060 "r_rows": 10, 4061 "r_table_time_ms": "REPLACED", 4062 "r_other_time_ms": "REPLACED", 4063 "filtered": 100, 4064 "r_filtered": 100 4065 } 4066 } 4067 } 4068 } 4069} 4070drop table t1; 4071# 4072# MDEV-21946: Server crash in store_length upon GROUP BY WITH ROLLUP with geometry field 4073# 4074create table t1 ( a longblob); 4075insert into t1 select repeat('a', 256); 4076insert into t1 select repeat('b', 256); 4077insert into t1 select repeat('c', 256); 4078insert into t1 select repeat('d', 256); 4079SELECT IF( 0, NULL, a ) AS f FROM t1 GROUP BY f WITH ROLLUP; 4080f 4081aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4082bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 4083cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc 4084dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd 4085NULL 4086DROP TABLE t1; 4087# 4088# MDEV-22303: Incorrect ordering with REGEXP_REPLACE and OFFSET/LIMIT 4089# 4090CREATE TABLE t1 (name VARCHAR(20) CHARACTER SET utf8 NOT NULL); 4091INSERT INTO t1 (name) VALUES ('Charles Dickens'), ('Roald Dahl'); 4092SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM t1 4093ORDER BY surname_first ASC; 4094name surname_first 4095Roald Dahl Dahl, Roald 4096Charles Dickens Dickens, Charles 4097SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM t1 4098ORDER BY surname_first ASC LIMIT 1; 4099name surname_first 4100Roald Dahl Dahl, Roald 4101SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM t1 4102ORDER BY surname_first ASC LIMIT 1 OFFSET 1; 4103name surname_first 4104Charles Dickens Dickens, Charles 4105DROP TABLE t1; 4106# 4107# MDEV-22836: Server crashes in err_conv / ErrBuff::set_str 4108# 4109CREATE TABLE t1 (a INT); 4110INSERT INTO t1 VALUES (0),(1); 4111SELECT * FROM t1 ORDER BY CONVERT(AES_ENCRYPT(1,a), CHAR(4)); 4112a 41131 41140 4115DROP TABLE t1; 4116# 4117# MDEV-22819:Wrong result or Assertion `ix > 0' failed in read_to_buffer upon select 4118# with GROUP BY and GROUP_CONCAT 4119# 4120CREATE TABLE t1 (a VARCHAR(1000), b CHAR(1)); 4121INSERT INTO t1 VALUES 4122(REPEAT('a',1000),'a'),(REPEAT('t',932),'t'),('x',NULL),('x',NULL), 4123(REPEAT('z',298),'z'),(REPEAT('p',1000),'p'),(REPEAT('k',468),'k'), 4124(REPEAT('c',1000),'c'),(REPEAT('o',648),'o'),('x',NULL),('x',NULL), 4125(REPEAT('c',258),'c'),(REPEAT('t',414),'t'),(REPEAT('f',966),'f'), 4126(REPEAT('y',746),'y'),(REPEAT('f',1000),'f'); 4127INSERT INTO t1 SELECT * FROM t1; 4128INSERT INTO t1 SELECT * FROM t1; 4129INSERT INTO t1 SELECT * FROM t1; 4130INSERT INTO t1 SELECT * FROM t1; 4131SET @save_sort_buffer_size= @@sort_buffer_size; 4132SET sort_buffer_size= 16384; 4133SELECT LEFT(a,1), GROUP_CONCAT(b) FROM t1 GROUP BY a; 4134LEFT(a,1) GROUP_CONCAT(b) 4135a a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a 4136c c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 4137c c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 4138f f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 4139f f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 4140k k,k,k,k,k,k,k,k,k,k,k,k,k,k,k,k 4141o o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o 4142p p,p,p,p,p,p,p,p,p,p,p,p,p,p,p,p 4143t t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 4144t t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 4145x NULL 4146y y,y,y,y,y,y,y,y,y,y,y,y,y,y,y,y 4147z z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z 4148SELECT SUBSTR(a,1,1), LENGTH(a), GROUP_CONCAT(b), COUNT(*) FROM t1 GROUP BY a; 4149SUBSTR(a,1,1) LENGTH(a) GROUP_CONCAT(b) COUNT(*) 4150a 1000 a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a 16 4151c 258 c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 16 4152c 1000 c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 16 4153f 966 f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 16 4154f 1000 f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 16 4155k 468 k,k,k,k,k,k,k,k,k,k,k,k,k,k,k,k 16 4156o 648 o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o 16 4157p 1000 p,p,p,p,p,p,p,p,p,p,p,p,p,p,p,p 16 4158t 414 t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 16 4159t 932 t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 16 4160x 1 NULL 64 4161y 746 y,y,y,y,y,y,y,y,y,y,y,y,y,y,y,y 16 4162z 298 z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z 16 4163SET @@sort_buffer_size= @save_sort_buffer_size; 4164DROP TABLE t1; 4165CREATE TABLE t1(a VARCHAR(1027), b INT); 4166INSERT INTO t1 SELECT seq, seq from seq_1_to_34; 4167SET @save_tmp_memory_table_size= @@tmp_memory_table_size; 4168SET tmp_memory_table_size= 1056*2; 4169SELECT COUNT(DISTINCT a) FROM t1; 4170COUNT(DISTINCT a) 417134 4172SET @@tmp_memory_table_size= @save_tmp_memory_table_size; 4173DROP TABLE t1; 4174# 4175# MDEV-23216: LONGTEXT column with collation doesn't sort 4176# 4177CREATE TABLE t1 (a LONGTEXT COLLATE utf8mb4_swedish_ci); 4178INSERT INTO t1 VALUES ('A'),('Z'),('B'),('Y'); 4179SELECT * FROM t1 ORDER BY a; 4180a 4181A 4182B 4183Y 4184Z 4185SELECT * FROM t1 ORDER BY a DESC; 4186a 4187Z 4188Y 4189B 4190A 4191DROP TABLE t1; 4192# 4193# MDEV-23414 Assertion `res->charset() == item->collation.collation' failed in Type_handler_string_result::make_packed_sort_key_part 4194# 4195CREATE TABLE t1 (a CHAR(3), b BINARY(255)); 4196INSERT t1 VALUES ('foo','bar'),('baz','qux'); 4197SELECT COALESCE(a, b) AS f FROM t1 ORDER BY f; 4198f 4199baz 4200foo 4201DROP TABLE t1; 4202# 4203# MDEV-24015: SQL Error (1038): Out of sort memory when enough memory for the 4204# sort buffer is provided 4205# 4206CREATE TABLE t1 (a VARCHAR(100), b INT); 4207CREATE TABLE t2 (a VARCHAR(100), b INT); 4208INSERT INTO t1 SELECT 'abc', seq FROM seq_1_to_50; 4209INSERT INTO t2 SELECT seq, seq FROM seq_1_to_50; 4210set @save_sort_buffer_size= @@sort_buffer_size; 4211set sort_buffer_size=2000; 4212ANALYZE FORMAT=JSON 4213SELECT (SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) FROM t1; 4214ANALYZE 4215{ 4216 "query_block": { 4217 "select_id": 1, 4218 "r_loops": 1, 4219 "r_total_time_ms": "REPLACED", 4220 "table": { 4221 "table_name": "t1", 4222 "access_type": "ALL", 4223 "r_loops": 1, 4224 "rows": 50, 4225 "r_rows": 50, 4226 "r_table_time_ms": "REPLACED", 4227 "r_other_time_ms": "REPLACED", 4228 "filtered": 100, 4229 "r_filtered": 100 4230 }, 4231 "subqueries": [ 4232 { 4233 "expression_cache": { 4234 "r_loops": 50, 4235 "r_hit_ratio": 0, 4236 "query_block": { 4237 "select_id": 2, 4238 "r_loops": 50, 4239 "r_total_time_ms": "REPLACED", 4240 "filesort": { 4241 "sort_key": "t2.a", 4242 "r_loops": 50, 4243 "r_total_time_ms": "REPLACED", 4244 "r_used_priority_queue": false, 4245 "r_output_rows": 1, 4246 "r_buffer_size": "REPLACED" across executions)", 4247 "r_sort_mode": "sort_key,rowid", 4248 "temporary_table": { 4249 "table": { 4250 "table_name": "t2", 4251 "access_type": "ALL", 4252 "r_loops": 50, 4253 "rows": 50, 4254 "r_rows": 50, 4255 "r_table_time_ms": "REPLACED", 4256 "r_other_time_ms": "REPLACED", 4257 "filtered": 100, 4258 "r_filtered": 2, 4259 "attached_condition": "t1.b = t2.b" 4260 } 4261 } 4262 } 4263 } 4264 } 4265 } 4266 ] 4267 } 4268} 4269SELECT (SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) FROM t1; 4270(SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) 42711 42722 42733 42744 42755 42766 42777 42788 42799 428010 428111 428212 428313 428414 428515 428616 428717 428818 428919 429020 429121 429222 429323 429424 429525 429626 429727 429828 429929 430030 430131 430232 430333 430434 430535 430636 430737 430838 430939 431040 431141 431242 431343 431444 431545 431646 431747 431848 431949 432050 4321set sort_buffer_size= @save_sort_buffer_size; 4322DROP TABLE t1,t2; 4323# End of 10.5 tests 4324
