1# 2# Bug with order by 3# 4 5--disable_warnings 6drop table if exists t1,t2,t3; 7--enable_warnings 8 9CREATE TABLE t1 ( 10 id int(6) DEFAULT '0' NOT NULL, 11 idservice int(5), 12 clee char(20) NOT NULL, 13 flag char(1), 14 KEY id (id), 15 PRIMARY KEY (clee) 16); 17 18 19INSERT INTO t1 VALUES (2,4,'6067169d','Y'); 20INSERT INTO t1 VALUES (2,5,'606716d1','Y'); 21INSERT INTO t1 VALUES (2,1,'606717c1','Y'); 22INSERT INTO t1 VALUES (3,1,'6067178d','Y'); 23INSERT INTO t1 VALUES (2,6,'60671515','Y'); 24INSERT INTO t1 VALUES (2,7,'60671569','Y'); 25INSERT INTO t1 VALUES (2,3,'dd','Y'); 26 27CREATE TABLE t2 ( 28 id int(6) NOT NULL auto_increment, 29 description varchar(40) NOT NULL, 30 idform varchar(40), 31 ordre int(6) unsigned DEFAULT '0' NOT NULL, 32 image varchar(60), 33 PRIMARY KEY (id), 34 KEY id (id,ordre) 35); 36 37# 38# Dumping data for table 't2' 39# 40 41INSERT INTO t2 VALUES (1,'Emettre un appel d''offres','en_construction.html',10,'emettre.gif'); 42INSERT INTO t2 VALUES (2,'Emettre des soumissions','en_construction.html',20,'emettre.gif'); 43INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif'); 44INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif'); 45INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif'); 46INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif'); 47INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); 48INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif'); 49INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif'); 50INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif'); 51 52 53select t1.id,t1.idservice,t2.ordre,t2.description from t1, t2 where t1.id = 2 and t1.idservice = t2.id order by t2.ordre; 54 55drop table t1,t2; 56 57# 58# Test of ORDER BY on concat() result 59# 60 61create table t1 (first char(10),last char(10)); 62insert into t1 values ("Michael","Widenius"); 63insert into t1 values ("Allan","Larsson"); 64insert into t1 values ("David","Axmark"); 65select concat(first," ",last) as name from t1 order by name; 66select concat(last," ",first) as name from t1 order by name; 67drop table t1; 68 69# 70# bug in distinct + order by 71# 72 73create table t1 (i int); 74insert into t1 values(1),(2),(1),(2),(1),(2),(3); 75select distinct i from t1; 76select distinct i from t1 order by rand(5); 77select distinct i from t1 order by i desc; 78select distinct i from t1 order by 1-i; 79select distinct i from t1 order by mod(i,2),i; 80drop table t1; 81 82# 83# bug#3681 84# 85 86create table t1 ( pk int primary key, name varchar(255) not null, number varchar(255) not null); 87insert into t1 values (1, 'Gamma', '123'), (2, 'Gamma Ext', '123a'), (3, 'Alpha', '001'), (4, 'Beta', '200c'); 88select distinct t1.name as 'Building Name',t1.number as 'Building Number' from t1 order by t1.name asc; 89drop table t1; 90 91 92# 93# Order by on first index part 94# 95 96create table t1 (id int not null,col1 int not null,col2 int not null,index(col1)); 97insert 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); 98select * from t1 order by col1,col2; 99select col1 from t1 order by id; 100select col1 as id from t1 order by id; 101select concat(col1) as id from t1 order by id; 102drop table t1; 103 104# 105# Test of order by on field() 106# 107 108CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta timestamp); 109insert into t1 (aika) values ('Keskiviikko'); 110insert into t1 (aika) values ('Tiistai'); 111insert into t1 (aika) values ('Maanantai'); 112insert into t1 (aika) values ('Sunnuntai'); 113 114SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test; 115drop table t1; 116 117# 118# Test of ORDER BY on IF 119# 120 121CREATE TABLE t1 122( 123 a int unsigned NOT NULL, 124 b int unsigned NOT NULL, 125 c int unsigned NOT NULL, 126 UNIQUE(a), 127 INDEX(b), 128 INDEX(c) 129); 130 131CREATE TABLE t2 132( 133 c int unsigned NOT NULL, 134 i int unsigned NOT NULL, 135 INDEX(c) 136); 137 138CREATE TABLE t3 139( 140 c int unsigned NOT NULL, 141 v varchar(64), 142 INDEX(c) 143); 144 145INSERT INTO t1 VALUES (1,1,1); 146INSERT INTO t1 VALUES (2,1,2); 147INSERT INTO t1 VALUES (3,2,1); 148INSERT INTO t1 VALUES (4,2,2); 149INSERT INTO t2 VALUES (1,50); 150INSERT INTO t2 VALUES (2,25); 151INSERT INTO t3 VALUES (1,'123 Park Place'); 152INSERT INTO t3 VALUES (2,'453 Boardwalk'); 153 154SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 155FROM t1 156LEFT JOIN t2 USING(c) 157LEFT JOIN t3 ON t3.c = t1.c; 158 159SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 160FROM t1 161LEFT JOIN t2 ON t1.c = t2.c 162LEFT JOIN t3 ON t3.c = t1.c; 163 164SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 165FROM t1 166LEFT JOIN t2 USING(c) 167LEFT JOIN t3 ON t3.c = t1.c 168ORDER BY a; 169 170SELECT a,b,if(b = 1,i,if(b = 2,v,'')) 171FROM t1 172LEFT JOIN t2 ON t1.c = t2.c 173LEFT JOIN t3 ON t3.c = t1.c 174ORDER BY a; 175 176drop table t1,t2,t3; 177 178# 179# Test of ORDER BY (Bug found by Dean Edmonds) 180# 181 182create table t1 (ID int not null primary key, TransactionID int not null); 183insert 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); 184create table t2 (ID int not null primary key, GroupID int not null); 185 insert into t2 (ID, GroupID) values (87, 87), (89, 89), (92, 92), (94, 94), (486, 486), (490, 490),(753, 753), (828, 828), (832, 832), (834, 834), (840, 840); 186create table t3 (ID int not null primary key, DateOfAction date not null); 187insert 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'); 188select 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; 189select 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; 190drop table t1,t2,t3; 191 192#bug reported by Wouter de Jong 193 194CREATE TABLE t1 ( 195 member_id int(11) NOT NULL auto_increment, 196 inschrijf_datum varchar(20) NOT NULL default '', 197 lastchange_datum varchar(20) NOT NULL default '', 198 nickname varchar(20) NOT NULL default '', 199 password varchar(8) NOT NULL default '', 200 voornaam varchar(30) NOT NULL default '', 201 tussenvoegsels varchar(10) NOT NULL default '', 202 achternaam varchar(50) NOT NULL default '', 203 straat varchar(100) NOT NULL default '', 204 postcode varchar(10) NOT NULL default '', 205 wijk varchar(40) NOT NULL default '', 206 plaats varchar(50) NOT NULL default '', 207 telefoon varchar(10) NOT NULL default '', 208 geboortedatum date NOT NULL default '0000-00-00', 209 geslacht varchar(5) NOT NULL default '', 210 email varchar(80) NOT NULL default '', 211 uin varchar(15) NOT NULL default '', 212 homepage varchar(100) NOT NULL default '', 213 internet varchar(15) NOT NULL default '', 214 scherk varchar(30) NOT NULL default '', 215 favo_boek varchar(50) NOT NULL default '', 216 favo_tijdschrift varchar(50) NOT NULL default '', 217 favo_tv varchar(50) NOT NULL default '', 218 favo_eten varchar(50) NOT NULL default '', 219 favo_muziek varchar(30) NOT NULL default '', 220 info text NOT NULL default '', 221 ipnr varchar(30) NOT NULL default '', 222 PRIMARY KEY (member_id) 223) ENGINE=MyISAM PACK_KEYS=1; 224 225insert into t1 (member_id) values (1),(2),(3); 226select member_id, nickname, voornaam FROM t1 227ORDER by lastchange_datum DESC LIMIT 2; 228drop table t1; 229 230# 231# Test optimization of ORDER BY DESC 232# 233 234create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); 235insert 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'); 236 237explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; 238select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; 239explain select * from t1 where a >= 1 and a < 3 order by a desc; 240select * from t1 where a >= 1 and a < 3 order by a desc; 241explain select * from t1 where a = 1 order by a desc, b desc; 242select * from t1 where a = 1 order by a desc, b desc; 243explain select * from t1 where a = 1 and b is null order by a desc, b desc; 244select * from t1 where a = 1 and b is null order by a desc, b desc; 245explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc; 246explain select * from t1 where a = 2 and b >0 order by a desc,b desc; 247explain select * from t1 where a = 2 and b is null order by a desc,b desc; 248explain select * from t1 where a = 2 and (b is null or b > 0) order by a 249desc,b desc; 250explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; 251explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; 252explain select * from t1 where a = 1 order by b desc; 253select * from t1 where a = 1 order by b desc; 254# 255# Test things when we don't have NULL keys 256# 257 258alter table t1 modify b int not null, modify c varchar(10) not null; 259explain select * from t1 order by a, b, c; 260select * from t1 order by a, b, c; 261explain select * from t1 order by a desc, b desc, c desc; 262select * from t1 order by a desc, b desc, c desc; 263# test multiple ranges, NO_MAX_RANGE and EQ_RANGE 264explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; 265select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; 266# test NEAR_MAX, NO_MIN_RANGE 267explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc; 268select * from t1 where a < 2 and b <= 1 order by a desc, b desc; 269select count(*) from t1 where a < 5 and b > 0; 270select * from t1 where a < 5 and b > 0 order by a desc,b desc; 271# test HA_READ_AFTER_KEY (at the end of the file), NEAR_MIN 272explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; 273select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; 274# test HA_READ_AFTER_KEY (in the middle of the file) 275explain select * from t1 where a between 0 and 1 order by a desc, b desc; 276select * from t1 where a between 0 and 1 order by a desc, b desc; 277drop table t1; 278 279 280CREATE TABLE t1 ( 281 gid int(10) unsigned NOT NULL auto_increment, 282 cid smallint(5) unsigned NOT NULL default '0', 283 PRIMARY KEY (gid), 284 KEY component_id (cid) 285) ENGINE=MyISAM; 286INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108); 287ALTER TABLE t1 add skr int(10) not null; 288 289CREATE TABLE t2 ( 290 gid int(10) unsigned NOT NULL default '0', 291 uid smallint(5) unsigned NOT NULL default '1', 292 sid tinyint(3) unsigned NOT NULL default '1', 293 PRIMARY KEY (gid), 294 KEY uid (uid), 295 KEY status_id (sid) 296) ENGINE=MyISAM; 297INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5); 298 299CREATE TABLE t3 ( 300 uid smallint(6) NOT NULL auto_increment, 301 PRIMARY KEY (uid) 302) ENGINE=MyISAM; 303INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250); 304ALTER TABLE t3 add skr int(10) not null; 305 306select 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; 307select 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; 308 309# The following ORDER BY can be optimimized 310EXPLAIN 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; 311EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr; 312 313# The following ORDER BY can't be optimimized 314EXPLAIN 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; 315EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid; 316EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; 317drop table t1,t2,t3; 318 319# 320# Test of bug when doing an ORDER BY with const items 321# 322 323CREATE TABLE t1 ( 324 `titre` char(80) NOT NULL default '', 325 `numeropost` mediumint(8) unsigned NOT NULL auto_increment, 326 `date` datetime NOT NULL default '0000-00-00 00:00:00', 327 `auteur` char(35) NOT NULL default '', 328 `icone` tinyint(2) unsigned NOT NULL default '0', 329 `lastauteur` char(35) NOT NULL default '', 330 `nbrep` smallint(6) unsigned NOT NULL default '0', 331 `dest` char(35) NOT NULL default '', 332 `lu` tinyint(1) unsigned NOT NULL default '0', 333 `vue` mediumint(8) unsigned NOT NULL default '0', 334 `ludest` tinyint(1) unsigned NOT NULL default '0', 335 `ouvert` tinyint(1) unsigned NOT NULL default '1', 336 PRIMARY KEY (`numeropost`), 337 KEY `date` (`date`), 338 KEY `dest` (`dest`,`ludest`), 339 KEY `auteur` (`auteur`,`lu`), 340 KEY `auteur_2` (`auteur`,`date`), 341 KEY `dest_2` (`dest`,`date`) 342) CHECKSUM=1; 343 344CREATE TABLE t2 ( 345 `numeropost` mediumint(8) unsigned NOT NULL default '0', 346 `pseudo` char(35) NOT NULL default '', 347 PRIMARY KEY (`numeropost`,`pseudo`), 348 KEY `pseudo` (`pseudo`) 349); 350 351INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug'); 352INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug'); 353SELECT 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; 354SELECT 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; 355SELECT 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; 356SELECT 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; 357drop table t1,t2; 358 359# 360# Test order by with NULL values 361# 362CREATE TABLE t1 (a int, b int); 363INSERT INTO t1 VALUES (1, 2); 364INSERT INTO t1 VALUES (3, 4); 365INSERT INTO t1 VALUES (5, NULL); 366SELECT * FROM t1 ORDER BY b; 367SELECT * FROM t1 ORDER BY b DESC; 368SELECT * FROM t1 ORDER BY (a + b); 369SELECT * FROM t1 ORDER BY (a + b) DESC; 370DROP TABLE t1; 371 372# 373# Test of FORCE INDEX ... ORDER BY 374# 375 376create table t1(id int not null auto_increment primary key, t char(12)); 377disable_query_log; 378let $1 = 1000; 379while ($1) 380 { 381 eval insert into t1(t) values ('$1'); 382 dec $1; 383 } 384enable_query_log; 385explain select id,t from t1 order by id; 386explain select id,t from t1 force index (primary) order by id; 387drop table t1; 388 389# 390# Test of test_if_subkey() function 391# 392CREATE TABLE t1 ( 393 FieldKey varchar(36) NOT NULL default '', 394 LongVal bigint(20) default NULL, 395 StringVal mediumtext, 396 KEY FieldKey (FieldKey), 397 KEY LongField (FieldKey,LongVal), 398 KEY StringField (FieldKey,StringVal(32)) 399); 400INSERT 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'); 401EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; 402SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; 403EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; 404SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; 405EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; 406SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; 407DROP TABLE t1; 408# 409# Bug #1945 - Crashing bug with bad User Variables in UPDATE ... ORDER BY ... 410# 411CREATE TABLE t1 (a INT, b INT); 412SET @id=0; 413UPDATE t1 SET a=0 ORDER BY (a=@id), b; 414DROP TABLE t1; 415 416# 417# Bug when doing an order by on a 1 byte string (Bug #2147) 418# 419 420CREATE 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; 421INSERT INTO t1 VALUES (11384, 2),(11392, 2); 422SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ; 423drop table t1; 424 425# 426# REF_OR_NULL optimization + filesort (bug #2419) 427# 428 429create table t1(a int, b int, index(b)); 430insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); 431explain select * from t1 where b=1 or b is null order by a; 432select * from t1 where b=1 or b is null order by a; 433explain select * from t1 where b=2 or b is null order by a; 434select * from t1 where b=2 or b is null order by a; 435drop table t1; 436 437# 438# Bug #3155 - Strange results with index (x, y) ... WHERE ... ORDER BY pk 439# 440 441create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null, 442key(a,b,d), key(c,b,a)); 443create table t2 like t1; 444insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3); 445insert into t2 select null, b, c, d from t1; 446insert into t1 select null, b, c, d from t2; 447insert into t2 select null, b, c, d from t1; 448insert into t1 select null, b, c, d from t2; 449insert into t2 select null, b, c, d from t1; 450insert into t1 select null, b, c, d from t2; 451insert into t2 select null, b, c, d from t1; 452insert into t1 select null, b, c, d from t2; 453insert into t2 select null, b, c, d from t1; 454insert into t1 select null, b, c, d from t2; 455optimize table t1; 456set @row=10; 457insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10; 458select * from t1 where a=1 and b in (1) order by c, b, a; 459select * from t1 where a=1 and b in (1); 460drop table t1, t2; 461 462# 463# Bug #4302 464# Ambiguos order by when renamed column is identical to another in result. 465# Should not fail and prefer column from t1 for sorting. 466# 467create table t1 (col1 int, col int); 468create table t2 (col2 int, col int); 469insert into t1 values (1,1),(2,2),(3,3); 470insert into t2 values (1,3),(2,2),(3,1); 471select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2) 472 order by col; 473 474# 475# Let us also test various ambiguos and potentially ambiguos cases 476# related to aliases 477# 478--error 1052 479select col1 as col, col from t1 order by col; 480--error 1052 481select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 482 order by col; 483--error 1052 484select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 485 order by col; 486--error 1052 487select col1 from t1, t2 where t1.col1=t2.col2 order by col; 488--error 1052 489select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2 490 order by col; 491 492select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2 493 order by col; 494select col2 as c, col as c from t2 order by col; 495select col2 as col, col as col2 from t2 order by col; 496select t2.col2, t2.col, t2.col from t2 order by col; 497 498select t2.col2 as col from t2 order by t2.col; 499select t2.col2 as col, t2.col from t2 order by t2.col; 500select t2.col2, t2.col, t2.col from t2 order by t2.col; 501 502drop table t1, t2; 503 504# 505# Bug #5428: a problem with small max_sort_length value 506# 507 508create table t1 (a char(25)); 509insert into t1 set a = repeat('x', 20); 510insert into t1 set a = concat(repeat('x', 19), 'z'); 511insert into t1 set a = concat(repeat('x', 19), 'ab'); 512insert into t1 set a = concat(repeat('x', 19), 'aa'); 513set max_sort_length=20; 514select a from t1 order by a; 515drop table t1; 516 517# 518# Bug #7331 519# 520 521create table t1 ( 522 `sid` decimal(8,0) default null, 523 `wnid` varchar(11) not null default '', 524 key `wnid14` (`wnid`(4)), 525 key `wnid` (`wnid`) 526) engine=myisam default charset=latin1; 527 528insert into t1 (`sid`, `wnid`) values 529('10100','01019000000'),('37986','01019000000'),('37987','01019010000'), 530('39560','01019090000'),('37989','01019000000'),('37990','01019011000'), 531('37991','01019011000'),('37992','01019019000'),('37993','01019030000'), 532('37994','01019090000'),('475','02070000000'),('25253','02071100000'), 533('25255','02071100000'),('25256','02071110000'),('25258','02071130000'), 534('25259','02071190000'),('25260','02071200000'),('25261','02071210000'), 535('25262','02071290000'),('25263','02071300000'),('25264','02071310000'), 536('25265','02071310000'),('25266','02071320000'),('25267','02071320000'), 537('25269','02071330000'),('25270','02071340000'),('25271','02071350000'), 538('25272','02071360000'),('25273','02071370000'),('25281','02071391000'), 539('25282','02071391000'),('25283','02071399000'),('25284','02071400000'), 540('25285','02071410000'),('25286','02071410000'),('25287','02071420000'), 541('25288','02071420000'),('25291','02071430000'),('25290','02071440000'), 542('25292','02071450000'),('25293','02071460000'),('25294','02071470000'), 543('25295','02071491000'),('25296','02071491000'),('25297','02071499000'); 544 545explain select * from t1 where wnid like '0101%' order by wnid; 546 547select * from t1 where wnid like '0101%' order by wnid; 548 549drop table t1; 550 551# 552# Bug #7672 - a wrong result for a select query in braces followed by order by 553# 554 555CREATE TABLE t1 (a int); 556INSERT INTO t1 VALUES (2), (1), (1), (2), (1); 557SELECT a FROM t1 ORDER BY a; 558(SELECT a FROM t1) ORDER BY a; 559DROP TABLE t1; 560 561# 562# Bug #18767: global ORDER BY applied to a SELECT with ORDER BY either was 563# ignored or 'concatened' to the latter. 564 565CREATE TABLE t1 (a int, b int); 566INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10); 567 568(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a; 569(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC; 570(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b; 571(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b; 572 573DROP TABLE t1; 574 575# 576# Bug #22457: Column alias in ORDER BY works, but not if in an expression 577# 578 579CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); 580SELECT a + 1 AS num FROM t1 ORDER BY 30 - num; 581SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str); 582SELECT a + 1 AS num FROM t1 GROUP BY 30 - num; 583SELECT a + 1 AS num FROM t1 HAVING 30 - num; 584--error 1054 585SELECT a + 1 AS num, num + 1 FROM t1; 586SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; 587--error 1054 588SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; 589DROP TABLE t1; 590 591# 592# Bug#25126: Reference to non-existant column in UPDATE...ORDER BY... 593# crashes server 594# 595CREATE TABLE bug25126 ( 596 val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY 597); 598--error 1054 599UPDATE bug25126 SET MissingCol = MissingCol; 600--error 1054 601UPDATE bug25126 SET val = val ORDER BY MissingCol; 602UPDATE bug25126 SET val = val ORDER BY val; 603UPDATE bug25126 SET val = 1 ORDER BY val; 604--error 1054 605UPDATE bug25126 SET val = 1 ORDER BY MissingCol; 606--error 1054 607UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol; 608--error 1054 609UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol; 610--error 1054 611UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol; 612--error 1054 613UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol; 614--error 1054 615UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol; 616--error 1054 617UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol; 618DROP TABLE bug25126; 619 620# 621# Bug #25427: crash when order by expression contains a name 622# that cannot be resolved unambiguously 623# 624 625CREATE TABLE t1 (a int); 626 627SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1; 628--error 1052 629SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; 630--error 1052 631SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; 632 633DROP TABLE t1; 634 635# 636# Bug #27532: ORDER/GROUP BY expressions with IN/BETWEEN and NOT IN/BETWEEN 637# 638 639CREATE TABLE t1 (a int); 640INSERT INTO t1 VALUES (3), (2), (4), (1); 641 642SELECT a, IF(a IN (2,3), a, a+10) FROM t1 643 ORDER BY IF(a IN (2,3), a, a+10); 644SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1 645 ORDER BY IF(a NOT IN (2,3), a, a+10); 646SELECT a, IF(a IN (2,3), a, a+10) FROM t1 647 ORDER BY IF(a NOT IN (2,3), a, a+10); 648 649SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 650 ORDER BY IF(a BETWEEN 2 AND 3, a, a+10); 651SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1 652 ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); 653SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 654 ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); 655 656SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 657 FROM t1 GROUP BY x1, x2; 658SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 659 FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, ''); 660 661# The remaining queries are for better coverage 662SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2); 663SELECT a FROM t1 ORDER BY a IN (1,2); 664SELECT a+10 FROM t1 ORDER BY a IN (1,2); 665SELECT a, IF(a IN (1,2), a, a+10) FROM t1 666 ORDER BY IF(a IN (3,4), a, a+10); 667DROP TABLE t1; 668 669# End of 4.1 670create table t1 (a int not null, b int not null, c int not null); 671insert t1 values (1,1,1),(1,1,2),(1,2,1); 672select a, b from t1 group by a, b order by sum(c); 673drop table t1; 674 675# 676# Bug#21302: Result not properly sorted when using an ORDER BY on a second 677# table in a join 678# 679CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); 680INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 681 682explain SELECT t1.b as a, t2.b as c FROM 683 t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 684ORDER BY c; 685SELECT t2.b as c FROM 686 t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 687ORDER BY c; 688 689# check that it still removes sort of const table 690explain SELECT t1.b as a, t2.b as c FROM 691 t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 692ORDER BY c; 693 694CREATE TABLE t2 LIKE t1; 695INSERT INTO t2 SELECT * from t1; 696CREATE TABLE t3 LIKE t1; 697INSERT INTO t3 SELECT * from t1; 698CREATE TABLE t4 LIKE t1; 699INSERT INTO t4 SELECT * from t1; 700INSERT INTO t1 values (0,0),(4,4); 701 702SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a) 703ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b; 704 705DROP TABLE t1,t2,t3,t4; 706 707# 708# Bug#25376: Incomplete setup of ORDER BY clause results in a wrong result. 709# 710create table t1 (a int, b int, c int); 711insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9); 712select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc; 713drop table t1; 714 715# 716# Bug#26672: Incorrect SEC_TO_TIME() casting in ORDER BY 717# 718CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME); 719INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10); 720UPDATE t1 SET b = SEC_TO_TIME(a); 721 722# Correct ORDER 723SELECT a, b FROM t1 ORDER BY b DESC; 724 725# must be ordered as the above 726SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC; 727 728DROP TABLE t1; 729 730# 731# BUG#16590: Optimized does not do right "const" table pre-read 732# 733CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b)); 734INSERT INTO t1 VALUES (1,1),(2,2); 735 736CREATE TABLE t2 (a INT, b INT, KEY a (a,b)); 737INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); 738 739EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; 740 741DROP TABLE t1,t2; 742 743# End of 5.0 744 745# 746# Bug #28404: query with ORDER BY and ref access 747# 748 749CREATE TABLE t1( 750 id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3)); 751 752INSERT INTO t1 (c2,c3) VALUES 753 (31,34),(35,38),(34,31),(32,35),(31,39), 754 (11,14),(15,18),(14,11),(12,15),(11,19); 755 756INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 757INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 758INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 759INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 760INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 761INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 762INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 763INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 764INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 765INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 766INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 767INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; 768UPDATE t1 SET c2=20 WHERE id%100 = 0; 769SELECT COUNT(*) FROM t1; 770 771CREATE TABLE t2 LIKE t1; 772INSERT INTO t2 SELECT * FROM t1 ORDER BY id; 773 774EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; 775EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; 776EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; 777EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; 778 779SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; 780 781DROP TABLE t1,t2; 782 783# 784# Bug #30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY} 785# 786CREATE TABLE t1 ( 787 a INT, 788 b INT, 789 PRIMARY KEY (a), 790 KEY ab(a, b) 791); 792INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); 793INSERT INTO t1 SELECT a + 4, b + 4 FROM t1; 794INSERT INTO t1 SELECT a + 8, b + 8 FROM t1; 795INSERT INTO t1 SELECT a +16, b +16 FROM t1; 796INSERT INTO t1 SELECT a +32, b +32 FROM t1; 797INSERT INTO t1 SELECT a +64, b +64 FROM t1; 798 799EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; 800 801--disable_query_log 802--let $q = `show status like 'Created_tmp_tables';` 803eval set @tmp_tables_before = 804 CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED); 805--enable_query_log 806 807SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; 808 809# this query creates one temporary table in itself, which we are not 810# interested in. 811 812--disable_query_log 813--let $q = `show status like 'Created_tmp_tables';` 814eval set @tmp_tables_after = 815 CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED); 816--enable_query_log 817 818SELECT @tmp_tables_after = @tmp_tables_before ; 819 820EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; 821 822--disable_query_log 823--let $q = `show status like 'Created_tmp_tables';` 824eval set @tmp_tables_before = 825 CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED); 826--enable_query_log 827 828SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; 829 830--disable_query_log 831--let $q = `show status like 'Created_tmp_tables';` 832eval set @tmp_tables_after = 833 CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED); 834--enable_query_log 835 836SELECT @tmp_tables_after = @tmp_tables_before; 837 838DROP TABLE t1; 839--echo # 840--echo # Bug#31590: Wrong error message on sort buffer being too small. 841--echo # 842create table t1(a int, b tinytext); 843insert into t1 values (1,2),(3,2); 844set session sort_buffer_size= 30000; 845set session max_sort_length= 2180; 846CALL mtr.add_suppression("Out of sort memory"); 847--error ER_OUT_OF_SORTMEMORY 848select * from t1 order by b; 849drop table t1; 850call mtr.add_suppression("Out of sort memory; increase server sort buffer size"); 851--echo # 852--echo # Bug #39844: Query Crash Mysql Server 5.0.67 853--echo # 854 855CREATE TABLE t1 (a INT PRIMARY KEY); 856CREATE TABLE t2 (a INT PRIMARY KEY, b INT); 857CREATE TABLE t3 (c INT); 858 859INSERT INTO t1 (a) VALUES (1), (2); 860INSERT INTO t2 (a,b) VALUES (1,2), (2,3); 861INSERT INTO t3 (c) VALUES (1), (2); 862 863SELECT 864 (SELECT t1.a FROM t1, t2 WHERE t1.a = t2.b AND t2.a = t3.c ORDER BY t1.a) 865 FROM t3; 866 867DROP TABLE t1, t2, t3; 868 869 870--echo # 871--echo # Bug #42760: Select doesn't return desired results when we have null 872--echo # values 873--echo # 874 875CREATE TABLE t1 ( 876 a INT, 877 c INT, 878 UNIQUE KEY a_c (a,c), 879 KEY (a)); 880 881INSERT INTO t1 VALUES (1, 10), (2, NULL); 882 883--echo # Must use ref-or-null on the a_c index 884EXPLAIN 885SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 886--echo # Must return 1 row 887SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 888 889# part 2 of the problem : DESC test cases 890--echo # Must use ref-or-null on the a_c index 891--replace_column 1 x 2 x 3 x 6 x 7 x 8 x 9 x 10 x 892EXPLAIN 893SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 894--echo # Must return 1 row 895SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 896 897 898DROP TABLE t1; 899 900 901--echo End of 5.0 tests 902 903 904# 905# Bug #35206: select query result different if the key is indexed or not 906# 907 908CREATE TABLE t2 (a varchar(32), b int(11), c float, d double, 909 UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c)); 910 911CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b)); 912CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b)); 913 914--disable_query_log 915INSERT INTO t1 (a, b) VALUES 916('domestic', 'CH'), ('domestic', 'LI'), ('plfcz1', 'FR'), ('all', 'AD'), 917('all', 'AE'), ('all', 'AF'), ('all', 'AG'), ('all', 'AI'), ('all', 'AL'), 918('all', 'AM'), ('all', 'AN'), ('all', 'AO'), ('all', 'AP'), ('all', 'AQ'), 919('all', 'AR'), ('all', 'AS'), ('all', 'AT'), ('all', 'AU'), ('all', 'AW'), 920('all', 'AZ'), ('all', 'BA'), ('all', 'BB'), ('all', 'BD'), ('all', 'BE'), 921('all', 'BF'), ('all', 'BG'), ('all', 'BH'), ('all', 'BI'), ('all', 'BJ'), 922('all', 'BM'), ('all', 'BN'), ('all', 'BO'), ('all', 'BR'), ('all', 'BS'), 923('all', 'BT'), ('all', 'BV'), ('all', 'BW'), ('all', 'BY'), ('all', 'BZ'), 924('all', 'CA'), ('all', 'CC'), ('all', 'CD'), ('all', 'CF'), ('all', 'CG'), 925('all', 'CH'), ('all', 'CI'), ('all', 'CK'), ('all', 'CL'), ('all', 'CM'), 926('all', 'CN'), ('all', 'CO'), ('all', 'CR'), ('all', 'CU'), ('all', 'CV'), 927('all', 'CX'), ('all', 'CY'), ('all', 'CZ'), ('all', 'DE'), ('all', 'DJ'), 928('all', 'DK'), ('all', 'DM'), ('all', 'DO'), ('all', 'DZ'), ('all', 'EC'), 929('all', 'EE'), ('all', 'EG'), ('all', 'EH'), ('all', 'EI'), ('all', 'ER'), 930('all', 'ES'), ('all', 'ET'), ('all', 'FI'), ('all', 'FJ'), ('all', 'FK'), 931('all', 'FM'), ('all', 'FO'), ('all', 'FR'), ('all', 'FX'), ('all', 'GA'), 932('all', 'GB'), ('all', 'GD'), ('all', 'GE'), ('all', 'GF'), ('all', 'GH'), 933('all', 'GI'), ('all', 'GL'), ('all', 'GM'), ('all', 'GN'), ('all', 'GP'), 934('all', 'GQ'), ('all', 'GR'), ('all', 'GS'), ('all', 'GT'), ('all', 'GU'), 935('all', 'GW'), ('all', 'GY'), ('all', 'HK'), ('all', 'HM'), ('all', 'HN'), 936( 'all', 'HR'), ( 'all', 'HT'), ( 'all', 'HU'), ( 'all', 'ID'), ( 'all', 'IE'), 937( 'all', 'IL'), ( 'all', 'IN'), ( 'all', 'IO'), ( 'all', 'IQ'), ( 'all', 'IR'), 938( 'all', 'IS'), ( 'all', 'IT'), ( 'all', 'JM'), ( 'all', 'JO'), ( 'all', 'JP'), 939( 'all', 'KE'), ( 'all', 'KG'), ( 'all', 'KH'), ( 'all', 'KI'), ( 'all', 'KM'), 940( 'all', 'KN'), ( 'all', 'KP'), ( 'all', 'KR'), ( 'all', 'KW'), ( 'all', 'KY'), 941( 'all', 'KZ'), ( 'all', 'LA'), ( 'all', 'LB'), ( 'all', 'LC'), ( 'all', 'LI'), 942( 'all', 'LK'), ( 'all', 'LR'), ( 'all', 'LS'), ( 'all', 'LT'), ( 'all', 'LU'), 943( 'all', 'LV'), ( 'all', 'LY'), ( 'all', 'MA'), ( 'all', 'MC'), ( 'all', 'MD'), 944( 'all', 'ME'), ( 'all', 'MG'), ( 'all', 'MH'), ( 'all', 'MK'), ( 'all', 'ML'), 945( 'all', 'MM'), ( 'all', 'MN'), ( 'all', 'MO'), ( 'all', 'MP'), ( 'all', 'MQ'), 946( 'all', 'MR'), ( 'all', 'MS'), ( 'all', 'MT'), ( 'all', 'MU'), ( 'all', 'MV'), 947( 'all', 'MW'), ( 'all', 'MX'), ( 'all', 'MY'), ( 'all', 'MZ'), ( 'all', 'NA'), 948( 'all', 'NC'), ( 'all', 'NE'), ( 'all', 'NF'), ( 'all', 'NG'), ( 'all', 'NI'), 949( 'all', 'NL'), ( 'all', 'NO'), ( 'all', 'NP'), ( 'all', 'NR'), ( 'all', 'NU'), 950( 'all', 'NV'), ( 'all', 'NZ'), ( 'all', 'OM'), ( 'all', 'PA'), ( 'all', 'PE'), 951( 'all', 'PF'), ( 'all', 'PG'), ( 'all', 'PH'), ( 'all', 'PK'), ( 'all', 'PL'), 952( 'all', 'PM'), ( 'all', 'PN'), ( 'all', 'PR'), ( 'all', 'PS'), ( 'all', 'PT'), 953( 'all', 'PW'), ( 'all', 'PY'), ( 'all', 'QA'), ( 'all', 'RE'), ( 'all', 'RO'), 954( 'all', 'RU'), ( 'all', 'RW'), ( 'all', 'SA'), ( 'all', 'SB'), ( 'all', 'SC'), 955( 'all', 'SD'), ( 'all', 'SE'), ( 'all', 'SG'), ( 'all', 'SH'), ( 'all', 'SI'), 956( 'all', 'SJ'), ( 'all', 'SK'), ( 'all', 'SL'), ( 'all', 'SM'), ( 'all', 'SN'), 957( 'all', 'SO'), ( 'all', 'SR'), ( 'all', 'ST'), ( 'all', 'SV'), ( 'all', 'SY'), 958( 'all', 'SZ'), ( 'all', 'TA'), ( 'all', 'TC'), ( 'all', 'TD'), ( 'all', 'TF'), 959( 'all', 'TG'), ( 'all', 'TH'), ( 'all', 'TJ'), ( 'all', 'TK'), ( 'all', 'TM'), 960( 'all', 'TN'), ( 'all', 'TO'), ( 'all', 'TP'), ( 'all', 'TR'), ( 'all', 'TT'), 961( 'all', 'TV'), ( 'all', 'TW'), ( 'all', 'TZ'), ( 'all', 'UA'), ( 'all', 'UG'), 962( 'all', 'UM'), ( 'all', 'US'), ( 'all', 'UY'), ( 'all', 'UZ'), ( 'all', 'VA'), 963( 'all', 'VC'), ( 'all', 'VE'), ( 'all', 'VG'), ( 'all', 'VI'), ( 'all', 'VN'), 964( 'all', 'VU'), ( 'all', 'WF'), ( 'all', 'WS'), ( 'plfcz1', 'FI'), 965( 'all', 'XE'), ( 'all', 'XS'), ( 'all', 'XU'), ( 'plfcz1', 'XE'), 966( 'all', 'YE'), ( 'all', 'YT'), ( 'all', 'YU'), ( 'all', 'ZA'), ( 'all', 'ZM'), 967( 'all', 'ZR'), ( 'all', 'ZW'), ( 'foreign', 'AD'), ( 'foreign', 'AE'), 968( 'foreign', 'AF'), ( 'foreign', 'AG'), ( 'foreign', 'AI'), 969( 'foreign', 'AL'), ( 'foreign', 'AM'), ( 'foreign', 'AN'), ( 'foreign', 'AO'), 970( 'foreign', 'AP'), ( 'foreign', 'AQ'), ( 'foreign', 'AR'), ( 'foreign', 'AS'), 971( 'foreign', 'AT'), ( 'foreign', 'AU'), ( 'foreign', 'AW'), ( 'foreign', 'AZ'), 972( 'foreign', 'BA'), ( 'foreign', 'BB'), ( 'foreign', 'BD'), ( 'foreign', 'BE'), 973( 'foreign', 'BF'), ( 'foreign', 'BG'), ( 'foreign', 'BH'), ( 'foreign', 'BI'), 974( 'foreign', 'BJ'), ( 'foreign', 'BM'), ( 'foreign', 'BN'), ( 'foreign', 'BO'), 975( 'foreign', 'BR'), ( 'foreign', 'BS'), ( 'foreign', 'BT'), ( 'foreign', 'BV'), 976( 'foreign', 'BW'), ( 'foreign', 'BY'), ( 'foreign', 'BZ'), ( 'foreign', 'CA'), 977( 'foreign', 'CC'), ( 'foreign', 'CD'), ( 'foreign', 'CF'), ( 'foreign', 'CG'), 978( 'foreign', 'CI'), ( 'foreign', 'CK'), ( 'foreign', 'CL'), ( 'foreign', 'CM'), 979( 'foreign', 'CN'), ( 'foreign', 'CO'), ( 'foreign', 'CR'), ( 'foreign', 'CU'), 980( 'foreign', 'CV'), ( 'foreign', 'CX'), ( 'foreign', 'CY'), ( 'foreign', 'CZ'), 981( 'foreign', 'DE'), ( 'foreign', 'DJ'), ( 'foreign', 'DK'), ( 'foreign', 'DM'), 982( 'foreign', 'DO'), ( 'foreign', 'DZ'), ( 'foreign', 'EC'), ( 'foreign', 'EE'), 983( 'foreign', 'EG'), ( 'foreign', 'EH'), ( 'foreign', 'EI'), ( 'foreign', 'ER'), 984( 'foreign', 'ES'), ( 'foreign', 'ET'), ( 'foreign', 'FI'), ( 'foreign', 'FJ'), 985( 'foreign', 'FK'), ( 'foreign', 'FM'), ( 'foreign', 'FO'), ( 'foreign', 'FR'), 986( 'foreign', 'FX'), ( 'foreign', 'GA'), ( 'foreign', 'GB'), ( 'foreign', 'GD'), 987( 'foreign', 'GE'), ( 'foreign', 'GF'), ( 'foreign', 'GH'), ( 'foreign', 'GI'), 988( 'foreign', 'GL'), ( 'foreign', 'GM'), ( 'foreign', 'GN'), ( 'foreign', 'GP'), 989( 'foreign', 'GQ'), ( 'foreign', 'GR'), ( 'foreign', 'GS'), ( 'foreign', 'GT'), 990( 'foreign', 'GU'), ( 'foreign', 'GW'), ( 'foreign', 'GY'), ( 'foreign', 'HK'), 991( 'foreign', 'HM'), ( 'foreign', 'HN'), ( 'foreign', 'HR'), ( 'foreign', 'HT'), 992( 'foreign', 'HU'), ( 'foreign', 'ID'), ( 'foreign', 'IE'), ( 'foreign', 'IL'), 993( 'foreign', 'IN'), ( 'foreign', 'IO'), ( 'foreign', 'IQ'), ( 'foreign', 'IR'), 994( 'foreign', 'IS'), ( 'foreign', 'IT'), ( 'foreign', 'JM'), ( 'foreign', 'JO'), 995( 'foreign', 'JP'), ( 'foreign', 'KE'), ( 'foreign', 'KG'), ( 'foreign', 'KH'), 996( 'foreign', 'KI'), ( 'foreign', 'KM'), ( 'foreign', 'KN'), ( 'foreign', 'KP'), 997( 'foreign', 'KR'), ( 'foreign', 'KW'), ( 'foreign', 'KY'), ( 'foreign', 'KZ'), 998( 'foreign', 'LA'), ( 'foreign', 'LB'), ( 'foreign', 'LC'), ( 'foreign', 'LK'), 999( 'foreign', 'LR'), ( 'foreign', 'LS'), ( 'foreign', 'LT'), ( 'foreign', 'LU'), 1000( 'foreign', 'LV'), ( 'foreign', 'LY'), ( 'foreign', 'MA'), ( 'foreign', 'MC'), 1001( 'foreign', 'MD'), ( 'foreign', 'ME'), ( 'foreign', 'MG'), ( 'foreign', 'MH'), 1002( 'foreign', 'MK'), ( 'foreign', 'ML'), ( 'foreign', 'MM'), ( 'foreign', 'MN'), 1003( 'foreign', 'MO'), ( 'foreign', 'MP'), ( 'foreign', 'MQ'), ( 'foreign', 'MR'), 1004( 'foreign', 'MS'), ( 'foreign', 'MT'), ( 'foreign', 'MU'), ( 'foreign', 'MV'), 1005( 'foreign', 'MW'), ( 'foreign', 'MX'), ( 'foreign', 'MY'), ( 'foreign', 'MZ'), 1006( 'foreign', 'NA'), ( 'foreign', 'NC'), ( 'foreign', 'NE'), ( 'foreign', 'NF'), 1007( 'foreign', 'NG'), ( 'foreign', 'NI'), ( 'foreign', 'NL'), ( 'foreign', 'NO'), 1008( 'foreign', 'NP'), ( 'foreign', 'NR'), ( 'foreign', 'NU'), ( 'foreign', 'NV'), 1009( 'foreign', 'NZ'), ( 'foreign', 'OM'), ( 'foreign', 'PA'), ( 'foreign', 'PE'), 1010( 'foreign', 'PF'), ( 'foreign', 'PG'), ( 'foreign', 'PH'), ( 'foreign', 'PK'), 1011( 'foreign', 'PL'), ( 'foreign', 'PM'), ( 'foreign', 'PN'), ( 'foreign', 'PR'), 1012( 'foreign', 'PS'), ( 'foreign', 'PT'), ( 'foreign', 'PW'), ( 'foreign', 'PY'), 1013( 'foreign', 'QA'), ( 'foreign', 'RE'), ( 'foreign', 'RO'), ( 'foreign', 'RU'), 1014( 'foreign', 'RW'), ( 'foreign', 'SA'), ( 'foreign', 'SB'), ( 'foreign', 'SC'), 1015( 'foreign', 'SD'), ( 'foreign', 'SE'), ( 'foreign', 'SG'), ( 'foreign', 'SH'), 1016( 'foreign', 'SI'), ( 'foreign', 'SJ'), ( 'foreign', 'SK'), ( 'foreign', 'SL'), 1017( 'foreign', 'SM'), ( 'foreign', 'SN'), ( 'foreign', 'SO'), ( 'foreign', 'SR'), 1018( 'foreign', 'ST'), ( 'foreign', 'SV'), ( 'foreign', 'SY'), ( 'foreign', 'SZ'), 1019( 'foreign', 'TA'), ( 'foreign', 'TC'), ( 'foreign', 'TD'), ( 'foreign', 'TF'), 1020( 'foreign', 'TG'), ( 'foreign', 'TH'), ( 'foreign', 'TJ'), ( 'foreign', 'TK'), 1021( 'foreign', 'TM'), ( 'foreign', 'TN'), ( 'foreign', 'TO'), ( 'foreign', 'TP'), 1022( 'foreign', 'TR'), ( 'foreign', 'TT'), ( 'foreign', 'TV'), ( 'foreign', 'TW'), 1023( 'foreign', 'TZ'), ( 'foreign', 'UA'), ( 'foreign', 'UG'), ( 'foreign', 'UM'), 1024( 'foreign', 'US'), ( 'foreign', 'UY'), ( 'foreign', 'UZ'), ( 'foreign', 'VA'), 1025( 'foreign', 'VC'), ( 'foreign', 'VE'), ( 'foreign', 'VG'), ( 'foreign', 'VI'), 1026( 'foreign', 'VN'), ( 'foreign', 'VU'), ( 'foreign', 'WF'), ( 'foreign', 'WS'), 1027( 'plfcz1', 'DK'), ( 'foreign', 'XE'), ( 'foreign', 'XS'), ( 'foreign', 'XU'), 1028( 'plfcz1', 'BE'), ( 'foreign', 'YE'), ( 'foreign', 'YT'), ( 'foreign', 'YU'), 1029( 'foreign', 'ZA'), ( 'foreign', 'ZM'), ( 'foreign', 'ZR'), ( 'foreign', 'ZW'), 1030( 'plfcz1', 'DE'), ( 'plfcz1', 'GI'), ( 'plfcz1', 'GR'), ( 'plfcz1', 'IS'), 1031( 'plfcz1', 'EI'), ( 'plfcz1', 'IT'), ( 'plfcz1', 'LU'), ( 'plfcz1', 'NL'), 1032( 'plfcz1', 'NO'), ( 'plfcz1', 'ES'), ( 'plfcz1', 'SE'), ( 'plfcz1', 'AL'), 1033( 'plfcz1', 'AD'), ( 'plfcz1', 'BY'), ( 'plfcz1', 'BA'), ( 'plfcz1', 'BG'), 1034( 'plfcz1', 'EE'), ( 'plfcz1', 'FO'), ( 'plfcz1', 'GL'), ( 'plfcz1', 'GB'), 1035( 'plfcz1', 'HR'), ( 'plfcz1', 'LV'), ( 'plfcz1', 'LT'), ( 'plfcz1', 'MT'), 1036( 'plfcz1', 'MK'), ( 'plfcz1', 'MD'), ( 'plfcz1', 'MC'), ( 'plfcz1', 'AT'), 1037( 'plfcz1', 'PL'), ( 'plfcz1', 'PT'), ( 'plfcz1', 'RO'), ( 'plfcz1', 'RU'), 1038( 'plfcz1', 'SM'), ( 'plfcz1', 'XS'), ( 'plfcz1', 'SK'), ( 'plfcz1', 'SI'), 1039( 'plfcz1', 'CZ'), ( 'plfcz1', 'TR'), ( 'plfcz1', 'UA'), ( 'plfcz1', 'HU'), 1040( 'plfcz1', 'VA'), ( 'plfcz1', 'CY'), ( 'plfcz2', 'AF'), ( 'plfcz2', 'DZ'), 1041( 'plfcz2', 'AS'), ( 'plfcz2', 'AO'), ( 'plfcz2', 'AI'), ( 'plfcz2', 'AQ'), 1042( 'plfcz2', 'AG'), ( 'plfcz2', 'AR'), ( 'plfcz2', 'AM'), ( 'plfcz2', 'AW'), 1043( 'plfcz2', 'AU'), ( 'plfcz2', 'AZ'), ( 'plfcz2', 'AP'), ( 'plfcz2', 'BS'), 1044( 'plfcz2', 'BH'), ( 'plfcz2', 'BD'), ( 'plfcz2', 'BB'), ( 'plfcz2', 'BZ'), 1045( 'plfcz2', 'BJ'), ( 'plfcz2', 'BM'), ( 'plfcz2', 'BT'), ( 'plfcz2', 'BO'), 1046( 'plfcz2', 'BW'), ( 'plfcz2', 'BV'), ( 'plfcz2', 'BR'), ( 'plfcz2', 'IO'), 1047( 'plfcz2', 'VG'), ( 'plfcz2', 'BN'), ( 'plfcz2', 'BF'), ( 'plfcz2', 'BI'), 1048( 'plfcz2', 'KH'), ( 'plfcz2', 'CM'), ( 'plfcz2', 'CA'), ( 'plfcz2', 'CV'), 1049( 'plfcz2', 'KY'), ( 'plfcz2', 'CF'), ( 'plfcz2', 'TD'), ( 'plfcz2', 'CL'), 1050( 'plfcz2', 'CN'), ( 'plfcz2', 'CX'), ( 'plfcz2', 'CC'), ( 'plfcz2', 'CO'), 1051( 'plfcz2', 'KM'), ( 'plfcz2', 'CG'), ( 'plfcz2', 'CD'), ( 'plfcz2', 'CK'), 1052( 'plfcz2', 'CR'), ( 'plfcz2', 'CI'), ( 'plfcz2', 'CU'), ( 'plfcz2', 'DJ'), 1053( 'plfcz2', 'DM'), ( 'plfcz2', 'DO'), ( 'plfcz2', 'TP'), ( 'plfcz2', 'EC'), 1054( 'plfcz2', 'EG'), ( 'plfcz2', 'SV'), ( 'plfcz2', 'GQ'), ( 'plfcz2', 'ER'), 1055( 'plfcz2', 'ET'), ( 'plfcz2', 'FK'), ( 'plfcz2', 'FJ'), ( 'plfcz2', 'FX'), 1056( 'plfcz2', 'GF'), ( 'plfcz2', 'PF'), ( 'plfcz2', 'TA'), ( 'plfcz2', 'TF'), 1057( 'plfcz2', 'GA'), ( 'plfcz2', 'GM'), ( 'plfcz2', 'GE'), ( 'plfcz2', 'GH'), 1058( 'plfcz2', 'GD'), ( 'plfcz2', 'GP'), ( 'plfcz2', 'GU'), ( 'plfcz2', 'GT'), 1059( 'plfcz2', 'GN'), ( 'plfcz2', 'GW'), ( 'plfcz2', 'GY'), ( 'plfcz2', 'HT'), 1060( 'plfcz2', 'HM'), ( 'plfcz2', 'HN'), ( 'plfcz2', 'HK'), ( 'plfcz2', 'IN'), 1061( 'plfcz2', 'ID'), ( 'plfcz2', 'IR'), ( 'plfcz2', 'IQ'), ( 'plfcz2', 'IE'), 1062( 'plfcz2', 'IL'), ( 'plfcz2', 'JM'), ( 'plfcz2', 'JP'), ( 'plfcz2', 'JO'), 1063( 'plfcz2', 'KZ'), ( 'plfcz2', 'KE'), ( 'plfcz2', 'KI'), ( 'plfcz2', 'KP'), 1064( 'plfcz2', 'KW'), ( 'plfcz2', 'KG'), ( 'plfcz2', 'LA'), ( 'plfcz2', 'LB'), 1065( 'plfcz2', 'LS'), ( 'plfcz2', 'LR'), ( 'plfcz2', 'LY'), ( 'plfcz2', 'MO'), 1066( 'plfcz2', 'MG'), ( 'plfcz2', 'ME'), ( 'plfcz2', 'MW'), ( 'plfcz2', 'MY'), 1067( 'plfcz2', 'MV'), ( 'plfcz2', 'ML'), ( 'plfcz2', 'MH'), ( 'plfcz2', 'MQ'), 1068( 'plfcz2', 'MR'), ( 'plfcz2', 'MU'), ( 'plfcz2', 'YT'), ( 'plfcz2', 'MX'), 1069( 'plfcz2', 'FM'), ( 'plfcz2', 'MN'), ( 'plfcz2', 'MS'), ( 'plfcz2', 'MA'), 1070( 'plfcz2', 'MZ'), ( 'plfcz2', 'MM'), ( 'plfcz2', 'NA'), ( 'plfcz2', 'NR'), 1071( 'plfcz2', 'NP'), ( 'plfcz2', 'AN'), ( 'plfcz2', 'NC'), ( 'plfcz2', 'NZ'), 1072( 'plfcz2', 'NI'), ( 'plfcz2', 'NE'), ( 'plfcz2', 'NG'), ( 'plfcz2', 'NU'), 1073( 'plfcz2', 'NF'), ( 'plfcz2', 'MP'), ( 'plfcz2', 'OM'), ( 'plfcz2', 'PK'), 1074( 'plfcz2', 'PW'), ( 'plfcz2', 'PS'), ( 'plfcz2', 'PA'), ( 'plfcz2', 'PG'), 1075( 'plfcz2', 'PY'), ( 'plfcz2', 'PE'), ( 'plfcz2', 'PH'), ( 'plfcz2', 'PN'), 1076( 'plfcz2', 'PR'), ( 'plfcz2', 'QA'), ( 'plfcz2', 'RE'), ( 'plfcz2', 'RW'), 1077( 'plfcz2', 'KN'), ( 'plfcz2', 'ST'), ( 'plfcz2', 'SA'), ( 'plfcz2', 'SN'), 1078( 'plfcz2', 'SC'), ( 'plfcz2', 'SL'), ( 'plfcz2', 'SG'), ( 'plfcz2', 'SB'), 1079( 'plfcz2', 'SO'), ( 'plfcz2', 'ZA'), ( 'plfcz2', 'GS'), ( 'plfcz2', 'KR'), 1080( 'plfcz2', 'LK'), ( 'plfcz2', 'NV'), ( 'plfcz2', 'SH'), ( 'plfcz2', 'LC'), 1081( 'plfcz2', 'PM'), ( 'plfcz2', 'VC'), ( 'plfcz2', 'SD'), ( 'plfcz2', 'SR'), 1082( 'plfcz2', 'SJ'), ( 'plfcz2', 'SZ'), ( 'plfcz2', 'SY'), ( 'plfcz2', 'TW'), 1083( 'plfcz2', 'TJ'), ( 'plfcz2', 'TZ'), ( 'plfcz2', 'TH'), ( 'plfcz2', 'TG'), 1084( 'plfcz2', 'TK'), ( 'plfcz2', 'TO'), ( 'plfcz2', 'TT'), ( 'plfcz2', 'XU'), 1085( 'plfcz2', 'TN'), ( 'plfcz2', 'TM'), ( 'plfcz2', 'TC'), ( 'plfcz2', 'TV'), 1086( 'plfcz2', 'UG'), ( 'plfcz2', 'AE'), ( 'plfcz2', 'US'), ( 'plfcz2', 'UM'), 1087( 'plfcz2', 'UY'), ( 'plfcz2', 'UZ'), ( 'plfcz2', 'VU'), ( 'plfcz2', 'VE'), 1088( 'plfcz2', 'VN'), ( 'plfcz2', 'VI'), ( 'plfcz2', 'WF'), ( 'plfcz2', 'EH'), 1089( 'plfcz2', 'WS'), ( 'plfcz2', 'YE'), ( 'plfcz2', 'YU'), ( 'plfcz2', 'ZR'), 1090( 'plfcz2', 'ZM'), ( 'plfcz2', 'ZW'), ( 'ppfcz1', 'AT'), ( 'ppfcz1', 'BE'), 1091( 'ppfcz1', 'DE'), ( 'ppfcz1', 'FR'), ( 'ppfcz1', 'FX'), ( 'ppfcz1', 'IT'), 1092( 'ppfcz1', 'LU'), ( 'ppfcz1', 'MC'), ( 'ppfcz1', 'NL'), ( 'ppfcz1', 'SM'), 1093( 'ppfcz1', 'VA'), ( 'ppfcz1', 'XE'), ( 'ppfcz2', 'AD'), ( 'ppfcz2', 'AL'), 1094( 'ppfcz2', 'BA'), ( 'ppfcz2', 'BG'), ( 'ppfcz2', 'BY'), ( 'ppfcz2', 'CY'), 1095( 'ppfcz2', 'CZ'), ( 'ppfcz2', 'DK'), ( 'ppfcz2', 'EE'), ( 'ppfcz2', 'EI'), 1096( 'ppfcz2', 'ES'), ( 'ppfcz2', 'FI'), ( 'ppfcz2', 'FO'), ( 'ppfcz2', 'GB'), 1097( 'ppfcz2', 'GI'), ( 'ppfcz2', 'GL'), ( 'ppfcz2', 'GR'), ( 'ppfcz2', 'HR'), 1098( 'ppfcz2', 'HU'), ( 'ppfcz2', 'IE'), ( 'ppfcz2', 'IS'), ( 'ppfcz2', 'LT'), 1099( 'ppfcz2', 'LV'), ( 'ppfcz2', 'MD'), ( 'ppfcz2', 'MK'), ( 'ppfcz2', 'MT'), 1100( 'ppfcz2', 'NO'), ( 'ppfcz2', 'PL'), ( 'ppfcz2', 'PT'), ( 'ppfcz2', 'RO'), 1101( 'ppfcz2', 'RU'), ( 'ppfcz2', 'SE'), ( 'ppfcz2', 'SI'), ( 'ppfcz2', 'SK'), 1102( 'ppfcz2', 'TR'), ( 'ppfcz2', 'UA'), ( 'ppfcz2', 'XS'), ( 'ppfcz2', 'YU'), 1103( 'ppfcz3', 'CA'), ( 'ppfcz3', 'DZ'), ( 'ppfcz3', 'EG'), ( 'ppfcz3', 'IL'), 1104( 'ppfcz3', 'JO'), ( 'ppfcz3', 'LB'), ( 'ppfcz3', 'LY'), ( 'ppfcz3', 'MA'), 1105( 'ppfcz3', 'MX'), ( 'ppfcz3', 'PM'), ( 'ppfcz3', 'SY'), ( 'ppfcz3', 'TN'), 1106( 'ppfcz3', 'US'), ( 'ppfcz4', 'AE'), ( 'ppfcz4', 'AF'), ( 'ppfcz4', 'AM'), 1107( 'ppfcz4', 'AO'), ( 'ppfcz4', 'AZ'), ( 'ppfcz4', 'BD'), ( 'ppfcz4', 'BF'), 1108( 'ppfcz4', 'BH'), ( 'ppfcz4', 'BI'), ( 'ppfcz4', 'BJ'), ( 'ppfcz4', 'BT'), 1109( 'ppfcz4', 'BV'), ( 'ppfcz4', 'BW'), ( 'ppfcz4', 'CF'), ( 'ppfcz4', 'CG'), 1110( 'ppfcz4', 'CI'), ( 'ppfcz4', 'CM'), ( 'ppfcz4', 'CN'), ( 'ppfcz4', 'DJ'), 1111( 'ppfcz4', 'DO'), ( 'ppfcz4', 'ER'), ( 'ppfcz4', 'ET'), ( 'ppfcz4', 'GA'), 1112( 'ppfcz4', 'GE'), ( 'ppfcz4', 'GH'), ( 'ppfcz4', 'GM'), ( 'ppfcz4', 'GN'), 1113( 'ppfcz4', 'GQ'), ( 'ppfcz4', 'GW'), ( 'ppfcz4', 'HK'), ( 'ppfcz4', 'IN'), 1114( 'ppfcz4', 'IQ'), ( 'ppfcz4', 'IR'), ( 'ppfcz4', 'JP'), ( 'ppfcz4', 'KE'), 1115( 'ppfcz4', 'KG'), ( 'ppfcz4', 'KH'), ( 'ppfcz4', 'KP'), ( 'ppfcz4', 'KW'), 1116( 'ppfcz4', 'KZ'), ( 'ppfcz4', 'LA'), ( 'ppfcz4', 'LK'), ( 'ppfcz4', 'LR'), 1117( 'ppfcz4', 'LS'), ( 'ppfcz4', 'MG'), ( 'ppfcz4', 'ML'), ( 'ppfcz4', 'MM'), 1118( 'ppfcz4', 'MN'), ( 'ppfcz4', 'MO'), ( 'ppfcz4', 'MR'), ( 'ppfcz4', 'MU'), 1119( 'ppfcz4', 'MV'), ( 'ppfcz4', 'MW'), ( 'ppfcz4', 'MY'), ( 'ppfcz4', 'MZ'), 1120( 'ppfcz4', 'NA'), ( 'ppfcz4', 'NE'), ( 'ppfcz4', 'NG'), ( 'ppfcz4', 'NP'), 1121( 'ppfcz4', 'OM'), ( 'ppfcz4', 'PK'), ( 'ppfcz4', 'QA'), ( 'ppfcz4', 'RE'), 1122( 'ppfcz4', 'RW'), ( 'ppfcz4', 'SA'), ( 'ppfcz4', 'SC'), ( 'ppfcz4', 'SD'), 1123( 'ppfcz4', 'SG'), ( 'ppfcz4', 'SH'), ( 'ppfcz4', 'SL'), ( 'ppfcz4', 'SN'), 1124( 'ppfcz4', 'SO'), ( 'ppfcz4', 'SZ'), ( 'ppfcz4', 'TD'), ( 'ppfcz4', 'TG'), 1125( 'ppfcz4', 'TH'), ( 'ppfcz4', 'TJ'), ( 'ppfcz4', 'TM'), ( 'ppfcz4', 'TW'), 1126( 'ppfcz4', 'TZ'), ( 'ppfcz4', 'UG'), ( 'ppfcz4', 'UZ'), ( 'ppfcz4', 'VN'), 1127( 'ppfcz4', 'XU'), ( 'ppfcz4', 'YT'), ( 'ppfcz4', 'ZA'), ( 'ppfcz4', 'ZW'), 1128( 'ppfcz5', 'AG'), ( 'ppfcz5', 'AI'), ( 'ppfcz5', 'AN'), ( 'ppfcz5', 'AP'), 1129( 'ppfcz5', 'AQ'), ( 'ppfcz5', 'AR'), ( 'ppfcz5', 'AS'), ( 'ppfcz5', 'AU'), 1130( 'ppfcz5', 'AW'), ( 'ppfcz5', 'BB'), ( 'ppfcz5', 'BM'), ( 'ppfcz5', 'BN'), 1131( 'ppfcz5', 'BO'), ( 'ppfcz5', 'BR'), ( 'ppfcz5', 'BS'), ( 'ppfcz5', 'BZ'), 1132( 'ppfcz5', 'CC'), ( 'ppfcz5', 'CD'), ( 'ppfcz5', 'CK'), ( 'ppfcz5', 'CL'), 1133( 'ppfcz5', 'CO'), ( 'ppfcz5', 'CR'), ( 'ppfcz5', 'CU'), ( 'ppfcz5', 'CV'), 1134( 'ppfcz5', 'CX'), ( 'ppfcz5', 'DM'), ( 'ppfcz5', 'EC'), ( 'ppfcz5', 'EH'), 1135( 'ppfcz5', 'FJ'), ( 'ppfcz5', 'FK'), ( 'ppfcz5', 'FM'), ( 'ppfcz5', 'GD'), 1136( 'ppfcz5', 'GF'), ( 'ppfcz5', 'GP'), ( 'ppfcz5', 'GS'), ( 'ppfcz5', 'GT'), 1137( 'ppfcz5', 'GU'), ( 'ppfcz5', 'GY'), ( 'ppfcz5', 'HM'), ( 'ppfcz5', 'HN'), 1138( 'ppfcz5', 'HT'), ( 'ppfcz5', 'ID'), ( 'ppfcz5', 'IO'), ( 'ppfcz5', 'JM'), 1139( 'ppfcz5', 'KI'), ( 'ppfcz5', 'KM'), ( 'ppfcz5', 'KN'), ( 'ppfcz5', 'KR'), 1140( 'ppfcz5', 'KY'), ( 'ppfcz5', 'LC'), ( 'ppfcz5', 'ME'), ( 'ppfcz5', 'MH'), 1141( 'ppfcz5', 'MP'), ( 'ppfcz5', 'MQ'), ( 'ppfcz5', 'MS'), ( 'ppfcz5', 'NC'), 1142( 'ppfcz5', 'NF'), ( 'ppfcz5', 'NI'), ( 'ppfcz5', 'NR'), ( 'ppfcz5', 'NU'), 1143( 'ppfcz5', 'NZ'), ( 'ppfcz5', 'PA'), ( 'ppfcz5', 'PE'), ( 'ppfcz5', 'PF'), 1144( 'ppfcz5', 'PG'), ( 'ppfcz5', 'PH'), ( 'ppfcz5', 'PN'), ( 'ppfcz5', 'PR'), 1145( 'ppfcz5', 'PS'), ( 'ppfcz5', 'PW'), ( 'ppfcz5', 'PY'), ( 'ppfcz5', 'SB'), 1146( 'ppfcz5', 'SJ'), ( 'ppfcz5', 'SR'), ( 'ppfcz5', 'ST'), ( 'ppfcz5', 'SV'), 1147( 'ppfcz5', 'TA'), ( 'ppfcz5', 'TC'), ( 'ppfcz5', 'TF'), ( 'ppfcz5', 'TK'), 1148( 'ppfcz5', 'TO'), ( 'ppfcz5', 'TP'), ( 'ppfcz5', 'TT'), ( 'ppfcz5', 'TV'), 1149( 'ppfcz5', 'UM'), ( 'ppfcz5', 'UY'), ( 'ppfcz5', 'VC'), ( 'ppfcz5', 'VE'), 1150( 'ppfcz5', 'VG'), ( 'ppfcz5', 'VI'), ( 'ppfcz5', 'VU'), ( 'ppfcz5', 'WF'), 1151( 'ppfcz5', 'WS'), ( 'ppfcz5', 'YE'), ( 'ppfcz5', 'ZM'), ( 'ppfcz5', 'ZR'); 1152 1153INSERT INTO t2 (a, b, c, d) VALUES 1154('domestic', 26, 0.25, 4.7), ('domestic', 27, 0.25, 6), 1155('domestic', 19, 2, 6.3), ('domestic', 19, 5, 7.77), 1156('domestic', 19, 10, 10.3), ('domestic', 19, 20, 14.83), 1157('domestic', 19, 30, 20.88), ('domestic', 20, 2, 7.3), 1158('domestic', 20, 5, 8.77), ('domestic', 20, 10, 11.3), 1159('domestic', 20, 20, 15.83), ('domestic', 20, 30, 21.88), 1160('domestic', 23, 2, 18.8), ('domestic', 23, 5, 20.8), 1161('domestic', 23, 10, 24.8), ('domestic', 23, 20, 27.8), 1162('domestic', 23, 30, 30.8), ('domestic', 24, 2, 21.1405), 1163('domestic', 24, 5, 22.3705), ('domestic', 24, 10, 25.0905), 1164('domestic', 24, 20, 29.7705), ('domestic', 24, 30, 35.9605), 1165('domestic', 17, 2, 7.2), ('domestic', 17, 5, 8.43), 1166('domestic', 17, 10, 11.15), ('domestic', 17, 20, 15.83), 1167('domestic', 17, 30, 22.02), ('domestic', 18, 2, 8.2), 1168('domestic', 18, 5, 9.43), ('domestic', 18, 10, 12.15), 1169('domestic', 18, 20, 16.83), ('domestic', 18, 30, 23.02), 1170('domestic', 28, 2, 17), ('domestic', 28, 5, 19), 1171('domestic', 28, 10, 22), ('domestic', 28, 20, 28), 1172('domestic', 28, 30, 35), ('domestic', 29, 30, 29.5), 1173('foreign', 25, 200, 0), ('domestic', 3, 100, 59), 1174('foreign', 10, 30, 0), ('foreign', 22, 0, 0), 1175('foreign', 11, 30, 0), ('foreign', 12, 30, 0), 1176('all', 1, 10000, 0), ('all', 2, 10000, 0), 1177('domestic', 9, 10000, 0), ('domestic', 4, 500, 0), 1178('domestic', 5, 500, 0), ('domestic', 6, 500, 0), 1179('domestic', 7, 500, 0), ('domestic', 8, 500, 0), 1180('domestic', 21, 3.9, 10.8), ('domestic', 21, 4.9, 12.2), 1181('domestic', 21, 9.9, 15.3), ('domestic', 21, 19.9, 20.6), 1182('domestic', 21, 30, 28.1), ('plfcz1', 16, 0.5, 19), 1183('plfcz2', 16, 0.5, 25), ( 'ppfcz2', 15, 16, 76.5), 1184( 'ppfcz2', 15, 15, 75.5), ( 'ppfcz2', 15, 14, 73.5), 1185( 'ppfcz2', 15, 13, 71.5), ( 'ppfcz2', 15, 12, 69.5), 1186( 'ppfcz2', 15, 11, 67.5), ( 'ppfcz2', 15, 10, 65.5), 1187( 'ppfcz2', 15, 9, 62.5), ( 'ppfcz2', 15, 8, 59.5), 1188( 'ppfcz2', 15, 7, 56.5), ( 'ppfcz2', 15, 6, 53.5), 1189( 'ppfcz2', 15, 5, 50.5), ( 'ppfcz2', 15, 4, 46.5), 1190( 'ppfcz2', 15, 3, 42.5), ( 'ppfcz2', 15, 2, 38.5), 1191('ppfcz1', 15, 2, 33.5), ('ppfcz1', 15, 3, 36.5), 1192('ppfcz1', 15, 4, 39.5), ('ppfcz1', 15, 5, 41.5), 1193('ppfcz1', 15, 6, 42.5), ('ppfcz1', 15, 7, 43.5), 1194('ppfcz1', 15, 8, 44.5), ('ppfcz1', 15, 9, 45.5), 1195('ppfcz1', 15, 10, 46.5), ('ppfcz1', 15, 11, 47.5), 1196( 'ppfcz1', 15, 12, 48.5), ( 'ppfcz1', 15, 13, 49.5), ( 'ppfcz1', 15, 14, 50.5), 1197( 'ppfcz1', 15, 15, 51.5), ( 'ppfcz1', 15, 16, 52.5), ( 'ppfcz1', 15, 17, 53.5), 1198( 'ppfcz1', 15, 18, 54.5), ( 'ppfcz1', 15, 19, 55.5), ( 'ppfcz1', 15, 20, 56.5), 1199( 'ppfcz1', 15, 21, 57.5), ( 'ppfcz1', 15, 22, 58.5), ( 'ppfcz1', 15, 23, 59.5), 1200( 'ppfcz1', 15, 24, 60.5), ( 'ppfcz1', 15, 25, 61.5), ( 'ppfcz1', 15, 26, 62.5), 1201( 'ppfcz1', 15, 27, 63.5), ( 'ppfcz1', 15, 28, 64.5), ( 'ppfcz1', 15, 29, 65.5), 1202( 'ppfcz1', 15, 30, 66.5), ( 'ppfcz2', 15, 17, 77.5), ( 'ppfcz2', 15, 18, 78.5), 1203( 'ppfcz2', 15, 19, 79.5), ( 'ppfcz2', 15, 20, 80.5), ( 'ppfcz2', 15, 21, 81.5), 1204( 'ppfcz2', 15, 22, 82.5), ( 'ppfcz2', 15, 23, 83.5), ( 'ppfcz2', 15, 24, 84.5), 1205( 'ppfcz2', 15, 25, 85.5), ( 'ppfcz2', 15, 26, 86.5), ( 'ppfcz2', 15, 27, 87.5), 1206( 'ppfcz2', 15, 28, 88.5), ( 'ppfcz2', 15, 29, 89.5), ( 'ppfcz2', 15, 30, 90.5), 1207( 'ppfcz3', 15, 2, 39.5), ( 'ppfcz3', 15, 3, 45.5), ( 'ppfcz3', 15, 4, 51.5), 1208( 'ppfcz3', 15, 5, 57.5), ( 'ppfcz3', 15, 6, 63.5), ( 'ppfcz3', 15, 7, 69.5), 1209( 'ppfcz3', 15, 8, 75.5), ( 'ppfcz3', 15, 9, 81.5), ( 'ppfcz3', 15, 10, 87.5), 1210( 'ppfcz3', 15, 11, 93.5), ( 'ppfcz3', 15, 12, 99.5), ( 'ppfcz3', 15, 13, 105.5), 1211( 'ppfcz3', 15, 14, 111.5), ( 'ppfcz3', 15, 15, 117.5), ( 'ppfcz3', 15, 16, 122.5), 1212( 'ppfcz3', 15, 17, 127.5), ( 'ppfcz3', 15, 18, 132.5), ( 'ppfcz3', 15, 19, 137.5), 1213( 'ppfcz3', 15, 20, 142.5), ( 'ppfcz3', 15, 21, 146.5), ( 'ppfcz3', 15, 22, 150.5), 1214( 'ppfcz3', 15, 23, 154.5), ( 'ppfcz3', 15, 24, 158.5), ( 'ppfcz3', 15, 25, 162.5), 1215( 'ppfcz3', 15, 26, 166.5), ( 'ppfcz3', 15, 27, 170.5), ( 'ppfcz3', 15, 28, 174.5), 1216( 'ppfcz3', 15, 29, 178.5), ( 'ppfcz3', 15, 30, 182.5), ( 'ppfcz4', 15, 2, 44.5), 1217( 'ppfcz4', 15, 3, 51.5), ( 'ppfcz4', 15, 4, 58.5), ( 'ppfcz4', 15, 5, 65.5), 1218( 'ppfcz4', 15, 6, 72.5), ( 'ppfcz4', 15, 7, 79.5), ( 'ppfcz4', 15, 8, 86.5), 1219( 'ppfcz4', 15, 9, 93.5), ( 'ppfcz4', 15, 10, 100.5), ( 'ppfcz4', 15, 11, 105.5), 1220( 'ppfcz4', 15, 12, 110.5), ( 'ppfcz4', 15, 13, 115.5), ( 'ppfcz4', 15, 14, 120.5), 1221( 'ppfcz4', 15, 15, 125.5), ( 'ppfcz4', 15, 16, 130.5), ( 'ppfcz4', 15, 17, 135.5), 1222( 'ppfcz4', 15, 18, 140.5), ( 'ppfcz4', 15, 19, 145.5), ( 'ppfcz4', 15, 20, 150.5), 1223( 'ppfcz4', 15, 21, 154.5), ( 'ppfcz4', 15, 22, 158.5), ( 'ppfcz4', 15, 23, 162.5), 1224( 'ppfcz4', 15, 24, 166.5), ( 'ppfcz4', 15, 25, 170.5), ( 'ppfcz4', 15, 26, 174.5), 1225( 'ppfcz4', 15, 27, 178.5), ( 'ppfcz4', 15, 28, 182.5), ( 'ppfcz4', 15, 29, 186.5), 1226( 'ppfcz4', 15, 30, 190.5), ( 'ppfcz5', 15, 2, 48.5), ( 'ppfcz5', 15, 3, 56.5), 1227( 'ppfcz5', 15, 4, 64.5), ( 'ppfcz5', 15, 5, 72.5), ( 'ppfcz5', 15, 6, 80.5), 1228( 'ppfcz5', 15, 7, 88.5), ( 'ppfcz5', 15, 8, 96.5), ( 'ppfcz5', 15, 9, 104.5), 1229( 'ppfcz5', 15, 10, 112.5), ( 'ppfcz5', 15, 11, 119.5), ( 'ppfcz5', 15, 12, 126.5), 1230( 'ppfcz5', 15, 13, 133.5), ( 'ppfcz5', 15, 14, 140.5), ( 'ppfcz5', 15, 15, 147.5), 1231( 'ppfcz5', 15, 16, 153.5), ( 'ppfcz5', 15, 17, 161.5), ( 'ppfcz5', 15, 18, 167.5), 1232( 'ppfcz5', 15, 19, 173.5), ( 'ppfcz5', 15, 20, 179.5), ( 'ppfcz5', 15, 21, 185.5), 1233( 'ppfcz5', 15, 22, 191.5), ( 'ppfcz5', 15, 23, 197.5), ( 'ppfcz5', 15, 24, 203.5), 1234( 'ppfcz5', 15, 25, 207.5), ( 'ppfcz5', 15, 26, 212.5), ( 'ppfcz5', 15, 27, 217.5), 1235( 'ppfcz5', 15, 28, 222.5), ( 'ppfcz5', 15, 29, 227.5), ( 'ppfcz5', 15, 30, 232.5), 1236( 'ppfcz1', 14, 2, 37.5), ( 'ppfcz1', 14, 3, 41.5), ( 'ppfcz1', 14, 4, 45.5), 1237( 'ppfcz1', 14, 5, 48.5), ( 'ppfcz1', 14, 6, 52.5), ( 'ppfcz1', 14, 7, 55.5), 1238( 'ppfcz1', 14, 8, 57.5), ( 'ppfcz1', 14, 9, 59.5), ( 'ppfcz1', 14, 10, 61.5), 1239( 'ppfcz1', 14, 11, 62.5), ( 'ppfcz1', 14, 12, 63.5), ( 'ppfcz1', 14, 13, 64.5), 1240( 'ppfcz1', 14, 14, 65.5), ( 'ppfcz1', 14, 15, 66.5), ( 'ppfcz1', 14, 16, 67.5), 1241( 'ppfcz1', 14, 17, 68.5), ( 'ppfcz1', 14, 18, 69.5), ( 'ppfcz1', 14, 19, 70.5), 1242( 'ppfcz1', 14, 20, 71.5), ( 'ppfcz1', 14, 21, 72.5), ( 'ppfcz1', 14, 22, 73.5), 1243( 'ppfcz1', 14, 23, 74.5), ( 'ppfcz1', 14, 24, 75.5), ( 'ppfcz1', 14, 25, 76.5), 1244( 'ppfcz1', 14, 26, 77.5), ( 'ppfcz1', 14, 27, 78.5), ( 'ppfcz1', 14, 28, 79.5), 1245( 'ppfcz1', 14, 29, 80.5), ( 'ppfcz1', 14, 30, 81.5), ( 'ppfcz2', 14, 2, 43.5), 1246( 'ppfcz2', 14, 3, 48.5), ( 'ppfcz2', 14, 4, 53.5), ( 'ppfcz2', 14, 5, 57.5), 1247( 'ppfcz2', 14, 6, 61.5), ( 'ppfcz2', 14, 7, 65.5), ( 'ppfcz2', 14, 8, 69.5), 1248( 'ppfcz2', 14, 9, 73.5), ( 'ppfcz2', 14, 10, 77.5), ( 'ppfcz2', 14, 11, 80.5), 1249( 'ppfcz2', 14, 12, 83.5), ( 'ppfcz2', 14, 13, 86.5), ( 'ppfcz2', 14, 14, 89.5), 1250( 'ppfcz2', 14, 15, 92.5), ( 'ppfcz2', 14, 16, 94.5), ( 'ppfcz2', 14, 17, 96.5), 1251( 'ppfcz2', 14, 18, 98.5), ( 'ppfcz2', 14, 19, 99.5), ( 'ppfcz2', 14, 20, 100.5), 1252( 'ppfcz2', 14, 21, 101.5), ( 'ppfcz2', 14, 22, 102.5), ( 'ppfcz2', 14, 23, 103.5), 1253( 'ppfcz2', 14, 24, 104.5), ( 'ppfcz2', 14, 25, 105.5), ( 'ppfcz2', 14, 26, 106.5), 1254( 'ppfcz2', 14, 27, 107.5), ( 'ppfcz2', 14, 28, 108.5), ( 'ppfcz2', 14, 29, 109.5), 1255( 'ppfcz2', 14, 30, 110.5), ( 'ppfcz3', 14, 2, 47.5), ( 'ppfcz3', 14, 3, 56.5), 1256( 'ppfcz3', 14, 4, 67.5), ( 'ppfcz3', 14, 5, 78.5), ( 'ppfcz3', 14, 6, 87.5), 1257( 'ppfcz3', 14, 7, 96.5), ( 'ppfcz3', 14, 8, 105.5), ( 'ppfcz3', 14, 9, 114.5), 1258( 'ppfcz3', 14, 10, 123.5), ( 'ppfcz3', 14, 11, 131.5), ( 'ppfcz3', 14, 12, 139.5), 1259( 'ppfcz3', 14, 13, 147.5), ( 'ppfcz3', 14, 14, 155.5), ( 'ppfcz3', 14, 15, 163.5), 1260( 'ppfcz3', 14, 16, 171.5), ( 'ppfcz3', 14, 17, 179.5), ( 'ppfcz3', 14, 18, 187.5), 1261( 'ppfcz3', 14, 19, 195.5), ( 'ppfcz3', 14, 20, 203.5), ( 'ppfcz3', 14, 21, 210.5), 1262( 'ppfcz3', 14, 22, 217.5), ( 'ppfcz3', 14, 23, 224.5), ( 'ppfcz3', 14, 24, 231.5), 1263( 'ppfcz3', 14, 25, 238.5), ( 'ppfcz3', 14, 26, 245.5), ( 'ppfcz3', 14, 27, 252.5), 1264( 'ppfcz3', 14, 28, 259.5), ( 'ppfcz3', 14, 29, 266.5), ( 'ppfcz3', 14, 30, 273.5), 1265( 'ppfcz4', 14, 2, 54.5), ( 'ppfcz4', 14, 3, 68.5), ( 'ppfcz4', 14, 4, 81.5), 1266( 'ppfcz4', 14, 5, 95.5), ( 'ppfcz4', 14, 6, 108.5), ( 'ppfcz4', 14, 7, 121.5), 1267( 'ppfcz4', 14, 8, 134.5), ( 'ppfcz4', 14, 9, 147.5), ( 'ppfcz4', 14, 10, 160.5), 1268( 'ppfcz4', 14, 11, 168.5), ( 'ppfcz4', 14, 12, 178.5), ( 'ppfcz4', 14, 13, 188.5), 1269( 'ppfcz4', 14, 14, 198.5), ( 'ppfcz4', 14, 15, 208.5), ( 'ppfcz4', 14, 16, 216.5), 1270( 'ppfcz4', 14, 17, 224.5), ( 'ppfcz4', 14, 18, 232.5), ( 'ppfcz4', 14, 19, 240.5), 1271( 'ppfcz4', 14, 20, 248.5), ( 'ppfcz4', 14, 21, 256.5), ( 'ppfcz4', 14, 22, 264.5), 1272( 'ppfcz4', 14, 23, 272.5), ( 'ppfcz4', 14, 24, 280.5), ( 'ppfcz4', 14, 25, 288.5), 1273( 'ppfcz4', 14, 26, 296.5), ( 'ppfcz4', 14, 27, 304.5), ( 'ppfcz4', 14, 28, 312.5), 1274( 'ppfcz4', 14, 29, 320.5), ( 'ppfcz4', 14, 30, 328.5), ( 'ppfcz5', 14, 2, 66.5), 1275( 'ppfcz5', 14, 3, 84.5), ( 'ppfcz5', 14, 4, 102.5), ( 'ppfcz5', 14, 5, 120.5), 1276( 'ppfcz5', 14, 6, 137.5), ( 'ppfcz5', 14, 7, 154.5), ( 'ppfcz5', 14, 8, 171.5), 1277( 'ppfcz5', 14, 9, 188.5), ( 'ppfcz5', 14, 10, 205.5), ( 'ppfcz5', 14, 11, 220.5), 1278( 'ppfcz5', 14, 12, 235.5), ( 'ppfcz5', 14, 13, 250.5), ( 'ppfcz5', 14, 14, 265.5), 1279( 'ppfcz5', 14, 15, 280.5), ( 'ppfcz5', 14, 16, 295.5), ( 'ppfcz5', 14, 17, 310.5), 1280( 'ppfcz5', 14, 18, 325.5), ( 'ppfcz5', 14, 19, 340.5), ( 'ppfcz5', 14, 20, 355.5), 1281( 'ppfcz5', 14, 21, 368.5), ( 'ppfcz5', 14, 22, 381.5), ( 'ppfcz5', 14, 23, 394.5), 1282( 'ppfcz5', 14, 24, 407.5), ( 'ppfcz5', 14, 25, 420.5), ( 'ppfcz5', 14, 26, 433.5), 1283( 'ppfcz5', 14, 27, 446.5), ( 'ppfcz5', 14, 28, 459.5), ( 'ppfcz5', 14, 29, 472.5), 1284( 'ppfcz5', 14, 30, 485.5), ( 'ppfcz1', 30, 0.5, 56.5), ( 'ppfcz1', 30, 1, 63.5), 1285( 'ppfcz1', 30, 1.5, 69.5), ( 'ppfcz1', 30, 2, 75.5), ( 'ppfcz1', 30, 2.5, 80.5), 1286( 'ppfcz1', 30, 3, 86.5), ( 'ppfcz1', 30, 3.5, 92.5), ( 'ppfcz1', 30, 4, 99.5), 1287( 'ppfcz1', 30, 4.5, 105.5), ( 'ppfcz1', 30, 5, 111.5), ( 'ppfcz1', 30, 6, 118.5), 1288( 'ppfcz1', 30, 7, 126.5), ( 'ppfcz1', 30, 8, 133.5), ( 'ppfcz1', 30, 9, 141.5), 1289( 'ppfcz1', 30, 10, 148.5), ( 'ppfcz1', 30, 11, 156.5), ( 'ppfcz1', 30, 12, 163.5), 1290( 'ppfcz1', 30, 13, 171.5), ( 'ppfcz1', 30, 14, 178.5), ( 'ppfcz1', 30, 15, 186.5), 1291( 'ppfcz1', 30, 16, 193.5), ( 'ppfcz1', 30, 17, 201.5), ( 'ppfcz1', 30, 18, 209.5), 1292( 'ppfcz1', 30, 19, 216.5), ( 'ppfcz1', 30, 20, 224.5), ( 'ppfcz1', 30, 21, 231.5), 1293( 'ppfcz1', 30, 22, 239.5), ( 'ppfcz1', 30, 23, 246.5), ( 'ppfcz1', 30, 24, 254.5), 1294( 'ppfcz1', 30, 25, 261.5), ( 'ppfcz1', 30, 26, 269.5), ( 'ppfcz1', 30, 27, 276.5), 1295( 'ppfcz1', 30, 28, 284.5), ( 'ppfcz1', 30, 29, 291.5), ( 'ppfcz1', 30, 30, 299.5), 1296( 'ppfcz2', 30, 0.5, 61.5), ( 'ppfcz2', 30, 1, 65.5), ( 'ppfcz2', 30, 1.5, 75.5), 1297( 'ppfcz2', 30, 2, 80.5), ( 'ppfcz2', 30, 2.5, 86.5), ( 'ppfcz2', 30, 3, 99.5), 1298( 'ppfcz2', 30, 3.5, 109.5), ( 'ppfcz2', 30, 4, 113.5), ( 'ppfcz2', 30, 4.5, 121.5), 1299( 'ppfcz2', 30, 5, 129.5), ( 'ppfcz2', 30, 6, 139.5), ( 'ppfcz2', 30, 7, 149.5), 1300( 'ppfcz2', 30, 8, 159.5), ( 'ppfcz2', 30, 9, 169.5), ( 'ppfcz2', 30, 10, 180.5), 1301( 'ppfcz2', 30, 11, 189.5), ( 'ppfcz2', 30, 12, 199.5), ( 'ppfcz2', 30, 13, 210.5), 1302( 'ppfcz2', 30, 14, 219.5), ( 'ppfcz2', 30, 15, 229.5), ( 'ppfcz2', 30, 16, 240.5), 1303( 'ppfcz2', 30, 17, 249.5), ( 'ppfcz2', 30, 18, 259.5), ( 'ppfcz2', 30, 19, 270.5), 1304( 'ppfcz2', 30, 20, 280.5), ( 'ppfcz2', 30, 21, 289.5), ( 'ppfcz2', 30, 22, 300.5), 1305( 'ppfcz2', 30, 23, 310.5), ( 'ppfcz2', 30, 24, 320.5), ( 'ppfcz2', 30, 25, 330.5), 1306( 'ppfcz2', 30, 26, 340.5), ( 'ppfcz2', 30, 27, 350.5), ( 'ppfcz2', 30, 28, 360.5), 1307( 'ppfcz2', 30, 29, 370.5), ( 'ppfcz2', 30, 30, 381.5), ( 'ppfcz3', 30, 0.5, 74.5), 1308( 'ppfcz3', 30, 1, 83.5), ( 'ppfcz3', 30, 1.5, 90.5), ( 'ppfcz3', 30, 2, 99.5), 1309( 'ppfcz3', 30, 2.5, 107.5), ( 'ppfcz3', 30, 3, 114.5), ( 'ppfcz3', 30, 3.5, 122.5), 1310( 'ppfcz3', 30, 4, 130.5), ( 'ppfcz3', 30, 4.5, 140.5), ( 'ppfcz3', 30, 5, 147.5), 1311( 'ppfcz3', 30, 6, 162.5), ( 'ppfcz3', 30, 7, 174.5), ( 'ppfcz3', 30, 8, 188.5), 1312( 'ppfcz3', 30, 9, 201.5), ( 'ppfcz3', 30, 10, 213.5), ( 'ppfcz3', 30, 11, 227.5), 1313( 'ppfcz3', 30, 12, 240.5), ( 'ppfcz3', 30, 13, 252.5), ( 'ppfcz3', 30, 14, 266.5), 1314( 'ppfcz3', 30, 15, 278.5), ( 'ppfcz3', 30, 16, 290.5), ( 'ppfcz3', 30, 17, 304.5), 1315( 'ppfcz3', 30, 18, 317.5), ( 'ppfcz3', 30, 19, 330.5), ( 'ppfcz3', 30, 20, 343.5), 1316( 'ppfcz3', 30, 21, 354.5), ( 'ppfcz3', 30, 22, 363.5), ( 'ppfcz3', 30, 23, 375.5), 1317( 'ppfcz3', 30, 24, 385.5), ( 'ppfcz3', 30, 25, 396.5), ( 'ppfcz3', 30, 26, 405.5), 1318( 'ppfcz3', 30, 27, 417.5), ( 'ppfcz3', 30, 28, 428.5), ( 'ppfcz3', 30, 29, 438.5), 1319( 'ppfcz3', 30, 30, 448.5), ( 'ppfcz4', 30, 0.5, 90.5), ( 'ppfcz4', 30, 1, 104.5), 1320( 'ppfcz4', 30, 1.5, 118.5), ( 'ppfcz4', 30, 2, 134.5), ( 'ppfcz4', 30, 2.5, 146.5), 1321( 'ppfcz4', 30, 3, 163.5), ( 'ppfcz4', 30, 3.5, 179.5), ( 'ppfcz4', 30, 4, 195.5), 1322( 'ppfcz4', 30, 4.5, 211.5), ( 'ppfcz4', 30, 5, 232.5), ( 'ppfcz4', 30, 6, 257.5), 1323( 'ppfcz4', 30, 7, 278.5), ( 'ppfcz4', 30, 8, 300.5), ( 'ppfcz4', 30, 9, 321.5), 1324( 'ppfcz4', 30, 10, 343.5), ( 'ppfcz4', 30, 11, 364.5), ( 'ppfcz4', 30, 12, 386.5), 1325( 'ppfcz4', 30, 13, 407.5), ( 'ppfcz4', 30, 14, 429.5), ( 'ppfcz4', 30, 15, 450.5), 1326( 'ppfcz4', 30, 16, 472.5), ( 'ppfcz4', 30, 17, 493.5), ( 'ppfcz4', 30, 18, 515.5), 1327( 'ppfcz4', 30, 19, 536.5), ( 'ppfcz4', 30, 20, 558.5), ( 'ppfcz4', 30, 21, 579.5), 1328( 'ppfcz4', 30, 22, 601.5), ( 'ppfcz4', 30, 23, 622.5), ( 'ppfcz4', 30, 24, 644.5), 1329( 'ppfcz4', 30, 25, 665.5), ( 'ppfcz4', 30, 26, 687.5), ( 'ppfcz4', 30, 27, 708.5), 1330( 'ppfcz4', 30, 28, 730.5), ( 'ppfcz4', 30, 29, 751.5), ( 'ppfcz4', 30, 30, 773.5), 1331( 'ppfcz5', 30, 0.5, 97.5), ( 'ppfcz5', 30, 1, 114.5), ( 'ppfcz5', 30, 1.5, 131.5), 1332( 'ppfcz5', 30, 2, 148.5), ( 'ppfcz5', 30, 2.5, 165.5), ( 'ppfcz5', 30, 3, 183.5), 1333( 'ppfcz5', 30, 3.5, 200.5), ( 'ppfcz5', 30, 4, 221.5), ( 'ppfcz5', 30, 4.5, 243.5), 1334( 'ppfcz5', 30, 5, 264.5), ( 'ppfcz5', 30, 6, 289.5), ( 'ppfcz5', 30, 7, 313.5), 1335( 'ppfcz5', 30, 8, 336.5), ( 'ppfcz5', 30, 9, 360.5), ( 'ppfcz5', 30, 10, 384.5), 1336( 'ppfcz5', 30, 11, 407.5), ( 'ppfcz5', 30, 12, 431.5), ( 'ppfcz5', 30, 13, 455.5), 1337( 'ppfcz5', 30, 14, 478.5), ( 'ppfcz5', 30, 15, 502.5), ( 'ppfcz5', 30, 16, 526.5), 1338( 'ppfcz5', 30, 17, 549.5), ( 'ppfcz5', 30, 18, 573.5), ( 'ppfcz5', 30, 19, 597.5), 1339( 'ppfcz5', 30, 20, 620.5), ( 'ppfcz5', 30, 21, 644.5), ( 'ppfcz5', 30, 22, 668.5), 1340( 'ppfcz5', 30, 23, 691.5), ( 'ppfcz5', 30, 24, 715.5), ( 'ppfcz5', 30, 25, 738.5), 1341( 'ppfcz5', 30, 26, 762.5), ( 'ppfcz5', 30, 27, 786.5), ( 'ppfcz5', 30, 28, 809.5), 1342( 'ppfcz5', 30, 29, 833.5), ( 'ppfcz5', 30, 30, 857.5), ( 'foreign', 13, 30, 0), 1343( 'all', 32, 10000, 23.2342007434944); 1344 1345--enable_query_log 1346 1347INSERT INTO t3 SELECT * FROM t1; 1348 1349EXPLAIN 1350SELECT d FROM t1, t2 1351WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1352ORDER BY t2.c LIMIT 1; 1353SELECT d FROM t1, t2 1354WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1355ORDER BY t2.c LIMIT 1; 1356 1357EXPLAIN 1358SELECT d FROM t3 AS t1, t2 AS t2 1359WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1360ORDER BY t2.c LIMIT 1; 1361SELECT d FROM t3 AS t1, t2 AS t2 1362WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' 1363ORDER BY t2.c LIMIT 1; 1364 1365DROP TABLE t1,t2,t3; 1366 1367--echo # 1368--echo # WL#1393 - Optimizing filesort with small limit 1369--echo # 1370 1371CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200)); 1372INSERT INTO t1(f1, f2) VALUES 1373(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), 1374(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), 1375(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), 1376(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), 1377(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), 1378(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), 1379(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), 1380(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), 1381(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), 1382(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), 1383(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), 1384(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), 1385(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), 1386(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), 1387(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), 1388(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), 1389(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), 1390(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), 1391(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), 1392(96,"96"),(97,"97"),(98,"98"),(99,"99"); 1393 1394################ 1395## Test sort when source data fits in memory 1396 1397SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100; 1398SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; 1399SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; 1400SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; 1401SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; 1402SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; 1403SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; 1404SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; 1405SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; 1406 1407################ 1408## Test sort when source data does not fit in memory 1409set sort_buffer_size= 32768; 1410CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)); 1411INSERT INTO tmp SELECT f1, f2 FROM t1; 1412INSERT INTO t1(f1, f2) SELECT * FROM tmp; 1413INSERT INTO tmp SELECT f1, f2 FROM t1; 1414INSERT INTO t1(f1, f2) SELECT * FROM tmp; 1415 1416SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; 1417SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; 1418SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; 1419SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; 1420SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; 1421SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; 1422SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; 1423SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; 1424 1425################ 1426## Test with SQL_CALC_FOUND_ROWS 1427set sort_buffer_size= 32768; 1428SELECT SQL_CALC_FOUND_ROWS * FROM t1 1429ORDER BY f1, f0 LIMIT 30; 1430SELECT FOUND_ROWS(); 1431 1432SELECT SQL_CALC_FOUND_ROWS * FROM t1 1433ORDER BY f1, f0 LIMIT 0; 1434SELECT FOUND_ROWS(); 1435 1436SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 1437ORDER BY f2, f0 LIMIT 20; 1438SELECT FOUND_ROWS(); 1439 1440SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 1441ORDER BY f2, f0 LIMIT 0; 1442SELECT FOUND_ROWS(); 1443 1444SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 1445ORDER BY f2, f0 LIMIT 10 OFFSET 10; 1446SELECT FOUND_ROWS(); 1447 1448SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 1449ORDER BY f2, f0 LIMIT 0 OFFSET 10; 1450SELECT FOUND_ROWS(); 1451 1452################ 1453## Test sorting with join 1454## These are re-written to use PQ during execution. 1455set sort_buffer_size= 327680; 1456 1457SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 1458ORDER BY tmp.f1, f0 LIMIT 30; 1459 1460SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 1461ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; 1462 1463SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 1464ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; 1465SELECT FOUND_ROWS(); 1466 1467SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 1468WHERE t1.f2>20 1469ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; 1470SELECT FOUND_ROWS(); 1471 1472################ 1473## Test views 1474CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30; 1475SELECT * FROM v1; 1476drop view v1; 1477 1478CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100; 1479SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; 1480 1481CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100; 1482SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0 1483LIMIT 30; 1484 1485################ 1486## Test group & having 1487SELECT floor(f1/10) f3, count(f2) FROM t1 1488GROUP BY 1 ORDER BY 2,1 LIMIT 5; 1489 1490SELECT floor(f1/10) f3, count(f2) FROM t1 1491GROUP BY 1 ORDER BY 2,1 LIMIT 0; 1492 1493################ 1494## Test SP 1495delimiter |; 1496CREATE PROCEDURE wl1393_sp_test() 1497BEGIN 1498SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30; 1499SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15; 1500SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 1501ORDER BY f2, f0 LIMIT 15 OFFSET 15; 1502SELECT FOUND_ROWS(); 1503SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; 1504END| 1505CALL wl1393_sp_test()| 1506DROP PROCEDURE wl1393_sp_test| 1507delimiter ;| 1508 1509################ 1510## Test with subqueries 1511SELECT d1.f1, d1.f2 FROM t1 1512LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1 1513ORDER BY d1.f2 DESC LIMIT 30; 1514 1515SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1); 1516 1517--error ER_SUBQUERY_NO_1_ROW 1518SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2); 1519 1520DROP TABLE t1, tmp; 1521DROP VIEW v1, v2; 1522 1523--echo # end of WL#1393 - Optimizing filesort with small limit 1524 1525--echo # 1526--echo # Bug #58761 1527--echo # Crash in Field::is_null in field.h on subquery in WHERE clause 1528--echo # 1529 1530CREATE TABLE t1 ( 1531 pk INT NOT NULL AUTO_INCREMENT, 1532 col_int_key INT DEFAULT NULL, 1533 col_varchar_key VARCHAR(1) DEFAULT NULL, 1534 PRIMARY KEY (pk), 1535 KEY col_varchar_key (col_varchar_key,col_int_key) 1536); 1537 1538INSERT INTO t1 VALUES (27,7,'x'); 1539INSERT INTO t1 VALUES (28,6,'m'); 1540INSERT INTO t1 VALUES (29,4,'c'); 1541 1542CREATE TABLE where_subselect 1543 SELECT DISTINCT `pk` AS field1 , `pk` AS field2 1544 FROM t1 AS alias1 1545 WHERE alias1 . `col_int_key` > 229 1546 OR alias1 . `col_varchar_key` IS NOT NULL 1547 GROUP BY field1, field2 1548; 1549 1550SELECT * 1551FROM where_subselect 1552WHERE (field1, field2) IN ( 1553 SELECT DISTINCT `pk` AS field1 , `pk` AS field2 1554 FROM t1 AS alias1 1555 WHERE alias1 . `col_int_key` > 229 1556 OR alias1 . `col_varchar_key` IS NOT NULL 1557 GROUP BY field1, field2 1558); 1559 1560DROP TABLE t1; 1561DROP TABLE where_subselect; 1562 1563--echo # End of Bug #58761 1564 1565# 1566# Bug#35844: Covering index for ref access not compatible with ORDER BY list 1567# 1568 1569CREATE TABLE t1 ( 1570 id1 INT NULL, 1571 id2 INT NOT NULL, 1572 junk INT NOT NULL, 1573 PRIMARY KEY (id1, id2, junk), 1574 INDEX id2_j_id1 (id2, junk, id1) 1575); 1576 1577INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4); 1578INSERT INTO t1 VALUES (5, 2, 1), (6, 2, 2), (7, 2, 3), (8, 2, 4); 1579INSERT INTO t1 VALUES (9, 3, 1), (10, 3, 2), (11, 3, 3), (12, 3, 4); 1580INSERT INTO t1 VALUES (13, 4, 1), (14, 4, 2), (15, 4, 3), (16, 4, 4); 1581INSERT INTO t1 VALUES (17, 5, 1), (18, 5, 2), (19, 5, 3), (20, 5, 4); 1582INSERT INTO t1 VALUES (21, 6, 1), (22, 6, 2), (23, 6, 3), (24, 6, 4); 1583INSERT INTO t1 VALUES (25, 7, 1), (26, 7, 2), (27, 7, 3), (28, 7, 4); 1584INSERT INTO t1 VALUES (29, 8, 1), (30, 8, 2), (31, 8, 3), (32, 8, 4); 1585INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4); 1586 1587EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; 1588 1589SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; 1590 1591DROP TABLE t1; 1592 1593 1594 1595# 1596# Bug#46454: MySQL wrong index optimisation leads to incorrect result & crashes 1597# 1598CREATE TABLE t1 ( 1599 a INT, 1600 b INT NOT NULL, 1601 c char(100), 1602 KEY (b, c), 1603 KEY (b, a, c) 1604) 1605DEFAULT CHARSET = utf8; 1606 1607INSERT INTO t1 VALUES 1608(1, 1, 1), 1609(2, 2, 2), 1610(3, 3, 3), 1611(4, 4, 4), 1612(5, 5, 5), 1613(6, 6, 6), 1614(7, 7, 7), 1615(8, 8, 8), 1616(9, 9, 9); 1617 1618INSERT INTO t1 SELECT a + 10, b, c FROM t1; 1619INSERT INTO t1 SELECT a + 20, b, c FROM t1; 1620INSERT INTO t1 SELECT a + 40, b, c FROM t1; 1621INSERT INTO t1 SELECT a + 80, b, c FROM t1; 1622INSERT INTO t1 SELECT a + 160, b, c FROM t1; 1623INSERT INTO t1 SELECT a + 320, b, c FROM t1; 1624INSERT INTO t1 SELECT a + 640, b, c FROM t1; 1625INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; 1626 1627EXPLAIN 1628SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; 1629SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; 1630 1631EXPLAIN 1632SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; 1633SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; 1634 1635DROP TABLE t1; 1636 1637--echo # 1638--echo # Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering 1639--echo # is used 1640--echo # 1641 1642CREATE TABLE t1 (a INT, b INT, KEY (a)); 1643 1644INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL); 1645INSERT INTO t1 SELECT a+4, b FROM t1; 1646INSERT INTO t1 SELECT a+8, b FROM t1; 1647 1648CREATE TABLE t2 (a INT, b INT); 1649 1650INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL); 1651INSERT INTO t2 SELECT a+4, b FROM t2; 1652 1653--echo # shouldn't have "using filesort" 1654EXPLAIN 1655SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; 1656 1657--echo # should have "using filesort" 1658EXPLAIN 1659SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; 1660 1661--echo # should have "using filesort" 1662EXPLAIN 1663SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; 1664 1665DROP TABLE t1, t2; 1666 1667--echo # 1668--echo # Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and 1669--echo # ORDER BY computed col 1670--echo # 1671CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, KEY( a, b ) ); 1672 1673INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 1674INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; 1675 1676CREATE TABLE t2( a INT PRIMARY KEY, b INT ); 1677 1678INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 1679INSERT INTO t2 SELECT a + 5, b + 5 FROM t2; 1680 1681EXPLAIN 1682SELECT count(*) AS c, t1.a 1683FROM t1 JOIN t2 ON t1.b = t2.a 1684WHERE t2.b = 1 1685GROUP BY t1.a 1686ORDER by c 1687LIMIT 2; 1688 1689DROP TABLE t1, t2; 1690 1691 1692--echo # 1693--echo # Bug #59110: Memory leak of QUICK_SELECT_I allocated memory 1694--echo # and 1695--echo # Bug #59308: Incorrect result for 1696--echo SELECT DISTINCT <col>... ORDER BY <col> DESC 1697--echo 1698--echo # Use Valgrind to detect #59110! 1699--echo # 1700 1701CREATE TABLE t1 (a INT,KEY (a)); 1702INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 1703 1704EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; 1705SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; 1706 1707DROP TABLE t1; 1708 1709--echo # 1710--echo # Bug#11765255 58201: 1711--echo # VALGRIND/CRASH WHEN ORDERING BY MULTIPLE AGGREGATE FUNCTIONS 1712--echo # 1713 1714select 1 order by max(1) + min(1); 1715 1716--echo End of 5.1 tests 1717 1718 1719--echo # 1720--echo # Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY 1721--echo # when it should use index 1722--echo # 1723 1724CREATE TABLE t1 (i1 integer NOT NULL PRIMARY KEY); 1725CREATE TABLE t2 (i2 integer NOT NULL PRIMARY KEY); 1726CREATE TABLE t3 (i3 integer); 1727 1728INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12); 1729INSERT INTO t2 SELECT * FROM t1; 1730 1731EXPLAIN EXTENDED 1732SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 1733 LEFT JOIN t3 ON t2.i2 = t3.i3 1734 ORDER BY t1.i1 LIMIT 5; 1735 1736SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 1737 LEFT JOIN t3 ON t2.i2 = t3.i3 1738 ORDER BY t1.i1 LIMIT 5; 1739 1740DROP TABLE t1, t2, t3; 1741 1742--echo # 1743--echo # Bug #11885377 VOID JOIN_READ_KEY_UNLOCK_ROW(ST_JOIN_TABLE*): ASSERTION 1744--echo # `TAB->REF.USE_COUNT' 1745--echo # 1746 1747CREATE TABLE t1(a INT PRIMARY KEY); 1748CREATE TABLE t2(b INT,c INT); 1749INSERT INTO t1 VALUES (1), (2); 1750INSERT INTO t2 VALUES (1,2), (2,3); 1751SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1 ORDER BY a DESC) FROM t2; 1752DROP TABLE t1, t2; 1753 1754--echo # 1755--echo # Bug #13531865 1756--echo # TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF 1757--echo # 'TYPE' IS REF_OR_NULL 1758--echo # 1759--echo # 1760 1761CREATE TABLE t1 ( 1762 a INT, 1763 c INT, 1764 UNIQUE KEY a_c (a,c), 1765 KEY (a)) engine=myisam; 1766 1767INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10); 1768ANALYZE TABLE t1; 1769 1770--echo # Using 'KEY a_c' for order-by opt, would have required 1771--echo # REF_OR_NULL access which never can be order_by skipped. 1772--echo # -> Keep initial REF on 'KEY a' selected by cond. optimizer 1773EXPLAIN 1774SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL); 1775EXPLAIN 1776SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 1777SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; 1778 1779EXPLAIN 1780SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 1781SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; 1782 1783DROP TABLE t1; 1784 1785--echo # 1786--echo # Bug #13528826 1787--echo # TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT' 1788--echo # 1789--echo # 1790 1791CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam; 1792INSERT INTO t1 VALUES 1793 (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); 1794CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam; 1795INSERT INTO t2 VALUES 1796 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3), 1797 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1); 1798 1799--echo # number of rows in t1 was incorrectly used as an 1800--echo # implicit limit-clause if not explicit specified 1801EXPLAIN 1802SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a; 1803 1804--echo # Query above used to be explained identical to this: 1805EXPLAIN 1806SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8; 1807 1808-- echo # A really high limit was required to give the correct explain 1809EXPLAIN 1810SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000; 1811 1812DROP TABLE t1, t2; 1813 1814--echo # 1815--echo # Bug #13949068 ASSERT TAB->REF.KEY == REF_KEY IN 1816--echo # PLAN_CHANGE_WATCHDOG::~PLAN_CHANGE_WATCHDOG 1817--echo # 1818CREATE TABLE t1 (a INT, b INT, KEY(b), KEY(b,a)) ENGINE=INNODB; 1819INSERT INTO t1 VALUES (0,0); 1820let $query=SELECT DISTINCT a FROM t1 WHERE b=1 ORDER BY 1; 1821eval EXPLAIN $query; 1822eval $query; 1823DROP TABLE t1; 1824
