6
drop table if exists t1,t2,t3;
10
id int DEFAULT '0' NOT NULL,
12
clee varchar(20) NOT NULL,
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');
28
id int NOT NULL auto_increment,
29
description varchar(40) NOT NULL,
31
ordre int DEFAULT '0' NOT NULL,
38
# Dumping data for table 't2'
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');
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;
58
# Test of ORDER BY on concat() result
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;
70
# bug in distinct + order by
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;
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;
93
# Order by on first index part
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;
105
# Test of order by on field()
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');
114
SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test;
118
# Test of ORDER BY on IF
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');
154
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
156
LEFT JOIN t2 USING(c)
157
LEFT JOIN t3 ON t3.c = t1.c;
159
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
161
LEFT JOIN t2 ON t1.c = t2.c
162
LEFT JOIN t3 ON t3.c = t1.c;
164
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
166
LEFT JOIN t2 USING(c)
167
LEFT JOIN t3 ON t3.c = t1.c
170
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
172
LEFT JOIN t2 ON t1.c = t2.c
173
LEFT JOIN t3 ON t3.c = t1.c
179
# Test of ORDER BY (Bug found by Dean Edmonds)
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;
192
#bug reported by Wouter de Jong
194
CREATE TEMPORARY TABLE t1 (
195
member_id int 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 '',
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)
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;
231
# Test optimization of ORDER BY DESC
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');
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
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;
255
# Test things when we don't have NULL keys
258
delete from t1 WHERE b IS NULL OR c IS NULL;
259
alter table t1 modify b bigint not null, modify c varchar(100) not null;
260
explain select * from t1 order by a, b, c;
261
select * from t1 order by a, b, c;
262
explain select * from t1 order by a desc, b desc, c desc;
263
select * from t1 order by a desc, b desc, c desc;
264
# test multiple ranges, NO_MAX_RANGE and EQ_RANGE
265
explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
266
select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
267
# test NEAR_MAX, NO_MIN_RANGE
268
explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
269
select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
270
select count(*) from t1 where a < 5 and b > 0;
271
select * from t1 where a < 5 and b > 0 order by a desc,b desc;
272
# test HA_READ_AFTER_KEY (at the end of the file), NEAR_MIN
273
explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
274
select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
275
# test HA_READ_AFTER_KEY (in the middle of the file)
276
explain select * from t1 where a between 0 and 1 order by a desc, b desc;
277
select * from t1 where a between 0 and 1 order by a desc, b desc;
281
CREATE TEMPORARY TABLE t1 (
282
gid int NOT NULL auto_increment,
283
cid int NOT NULL default '0',
285
KEY component_id (cid)
287
INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108);
288
ALTER TABLE t1 add skr int not null;
290
CREATE TEMPORARY TABLE t2 (
291
gid int NOT NULL default '0',
292
uid int NOT NULL default '1',
293
sid int NOT NULL default '1',
298
INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5);
300
CREATE TEMPORARY TABLE t3 (
301
uid int NOT NULL auto_increment,
304
INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250);
305
ALTER TABLE t3 add skr int not null;
307
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;
308
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;
310
# The following ORDER BY can be optimimized
311
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;
312
SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
314
# The following ORDER BY can't be optimimized
315
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;
316
SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
317
SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
321
# Test of bug when doing an ORDER BY with const items
325
`titre` char(80) NOT NULL default '',
326
`numeropost` int NOT NULL auto_increment,
328
`auteur` char(35) NOT NULL default '',
329
`icone` int NOT NULL default '0',
330
`lastauteur` char(35) NOT NULL default '',
331
`nbrep` int NOT NULL default '0',
332
`dest` char(35) NOT NULL default '',
333
`lu` int NOT NULL default '0',
334
`vue` int NOT NULL default '0',
335
`ludest` int NOT NULL default '0',
336
`ouvert` int NOT NULL default '1',
337
PRIMARY KEY (`numeropost`),
339
KEY `dest` (`dest`,`ludest`),
340
KEY `auteur` (`auteur`,`lu`),
341
KEY `auteur_2` (`auteur`,`date`),
342
KEY `dest_2` (`dest`,`date`)
346
`numeropost` int NOT NULL default '0',
347
`pseudo` char(35) NOT NULL default '',
348
PRIMARY KEY (`numeropost`,`pseudo`),
349
KEY `pseudo` (`pseudo`)
352
INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
353
INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
354
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;
355
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;
356
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;
357
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;
361
# Test order by with NULL values
363
CREATE TABLE t1 (a int, b int);
364
INSERT INTO t1 VALUES (1, 2);
365
INSERT INTO t1 VALUES (3, 4);
366
INSERT INTO t1 VALUES (5, NULL);
367
SELECT * FROM t1 ORDER BY b;
368
SELECT * FROM t1 ORDER BY b DESC;
369
SELECT * FROM t1 ORDER BY (a + b);
370
SELECT * FROM t1 ORDER BY (a + b) DESC;
374
# Test of FORCE INDEX ... ORDER BY
377
create table t1(id int not null auto_increment primary key, t char(12));
383
eval insert into t1(t) values ('$1');
388
select id,t from t1 force index (primary) order by id;
392
# Test of test_if_subkey() function
395
FieldKey varchar(36) NOT NULL default '',
396
LongVal bigint default NULL,
398
KEY FieldKey (FieldKey),
399
KEY LongField (FieldKey,LongVal),
400
KEY StringField (FieldKey,StringVal(32))
402
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');
403
SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
404
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
405
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
408
# Bug #1945 - Crashing bug with bad User Variables in UPDATE ... ORDER BY ...
410
CREATE TABLE t1 (a INT, b INT);
412
UPDATE t1 SET a=0 ORDER BY (a=@id), b;
416
# Bug when doing an order by on a 1 byte string (Bug #2147)
419
CREATE TEMPORARY TABLE t1 ( id int NOT NULL default '0', menu int NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM;
420
INSERT INTO t1 VALUES (11384, 2),(11392, 2);
421
SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
425
# REF_OR_NULL optimization + filesort (bug #2419)
428
create table t1(a int, b int, index(b));
429
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
430
explain select * from t1 where b=1 or b is null order by a;
431
select * from t1 where b=1 or b is null order by a;
432
explain select * from t1 where b=2 or b is null order by a;
433
select * from t1 where b=2 or b is null order by a;
437
# Bug #3155 - Strange results with index (x, y) ... WHERE ... ORDER BY pk
440
create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null,
441
key(a,b,d), key(c,b,a));
442
create table t2 like t1;
443
insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3);
444
insert into t2 select null, b, c, d from t1;
445
insert into t1 select null, b, c, d from t2;
446
insert into t2 select null, b, c, d from t1;
447
insert into t1 select null, b, c, d from t2;
448
insert into t2 select null, b, c, d from t1;
449
insert into t1 select null, b, c, d from t2;
450
insert into t2 select null, b, c, d from t1;
451
insert into t1 select null, b, c, d from t2;
452
insert into t2 select null, b, c, d from t1;
453
insert into t1 select null, b, c, d from t2;
454
alter table t1 engine="default";
456
insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10;
457
select * from t1 where a=1 and b in (1) order by c, b, a;
458
select * from t1 where a=1 and b in (1);
463
# Ambiguos order by when renamed column is identical to another in result.
464
# Should not fail and prefer column from t1 for sorting.
466
create table t1 (col1 int, col int);
467
create table t2 (col2 int, col int);
468
insert into t1 values (1,1),(2,2),(3,3);
469
insert into t2 values (1,3),(2,2),(3,1);
470
select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2)
474
# Let us also test various ambiguos and potentially ambiguos cases
478
select col1 as col, col from t1 order by col;
480
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
483
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
486
select col1 from t1, t2 where t1.col1=t2.col2 order by col;
488
select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2
491
select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2
493
select col2 as c, col as c from t2 order by col;
494
select col2 as col, col as col2 from t2 order by col;
495
select t2.col2, t2.col, t2.col from t2 order by col;
497
select t2.col2 as col from t2 order by t2.col;
498
select t2.col2 as col, t2.col from t2 order by t2.col;
499
select t2.col2, t2.col, t2.col from t2 order by t2.col;
504
# Bug #5428: a problem with small max_sort_length value
507
create table t1 (a char(25));
508
insert into t1 set a = repeat('x', 20);
509
insert into t1 set a = concat(repeat('x', 19), 'z');
510
insert into t1 set a = concat(repeat('x', 19), 'ab');
511
insert into t1 set a = concat(repeat('x', 19), 'aa');
512
set max_sort_length=20;
513
select a from t1 order by a;
520
create temporary table t1 (
521
`sid` decimal(8,0) default null,
522
`wnid` varchar(11) not null default '',
523
key `wnid14` (`wnid`(4)),
527
insert into t1 (`sid`, `wnid`) values
528
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
529
('39560','01019090000'),('37989','01019000000'),('37990','01019011000'),
530
('37991','01019011000'),('37992','01019019000'),('37993','01019030000'),
531
('37994','01019090000'),('475','02070000000'),('25253','02071100000'),
532
('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),
533
('25259','02071190000'),('25260','02071200000'),('25261','02071210000'),
534
('25262','02071290000'),('25263','02071300000'),('25264','02071310000'),
535
('25265','02071310000'),('25266','02071320000'),('25267','02071320000'),
536
('25269','02071330000'),('25270','02071340000'),('25271','02071350000'),
537
('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),
538
('25282','02071391000'),('25283','02071399000'),('25284','02071400000'),
539
('25285','02071410000'),('25286','02071410000'),('25287','02071420000'),
540
('25288','02071420000'),('25291','02071430000'),('25290','02071440000'),
541
('25292','02071450000'),('25293','02071460000'),('25294','02071470000'),
542
('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
544
explain select * from t1 where wnid like '0101%' order by wnid;
546
select * from t1 where wnid like '0101%' order by wnid;
551
# Bug #7672 - a wrong result for a select query in braces followed by order by
554
CREATE TABLE t1 (a int);
555
INSERT INTO t1 VALUES (2), (1), (1), (2), (1);
556
SELECT a FROM t1 ORDER BY a;
557
(SELECT a FROM t1) ORDER BY a;
561
# Bug #18767: global ORDER BY applied to a SELECT with ORDER BY either was
562
# ignored or 'concatened' to the latter.
564
CREATE TABLE t1 (a int, b int);
565
INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10);
567
(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a;
568
(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC;
569
(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b;
570
(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b;
575
# Bug #22457: Column alias in ORDER BY works, but not if in an expression
578
CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2);
579
SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
580
SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
581
SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
582
SELECT a + 1 AS num FROM t1 HAVING 30 - num;
584
SELECT a + 1 AS num, num + 1 FROM t1;
585
SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
587
SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
591
# Bug#25126: Reference to non-existant column in UPDATE...ORDER BY...
594
CREATE TABLE bug25126 (
595
val int NOT NULL AUTO_INCREMENT PRIMARY KEY
598
UPDATE bug25126 SET MissingCol = MissingCol;
600
UPDATE bug25126 SET val = val ORDER BY MissingCol;
601
UPDATE bug25126 SET val = val ORDER BY val;
602
UPDATE bug25126 SET val = 1 ORDER BY val;
604
UPDATE bug25126 SET val = 1 ORDER BY MissingCol;
606
UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol;
608
UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol;
610
UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol;
612
UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol;
614
UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol;
616
UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol;
620
# Bug #25427: crash when order by expression contains a name
621
# that cannot be resolved unambiguously
624
CREATE TABLE t1 (a int);
626
SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1;
628
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val;
630
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1;
635
# Bug #27532: ORDER/GROUP BY expressions with IN/BETWEEN and NOT IN/BETWEEN
638
CREATE TABLE t1 (a int);
639
INSERT INTO t1 VALUES (3), (2), (4), (1);
641
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
642
ORDER BY IF(a IN (2,3), a, a+10);
643
SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1
644
ORDER BY IF(a NOT IN (2,3), a, a+10);
645
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
646
ORDER BY IF(a NOT IN (2,3), a, a+10);
648
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
649
ORDER BY IF(a BETWEEN 2 AND 3, a, a+10);
650
SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1
651
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);
652
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
653
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);
655
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
656
FROM t1 GROUP BY x1, x2;
657
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
658
FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, '');
660
# The remaining queries are for better coverage
661
SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2);
662
SELECT a FROM t1 ORDER BY a IN (1,2);
663
SELECT a+10 FROM t1 ORDER BY a IN (1,2);
664
SELECT a, IF(a IN (1,2), a, a+10) FROM t1
665
ORDER BY IF(a IN (3,4), a, a+10);
669
create table t1 (a int not null, b int not null, c int not null);
670
insert t1 values (1,1,1),(1,1,2),(1,2,1);
671
select a, b from t1 group by a, b order by sum(c);
675
# Bug#21302: Result not properly sorted when using an ORDER BY on a second
678
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
679
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
681
explain SELECT t1.b as a, t2.b as c FROM
682
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
684
SELECT t2.b as c FROM
685
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
688
# check that it still removes sort of const table
689
explain SELECT t1.b as a, t2.b as c FROM
690
t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
693
CREATE TABLE t2 LIKE t1;
694
INSERT INTO t2 SELECT * from t1;
695
CREATE TABLE t3 LIKE t1;
696
INSERT INTO t3 SELECT * from t1;
697
CREATE TABLE t4 LIKE t1;
698
INSERT INTO t4 SELECT * from t1;
699
INSERT INTO t1 values (0,0),(4,4);
701
SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
702
ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
704
DROP TABLE t1,t2,t3,t4;
707
# Bug#25376: Incomplete setup of ORDER BY clause results in a wrong result.
709
create table t1 (a int, b int, c int);
710
insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
711
select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
715
# BUG#16590: Optimized does not do right "const" table pre-read
717
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
718
INSERT INTO t1 VALUES (1,1),(2,2);
720
CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
721
INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
723
SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
730
# Bug #30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
738
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
739
INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
740
INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
741
INSERT INTO t1 SELECT a +16, b +16 FROM t1;
742
INSERT INTO t1 SELECT a +32, b +32 FROM t1;
743
INSERT INTO t1 SELECT a +64, b +64 FROM t1;
745
SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
747
SELECT @tmp_tables_after = @tmp_tables_before ;
749
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
751
SELECT @tmp_tables_after = @tmp_tables_before;
755
--echo # Bug#31590: Wrong error message on sort buffer being too small.
757
create table t1(a int, b text);
758
insert into t1 values (1,2),(3,2);
759
set session sort_buffer_size= 30000;
760
set session max_sort_length= 2180;
761
select * from t1 order by b;
765
# Bug #35206: select query result different if the key is indexed or not
768
CREATE TABLE t2 (a varchar(32), b int, c float, d double,
769
UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c));
771
CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));
772
CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b));
775
INSERT INTO t1 (a, b) VALUES
776
('domestic', 'CH'), ('domestic', 'LI'), ('plfcz1', 'FR'), ('all', 'AD'),
777
('all', 'AE'), ('all', 'AF'), ('all', 'AG'), ('all', 'AI'), ('all', 'AL'),
778
('all', 'AM'), ('all', 'AN'), ('all', 'AO'), ('all', 'AP'), ('all', 'AQ'),
779
('all', 'AR'), ('all', 'AS'), ('all', 'AT'), ('all', 'AU'), ('all', 'AW'),
780
('all', 'AZ'), ('all', 'BA'), ('all', 'BB'), ('all', 'BD'), ('all', 'BE'),
781
('all', 'BF'), ('all', 'BG'), ('all', 'BH'), ('all', 'BI'), ('all', 'BJ'),
782
('all', 'BM'), ('all', 'BN'), ('all', 'BO'), ('all', 'BR'), ('all', 'BS'),
783
('all', 'BT'), ('all', 'BV'), ('all', 'BW'), ('all', 'BY'), ('all', 'BZ'),
784
('all', 'CA'), ('all', 'CC'), ('all', 'CD'), ('all', 'CF'), ('all', 'CG'),
785
('all', 'CH'), ('all', 'CI'), ('all', 'CK'), ('all', 'CL'), ('all', 'CM'),
786
('all', 'CN'), ('all', 'CO'), ('all', 'CR'), ('all', 'CU'), ('all', 'CV'),
787
('all', 'CX'), ('all', 'CY'), ('all', 'CZ'), ('all', 'DE'), ('all', 'DJ'),
788
('all', 'DK'), ('all', 'DM'), ('all', 'DO'), ('all', 'DZ'), ('all', 'EC'),
789
('all', 'EE'), ('all', 'EG'), ('all', 'EH'), ('all', 'EI'), ('all', 'ER'),
790
('all', 'ES'), ('all', 'ET'), ('all', 'FI'), ('all', 'FJ'), ('all', 'FK'),
791
('all', 'FM'), ('all', 'FO'), ('all', 'FR'), ('all', 'FX'), ('all', 'GA'),
792
('all', 'GB'), ('all', 'GD'), ('all', 'GE'), ('all', 'GF'), ('all', 'GH'),
793
('all', 'GI'), ('all', 'GL'), ('all', 'GM'), ('all', 'GN'), ('all', 'GP'),
794
('all', 'GQ'), ('all', 'GR'), ('all', 'GS'), ('all', 'GT'), ('all', 'GU'),
795
('all', 'GW'), ('all', 'GY'), ('all', 'HK'), ('all', 'HM'), ('all', 'HN'),
796
( 'all', 'HR'), ( 'all', 'HT'), ( 'all', 'HU'), ( 'all', 'ID'), ( 'all', 'IE'),
797
( 'all', 'IL'), ( 'all', 'IN'), ( 'all', 'IO'), ( 'all', 'IQ'), ( 'all', 'IR'),
798
( 'all', 'IS'), ( 'all', 'IT'), ( 'all', 'JM'), ( 'all', 'JO'), ( 'all', 'JP'),
799
( 'all', 'KE'), ( 'all', 'KG'), ( 'all', 'KH'), ( 'all', 'KI'), ( 'all', 'KM'),
800
( 'all', 'KN'), ( 'all', 'KP'), ( 'all', 'KR'), ( 'all', 'KW'), ( 'all', 'KY'),
801
( 'all', 'KZ'), ( 'all', 'LA'), ( 'all', 'LB'), ( 'all', 'LC'), ( 'all', 'LI'),
802
( 'all', 'LK'), ( 'all', 'LR'), ( 'all', 'LS'), ( 'all', 'LT'), ( 'all', 'LU'),
803
( 'all', 'LV'), ( 'all', 'LY'), ( 'all', 'MA'), ( 'all', 'MC'), ( 'all', 'MD'),
804
( 'all', 'ME'), ( 'all', 'MG'), ( 'all', 'MH'), ( 'all', 'MK'), ( 'all', 'ML'),
805
( 'all', 'MM'), ( 'all', 'MN'), ( 'all', 'MO'), ( 'all', 'MP'), ( 'all', 'MQ'),
806
( 'all', 'MR'), ( 'all', 'MS'), ( 'all', 'MT'), ( 'all', 'MU'), ( 'all', 'MV'),
807
( 'all', 'MW'), ( 'all', 'MX'), ( 'all', 'MY'), ( 'all', 'MZ'), ( 'all', 'NA'),
808
( 'all', 'NC'), ( 'all', 'NE'), ( 'all', 'NF'), ( 'all', 'NG'), ( 'all', 'NI'),
809
( 'all', 'NL'), ( 'all', 'NO'), ( 'all', 'NP'), ( 'all', 'NR'), ( 'all', 'NU'),
810
( 'all', 'NV'), ( 'all', 'NZ'), ( 'all', 'OM'), ( 'all', 'PA'), ( 'all', 'PE'),
811
( 'all', 'PF'), ( 'all', 'PG'), ( 'all', 'PH'), ( 'all', 'PK'), ( 'all', 'PL'),
812
( 'all', 'PM'), ( 'all', 'PN'), ( 'all', 'PR'), ( 'all', 'PS'), ( 'all', 'PT'),
813
( 'all', 'PW'), ( 'all', 'PY'), ( 'all', 'QA'), ( 'all', 'RE'), ( 'all', 'RO'),
814
( 'all', 'RU'), ( 'all', 'RW'), ( 'all', 'SA'), ( 'all', 'SB'), ( 'all', 'SC'),
815
( 'all', 'SD'), ( 'all', 'SE'), ( 'all', 'SG'), ( 'all', 'SH'), ( 'all', 'SI'),
816
( 'all', 'SJ'), ( 'all', 'SK'), ( 'all', 'SL'), ( 'all', 'SM'), ( 'all', 'SN'),
817
( 'all', 'SO'), ( 'all', 'SR'), ( 'all', 'ST'), ( 'all', 'SV'), ( 'all', 'SY'),
818
( 'all', 'SZ'), ( 'all', 'TA'), ( 'all', 'TC'), ( 'all', 'TD'), ( 'all', 'TF'),
819
( 'all', 'TG'), ( 'all', 'TH'), ( 'all', 'TJ'), ( 'all', 'TK'), ( 'all', 'TM'),
820
( 'all', 'TN'), ( 'all', 'TO'), ( 'all', 'TP'), ( 'all', 'TR'), ( 'all', 'TT'),
821
( 'all', 'TV'), ( 'all', 'TW'), ( 'all', 'TZ'), ( 'all', 'UA'), ( 'all', 'UG'),
822
( 'all', 'UM'), ( 'all', 'US'), ( 'all', 'UY'), ( 'all', 'UZ'), ( 'all', 'VA'),
823
( 'all', 'VC'), ( 'all', 'VE'), ( 'all', 'VG'), ( 'all', 'VI'), ( 'all', 'VN'),
824
( 'all', 'VU'), ( 'all', 'WF'), ( 'all', 'WS'), ( 'plfcz1', 'FI'),
825
( 'all', 'XE'), ( 'all', 'XS'), ( 'all', 'XU'), ( 'plfcz1', 'XE'),
826
( 'all', 'YE'), ( 'all', 'YT'), ( 'all', 'YU'), ( 'all', 'ZA'), ( 'all', 'ZM'),
827
( 'all', 'ZR'), ( 'all', 'ZW'), ( 'foreign', 'AD'), ( 'foreign', 'AE'),
828
( 'foreign', 'AF'), ( 'foreign', 'AG'), ( 'foreign', 'AI'),
829
( 'foreign', 'AL'), ( 'foreign', 'AM'), ( 'foreign', 'AN'), ( 'foreign', 'AO'),
830
( 'foreign', 'AP'), ( 'foreign', 'AQ'), ( 'foreign', 'AR'), ( 'foreign', 'AS'),
831
( 'foreign', 'AT'), ( 'foreign', 'AU'), ( 'foreign', 'AW'), ( 'foreign', 'AZ'),
832
( 'foreign', 'BA'), ( 'foreign', 'BB'), ( 'foreign', 'BD'), ( 'foreign', 'BE'),
833
( 'foreign', 'BF'), ( 'foreign', 'BG'), ( 'foreign', 'BH'), ( 'foreign', 'BI'),
834
( 'foreign', 'BJ'), ( 'foreign', 'BM'), ( 'foreign', 'BN'), ( 'foreign', 'BO'),
835
( 'foreign', 'BR'), ( 'foreign', 'BS'), ( 'foreign', 'BT'), ( 'foreign', 'BV'),
836
( 'foreign', 'BW'), ( 'foreign', 'BY'), ( 'foreign', 'BZ'), ( 'foreign', 'CA'),
837
( 'foreign', 'CC'), ( 'foreign', 'CD'), ( 'foreign', 'CF'), ( 'foreign', 'CG'),
838
( 'foreign', 'CI'), ( 'foreign', 'CK'), ( 'foreign', 'CL'), ( 'foreign', 'CM'),
839
( 'foreign', 'CN'), ( 'foreign', 'CO'), ( 'foreign', 'CR'), ( 'foreign', 'CU'),
840
( 'foreign', 'CV'), ( 'foreign', 'CX'), ( 'foreign', 'CY'), ( 'foreign', 'CZ'),
841
( 'foreign', 'DE'), ( 'foreign', 'DJ'), ( 'foreign', 'DK'), ( 'foreign', 'DM'),
842
( 'foreign', 'DO'), ( 'foreign', 'DZ'), ( 'foreign', 'EC'), ( 'foreign', 'EE'),
843
( 'foreign', 'EG'), ( 'foreign', 'EH'), ( 'foreign', 'EI'), ( 'foreign', 'ER'),
844
( 'foreign', 'ES'), ( 'foreign', 'ET'), ( 'foreign', 'FI'), ( 'foreign', 'FJ'),
845
( 'foreign', 'FK'), ( 'foreign', 'FM'), ( 'foreign', 'FO'), ( 'foreign', 'FR'),
846
( 'foreign', 'FX'), ( 'foreign', 'GA'), ( 'foreign', 'GB'), ( 'foreign', 'GD'),
847
( 'foreign', 'GE'), ( 'foreign', 'GF'), ( 'foreign', 'GH'), ( 'foreign', 'GI'),
848
( 'foreign', 'GL'), ( 'foreign', 'GM'), ( 'foreign', 'GN'), ( 'foreign', 'GP'),
849
( 'foreign', 'GQ'), ( 'foreign', 'GR'), ( 'foreign', 'GS'), ( 'foreign', 'GT'),
850
( 'foreign', 'GU'), ( 'foreign', 'GW'), ( 'foreign', 'GY'), ( 'foreign', 'HK'),
851
( 'foreign', 'HM'), ( 'foreign', 'HN'), ( 'foreign', 'HR'), ( 'foreign', 'HT'),
852
( 'foreign', 'HU'), ( 'foreign', 'ID'), ( 'foreign', 'IE'), ( 'foreign', 'IL'),
853
( 'foreign', 'IN'), ( 'foreign', 'IO'), ( 'foreign', 'IQ'), ( 'foreign', 'IR'),
854
( 'foreign', 'IS'), ( 'foreign', 'IT'), ( 'foreign', 'JM'), ( 'foreign', 'JO'),
855
( 'foreign', 'JP'), ( 'foreign', 'KE'), ( 'foreign', 'KG'), ( 'foreign', 'KH'),
856
( 'foreign', 'KI'), ( 'foreign', 'KM'), ( 'foreign', 'KN'), ( 'foreign', 'KP'),
857
( 'foreign', 'KR'), ( 'foreign', 'KW'), ( 'foreign', 'KY'), ( 'foreign', 'KZ'),
858
( 'foreign', 'LA'), ( 'foreign', 'LB'), ( 'foreign', 'LC'), ( 'foreign', 'LK'),
859
( 'foreign', 'LR'), ( 'foreign', 'LS'), ( 'foreign', 'LT'), ( 'foreign', 'LU'),
860
( 'foreign', 'LV'), ( 'foreign', 'LY'), ( 'foreign', 'MA'), ( 'foreign', 'MC'),
861
( 'foreign', 'MD'), ( 'foreign', 'ME'), ( 'foreign', 'MG'), ( 'foreign', 'MH'),
862
( 'foreign', 'MK'), ( 'foreign', 'ML'), ( 'foreign', 'MM'), ( 'foreign', 'MN'),
863
( 'foreign', 'MO'), ( 'foreign', 'MP'), ( 'foreign', 'MQ'), ( 'foreign', 'MR'),
864
( 'foreign', 'MS'), ( 'foreign', 'MT'), ( 'foreign', 'MU'), ( 'foreign', 'MV'),
865
( 'foreign', 'MW'), ( 'foreign', 'MX'), ( 'foreign', 'MY'), ( 'foreign', 'MZ'),
866
( 'foreign', 'NA'), ( 'foreign', 'NC'), ( 'foreign', 'NE'), ( 'foreign', 'NF'),
867
( 'foreign', 'NG'), ( 'foreign', 'NI'), ( 'foreign', 'NL'), ( 'foreign', 'NO'),
868
( 'foreign', 'NP'), ( 'foreign', 'NR'), ( 'foreign', 'NU'), ( 'foreign', 'NV'),
869
( 'foreign', 'NZ'), ( 'foreign', 'OM'), ( 'foreign', 'PA'), ( 'foreign', 'PE'),
870
( 'foreign', 'PF'), ( 'foreign', 'PG'), ( 'foreign', 'PH'), ( 'foreign', 'PK'),
871
( 'foreign', 'PL'), ( 'foreign', 'PM'), ( 'foreign', 'PN'), ( 'foreign', 'PR'),
872
( 'foreign', 'PS'), ( 'foreign', 'PT'), ( 'foreign', 'PW'), ( 'foreign', 'PY'),
873
( 'foreign', 'QA'), ( 'foreign', 'RE'), ( 'foreign', 'RO'), ( 'foreign', 'RU'),
874
( 'foreign', 'RW'), ( 'foreign', 'SA'), ( 'foreign', 'SB'), ( 'foreign', 'SC'),
875
( 'foreign', 'SD'), ( 'foreign', 'SE'), ( 'foreign', 'SG'), ( 'foreign', 'SH'),
876
( 'foreign', 'SI'), ( 'foreign', 'SJ'), ( 'foreign', 'SK'), ( 'foreign', 'SL'),
877
( 'foreign', 'SM'), ( 'foreign', 'SN'), ( 'foreign', 'SO'), ( 'foreign', 'SR'),
878
( 'foreign', 'ST'), ( 'foreign', 'SV'), ( 'foreign', 'SY'), ( 'foreign', 'SZ'),
879
( 'foreign', 'TA'), ( 'foreign', 'TC'), ( 'foreign', 'TD'), ( 'foreign', 'TF'),
880
( 'foreign', 'TG'), ( 'foreign', 'TH'), ( 'foreign', 'TJ'), ( 'foreign', 'TK'),
881
( 'foreign', 'TM'), ( 'foreign', 'TN'), ( 'foreign', 'TO'), ( 'foreign', 'TP'),
882
( 'foreign', 'TR'), ( 'foreign', 'TT'), ( 'foreign', 'TV'), ( 'foreign', 'TW'),
883
( 'foreign', 'TZ'), ( 'foreign', 'UA'), ( 'foreign', 'UG'), ( 'foreign', 'UM'),
884
( 'foreign', 'US'), ( 'foreign', 'UY'), ( 'foreign', 'UZ'), ( 'foreign', 'VA'),
885
( 'foreign', 'VC'), ( 'foreign', 'VE'), ( 'foreign', 'VG'), ( 'foreign', 'VI'),
886
( 'foreign', 'VN'), ( 'foreign', 'VU'), ( 'foreign', 'WF'), ( 'foreign', 'WS'),
887
( 'plfcz1', 'DK'), ( 'foreign', 'XE'), ( 'foreign', 'XS'), ( 'foreign', 'XU'),
888
( 'plfcz1', 'BE'), ( 'foreign', 'YE'), ( 'foreign', 'YT'), ( 'foreign', 'YU'),
889
( 'foreign', 'ZA'), ( 'foreign', 'ZM'), ( 'foreign', 'ZR'), ( 'foreign', 'ZW'),
890
( 'plfcz1', 'DE'), ( 'plfcz1', 'GI'), ( 'plfcz1', 'GR'), ( 'plfcz1', 'IS'),
891
( 'plfcz1', 'EI'), ( 'plfcz1', 'IT'), ( 'plfcz1', 'LU'), ( 'plfcz1', 'NL'),
892
( 'plfcz1', 'NO'), ( 'plfcz1', 'ES'), ( 'plfcz1', 'SE'), ( 'plfcz1', 'AL'),
893
( 'plfcz1', 'AD'), ( 'plfcz1', 'BY'), ( 'plfcz1', 'BA'), ( 'plfcz1', 'BG'),
894
( 'plfcz1', 'EE'), ( 'plfcz1', 'FO'), ( 'plfcz1', 'GL'), ( 'plfcz1', 'GB'),
895
( 'plfcz1', 'HR'), ( 'plfcz1', 'LV'), ( 'plfcz1', 'LT'), ( 'plfcz1', 'MT'),
896
( 'plfcz1', 'MK'), ( 'plfcz1', 'MD'), ( 'plfcz1', 'MC'), ( 'plfcz1', 'AT'),
897
( 'plfcz1', 'PL'), ( 'plfcz1', 'PT'), ( 'plfcz1', 'RO'), ( 'plfcz1', 'RU'),
898
( 'plfcz1', 'SM'), ( 'plfcz1', 'XS'), ( 'plfcz1', 'SK'), ( 'plfcz1', 'SI'),
899
( 'plfcz1', 'CZ'), ( 'plfcz1', 'TR'), ( 'plfcz1', 'UA'), ( 'plfcz1', 'HU'),
900
( 'plfcz1', 'VA'), ( 'plfcz1', 'CY'), ( 'plfcz2', 'AF'), ( 'plfcz2', 'DZ'),
901
( 'plfcz2', 'AS'), ( 'plfcz2', 'AO'), ( 'plfcz2', 'AI'), ( 'plfcz2', 'AQ'),
902
( 'plfcz2', 'AG'), ( 'plfcz2', 'AR'), ( 'plfcz2', 'AM'), ( 'plfcz2', 'AW'),
903
( 'plfcz2', 'AU'), ( 'plfcz2', 'AZ'), ( 'plfcz2', 'AP'), ( 'plfcz2', 'BS'),
904
( 'plfcz2', 'BH'), ( 'plfcz2', 'BD'), ( 'plfcz2', 'BB'), ( 'plfcz2', 'BZ'),
905
( 'plfcz2', 'BJ'), ( 'plfcz2', 'BM'), ( 'plfcz2', 'BT'), ( 'plfcz2', 'BO'),
906
( 'plfcz2', 'BW'), ( 'plfcz2', 'BV'), ( 'plfcz2', 'BR'), ( 'plfcz2', 'IO'),
907
( 'plfcz2', 'VG'), ( 'plfcz2', 'BN'), ( 'plfcz2', 'BF'), ( 'plfcz2', 'BI'),
908
( 'plfcz2', 'KH'), ( 'plfcz2', 'CM'), ( 'plfcz2', 'CA'), ( 'plfcz2', 'CV'),
909
( 'plfcz2', 'KY'), ( 'plfcz2', 'CF'), ( 'plfcz2', 'TD'), ( 'plfcz2', 'CL'),
910
( 'plfcz2', 'CN'), ( 'plfcz2', 'CX'), ( 'plfcz2', 'CC'), ( 'plfcz2', 'CO'),
911
( 'plfcz2', 'KM'), ( 'plfcz2', 'CG'), ( 'plfcz2', 'CD'), ( 'plfcz2', 'CK'),
912
( 'plfcz2', 'CR'), ( 'plfcz2', 'CI'), ( 'plfcz2', 'CU'), ( 'plfcz2', 'DJ'),
913
( 'plfcz2', 'DM'), ( 'plfcz2', 'DO'), ( 'plfcz2', 'TP'), ( 'plfcz2', 'EC'),
914
( 'plfcz2', 'EG'), ( 'plfcz2', 'SV'), ( 'plfcz2', 'GQ'), ( 'plfcz2', 'ER'),
915
( 'plfcz2', 'ET'), ( 'plfcz2', 'FK'), ( 'plfcz2', 'FJ'), ( 'plfcz2', 'FX'),
916
( 'plfcz2', 'GF'), ( 'plfcz2', 'PF'), ( 'plfcz2', 'TA'), ( 'plfcz2', 'TF'),
917
( 'plfcz2', 'GA'), ( 'plfcz2', 'GM'), ( 'plfcz2', 'GE'), ( 'plfcz2', 'GH'),
918
( 'plfcz2', 'GD'), ( 'plfcz2', 'GP'), ( 'plfcz2', 'GU'), ( 'plfcz2', 'GT'),
919
( 'plfcz2', 'GN'), ( 'plfcz2', 'GW'), ( 'plfcz2', 'GY'), ( 'plfcz2', 'HT'),
920
( 'plfcz2', 'HM'), ( 'plfcz2', 'HN'), ( 'plfcz2', 'HK'), ( 'plfcz2', 'IN'),
921
( 'plfcz2', 'ID'), ( 'plfcz2', 'IR'), ( 'plfcz2', 'IQ'), ( 'plfcz2', 'IE'),
922
( 'plfcz2', 'IL'), ( 'plfcz2', 'JM'), ( 'plfcz2', 'JP'), ( 'plfcz2', 'JO'),
923
( 'plfcz2', 'KZ'), ( 'plfcz2', 'KE'), ( 'plfcz2', 'KI'), ( 'plfcz2', 'KP'),
924
( 'plfcz2', 'KW'), ( 'plfcz2', 'KG'), ( 'plfcz2', 'LA'), ( 'plfcz2', 'LB'),
925
( 'plfcz2', 'LS'), ( 'plfcz2', 'LR'), ( 'plfcz2', 'LY'), ( 'plfcz2', 'MO'),
926
( 'plfcz2', 'MG'), ( 'plfcz2', 'ME'), ( 'plfcz2', 'MW'), ( 'plfcz2', 'MY'),
927
( 'plfcz2', 'MV'), ( 'plfcz2', 'ML'), ( 'plfcz2', 'MH'), ( 'plfcz2', 'MQ'),
928
( 'plfcz2', 'MR'), ( 'plfcz2', 'MU'), ( 'plfcz2', 'YT'), ( 'plfcz2', 'MX'),
929
( 'plfcz2', 'FM'), ( 'plfcz2', 'MN'), ( 'plfcz2', 'MS'), ( 'plfcz2', 'MA'),
930
( 'plfcz2', 'MZ'), ( 'plfcz2', 'MM'), ( 'plfcz2', 'NA'), ( 'plfcz2', 'NR'),
931
( 'plfcz2', 'NP'), ( 'plfcz2', 'AN'), ( 'plfcz2', 'NC'), ( 'plfcz2', 'NZ'),
932
( 'plfcz2', 'NI'), ( 'plfcz2', 'NE'), ( 'plfcz2', 'NG'), ( 'plfcz2', 'NU'),
933
( 'plfcz2', 'NF'), ( 'plfcz2', 'MP'), ( 'plfcz2', 'OM'), ( 'plfcz2', 'PK'),
934
( 'plfcz2', 'PW'), ( 'plfcz2', 'PS'), ( 'plfcz2', 'PA'), ( 'plfcz2', 'PG'),
935
( 'plfcz2', 'PY'), ( 'plfcz2', 'PE'), ( 'plfcz2', 'PH'), ( 'plfcz2', 'PN'),
936
( 'plfcz2', 'PR'), ( 'plfcz2', 'QA'), ( 'plfcz2', 'RE'), ( 'plfcz2', 'RW'),
937
( 'plfcz2', 'KN'), ( 'plfcz2', 'ST'), ( 'plfcz2', 'SA'), ( 'plfcz2', 'SN'),
938
( 'plfcz2', 'SC'), ( 'plfcz2', 'SL'), ( 'plfcz2', 'SG'), ( 'plfcz2', 'SB'),
939
( 'plfcz2', 'SO'), ( 'plfcz2', 'ZA'), ( 'plfcz2', 'GS'), ( 'plfcz2', 'KR'),
940
( 'plfcz2', 'LK'), ( 'plfcz2', 'NV'), ( 'plfcz2', 'SH'), ( 'plfcz2', 'LC'),
941
( 'plfcz2', 'PM'), ( 'plfcz2', 'VC'), ( 'plfcz2', 'SD'), ( 'plfcz2', 'SR'),
942
( 'plfcz2', 'SJ'), ( 'plfcz2', 'SZ'), ( 'plfcz2', 'SY'), ( 'plfcz2', 'TW'),
943
( 'plfcz2', 'TJ'), ( 'plfcz2', 'TZ'), ( 'plfcz2', 'TH'), ( 'plfcz2', 'TG'),
944
( 'plfcz2', 'TK'), ( 'plfcz2', 'TO'), ( 'plfcz2', 'TT'), ( 'plfcz2', 'XU'),
945
( 'plfcz2', 'TN'), ( 'plfcz2', 'TM'), ( 'plfcz2', 'TC'), ( 'plfcz2', 'TV'),
946
( 'plfcz2', 'UG'), ( 'plfcz2', 'AE'), ( 'plfcz2', 'US'), ( 'plfcz2', 'UM'),
947
( 'plfcz2', 'UY'), ( 'plfcz2', 'UZ'), ( 'plfcz2', 'VU'), ( 'plfcz2', 'VE'),
948
( 'plfcz2', 'VN'), ( 'plfcz2', 'VI'), ( 'plfcz2', 'WF'), ( 'plfcz2', 'EH'),
949
( 'plfcz2', 'WS'), ( 'plfcz2', 'YE'), ( 'plfcz2', 'YU'), ( 'plfcz2', 'ZR'),
950
( 'plfcz2', 'ZM'), ( 'plfcz2', 'ZW'), ( 'ppfcz1', 'AT'), ( 'ppfcz1', 'BE'),
951
( 'ppfcz1', 'DE'), ( 'ppfcz1', 'FR'), ( 'ppfcz1', 'FX'), ( 'ppfcz1', 'IT'),
952
( 'ppfcz1', 'LU'), ( 'ppfcz1', 'MC'), ( 'ppfcz1', 'NL'), ( 'ppfcz1', 'SM'),
953
( 'ppfcz1', 'VA'), ( 'ppfcz1', 'XE'), ( 'ppfcz2', 'AD'), ( 'ppfcz2', 'AL'),
954
( 'ppfcz2', 'BA'), ( 'ppfcz2', 'BG'), ( 'ppfcz2', 'BY'), ( 'ppfcz2', 'CY'),
955
( 'ppfcz2', 'CZ'), ( 'ppfcz2', 'DK'), ( 'ppfcz2', 'EE'), ( 'ppfcz2', 'EI'),
956
( 'ppfcz2', 'ES'), ( 'ppfcz2', 'FI'), ( 'ppfcz2', 'FO'), ( 'ppfcz2', 'GB'),
957
( 'ppfcz2', 'GI'), ( 'ppfcz2', 'GL'), ( 'ppfcz2', 'GR'), ( 'ppfcz2', 'HR'),
958
( 'ppfcz2', 'HU'), ( 'ppfcz2', 'IE'), ( 'ppfcz2', 'IS'), ( 'ppfcz2', 'LT'),
959
( 'ppfcz2', 'LV'), ( 'ppfcz2', 'MD'), ( 'ppfcz2', 'MK'), ( 'ppfcz2', 'MT'),
960
( 'ppfcz2', 'NO'), ( 'ppfcz2', 'PL'), ( 'ppfcz2', 'PT'), ( 'ppfcz2', 'RO'),
961
( 'ppfcz2', 'RU'), ( 'ppfcz2', 'SE'), ( 'ppfcz2', 'SI'), ( 'ppfcz2', 'SK'),
962
( 'ppfcz2', 'TR'), ( 'ppfcz2', 'UA'), ( 'ppfcz2', 'XS'), ( 'ppfcz2', 'YU'),
963
( 'ppfcz3', 'CA'), ( 'ppfcz3', 'DZ'), ( 'ppfcz3', 'EG'), ( 'ppfcz3', 'IL'),
964
( 'ppfcz3', 'JO'), ( 'ppfcz3', 'LB'), ( 'ppfcz3', 'LY'), ( 'ppfcz3', 'MA'),
965
( 'ppfcz3', 'MX'), ( 'ppfcz3', 'PM'), ( 'ppfcz3', 'SY'), ( 'ppfcz3', 'TN'),
966
( 'ppfcz3', 'US'), ( 'ppfcz4', 'AE'), ( 'ppfcz4', 'AF'), ( 'ppfcz4', 'AM'),
967
( 'ppfcz4', 'AO'), ( 'ppfcz4', 'AZ'), ( 'ppfcz4', 'BD'), ( 'ppfcz4', 'BF'),
968
( 'ppfcz4', 'BH'), ( 'ppfcz4', 'BI'), ( 'ppfcz4', 'BJ'), ( 'ppfcz4', 'BT'),
969
( 'ppfcz4', 'BV'), ( 'ppfcz4', 'BW'), ( 'ppfcz4', 'CF'), ( 'ppfcz4', 'CG'),
970
( 'ppfcz4', 'CI'), ( 'ppfcz4', 'CM'), ( 'ppfcz4', 'CN'), ( 'ppfcz4', 'DJ'),
971
( 'ppfcz4', 'DO'), ( 'ppfcz4', 'ER'), ( 'ppfcz4', 'ET'), ( 'ppfcz4', 'GA'),
972
( 'ppfcz4', 'GE'), ( 'ppfcz4', 'GH'), ( 'ppfcz4', 'GM'), ( 'ppfcz4', 'GN'),
973
( 'ppfcz4', 'GQ'), ( 'ppfcz4', 'GW'), ( 'ppfcz4', 'HK'), ( 'ppfcz4', 'IN'),
974
( 'ppfcz4', 'IQ'), ( 'ppfcz4', 'IR'), ( 'ppfcz4', 'JP'), ( 'ppfcz4', 'KE'),
975
( 'ppfcz4', 'KG'), ( 'ppfcz4', 'KH'), ( 'ppfcz4', 'KP'), ( 'ppfcz4', 'KW'),
976
( 'ppfcz4', 'KZ'), ( 'ppfcz4', 'LA'), ( 'ppfcz4', 'LK'), ( 'ppfcz4', 'LR'),
977
( 'ppfcz4', 'LS'), ( 'ppfcz4', 'MG'), ( 'ppfcz4', 'ML'), ( 'ppfcz4', 'MM'),
978
( 'ppfcz4', 'MN'), ( 'ppfcz4', 'MO'), ( 'ppfcz4', 'MR'), ( 'ppfcz4', 'MU'),
979
( 'ppfcz4', 'MV'), ( 'ppfcz4', 'MW'), ( 'ppfcz4', 'MY'), ( 'ppfcz4', 'MZ'),
980
( 'ppfcz4', 'NA'), ( 'ppfcz4', 'NE'), ( 'ppfcz4', 'NG'), ( 'ppfcz4', 'NP'),
981
( 'ppfcz4', 'OM'), ( 'ppfcz4', 'PK'), ( 'ppfcz4', 'QA'), ( 'ppfcz4', 'RE'),
982
( 'ppfcz4', 'RW'), ( 'ppfcz4', 'SA'), ( 'ppfcz4', 'SC'), ( 'ppfcz4', 'SD'),
983
( 'ppfcz4', 'SG'), ( 'ppfcz4', 'SH'), ( 'ppfcz4', 'SL'), ( 'ppfcz4', 'SN'),
984
( 'ppfcz4', 'SO'), ( 'ppfcz4', 'SZ'), ( 'ppfcz4', 'TD'), ( 'ppfcz4', 'TG'),
985
( 'ppfcz4', 'TH'), ( 'ppfcz4', 'TJ'), ( 'ppfcz4', 'TM'), ( 'ppfcz4', 'TW'),
986
( 'ppfcz4', 'TZ'), ( 'ppfcz4', 'UG'), ( 'ppfcz4', 'UZ'), ( 'ppfcz4', 'VN'),
987
( 'ppfcz4', 'XU'), ( 'ppfcz4', 'YT'), ( 'ppfcz4', 'ZA'), ( 'ppfcz4', 'ZW'),
988
( 'ppfcz5', 'AG'), ( 'ppfcz5', 'AI'), ( 'ppfcz5', 'AN'), ( 'ppfcz5', 'AP'),
989
( 'ppfcz5', 'AQ'), ( 'ppfcz5', 'AR'), ( 'ppfcz5', 'AS'), ( 'ppfcz5', 'AU'),
990
( 'ppfcz5', 'AW'), ( 'ppfcz5', 'BB'), ( 'ppfcz5', 'BM'), ( 'ppfcz5', 'BN'),
991
( 'ppfcz5', 'BO'), ( 'ppfcz5', 'BR'), ( 'ppfcz5', 'BS'), ( 'ppfcz5', 'BZ'),
992
( 'ppfcz5', 'CC'), ( 'ppfcz5', 'CD'), ( 'ppfcz5', 'CK'), ( 'ppfcz5', 'CL'),
993
( 'ppfcz5', 'CO'), ( 'ppfcz5', 'CR'), ( 'ppfcz5', 'CU'), ( 'ppfcz5', 'CV'),
994
( 'ppfcz5', 'CX'), ( 'ppfcz5', 'DM'), ( 'ppfcz5', 'EC'), ( 'ppfcz5', 'EH'),
995
( 'ppfcz5', 'FJ'), ( 'ppfcz5', 'FK'), ( 'ppfcz5', 'FM'), ( 'ppfcz5', 'GD'),
996
( 'ppfcz5', 'GF'), ( 'ppfcz5', 'GP'), ( 'ppfcz5', 'GS'), ( 'ppfcz5', 'GT'),
997
( 'ppfcz5', 'GU'), ( 'ppfcz5', 'GY'), ( 'ppfcz5', 'HM'), ( 'ppfcz5', 'HN'),
998
( 'ppfcz5', 'HT'), ( 'ppfcz5', 'ID'), ( 'ppfcz5', 'IO'), ( 'ppfcz5', 'JM'),
999
( 'ppfcz5', 'KI'), ( 'ppfcz5', 'KM'), ( 'ppfcz5', 'KN'), ( 'ppfcz5', 'KR'),
1000
( 'ppfcz5', 'KY'), ( 'ppfcz5', 'LC'), ( 'ppfcz5', 'ME'), ( 'ppfcz5', 'MH'),
1001
( 'ppfcz5', 'MP'), ( 'ppfcz5', 'MQ'), ( 'ppfcz5', 'MS'), ( 'ppfcz5', 'NC'),
1002
( 'ppfcz5', 'NF'), ( 'ppfcz5', 'NI'), ( 'ppfcz5', 'NR'), ( 'ppfcz5', 'NU'),
1003
( 'ppfcz5', 'NZ'), ( 'ppfcz5', 'PA'), ( 'ppfcz5', 'PE'), ( 'ppfcz5', 'PF'),
1004
( 'ppfcz5', 'PG'), ( 'ppfcz5', 'PH'), ( 'ppfcz5', 'PN'), ( 'ppfcz5', 'PR'),
1005
( 'ppfcz5', 'PS'), ( 'ppfcz5', 'PW'), ( 'ppfcz5', 'PY'), ( 'ppfcz5', 'SB'),
1006
( 'ppfcz5', 'SJ'), ( 'ppfcz5', 'SR'), ( 'ppfcz5', 'ST'), ( 'ppfcz5', 'SV'),
1007
( 'ppfcz5', 'TA'), ( 'ppfcz5', 'TC'), ( 'ppfcz5', 'TF'), ( 'ppfcz5', 'TK'),
1008
( 'ppfcz5', 'TO'), ( 'ppfcz5', 'TP'), ( 'ppfcz5', 'TT'), ( 'ppfcz5', 'TV'),
1009
( 'ppfcz5', 'UM'), ( 'ppfcz5', 'UY'), ( 'ppfcz5', 'VC'), ( 'ppfcz5', 'VE'),
1010
( 'ppfcz5', 'VG'), ( 'ppfcz5', 'VI'), ( 'ppfcz5', 'VU'), ( 'ppfcz5', 'WF'),
1011
( 'ppfcz5', 'WS'), ( 'ppfcz5', 'YE'), ( 'ppfcz5', 'ZM'), ( 'ppfcz5', 'ZR');
1013
INSERT INTO t2 (a, b, c, d) VALUES
1014
('domestic', 26, 0.25, 4.7), ('domestic', 27, 0.25, 6),
1015
('domestic', 19, 2, 6.3), ('domestic', 19, 5, 7.77),
1016
('domestic', 19, 10, 10.3), ('domestic', 19, 20, 14.83),
1017
('domestic', 19, 30, 20.88), ('domestic', 20, 2, 7.3),
1018
('domestic', 20, 5, 8.77), ('domestic', 20, 10, 11.3),
1019
('domestic', 20, 20, 15.83), ('domestic', 20, 30, 21.88),
1020
('domestic', 23, 2, 18.8), ('domestic', 23, 5, 20.8),
1021
('domestic', 23, 10, 24.8), ('domestic', 23, 20, 27.8),
1022
('domestic', 23, 30, 30.8), ('domestic', 24, 2, 21.1405),
1023
('domestic', 24, 5, 22.3705), ('domestic', 24, 10, 25.0905),
1024
('domestic', 24, 20, 29.7705), ('domestic', 24, 30, 35.9605),
1025
('domestic', 17, 2, 7.2), ('domestic', 17, 5, 8.43),
1026
('domestic', 17, 10, 11.15), ('domestic', 17, 20, 15.83),
1027
('domestic', 17, 30, 22.02), ('domestic', 18, 2, 8.2),
1028
('domestic', 18, 5, 9.43), ('domestic', 18, 10, 12.15),
1029
('domestic', 18, 20, 16.83), ('domestic', 18, 30, 23.02),
1030
('domestic', 28, 2, 17), ('domestic', 28, 5, 19),
1031
('domestic', 28, 10, 22), ('domestic', 28, 20, 28),
1032
('domestic', 28, 30, 35), ('domestic', 29, 30, 29.5),
1033
('foreign', 25, 200, 0), ('domestic', 3, 100, 59),
1034
('foreign', 10, 30, 0), ('foreign', 22, 0, 0),
1035
('foreign', 11, 30, 0), ('foreign', 12, 30, 0),
1036
('all', 1, 10000, 0), ('all', 2, 10000, 0),
1037
('domestic', 9, 10000, 0), ('domestic', 4, 500, 0),
1038
('domestic', 5, 500, 0), ('domestic', 6, 500, 0),
1039
('domestic', 7, 500, 0), ('domestic', 8, 500, 0),
1040
('domestic', 21, 3.9, 10.8), ('domestic', 21, 4.9, 12.2),
1041
('domestic', 21, 9.9, 15.3), ('domestic', 21, 19.9, 20.6),
1042
('domestic', 21, 30, 28.1), ('plfcz1', 16, 0.5, 19),
1043
('plfcz2', 16, 0.5, 25), ( 'ppfcz2', 15, 16, 76.5),
1044
( 'ppfcz2', 15, 15, 75.5), ( 'ppfcz2', 15, 14, 73.5),
1045
( 'ppfcz2', 15, 13, 71.5), ( 'ppfcz2', 15, 12, 69.5),
1046
( 'ppfcz2', 15, 11, 67.5), ( 'ppfcz2', 15, 10, 65.5),
1047
( 'ppfcz2', 15, 9, 62.5), ( 'ppfcz2', 15, 8, 59.5),
1048
( 'ppfcz2', 15, 7, 56.5), ( 'ppfcz2', 15, 6, 53.5),
1049
( 'ppfcz2', 15, 5, 50.5), ( 'ppfcz2', 15, 4, 46.5),
1050
( 'ppfcz2', 15, 3, 42.5), ( 'ppfcz2', 15, 2, 38.5),
1051
('ppfcz1', 15, 2, 33.5), ('ppfcz1', 15, 3, 36.5),
1052
('ppfcz1', 15, 4, 39.5), ('ppfcz1', 15, 5, 41.5),
1053
('ppfcz1', 15, 6, 42.5), ('ppfcz1', 15, 7, 43.5),
1054
('ppfcz1', 15, 8, 44.5), ('ppfcz1', 15, 9, 45.5),
1055
('ppfcz1', 15, 10, 46.5), ('ppfcz1', 15, 11, 47.5),
1056
( 'ppfcz1', 15, 12, 48.5), ( 'ppfcz1', 15, 13, 49.5), ( 'ppfcz1', 15, 14, 50.5),
1057
( 'ppfcz1', 15, 15, 51.5), ( 'ppfcz1', 15, 16, 52.5), ( 'ppfcz1', 15, 17, 53.5),
1058
( 'ppfcz1', 15, 18, 54.5), ( 'ppfcz1', 15, 19, 55.5), ( 'ppfcz1', 15, 20, 56.5),
1059
( 'ppfcz1', 15, 21, 57.5), ( 'ppfcz1', 15, 22, 58.5), ( 'ppfcz1', 15, 23, 59.5),
1060
( 'ppfcz1', 15, 24, 60.5), ( 'ppfcz1', 15, 25, 61.5), ( 'ppfcz1', 15, 26, 62.5),
1061
( 'ppfcz1', 15, 27, 63.5), ( 'ppfcz1', 15, 28, 64.5), ( 'ppfcz1', 15, 29, 65.5),
1062
( 'ppfcz1', 15, 30, 66.5), ( 'ppfcz2', 15, 17, 77.5), ( 'ppfcz2', 15, 18, 78.5),
1063
( 'ppfcz2', 15, 19, 79.5), ( 'ppfcz2', 15, 20, 80.5), ( 'ppfcz2', 15, 21, 81.5),
1064
( 'ppfcz2', 15, 22, 82.5), ( 'ppfcz2', 15, 23, 83.5), ( 'ppfcz2', 15, 24, 84.5),
1065
( 'ppfcz2', 15, 25, 85.5), ( 'ppfcz2', 15, 26, 86.5), ( 'ppfcz2', 15, 27, 87.5),
1066
( 'ppfcz2', 15, 28, 88.5), ( 'ppfcz2', 15, 29, 89.5), ( 'ppfcz2', 15, 30, 90.5),
1067
( 'ppfcz3', 15, 2, 39.5), ( 'ppfcz3', 15, 3, 45.5), ( 'ppfcz3', 15, 4, 51.5),
1068
( 'ppfcz3', 15, 5, 57.5), ( 'ppfcz3', 15, 6, 63.5), ( 'ppfcz3', 15, 7, 69.5),
1069
( 'ppfcz3', 15, 8, 75.5), ( 'ppfcz3', 15, 9, 81.5), ( 'ppfcz3', 15, 10, 87.5),
1070
( 'ppfcz3', 15, 11, 93.5), ( 'ppfcz3', 15, 12, 99.5), ( 'ppfcz3', 15, 13, 105.5),
1071
( 'ppfcz3', 15, 14, 111.5), ( 'ppfcz3', 15, 15, 117.5), ( 'ppfcz3', 15, 16, 122.5),
1072
( 'ppfcz3', 15, 17, 127.5), ( 'ppfcz3', 15, 18, 132.5), ( 'ppfcz3', 15, 19, 137.5),
1073
( 'ppfcz3', 15, 20, 142.5), ( 'ppfcz3', 15, 21, 146.5), ( 'ppfcz3', 15, 22, 150.5),
1074
( 'ppfcz3', 15, 23, 154.5), ( 'ppfcz3', 15, 24, 158.5), ( 'ppfcz3', 15, 25, 162.5),
1075
( 'ppfcz3', 15, 26, 166.5), ( 'ppfcz3', 15, 27, 170.5), ( 'ppfcz3', 15, 28, 174.5),
1076
( 'ppfcz3', 15, 29, 178.5), ( 'ppfcz3', 15, 30, 182.5), ( 'ppfcz4', 15, 2, 44.5),
1077
( 'ppfcz4', 15, 3, 51.5), ( 'ppfcz4', 15, 4, 58.5), ( 'ppfcz4', 15, 5, 65.5),
1078
( 'ppfcz4', 15, 6, 72.5), ( 'ppfcz4', 15, 7, 79.5), ( 'ppfcz4', 15, 8, 86.5),
1079
( 'ppfcz4', 15, 9, 93.5), ( 'ppfcz4', 15, 10, 100.5), ( 'ppfcz4', 15, 11, 105.5),
1080
( 'ppfcz4', 15, 12, 110.5), ( 'ppfcz4', 15, 13, 115.5), ( 'ppfcz4', 15, 14, 120.5),
1081
( 'ppfcz4', 15, 15, 125.5), ( 'ppfcz4', 15, 16, 130.5), ( 'ppfcz4', 15, 17, 135.5),
1082
( 'ppfcz4', 15, 18, 140.5), ( 'ppfcz4', 15, 19, 145.5), ( 'ppfcz4', 15, 20, 150.5),
1083
( 'ppfcz4', 15, 21, 154.5), ( 'ppfcz4', 15, 22, 158.5), ( 'ppfcz4', 15, 23, 162.5),
1084
( 'ppfcz4', 15, 24, 166.5), ( 'ppfcz4', 15, 25, 170.5), ( 'ppfcz4', 15, 26, 174.5),
1085
( 'ppfcz4', 15, 27, 178.5), ( 'ppfcz4', 15, 28, 182.5), ( 'ppfcz4', 15, 29, 186.5),
1086
( 'ppfcz4', 15, 30, 190.5), ( 'ppfcz5', 15, 2, 48.5), ( 'ppfcz5', 15, 3, 56.5),
1087
( 'ppfcz5', 15, 4, 64.5), ( 'ppfcz5', 15, 5, 72.5), ( 'ppfcz5', 15, 6, 80.5),
1088
( 'ppfcz5', 15, 7, 88.5), ( 'ppfcz5', 15, 8, 96.5), ( 'ppfcz5', 15, 9, 104.5),
1089
( 'ppfcz5', 15, 10, 112.5), ( 'ppfcz5', 15, 11, 119.5), ( 'ppfcz5', 15, 12, 126.5),
1090
( 'ppfcz5', 15, 13, 133.5), ( 'ppfcz5', 15, 14, 140.5), ( 'ppfcz5', 15, 15, 147.5),
1091
( 'ppfcz5', 15, 16, 153.5), ( 'ppfcz5', 15, 17, 161.5), ( 'ppfcz5', 15, 18, 167.5),
1092
( 'ppfcz5', 15, 19, 173.5), ( 'ppfcz5', 15, 20, 179.5), ( 'ppfcz5', 15, 21, 185.5),
1093
( 'ppfcz5', 15, 22, 191.5), ( 'ppfcz5', 15, 23, 197.5), ( 'ppfcz5', 15, 24, 203.5),
1094
( 'ppfcz5', 15, 25, 207.5), ( 'ppfcz5', 15, 26, 212.5), ( 'ppfcz5', 15, 27, 217.5),
1095
( 'ppfcz5', 15, 28, 222.5), ( 'ppfcz5', 15, 29, 227.5), ( 'ppfcz5', 15, 30, 232.5),
1096
( 'ppfcz1', 14, 2, 37.5), ( 'ppfcz1', 14, 3, 41.5), ( 'ppfcz1', 14, 4, 45.5),
1097
( 'ppfcz1', 14, 5, 48.5), ( 'ppfcz1', 14, 6, 52.5), ( 'ppfcz1', 14, 7, 55.5),
1098
( 'ppfcz1', 14, 8, 57.5), ( 'ppfcz1', 14, 9, 59.5), ( 'ppfcz1', 14, 10, 61.5),
1099
( 'ppfcz1', 14, 11, 62.5), ( 'ppfcz1', 14, 12, 63.5), ( 'ppfcz1', 14, 13, 64.5),
1100
( 'ppfcz1', 14, 14, 65.5), ( 'ppfcz1', 14, 15, 66.5), ( 'ppfcz1', 14, 16, 67.5),
1101
( 'ppfcz1', 14, 17, 68.5), ( 'ppfcz1', 14, 18, 69.5), ( 'ppfcz1', 14, 19, 70.5),
1102
( 'ppfcz1', 14, 20, 71.5), ( 'ppfcz1', 14, 21, 72.5), ( 'ppfcz1', 14, 22, 73.5),
1103
( 'ppfcz1', 14, 23, 74.5), ( 'ppfcz1', 14, 24, 75.5), ( 'ppfcz1', 14, 25, 76.5),
1104
( 'ppfcz1', 14, 26, 77.5), ( 'ppfcz1', 14, 27, 78.5), ( 'ppfcz1', 14, 28, 79.5),
1105
( 'ppfcz1', 14, 29, 80.5), ( 'ppfcz1', 14, 30, 81.5), ( 'ppfcz2', 14, 2, 43.5),
1106
( 'ppfcz2', 14, 3, 48.5), ( 'ppfcz2', 14, 4, 53.5), ( 'ppfcz2', 14, 5, 57.5),
1107
( 'ppfcz2', 14, 6, 61.5), ( 'ppfcz2', 14, 7, 65.5), ( 'ppfcz2', 14, 8, 69.5),
1108
( 'ppfcz2', 14, 9, 73.5), ( 'ppfcz2', 14, 10, 77.5), ( 'ppfcz2', 14, 11, 80.5),
1109
( 'ppfcz2', 14, 12, 83.5), ( 'ppfcz2', 14, 13, 86.5), ( 'ppfcz2', 14, 14, 89.5),
1110
( 'ppfcz2', 14, 15, 92.5), ( 'ppfcz2', 14, 16, 94.5), ( 'ppfcz2', 14, 17, 96.5),
1111
( 'ppfcz2', 14, 18, 98.5), ( 'ppfcz2', 14, 19, 99.5), ( 'ppfcz2', 14, 20, 100.5),
1112
( 'ppfcz2', 14, 21, 101.5), ( 'ppfcz2', 14, 22, 102.5), ( 'ppfcz2', 14, 23, 103.5),
1113
( 'ppfcz2', 14, 24, 104.5), ( 'ppfcz2', 14, 25, 105.5), ( 'ppfcz2', 14, 26, 106.5),
1114
( 'ppfcz2', 14, 27, 107.5), ( 'ppfcz2', 14, 28, 108.5), ( 'ppfcz2', 14, 29, 109.5),
1115
( 'ppfcz2', 14, 30, 110.5), ( 'ppfcz3', 14, 2, 47.5), ( 'ppfcz3', 14, 3, 56.5),
1116
( 'ppfcz3', 14, 4, 67.5), ( 'ppfcz3', 14, 5, 78.5), ( 'ppfcz3', 14, 6, 87.5),
1117
( 'ppfcz3', 14, 7, 96.5), ( 'ppfcz3', 14, 8, 105.5), ( 'ppfcz3', 14, 9, 114.5),
1118
( 'ppfcz3', 14, 10, 123.5), ( 'ppfcz3', 14, 11, 131.5), ( 'ppfcz3', 14, 12, 139.5),
1119
( 'ppfcz3', 14, 13, 147.5), ( 'ppfcz3', 14, 14, 155.5), ( 'ppfcz3', 14, 15, 163.5),
1120
( 'ppfcz3', 14, 16, 171.5), ( 'ppfcz3', 14, 17, 179.5), ( 'ppfcz3', 14, 18, 187.5),
1121
( 'ppfcz3', 14, 19, 195.5), ( 'ppfcz3', 14, 20, 203.5), ( 'ppfcz3', 14, 21, 210.5),
1122
( 'ppfcz3', 14, 22, 217.5), ( 'ppfcz3', 14, 23, 224.5), ( 'ppfcz3', 14, 24, 231.5),
1123
( 'ppfcz3', 14, 25, 238.5), ( 'ppfcz3', 14, 26, 245.5), ( 'ppfcz3', 14, 27, 252.5),
1124
( 'ppfcz3', 14, 28, 259.5), ( 'ppfcz3', 14, 29, 266.5), ( 'ppfcz3', 14, 30, 273.5),
1125
( 'ppfcz4', 14, 2, 54.5), ( 'ppfcz4', 14, 3, 68.5), ( 'ppfcz4', 14, 4, 81.5),
1126
( 'ppfcz4', 14, 5, 95.5), ( 'ppfcz4', 14, 6, 108.5), ( 'ppfcz4', 14, 7, 121.5),
1127
( 'ppfcz4', 14, 8, 134.5), ( 'ppfcz4', 14, 9, 147.5), ( 'ppfcz4', 14, 10, 160.5),
1128
( 'ppfcz4', 14, 11, 168.5), ( 'ppfcz4', 14, 12, 178.5), ( 'ppfcz4', 14, 13, 188.5),
1129
( 'ppfcz4', 14, 14, 198.5), ( 'ppfcz4', 14, 15, 208.5), ( 'ppfcz4', 14, 16, 216.5),
1130
( 'ppfcz4', 14, 17, 224.5), ( 'ppfcz4', 14, 18, 232.5), ( 'ppfcz4', 14, 19, 240.5),
1131
( 'ppfcz4', 14, 20, 248.5), ( 'ppfcz4', 14, 21, 256.5), ( 'ppfcz4', 14, 22, 264.5),
1132
( 'ppfcz4', 14, 23, 272.5), ( 'ppfcz4', 14, 24, 280.5), ( 'ppfcz4', 14, 25, 288.5),
1133
( 'ppfcz4', 14, 26, 296.5), ( 'ppfcz4', 14, 27, 304.5), ( 'ppfcz4', 14, 28, 312.5),
1134
( 'ppfcz4', 14, 29, 320.5), ( 'ppfcz4', 14, 30, 328.5), ( 'ppfcz5', 14, 2, 66.5),
1135
( 'ppfcz5', 14, 3, 84.5), ( 'ppfcz5', 14, 4, 102.5), ( 'ppfcz5', 14, 5, 120.5),
1136
( 'ppfcz5', 14, 6, 137.5), ( 'ppfcz5', 14, 7, 154.5), ( 'ppfcz5', 14, 8, 171.5),
1137
( 'ppfcz5', 14, 9, 188.5), ( 'ppfcz5', 14, 10, 205.5), ( 'ppfcz5', 14, 11, 220.5),
1138
( 'ppfcz5', 14, 12, 235.5), ( 'ppfcz5', 14, 13, 250.5), ( 'ppfcz5', 14, 14, 265.5),
1139
( 'ppfcz5', 14, 15, 280.5), ( 'ppfcz5', 14, 16, 295.5), ( 'ppfcz5', 14, 17, 310.5),
1140
( 'ppfcz5', 14, 18, 325.5), ( 'ppfcz5', 14, 19, 340.5), ( 'ppfcz5', 14, 20, 355.5),
1141
( 'ppfcz5', 14, 21, 368.5), ( 'ppfcz5', 14, 22, 381.5), ( 'ppfcz5', 14, 23, 394.5),
1142
( 'ppfcz5', 14, 24, 407.5), ( 'ppfcz5', 14, 25, 420.5), ( 'ppfcz5', 14, 26, 433.5),
1143
( 'ppfcz5', 14, 27, 446.5), ( 'ppfcz5', 14, 28, 459.5), ( 'ppfcz5', 14, 29, 472.5),
1144
( 'ppfcz5', 14, 30, 485.5), ( 'ppfcz1', 30, 0.5, 56.5), ( 'ppfcz1', 30, 1, 63.5),
1145
( 'ppfcz1', 30, 1.5, 69.5), ( 'ppfcz1', 30, 2, 75.5), ( 'ppfcz1', 30, 2.5, 80.5),
1146
( 'ppfcz1', 30, 3, 86.5), ( 'ppfcz1', 30, 3.5, 92.5), ( 'ppfcz1', 30, 4, 99.5),
1147
( 'ppfcz1', 30, 4.5, 105.5), ( 'ppfcz1', 30, 5, 111.5), ( 'ppfcz1', 30, 6, 118.5),
1148
( 'ppfcz1', 30, 7, 126.5), ( 'ppfcz1', 30, 8, 133.5), ( 'ppfcz1', 30, 9, 141.5),
1149
( 'ppfcz1', 30, 10, 148.5), ( 'ppfcz1', 30, 11, 156.5), ( 'ppfcz1', 30, 12, 163.5),
1150
( 'ppfcz1', 30, 13, 171.5), ( 'ppfcz1', 30, 14, 178.5), ( 'ppfcz1', 30, 15, 186.5),
1151
( 'ppfcz1', 30, 16, 193.5), ( 'ppfcz1', 30, 17, 201.5), ( 'ppfcz1', 30, 18, 209.5),
1152
( 'ppfcz1', 30, 19, 216.5), ( 'ppfcz1', 30, 20, 224.5), ( 'ppfcz1', 30, 21, 231.5),
1153
( 'ppfcz1', 30, 22, 239.5), ( 'ppfcz1', 30, 23, 246.5), ( 'ppfcz1', 30, 24, 254.5),
1154
( 'ppfcz1', 30, 25, 261.5), ( 'ppfcz1', 30, 26, 269.5), ( 'ppfcz1', 30, 27, 276.5),
1155
( 'ppfcz1', 30, 28, 284.5), ( 'ppfcz1', 30, 29, 291.5), ( 'ppfcz1', 30, 30, 299.5),
1156
( 'ppfcz2', 30, 0.5, 61.5), ( 'ppfcz2', 30, 1, 65.5), ( 'ppfcz2', 30, 1.5, 75.5),
1157
( 'ppfcz2', 30, 2, 80.5), ( 'ppfcz2', 30, 2.5, 86.5), ( 'ppfcz2', 30, 3, 99.5),
1158
( 'ppfcz2', 30, 3.5, 109.5), ( 'ppfcz2', 30, 4, 113.5), ( 'ppfcz2', 30, 4.5, 121.5),
1159
( 'ppfcz2', 30, 5, 129.5), ( 'ppfcz2', 30, 6, 139.5), ( 'ppfcz2', 30, 7, 149.5),
1160
( 'ppfcz2', 30, 8, 159.5), ( 'ppfcz2', 30, 9, 169.5), ( 'ppfcz2', 30, 10, 180.5),
1161
( 'ppfcz2', 30, 11, 189.5), ( 'ppfcz2', 30, 12, 199.5), ( 'ppfcz2', 30, 13, 210.5),
1162
( 'ppfcz2', 30, 14, 219.5), ( 'ppfcz2', 30, 15, 229.5), ( 'ppfcz2', 30, 16, 240.5),
1163
( 'ppfcz2', 30, 17, 249.5), ( 'ppfcz2', 30, 18, 259.5), ( 'ppfcz2', 30, 19, 270.5),
1164
( 'ppfcz2', 30, 20, 280.5), ( 'ppfcz2', 30, 21, 289.5), ( 'ppfcz2', 30, 22, 300.5),
1165
( 'ppfcz2', 30, 23, 310.5), ( 'ppfcz2', 30, 24, 320.5), ( 'ppfcz2', 30, 25, 330.5),
1166
( 'ppfcz2', 30, 26, 340.5), ( 'ppfcz2', 30, 27, 350.5), ( 'ppfcz2', 30, 28, 360.5),
1167
( 'ppfcz2', 30, 29, 370.5), ( 'ppfcz2', 30, 30, 381.5), ( 'ppfcz3', 30, 0.5, 74.5),
1168
( 'ppfcz3', 30, 1, 83.5), ( 'ppfcz3', 30, 1.5, 90.5), ( 'ppfcz3', 30, 2, 99.5),
1169
( 'ppfcz3', 30, 2.5, 107.5), ( 'ppfcz3', 30, 3, 114.5), ( 'ppfcz3', 30, 3.5, 122.5),
1170
( 'ppfcz3', 30, 4, 130.5), ( 'ppfcz3', 30, 4.5, 140.5), ( 'ppfcz3', 30, 5, 147.5),
1171
( 'ppfcz3', 30, 6, 162.5), ( 'ppfcz3', 30, 7, 174.5), ( 'ppfcz3', 30, 8, 188.5),
1172
( 'ppfcz3', 30, 9, 201.5), ( 'ppfcz3', 30, 10, 213.5), ( 'ppfcz3', 30, 11, 227.5),
1173
( 'ppfcz3', 30, 12, 240.5), ( 'ppfcz3', 30, 13, 252.5), ( 'ppfcz3', 30, 14, 266.5),
1174
( 'ppfcz3', 30, 15, 278.5), ( 'ppfcz3', 30, 16, 290.5), ( 'ppfcz3', 30, 17, 304.5),
1175
( 'ppfcz3', 30, 18, 317.5), ( 'ppfcz3', 30, 19, 330.5), ( 'ppfcz3', 30, 20, 343.5),
1176
( 'ppfcz3', 30, 21, 354.5), ( 'ppfcz3', 30, 22, 363.5), ( 'ppfcz3', 30, 23, 375.5),
1177
( 'ppfcz3', 30, 24, 385.5), ( 'ppfcz3', 30, 25, 396.5), ( 'ppfcz3', 30, 26, 405.5),
1178
( 'ppfcz3', 30, 27, 417.5), ( 'ppfcz3', 30, 28, 428.5), ( 'ppfcz3', 30, 29, 438.5),
1179
( 'ppfcz3', 30, 30, 448.5), ( 'ppfcz4', 30, 0.5, 90.5), ( 'ppfcz4', 30, 1, 104.5),
1180
( 'ppfcz4', 30, 1.5, 118.5), ( 'ppfcz4', 30, 2, 134.5), ( 'ppfcz4', 30, 2.5, 146.5),
1181
( 'ppfcz4', 30, 3, 163.5), ( 'ppfcz4', 30, 3.5, 179.5), ( 'ppfcz4', 30, 4, 195.5),
1182
( 'ppfcz4', 30, 4.5, 211.5), ( 'ppfcz4', 30, 5, 232.5), ( 'ppfcz4', 30, 6, 257.5),
1183
( 'ppfcz4', 30, 7, 278.5), ( 'ppfcz4', 30, 8, 300.5), ( 'ppfcz4', 30, 9, 321.5),
1184
( 'ppfcz4', 30, 10, 343.5), ( 'ppfcz4', 30, 11, 364.5), ( 'ppfcz4', 30, 12, 386.5),
1185
( 'ppfcz4', 30, 13, 407.5), ( 'ppfcz4', 30, 14, 429.5), ( 'ppfcz4', 30, 15, 450.5),
1186
( 'ppfcz4', 30, 16, 472.5), ( 'ppfcz4', 30, 17, 493.5), ( 'ppfcz4', 30, 18, 515.5),
1187
( 'ppfcz4', 30, 19, 536.5), ( 'ppfcz4', 30, 20, 558.5), ( 'ppfcz4', 30, 21, 579.5),
1188
( 'ppfcz4', 30, 22, 601.5), ( 'ppfcz4', 30, 23, 622.5), ( 'ppfcz4', 30, 24, 644.5),
1189
( 'ppfcz4', 30, 25, 665.5), ( 'ppfcz4', 30, 26, 687.5), ( 'ppfcz4', 30, 27, 708.5),
1190
( 'ppfcz4', 30, 28, 730.5), ( 'ppfcz4', 30, 29, 751.5), ( 'ppfcz4', 30, 30, 773.5),
1191
( 'ppfcz5', 30, 0.5, 97.5), ( 'ppfcz5', 30, 1, 114.5), ( 'ppfcz5', 30, 1.5, 131.5),
1192
( 'ppfcz5', 30, 2, 148.5), ( 'ppfcz5', 30, 2.5, 165.5), ( 'ppfcz5', 30, 3, 183.5),
1193
( 'ppfcz5', 30, 3.5, 200.5), ( 'ppfcz5', 30, 4, 221.5), ( 'ppfcz5', 30, 4.5, 243.5),
1194
( 'ppfcz5', 30, 5, 264.5), ( 'ppfcz5', 30, 6, 289.5), ( 'ppfcz5', 30, 7, 313.5),
1195
( 'ppfcz5', 30, 8, 336.5), ( 'ppfcz5', 30, 9, 360.5), ( 'ppfcz5', 30, 10, 384.5),
1196
( 'ppfcz5', 30, 11, 407.5), ( 'ppfcz5', 30, 12, 431.5), ( 'ppfcz5', 30, 13, 455.5),
1197
( 'ppfcz5', 30, 14, 478.5), ( 'ppfcz5', 30, 15, 502.5), ( 'ppfcz5', 30, 16, 526.5),
1198
( 'ppfcz5', 30, 17, 549.5), ( 'ppfcz5', 30, 18, 573.5), ( 'ppfcz5', 30, 19, 597.5),
1199
( 'ppfcz5', 30, 20, 620.5), ( 'ppfcz5', 30, 21, 644.5), ( 'ppfcz5', 30, 22, 668.5),
1200
( 'ppfcz5', 30, 23, 691.5), ( 'ppfcz5', 30, 24, 715.5), ( 'ppfcz5', 30, 25, 738.5),
1201
( 'ppfcz5', 30, 26, 762.5), ( 'ppfcz5', 30, 27, 786.5), ( 'ppfcz5', 30, 28, 809.5),
1202
( 'ppfcz5', 30, 29, 833.5), ( 'ppfcz5', 30, 30, 857.5), ( 'foreign', 13, 30, 0),
1203
( 'all', 32, 10000, 23.2342007434944);
1207
INSERT INTO t3 SELECT * FROM t1;
1209
SELECT d FROM t1, t2
1210
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1211
ORDER BY t2.c LIMIT 1;
1213
SELECT d FROM t3 AS t1, t2 AS t2
1214
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1215
ORDER BY t2.c LIMIT 1;
1217
DROP TABLE t1,t2,t3;
added
removed
tests/t/order_by.test
