1drop table if exists t1,t2,t3; 2CREATE TABLE t1 ( 3id int(6) DEFAULT '0' NOT NULL, 4idservice int(5), 5clee char(20) NOT NULL, 6flag char(1), 7KEY id (id), 8PRIMARY KEY (clee) 9); 10INSERT INTO t1 VALUES (2,4,'6067169d','Y'); 11INSERT INTO t1 VALUES (2,5,'606716d1','Y'); 12INSERT INTO t1 VALUES (2,1,'606717c1','Y'); 13INSERT INTO t1 VALUES (3,1,'6067178d','Y'); 14INSERT INTO t1 VALUES (2,6,'60671515','Y'); 15INSERT INTO t1 VALUES (2,7,'60671569','Y'); 16INSERT INTO t1 VALUES (2,3,'dd','Y'); 17CREATE TABLE t2 ( 18id int(6) NOT NULL auto_increment, 19description varchar(40) NOT NULL, 20idform varchar(40), 21ordre int(6) unsigned DEFAULT '0' NOT NULL, 22image varchar(60), 23PRIMARY KEY (id), 24KEY id (id,ordre) 25); 26INSERT INTO t2 VALUES (1,'Emettre un appel d''offres','en_construction.html',10,'emettre.gif'); 27INSERT INTO t2 VALUES (2,'Emettre des soumissions','en_construction.html',20,'emettre.gif'); 28INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif'); 29INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif'); 30INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif'); 31INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif'); 32INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); 33INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif'); 34INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif'); 35INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif'); 36select t1.id,t1.idservice,t2.ordre,t2.description from t1, t2 where t1.id = 2 and t1.idservice = t2.id order by t2.ordre; 37id idservice ordre description 382 1 10 Emettre un appel d'offres 392 3 40000 Créer une fiche de client 402 4 40010 Modifier des clients 412 5 40020 Effacer des clients 422 6 51050 Ajouter un service 432 7 51060 Liste des t2 44drop table t1,t2; 45create table t1 (first char(10),last char(10)); 46insert into t1 values ("Michael","Widenius"); 47insert into t1 values ("Allan","Larsson"); 48insert into t1 values ("David","Axmark"); 49select concat(first," ",last) as name from t1 order by name; 50name 51Allan Larsson 52David Axmark 53Michael Widenius 54select concat(last," ",first) as name from t1 order by name; 55name 56Axmark David 57Larsson Allan 58Widenius Michael 59drop table t1; 60create table t1 (i int); 61insert into t1 values(1),(2),(1),(2),(1),(2),(3); 62select distinct i from t1; 63i 641 652 663 67select distinct i from t1 order by rand(5); 68i 691 703 712 72select distinct i from t1 order by i desc; 73i 743 752 761 77select distinct i from t1 order by 1-i; 78i 793 802 811 82select distinct i from t1 order by mod(i,2),i; 83i 842 851 863 87drop table t1; 88create table t1 ( pk int primary key, name varchar(255) not null, number varchar(255) not null); 89insert into t1 values (1, 'Gamma', '123'), (2, 'Gamma Ext', '123a'), (3, 'Alpha', '001'), (4, 'Beta', '200c'); 90select distinct t1.name as 'Building Name',t1.number as 'Building Number' from t1 order by t1.name asc; 91Building Name Building Number 92Alpha 001 93Beta 200c 94Gamma 123 95Gamma Ext 123a 96drop table t1; 97create table t1 (id int not null,col1 int not null,col2 int not null,index(col1)); 98insert 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); 99select * from t1 order by col1,col2; 100id col1 col2 1014 1 1 1023 1 2 1035 1 4 1042 2 1 1051 2 2 1066 2 3 1078 2 4 1087 3 1 109select col1 from t1 order by id; 110col1 1112 1122 1131 1141 1151 1162 1173 1182 119select col1 as id from t1 order by id; 120id 1211 1221 1231 1242 1252 1262 1272 1283 129select concat(col1) as id from t1 order by id; 130id 1311 1321 1331 1342 1352 1362 1372 1383 139drop table t1; 140CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta timestamp); 141insert into t1 (aika) values ('Keskiviikko'); 142insert into t1 (aika) values ('Tiistai'); 143insert into t1 (aika) values ('Maanantai'); 144insert into t1 (aika) values ('Sunnuntai'); 145SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test; 146test 1471 1482 1493 1507 151drop table t1; 152CREATE TABLE t1 153( 154a int unsigned NOT NULL, 155b int unsigned NOT NULL, 156c int unsigned NOT NULL, 157UNIQUE(a), 158INDEX(b), 159INDEX(c) 160); 161CREATE TABLE t2 162( 163c int unsigned NOT NULL, 164i int unsigned NOT NULL, 165INDEX(c) 166); 167CREATE TABLE t3 168( 169c int unsigned NOT NULL, 170v varchar(64), 171INDEX(c) 172); 173INSERT INTO t1 VALUES (1,1,1); 174INSERT INTO t1 VALUES (2,1,2); 175INSERT INTO t1 VALUES (3,2,1); 176INSERT INTO t1 VALUES (4,2,2); 177INSERT INTO t2 VALUES (1,50); 178INSERT INTO t2 VALUES (2,25); 179INSERT INTO t3 VALUES (1,'123 Park Place'); 180INSERT INTO t3 VALUES (2,'453 Boardwalk'); 181SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 182FROM t1 183LEFT JOIN t2 USING(c) 184LEFT JOIN t3 ON t3.c = t1.c; 185a b if(b = 1,i,if(b = 2,v,'')) 1861 1 50 1873 2 123 Park Place 1882 1 25 1894 2 453 Boardwalk 190SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 191FROM t1 192LEFT JOIN t2 ON t1.c = t2.c 193LEFT JOIN t3 ON t3.c = t1.c; 194a b if(b = 1,i,if(b = 2,v,'')) 1951 1 50 1963 2 123 Park Place 1972 1 25 1984 2 453 Boardwalk 199SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 200FROM t1 201LEFT JOIN t2 USING(c) 202LEFT JOIN t3 ON t3.c = t1.c 203ORDER BY a; 204a b if(b = 1,i,if(b = 2,v,'')) 2051 1 50 2062 1 25 2073 2 123 Park Place 2084 2 453 Boardwalk 209SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 210FROM t1 211LEFT JOIN t2 ON t1.c = t2.c 212LEFT JOIN t3 ON t3.c = t1.c 213ORDER BY a; 214a b if(b = 1,i,if(b = 2,v,'')) 2151 1 50 2162 1 25 2173 2 123 Park Place 2184 2 453 Boardwalk 219drop table t1,t2,t3; 220create table t1 (ID int not null primary key, TransactionID int not null); 221insert 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); 222create table t2 (ID int not null primary key, GroupID int not null); 223insert 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); 224create table t3 (ID int not null primary key, DateOfAction date not null); 225insert 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'); 226select 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; 227DateOfAction TransactionID 2281999-07-18 486 2291999-07-19 87 2301999-07-19 89 2311999-07-19 92 2321999-07-19 94 2331999-07-27 828 2341999-07-27 832 2351999-07-27 834 2361999-07-27 840 2372000-03-27 490 2382000-03-28 753 239select 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; 240DateOfAction TransactionID 2411999-07-19 87 2421999-07-19 89 2431999-07-19 92 2441999-07-19 94 2451999-07-18 486 2462000-03-27 490 2472000-03-28 753 2481999-07-27 828 2491999-07-27 832 2501999-07-27 834 2511999-07-27 840 252drop table t1,t2,t3; 253CREATE TABLE t1 ( 254member_id int(11) NOT NULL auto_increment, 255inschrijf_datum varchar(20) NOT NULL default '', 256lastchange_datum varchar(20) NOT NULL default '', 257nickname varchar(20) NOT NULL default '', 258password varchar(8) NOT NULL default '', 259voornaam varchar(30) NOT NULL default '', 260tussenvoegsels varchar(10) NOT NULL default '', 261achternaam varchar(50) NOT NULL default '', 262straat varchar(100) NOT NULL default '', 263postcode varchar(10) NOT NULL default '', 264wijk varchar(40) NOT NULL default '', 265plaats varchar(50) NOT NULL default '', 266telefoon varchar(10) NOT NULL default '', 267geboortedatum date NOT NULL default '0000-00-00', 268geslacht varchar(5) NOT NULL default '', 269email varchar(80) NOT NULL default '', 270uin varchar(15) NOT NULL default '', 271homepage varchar(100) NOT NULL default '', 272internet varchar(15) NOT NULL default '', 273scherk varchar(30) NOT NULL default '', 274favo_boek varchar(50) NOT NULL default '', 275favo_tijdschrift varchar(50) NOT NULL default '', 276favo_tv varchar(50) NOT NULL default '', 277favo_eten varchar(50) NOT NULL default '', 278favo_muziek varchar(30) NOT NULL default '', 279info text NOT NULL default '', 280ipnr varchar(30) NOT NULL default '', 281PRIMARY KEY (member_id) 282) ENGINE=MyISAM PACK_KEYS=1; 283Warnings: 284Warning 1101 BLOB/TEXT column 'info' can't have a default value 285insert into t1 (member_id) values (1),(2),(3); 286select member_id, nickname, voornaam FROM t1 287ORDER by lastchange_datum DESC LIMIT 2; 288member_id nickname voornaam 2891 2902 291drop table t1; 292create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); 293insert 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'); 294explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; 295id select_type table type possible_keys key key_len ref rows Extra 2961 SIMPLE t1 index a a 22 NULL 11 Using where; Using index 297select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; 298a b c 2991 NULL b 300explain select * from t1 where a >= 1 and a < 3 order by a desc; 301id select_type table type possible_keys key key_len ref rows Extra 3021 SIMPLE t1 range a a 4 NULL 10 Using where; Using index 303select * from t1 where a >= 1 and a < 3 order by a desc; 304a b c 3052 3 c 3062 2 b 3072 2 a 3082 1 b 3092 1 a 3101 3 b 3111 1 b 3121 1 b 3131 1 NULL 3141 NULL b 3151 NULL NULL 316explain select * from t1 where a = 1 order by a desc, b desc; 317id select_type table type possible_keys key key_len ref rows Extra 3181 SIMPLE t1 ref a a 4 const 5 Using where; Using index 319select * from t1 where a = 1 order by a desc, b desc; 320a b c 3211 3 b 3221 1 b 3231 1 b 3241 1 NULL 3251 NULL b 3261 NULL NULL 327explain select * from t1 where a = 1 and b is null order by a desc, b desc; 328id select_type table type possible_keys key key_len ref rows Extra 3291 SIMPLE t1 ref a a 9 const,const 2 Using where; Using index; Using filesort 330select * from t1 where a = 1 and b is null order by a desc, b desc; 331a b c 3321 NULL NULL 3331 NULL b 334explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc; 335id select_type table type possible_keys key key_len ref rows Extra 3361 SIMPLE t1 range a a 9 NULL 8 Using where; Using index 337explain select * from t1 where a = 2 and b >0 order by a desc,b desc; 338id select_type table type possible_keys key key_len ref rows Extra 3391 SIMPLE t1 range a a 9 NULL 5 Using where; Using index 340explain select * from t1 where a = 2 and b is null order by a desc,b desc; 341id select_type table type possible_keys key key_len ref rows Extra 3421 SIMPLE t1 ref a a 9 const,const 1 Using where; Using index; Using filesort 343explain select * from t1 where a = 2 and (b is null or b > 0) order by a 344desc,b desc; 345id select_type table type possible_keys key key_len ref rows Extra 3461 SIMPLE t1 range a a 9 NULL 6 Using where; Using index 347explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; 348id select_type table type possible_keys key key_len ref rows Extra 3491 SIMPLE t1 range a a 9 NULL 5 Using where; Using index 350explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; 351id select_type table type possible_keys key key_len ref rows Extra 3521 SIMPLE t1 range a a 9 NULL 2 Using where; Using index 353explain select * from t1 where a = 1 order by b desc; 354id select_type table type possible_keys key key_len ref rows Extra 3551 SIMPLE t1 ref a a 4 const 5 Using where; Using index 356select * from t1 where a = 1 order by b desc; 357a b c 3581 3 b 3591 1 b 3601 1 b 3611 1 NULL 3621 NULL b 3631 NULL NULL 364alter table t1 modify b int not null, modify c varchar(10) not null; 365Warnings: 366Warning 1265 Data truncated for column 'b' at row 1 367Warning 1265 Data truncated for column 'c' at row 1 368Warning 1265 Data truncated for column 'b' at row 2 369Warning 1265 Data truncated for column 'c' at row 3 370explain select * from t1 order by a, b, c; 371id select_type table type possible_keys key key_len ref rows Extra 3721 SIMPLE t1 index NULL a 20 NULL 11 Using index 373select * from t1 order by a, b, c; 374a b c 3751 0 3761 0 b 3771 1 3781 1 b 3791 1 b 3801 3 b 3812 1 a 3822 1 b 3832 2 a 3842 2 b 3852 3 c 386explain select * from t1 order by a desc, b desc, c desc; 387id select_type table type possible_keys key key_len ref rows Extra 3881 SIMPLE t1 index NULL a 20 NULL 11 Using index 389select * from t1 order by a desc, b desc, c desc; 390a b c 3912 3 c 3922 2 b 3932 2 a 3942 1 b 3952 1 a 3961 3 b 3971 1 b 3981 1 b 3991 1 4001 0 b 4011 0 402explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; 403id select_type table type possible_keys key key_len ref rows Extra 4041 SIMPLE t1 range a a 20 NULL 3 Using where; Using index 405select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; 406a b c 4071 1 b 4081 1 b 409explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc; 410id select_type table type possible_keys key key_len ref rows Extra 4111 SIMPLE t1 range a a 4 NULL 6 Using where; Using index 412select * from t1 where a < 2 and b <= 1 order by a desc, b desc; 413a b c 4141 1 b 4151 1 b 4161 1 4171 0 b 4181 0 419select count(*) from t1 where a < 5 and b > 0; 420count(*) 4219 422select * from t1 where a < 5 and b > 0 order by a desc,b desc; 423a b c 4242 3 c 4252 2 b 4262 2 a 4272 1 b 4282 1 a 4291 3 b 4301 1 b 4311 1 b 4321 1 433explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; 434id select_type table type possible_keys key key_len ref rows Extra 4351 SIMPLE t1 range a a 8 NULL 10 Using where; Using index 436select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; 437a b c 4382 1 b 4392 1 a 4401 1 b 4411 1 b 4421 1 4431 0 b 4441 0 445explain select * from t1 where a between 0 and 1 order by a desc, b desc; 446id select_type table type possible_keys key key_len ref rows Extra 4471 SIMPLE t1 range a a 4 NULL 5 Using where; Using index 448select * from t1 where a between 0 and 1 order by a desc, b desc; 449a b c 4501 3 b 4511 1 b 4521 1 b 4531 1 4541 0 b 4551 0 456drop table t1; 457CREATE TABLE t1 ( 458gid int(10) unsigned NOT NULL auto_increment, 459cid smallint(5) unsigned NOT NULL default '0', 460PRIMARY KEY (gid), 461KEY component_id (cid) 462) ENGINE=MyISAM; 463INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108); 464ALTER TABLE t1 add skr int(10) not null; 465CREATE TABLE t2 ( 466gid int(10) unsigned NOT NULL default '0', 467uid smallint(5) unsigned NOT NULL default '1', 468sid tinyint(3) unsigned NOT NULL default '1', 469PRIMARY KEY (gid), 470KEY uid (uid), 471KEY status_id (sid) 472) ENGINE=MyISAM; 473INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5); 474CREATE TABLE t3 ( 475uid smallint(6) NOT NULL auto_increment, 476PRIMARY KEY (uid) 477) ENGINE=MyISAM; 478INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250); 479ALTER TABLE t3 add skr int(10) not null; 480select 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; 481gid sid uid 482104620 5 15 483103867 5 27 484103962 5 27 485104619 5 75 486104505 5 117 487103853 5 250 488select 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; 489gid sid uid 490104620 5 15 491103867 5 27 492103962 5 27 493104619 5 75 494104505 5 117 495103853 5 250 496EXPLAIN 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; 497id select_type table type possible_keys key key_len ref rows Extra 4981 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort 4991 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index 5001 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index 501EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr; 502id select_type table type possible_keys key key_len ref rows Extra 5031 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort 5041 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index 505EXPLAIN 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; 506id select_type table type possible_keys key key_len ref rows Extra 5071 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort 5081 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index 5091 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index 510EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid; 511id select_type table type possible_keys key key_len ref rows Extra 5121 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort 5131 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index 514EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; 515id select_type table type possible_keys key key_len ref rows Extra 5161 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 5171 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using where 518drop table t1,t2,t3; 519CREATE TABLE t1 ( 520`titre` char(80) NOT NULL default '', 521`numeropost` mediumint(8) unsigned NOT NULL auto_increment, 522`date` datetime NOT NULL default '0000-00-00 00:00:00', 523`auteur` char(35) NOT NULL default '', 524`icone` tinyint(2) unsigned NOT NULL default '0', 525`lastauteur` char(35) NOT NULL default '', 526`nbrep` smallint(6) unsigned NOT NULL default '0', 527`dest` char(35) NOT NULL default '', 528`lu` tinyint(1) unsigned NOT NULL default '0', 529`vue` mediumint(8) unsigned NOT NULL default '0', 530`ludest` tinyint(1) unsigned NOT NULL default '0', 531`ouvert` tinyint(1) unsigned NOT NULL default '1', 532PRIMARY KEY (`numeropost`), 533KEY `date` (`date`), 534KEY `dest` (`dest`,`ludest`), 535KEY `auteur` (`auteur`,`lu`), 536KEY `auteur_2` (`auteur`,`date`), 537KEY `dest_2` (`dest`,`date`) 538) CHECKSUM=1; 539CREATE TABLE t2 ( 540`numeropost` mediumint(8) unsigned NOT NULL default '0', 541`pseudo` char(35) NOT NULL default '', 542PRIMARY KEY (`numeropost`,`pseudo`), 543KEY `pseudo` (`pseudo`) 544); 545INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug'); 546INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug'); 547SELECT 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; 548titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest 549test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug 550SELECT 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; 551titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest 552test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug 553SELECT 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; 554titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest 555test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug 556SELECT 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; 557titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest 558test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug 559drop table t1,t2; 560CREATE TABLE t1 (a int, b int); 561INSERT INTO t1 VALUES (1, 2); 562INSERT INTO t1 VALUES (3, 4); 563INSERT INTO t1 VALUES (5, NULL); 564SELECT * FROM t1 ORDER BY b; 565a b 5665 NULL 5671 2 5683 4 569SELECT * FROM t1 ORDER BY b DESC; 570a b 5713 4 5721 2 5735 NULL 574SELECT * FROM t1 ORDER BY (a + b); 575a b 5765 NULL 5771 2 5783 4 579SELECT * FROM t1 ORDER BY (a + b) DESC; 580a b 5813 4 5821 2 5835 NULL 584DROP TABLE t1; 585create table t1(id int not null auto_increment primary key, t char(12)); 586explain select id,t from t1 order by id; 587id select_type table type possible_keys key key_len ref rows Extra 5881 SIMPLE t1 ALL NULL NULL NULL NULL 1000 Using filesort 589explain select id,t from t1 force index (primary) order by id; 590id select_type table type possible_keys key key_len ref rows Extra 5911 SIMPLE t1 index NULL PRIMARY 4 NULL 1000 NULL 592drop table t1; 593CREATE TABLE t1 ( 594FieldKey varchar(36) NOT NULL default '', 595LongVal bigint(20) default NULL, 596StringVal mediumtext, 597KEY FieldKey (FieldKey), 598KEY LongField (FieldKey,LongVal), 599KEY StringField (FieldKey,StringVal(32)) 600); 601INSERT 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'); 602EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; 603id select_type table type possible_keys key key_len ref rows Extra 6041 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using where 605SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; 606FieldKey LongVal StringVal 6071 0 2 6081 1 3 6091 2 1 610EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; 611id select_type table type possible_keys key key_len ref rows Extra 6121 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort 613SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; 614FieldKey LongVal StringVal 6153 1 2 6163 2 1 6173 3 3 618EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; 619id select_type table type possible_keys key key_len ref rows Extra 6201 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 4 Using where 621SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; 622FieldKey LongVal StringVal 6233 1 2 6243 2 1 6253 3 3 626DROP TABLE t1; 627CREATE TABLE t1 (a INT, b INT); 628SET @id=0; 629UPDATE t1 SET a=0 ORDER BY (a=@id), b; 630DROP TABLE t1; 631CREATE 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; 632INSERT INTO t1 VALUES (11384, 2),(11392, 2); 633SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ; 634id 63511392 636drop table t1; 637create table t1(a int, b int, index(b)); 638insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); 639explain select * from t1 where b=1 or b is null order by a; 640id select_type table type possible_keys key key_len ref rows Extra 6411 SIMPLE t1 ref_or_null b b 5 const 3 Using where; Using filesort 642select * from t1 where b=1 or b is null order by a; 643a b 6441 1 6452 1 6463 NULL 6474 NULL 648explain select * from t1 where b=2 or b is null order by a; 649id select_type table type possible_keys key key_len ref rows Extra 6501 SIMPLE t1 ref_or_null b b 5 const 4 Using where; Using filesort 651select * from t1 where b=2 or b is null order by a; 652a b 6533 NULL 6544 NULL 6555 2 6566 2 657drop table t1; 658create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null, 659key(a,b,d), key(c,b,a)); 660create table t2 like t1; 661insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3); 662insert into t2 select null, b, c, d from t1; 663insert into t1 select null, b, c, d from t2; 664insert into t2 select null, b, c, d from t1; 665insert into t1 select null, b, c, d from t2; 666insert into t2 select null, b, c, d from t1; 667insert into t1 select null, b, c, d from t2; 668insert into t2 select null, b, c, d from t1; 669insert into t1 select null, b, c, d from t2; 670insert into t2 select null, b, c, d from t1; 671insert into t1 select null, b, c, d from t2; 672optimize table t1; 673Table Op Msg_type Msg_text 674test.t1 optimize status OK 675set @row=10; 676insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10; 677select * from t1 where a=1 and b in (1) order by c, b, a; 678a b c d 6791 1 2 0 6801 1 12 -1 6811 1 52 -5 6821 1 92 -9 683select * from t1 where a=1 and b in (1); 684a b c d 6851 1 92 -9 6861 1 52 -5 6871 1 12 -1 6881 1 2 0 689drop table t1, t2; 690create table t1 (col1 int, col int); 691create table t2 (col2 int, col int); 692insert into t1 values (1,1),(2,2),(3,3); 693insert into t2 values (1,3),(2,2),(3,1); 694select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2) 695order by col; 696col1 col t2_col 6971 1 3 6982 2 2 6993 3 1 700select col1 as col, col from t1 order by col; 701ERROR 23000: Column 'col' in order clause is ambiguous 702select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 703order by col; 704ERROR 23000: Column 'col' in order clause is ambiguous 705select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 706order by col; 707ERROR 23000: Column 'col' in order clause is ambiguous 708select col1 from t1, t2 where t1.col1=t2.col2 order by col; 709ERROR 23000: Column 'col' in order clause is ambiguous 710select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2 711order by col; 712ERROR 23000: Column 'col' in order clause is ambiguous 713select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2 714order by col; 715t1_col col 7163 1 7172 2 7181 3 719select col2 as c, col as c from t2 order by col; 720c c 7213 1 7222 2 7231 3 724select col2 as col, col as col2 from t2 order by col; 725col col2 7261 3 7272 2 7283 1 729select t2.col2, t2.col, t2.col from t2 order by col; 730col2 col col 7313 1 1 7322 2 2 7331 3 3 734select t2.col2 as col from t2 order by t2.col; 735col 7363 7372 7381 739select t2.col2 as col, t2.col from t2 order by t2.col; 740col col 7413 1 7422 2 7431 3 744select t2.col2, t2.col, t2.col from t2 order by t2.col; 745col2 col col 7463 1 1 7472 2 2 7481 3 3 749drop table t1, t2; 750create table t1 (a char(25)); 751insert into t1 set a = repeat('x', 20); 752insert into t1 set a = concat(repeat('x', 19), 'z'); 753insert into t1 set a = concat(repeat('x', 19), 'ab'); 754insert into t1 set a = concat(repeat('x', 19), 'aa'); 755set max_sort_length=20; 756select a from t1 order by a; 757a 758xxxxxxxxxxxxxxxxxxxab 759xxxxxxxxxxxxxxxxxxxaa 760xxxxxxxxxxxxxxxxxxxx 761xxxxxxxxxxxxxxxxxxxz 762drop table t1; 763create table t1 ( 764`sid` decimal(8,0) default null, 765`wnid` varchar(11) not null default '', 766key `wnid14` (`wnid`(4)), 767key `wnid` (`wnid`) 768) engine=myisam default charset=latin1; 769insert into t1 (`sid`, `wnid`) values 770('10100','01019000000'),('37986','01019000000'),('37987','01019010000'), 771('39560','01019090000'),('37989','01019000000'),('37990','01019011000'), 772('37991','01019011000'),('37992','01019019000'),('37993','01019030000'), 773('37994','01019090000'),('475','02070000000'),('25253','02071100000'), 774('25255','02071100000'),('25256','02071110000'),('25258','02071130000'), 775('25259','02071190000'),('25260','02071200000'),('25261','02071210000'), 776('25262','02071290000'),('25263','02071300000'),('25264','02071310000'), 777('25265','02071310000'),('25266','02071320000'),('25267','02071320000'), 778('25269','02071330000'),('25270','02071340000'),('25271','02071350000'), 779('25272','02071360000'),('25273','02071370000'),('25281','02071391000'), 780('25282','02071391000'),('25283','02071399000'),('25284','02071400000'), 781('25285','02071410000'),('25286','02071410000'),('25287','02071420000'), 782('25288','02071420000'),('25291','02071430000'),('25290','02071440000'), 783('25292','02071450000'),('25293','02071460000'),('25294','02071470000'), 784('25295','02071491000'),('25296','02071491000'),('25297','02071499000'); 785explain select * from t1 where wnid like '0101%' order by wnid; 786id select_type table type possible_keys key key_len ref rows Extra 7871 SIMPLE t1 range wnid14,wnid wnid 13 NULL 10 Using where 788select * from t1 where wnid like '0101%' order by wnid; 789sid wnid 79010100 01019000000 79137986 01019000000 79237989 01019000000 79337987 01019010000 79437990 01019011000 79537991 01019011000 79637992 01019019000 79737993 01019030000 79839560 01019090000 79937994 01019090000 800drop table t1; 801CREATE TABLE t1 (a int); 802INSERT INTO t1 VALUES (2), (1), (1), (2), (1); 803SELECT a FROM t1 ORDER BY a; 804a 8051 8061 8071 8082 8092 810(SELECT a FROM t1) ORDER BY a; 811a 8121 8131 8141 8152 8162 817DROP TABLE t1; 818CREATE TABLE t1 (a int, b int); 819INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10); 820(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a; 821b a 82210 1 82310 2 82420 1 82520 2 82630 1 82730 2 828(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC; 829b 83010 83110 83220 83320 83430 83530 836(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b; 837b a 83810 1 83920 1 84030 1 84110 2 84220 2 84330 2 844(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b; 845b a 84610 1 84720 1 84810 2 849DROP TABLE t1; 850CREATE TABLE t1 (a INT); 851INSERT INTO t1 VALUES (1),(2); 852SELECT a + 1 AS num FROM t1 ORDER BY 30 - num; 853num 8543 8552 856SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str); 857str 858test1 859test2 860SELECT a + 1 AS num FROM t1 GROUP BY 30 - num; 861num 8623 8632 864SELECT a + 1 AS num FROM t1 HAVING 30 - num; 865num 8662 8673 868SELECT a + 1 AS num, num + 1 FROM t1; 869ERROR 42S22: Unknown column 'num' in 'field list' 870SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; 871num (select num + 2 FROM t1 LIMIT 1) 8722 4 8733 5 874SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; 875ERROR 42S22: Unknown column 'num' in 'on clause' 876DROP TABLE t1; 877CREATE TABLE bug25126 ( 878val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY 879); 880UPDATE bug25126 SET MissingCol = MissingCol; 881ERROR 42S22: Unknown column 'MissingCol' in 'field list' 882UPDATE bug25126 SET val = val ORDER BY MissingCol; 883ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 884UPDATE bug25126 SET val = val ORDER BY val; 885UPDATE bug25126 SET val = 1 ORDER BY val; 886UPDATE bug25126 SET val = 1 ORDER BY MissingCol; 887ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 888UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol; 889ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 890UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol; 891ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 892UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol; 893ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 894UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol; 895ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 896UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol; 897ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 898UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol; 899ERROR 42S22: Unknown column 'MissingCol' in 'order clause' 900DROP TABLE bug25126; 901CREATE TABLE t1 (a int); 902SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1; 903val val1 904SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; 905ERROR 23000: Column 'val' in order clause is ambiguous 906SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; 907ERROR 23000: Column 'val' in order clause is ambiguous 908DROP TABLE t1; 909CREATE TABLE t1 (a int); 910INSERT INTO t1 VALUES (3), (2), (4), (1); 911SELECT a, IF(a IN (2,3), a, a+10) FROM t1 912ORDER BY IF(a IN (2,3), a, a+10); 913a IF(a IN (2,3), a, a+10) 9142 2 9153 3 9161 11 9174 14 918SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1 919ORDER BY IF(a NOT IN (2,3), a, a+10); 920a IF(a NOT IN (2,3), a, a+10) 9211 1 9224 4 9232 12 9243 13 925SELECT a, IF(a IN (2,3), a, a+10) FROM t1 926ORDER BY IF(a NOT IN (2,3), a, a+10); 927a IF(a IN (2,3), a, a+10) 9281 11 9294 14 9302 2 9313 3 932SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 933ORDER BY IF(a BETWEEN 2 AND 3, a, a+10); 934a IF(a BETWEEN 2 AND 3, a, a+10) 9352 2 9363 3 9371 11 9384 14 939SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1 940ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); 941a IF(a NOT BETWEEN 2 AND 3, a, a+10) 9421 1 9434 4 9442 12 9453 13 946SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 947ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); 948a IF(a BETWEEN 2 AND 3, a, a+10) 9491 11 9504 14 9512 2 9523 3 953SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 954FROM t1 GROUP BY x1, x2; 955x1 x2 956 3 957 4 9581 9592 960SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 961FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, ''); 962x1 x2 963 3 964 4 9651 9662 967SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2); 968a a IN (1,2) 9693 0 9704 0 9712 1 9721 1 973SELECT a FROM t1 ORDER BY a IN (1,2); 974a 9753 9764 9772 9781 979SELECT a+10 FROM t1 ORDER BY a IN (1,2); 980a+10 98113 98214 98312 98411 985SELECT a, IF(a IN (1,2), a, a+10) FROM t1 986ORDER BY IF(a IN (3,4), a, a+10); 987a IF(a IN (1,2), a, a+10) 9883 13 9894 14 9901 1 9912 2 992DROP TABLE t1; 993create table t1 (a int not null, b int not null, c int not null); 994insert t1 values (1,1,1),(1,1,2),(1,2,1); 995select a, b from t1 group by a, b order by sum(c); 996a b 9971 2 9981 1 999drop table t1; 1000CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); 1001INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 1002explain SELECT t1.b as a, t2.b as c FROM 1003t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 1004ORDER BY c; 1005id select_type table type possible_keys key key_len ref rows Extra 10061 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort 10071 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where 1008SELECT t2.b as c FROM 1009t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 1010ORDER BY c; 1011c 1012NULL 1013NULL 10142 1015explain SELECT t1.b as a, t2.b as c FROM 1016t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 1017ORDER BY c; 1018id select_type table type possible_keys key key_len ref rows Extra 10191 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 10201 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 NULL 1021CREATE TABLE t2 LIKE t1; 1022INSERT INTO t2 SELECT * from t1; 1023CREATE TABLE t3 LIKE t1; 1024INSERT INTO t3 SELECT * from t1; 1025CREATE TABLE t4 LIKE t1; 1026INSERT INTO t4 SELECT * from t1; 1027INSERT INTO t1 values (0,0),(4,4); 1028SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a) 1029ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b; 1030b 1031NULL 1032NULL 10331 10342 10353 1036DROP TABLE t1,t2,t3,t4; 1037create table t1 (a int, b int, c int); 1038insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9); 1039select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc; 1040a ratio 10411 0.5000 104219 1.3333 10439 2.6667 1044drop table t1; 1045CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME); 1046INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10); 1047UPDATE t1 SET b = SEC_TO_TIME(a); 1048SELECT a, b FROM t1 ORDER BY b DESC; 1049a b 10501000000 277:46:40 1051100000 27:46:40 105210000 02:46:40 10531000 00:16:40 1054100 00:01:40 105510 00:00:10 10560 00:00:00 1057SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC; 1058a b 10591000000 277:46:40 1060100000 27:46:40 106110000 02:46:40 10621000 00:16:40 1063100 00:01:40 106410 00:00:10 10650 00:00:00 1066DROP TABLE t1; 1067CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b)); 1068INSERT INTO t1 VALUES (1,1),(2,2); 1069CREATE TABLE t2 (a INT, b INT, KEY a (a,b)); 1070INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); 1071EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; 1072id select_type table type possible_keys key key_len ref rows Extra 10731 SIMPLE t1 const PRIMARY,b b 5 const 1 NULL 10741 SIMPLE t2 ref a a 5 const 2 Using where; Using index 1075DROP TABLE t1,t2; 1076CREATE TABLE t1( 1077id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3)); 1078INSERT INTO t1 (c2,c3) VALUES 1079(31,34),(35,38),(34,31),(32,35),(31,39), 1080(11,14),(15,18),(14,11),(12,15),(11,19); 1081INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1082INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1083INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1084INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1085INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1086INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1087INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1088INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1089INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1090INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1091INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1092INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 1093UPDATE t1 SET c2=20 WHERE id%100 = 0; 1094SELECT COUNT(*) FROM t1; 1095COUNT(*) 109640960 1097CREATE TABLE t2 LIKE t1; 1098INSERT INTO t2 SELECT * FROM t1 ORDER BY id; 1099EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; 1100id select_type table type possible_keys key key_len ref rows Extra 11011 SIMPLE t2 index k2 k3 5 NULL 111 Using where 1102EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; 1103id select_type table type possible_keys key key_len ref rows Extra 11041 SIMPLE t2 ref k2 k2 5 const 7341 Using where; Using filesort 1105EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; 1106id select_type table type possible_keys key key_len ref rows Extra 11071 SIMPLE t2 index k2 k3 5 NULL 73 Using where 1108EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; 1109id select_type table type possible_keys key key_len ref rows Extra 11101 SIMPLE t2 range k2 k2 5 NULL 386 Using where; Using filesort 1111SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; 1112id c3 11136 14 111416 14 111526 14 111636 14 111746 14 111856 14 111966 14 112076 14 112186 14 112296 14 1123106 14 1124116 14 1125126 14 1126136 14 1127146 14 1128156 14 1129166 14 1130176 14 1131186 14 1132196 14 1133DROP TABLE t1,t2; 1134CREATE TABLE t1 ( 1135a INT, 1136b INT, 1137PRIMARY KEY (a), 1138KEY ab(a, b) 1139); 1140INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); 1141INSERT INTO t1 SELECT a + 4, b + 4 FROM t1; 1142INSERT INTO t1 SELECT a + 8, b + 8 FROM t1; 1143INSERT INTO t1 SELECT a +16, b +16 FROM t1; 1144INSERT INTO t1 SELECT a +32, b +32 FROM t1; 1145INSERT INTO t1 SELECT a +64, b +64 FROM t1; 1146EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; 1147id select_type table type possible_keys key key_len ref rows Extra 11481 SIMPLE t1 range PRIMARY,ab ab 4 NULL 10 Using index for group-by 1149SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; 1150a 11511 11522 11533 11544 11555 11566 11577 11588 11599 116010 116111 116212 116313 116414 116515 116616 116717 116818 116919 117020 117121 117222 117323 117424 117525 117626 117727 117828 117929 118030 118131 118232 118333 118434 118535 118636 118737 118838 118939 119040 119141 119242 119343 119444 119545 119646 119747 119848 119949 120050 120151 120252 120353 120454 120555 120656 120757 120858 120959 121060 121161 121262 121363 121464 121565 121666 121767 121868 121969 122070 122171 122272 122373 122474 122575 122676 122777 122878 122979 123080 123181 123282 123383 123484 123585 123686 123787 123888 123989 124090 124191 124292 124393 124494 124595 124696 124797 124898 124999 1250100 1251101 1252102 1253103 1254104 1255105 1256106 1257107 1258108 1259109 1260110 1261111 1262112 1263113 1264114 1265115 1266116 1267117 1268118 1269119 1270120 1271121 1272122 1273123 1274124 1275125 1276126 1277127 1278128 1279SELECT @tmp_tables_after = @tmp_tables_before ; 1280@tmp_tables_after = @tmp_tables_before 12811 1282EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; 1283id select_type table type possible_keys key key_len ref rows Extra 12841 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index 1285SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; 1286a 12871 12882 12893 12904 12915 12926 12937 12948 12959 129610 129711 129812 129913 130014 130115 130216 130317 130418 130519 130620 130721 130822 130923 131024 131125 131226 131327 131428 131529 131630 131731 131832 131933 132034 132135 132236 132337 132438 132539 132640 132741 132842 132943 133044 133145 133246 133347 133448 133549 133650 133751 133852 133953 134054 134155 134256 134357 134458 134559 134660 134761 134862 134963 135064 135165 135266 135367 135468 135569 135670 135771 135872 135973 136074 136175 136276 136377 136478 136579 136680 136781 136882 136983 137084 137185 137286 137387 137488 137589 137690 137791 137892 137993 138094 138195 138296 138397 138498 138599 1386100 1387101 1388102 1389103 1390104 1391105 1392106 1393107 1394108 1395109 1396110 1397111 1398112 1399113 1400114 1401115 1402116 1403117 1404118 1405119 1406120 1407121 1408122 1409123 1410124 1411125 1412126 1413127 1414128 1415SELECT @tmp_tables_after = @tmp_tables_before; 1416@tmp_tables_after = @tmp_tables_before 14171 1418DROP TABLE t1; 1419# 1420# Bug#31590: Wrong error message on sort buffer being too small. 1421# 1422create table t1(a int, b tinytext); 1423insert into t1 values (1,2),(3,2); 1424set session sort_buffer_size= 30000; 1425Warnings: 1426Warning 1292 Truncated incorrect sort_buffer_size value: '30000' 1427set session max_sort_length= 2180; 1428CALL mtr.add_suppression("Out of sort memory"); 1429select * from t1 order by b; 1430ERROR HY001: Out of sort memory, consider increasing server sort buffer size 1431drop table t1; 1432call mtr.add_suppression("Out of sort memory; increase server sort buffer size"); 1433# 1434# Bug #39844: Query Crash Mysql Server 5.0.67 1435# 1436CREATE TABLE t1 (a INT PRIMARY KEY); 1437CREATE TABLE t2 (a INT PRIMARY KEY, b INT); 1438CREATE TABLE t3 (c INT); 1439INSERT INTO t1 (a) VALUES (1), (2); 1440INSERT INTO t2 (a,b) VALUES (1,2), (2,3); 1441INSERT INTO t3 (c) VALUES (1), (2); 1442SELECT 1443(SELECT t1.a FROM t1, t2 WHERE t1.a = t2.b AND t2.a = t3.c ORDER BY t1.a) 1444FROM t3; 1445(SELECT t1.a FROM t1, t2 WHERE t1.a = t2.b AND t2.a = t3.c ORDER BY t1.a) 14462 1447NULL 1448DROP TABLE t1, t2, t3; 1449# 1450# Bug #42760: Select doesn't return desired results when we have null 1451# values 1452# 1453CREATE TABLE t1 ( 1454a INT, 1455c INT, 1456UNIQUE KEY a_c (a,c), 1457KEY (a)); 1458INSERT INTO t1 VALUES (1, 10), (2, NULL); 1459# Must use ref-or-null on the a_c index 1460EXPLAIN 1461SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 1462id select_type table type possible_keys key key_len ref rows Extra 14631 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort 1464# Must return 1 row 1465SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 1466col 14671 1468# Must use ref-or-null on the a_c index 1469EXPLAIN 1470SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 1471id select_type table type possible_keys key key_len ref rows Extra 1472x x x ref a_c,a x x x x x 1473# Must return 1 row 1474SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 1475col 14761 1477DROP TABLE t1; 1478End of 5.0 tests 1479CREATE TABLE t2 (a varchar(32), b int(11), c float, d double, 1480UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c)); 1481CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b)); 1482CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b)); 1483INSERT INTO t3 SELECT * FROM t1; 1484EXPLAIN 1485SELECT d FROM t1, t2 1486WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1487ORDER BY t2.c LIMIT 1; 1488id select_type table type possible_keys key key_len ref rows Extra 14891 SIMPLE t1 ref a,b b 4 const 4 Using where; Using temporary; Using filesort 14901 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using where 1491SELECT d FROM t1, t2 1492WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1493ORDER BY t2.c LIMIT 1; 1494d 149552.5 1496EXPLAIN 1497SELECT d FROM t3 AS t1, t2 AS t2 1498WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1499ORDER BY t2.c LIMIT 1; 1500id select_type table type possible_keys key key_len ref rows Extra 15011 SIMPLE t2 range a,b,c c 5 NULL 420 Using where 15021 SIMPLE t1 ref a a 39 test.t2.a,const 10 Using where; Using index 1503SELECT d FROM t3 AS t1, t2 AS t2 1504WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1505ORDER BY t2.c LIMIT 1; 1506d 150752.5 1508DROP TABLE t1,t2,t3; 1509# 1510# WL#1393 - Optimizing filesort with small limit 1511# 1512CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200)); 1513INSERT INTO t1(f1, f2) VALUES 1514(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), 1515(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), 1516(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), 1517(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), 1518(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), 1519(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), 1520(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), 1521(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), 1522(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), 1523(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), 1524(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), 1525(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), 1526(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), 1527(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), 1528(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), 1529(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), 1530(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), 1531(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), 1532(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), 1533(96,"96"),(97,"97"),(98,"98"),(99,"99"); 1534SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100; 1535f0 f1 f2 15361 0 0 15372 1 1 15383 2 2 15394 3 3 15405 4 4 15416 5 5 15427 6 6 15438 7 7 15449 8 8 154510 9 9 154611 10 10 154712 11 11 154813 12 12 154914 13 13 155015 14 14 155116 15 15 155217 16 16 155318 17 17 155419 18 18 155520 19 19 155621 20 20 155722 21 21 155823 22 22 155924 23 23 156025 24 24 156126 25 25 156227 26 26 156328 27 27 156429 28 28 156530 29 29 156631 30 30 156732 31 31 156833 32 32 156934 33 33 157035 34 34 157136 35 35 157237 36 36 157338 37 37 157439 38 38 157540 39 39 157641 40 40 157742 41 41 157843 42 42 157944 43 43 158045 44 44 158146 45 45 158247 46 46 158348 47 47 158449 48 48 158550 49 49 158651 50 50 158752 51 51 158853 52 52 158954 53 53 159055 54 54 159156 55 55 159257 56 56 159358 57 57 159459 58 58 159560 59 59 159661 60 60 159762 61 61 159863 62 62 159964 63 63 160065 64 64 160166 65 65 160267 66 66 160368 67 67 160469 68 68 160570 69 69 160671 70 70 160772 71 71 160873 72 72 160974 73 73 161075 74 74 161176 75 75 161277 76 76 161378 77 77 161479 78 78 161580 79 79 161681 80 80 161782 81 81 161883 82 82 161984 83 83 162085 84 84 162186 85 85 162287 86 86 162388 87 87 162489 88 88 162590 89 89 162691 90 90 162792 91 91 162893 92 92 162994 93 93 163095 94 94 163196 95 95 163297 96 96 163398 97 97 163499 98 98 1635100 99 99 1636SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; 1637f0 f1 f2 16381 0 0 16392 1 1 16403 2 2 16414 3 3 16425 4 4 16436 5 5 16447 6 6 16458 7 7 16469 8 8 164710 9 9 164811 10 10 164912 11 11 165013 12 12 165114 13 13 165215 14 14 165316 15 15 165417 16 16 165518 17 17 165619 18 18 165720 19 19 165821 20 20 165922 21 21 166023 22 22 166124 23 23 166225 24 24 166326 25 25 166427 26 26 166528 27 27 166629 28 28 166730 29 29 1668SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; 1669f0 f1 f2 1670SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; 1671f0 f1 f2 1672100 99 99 167399 98 98 167498 97 97 167597 96 96 167696 95 95 167795 94 94 167894 93 93 167993 92 92 168092 91 91 168191 90 90 168210 9 9 168390 89 89 168489 88 88 168588 87 87 168687 86 86 168786 85 85 168885 84 84 168984 83 83 169083 82 82 169182 81 81 169281 80 80 16939 8 8 169480 79 79 169579 78 78 169678 77 77 169777 76 76 169876 75 75 169975 74 74 170074 73 73 170173 72 72 1702SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; 1703f0 f1 f2 1704SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; 1705f0 f1 f2 170612 11 11 170713 12 12 170814 13 13 170915 14 14 171016 15 15 171117 16 16 171218 17 17 171319 18 18 171420 19 19 171521 20 20 171622 21 21 171723 22 22 171824 23 23 171925 24 24 172026 25 25 172127 26 26 172228 27 27 172329 28 28 172430 29 29 172531 30 30 1726SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; 1727f0 f1 f2 1728SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; 1729f0 f1 f2 173022 21 21 173123 22 22 173224 23 23 173325 24 24 173426 25 25 173527 26 26 173628 27 27 173729 28 28 173830 29 29 173931 30 30 1740SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; 1741f0 f1 f2 1742set sort_buffer_size= 32768; 1743CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)); 1744INSERT INTO tmp SELECT f1, f2 FROM t1; 1745INSERT INTO t1(f1, f2) SELECT * FROM tmp; 1746INSERT INTO tmp SELECT f1, f2 FROM t1; 1747INSERT INTO t1(f1, f2) SELECT * FROM tmp; 1748SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; 1749f0 f1 f2 17501 0 0 1751101 0 0 1752201 0 0 1753301 0 0 1754401 0 0 17552 1 1 1756102 1 1 1757202 1 1 1758302 1 1 1759402 1 1 17603 2 2 1761103 2 2 1762203 2 2 1763303 2 2 1764403 2 2 17654 3 3 1766104 3 3 1767204 3 3 1768304 3 3 1769404 3 3 17705 4 4 1771105 4 4 1772205 4 4 1773305 4 4 1774405 4 4 17756 5 5 1776106 5 5 1777206 5 5 1778306 5 5 1779406 5 5 1780SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; 1781f0 f1 f2 1782SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; 1783f0 f1 f2 1784100 99 99 1785200 99 99 1786300 99 99 1787400 99 99 1788500 99 99 178999 98 98 1790199 98 98 1791299 98 98 1792399 98 98 1793499 98 98 179498 97 97 1795198 97 97 1796298 97 97 1797398 97 97 1798498 97 97 179997 96 96 1800197 96 96 1801297 96 96 1802397 96 96 1803497 96 96 180496 95 95 1805196 95 95 1806296 95 95 1807396 95 95 1808496 95 95 180995 94 94 1810195 94 94 1811295 94 94 1812395 94 94 1813495 94 94 1814SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; 1815f0 f1 f2 1816SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; 1817f0 f1 f2 181812 11 11 1819112 11 11 1820212 11 11 1821312 11 11 1822412 11 11 182313 12 12 1824113 12 12 1825213 12 12 1826313 12 12 1827413 12 12 182814 13 13 1829114 13 13 1830214 13 13 1831314 13 13 1832414 13 13 183315 14 14 1834115 14 14 1835215 14 14 1836315 14 14 1837415 14 14 1838SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; 1839f0 f1 f2 1840SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; 1841f0 f1 f2 184214 13 13 1843114 13 13 1844214 13 13 1845314 13 13 1846414 13 13 184715 14 14 1848115 14 14 1849215 14 14 1850315 14 14 1851415 14 14 1852SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; 1853f0 f1 f2 1854set sort_buffer_size= 32768; 1855SELECT SQL_CALC_FOUND_ROWS * FROM t1 1856ORDER BY f1, f0 LIMIT 30; 1857f0 f1 f2 18581 0 0 1859101 0 0 1860201 0 0 1861301 0 0 1862401 0 0 18632 1 1 1864102 1 1 1865202 1 1 1866302 1 1 1867402 1 1 18683 2 2 1869103 2 2 1870203 2 2 1871303 2 2 1872403 2 2 18734 3 3 1874104 3 3 1875204 3 3 1876304 3 3 1877404 3 3 18785 4 4 1879105 4 4 1880205 4 4 1881305 4 4 1882405 4 4 18836 5 5 1884106 5 5 1885206 5 5 1886306 5 5 1887406 5 5 1888SELECT FOUND_ROWS(); 1889FOUND_ROWS() 1890500 1891SELECT SQL_CALC_FOUND_ROWS * FROM t1 1892ORDER BY f1, f0 LIMIT 0; 1893f0 f1 f2 1894SELECT FOUND_ROWS(); 1895FOUND_ROWS() 1896500 1897SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 1898ORDER BY f2, f0 LIMIT 20; 1899f0 f1 f2 190012 11 11 1901112 11 11 1902212 11 11 1903312 11 11 1904412 11 11 190513 12 12 1906113 12 12 1907213 12 12 1908313 12 12 1909413 12 12 191014 13 13 1911114 13 13 1912214 13 13 1913314 13 13 1914414 13 13 191515 14 14 1916115 14 14 1917215 14 14 1918315 14 14 1919415 14 14 1920SELECT FOUND_ROWS(); 1921FOUND_ROWS() 1922445 1923SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 1924ORDER BY f2, f0 LIMIT 0; 1925f0 f1 f2 1926SELECT FOUND_ROWS(); 1927FOUND_ROWS() 1928445 1929SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 1930ORDER BY f2, f0 LIMIT 10 OFFSET 10; 1931f0 f1 f2 193214 13 13 1933114 13 13 1934214 13 13 1935314 13 13 1936414 13 13 193715 14 14 1938115 14 14 1939215 14 14 1940315 14 14 1941415 14 14 1942SELECT FOUND_ROWS(); 1943FOUND_ROWS() 1944445 1945SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 1946ORDER BY f2, f0 LIMIT 0 OFFSET 10; 1947f0 f1 f2 1948SELECT FOUND_ROWS(); 1949FOUND_ROWS() 1950445 1951set sort_buffer_size= 327680; 1952SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 1953ORDER BY tmp.f1, f0 LIMIT 30; 1954f0 f1 f2 f1 f2 19551 0 0 0 0 19561 0 0 0 0 19571 0 0 0 0 1958101 0 0 0 0 1959101 0 0 0 0 1960101 0 0 0 0 1961201 0 0 0 0 1962201 0 0 0 0 1963201 0 0 0 0 1964301 0 0 0 0 1965301 0 0 0 0 1966301 0 0 0 0 1967401 0 0 0 0 1968401 0 0 0 0 1969401 0 0 0 0 19702 1 1 1 1 19712 1 1 1 1 19722 1 1 1 1 1973102 1 1 1 1 1974102 1 1 1 1 1975102 1 1 1 1 1976202 1 1 1 1 1977202 1 1 1 1 1978202 1 1 1 1 1979302 1 1 1 1 1980302 1 1 1 1 1981302 1 1 1 1 1982402 1 1 1 1 1983402 1 1 1 1 1984402 1 1 1 1 1985SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 1986ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; 1987f0 f1 f2 f1 f2 19883 2 2 2 2 19893 2 2 2 2 19903 2 2 2 2 1991103 2 2 2 2 1992103 2 2 2 2 1993103 2 2 2 2 1994203 2 2 2 2 1995203 2 2 2 2 1996203 2 2 2 2 1997303 2 2 2 2 1998303 2 2 2 2 1999303 2 2 2 2 2000403 2 2 2 2 2001403 2 2 2 2 2002403 2 2 2 2 20034 3 3 3 3 20044 3 3 3 3 20054 3 3 3 3 2006104 3 3 3 3 2007104 3 3 3 3 2008104 3 3 3 3 2009204 3 3 3 3 2010204 3 3 3 3 2011204 3 3 3 3 2012304 3 3 3 3 2013304 3 3 3 3 2014304 3 3 3 3 2015404 3 3 3 3 2016404 3 3 3 3 2017404 3 3 3 3 2018SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 2019ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; 2020f0 f1 f2 f1 f2 20213 2 2 2 2 20223 2 2 2 2 20233 2 2 2 2 2024103 2 2 2 2 2025103 2 2 2 2 2026103 2 2 2 2 2027203 2 2 2 2 2028203 2 2 2 2 2029203 2 2 2 2 2030303 2 2 2 2 2031303 2 2 2 2 2032303 2 2 2 2 2033403 2 2 2 2 2034403 2 2 2 2 2035403 2 2 2 2 20364 3 3 3 3 20374 3 3 3 3 20384 3 3 3 3 2039104 3 3 3 3 2040104 3 3 3 3 2041104 3 3 3 3 2042204 3 3 3 3 2043204 3 3 3 3 2044204 3 3 3 3 2045304 3 3 3 3 2046304 3 3 3 3 2047304 3 3 3 3 2048404 3 3 3 3 2049404 3 3 3 3 2050404 3 3 3 3 2051SELECT FOUND_ROWS(); 2052FOUND_ROWS() 20531500 2054SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 2055WHERE t1.f2>20 2056ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; 2057f0 f1 f2 f1 f2 205824 23 23 23 23 205924 23 23 23 23 206024 23 23 23 23 2061124 23 23 23 23 2062124 23 23 23 23 2063124 23 23 23 23 2064224 23 23 23 23 2065224 23 23 23 23 2066224 23 23 23 23 2067324 23 23 23 23 2068324 23 23 23 23 2069324 23 23 23 23 2070424 23 23 23 23 2071424 23 23 23 23 2072424 23 23 23 23 207325 24 24 24 24 207425 24 24 24 24 207525 24 24 24 24 2076125 24 24 24 24 2077125 24 24 24 24 2078125 24 24 24 24 2079225 24 24 24 24 2080225 24 24 24 24 2081225 24 24 24 24 2082325 24 24 24 24 2083325 24 24 24 24 2084325 24 24 24 24 2085425 24 24 24 24 2086425 24 24 24 24 2087425 24 24 24 24 2088SELECT FOUND_ROWS(); 2089FOUND_ROWS() 20901185 2091CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30; 2092SELECT * FROM v1; 2093f0 f1 f2 20941 0 0 2095101 0 0 2096201 0 0 2097301 0 0 2098401 0 0 20992 1 1 2100102 1 1 2101202 1 1 2102302 1 1 2103402 1 1 21043 2 2 2105103 2 2 2106203 2 2 2107303 2 2 2108403 2 2 21094 3 3 2110104 3 3 2111204 3 3 2112304 3 3 2113404 3 3 21145 4 4 2115105 4 4 2116205 4 4 2117305 4 4 2118405 4 4 21196 5 5 2120106 5 5 2121206 5 5 2122306 5 5 2123406 5 5 2124drop view v1; 2125CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100; 2126SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; 2127f0 f1 f2 21281 0 0 2129101 0 0 2130201 0 0 2131301 0 0 2132401 0 0 21332 1 1 2134102 1 1 2135202 1 1 2136302 1 1 2137402 1 1 213811 10 10 2139111 10 10 2140211 10 10 2141311 10 10 2142411 10 10 214312 11 11 2144112 11 11 2145212 11 11 2146312 11 11 2147412 11 11 214813 12 12 2149113 12 12 2150213 12 12 2151313 12 12 2152413 12 12 215314 13 13 2154114 13 13 2155214 13 13 2156314 13 13 2157414 13 13 2158CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100; 2159SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0 2160LIMIT 30; 2161f0 f1 f2 f0 f1 f2 21621 0 0 1 0 0 21631 0 0 101 0 0 21641 0 0 201 0 0 21651 0 0 301 0 0 21661 0 0 401 0 0 2167101 0 0 1 0 0 2168101 0 0 101 0 0 2169101 0 0 201 0 0 2170101 0 0 301 0 0 2171101 0 0 401 0 0 2172201 0 0 1 0 0 2173201 0 0 101 0 0 2174201 0 0 201 0 0 2175201 0 0 301 0 0 2176201 0 0 401 0 0 2177301 0 0 1 0 0 2178301 0 0 101 0 0 2179301 0 0 201 0 0 2180301 0 0 301 0 0 2181301 0 0 401 0 0 2182401 0 0 1 0 0 2183401 0 0 101 0 0 2184401 0 0 201 0 0 2185401 0 0 301 0 0 2186401 0 0 401 0 0 21872 1 1 2 1 1 21882 1 1 102 1 1 21892 1 1 202 1 1 21902 1 1 302 1 1 21912 1 1 402 1 1 2192SELECT floor(f1/10) f3, count(f2) FROM t1 2193GROUP BY 1 ORDER BY 2,1 LIMIT 5; 2194f3 count(f2) 21950 50 21961 50 21972 50 21983 50 21994 50 2200SELECT floor(f1/10) f3, count(f2) FROM t1 2201GROUP BY 1 ORDER BY 2,1 LIMIT 0; 2202f3 count(f2) 2203CREATE PROCEDURE wl1393_sp_test() 2204BEGIN 2205SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30; 2206SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15; 2207SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 2208ORDER BY f2, f0 LIMIT 15 OFFSET 15; 2209SELECT FOUND_ROWS(); 2210SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; 2211END| 2212CALL wl1393_sp_test()| 2213f0 f1 f2 221412 11 11 2215112 11 11 2216212 11 11 2217312 11 11 2218412 11 11 221913 12 12 2220113 12 12 2221213 12 12 2222313 12 12 2223413 12 12 222414 13 13 2225114 13 13 2226214 13 13 2227314 13 13 2228414 13 13 222915 14 14 2230115 14 14 2231215 14 14 2232315 14 14 2233415 14 14 223416 15 15 2235116 15 15 2236216 15 15 2237316 15 15 2238416 15 15 223917 16 16 2240117 16 16 2241217 16 16 2242317 16 16 2243417 16 16 2244f0 f1 f2 224515 14 14 2246115 14 14 2247215 14 14 2248315 14 14 2249415 14 14 225016 15 15 2251116 15 15 2252216 15 15 2253316 15 15 2254416 15 15 225517 16 16 2256117 16 16 2257217 16 16 2258317 16 16 2259417 16 16 2260f0 f1 f2 226115 14 14 2262115 14 14 2263215 14 14 2264315 14 14 2265415 14 14 226616 15 15 2267116 15 15 2268216 15 15 2269316 15 15 2270416 15 15 227117 16 16 2272117 16 16 2273217 16 16 2274317 16 16 2275417 16 16 2276FOUND_ROWS() 2277445 2278f0 f1 f2 22791 0 0 2280101 0 0 2281201 0 0 2282301 0 0 2283401 0 0 22842 1 1 2285102 1 1 2286202 1 1 2287302 1 1 2288402 1 1 228911 10 10 2290111 10 10 2291211 10 10 2292311 10 10 2293411 10 10 229412 11 11 2295112 11 11 2296212 11 11 2297312 11 11 2298412 11 11 229913 12 12 2300113 12 12 2301213 12 12 2302313 12 12 2303413 12 12 230414 13 13 2305114 13 13 2306214 13 13 2307314 13 13 2308414 13 13 2309DROP PROCEDURE wl1393_sp_test| 2310SELECT d1.f1, d1.f2 FROM t1 2311LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1 2312ORDER BY d1.f2 DESC LIMIT 30; 2313f1 f2 23145 5 23155 5 23165 5 23175 5 23185 5 23195 5 23205 5 23215 5 23225 5 23235 5 23245 5 23255 5 23265 5 23275 5 23285 5 23295 5 23305 5 23315 5 23325 5 23335 5 23345 5 23355 5 23365 5 23375 5 23385 5 23394 4 23404 4 23414 4 23424 4 23434 4 2344SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1); 2345f0 f1 f2 23461 0 0 2347101 0 0 2348201 0 0 2349301 0 0 2350401 0 0 2351SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2); 2352ERROR 21000: Subquery returns more than 1 row 2353DROP TABLE t1, tmp; 2354DROP VIEW v1, v2; 2355# end of WL#1393 - Optimizing filesort with small limit 2356# 2357# Bug #58761 2358# Crash in Field::is_null in field.h on subquery in WHERE clause 2359# 2360CREATE TABLE t1 ( 2361pk INT NOT NULL AUTO_INCREMENT, 2362col_int_key INT DEFAULT NULL, 2363col_varchar_key VARCHAR(1) DEFAULT NULL, 2364PRIMARY KEY (pk), 2365KEY col_varchar_key (col_varchar_key,col_int_key) 2366); 2367INSERT INTO t1 VALUES (27,7,'x'); 2368INSERT INTO t1 VALUES (28,6,'m'); 2369INSERT INTO t1 VALUES (29,4,'c'); 2370CREATE TABLE where_subselect 2371SELECT DISTINCT `pk` AS field1 , `pk` AS field2 2372FROM t1 AS alias1 2373WHERE alias1 . `col_int_key` > 229 2374OR alias1 . `col_varchar_key` IS NOT NULL 2375GROUP BY field1, field2 2376; 2377SELECT * 2378FROM where_subselect 2379WHERE (field1, field2) IN ( 2380SELECT DISTINCT `pk` AS field1 , `pk` AS field2 2381FROM t1 AS alias1 2382WHERE alias1 . `col_int_key` > 229 2383OR alias1 . `col_varchar_key` IS NOT NULL 2384GROUP BY field1, field2 2385); 2386field1 field2 238727 27 238828 28 238929 29 2390DROP TABLE t1; 2391DROP TABLE where_subselect; 2392# End of Bug #58761 2393CREATE TABLE t1 ( 2394id1 INT NULL, 2395id2 INT NOT NULL, 2396junk INT NOT NULL, 2397PRIMARY KEY (id1, id2, junk), 2398INDEX id2_j_id1 (id2, junk, id1) 2399); 2400INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4); 2401INSERT INTO t1 VALUES (5, 2, 1), (6, 2, 2), (7, 2, 3), (8, 2, 4); 2402INSERT INTO t1 VALUES (9, 3, 1), (10, 3, 2), (11, 3, 3), (12, 3, 4); 2403INSERT INTO t1 VALUES (13, 4, 1), (14, 4, 2), (15, 4, 3), (16, 4, 4); 2404INSERT INTO t1 VALUES (17, 5, 1), (18, 5, 2), (19, 5, 3), (20, 5, 4); 2405INSERT INTO t1 VALUES (21, 6, 1), (22, 6, 2), (23, 6, 3), (24, 6, 4); 2406INSERT INTO t1 VALUES (25, 7, 1), (26, 7, 2), (27, 7, 3), (28, 7, 4); 2407INSERT INTO t1 VALUES (29, 8, 1), (30, 8, 2), (31, 8, 3), (32, 8, 4); 2408INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4); 2409EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; 2410id select_type table type possible_keys key key_len ref rows Extra 24111 SIMPLE t1 ref id2_j_id1 id2_j_id1 4 const 4 Using where; Using index; Using filesort 2412SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; 2413id1 241413 241514 241615 241716 2418DROP TABLE t1; 2419CREATE TABLE t1 ( 2420a INT, 2421b INT NOT NULL, 2422c char(100), 2423KEY (b, c), 2424KEY (b, a, c) 2425) 2426DEFAULT CHARSET = utf8; 2427INSERT INTO t1 VALUES 2428(1, 1, 1), 2429(2, 2, 2), 2430(3, 3, 3), 2431(4, 4, 4), 2432(5, 5, 5), 2433(6, 6, 6), 2434(7, 7, 7), 2435(8, 8, 8), 2436(9, 9, 9); 2437INSERT INTO t1 SELECT a + 10, b, c FROM t1; 2438INSERT INTO t1 SELECT a + 20, b, c FROM t1; 2439INSERT INTO t1 SELECT a + 40, b, c FROM t1; 2440INSERT INTO t1 SELECT a + 80, b, c FROM t1; 2441INSERT INTO t1 SELECT a + 160, b, c FROM t1; 2442INSERT INTO t1 SELECT a + 320, b, c FROM t1; 2443INSERT INTO t1 SELECT a + 640, b, c FROM t1; 2444INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; 2445EXPLAIN 2446SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; 2447id select_type table type possible_keys key key_len ref rows Extra 24481 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where 2449SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; 2450a 24512071 24522061 24532051 24542041 24552031 24562021 24572011 24582001 24591991 2460EXPLAIN 2461SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; 2462id select_type table type possible_keys key key_len ref rows Extra 24631 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where; Using temporary 2464SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; 2465a 24662071 24672061 24682051 24692041 24702031 24712021 24722011 24732001 24741991 2475DROP TABLE t1; 2476# 2477# Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering 2478# is used 2479# 2480CREATE TABLE t1 (a INT, b INT, KEY (a)); 2481INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL); 2482INSERT INTO t1 SELECT a+4, b FROM t1; 2483INSERT INTO t1 SELECT a+8, b FROM t1; 2484CREATE TABLE t2 (a INT, b INT); 2485INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL); 2486INSERT INTO t2 SELECT a+4, b FROM t2; 2487# shouldn't have "using filesort" 2488EXPLAIN 2489SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; 2490id select_type table type possible_keys key key_len ref rows Extra 24911 SIMPLE t1 range a a 5 NULL 2 Using where 24921 SIMPLE t2 ALL NULL NULL NULL NULL 10 NULL 2493# should have "using filesort" 2494EXPLAIN 2495SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; 2496id select_type table type possible_keys key key_len ref rows Extra 24971 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort 24981 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (Block Nested Loop) 2499# should have "using filesort" 2500EXPLAIN 2501SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; 2502id select_type table type possible_keys key key_len ref rows Extra 25031 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort 25041 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (Block Nested Loop) 2505DROP TABLE t1, t2; 2506# 2507# Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and 2508# ORDER BY computed col 2509# 2510CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, KEY( a, b ) ); 2511INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 2512INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; 2513CREATE TABLE t2( a INT PRIMARY KEY, b INT ); 2514INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 2515INSERT INTO t2 SELECT a + 5, b + 5 FROM t2; 2516EXPLAIN 2517SELECT count(*) AS c, t1.a 2518FROM t1 JOIN t2 ON t1.b = t2.a 2519WHERE t2.b = 1 2520GROUP BY t1.a 2521ORDER by c 2522LIMIT 2; 2523id select_type table type possible_keys key key_len ref rows Extra 25241 SIMPLE t1 index a a 8 NULL 10 Using index; Using temporary; Using filesort 25251 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where 2526DROP TABLE t1, t2; 2527# 2528# Bug #59110: Memory leak of QUICK_SELECT_I allocated memory 2529# and 2530# Bug #59308: Incorrect result for 2531SELECT DISTINCT <col>... ORDER BY <col> DESC 2532 2533# Use Valgrind to detect #59110! 2534# 2535CREATE TABLE t1 (a INT,KEY (a)); 2536INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 2537EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; 2538id select_type table type possible_keys key key_len ref rows Extra 25391 SIMPLE t1 index a a 5 NULL 10 Using where; Using index 2540SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; 2541a 1 254210 1 25439 1 25448 1 25457 1 25466 1 25475 1 25484 1 25493 1 25502 1 2551DROP TABLE t1; 2552# 2553# Bug#11765255 58201: 2554# VALGRIND/CRASH WHEN ORDERING BY MULTIPLE AGGREGATE FUNCTIONS 2555# 2556select 1 order by max(1) + min(1); 25571 25581 2559End of 5.1 tests 2560# 2561# Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY 2562# when it should use index 2563# 2564CREATE TABLE t1 (i1 integer NOT NULL PRIMARY KEY); 2565CREATE TABLE t2 (i2 integer NOT NULL PRIMARY KEY); 2566CREATE TABLE t3 (i3 integer); 2567INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12); 2568INSERT INTO t2 SELECT * FROM t1; 2569EXPLAIN EXTENDED 2570SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 2571LEFT JOIN t3 ON t2.i2 = t3.i3 2572ORDER BY t1.i1 LIMIT 5; 2573id select_type table type possible_keys key key_len ref rows filtered Extra 25741 SIMPLE t3 system NULL NULL NULL NULL 0 0.00 const row not found 25751 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 240.00 Using index 25761 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 100.00 Using index 2577Warnings: 2578Note 1003 /* select#1 */ 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 2579SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 2580LEFT JOIN t3 ON t2.i2 = t3.i3 2581ORDER BY t1.i1 LIMIT 5; 2582i1 i2 25831 1 25842 2 25853 3 25864 4 25875 5 2588DROP TABLE t1, t2, t3; 2589# 2590# Bug #11885377 VOID JOIN_READ_KEY_UNLOCK_ROW(ST_JOIN_TABLE*): ASSERTION 2591# `TAB->REF.USE_COUNT' 2592# 2593CREATE TABLE t1(a INT PRIMARY KEY); 2594CREATE TABLE t2(b INT,c INT); 2595INSERT INTO t1 VALUES (1), (2); 2596INSERT INTO t2 VALUES (1,2), (2,3); 2597SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1 ORDER BY a DESC) FROM t2; 2598(SELECT 1 FROM t1 WHERE a=b AND c=1 ORDER BY a DESC) 2599NULL 2600NULL 2601DROP TABLE t1, t2; 2602# 2603# Bug #13531865 2604# TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF 2605# 'TYPE' IS REF_OR_NULL 2606# 2607# 2608CREATE TABLE t1 ( 2609a INT, 2610c INT, 2611UNIQUE KEY a_c (a,c), 2612KEY (a)) engine=myisam; 2613INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10); 2614ANALYZE TABLE t1; 2615Table Op Msg_type Msg_text 2616test.t1 analyze status OK 2617# Using 'KEY a_c' for order-by opt, would have required 2618# REF_OR_NULL access which never can be order_by skipped. 2619# -> Keep initial REF on 'KEY a' selected by cond. optimizer 2620EXPLAIN 2621SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL); 2622id select_type table type possible_keys key key_len ref rows Extra 26231 SIMPLE t1 ref a_c,a a 5 const 1 Using where 2624EXPLAIN 2625SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 2626id select_type table type possible_keys key key_len ref rows Extra 26271 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort 2628SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 2629c 2630NULL 263110 2632EXPLAIN 2633SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 2634id select_type table type possible_keys key key_len ref rows Extra 26351 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort 2636SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 2637c 263810 2639NULL 2640DROP TABLE t1; 2641# 2642# Bug #13528826 2643# TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT' 2644# 2645# 2646CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam; 2647INSERT INTO t1 VALUES 2648(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); 2649CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam; 2650INSERT INTO t2 VALUES 2651(103, 7), (109, 3), (102, 3), (108, 1), (106, 3), 2652(107, 7), (105, 1), (101, 3), (100, 7), (110, 1); 2653# number of rows in t1 was incorrectly used as an 2654# implicit limit-clause if not explicit specified 2655EXPLAIN 2656SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a; 2657id select_type table type possible_keys key key_len ref rows Extra 26581 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index 26591 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index 2660# Query above used to be explained identical to this: 2661EXPLAIN 2662SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8; 2663id select_type table type possible_keys key key_len ref rows Extra 26641 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index 26651 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index 2666# A really high limit was required to give the correct explain 2667EXPLAIN 2668SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000; 2669id select_type table type possible_keys key key_len ref rows Extra 26701 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index 26711 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index 2672DROP TABLE t1, t2; 2673# 2674# Bug #13949068 ASSERT TAB->REF.KEY == REF_KEY IN 2675# PLAN_CHANGE_WATCHDOG::~PLAN_CHANGE_WATCHDOG 2676# 2677CREATE TABLE t1 (a INT, b INT, KEY(b), KEY(b,a)) ENGINE=INNODB; 2678INSERT INTO t1 VALUES (0,0); 2679EXPLAIN SELECT DISTINCT a FROM t1 WHERE b=1 ORDER BY 1; 2680id select_type table type possible_keys key key_len ref rows Extra 26811 SIMPLE t1 ref b,b_2 b_2 5 const 1 Using where 2682SELECT DISTINCT a FROM t1 WHERE b=1 ORDER BY 1; 2683a 2684DROP TABLE t1; 2685set optimizer_switch=default; 2686
