~drizzle-trunk/drizzle/development : contents of mysql-test/t/order

~drizzle-trunk/drizzle/development : (revision 33)

1 by brian clean slate	1	#
	2	# Bug with order by
	3	#
	4
	5	--disable_warnings
	6	drop table if exists t1,t2,t3;
	7	--enable_warnings
	8
	9	CREATE 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
	19	INSERT INTO t1 VALUES (2,4,'6067169d','Y');
	20	INSERT INTO t1 VALUES (2,5,'606716d1','Y');
	21	INSERT INTO t1 VALUES (2,1,'606717c1','Y');
	22	INSERT INTO t1 VALUES (3,1,'6067178d','Y');
	23	INSERT INTO t1 VALUES (2,6,'60671515','Y');
	24	INSERT INTO t1 VALUES (2,7,'60671569','Y');
	25	INSERT INTO t1 VALUES (2,3,'dd','Y');
	26
	27	CREATE 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
	41	INSERT INTO t2 VALUES (1,'Emettre un appel d''offres','en_construction.html',10,'emettre.gif');
	42	INSERT INTO t2 VALUES (2,'Emettre des soumissions','en_construction.html',20,'emettre.gif');
	43	INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif');
	44	INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif');
	45	INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif');
	46	INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif');
	47	INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif');
	48	INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif');
	49	INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif');
	50	INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif');
	51
	52
	53	select t1.id,t1.idservice,t2.ordre,t2.description from t1, t2 where t1.id = 2 and t1.idservice = t2.id order by t2.ordre;
	54
	55	drop table t1,t2;
	56
	57	#
	58	# Test of ORDER BY on concat() result
	59	#
	60
	61	create table t1 (first char(10),last char(10));
	62	insert into t1 values ("Michael","Widenius");
	63	insert into t1 values ("Allan","Larsson");
	64	insert into t1 values ("David","Axmark");
65	select concat(first," ",last) as name from t1 order by name;
66	select concat(last," ",first) as name from t1 order by name;
67	drop table t1;
68
69	#
70	# bug in distinct + order by
71	#
72
73	create table t1 (i int);
74	insert into t1 values(1),(2),(1),(2),(1),(2),(3);
75	select distinct i from t1;
76	select distinct i from t1 order by rand(5);
77	select distinct i from t1 order by i desc;
78	select distinct i from t1 order by 1-i;
79	select distinct i from t1 order by mod(i,2),i;
80	drop table t1;
81
82	#
83	# bug#3681
84	#
85
86	create table t1 ( pk int primary key, name varchar(255) not null, number varchar(255) not null);
87	insert into t1 values (1, 'Gamma', '123'), (2, 'Gamma Ext', '123a'), (3, 'Alpha', '001'), (4, 'Beta', '200c');
88	select distinct t1.name as 'Building Name',t1.number as 'Building Number' from t1 order by t1.name asc;
89	drop table t1;
90
91
92	#
93	# Order by on first index part
94	#
95
96	create table t1 (id int not null,col1 int not null,col2 int not null,index(col1));
97	insert into t1 values(1,2,2),(2,2,1),(3,1,2),(4,1,1),(5,1,4),(6,2,3),(7,3,1),(8,2,4);
98	select * from t1 order by col1,col2;
99	select col1 from t1 order by id;
100	select col1 as id from t1 order by id;
101	select concat(col1) as id from t1 order by id;
102	drop table t1;
103
104	#
105	# Test of order by on field()
106	#
107
108	CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta timestamp);
109	insert into t1 (aika) values ('Keskiviikko');
110	insert into t1 (aika) values ('Tiistai');
111	insert into t1 (aika) values ('Maanantai');
112	insert into t1 (aika) values ('Sunnuntai');
113
114	SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test;
115	drop table t1;
116
117	#
118	# Test of ORDER BY on IF
119	#
120
121	CREATE 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
131	CREATE TABLE t2
132	(
133	c int unsigned NOT NULL,
134	i int unsigned NOT NULL,
135	INDEX(c)
136	);
137
138	CREATE TABLE t3
139	(
140	c int unsigned NOT NULL,
141	v varchar(64),
142	INDEX(c)
143	);
144
145	INSERT INTO t1 VALUES (1,1,1);
146	INSERT INTO t1 VALUES (2,1,2);
147	INSERT INTO t1 VALUES (3,2,1);
148	INSERT INTO t1 VALUES (4,2,2);
149	INSERT INTO t2 VALUES (1,50);
150	INSERT INTO t2 VALUES (2,25);
151	INSERT INTO t3 VALUES (1,'123 Park Place');
152	INSERT INTO t3 VALUES (2,'453 Boardwalk');
153
154	SELECT a,b,if(b = 1,i,if(b = 2,v,''))
155	FROM t1
156	LEFT JOIN t2 USING(c)
157	LEFT JOIN t3 ON t3.c = t1.c;
158
159	SELECT a,b,if(b = 1,i,if(b = 2,v,''))
160	FROM t1
161	LEFT JOIN t2 ON t1.c = t2.c
162	LEFT JOIN t3 ON t3.c = t1.c;
163
164	SELECT a,b,if(b = 1,i,if(b = 2,v,''))
165	FROM t1
166	LEFT JOIN t2 USING(c)
167	LEFT JOIN t3 ON t3.c = t1.c
168	ORDER BY a;
169
170	SELECT a,b,if(b = 1,i,if(b = 2,v,''))
171	FROM t1
172	LEFT JOIN t2 ON t1.c = t2.c
173	LEFT JOIN t3 ON t3.c = t1.c
174	ORDER BY a;
175
176	drop table t1,t2,t3;
177
178	#
179	# Test of ORDER BY (Bug found by Dean Edmonds)
180	#
181
182	create table t1 (ID int not null primary key, TransactionID int not null);
183	insert into t1 (ID, TransactionID) values (1, 87), (2, 89), (3, 92), (4, 94), (5, 486), (6, 490), (7, 753), (9, 828), (10, 832), (11, 834), (12, 840);
184	create 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);
186	create table t3 (ID int not null primary key, DateOfAction date not null);
187	insert into t3 (ID, DateOfAction) values (87, '1999-07-19'), (89, '1999-07-19'), (92, '1999-07-19'), (94, '1999-07-19'), (486, '1999-07-18'), (490, '2000-03-27'), (753, '2000-03-28'), (828, '1999-07-27'), (832, '1999-07-27'),(834, '1999-07-27'), (840, '1999-07-27');
188	select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t3.DateOfAction, t1.TransactionID;
189	select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t1.TransactionID,t3.DateOfAction;
190	drop table t1,t2,t3;
191
192	#bug reported by Wouter de Jong
193
194	CREATE 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
225	insert into t1 (member_id) values (1),(2),(3);
226	select member_id, nickname, voornaam FROM t1
227	ORDER by lastchange_datum DESC LIMIT 2;
228	drop table t1;
229
230	#
231	# Test optimization of ORDER BY DESC
232	#
233
234	create table t1 (a int not null, b int, c varchar(10), key (a, b, c));
235	insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
236
237	explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
238	select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
239	explain select * from t1 where a >= 1 and a < 3 order by a desc;
240	select * from t1 where a >= 1 and a < 3 order by a desc;
241	explain select * from t1 where a = 1 order by a desc, b desc;
242	select * from t1 where a = 1 order by a desc, b desc;
243	explain select * from t1 where a = 1 and b is null order by a desc, b desc;
244	select * from t1 where a = 1 and b is null order by a desc, b desc;
245	explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc;
246	explain select * from t1 where a = 2 and b >0 order by a desc,b desc;
247	explain select * from t1 where a = 2 and b is null order by a desc,b desc;
248	explain select * from t1 where a = 2 and (b is null or b > 0) order by a
249	desc,b desc;
250	explain select * from t1 where a = 2 and b > 0 order by a desc,b desc;
251	explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
252	explain select * from t1 where a = 1 order by b desc;
253	select * from t1 where a = 1 order by b desc;
254	#
255	# Test things when we don't have NULL keys
256	#
257
258	alter table t1 modify b int not null, modify c varchar(10) not null;
259	explain select * from t1 order by a, b, c;
260	select * from t1 order by a, b, c;
261	explain select * from t1 order by a desc, b desc, c desc;
262	select * from t1 order by a desc, b desc, c desc;
263	# test multiple ranges, NO_MAX_RANGE and EQ_RANGE
264	explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
265	select * 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
267	explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
268	select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
269	select count(*) from t1 where a < 5 and b > 0;
270	select * 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
272	explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
273	select * 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)
275	explain select * from t1 where a between 0 and 1 order by a desc, b desc;
276	select * from t1 where a between 0 and 1 order by a desc, b desc;
277	drop table t1;
278
279
280	CREATE 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;
286	INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108);
287	ALTER TABLE t1 add skr int(10) not null;
288
289	CREATE 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;
297	INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5);
298
299	CREATE TABLE t3 (
300	uid smallint(6) NOT NULL auto_increment,
301	PRIMARY KEY (uid)
302	) ENGINE=MyISAM;
303	INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250);
304	ALTER TABLE t3 add skr int(10) not null;
305
306	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;
307	select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
308
309	# The following ORDER BY can be optimimized
310	EXPLAIN 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;
311	EXPLAIN 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
314	EXPLAIN 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;
315	EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
316	EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
317	drop table t1,t2,t3;
318
319	#
320	# Test of bug when doing an ORDER BY with const items
321	#
322
323	CREATE 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
344	CREATE 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
351	INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
352	INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
353	SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
354	SELECT titre,numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
355	SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
356	SELECT titre,numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
357	drop table t1,t2;
358
359	#
360	# Test order by with NULL values
361	#
362	CREATE TABLE t1 (a int, b int);
363	INSERT INTO t1 VALUES (1, 2);
364	INSERT INTO t1 VALUES (3, 4);
365	INSERT INTO t1 VALUES (5, NULL);
366	SELECT * FROM t1 ORDER BY b;
367	SELECT * FROM t1 ORDER BY b DESC;
368	SELECT * FROM t1 ORDER BY (a + b);
369	SELECT * FROM t1 ORDER BY (a + b) DESC;
370	DROP TABLE t1;
371
372	#
373	# Test of FORCE INDEX ... ORDER BY
374	#
375
376	create table t1(id int not null auto_increment primary key, t char(12));
377	disable_query_log;
378	let $1 = 1000;
379	while ($1)
380	{
381	eval insert into t1(t) values ('$1');
382	dec $1;
383	}
384	enable_query_log;
385	explain select id,t from t1 order by id;
386	explain select id,t from t1 force index (primary) order by id;
387	drop table t1;
388
389	#
390	# Test of test_if_subkey() function
391	#
392	CREATE 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	);
400	INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3');
401	EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
402	SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
403	EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;
404	SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
405	EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
406	SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
407	DROP TABLE t1;
408	#
409	# Bug #1945 - Crashing bug with bad User Variables in UPDATE ... ORDER BY ...
410	#
411	CREATE TABLE t1 (a INT, b INT);
412	SET @id=0;
413	UPDATE t1 SET a=0 ORDER BY (a=@id), b;
414	DROP TABLE t1;
415
416	#
417	# Bug when doing an order by on a 1 byte string (Bug #2147)
418	#
419
420	CREATE 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;
421	INSERT INTO t1 VALUES (11384, 2),(11392, 2);
422	SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
423	drop table t1;
424
425	#
426	# REF_OR_NULL optimization + filesort (bug #2419)
427	#
428
429	create table t1(a int, b int, index(b));
430	insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
431	explain select * from t1 where b=1 or b is null order by a;
432	select * from t1 where b=1 or b is null order by a;
433	explain select * from t1 where b=2 or b is null order by a;
434	select * from t1 where b=2 or b is null order by a;
435	drop table t1;
436
437	#
438	# Bug #3155 - Strange results with index (x, y) ... WHERE ... ORDER BY pk
439	#
440
441	create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null,
442	key(a,b,d), key(c,b,a));
443	create table t2 like t1;
444	insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3);
445	insert into t2 select null, b, c, d from t1;
446	insert into t1 select null, b, c, d from t2;
447	insert into t2 select null, b, c, d from t1;
448	insert into t1 select null, b, c, d from t2;
449	insert into t2 select null, b, c, d from t1;
450	insert into t1 select null, b, c, d from t2;
451	insert into t2 select null, b, c, d from t1;
452	insert into t1 select null, b, c, d from t2;
453	insert into t2 select null, b, c, d from t1;
454	insert into t1 select null, b, c, d from t2;
455	optimize table t1;
456	set @row=10;
457	insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10;
458	select * from t1 where a=1 and b in (1) order by c, b, a;
459	select * from t1 where a=1 and b in (1);
460	drop 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	#
467	create table t1 (col1 int, col int);
468	create table t2 (col2 int, col int);
469	insert into t1 values (1,1),(2,2),(3,3);
470	insert into t2 values (1,3),(2,2),(3,1);
471	select 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
479	select col1 as col, col from t1 order by col;
480	--error 1052
481	select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
482	order by col;
483	--error 1052
484	select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
485	order by col;
486	--error 1052
487	select col1 from t1, t2 where t1.col1=t2.col2 order by col;
488	--error 1052
489	select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2
490	order by col;
491
492	select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2
493	order by col;
494	select col2 as c, col as c from t2 order by col;
495	select col2 as col, col as col2 from t2 order by col;
496	select t2.col2, t2.col, t2.col from t2 order by col;
497
498	select t2.col2 as col from t2 order by t2.col;
499	select t2.col2 as col, t2.col from t2 order by t2.col;
500	select t2.col2, t2.col, t2.col from t2 order by t2.col;
501
502	drop table t1, t2;
503
504	#
505	# Bug #5428: a problem with small max_sort_length value
506	#
507
508	create table t1 (a char(25));
509	insert into t1 set a = repeat('x', 20);
510	insert into t1 set a = concat(repeat('x', 19), 'z');
511	insert into t1 set a = concat(repeat('x', 19), 'ab');
512	insert into t1 set a = concat(repeat('x', 19), 'aa');
513	set max_sort_length=20;
514	select a from t1 order by a;
515	drop table t1;
516
517	#
518	# Bug #7331
519	#
520
521	create 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
528	insert 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
545	explain select * from t1 where wnid like '0101%' order by wnid;
546
547	select * from t1 where wnid like '0101%' order by wnid;
548
549	drop table t1;
550
551	#
552	# Bug #7672 - a wrong result for a select query in braces followed by order by
553	#
554
555	CREATE TABLE t1 (a int);
556	INSERT INTO t1 VALUES (2), (1), (1), (2), (1);
557	SELECT a FROM t1 ORDER BY a;
558	(SELECT a FROM t1) ORDER BY a;
559	DROP 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
565	CREATE TABLE t1 (a int, b int);
566	INSERT 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
573	DROP TABLE t1;
574
575	#
576	# Bug #22457: Column alias in ORDER BY works, but not if in an expression
577	#
578
579	CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2);
580	SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
581	SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
582	SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
583	SELECT a + 1 AS num FROM t1 HAVING 30 - num;
584	--error 1054
585	SELECT a + 1 AS num, num + 1 FROM t1;
586	SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
587	--error 1054
588	SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
589	DROP TABLE t1;
590
591	#
592	# Bug#25126: Reference to non-existant column in UPDATE...ORDER BY...
593	# crashes server
594	#
595	CREATE TABLE bug25126 (
596	val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
597	);
598	--error 1054
599	UPDATE bug25126 SET MissingCol = MissingCol;
600	--error 1054
601	UPDATE bug25126 SET val = val ORDER BY MissingCol;
602	UPDATE bug25126 SET val = val ORDER BY val;
603	UPDATE bug25126 SET val = 1 ORDER BY val;
604	--error 1054
605	UPDATE bug25126 SET val = 1 ORDER BY MissingCol;
606	--error 1054
607	UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol;
608	--error 1054
609	UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol;
610	--error 1054
611	UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol;
612	--error 1054
613	UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol;
614	--error 1054
615	UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol;
616	--error 1054
617	UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol;
618	DROP TABLE bug25126;
619
620	#
621	# Bug #25427: crash when order by expression contains a name
622	# that cannot be resolved unambiguously
623	#
624
625	CREATE TABLE t1 (a int);
626
627	SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1;
628	--error 1052
629	SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val;
630	--error 1052
631	SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1;
632
633	DROP TABLE t1;
634
635	#
636	# Bug #27532: ORDER/GROUP BY expressions with IN/BETWEEN and NOT IN/BETWEEN
637	#
638
639	CREATE TABLE t1 (a int);
640	INSERT INTO t1 VALUES (3), (2), (4), (1);
641
642	SELECT a, IF(a IN (2,3), a, a+10) FROM t1
643	ORDER BY IF(a IN (2,3), a, a+10);
644	SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1
645	ORDER BY IF(a NOT IN (2,3), a, a+10);
646	SELECT a, IF(a IN (2,3), a, a+10) FROM t1
647	ORDER BY IF(a NOT IN (2,3), a, a+10);
648
649	SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
650	ORDER BY IF(a BETWEEN 2 AND 3, a, a+10);
651	SELECT 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);
653	SELECT 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
656	SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
657	FROM t1 GROUP BY x1, x2;
658	SELECT 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
662	SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2);
663	SELECT a FROM t1 ORDER BY a IN (1,2);
664	SELECT a+10 FROM t1 ORDER BY a IN (1,2);
665	SELECT a, IF(a IN (1,2), a, a+10) FROM t1
666	ORDER BY IF(a IN (3,4), a, a+10);
667	DROP TABLE t1;
668
669	# End of 4.1
670	create table t1 (a int not null, b int not null, c int not null);
671	insert t1 values (1,1,1),(1,1,2),(1,2,1);
672	select a, b from t1 group by a, b order by sum(c);
673	drop 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	#
679	CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
680	INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
681
682	explain 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)
684	ORDER BY c;
685	SELECT t2.b as c FROM
686	t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
687	ORDER BY c;
688
689	# check that it still removes sort of const table
690	explain SELECT t1.b as a, t2.b as c FROM
691	t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
692	ORDER BY c;
693
694	CREATE TABLE t2 LIKE t1;
695	INSERT INTO t2 SELECT * from t1;
696	CREATE TABLE t3 LIKE t1;
697	INSERT INTO t3 SELECT * from t1;
698	CREATE TABLE t4 LIKE t1;
699	INSERT INTO t4 SELECT * from t1;
700	INSERT INTO t1 values (0,0),(4,4);
701
702	SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
703	ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
704
705	DROP TABLE t1,t2,t3,t4;
706
707	#
708	# Bug#25376: Incomplete setup of ORDER BY clause results in a wrong result.
709	#
710	create table t1 (a int, b int, c int);
711	insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
712	select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
713	drop table t1;
714
715	#
716	# Bug#26672: Incorrect SEC_TO_TIME() casting in ORDER BY
717	#
718	CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
719	INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
720	UPDATE t1 SET b = SEC_TO_TIME(a);
721
722	# Correct ORDER
723	SELECT a, b FROM t1 ORDER BY b DESC;
724
725	# must be ordered as the above
726	SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
727
728	DROP TABLE t1;
729
730	#
731	# BUG#16590: Optimized does not do right "const" table pre-read
732	#
733	CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
734	INSERT INTO t1 VALUES (1,1),(2,2);
735
736	CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
737	INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
738
739	EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
740
741	DROP TABLE t1,t2;
742
743	# End of 5.0
744
745	#
746	# Bug #28404: query with ORDER BY and ref access
747	#
748
749	CREATE TABLE t1(
750	id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3));
751
752	INSERT 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
756	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
757	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
758	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
759	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
760	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
761	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
762	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
763	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
764	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
765	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
766	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
767	INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
768	UPDATE t1 SET c2=20 WHERE id%100 = 0;
769	SELECT COUNT(*) FROM t1;
770
771	CREATE TABLE t2 LIKE t1;
772	INSERT INTO t2 SELECT * FROM t1 ORDER BY id;
773
774	EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;
775	EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000;
776	EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20;
777	EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000;
778
779	SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;
780
781	DROP TABLE t1,t2;
782
783	#
784	# Bug #30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY\|GROUP BY}
785	#
786	CREATE TABLE t1 (
787	a INT,
788	b INT,
789	PRIMARY KEY (a),
790	KEY ab(a, b)
791	);
792	INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
793	INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
794	INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
795	INSERT INTO t1 SELECT a +16, b +16 FROM t1;
796	INSERT INTO t1 SELECT a +32, b +32 FROM t1;
797	INSERT INTO t1 SELECT a +64, b +64 FROM t1;
798
799	EXPLAIN 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';`
803	eval set @tmp_tables_before =
804	CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
805	--enable_query_log
806
807	SELECT 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';`
814	eval set @tmp_tables_after =
815	CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
816	--enable_query_log
817
818	SELECT @tmp_tables_after = @tmp_tables_before ;
819
820	EXPLAIN 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';`
824	eval set @tmp_tables_before =
825	CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
826	--enable_query_log
827
828	SELECT 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';`
832	eval set @tmp_tables_after =
833	CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
834	--enable_query_log
835
836	SELECT @tmp_tables_after = @tmp_tables_before;
837
838	DROP TABLE t1;
839	--echo #
840	--echo # Bug#31590: Wrong error message on sort buffer being too small.
841	--echo #
842	create table t1(a int, b tinytext);
843	insert into t1 values (1,2),(3,2);
844	set session sort_buffer_size= 30000;
845	set session max_sort_length= 2180;
846	--error 1038
847	select * from t1 order by b;
848	drop table t1;
849
850	#
851	# Bug #35206: select query result different if the key is indexed or not
852	#
853
854	CREATE TABLE t2 (a varchar(32), b int(11), c float, d double,
855	UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c));
856
857	CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));
858	CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b));
859
860	--disable_query_log
861	INSERT INTO t1 (a, b) VALUES
862	('domestic', 'CH'), ('domestic', 'LI'), ('plfcz1', 'FR'), ('all', 'AD'),
863	('all', 'AE'), ('all', 'AF'), ('all', 'AG'), ('all', 'AI'), ('all', 'AL'),
864	('all', 'AM'), ('all', 'AN'), ('all', 'AO'), ('all', 'AP'), ('all', 'AQ'),
865	('all', 'AR'), ('all', 'AS'), ('all', 'AT'), ('all', 'AU'), ('all', 'AW'),
866	('all', 'AZ'), ('all', 'BA'), ('all', 'BB'), ('all', 'BD'), ('all', 'BE'),
867	('all', 'BF'), ('all', 'BG'), ('all', 'BH'), ('all', 'BI'), ('all', 'BJ'),
868	('all', 'BM'), ('all', 'BN'), ('all', 'BO'), ('all', 'BR'), ('all', 'BS'),
869	('all', 'BT'), ('all', 'BV'), ('all', 'BW'), ('all', 'BY'), ('all', 'BZ'),
870	('all', 'CA'), ('all', 'CC'), ('all', 'CD'), ('all', 'CF'), ('all', 'CG'),
871	('all', 'CH'), ('all', 'CI'), ('all', 'CK'), ('all', 'CL'), ('all', 'CM'),
872	('all', 'CN'), ('all', 'CO'), ('all', 'CR'), ('all', 'CU'), ('all', 'CV'),
873	('all', 'CX'), ('all', 'CY'), ('all', 'CZ'), ('all', 'DE'), ('all', 'DJ'),
874	('all', 'DK'), ('all', 'DM'), ('all', 'DO'), ('all', 'DZ'), ('all', 'EC'),
875	('all', 'EE'), ('all', 'EG'), ('all', 'EH'), ('all', 'EI'), ('all', 'ER'),
876	('all', 'ES'), ('all', 'ET'), ('all', 'FI'), ('all', 'FJ'), ('all', 'FK'),
877	('all', 'FM'), ('all', 'FO'), ('all', 'FR'), ('all', 'FX'), ('all', 'GA'),
878	('all', 'GB'), ('all', 'GD'), ('all', 'GE'), ('all', 'GF'), ('all', 'GH'),
879	('all', 'GI'), ('all', 'GL'), ('all', 'GM'), ('all', 'GN'), ('all', 'GP'),
880	('all', 'GQ'), ('all', 'GR'), ('all', 'GS'), ('all', 'GT'), ('all', 'GU'),
881	('all', 'GW'), ('all', 'GY'), ('all', 'HK'), ('all', 'HM'), ('all', 'HN'),
882	( 'all', 'HR'), ( 'all', 'HT'), ( 'all', 'HU'), ( 'all', 'ID'), ( 'all', 'IE'),
883	( 'all', 'IL'), ( 'all', 'IN'), ( 'all', 'IO'), ( 'all', 'IQ'), ( 'all', 'IR'),
884	( 'all', 'IS'), ( 'all', 'IT'), ( 'all', 'JM'), ( 'all', 'JO'), ( 'all', 'JP'),
885	( 'all', 'KE'), ( 'all', 'KG'), ( 'all', 'KH'), ( 'all', 'KI'), ( 'all', 'KM'),
886	( 'all', 'KN'), ( 'all', 'KP'), ( 'all', 'KR'), ( 'all', 'KW'), ( 'all', 'KY'),
887	( 'all', 'KZ'), ( 'all', 'LA'), ( 'all', 'LB'), ( 'all', 'LC'), ( 'all', 'LI'),
888	( 'all', 'LK'), ( 'all', 'LR'), ( 'all', 'LS'), ( 'all', 'LT'), ( 'all', 'LU'),
889	( 'all', 'LV'), ( 'all', 'LY'), ( 'all', 'MA'), ( 'all', 'MC'), ( 'all', 'MD'),
890	( 'all', 'ME'), ( 'all', 'MG'), ( 'all', 'MH'), ( 'all', 'MK'), ( 'all', 'ML'),
891	( 'all', 'MM'), ( 'all', 'MN'), ( 'all', 'MO'), ( 'all', 'MP'), ( 'all', 'MQ'),
892	( 'all', 'MR'), ( 'all', 'MS'), ( 'all', 'MT'), ( 'all', 'MU'), ( 'all', 'MV'),
893	( 'all', 'MW'), ( 'all', 'MX'), ( 'all', 'MY'), ( 'all', 'MZ'), ( 'all', 'NA'),
894	( 'all', 'NC'), ( 'all', 'NE'), ( 'all', 'NF'), ( 'all', 'NG'), ( 'all', 'NI'),
895	( 'all', 'NL'), ( 'all', 'NO'), ( 'all', 'NP'), ( 'all', 'NR'), ( 'all', 'NU'),
896	( 'all', 'NV'), ( 'all', 'NZ'), ( 'all', 'OM'), ( 'all', 'PA'), ( 'all', 'PE'),
897	( 'all', 'PF'), ( 'all', 'PG'), ( 'all', 'PH'), ( 'all', 'PK'), ( 'all', 'PL'),
898	( 'all', 'PM'), ( 'all', 'PN'), ( 'all', 'PR'), ( 'all', 'PS'), ( 'all', 'PT'),
899	( 'all', 'PW'), ( 'all', 'PY'), ( 'all', 'QA'), ( 'all', 'RE'), ( 'all', 'RO'),
900	( 'all', 'RU'), ( 'all', 'RW'), ( 'all', 'SA'), ( 'all', 'SB'), ( 'all', 'SC'),
901	( 'all', 'SD'), ( 'all', 'SE'), ( 'all', 'SG'), ( 'all', 'SH'), ( 'all', 'SI'),
902	( 'all', 'SJ'), ( 'all', 'SK'), ( 'all', 'SL'), ( 'all', 'SM'), ( 'all', 'SN'),
903	( 'all', 'SO'), ( 'all', 'SR'), ( 'all', 'ST'), ( 'all', 'SV'), ( 'all', 'SY'),
904	( 'all', 'SZ'), ( 'all', 'TA'), ( 'all', 'TC'), ( 'all', 'TD'), ( 'all', 'TF'),
905	( 'all', 'TG'), ( 'all', 'TH'), ( 'all', 'TJ'), ( 'all', 'TK'), ( 'all', 'TM'),
906	( 'all', 'TN'), ( 'all', 'TO'), ( 'all', 'TP'), ( 'all', 'TR'), ( 'all', 'TT'),
907	( 'all', 'TV'), ( 'all', 'TW'), ( 'all', 'TZ'), ( 'all', 'UA'), ( 'all', 'UG'),
908	( 'all', 'UM'), ( 'all', 'US'), ( 'all', 'UY'), ( 'all', 'UZ'), ( 'all', 'VA'),
909	( 'all', 'VC'), ( 'all', 'VE'), ( 'all', 'VG'), ( 'all', 'VI'), ( 'all', 'VN'),
910	( 'all', 'VU'), ( 'all', 'WF'), ( 'all', 'WS'), ( 'plfcz1', 'FI'),
911	( 'all', 'XE'), ( 'all', 'XS'), ( 'all', 'XU'), ( 'plfcz1', 'XE'),
912	( 'all', 'YE'), ( 'all', 'YT'), ( 'all', 'YU'), ( 'all', 'ZA'), ( 'all', 'ZM'),
913	( 'all', 'ZR'), ( 'all', 'ZW'), ( 'foreign', 'AD'), ( 'foreign', 'AE'),
914	( 'foreign', 'AF'), ( 'foreign', 'AG'), ( 'foreign', 'AI'),
915	( 'foreign', 'AL'), ( 'foreign', 'AM'), ( 'foreign', 'AN'), ( 'foreign', 'AO'),
916	( 'foreign', 'AP'), ( 'foreign', 'AQ'), ( 'foreign', 'AR'), ( 'foreign', 'AS'),
917	( 'foreign', 'AT'), ( 'foreign', 'AU'), ( 'foreign', 'AW'), ( 'foreign', 'AZ'),
918	( 'foreign', 'BA'), ( 'foreign', 'BB'), ( 'foreign', 'BD'), ( 'foreign', 'BE'),
919	( 'foreign', 'BF'), ( 'foreign', 'BG'), ( 'foreign', 'BH'), ( 'foreign', 'BI'),
920	( 'foreign', 'BJ'), ( 'foreign', 'BM'), ( 'foreign', 'BN'), ( 'foreign', 'BO'),
921	( 'foreign', 'BR'), ( 'foreign', 'BS'), ( 'foreign', 'BT'), ( 'foreign', 'BV'),
922	( 'foreign', 'BW'), ( 'foreign', 'BY'), ( 'foreign', 'BZ'), ( 'foreign', 'CA'),
923	( 'foreign', 'CC'), ( 'foreign', 'CD'), ( 'foreign', 'CF'), ( 'foreign', 'CG'),
924	( 'foreign', 'CI'), ( 'foreign', 'CK'), ( 'foreign', 'CL'), ( 'foreign', 'CM'),
925	( 'foreign', 'CN'), ( 'foreign', 'CO'), ( 'foreign', 'CR'), ( 'foreign', 'CU'),
926	( 'foreign', 'CV'), ( 'foreign', 'CX'), ( 'foreign', 'CY'), ( 'foreign', 'CZ'),
927	( 'foreign', 'DE'), ( 'foreign', 'DJ'), ( 'foreign', 'DK'), ( 'foreign', 'DM'),
928	( 'foreign', 'DO'), ( 'foreign', 'DZ'), ( 'foreign', 'EC'), ( 'foreign', 'EE'),
929	( 'foreign', 'EG'), ( 'foreign', 'EH'), ( 'foreign', 'EI'), ( 'foreign', 'ER'),
930	( 'foreign', 'ES'), ( 'foreign', 'ET'), ( 'foreign', 'FI'), ( 'foreign', 'FJ'),
931	( 'foreign', 'FK'), ( 'foreign', 'FM'), ( 'foreign', 'FO'), ( 'foreign', 'FR'),
932	( 'foreign', 'FX'), ( 'foreign', 'GA'), ( 'foreign', 'GB'), ( 'foreign', 'GD'),
933	( 'foreign', 'GE'), ( 'foreign', 'GF'), ( 'foreign', 'GH'), ( 'foreign', 'GI'),
934	( 'foreign', 'GL'), ( 'foreign', 'GM'), ( 'foreign', 'GN'), ( 'foreign', 'GP'),
935	( 'foreign', 'GQ'), ( 'foreign', 'GR'), ( 'foreign', 'GS'), ( 'foreign', 'GT'),
936	( 'foreign', 'GU'), ( 'foreign', 'GW'), ( 'foreign', 'GY'), ( 'foreign', 'HK'),
937	( 'foreign', 'HM'), ( 'foreign', 'HN'), ( 'foreign', 'HR'), ( 'foreign', 'HT'),
938	( 'foreign', 'HU'), ( 'foreign', 'ID'), ( 'foreign', 'IE'), ( 'foreign', 'IL'),
939	( 'foreign', 'IN'), ( 'foreign', 'IO'), ( 'foreign', 'IQ'), ( 'foreign', 'IR'),
940	( 'foreign', 'IS'), ( 'foreign', 'IT'), ( 'foreign', 'JM'), ( 'foreign', 'JO'),
941	( 'foreign', 'JP'), ( 'foreign', 'KE'), ( 'foreign', 'KG'), ( 'foreign', 'KH'),
942	( 'foreign', 'KI'), ( 'foreign', 'KM'), ( 'foreign', 'KN'), ( 'foreign', 'KP'),
943	( 'foreign', 'KR'), ( 'foreign', 'KW'), ( 'foreign', 'KY'), ( 'foreign', 'KZ'),
944	( 'foreign', 'LA'), ( 'foreign', 'LB'), ( 'foreign', 'LC'), ( 'foreign', 'LK'),
945	( 'foreign', 'LR'), ( 'foreign', 'LS'), ( 'foreign', 'LT'), ( 'foreign', 'LU'),
946	( 'foreign', 'LV'), ( 'foreign', 'LY'), ( 'foreign', 'MA'), ( 'foreign', 'MC'),
947	( 'foreign', 'MD'), ( 'foreign', 'ME'), ( 'foreign', 'MG'), ( 'foreign', 'MH'),
948	( 'foreign', 'MK'), ( 'foreign', 'ML'), ( 'foreign', 'MM'), ( 'foreign', 'MN'),
949	( 'foreign', 'MO'), ( 'foreign', 'MP'), ( 'foreign', 'MQ'), ( 'foreign', 'MR'),
950	( 'foreign', 'MS'), ( 'foreign', 'MT'), ( 'foreign', 'MU'), ( 'foreign', 'MV'),
951	( 'foreign', 'MW'), ( 'foreign', 'MX'), ( 'foreign', 'MY'), ( 'foreign', 'MZ'),
952	( 'foreign', 'NA'), ( 'foreign', 'NC'), ( 'foreign', 'NE'), ( 'foreign', 'NF'),
953	( 'foreign', 'NG'), ( 'foreign', 'NI'), ( 'foreign', 'NL'), ( 'foreign', 'NO'),
954	( 'foreign', 'NP'), ( 'foreign', 'NR'), ( 'foreign', 'NU'), ( 'foreign', 'NV'),
955	( 'foreign', 'NZ'), ( 'foreign', 'OM'), ( 'foreign', 'PA'), ( 'foreign', 'PE'),
956	( 'foreign', 'PF'), ( 'foreign', 'PG'), ( 'foreign', 'PH'), ( 'foreign', 'PK'),
957	( 'foreign', 'PL'), ( 'foreign', 'PM'), ( 'foreign', 'PN'), ( 'foreign', 'PR'),
958	( 'foreign', 'PS'), ( 'foreign', 'PT'), ( 'foreign', 'PW'), ( 'foreign', 'PY'),
959	( 'foreign', 'QA'), ( 'foreign', 'RE'), ( 'foreign', 'RO'), ( 'foreign', 'RU'),
960	( 'foreign', 'RW'), ( 'foreign', 'SA'), ( 'foreign', 'SB'), ( 'foreign', 'SC'),
961	( 'foreign', 'SD'), ( 'foreign', 'SE'), ( 'foreign', 'SG'), ( 'foreign', 'SH'),
962	( 'foreign', 'SI'), ( 'foreign', 'SJ'), ( 'foreign', 'SK'), ( 'foreign', 'SL'),
963	( 'foreign', 'SM'), ( 'foreign', 'SN'), ( 'foreign', 'SO'), ( 'foreign', 'SR'),
964	( 'foreign', 'ST'), ( 'foreign', 'SV'), ( 'foreign', 'SY'), ( 'foreign', 'SZ'),
965	( 'foreign', 'TA'), ( 'foreign', 'TC'), ( 'foreign', 'TD'), ( 'foreign', 'TF'),
966	( 'foreign', 'TG'), ( 'foreign', 'TH'), ( 'foreign', 'TJ'), ( 'foreign', 'TK'),
967	( 'foreign', 'TM'), ( 'foreign', 'TN'), ( 'foreign', 'TO'), ( 'foreign', 'TP'),
968	( 'foreign', 'TR'), ( 'foreign', 'TT'), ( 'foreign', 'TV'), ( 'foreign', 'TW'),
969	( 'foreign', 'TZ'), ( 'foreign', 'UA'), ( 'foreign', 'UG'), ( 'foreign', 'UM'),
970	( 'foreign', 'US'), ( 'foreign', 'UY'), ( 'foreign', 'UZ'), ( 'foreign', 'VA'),
971	( 'foreign', 'VC'), ( 'foreign', 'VE'), ( 'foreign', 'VG'), ( 'foreign', 'VI'),
972	( 'foreign', 'VN'), ( 'foreign', 'VU'), ( 'foreign', 'WF'), ( 'foreign', 'WS'),
973	( 'plfcz1', 'DK'), ( 'foreign', 'XE'), ( 'foreign', 'XS'), ( 'foreign', 'XU'),
974	( 'plfcz1', 'BE'), ( 'foreign', 'YE'), ( 'foreign', 'YT'), ( 'foreign', 'YU'),
975	( 'foreign', 'ZA'), ( 'foreign', 'ZM'), ( 'foreign', 'ZR'), ( 'foreign', 'ZW'),
976	( 'plfcz1', 'DE'), ( 'plfcz1', 'GI'), ( 'plfcz1', 'GR'), ( 'plfcz1', 'IS'),
977	( 'plfcz1', 'EI'), ( 'plfcz1', 'IT'), ( 'plfcz1', 'LU'), ( 'plfcz1', 'NL'),
978	( 'plfcz1', 'NO'), ( 'plfcz1', 'ES'), ( 'plfcz1', 'SE'), ( 'plfcz1', 'AL'),
979	( 'plfcz1', 'AD'), ( 'plfcz1', 'BY'), ( 'plfcz1', 'BA'), ( 'plfcz1', 'BG'),
980	( 'plfcz1', 'EE'), ( 'plfcz1', 'FO'), ( 'plfcz1', 'GL'), ( 'plfcz1', 'GB'),
981	( 'plfcz1', 'HR'), ( 'plfcz1', 'LV'), ( 'plfcz1', 'LT'), ( 'plfcz1', 'MT'),
982	( 'plfcz1', 'MK'), ( 'plfcz1', 'MD'), ( 'plfcz1', 'MC'), ( 'plfcz1', 'AT'),
983	( 'plfcz1', 'PL'), ( 'plfcz1', 'PT'), ( 'plfcz1', 'RO'), ( 'plfcz1', 'RU'),
984	( 'plfcz1', 'SM'), ( 'plfcz1', 'XS'), ( 'plfcz1', 'SK'), ( 'plfcz1', 'SI'),
985	( 'plfcz1', 'CZ'), ( 'plfcz1', 'TR'), ( 'plfcz1', 'UA'), ( 'plfcz1', 'HU'),
986	( 'plfcz1', 'VA'), ( 'plfcz1', 'CY'), ( 'plfcz2', 'AF'), ( 'plfcz2', 'DZ'),
987	( 'plfcz2', 'AS'), ( 'plfcz2', 'AO'), ( 'plfcz2', 'AI'), ( 'plfcz2', 'AQ'),
988	( 'plfcz2', 'AG'), ( 'plfcz2', 'AR'), ( 'plfcz2', 'AM'), ( 'plfcz2', 'AW'),
989	( 'plfcz2', 'AU'), ( 'plfcz2', 'AZ'), ( 'plfcz2', 'AP'), ( 'plfcz2', 'BS'),
990	( 'plfcz2', 'BH'), ( 'plfcz2', 'BD'), ( 'plfcz2', 'BB'), ( 'plfcz2', 'BZ'),
991	( 'plfcz2', 'BJ'), ( 'plfcz2', 'BM'), ( 'plfcz2', 'BT'), ( 'plfcz2', 'BO'),
992	( 'plfcz2', 'BW'), ( 'plfcz2', 'BV'), ( 'plfcz2', 'BR'), ( 'plfcz2', 'IO'),
993	( 'plfcz2', 'VG'), ( 'plfcz2', 'BN'), ( 'plfcz2', 'BF'), ( 'plfcz2', 'BI'),
994	( 'plfcz2', 'KH'), ( 'plfcz2', 'CM'), ( 'plfcz2', 'CA'), ( 'plfcz2', 'CV'),
995	( 'plfcz2', 'KY'), ( 'plfcz2', 'CF'), ( 'plfcz2', 'TD'), ( 'plfcz2', 'CL'),
996	( 'plfcz2', 'CN'), ( 'plfcz2', 'CX'), ( 'plfcz2', 'CC'), ( 'plfcz2', 'CO'),
997	( 'plfcz2', 'KM'), ( 'plfcz2', 'CG'), ( 'plfcz2', 'CD'), ( 'plfcz2', 'CK'),
998	( 'plfcz2', 'CR'), ( 'plfcz2', 'CI'), ( 'plfcz2', 'CU'), ( 'plfcz2', 'DJ'),
999	( 'plfcz2', 'DM'), ( 'plfcz2', 'DO'), ( 'plfcz2', 'TP'), ( 'plfcz2', 'EC'),
1000	( 'plfcz2', 'EG'), ( 'plfcz2', 'SV'), ( 'plfcz2', 'GQ'), ( 'plfcz2', 'ER'),
1001	( 'plfcz2', 'ET'), ( 'plfcz2', 'FK'), ( 'plfcz2', 'FJ'), ( 'plfcz2', 'FX'),
1002	( 'plfcz2', 'GF'), ( 'plfcz2', 'PF'), ( 'plfcz2', 'TA'), ( 'plfcz2', 'TF'),
1003	( 'plfcz2', 'GA'), ( 'plfcz2', 'GM'), ( 'plfcz2', 'GE'), ( 'plfcz2', 'GH'),
1004	( 'plfcz2', 'GD'), ( 'plfcz2', 'GP'), ( 'plfcz2', 'GU'), ( 'plfcz2', 'GT'),
1005	( 'plfcz2', 'GN'), ( 'plfcz2', 'GW'), ( 'plfcz2', 'GY'), ( 'plfcz2', 'HT'),
1006	( 'plfcz2', 'HM'), ( 'plfcz2', 'HN'), ( 'plfcz2', 'HK'), ( 'plfcz2', 'IN'),
1007	( 'plfcz2', 'ID'), ( 'plfcz2', 'IR'), ( 'plfcz2', 'IQ'), ( 'plfcz2', 'IE'),
1008	( 'plfcz2', 'IL'), ( 'plfcz2', 'JM'), ( 'plfcz2', 'JP'), ( 'plfcz2', 'JO'),
1009	( 'plfcz2', 'KZ'), ( 'plfcz2', 'KE'), ( 'plfcz2', 'KI'), ( 'plfcz2', 'KP'),
1010	( 'plfcz2', 'KW'), ( 'plfcz2', 'KG'), ( 'plfcz2', 'LA'), ( 'plfcz2', 'LB'),
1011	( 'plfcz2', 'LS'), ( 'plfcz2', 'LR'), ( 'plfcz2', 'LY'), ( 'plfcz2', 'MO'),
1012	( 'plfcz2', 'MG'), ( 'plfcz2', 'ME'), ( 'plfcz2', 'MW'), ( 'plfcz2', 'MY'),
1013	( 'plfcz2', 'MV'), ( 'plfcz2', 'ML'), ( 'plfcz2', 'MH'), ( 'plfcz2', 'MQ'),
1014	( 'plfcz2', 'MR'), ( 'plfcz2', 'MU'), ( 'plfcz2', 'YT'), ( 'plfcz2', 'MX'),
1015	( 'plfcz2', 'FM'), ( 'plfcz2', 'MN'), ( 'plfcz2', 'MS'), ( 'plfcz2', 'MA'),
1016	( 'plfcz2', 'MZ'), ( 'plfcz2', 'MM'), ( 'plfcz2', 'NA'), ( 'plfcz2', 'NR'),
1017	( 'plfcz2', 'NP'), ( 'plfcz2', 'AN'), ( 'plfcz2', 'NC'), ( 'plfcz2', 'NZ'),
1018	( 'plfcz2', 'NI'), ( 'plfcz2', 'NE'), ( 'plfcz2', 'NG'), ( 'plfcz2', 'NU'),
1019	( 'plfcz2', 'NF'), ( 'plfcz2', 'MP'), ( 'plfcz2', 'OM'), ( 'plfcz2', 'PK'),
1020	( 'plfcz2', 'PW'), ( 'plfcz2', 'PS'), ( 'plfcz2', 'PA'), ( 'plfcz2', 'PG'),
1021	( 'plfcz2', 'PY'), ( 'plfcz2', 'PE'), ( 'plfcz2', 'PH'), ( 'plfcz2', 'PN'),
1022	( 'plfcz2', 'PR'), ( 'plfcz2', 'QA'), ( 'plfcz2', 'RE'), ( 'plfcz2', 'RW'),
1023	( 'plfcz2', 'KN'), ( 'plfcz2', 'ST'), ( 'plfcz2', 'SA'), ( 'plfcz2', 'SN'),
1024	( 'plfcz2', 'SC'), ( 'plfcz2', 'SL'), ( 'plfcz2', 'SG'), ( 'plfcz2', 'SB'),
1025	( 'plfcz2', 'SO'), ( 'plfcz2', 'ZA'), ( 'plfcz2', 'GS'), ( 'plfcz2', 'KR'),
1026	( 'plfcz2', 'LK'), ( 'plfcz2', 'NV'), ( 'plfcz2', 'SH'), ( 'plfcz2', 'LC'),
1027	( 'plfcz2', 'PM'), ( 'plfcz2', 'VC'), ( 'plfcz2', 'SD'), ( 'plfcz2', 'SR'),
1028	( 'plfcz2', 'SJ'), ( 'plfcz2', 'SZ'), ( 'plfcz2', 'SY'), ( 'plfcz2', 'TW'),
1029	( 'plfcz2', 'TJ'), ( 'plfcz2', 'TZ'), ( 'plfcz2', 'TH'), ( 'plfcz2', 'TG'),
1030	( 'plfcz2', 'TK'), ( 'plfcz2', 'TO'), ( 'plfcz2', 'TT'), ( 'plfcz2', 'XU'),
1031	( 'plfcz2', 'TN'), ( 'plfcz2', 'TM'), ( 'plfcz2', 'TC'), ( 'plfcz2', 'TV'),
1032	( 'plfcz2', 'UG'), ( 'plfcz2', 'AE'), ( 'plfcz2', 'US'), ( 'plfcz2', 'UM'),
1033	( 'plfcz2', 'UY'), ( 'plfcz2', 'UZ'), ( 'plfcz2', 'VU'), ( 'plfcz2', 'VE'),
1034	( 'plfcz2', 'VN'), ( 'plfcz2', 'VI'), ( 'plfcz2', 'WF'), ( 'plfcz2', 'EH'),
1035	( 'plfcz2', 'WS'), ( 'plfcz2', 'YE'), ( 'plfcz2', 'YU'), ( 'plfcz2', 'ZR'),
1036	( 'plfcz2', 'ZM'), ( 'plfcz2', 'ZW'), ( 'ppfcz1', 'AT'), ( 'ppfcz1', 'BE'),
1037	( 'ppfcz1', 'DE'), ( 'ppfcz1', 'FR'), ( 'ppfcz1', 'FX'), ( 'ppfcz1', 'IT'),
1038	( 'ppfcz1', 'LU'), ( 'ppfcz1', 'MC'), ( 'ppfcz1', 'NL'), ( 'ppfcz1', 'SM'),
1039	( 'ppfcz1', 'VA'), ( 'ppfcz1', 'XE'), ( 'ppfcz2', 'AD'), ( 'ppfcz2', 'AL'),
1040	( 'ppfcz2', 'BA'), ( 'ppfcz2', 'BG'), ( 'ppfcz2', 'BY'), ( 'ppfcz2', 'CY'),
1041	( 'ppfcz2', 'CZ'), ( 'ppfcz2', 'DK'), ( 'ppfcz2', 'EE'), ( 'ppfcz2', 'EI'),
1042	( 'ppfcz2', 'ES'), ( 'ppfcz2', 'FI'), ( 'ppfcz2', 'FO'), ( 'ppfcz2', 'GB'),
1043	( 'ppfcz2', 'GI'), ( 'ppfcz2', 'GL'), ( 'ppfcz2', 'GR'), ( 'ppfcz2', 'HR'),
1044	( 'ppfcz2', 'HU'), ( 'ppfcz2', 'IE'), ( 'ppfcz2', 'IS'), ( 'ppfcz2', 'LT'),
1045	( 'ppfcz2', 'LV'), ( 'ppfcz2', 'MD'), ( 'ppfcz2', 'MK'), ( 'ppfcz2', 'MT'),
1046	( 'ppfcz2', 'NO'), ( 'ppfcz2', 'PL'), ( 'ppfcz2', 'PT'), ( 'ppfcz2', 'RO'),
1047	( 'ppfcz2', 'RU'), ( 'ppfcz2', 'SE'), ( 'ppfcz2', 'SI'), ( 'ppfcz2', 'SK'),
1048	( 'ppfcz2', 'TR'), ( 'ppfcz2', 'UA'), ( 'ppfcz2', 'XS'), ( 'ppfcz2', 'YU'),
1049	( 'ppfcz3', 'CA'), ( 'ppfcz3', 'DZ'), ( 'ppfcz3', 'EG'), ( 'ppfcz3', 'IL'),
1050	( 'ppfcz3', 'JO'), ( 'ppfcz3', 'LB'), ( 'ppfcz3', 'LY'), ( 'ppfcz3', 'MA'),
1051	( 'ppfcz3', 'MX'), ( 'ppfcz3', 'PM'), ( 'ppfcz3', 'SY'), ( 'ppfcz3', 'TN'),
1052	( 'ppfcz3', 'US'), ( 'ppfcz4', 'AE'), ( 'ppfcz4', 'AF'), ( 'ppfcz4', 'AM'),
1053	( 'ppfcz4', 'AO'), ( 'ppfcz4', 'AZ'), ( 'ppfcz4', 'BD'), ( 'ppfcz4', 'BF'),
1054	( 'ppfcz4', 'BH'), ( 'ppfcz4', 'BI'), ( 'ppfcz4', 'BJ'), ( 'ppfcz4', 'BT'),
1055	( 'ppfcz4', 'BV'), ( 'ppfcz4', 'BW'), ( 'ppfcz4', 'CF'), ( 'ppfcz4', 'CG'),
1056	( 'ppfcz4', 'CI'), ( 'ppfcz4', 'CM'), ( 'ppfcz4', 'CN'), ( 'ppfcz4', 'DJ'),
1057	( 'ppfcz4', 'DO'), ( 'ppfcz4', 'ER'), ( 'ppfcz4', 'ET'), ( 'ppfcz4', 'GA'),
1058	( 'ppfcz4', 'GE'), ( 'ppfcz4', 'GH'), ( 'ppfcz4', 'GM'), ( 'ppfcz4', 'GN'),
1059	( 'ppfcz4', 'GQ'), ( 'ppfcz4', 'GW'), ( 'ppfcz4', 'HK'), ( 'ppfcz4', 'IN'),
1060	( 'ppfcz4', 'IQ'), ( 'ppfcz4', 'IR'), ( 'ppfcz4', 'JP'), ( 'ppfcz4', 'KE'),
1061	( 'ppfcz4', 'KG'), ( 'ppfcz4', 'KH'), ( 'ppfcz4', 'KP'), ( 'ppfcz4', 'KW'),
1062	( 'ppfcz4', 'KZ'), ( 'ppfcz4', 'LA'), ( 'ppfcz4', 'LK'), ( 'ppfcz4', 'LR'),
1063	( 'ppfcz4', 'LS'), ( 'ppfcz4', 'MG'), ( 'ppfcz4', 'ML'), ( 'ppfcz4', 'MM'),
1064	( 'ppfcz4', 'MN'), ( 'ppfcz4', 'MO'), ( 'ppfcz4', 'MR'), ( 'ppfcz4', 'MU'),
1065	( 'ppfcz4', 'MV'), ( 'ppfcz4', 'MW'), ( 'ppfcz4', 'MY'), ( 'ppfcz4', 'MZ'),
1066	( 'ppfcz4', 'NA'), ( 'ppfcz4', 'NE'), ( 'ppfcz4', 'NG'), ( 'ppfcz4', 'NP'),
1067	( 'ppfcz4', 'OM'), ( 'ppfcz4', 'PK'), ( 'ppfcz4', 'QA'), ( 'ppfcz4', 'RE'),
1068	( 'ppfcz4', 'RW'), ( 'ppfcz4', 'SA'), ( 'ppfcz4', 'SC'), ( 'ppfcz4', 'SD'),
1069	( 'ppfcz4', 'SG'), ( 'ppfcz4', 'SH'), ( 'ppfcz4', 'SL'), ( 'ppfcz4', 'SN'),
1070	( 'ppfcz4', 'SO'), ( 'ppfcz4', 'SZ'), ( 'ppfcz4', 'TD'), ( 'ppfcz4', 'TG'),
1071	( 'ppfcz4', 'TH'), ( 'ppfcz4', 'TJ'), ( 'ppfcz4', 'TM'), ( 'ppfcz4', 'TW'),
1072	( 'ppfcz4', 'TZ'), ( 'ppfcz4', 'UG'), ( 'ppfcz4', 'UZ'), ( 'ppfcz4', 'VN'),
1073	( 'ppfcz4', 'XU'), ( 'ppfcz4', 'YT'), ( 'ppfcz4', 'ZA'), ( 'ppfcz4', 'ZW'),
1074	( 'ppfcz5', 'AG'), ( 'ppfcz5', 'AI'), ( 'ppfcz5', 'AN'), ( 'ppfcz5', 'AP'),
1075	( 'ppfcz5', 'AQ'), ( 'ppfcz5', 'AR'), ( 'ppfcz5', 'AS'), ( 'ppfcz5', 'AU'),
1076	( 'ppfcz5', 'AW'), ( 'ppfcz5', 'BB'), ( 'ppfcz5', 'BM'), ( 'ppfcz5', 'BN'),
1077	( 'ppfcz5', 'BO'), ( 'ppfcz5', 'BR'), ( 'ppfcz5', 'BS'), ( 'ppfcz5', 'BZ'),
1078	( 'ppfcz5', 'CC'), ( 'ppfcz5', 'CD'), ( 'ppfcz5', 'CK'), ( 'ppfcz5', 'CL'),
1079	( 'ppfcz5', 'CO'), ( 'ppfcz5', 'CR'), ( 'ppfcz5', 'CU'), ( 'ppfcz5', 'CV'),
1080	( 'ppfcz5', 'CX'), ( 'ppfcz5', 'DM'), ( 'ppfcz5', 'EC'), ( 'ppfcz5', 'EH'),
1081	( 'ppfcz5', 'FJ'), ( 'ppfcz5', 'FK'), ( 'ppfcz5', 'FM'), ( 'ppfcz5', 'GD'),
1082	( 'ppfcz5', 'GF'), ( 'ppfcz5', 'GP'), ( 'ppfcz5', 'GS'), ( 'ppfcz5', 'GT'),
1083	( 'ppfcz5', 'GU'), ( 'ppfcz5', 'GY'), ( 'ppfcz5', 'HM'), ( 'ppfcz5', 'HN'),
1084	( 'ppfcz5', 'HT'), ( 'ppfcz5', 'ID'), ( 'ppfcz5', 'IO'), ( 'ppfcz5', 'JM'),
1085	( 'ppfcz5', 'KI'), ( 'ppfcz5', 'KM'), ( 'ppfcz5', 'KN'), ( 'ppfcz5', 'KR'),
1086	( 'ppfcz5', 'KY'), ( 'ppfcz5', 'LC'), ( 'ppfcz5', 'ME'), ( 'ppfcz5', 'MH'),
1087	( 'ppfcz5', 'MP'), ( 'ppfcz5', 'MQ'), ( 'ppfcz5', 'MS'), ( 'ppfcz5', 'NC'),
1088	( 'ppfcz5', 'NF'), ( 'ppfcz5', 'NI'), ( 'ppfcz5', 'NR'), ( 'ppfcz5', 'NU'),
1089	( 'ppfcz5', 'NZ'), ( 'ppfcz5', 'PA'), ( 'ppfcz5', 'PE'), ( 'ppfcz5', 'PF'),
1090	( 'ppfcz5', 'PG'), ( 'ppfcz5', 'PH'), ( 'ppfcz5', 'PN'), ( 'ppfcz5', 'PR'),
1091	( 'ppfcz5', 'PS'), ( 'ppfcz5', 'PW'), ( 'ppfcz5', 'PY'), ( 'ppfcz5', 'SB'),
1092	( 'ppfcz5', 'SJ'), ( 'ppfcz5', 'SR'), ( 'ppfcz5', 'ST'), ( 'ppfcz5', 'SV'),
1093	( 'ppfcz5', 'TA'), ( 'ppfcz5', 'TC'), ( 'ppfcz5', 'TF'), ( 'ppfcz5', 'TK'),
1094	( 'ppfcz5', 'TO'), ( 'ppfcz5', 'TP'), ( 'ppfcz5', 'TT'), ( 'ppfcz5', 'TV'),
1095	( 'ppfcz5', 'UM'), ( 'ppfcz5', 'UY'), ( 'ppfcz5', 'VC'), ( 'ppfcz5', 'VE'),
1096	( 'ppfcz5', 'VG'), ( 'ppfcz5', 'VI'), ( 'ppfcz5', 'VU'), ( 'ppfcz5', 'WF'),
1097	( 'ppfcz5', 'WS'), ( 'ppfcz5', 'YE'), ( 'ppfcz5', 'ZM'), ( 'ppfcz5', 'ZR');
1098
1099	INSERT INTO t2 (a, b, c, d) VALUES
1100	('domestic', 26, 0.25, 4.7), ('domestic', 27, 0.25, 6),
1101	('domestic', 19, 2, 6.3), ('domestic', 19, 5, 7.77),
1102	('domestic', 19, 10, 10.3), ('domestic', 19, 20, 14.83),
1103	('domestic', 19, 30, 20.88), ('domestic', 20, 2, 7.3),
1104	('domestic', 20, 5, 8.77), ('domestic', 20, 10, 11.3),
1105	('domestic', 20, 20, 15.83), ('domestic', 20, 30, 21.88),
1106	('domestic', 23, 2, 18.8), ('domestic', 23, 5, 20.8),
1107	('domestic', 23, 10, 24.8), ('domestic', 23, 20, 27.8),
1108	('domestic', 23, 30, 30.8), ('domestic', 24, 2, 21.1405),
1109	('domestic', 24, 5, 22.3705), ('domestic', 24, 10, 25.0905),
1110	('domestic', 24, 20, 29.7705), ('domestic', 24, 30, 35.9605),
1111	('domestic', 17, 2, 7.2), ('domestic', 17, 5, 8.43),
1112	('domestic', 17, 10, 11.15), ('domestic', 17, 20, 15.83),
1113	('domestic', 17, 30, 22.02), ('domestic', 18, 2, 8.2),
1114	('domestic', 18, 5, 9.43), ('domestic', 18, 10, 12.15),
1115	('domestic', 18, 20, 16.83), ('domestic', 18, 30, 23.02),
1116	('domestic', 28, 2, 17), ('domestic', 28, 5, 19),
1117	('domestic', 28, 10, 22), ('domestic', 28, 20, 28),
1118	('domestic', 28, 30, 35), ('domestic', 29, 30, 29.5),
1119	('foreign', 25, 200, 0), ('domestic', 3, 100, 59),
1120	('foreign', 10, 30, 0), ('foreign', 22, 0, 0),
1121	('foreign', 11, 30, 0), ('foreign', 12, 30, 0),
1122	('all', 1, 10000, 0), ('all', 2, 10000, 0),
1123	('domestic', 9, 10000, 0), ('domestic', 4, 500, 0),
1124	('domestic', 5, 500, 0), ('domestic', 6, 500, 0),
1125	('domestic', 7, 500, 0), ('domestic', 8, 500, 0),
1126	('domestic', 21, 3.9, 10.8), ('domestic', 21, 4.9, 12.2),
1127	('domestic', 21, 9.9, 15.3), ('domestic', 21, 19.9, 20.6),
1128	('domestic', 21, 30, 28.1), ('plfcz1', 16, 0.5, 19),
1129	('plfcz2', 16, 0.5, 25), ( 'ppfcz2', 15, 16, 76.5),
1130	( 'ppfcz2', 15, 15, 75.5), ( 'ppfcz2', 15, 14, 73.5),
1131	( 'ppfcz2', 15, 13, 71.5), ( 'ppfcz2', 15, 12, 69.5),
1132	( 'ppfcz2', 15, 11, 67.5), ( 'ppfcz2', 15, 10, 65.5),
1133	( 'ppfcz2', 15, 9, 62.5), ( 'ppfcz2', 15, 8, 59.5),
1134	( 'ppfcz2', 15, 7, 56.5), ( 'ppfcz2', 15, 6, 53.5),
1135	( 'ppfcz2', 15, 5, 50.5), ( 'ppfcz2', 15, 4, 46.5),
1136	( 'ppfcz2', 15, 3, 42.5), ( 'ppfcz2', 15, 2, 38.5),
1137	('ppfcz1', 15, 2, 33.5), ('ppfcz1', 15, 3, 36.5),
1138	('ppfcz1', 15, 4, 39.5), ('ppfcz1', 15, 5, 41.5),
1139	('ppfcz1', 15, 6, 42.5), ('ppfcz1', 15, 7, 43.5),
1140	('ppfcz1', 15, 8, 44.5), ('ppfcz1', 15, 9, 45.5),
1141	('ppfcz1', 15, 10, 46.5), ('ppfcz1', 15, 11, 47.5),
1142	( 'ppfcz1', 15, 12, 48.5), ( 'ppfcz1', 15, 13, 49.5), ( 'ppfcz1', 15, 14, 50.5),
1143	( 'ppfcz1', 15, 15, 51.5), ( 'ppfcz1', 15, 16, 52.5), ( 'ppfcz1', 15, 17, 53.5),
1144	( 'ppfcz1', 15, 18, 54.5), ( 'ppfcz1', 15, 19, 55.5), ( 'ppfcz1', 15, 20, 56.5),
1145	( 'ppfcz1', 15, 21, 57.5), ( 'ppfcz1', 15, 22, 58.5), ( 'ppfcz1', 15, 23, 59.5),
1146	( 'ppfcz1', 15, 24, 60.5), ( 'ppfcz1', 15, 25, 61.5), ( 'ppfcz1', 15, 26, 62.5),
1147	( 'ppfcz1', 15, 27, 63.5), ( 'ppfcz1', 15, 28, 64.5), ( 'ppfcz1', 15, 29, 65.5),
1148	( 'ppfcz1', 15, 30, 66.5), ( 'ppfcz2', 15, 17, 77.5), ( 'ppfcz2', 15, 18, 78.5),
1149	( 'ppfcz2', 15, 19, 79.5), ( 'ppfcz2', 15, 20, 80.5), ( 'ppfcz2', 15, 21, 81.5),
1150	( 'ppfcz2', 15, 22, 82.5), ( 'ppfcz2', 15, 23, 83.5), ( 'ppfcz2', 15, 24, 84.5),
1151	( 'ppfcz2', 15, 25, 85.5), ( 'ppfcz2', 15, 26, 86.5), ( 'ppfcz2', 15, 27, 87.5),
1152	( 'ppfcz2', 15, 28, 88.5), ( 'ppfcz2', 15, 29, 89.5), ( 'ppfcz2', 15, 30, 90.5),
1153	( 'ppfcz3', 15, 2, 39.5), ( 'ppfcz3', 15, 3, 45.5), ( 'ppfcz3', 15, 4, 51.5),
1154	( 'ppfcz3', 15, 5, 57.5), ( 'ppfcz3', 15, 6, 63.5), ( 'ppfcz3', 15, 7, 69.5),
1155	( 'ppfcz3', 15, 8, 75.5), ( 'ppfcz3', 15, 9, 81.5), ( 'ppfcz3', 15, 10, 87.5),
1156	( 'ppfcz3', 15, 11, 93.5), ( 'ppfcz3', 15, 12, 99.5), ( 'ppfcz3', 15, 13, 105.5),
1157	( 'ppfcz3', 15, 14, 111.5), ( 'ppfcz3', 15, 15, 117.5), ( 'ppfcz3', 15, 16, 122.5),
1158	( 'ppfcz3', 15, 17, 127.5), ( 'ppfcz3', 15, 18, 132.5), ( 'ppfcz3', 15, 19, 137.5),
1159	( 'ppfcz3', 15, 20, 142.5), ( 'ppfcz3', 15, 21, 146.5), ( 'ppfcz3', 15, 22, 150.5),
1160	( 'ppfcz3', 15, 23, 154.5), ( 'ppfcz3', 15, 24, 158.5), ( 'ppfcz3', 15, 25, 162.5),
1161	( 'ppfcz3', 15, 26, 166.5), ( 'ppfcz3', 15, 27, 170.5), ( 'ppfcz3', 15, 28, 174.5),
1162	( 'ppfcz3', 15, 29, 178.5), ( 'ppfcz3', 15, 30, 182.5), ( 'ppfcz4', 15, 2, 44.5),
1163	( 'ppfcz4', 15, 3, 51.5), ( 'ppfcz4', 15, 4, 58.5), ( 'ppfcz4', 15, 5, 65.5),
1164	( 'ppfcz4', 15, 6, 72.5), ( 'ppfcz4', 15, 7, 79.5), ( 'ppfcz4', 15, 8, 86.5),
1165	( 'ppfcz4', 15, 9, 93.5), ( 'ppfcz4', 15, 10, 100.5), ( 'ppfcz4', 15, 11, 105.5),
1166	( 'ppfcz4', 15, 12, 110.5), ( 'ppfcz4', 15, 13, 115.5), ( 'ppfcz4', 15, 14, 120.5),
1167	( 'ppfcz4', 15, 15, 125.5), ( 'ppfcz4', 15, 16, 130.5), ( 'ppfcz4', 15, 17, 135.5),
1168	( 'ppfcz4', 15, 18, 140.5), ( 'ppfcz4', 15, 19, 145.5), ( 'ppfcz4', 15, 20, 150.5),
1169	( 'ppfcz4', 15, 21, 154.5), ( 'ppfcz4', 15, 22, 158.5), ( 'ppfcz4', 15, 23, 162.5),
1170	( 'ppfcz4', 15, 24, 166.5), ( 'ppfcz4', 15, 25, 170.5), ( 'ppfcz4', 15, 26, 174.5),
1171	( 'ppfcz4', 15, 27, 178.5), ( 'ppfcz4', 15, 28, 182.5), ( 'ppfcz4', 15, 29, 186.5),
1172	( 'ppfcz4', 15, 30, 190.5), ( 'ppfcz5', 15, 2, 48.5), ( 'ppfcz5', 15, 3, 56.5),
1173	( 'ppfcz5', 15, 4, 64.5), ( 'ppfcz5', 15, 5, 72.5), ( 'ppfcz5', 15, 6, 80.5),
1174	( 'ppfcz5', 15, 7, 88.5), ( 'ppfcz5', 15, 8, 96.5), ( 'ppfcz5', 15, 9, 104.5),
1175	( 'ppfcz5', 15, 10, 112.5), ( 'ppfcz5', 15, 11, 119.5), ( 'ppfcz5', 15, 12, 126.5),
1176	( 'ppfcz5', 15, 13, 133.5), ( 'ppfcz5', 15, 14, 140.5), ( 'ppfcz5', 15, 15, 147.5),
1177	( 'ppfcz5', 15, 16, 153.5), ( 'ppfcz5', 15, 17, 161.5), ( 'ppfcz5', 15, 18, 167.5),
1178	( 'ppfcz5', 15, 19, 173.5), ( 'ppfcz5', 15, 20, 179.5), ( 'ppfcz5', 15, 21, 185.5),
1179	( 'ppfcz5', 15, 22, 191.5), ( 'ppfcz5', 15, 23, 197.5), ( 'ppfcz5', 15, 24, 203.5),
1180	( 'ppfcz5', 15, 25, 207.5), ( 'ppfcz5', 15, 26, 212.5), ( 'ppfcz5', 15, 27, 217.5),
1181	( 'ppfcz5', 15, 28, 222.5), ( 'ppfcz5', 15, 29, 227.5), ( 'ppfcz5', 15, 30, 232.5),
1182	( 'ppfcz1', 14, 2, 37.5), ( 'ppfcz1', 14, 3, 41.5), ( 'ppfcz1', 14, 4, 45.5),
1183	( 'ppfcz1', 14, 5, 48.5), ( 'ppfcz1', 14, 6, 52.5), ( 'ppfcz1', 14, 7, 55.5),
1184	( 'ppfcz1', 14, 8, 57.5), ( 'ppfcz1', 14, 9, 59.5), ( 'ppfcz1', 14, 10, 61.5),
1185	( 'ppfcz1', 14, 11, 62.5), ( 'ppfcz1', 14, 12, 63.5), ( 'ppfcz1', 14, 13, 64.5),
1186	( 'ppfcz1', 14, 14, 65.5), ( 'ppfcz1', 14, 15, 66.5), ( 'ppfcz1', 14, 16, 67.5),
1187	( 'ppfcz1', 14, 17, 68.5), ( 'ppfcz1', 14, 18, 69.5), ( 'ppfcz1', 14, 19, 70.5),
1188	( 'ppfcz1', 14, 20, 71.5), ( 'ppfcz1', 14, 21, 72.5), ( 'ppfcz1', 14, 22, 73.5),
1189	( 'ppfcz1', 14, 23, 74.5), ( 'ppfcz1', 14, 24, 75.5), ( 'ppfcz1', 14, 25, 76.5),
1190	( 'ppfcz1', 14, 26, 77.5), ( 'ppfcz1', 14, 27, 78.5), ( 'ppfcz1', 14, 28, 79.5),
1191	( 'ppfcz1', 14, 29, 80.5), ( 'ppfcz1', 14, 30, 81.5), ( 'ppfcz2', 14, 2, 43.5),
1192	( 'ppfcz2', 14, 3, 48.5), ( 'ppfcz2', 14, 4, 53.5), ( 'ppfcz2', 14, 5, 57.5),
1193	( 'ppfcz2', 14, 6, 61.5), ( 'ppfcz2', 14, 7, 65.5), ( 'ppfcz2', 14, 8, 69.5),
1194	( 'ppfcz2', 14, 9, 73.5), ( 'ppfcz2', 14, 10, 77.5), ( 'ppfcz2', 14, 11, 80.5),
1195	( 'ppfcz2', 14, 12, 83.5), ( 'ppfcz2', 14, 13, 86.5), ( 'ppfcz2', 14, 14, 89.5),
1196	( 'ppfcz2', 14, 15, 92.5), ( 'ppfcz2', 14, 16, 94.5), ( 'ppfcz2', 14, 17, 96.5),
1197	( 'ppfcz2', 14, 18, 98.5), ( 'ppfcz2', 14, 19, 99.5), ( 'ppfcz2', 14, 20, 100.5),
1198	( 'ppfcz2', 14, 21, 101.5), ( 'ppfcz2', 14, 22, 102.5), ( 'ppfcz2', 14, 23, 103.5),
1199	( 'ppfcz2', 14, 24, 104.5), ( 'ppfcz2', 14, 25, 105.5), ( 'ppfcz2', 14, 26, 106.5),
1200	( 'ppfcz2', 14, 27, 107.5), ( 'ppfcz2', 14, 28, 108.5), ( 'ppfcz2', 14, 29, 109.5),
1201	( 'ppfcz2', 14, 30, 110.5), ( 'ppfcz3', 14, 2, 47.5), ( 'ppfcz3', 14, 3, 56.5),
1202	( 'ppfcz3', 14, 4, 67.5), ( 'ppfcz3', 14, 5, 78.5), ( 'ppfcz3', 14, 6, 87.5),
1203	( 'ppfcz3', 14, 7, 96.5), ( 'ppfcz3', 14, 8, 105.5), ( 'ppfcz3', 14, 9, 114.5),
1204	( 'ppfcz3', 14, 10, 123.5), ( 'ppfcz3', 14, 11, 131.5), ( 'ppfcz3', 14, 12, 139.5),
1205	( 'ppfcz3', 14, 13, 147.5), ( 'ppfcz3', 14, 14, 155.5), ( 'ppfcz3', 14, 15, 163.5),
1206	( 'ppfcz3', 14, 16, 171.5), ( 'ppfcz3', 14, 17, 179.5), ( 'ppfcz3', 14, 18, 187.5),
1207	( 'ppfcz3', 14, 19, 195.5), ( 'ppfcz3', 14, 20, 203.5), ( 'ppfcz3', 14, 21, 210.5),
1208	( 'ppfcz3', 14, 22, 217.5), ( 'ppfcz3', 14, 23, 224.5), ( 'ppfcz3', 14, 24, 231.5),
1209	( 'ppfcz3', 14, 25, 238.5), ( 'ppfcz3', 14, 26, 245.5), ( 'ppfcz3', 14, 27, 252.5),
1210	( 'ppfcz3', 14, 28, 259.5), ( 'ppfcz3', 14, 29, 266.5), ( 'ppfcz3', 14, 30, 273.5),
1211	( 'ppfcz4', 14, 2, 54.5), ( 'ppfcz4', 14, 3, 68.5), ( 'ppfcz4', 14, 4, 81.5),
1212	( 'ppfcz4', 14, 5, 95.5), ( 'ppfcz4', 14, 6, 108.5), ( 'ppfcz4', 14, 7, 121.5),
1213	( 'ppfcz4', 14, 8, 134.5), ( 'ppfcz4', 14, 9, 147.5), ( 'ppfcz4', 14, 10, 160.5),
1214	( 'ppfcz4', 14, 11, 168.5), ( 'ppfcz4', 14, 12, 178.5), ( 'ppfcz4', 14, 13, 188.5),
1215	( 'ppfcz4', 14, 14, 198.5), ( 'ppfcz4', 14, 15, 208.5), ( 'ppfcz4', 14, 16, 216.5),
1216	( 'ppfcz4', 14, 17, 224.5), ( 'ppfcz4', 14, 18, 232.5), ( 'ppfcz4', 14, 19, 240.5),
1217	( 'ppfcz4', 14, 20, 248.5), ( 'ppfcz4', 14, 21, 256.5), ( 'ppfcz4', 14, 22, 264.5),
1218	( 'ppfcz4', 14, 23, 272.5), ( 'ppfcz4', 14, 24, 280.5), ( 'ppfcz4', 14, 25, 288.5),
1219	( 'ppfcz4', 14, 26, 296.5), ( 'ppfcz4', 14, 27, 304.5), ( 'ppfcz4', 14, 28, 312.5),
1220	( 'ppfcz4', 14, 29, 320.5), ( 'ppfcz4', 14, 30, 328.5), ( 'ppfcz5', 14, 2, 66.5),
1221	( 'ppfcz5', 14, 3, 84.5), ( 'ppfcz5', 14, 4, 102.5), ( 'ppfcz5', 14, 5, 120.5),
1222	( 'ppfcz5', 14, 6, 137.5), ( 'ppfcz5', 14, 7, 154.5), ( 'ppfcz5', 14, 8, 171.5),
1223	( 'ppfcz5', 14, 9, 188.5), ( 'ppfcz5', 14, 10, 205.5), ( 'ppfcz5', 14, 11, 220.5),
1224	( 'ppfcz5', 14, 12, 235.5), ( 'ppfcz5', 14, 13, 250.5), ( 'ppfcz5', 14, 14, 265.5),
1225	( 'ppfcz5', 14, 15, 280.5), ( 'ppfcz5', 14, 16, 295.5), ( 'ppfcz5', 14, 17, 310.5),
1226	( 'ppfcz5', 14, 18, 325.5), ( 'ppfcz5', 14, 19, 340.5), ( 'ppfcz5', 14, 20, 355.5),
1227	( 'ppfcz5', 14, 21, 368.5), ( 'ppfcz5', 14, 22, 381.5), ( 'ppfcz5', 14, 23, 394.5),
1228	( 'ppfcz5', 14, 24, 407.5), ( 'ppfcz5', 14, 25, 420.5), ( 'ppfcz5', 14, 26, 433.5),
1229	( 'ppfcz5', 14, 27, 446.5), ( 'ppfcz5', 14, 28, 459.5), ( 'ppfcz5', 14, 29, 472.5),
1230	( 'ppfcz5', 14, 30, 485.5), ( 'ppfcz1', 30, 0.5, 56.5), ( 'ppfcz1', 30, 1, 63.5),
1231	( 'ppfcz1', 30, 1.5, 69.5), ( 'ppfcz1', 30, 2, 75.5), ( 'ppfcz1', 30, 2.5, 80.5),
1232	( 'ppfcz1', 30, 3, 86.5), ( 'ppfcz1', 30, 3.5, 92.5), ( 'ppfcz1', 30, 4, 99.5),
1233	( 'ppfcz1', 30, 4.5, 105.5), ( 'ppfcz1', 30, 5, 111.5), ( 'ppfcz1', 30, 6, 118.5),
1234	( 'ppfcz1', 30, 7, 126.5), ( 'ppfcz1', 30, 8, 133.5), ( 'ppfcz1', 30, 9, 141.5),
1235	( 'ppfcz1', 30, 10, 148.5), ( 'ppfcz1', 30, 11, 156.5), ( 'ppfcz1', 30, 12, 163.5),
1236	( 'ppfcz1', 30, 13, 171.5), ( 'ppfcz1', 30, 14, 178.5), ( 'ppfcz1', 30, 15, 186.5),
1237	( 'ppfcz1', 30, 16, 193.5), ( 'ppfcz1', 30, 17, 201.5), ( 'ppfcz1', 30, 18, 209.5),
1238	( 'ppfcz1', 30, 19, 216.5), ( 'ppfcz1', 30, 20, 224.5), ( 'ppfcz1', 30, 21, 231.5),
1239	( 'ppfcz1', 30, 22, 239.5), ( 'ppfcz1', 30, 23, 246.5), ( 'ppfcz1', 30, 24, 254.5),
1240	( 'ppfcz1', 30, 25, 261.5), ( 'ppfcz1', 30, 26, 269.5), ( 'ppfcz1', 30, 27, 276.5),
1241	( 'ppfcz1', 30, 28, 284.5), ( 'ppfcz1', 30, 29, 291.5), ( 'ppfcz1', 30, 30, 299.5),
1242	( 'ppfcz2', 30, 0.5, 61.5), ( 'ppfcz2', 30, 1, 65.5), ( 'ppfcz2', 30, 1.5, 75.5),
1243	( 'ppfcz2', 30, 2, 80.5), ( 'ppfcz2', 30, 2.5, 86.5), ( 'ppfcz2', 30, 3, 99.5),
1244	( 'ppfcz2', 30, 3.5, 109.5), ( 'ppfcz2', 30, 4, 113.5), ( 'ppfcz2', 30, 4.5, 121.5),
1245	( 'ppfcz2', 30, 5, 129.5), ( 'ppfcz2', 30, 6, 139.5), ( 'ppfcz2', 30, 7, 149.5),
1246	( 'ppfcz2', 30, 8, 159.5), ( 'ppfcz2', 30, 9, 169.5), ( 'ppfcz2', 30, 10, 180.5),
1247	( 'ppfcz2', 30, 11, 189.5), ( 'ppfcz2', 30, 12, 199.5), ( 'ppfcz2', 30, 13, 210.5),
1248	( 'ppfcz2', 30, 14, 219.5), ( 'ppfcz2', 30, 15, 229.5), ( 'ppfcz2', 30, 16, 240.5),
1249	( 'ppfcz2', 30, 17, 249.5), ( 'ppfcz2', 30, 18, 259.5), ( 'ppfcz2', 30, 19, 270.5),
1250	( 'ppfcz2', 30, 20, 280.5), ( 'ppfcz2', 30, 21, 289.5), ( 'ppfcz2', 30, 22, 300.5),
1251	( 'ppfcz2', 30, 23, 310.5), ( 'ppfcz2', 30, 24, 320.5), ( 'ppfcz2', 30, 25, 330.5),
1252	( 'ppfcz2', 30, 26, 340.5), ( 'ppfcz2', 30, 27, 350.5), ( 'ppfcz2', 30, 28, 360.5),
1253	( 'ppfcz2', 30, 29, 370.5), ( 'ppfcz2', 30, 30, 381.5), ( 'ppfcz3', 30, 0.5, 74.5),
1254	( 'ppfcz3', 30, 1, 83.5), ( 'ppfcz3', 30, 1.5, 90.5), ( 'ppfcz3', 30, 2, 99.5),
1255	( 'ppfcz3', 30, 2.5, 107.5), ( 'ppfcz3', 30, 3, 114.5), ( 'ppfcz3', 30, 3.5, 122.5),
1256	( 'ppfcz3', 30, 4, 130.5), ( 'ppfcz3', 30, 4.5, 140.5), ( 'ppfcz3', 30, 5, 147.5),
1257	( 'ppfcz3', 30, 6, 162.5), ( 'ppfcz3', 30, 7, 174.5), ( 'ppfcz3', 30, 8, 188.5),
1258	( 'ppfcz3', 30, 9, 201.5), ( 'ppfcz3', 30, 10, 213.5), ( 'ppfcz3', 30, 11, 227.5),
1259	( 'ppfcz3', 30, 12, 240.5), ( 'ppfcz3', 30, 13, 252.5), ( 'ppfcz3', 30, 14, 266.5),
1260	( 'ppfcz3', 30, 15, 278.5), ( 'ppfcz3', 30, 16, 290.5), ( 'ppfcz3', 30, 17, 304.5),
1261	( 'ppfcz3', 30, 18, 317.5), ( 'ppfcz3', 30, 19, 330.5), ( 'ppfcz3', 30, 20, 343.5),
1262	( 'ppfcz3', 30, 21, 354.5), ( 'ppfcz3', 30, 22, 363.5), ( 'ppfcz3', 30, 23, 375.5),
1263	( 'ppfcz3', 30, 24, 385.5), ( 'ppfcz3', 30, 25, 396.5), ( 'ppfcz3', 30, 26, 405.5),
1264	( 'ppfcz3', 30, 27, 417.5), ( 'ppfcz3', 30, 28, 428.5), ( 'ppfcz3', 30, 29, 438.5),
1265	( 'ppfcz3', 30, 30, 448.5), ( 'ppfcz4', 30, 0.5, 90.5), ( 'ppfcz4', 30, 1, 104.5),
1266	( 'ppfcz4', 30, 1.5, 118.5), ( 'ppfcz4', 30, 2, 134.5), ( 'ppfcz4', 30, 2.5, 146.5),
1267	( 'ppfcz4', 30, 3, 163.5), ( 'ppfcz4', 30, 3.5, 179.5), ( 'ppfcz4', 30, 4, 195.5),
1268	( 'ppfcz4', 30, 4.5, 211.5), ( 'ppfcz4', 30, 5, 232.5), ( 'ppfcz4', 30, 6, 257.5),
1269	( 'ppfcz4', 30, 7, 278.5), ( 'ppfcz4', 30, 8, 300.5), ( 'ppfcz4', 30, 9, 321.5),
1270	( 'ppfcz4', 30, 10, 343.5), ( 'ppfcz4', 30, 11, 364.5), ( 'ppfcz4', 30, 12, 386.5),
1271	( 'ppfcz4', 30, 13, 407.5), ( 'ppfcz4', 30, 14, 429.5), ( 'ppfcz4', 30, 15, 450.5),
1272	( 'ppfcz4', 30, 16, 472.5), ( 'ppfcz4', 30, 17, 493.5), ( 'ppfcz4', 30, 18, 515.5),
1273	( 'ppfcz4', 30, 19, 536.5), ( 'ppfcz4', 30, 20, 558.5), ( 'ppfcz4', 30, 21, 579.5),
1274	( 'ppfcz4', 30, 22, 601.5), ( 'ppfcz4', 30, 23, 622.5), ( 'ppfcz4', 30, 24, 644.5),
1275	( 'ppfcz4', 30, 25, 665.5), ( 'ppfcz4', 30, 26, 687.5), ( 'ppfcz4', 30, 27, 708.5),
1276	( 'ppfcz4', 30, 28, 730.5), ( 'ppfcz4', 30, 29, 751.5), ( 'ppfcz4', 30, 30, 773.5),
1277	( 'ppfcz5', 30, 0.5, 97.5), ( 'ppfcz5', 30, 1, 114.5), ( 'ppfcz5', 30, 1.5, 131.5),
1278	( 'ppfcz5', 30, 2, 148.5), ( 'ppfcz5', 30, 2.5, 165.5), ( 'ppfcz5', 30, 3, 183.5),
1279	( 'ppfcz5', 30, 3.5, 200.5), ( 'ppfcz5', 30, 4, 221.5), ( 'ppfcz5', 30, 4.5, 243.5),
1280	( 'ppfcz5', 30, 5, 264.5), ( 'ppfcz5', 30, 6, 289.5), ( 'ppfcz5', 30, 7, 313.5),
1281	( 'ppfcz5', 30, 8, 336.5), ( 'ppfcz5', 30, 9, 360.5), ( 'ppfcz5', 30, 10, 384.5),
1282	( 'ppfcz5', 30, 11, 407.5), ( 'ppfcz5', 30, 12, 431.5), ( 'ppfcz5', 30, 13, 455.5),
1283	( 'ppfcz5', 30, 14, 478.5), ( 'ppfcz5', 30, 15, 502.5), ( 'ppfcz5', 30, 16, 526.5),
1284	( 'ppfcz5', 30, 17, 549.5), ( 'ppfcz5', 30, 18, 573.5), ( 'ppfcz5', 30, 19, 597.5),
1285	( 'ppfcz5', 30, 20, 620.5), ( 'ppfcz5', 30, 21, 644.5), ( 'ppfcz5', 30, 22, 668.5),
1286	( 'ppfcz5', 30, 23, 691.5), ( 'ppfcz5', 30, 24, 715.5), ( 'ppfcz5', 30, 25, 738.5),
1287	( 'ppfcz5', 30, 26, 762.5), ( 'ppfcz5', 30, 27, 786.5), ( 'ppfcz5', 30, 28, 809.5),
1288	( 'ppfcz5', 30, 29, 833.5), ( 'ppfcz5', 30, 30, 857.5), ( 'foreign', 13, 30, 0),
1289	( 'all', 32, 10000, 23.2342007434944);
1290
1291	--enable_query_log
1292
1293	INSERT INTO t3 SELECT * FROM t1;
1294
1295	EXPLAIN
1296	SELECT d FROM t1, t2
1297	WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1298	ORDER BY t2.c LIMIT 1;
1299	SELECT d FROM t1, t2
1300	WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1301	ORDER BY t2.c LIMIT 1;
1302
1303	EXPLAIN
1304	SELECT d FROM t3 AS t1, t2 AS t2
1305	WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1306	ORDER BY t2.c LIMIT 1;
1307	SELECT d FROM t3 AS t1, t2 AS t2
1308	WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1309	ORDER BY t2.c LIMIT 1;
1310
1311	DROP TABLE t1,t2,t3;