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
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 '',
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;
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;
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;
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);
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,
327
`date` datetime NOT NULL default '0000-00-00 00:00:00',
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));
382
eval insert into t1(t) values ('$1');
386
select id,t from t1 force index (primary) order by id;
390
# Test of test_if_subkey() function
393
FieldKey varchar(36) NOT NULL default '',
394
LongVal bigint default NULL,
396
KEY FieldKey (FieldKey),
397
KEY LongField (FieldKey,LongVal),
398
KEY StringField (FieldKey,StringVal(32))
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
SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
402
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
403
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
406
# Bug #1945 - Crashing bug with bad User Variables in UPDATE ... ORDER BY ...
408
CREATE TABLE t1 (a INT, b INT);
410
UPDATE t1 SET a=0 ORDER BY (a=@id), b;
414
# Bug when doing an order by on a 1 byte string (Bug #2147)
417
CREATE TABLE t1 ( id int NOT NULL default '0', menu int NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM;
418
INSERT INTO t1 VALUES (11384, 2),(11392, 2);
419
SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
423
# REF_OR_NULL optimization + filesort (bug #2419)
426
create table t1(a int, b int, index(b));
427
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
428
explain select * from t1 where b=1 or b is null order by a;
429
select * from t1 where b=1 or b is null order by a;
430
explain select * from t1 where b=2 or b is null order by a;
431
select * from t1 where b=2 or b is null order by a;
435
# Bug #3155 - Strange results with index (x, y) ... WHERE ... ORDER BY pk
438
create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null,
439
key(a,b,d), key(c,b,a));
440
create table t2 like t1;
441
insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3);
442
insert into t2 select null, b, c, d from t1;
443
insert into t1 select null, b, c, d from t2;
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;
454
insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10;
455
select * from t1 where a=1 and b in (1) order by c, b, a;
456
select * from t1 where a=1 and b in (1);
461
# Ambiguos order by when renamed column is identical to another in result.
462
# Should not fail and prefer column from t1 for sorting.
464
create table t1 (col1 int, col int);
465
create table t2 (col2 int, col int);
466
insert into t1 values (1,1),(2,2),(3,3);
467
insert into t2 values (1,3),(2,2),(3,1);
468
select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2)
472
# Let us also test various ambiguos and potentially ambiguos cases
476
select col1 as col, col from t1 order by col;
478
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
481
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
484
select col1 from t1, t2 where t1.col1=t2.col2 order by col;
486
select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2
489
select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2
491
select col2 as c, col as c from t2 order by col;
492
select col2 as col, col as col2 from t2 order by col;
493
select t2.col2, t2.col, t2.col from t2 order by col;
495
select t2.col2 as col from t2 order by t2.col;
496
select t2.col2 as col, t2.col from t2 order by t2.col;
497
select t2.col2, t2.col, t2.col from t2 order by t2.col;
502
# Bug #5428: a problem with small max_sort_length value
505
create table t1 (a char(25));
506
insert into t1 set a = repeat('x', 20);
507
insert into t1 set a = concat(repeat('x', 19), 'z');
508
insert into t1 set a = concat(repeat('x', 19), 'ab');
509
insert into t1 set a = concat(repeat('x', 19), 'aa');
510
set max_sort_length=20;
511
select a from t1 order by a;
519
`sid` decimal(8,0) default null,
520
`wnid` varchar(11) not null default '',
521
key `wnid14` (`wnid`(4)),
525
insert into t1 (`sid`, `wnid`) values
526
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
527
('39560','01019090000'),('37989','01019000000'),('37990','01019011000'),
528
('37991','01019011000'),('37992','01019019000'),('37993','01019030000'),
529
('37994','01019090000'),('475','02070000000'),('25253','02071100000'),
530
('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),
531
('25259','02071190000'),('25260','02071200000'),('25261','02071210000'),
532
('25262','02071290000'),('25263','02071300000'),('25264','02071310000'),
533
('25265','02071310000'),('25266','02071320000'),('25267','02071320000'),
534
('25269','02071330000'),('25270','02071340000'),('25271','02071350000'),
535
('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),
536
('25282','02071391000'),('25283','02071399000'),('25284','02071400000'),
537
('25285','02071410000'),('25286','02071410000'),('25287','02071420000'),
538
('25288','02071420000'),('25291','02071430000'),('25290','02071440000'),
539
('25292','02071450000'),('25293','02071460000'),('25294','02071470000'),
540
('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
542
explain select * from t1 where wnid like '0101%' order by wnid;
544
select * from t1 where wnid like '0101%' order by wnid;
549
# Bug #7672 - a wrong result for a select query in braces followed by order by
552
CREATE TABLE t1 (a int);
553
INSERT INTO t1 VALUES (2), (1), (1), (2), (1);
554
SELECT a FROM t1 ORDER BY a;
555
(SELECT a FROM t1) ORDER BY a;
559
# Bug #18767: global ORDER BY applied to a SELECT with ORDER BY either was
560
# ignored or 'concatened' to the latter.
562
CREATE TABLE t1 (a int, b int);
563
INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10);
565
(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a;
566
(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC;
567
(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b;
568
(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b;
573
# Bug #22457: Column alias in ORDER BY works, but not if in an expression
576
CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2);
577
SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
578
SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
579
SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
580
SELECT a + 1 AS num FROM t1 HAVING 30 - num;
582
SELECT a + 1 AS num, num + 1 FROM t1;
583
SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
585
SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
589
# Bug#25126: Reference to non-existant column in UPDATE...ORDER BY...
592
CREATE TABLE bug25126 (
593
val int NOT NULL AUTO_INCREMENT PRIMARY KEY
596
UPDATE bug25126 SET MissingCol = MissingCol;
598
UPDATE bug25126 SET val = val ORDER BY MissingCol;
599
UPDATE bug25126 SET val = val ORDER BY val;
600
UPDATE bug25126 SET val = 1 ORDER BY val;
602
UPDATE bug25126 SET val = 1 ORDER BY MissingCol;
604
UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol;
606
UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol;
608
UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol;
610
UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol;
612
UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol;
614
UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol;
618
# Bug #25427: crash when order by expression contains a name
619
# that cannot be resolved unambiguously
622
CREATE TABLE t1 (a int);
624
SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1;
626
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val;
628
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1;
633
# Bug #27532: ORDER/GROUP BY expressions with IN/BETWEEN and NOT IN/BETWEEN
636
CREATE TABLE t1 (a int);
637
INSERT INTO t1 VALUES (3), (2), (4), (1);
639
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
640
ORDER BY IF(a IN (2,3), a, a+10);
641
SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1
642
ORDER BY IF(a NOT IN (2,3), a, a+10);
643
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
644
ORDER BY IF(a NOT IN (2,3), a, a+10);
646
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
647
ORDER BY IF(a BETWEEN 2 AND 3, a, a+10);
648
SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1
649
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);
650
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
651
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);
653
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
654
FROM t1 GROUP BY x1, x2;
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, IF(a NOT IN (1,2), a, '');
658
# The remaining queries are for better coverage
659
SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2);
660
SELECT a FROM t1 ORDER BY a IN (1,2);
661
SELECT a+10 FROM t1 ORDER BY a IN (1,2);
662
SELECT a, IF(a IN (1,2), a, a+10) FROM t1
663
ORDER BY IF(a IN (3,4), a, a+10);
667
create table t1 (a int not null, b int not null, c int not null);
668
insert t1 values (1,1,1),(1,1,2),(1,2,1);
669
select a, b from t1 group by a, b order by sum(c);
673
# Bug#21302: Result not properly sorted when using an ORDER BY on a second
676
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
677
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
679
explain SELECT t1.b as a, t2.b as c FROM
680
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
682
SELECT t2.b as c FROM
683
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
686
# check that it still removes sort of const table
687
explain SELECT t1.b as a, t2.b as c FROM
688
t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
691
CREATE TABLE t2 LIKE t1;
692
INSERT INTO t2 SELECT * from t1;
693
CREATE TABLE t3 LIKE t1;
694
INSERT INTO t3 SELECT * from t1;
695
CREATE TABLE t4 LIKE t1;
696
INSERT INTO t4 SELECT * from t1;
697
INSERT INTO t1 values (0,0),(4,4);
699
SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
700
ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
702
DROP TABLE t1,t2,t3,t4;
705
# Bug#25376: Incomplete setup of ORDER BY clause results in a wrong result.
707
create table t1 (a int, b int, c int);
708
insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
709
select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
713
# Bug#26672: Incorrect SEC_TO_TIME() casting in ORDER BY
715
CREATE TABLE t1 (a INT NOT NULL, b TIME);
716
INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
717
UPDATE t1 SET b = SEC_TO_TIME(a);
720
SELECT a, b FROM t1 ORDER BY b DESC;
722
# must be ordered as the above
723
SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
728
# BUG#16590: Optimized does not do right "const" table pre-read
730
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
731
INSERT INTO t1 VALUES (1,1),(2,2);
733
CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
734
INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
736
SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
743
# Bug #30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
751
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
752
INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
753
INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
754
INSERT INTO t1 SELECT a +16, b +16 FROM t1;
755
INSERT INTO t1 SELECT a +32, b +32 FROM t1;
756
INSERT INTO t1 SELECT a +64, b +64 FROM t1;
758
SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
760
SELECT @tmp_tables_after = @tmp_tables_before ;
762
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
764
SELECT @tmp_tables_after = @tmp_tables_before;
768
--echo # Bug#31590: Wrong error message on sort buffer being too small.
770
create table t1(a int, b text);
771
insert into t1 values (1,2),(3,2);
772
set session sort_buffer_size= 30000;
773
set session max_sort_length= 2180;
774
select * from t1 order by b;
778
# Bug #35206: select query result different if the key is indexed or not
781
CREATE TABLE t2 (a varchar(32), b int, c float, d double,
782
UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c));
784
CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));
785
CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b));
788
INSERT INTO t1 (a, b) VALUES
789
('domestic', 'CH'), ('domestic', 'LI'), ('plfcz1', 'FR'), ('all', 'AD'),
790
('all', 'AE'), ('all', 'AF'), ('all', 'AG'), ('all', 'AI'), ('all', 'AL'),
791
('all', 'AM'), ('all', 'AN'), ('all', 'AO'), ('all', 'AP'), ('all', 'AQ'),
792
('all', 'AR'), ('all', 'AS'), ('all', 'AT'), ('all', 'AU'), ('all', 'AW'),
793
('all', 'AZ'), ('all', 'BA'), ('all', 'BB'), ('all', 'BD'), ('all', 'BE'),
794
('all', 'BF'), ('all', 'BG'), ('all', 'BH'), ('all', 'BI'), ('all', 'BJ'),
795
('all', 'BM'), ('all', 'BN'), ('all', 'BO'), ('all', 'BR'), ('all', 'BS'),
796
('all', 'BT'), ('all', 'BV'), ('all', 'BW'), ('all', 'BY'), ('all', 'BZ'),
797
('all', 'CA'), ('all', 'CC'), ('all', 'CD'), ('all', 'CF'), ('all', 'CG'),
798
('all', 'CH'), ('all', 'CI'), ('all', 'CK'), ('all', 'CL'), ('all', 'CM'),
799
('all', 'CN'), ('all', 'CO'), ('all', 'CR'), ('all', 'CU'), ('all', 'CV'),
800
('all', 'CX'), ('all', 'CY'), ('all', 'CZ'), ('all', 'DE'), ('all', 'DJ'),
801
('all', 'DK'), ('all', 'DM'), ('all', 'DO'), ('all', 'DZ'), ('all', 'EC'),
802
('all', 'EE'), ('all', 'EG'), ('all', 'EH'), ('all', 'EI'), ('all', 'ER'),
803
('all', 'ES'), ('all', 'ET'), ('all', 'FI'), ('all', 'FJ'), ('all', 'FK'),
804
('all', 'FM'), ('all', 'FO'), ('all', 'FR'), ('all', 'FX'), ('all', 'GA'),
805
('all', 'GB'), ('all', 'GD'), ('all', 'GE'), ('all', 'GF'), ('all', 'GH'),
806
('all', 'GI'), ('all', 'GL'), ('all', 'GM'), ('all', 'GN'), ('all', 'GP'),
807
('all', 'GQ'), ('all', 'GR'), ('all', 'GS'), ('all', 'GT'), ('all', 'GU'),
808
('all', 'GW'), ('all', 'GY'), ('all', 'HK'), ('all', 'HM'), ('all', 'HN'),
809
( 'all', 'HR'), ( 'all', 'HT'), ( 'all', 'HU'), ( 'all', 'ID'), ( 'all', 'IE'),
810
( 'all', 'IL'), ( 'all', 'IN'), ( 'all', 'IO'), ( 'all', 'IQ'), ( 'all', 'IR'),
811
( 'all', 'IS'), ( 'all', 'IT'), ( 'all', 'JM'), ( 'all', 'JO'), ( 'all', 'JP'),
812
( 'all', 'KE'), ( 'all', 'KG'), ( 'all', 'KH'), ( 'all', 'KI'), ( 'all', 'KM'),
813
( 'all', 'KN'), ( 'all', 'KP'), ( 'all', 'KR'), ( 'all', 'KW'), ( 'all', 'KY'),
814
( 'all', 'KZ'), ( 'all', 'LA'), ( 'all', 'LB'), ( 'all', 'LC'), ( 'all', 'LI'),
815
( 'all', 'LK'), ( 'all', 'LR'), ( 'all', 'LS'), ( 'all', 'LT'), ( 'all', 'LU'),
816
( 'all', 'LV'), ( 'all', 'LY'), ( 'all', 'MA'), ( 'all', 'MC'), ( 'all', 'MD'),
817
( 'all', 'ME'), ( 'all', 'MG'), ( 'all', 'MH'), ( 'all', 'MK'), ( 'all', 'ML'),
818
( 'all', 'MM'), ( 'all', 'MN'), ( 'all', 'MO'), ( 'all', 'MP'), ( 'all', 'MQ'),
819
( 'all', 'MR'), ( 'all', 'MS'), ( 'all', 'MT'), ( 'all', 'MU'), ( 'all', 'MV'),
820
( 'all', 'MW'), ( 'all', 'MX'), ( 'all', 'MY'), ( 'all', 'MZ'), ( 'all', 'NA'),
821
( 'all', 'NC'), ( 'all', 'NE'), ( 'all', 'NF'), ( 'all', 'NG'), ( 'all', 'NI'),
822
( 'all', 'NL'), ( 'all', 'NO'), ( 'all', 'NP'), ( 'all', 'NR'), ( 'all', 'NU'),
823
( 'all', 'NV'), ( 'all', 'NZ'), ( 'all', 'OM'), ( 'all', 'PA'), ( 'all', 'PE'),
824
( 'all', 'PF'), ( 'all', 'PG'), ( 'all', 'PH'), ( 'all', 'PK'), ( 'all', 'PL'),
825
( 'all', 'PM'), ( 'all', 'PN'), ( 'all', 'PR'), ( 'all', 'PS'), ( 'all', 'PT'),
826
( 'all', 'PW'), ( 'all', 'PY'), ( 'all', 'QA'), ( 'all', 'RE'), ( 'all', 'RO'),
827
( 'all', 'RU'), ( 'all', 'RW'), ( 'all', 'SA'), ( 'all', 'SB'), ( 'all', 'SC'),
828
( 'all', 'SD'), ( 'all', 'SE'), ( 'all', 'SG'), ( 'all', 'SH'), ( 'all', 'SI'),
829
( 'all', 'SJ'), ( 'all', 'SK'), ( 'all', 'SL'), ( 'all', 'SM'), ( 'all', 'SN'),
830
( 'all', 'SO'), ( 'all', 'SR'), ( 'all', 'ST'), ( 'all', 'SV'), ( 'all', 'SY'),
831
( 'all', 'SZ'), ( 'all', 'TA'), ( 'all', 'TC'), ( 'all', 'TD'), ( 'all', 'TF'),
832
( 'all', 'TG'), ( 'all', 'TH'), ( 'all', 'TJ'), ( 'all', 'TK'), ( 'all', 'TM'),
833
( 'all', 'TN'), ( 'all', 'TO'), ( 'all', 'TP'), ( 'all', 'TR'), ( 'all', 'TT'),
834
( 'all', 'TV'), ( 'all', 'TW'), ( 'all', 'TZ'), ( 'all', 'UA'), ( 'all', 'UG'),
835
( 'all', 'UM'), ( 'all', 'US'), ( 'all', 'UY'), ( 'all', 'UZ'), ( 'all', 'VA'),
836
( 'all', 'VC'), ( 'all', 'VE'), ( 'all', 'VG'), ( 'all', 'VI'), ( 'all', 'VN'),
837
( 'all', 'VU'), ( 'all', 'WF'), ( 'all', 'WS'), ( 'plfcz1', 'FI'),
838
( 'all', 'XE'), ( 'all', 'XS'), ( 'all', 'XU'), ( 'plfcz1', 'XE'),
839
( 'all', 'YE'), ( 'all', 'YT'), ( 'all', 'YU'), ( 'all', 'ZA'), ( 'all', 'ZM'),
840
( 'all', 'ZR'), ( 'all', 'ZW'), ( 'foreign', 'AD'), ( 'foreign', 'AE'),
841
( 'foreign', 'AF'), ( 'foreign', 'AG'), ( 'foreign', 'AI'),
842
( 'foreign', 'AL'), ( 'foreign', 'AM'), ( 'foreign', 'AN'), ( 'foreign', 'AO'),
843
( 'foreign', 'AP'), ( 'foreign', 'AQ'), ( 'foreign', 'AR'), ( 'foreign', 'AS'),
844
( 'foreign', 'AT'), ( 'foreign', 'AU'), ( 'foreign', 'AW'), ( 'foreign', 'AZ'),
845
( 'foreign', 'BA'), ( 'foreign', 'BB'), ( 'foreign', 'BD'), ( 'foreign', 'BE'),
846
( 'foreign', 'BF'), ( 'foreign', 'BG'), ( 'foreign', 'BH'), ( 'foreign', 'BI'),
847
( 'foreign', 'BJ'), ( 'foreign', 'BM'), ( 'foreign', 'BN'), ( 'foreign', 'BO'),
848
( 'foreign', 'BR'), ( 'foreign', 'BS'), ( 'foreign', 'BT'), ( 'foreign', 'BV'),
849
( 'foreign', 'BW'), ( 'foreign', 'BY'), ( 'foreign', 'BZ'), ( 'foreign', 'CA'),
850
( 'foreign', 'CC'), ( 'foreign', 'CD'), ( 'foreign', 'CF'), ( 'foreign', 'CG'),
851
( 'foreign', 'CI'), ( 'foreign', 'CK'), ( 'foreign', 'CL'), ( 'foreign', 'CM'),
852
( 'foreign', 'CN'), ( 'foreign', 'CO'), ( 'foreign', 'CR'), ( 'foreign', 'CU'),
853
( 'foreign', 'CV'), ( 'foreign', 'CX'), ( 'foreign', 'CY'), ( 'foreign', 'CZ'),
854
( 'foreign', 'DE'), ( 'foreign', 'DJ'), ( 'foreign', 'DK'), ( 'foreign', 'DM'),
855
( 'foreign', 'DO'), ( 'foreign', 'DZ'), ( 'foreign', 'EC'), ( 'foreign', 'EE'),
856
( 'foreign', 'EG'), ( 'foreign', 'EH'), ( 'foreign', 'EI'), ( 'foreign', 'ER'),
857
( 'foreign', 'ES'), ( 'foreign', 'ET'), ( 'foreign', 'FI'), ( 'foreign', 'FJ'),
858
( 'foreign', 'FK'), ( 'foreign', 'FM'), ( 'foreign', 'FO'), ( 'foreign', 'FR'),
859
( 'foreign', 'FX'), ( 'foreign', 'GA'), ( 'foreign', 'GB'), ( 'foreign', 'GD'),
860
( 'foreign', 'GE'), ( 'foreign', 'GF'), ( 'foreign', 'GH'), ( 'foreign', 'GI'),
861
( 'foreign', 'GL'), ( 'foreign', 'GM'), ( 'foreign', 'GN'), ( 'foreign', 'GP'),
862
( 'foreign', 'GQ'), ( 'foreign', 'GR'), ( 'foreign', 'GS'), ( 'foreign', 'GT'),
863
( 'foreign', 'GU'), ( 'foreign', 'GW'), ( 'foreign', 'GY'), ( 'foreign', 'HK'),
864
( 'foreign', 'HM'), ( 'foreign', 'HN'), ( 'foreign', 'HR'), ( 'foreign', 'HT'),
865
( 'foreign', 'HU'), ( 'foreign', 'ID'), ( 'foreign', 'IE'), ( 'foreign', 'IL'),
866
( 'foreign', 'IN'), ( 'foreign', 'IO'), ( 'foreign', 'IQ'), ( 'foreign', 'IR'),
867
( 'foreign', 'IS'), ( 'foreign', 'IT'), ( 'foreign', 'JM'), ( 'foreign', 'JO'),
868
( 'foreign', 'JP'), ( 'foreign', 'KE'), ( 'foreign', 'KG'), ( 'foreign', 'KH'),
869
( 'foreign', 'KI'), ( 'foreign', 'KM'), ( 'foreign', 'KN'), ( 'foreign', 'KP'),
870
( 'foreign', 'KR'), ( 'foreign', 'KW'), ( 'foreign', 'KY'), ( 'foreign', 'KZ'),
871
( 'foreign', 'LA'), ( 'foreign', 'LB'), ( 'foreign', 'LC'), ( 'foreign', 'LK'),
872
( 'foreign', 'LR'), ( 'foreign', 'LS'), ( 'foreign', 'LT'), ( 'foreign', 'LU'),
873
( 'foreign', 'LV'), ( 'foreign', 'LY'), ( 'foreign', 'MA'), ( 'foreign', 'MC'),
874
( 'foreign', 'MD'), ( 'foreign', 'ME'), ( 'foreign', 'MG'), ( 'foreign', 'MH'),
875
( 'foreign', 'MK'), ( 'foreign', 'ML'), ( 'foreign', 'MM'), ( 'foreign', 'MN'),
876
( 'foreign', 'MO'), ( 'foreign', 'MP'), ( 'foreign', 'MQ'), ( 'foreign', 'MR'),
877
( 'foreign', 'MS'), ( 'foreign', 'MT'), ( 'foreign', 'MU'), ( 'foreign', 'MV'),
878
( 'foreign', 'MW'), ( 'foreign', 'MX'), ( 'foreign', 'MY'), ( 'foreign', 'MZ'),
879
( 'foreign', 'NA'), ( 'foreign', 'NC'), ( 'foreign', 'NE'), ( 'foreign', 'NF'),
880
( 'foreign', 'NG'), ( 'foreign', 'NI'), ( 'foreign', 'NL'), ( 'foreign', 'NO'),
881
( 'foreign', 'NP'), ( 'foreign', 'NR'), ( 'foreign', 'NU'), ( 'foreign', 'NV'),
882
( 'foreign', 'NZ'), ( 'foreign', 'OM'), ( 'foreign', 'PA'), ( 'foreign', 'PE'),
883
( 'foreign', 'PF'), ( 'foreign', 'PG'), ( 'foreign', 'PH'), ( 'foreign', 'PK'),
884
( 'foreign', 'PL'), ( 'foreign', 'PM'), ( 'foreign', 'PN'), ( 'foreign', 'PR'),
885
( 'foreign', 'PS'), ( 'foreign', 'PT'), ( 'foreign', 'PW'), ( 'foreign', 'PY'),
886
( 'foreign', 'QA'), ( 'foreign', 'RE'), ( 'foreign', 'RO'), ( 'foreign', 'RU'),
887
( 'foreign', 'RW'), ( 'foreign', 'SA'), ( 'foreign', 'SB'), ( 'foreign', 'SC'),
888
( 'foreign', 'SD'), ( 'foreign', 'SE'), ( 'foreign', 'SG'), ( 'foreign', 'SH'),
889
( 'foreign', 'SI'), ( 'foreign', 'SJ'), ( 'foreign', 'SK'), ( 'foreign', 'SL'),
890
( 'foreign', 'SM'), ( 'foreign', 'SN'), ( 'foreign', 'SO'), ( 'foreign', 'SR'),
891
( 'foreign', 'ST'), ( 'foreign', 'SV'), ( 'foreign', 'SY'), ( 'foreign', 'SZ'),
892
( 'foreign', 'TA'), ( 'foreign', 'TC'), ( 'foreign', 'TD'), ( 'foreign', 'TF'),
893
( 'foreign', 'TG'), ( 'foreign', 'TH'), ( 'foreign', 'TJ'), ( 'foreign', 'TK'),
894
( 'foreign', 'TM'), ( 'foreign', 'TN'), ( 'foreign', 'TO'), ( 'foreign', 'TP'),
895
( 'foreign', 'TR'), ( 'foreign', 'TT'), ( 'foreign', 'TV'), ( 'foreign', 'TW'),
896
( 'foreign', 'TZ'), ( 'foreign', 'UA'), ( 'foreign', 'UG'), ( 'foreign', 'UM'),
897
( 'foreign', 'US'), ( 'foreign', 'UY'), ( 'foreign', 'UZ'), ( 'foreign', 'VA'),
898
( 'foreign', 'VC'), ( 'foreign', 'VE'), ( 'foreign', 'VG'), ( 'foreign', 'VI'),
899
( 'foreign', 'VN'), ( 'foreign', 'VU'), ( 'foreign', 'WF'), ( 'foreign', 'WS'),
900
( 'plfcz1', 'DK'), ( 'foreign', 'XE'), ( 'foreign', 'XS'), ( 'foreign', 'XU'),
901
( 'plfcz1', 'BE'), ( 'foreign', 'YE'), ( 'foreign', 'YT'), ( 'foreign', 'YU'),
902
( 'foreign', 'ZA'), ( 'foreign', 'ZM'), ( 'foreign', 'ZR'), ( 'foreign', 'ZW'),
903
( 'plfcz1', 'DE'), ( 'plfcz1', 'GI'), ( 'plfcz1', 'GR'), ( 'plfcz1', 'IS'),
904
( 'plfcz1', 'EI'), ( 'plfcz1', 'IT'), ( 'plfcz1', 'LU'), ( 'plfcz1', 'NL'),
905
( 'plfcz1', 'NO'), ( 'plfcz1', 'ES'), ( 'plfcz1', 'SE'), ( 'plfcz1', 'AL'),
906
( 'plfcz1', 'AD'), ( 'plfcz1', 'BY'), ( 'plfcz1', 'BA'), ( 'plfcz1', 'BG'),
907
( 'plfcz1', 'EE'), ( 'plfcz1', 'FO'), ( 'plfcz1', 'GL'), ( 'plfcz1', 'GB'),
908
( 'plfcz1', 'HR'), ( 'plfcz1', 'LV'), ( 'plfcz1', 'LT'), ( 'plfcz1', 'MT'),
909
( 'plfcz1', 'MK'), ( 'plfcz1', 'MD'), ( 'plfcz1', 'MC'), ( 'plfcz1', 'AT'),
910
( 'plfcz1', 'PL'), ( 'plfcz1', 'PT'), ( 'plfcz1', 'RO'), ( 'plfcz1', 'RU'),
911
( 'plfcz1', 'SM'), ( 'plfcz1', 'XS'), ( 'plfcz1', 'SK'), ( 'plfcz1', 'SI'),
912
( 'plfcz1', 'CZ'), ( 'plfcz1', 'TR'), ( 'plfcz1', 'UA'), ( 'plfcz1', 'HU'),
913
( 'plfcz1', 'VA'), ( 'plfcz1', 'CY'), ( 'plfcz2', 'AF'), ( 'plfcz2', 'DZ'),
914
( 'plfcz2', 'AS'), ( 'plfcz2', 'AO'), ( 'plfcz2', 'AI'), ( 'plfcz2', 'AQ'),
915
( 'plfcz2', 'AG'), ( 'plfcz2', 'AR'), ( 'plfcz2', 'AM'), ( 'plfcz2', 'AW'),
916
( 'plfcz2', 'AU'), ( 'plfcz2', 'AZ'), ( 'plfcz2', 'AP'), ( 'plfcz2', 'BS'),
917
( 'plfcz2', 'BH'), ( 'plfcz2', 'BD'), ( 'plfcz2', 'BB'), ( 'plfcz2', 'BZ'),
918
( 'plfcz2', 'BJ'), ( 'plfcz2', 'BM'), ( 'plfcz2', 'BT'), ( 'plfcz2', 'BO'),
919
( 'plfcz2', 'BW'), ( 'plfcz2', 'BV'), ( 'plfcz2', 'BR'), ( 'plfcz2', 'IO'),
920
( 'plfcz2', 'VG'), ( 'plfcz2', 'BN'), ( 'plfcz2', 'BF'), ( 'plfcz2', 'BI'),
921
( 'plfcz2', 'KH'), ( 'plfcz2', 'CM'), ( 'plfcz2', 'CA'), ( 'plfcz2', 'CV'),
922
( 'plfcz2', 'KY'), ( 'plfcz2', 'CF'), ( 'plfcz2', 'TD'), ( 'plfcz2', 'CL'),
923
( 'plfcz2', 'CN'), ( 'plfcz2', 'CX'), ( 'plfcz2', 'CC'), ( 'plfcz2', 'CO'),
924
( 'plfcz2', 'KM'), ( 'plfcz2', 'CG'), ( 'plfcz2', 'CD'), ( 'plfcz2', 'CK'),
925
( 'plfcz2', 'CR'), ( 'plfcz2', 'CI'), ( 'plfcz2', 'CU'), ( 'plfcz2', 'DJ'),
926
( 'plfcz2', 'DM'), ( 'plfcz2', 'DO'), ( 'plfcz2', 'TP'), ( 'plfcz2', 'EC'),
927
( 'plfcz2', 'EG'), ( 'plfcz2', 'SV'), ( 'plfcz2', 'GQ'), ( 'plfcz2', 'ER'),
928
( 'plfcz2', 'ET'), ( 'plfcz2', 'FK'), ( 'plfcz2', 'FJ'), ( 'plfcz2', 'FX'),
929
( 'plfcz2', 'GF'), ( 'plfcz2', 'PF'), ( 'plfcz2', 'TA'), ( 'plfcz2', 'TF'),
930
( 'plfcz2', 'GA'), ( 'plfcz2', 'GM'), ( 'plfcz2', 'GE'), ( 'plfcz2', 'GH'),
931
( 'plfcz2', 'GD'), ( 'plfcz2', 'GP'), ( 'plfcz2', 'GU'), ( 'plfcz2', 'GT'),
932
( 'plfcz2', 'GN'), ( 'plfcz2', 'GW'), ( 'plfcz2', 'GY'), ( 'plfcz2', 'HT'),
933
( 'plfcz2', 'HM'), ( 'plfcz2', 'HN'), ( 'plfcz2', 'HK'), ( 'plfcz2', 'IN'),
934
( 'plfcz2', 'ID'), ( 'plfcz2', 'IR'), ( 'plfcz2', 'IQ'), ( 'plfcz2', 'IE'),
935
( 'plfcz2', 'IL'), ( 'plfcz2', 'JM'), ( 'plfcz2', 'JP'), ( 'plfcz2', 'JO'),
936
( 'plfcz2', 'KZ'), ( 'plfcz2', 'KE'), ( 'plfcz2', 'KI'), ( 'plfcz2', 'KP'),
937
( 'plfcz2', 'KW'), ( 'plfcz2', 'KG'), ( 'plfcz2', 'LA'), ( 'plfcz2', 'LB'),
938
( 'plfcz2', 'LS'), ( 'plfcz2', 'LR'), ( 'plfcz2', 'LY'), ( 'plfcz2', 'MO'),
939
( 'plfcz2', 'MG'), ( 'plfcz2', 'ME'), ( 'plfcz2', 'MW'), ( 'plfcz2', 'MY'),
940
( 'plfcz2', 'MV'), ( 'plfcz2', 'ML'), ( 'plfcz2', 'MH'), ( 'plfcz2', 'MQ'),
941
( 'plfcz2', 'MR'), ( 'plfcz2', 'MU'), ( 'plfcz2', 'YT'), ( 'plfcz2', 'MX'),
942
( 'plfcz2', 'FM'), ( 'plfcz2', 'MN'), ( 'plfcz2', 'MS'), ( 'plfcz2', 'MA'),
943
( 'plfcz2', 'MZ'), ( 'plfcz2', 'MM'), ( 'plfcz2', 'NA'), ( 'plfcz2', 'NR'),
944
( 'plfcz2', 'NP'), ( 'plfcz2', 'AN'), ( 'plfcz2', 'NC'), ( 'plfcz2', 'NZ'),
945
( 'plfcz2', 'NI'), ( 'plfcz2', 'NE'), ( 'plfcz2', 'NG'), ( 'plfcz2', 'NU'),
946
( 'plfcz2', 'NF'), ( 'plfcz2', 'MP'), ( 'plfcz2', 'OM'), ( 'plfcz2', 'PK'),
947
( 'plfcz2', 'PW'), ( 'plfcz2', 'PS'), ( 'plfcz2', 'PA'), ( 'plfcz2', 'PG'),
948
( 'plfcz2', 'PY'), ( 'plfcz2', 'PE'), ( 'plfcz2', 'PH'), ( 'plfcz2', 'PN'),
949
( 'plfcz2', 'PR'), ( 'plfcz2', 'QA'), ( 'plfcz2', 'RE'), ( 'plfcz2', 'RW'),
950
( 'plfcz2', 'KN'), ( 'plfcz2', 'ST'), ( 'plfcz2', 'SA'), ( 'plfcz2', 'SN'),
951
( 'plfcz2', 'SC'), ( 'plfcz2', 'SL'), ( 'plfcz2', 'SG'), ( 'plfcz2', 'SB'),
952
( 'plfcz2', 'SO'), ( 'plfcz2', 'ZA'), ( 'plfcz2', 'GS'), ( 'plfcz2', 'KR'),
953
( 'plfcz2', 'LK'), ( 'plfcz2', 'NV'), ( 'plfcz2', 'SH'), ( 'plfcz2', 'LC'),
954
( 'plfcz2', 'PM'), ( 'plfcz2', 'VC'), ( 'plfcz2', 'SD'), ( 'plfcz2', 'SR'),
955
( 'plfcz2', 'SJ'), ( 'plfcz2', 'SZ'), ( 'plfcz2', 'SY'), ( 'plfcz2', 'TW'),
956
( 'plfcz2', 'TJ'), ( 'plfcz2', 'TZ'), ( 'plfcz2', 'TH'), ( 'plfcz2', 'TG'),
957
( 'plfcz2', 'TK'), ( 'plfcz2', 'TO'), ( 'plfcz2', 'TT'), ( 'plfcz2', 'XU'),
958
( 'plfcz2', 'TN'), ( 'plfcz2', 'TM'), ( 'plfcz2', 'TC'), ( 'plfcz2', 'TV'),
959
( 'plfcz2', 'UG'), ( 'plfcz2', 'AE'), ( 'plfcz2', 'US'), ( 'plfcz2', 'UM'),
960
( 'plfcz2', 'UY'), ( 'plfcz2', 'UZ'), ( 'plfcz2', 'VU'), ( 'plfcz2', 'VE'),
961
( 'plfcz2', 'VN'), ( 'plfcz2', 'VI'), ( 'plfcz2', 'WF'), ( 'plfcz2', 'EH'),
962
( 'plfcz2', 'WS'), ( 'plfcz2', 'YE'), ( 'plfcz2', 'YU'), ( 'plfcz2', 'ZR'),
963
( 'plfcz2', 'ZM'), ( 'plfcz2', 'ZW'), ( 'ppfcz1', 'AT'), ( 'ppfcz1', 'BE'),
964
( 'ppfcz1', 'DE'), ( 'ppfcz1', 'FR'), ( 'ppfcz1', 'FX'), ( 'ppfcz1', 'IT'),
965
( 'ppfcz1', 'LU'), ( 'ppfcz1', 'MC'), ( 'ppfcz1', 'NL'), ( 'ppfcz1', 'SM'),
966
( 'ppfcz1', 'VA'), ( 'ppfcz1', 'XE'), ( 'ppfcz2', 'AD'), ( 'ppfcz2', 'AL'),
967
( 'ppfcz2', 'BA'), ( 'ppfcz2', 'BG'), ( 'ppfcz2', 'BY'), ( 'ppfcz2', 'CY'),
968
( 'ppfcz2', 'CZ'), ( 'ppfcz2', 'DK'), ( 'ppfcz2', 'EE'), ( 'ppfcz2', 'EI'),
969
( 'ppfcz2', 'ES'), ( 'ppfcz2', 'FI'), ( 'ppfcz2', 'FO'), ( 'ppfcz2', 'GB'),
970
( 'ppfcz2', 'GI'), ( 'ppfcz2', 'GL'), ( 'ppfcz2', 'GR'), ( 'ppfcz2', 'HR'),
971
( 'ppfcz2', 'HU'), ( 'ppfcz2', 'IE'), ( 'ppfcz2', 'IS'), ( 'ppfcz2', 'LT'),
972
( 'ppfcz2', 'LV'), ( 'ppfcz2', 'MD'), ( 'ppfcz2', 'MK'), ( 'ppfcz2', 'MT'),
973
( 'ppfcz2', 'NO'), ( 'ppfcz2', 'PL'), ( 'ppfcz2', 'PT'), ( 'ppfcz2', 'RO'),
974
( 'ppfcz2', 'RU'), ( 'ppfcz2', 'SE'), ( 'ppfcz2', 'SI'), ( 'ppfcz2', 'SK'),
975
( 'ppfcz2', 'TR'), ( 'ppfcz2', 'UA'), ( 'ppfcz2', 'XS'), ( 'ppfcz2', 'YU'),
976
( 'ppfcz3', 'CA'), ( 'ppfcz3', 'DZ'), ( 'ppfcz3', 'EG'), ( 'ppfcz3', 'IL'),
977
( 'ppfcz3', 'JO'), ( 'ppfcz3', 'LB'), ( 'ppfcz3', 'LY'), ( 'ppfcz3', 'MA'),
978
( 'ppfcz3', 'MX'), ( 'ppfcz3', 'PM'), ( 'ppfcz3', 'SY'), ( 'ppfcz3', 'TN'),
979
( 'ppfcz3', 'US'), ( 'ppfcz4', 'AE'), ( 'ppfcz4', 'AF'), ( 'ppfcz4', 'AM'),
980
( 'ppfcz4', 'AO'), ( 'ppfcz4', 'AZ'), ( 'ppfcz4', 'BD'), ( 'ppfcz4', 'BF'),
981
( 'ppfcz4', 'BH'), ( 'ppfcz4', 'BI'), ( 'ppfcz4', 'BJ'), ( 'ppfcz4', 'BT'),
982
( 'ppfcz4', 'BV'), ( 'ppfcz4', 'BW'), ( 'ppfcz4', 'CF'), ( 'ppfcz4', 'CG'),
983
( 'ppfcz4', 'CI'), ( 'ppfcz4', 'CM'), ( 'ppfcz4', 'CN'), ( 'ppfcz4', 'DJ'),
984
( 'ppfcz4', 'DO'), ( 'ppfcz4', 'ER'), ( 'ppfcz4', 'ET'), ( 'ppfcz4', 'GA'),
985
( 'ppfcz4', 'GE'), ( 'ppfcz4', 'GH'), ( 'ppfcz4', 'GM'), ( 'ppfcz4', 'GN'),
986
( 'ppfcz4', 'GQ'), ( 'ppfcz4', 'GW'), ( 'ppfcz4', 'HK'), ( 'ppfcz4', 'IN'),
987
( 'ppfcz4', 'IQ'), ( 'ppfcz4', 'IR'), ( 'ppfcz4', 'JP'), ( 'ppfcz4', 'KE'),
988
( 'ppfcz4', 'KG'), ( 'ppfcz4', 'KH'), ( 'ppfcz4', 'KP'), ( 'ppfcz4', 'KW'),
989
( 'ppfcz4', 'KZ'), ( 'ppfcz4', 'LA'), ( 'ppfcz4', 'LK'), ( 'ppfcz4', 'LR'),
990
( 'ppfcz4', 'LS'), ( 'ppfcz4', 'MG'), ( 'ppfcz4', 'ML'), ( 'ppfcz4', 'MM'),
991
( 'ppfcz4', 'MN'), ( 'ppfcz4', 'MO'), ( 'ppfcz4', 'MR'), ( 'ppfcz4', 'MU'),
992
( 'ppfcz4', 'MV'), ( 'ppfcz4', 'MW'), ( 'ppfcz4', 'MY'), ( 'ppfcz4', 'MZ'),
993
( 'ppfcz4', 'NA'), ( 'ppfcz4', 'NE'), ( 'ppfcz4', 'NG'), ( 'ppfcz4', 'NP'),
994
( 'ppfcz4', 'OM'), ( 'ppfcz4', 'PK'), ( 'ppfcz4', 'QA'), ( 'ppfcz4', 'RE'),
995
( 'ppfcz4', 'RW'), ( 'ppfcz4', 'SA'), ( 'ppfcz4', 'SC'), ( 'ppfcz4', 'SD'),
996
( 'ppfcz4', 'SG'), ( 'ppfcz4', 'SH'), ( 'ppfcz4', 'SL'), ( 'ppfcz4', 'SN'),
997
( 'ppfcz4', 'SO'), ( 'ppfcz4', 'SZ'), ( 'ppfcz4', 'TD'), ( 'ppfcz4', 'TG'),
998
( 'ppfcz4', 'TH'), ( 'ppfcz4', 'TJ'), ( 'ppfcz4', 'TM'), ( 'ppfcz4', 'TW'),
999
( 'ppfcz4', 'TZ'), ( 'ppfcz4', 'UG'), ( 'ppfcz4', 'UZ'), ( 'ppfcz4', 'VN'),
1000
( 'ppfcz4', 'XU'), ( 'ppfcz4', 'YT'), ( 'ppfcz4', 'ZA'), ( 'ppfcz4', 'ZW'),
1001
( 'ppfcz5', 'AG'), ( 'ppfcz5', 'AI'), ( 'ppfcz5', 'AN'), ( 'ppfcz5', 'AP'),
1002
( 'ppfcz5', 'AQ'), ( 'ppfcz5', 'AR'), ( 'ppfcz5', 'AS'), ( 'ppfcz5', 'AU'),
1003
( 'ppfcz5', 'AW'), ( 'ppfcz5', 'BB'), ( 'ppfcz5', 'BM'), ( 'ppfcz5', 'BN'),
1004
( 'ppfcz5', 'BO'), ( 'ppfcz5', 'BR'), ( 'ppfcz5', 'BS'), ( 'ppfcz5', 'BZ'),
1005
( 'ppfcz5', 'CC'), ( 'ppfcz5', 'CD'), ( 'ppfcz5', 'CK'), ( 'ppfcz5', 'CL'),
1006
( 'ppfcz5', 'CO'), ( 'ppfcz5', 'CR'), ( 'ppfcz5', 'CU'), ( 'ppfcz5', 'CV'),
1007
( 'ppfcz5', 'CX'), ( 'ppfcz5', 'DM'), ( 'ppfcz5', 'EC'), ( 'ppfcz5', 'EH'),
1008
( 'ppfcz5', 'FJ'), ( 'ppfcz5', 'FK'), ( 'ppfcz5', 'FM'), ( 'ppfcz5', 'GD'),
1009
( 'ppfcz5', 'GF'), ( 'ppfcz5', 'GP'), ( 'ppfcz5', 'GS'), ( 'ppfcz5', 'GT'),
1010
( 'ppfcz5', 'GU'), ( 'ppfcz5', 'GY'), ( 'ppfcz5', 'HM'), ( 'ppfcz5', 'HN'),
1011
( 'ppfcz5', 'HT'), ( 'ppfcz5', 'ID'), ( 'ppfcz5', 'IO'), ( 'ppfcz5', 'JM'),
1012
( 'ppfcz5', 'KI'), ( 'ppfcz5', 'KM'), ( 'ppfcz5', 'KN'), ( 'ppfcz5', 'KR'),
1013
( 'ppfcz5', 'KY'), ( 'ppfcz5', 'LC'), ( 'ppfcz5', 'ME'), ( 'ppfcz5', 'MH'),
1014
( 'ppfcz5', 'MP'), ( 'ppfcz5', 'MQ'), ( 'ppfcz5', 'MS'), ( 'ppfcz5', 'NC'),
1015
( 'ppfcz5', 'NF'), ( 'ppfcz5', 'NI'), ( 'ppfcz5', 'NR'), ( 'ppfcz5', 'NU'),
1016
( 'ppfcz5', 'NZ'), ( 'ppfcz5', 'PA'), ( 'ppfcz5', 'PE'), ( 'ppfcz5', 'PF'),
1017
( 'ppfcz5', 'PG'), ( 'ppfcz5', 'PH'), ( 'ppfcz5', 'PN'), ( 'ppfcz5', 'PR'),
1018
( 'ppfcz5', 'PS'), ( 'ppfcz5', 'PW'), ( 'ppfcz5', 'PY'), ( 'ppfcz5', 'SB'),
1019
( 'ppfcz5', 'SJ'), ( 'ppfcz5', 'SR'), ( 'ppfcz5', 'ST'), ( 'ppfcz5', 'SV'),
1020
( 'ppfcz5', 'TA'), ( 'ppfcz5', 'TC'), ( 'ppfcz5', 'TF'), ( 'ppfcz5', 'TK'),
1021
( 'ppfcz5', 'TO'), ( 'ppfcz5', 'TP'), ( 'ppfcz5', 'TT'), ( 'ppfcz5', 'TV'),
1022
( 'ppfcz5', 'UM'), ( 'ppfcz5', 'UY'), ( 'ppfcz5', 'VC'), ( 'ppfcz5', 'VE'),
1023
( 'ppfcz5', 'VG'), ( 'ppfcz5', 'VI'), ( 'ppfcz5', 'VU'), ( 'ppfcz5', 'WF'),
1024
( 'ppfcz5', 'WS'), ( 'ppfcz5', 'YE'), ( 'ppfcz5', 'ZM'), ( 'ppfcz5', 'ZR');
1026
INSERT INTO t2 (a, b, c, d) VALUES
1027
('domestic', 26, 0.25, 4.7), ('domestic', 27, 0.25, 6),
1028
('domestic', 19, 2, 6.3), ('domestic', 19, 5, 7.77),
1029
('domestic', 19, 10, 10.3), ('domestic', 19, 20, 14.83),
1030
('domestic', 19, 30, 20.88), ('domestic', 20, 2, 7.3),
1031
('domestic', 20, 5, 8.77), ('domestic', 20, 10, 11.3),
1032
('domestic', 20, 20, 15.83), ('domestic', 20, 30, 21.88),
1033
('domestic', 23, 2, 18.8), ('domestic', 23, 5, 20.8),
1034
('domestic', 23, 10, 24.8), ('domestic', 23, 20, 27.8),
1035
('domestic', 23, 30, 30.8), ('domestic', 24, 2, 21.1405),
1036
('domestic', 24, 5, 22.3705), ('domestic', 24, 10, 25.0905),
1037
('domestic', 24, 20, 29.7705), ('domestic', 24, 30, 35.9605),
1038
('domestic', 17, 2, 7.2), ('domestic', 17, 5, 8.43),
1039
('domestic', 17, 10, 11.15), ('domestic', 17, 20, 15.83),
1040
('domestic', 17, 30, 22.02), ('domestic', 18, 2, 8.2),
1041
('domestic', 18, 5, 9.43), ('domestic', 18, 10, 12.15),
1042
('domestic', 18, 20, 16.83), ('domestic', 18, 30, 23.02),
1043
('domestic', 28, 2, 17), ('domestic', 28, 5, 19),
1044
('domestic', 28, 10, 22), ('domestic', 28, 20, 28),
1045
('domestic', 28, 30, 35), ('domestic', 29, 30, 29.5),
1046
('foreign', 25, 200, 0), ('domestic', 3, 100, 59),
1047
('foreign', 10, 30, 0), ('foreign', 22, 0, 0),
1048
('foreign', 11, 30, 0), ('foreign', 12, 30, 0),
1049
('all', 1, 10000, 0), ('all', 2, 10000, 0),
1050
('domestic', 9, 10000, 0), ('domestic', 4, 500, 0),
1051
('domestic', 5, 500, 0), ('domestic', 6, 500, 0),
1052
('domestic', 7, 500, 0), ('domestic', 8, 500, 0),
1053
('domestic', 21, 3.9, 10.8), ('domestic', 21, 4.9, 12.2),
1054
('domestic', 21, 9.9, 15.3), ('domestic', 21, 19.9, 20.6),
1055
('domestic', 21, 30, 28.1), ('plfcz1', 16, 0.5, 19),
1056
('plfcz2', 16, 0.5, 25), ( 'ppfcz2', 15, 16, 76.5),
1057
( 'ppfcz2', 15, 15, 75.5), ( 'ppfcz2', 15, 14, 73.5),
1058
( 'ppfcz2', 15, 13, 71.5), ( 'ppfcz2', 15, 12, 69.5),
1059
( 'ppfcz2', 15, 11, 67.5), ( 'ppfcz2', 15, 10, 65.5),
1060
( 'ppfcz2', 15, 9, 62.5), ( 'ppfcz2', 15, 8, 59.5),
1061
( 'ppfcz2', 15, 7, 56.5), ( 'ppfcz2', 15, 6, 53.5),
1062
( 'ppfcz2', 15, 5, 50.5), ( 'ppfcz2', 15, 4, 46.5),
1063
( 'ppfcz2', 15, 3, 42.5), ( 'ppfcz2', 15, 2, 38.5),
1064
('ppfcz1', 15, 2, 33.5), ('ppfcz1', 15, 3, 36.5),
1065
('ppfcz1', 15, 4, 39.5), ('ppfcz1', 15, 5, 41.5),
1066
('ppfcz1', 15, 6, 42.5), ('ppfcz1', 15, 7, 43.5),
1067
('ppfcz1', 15, 8, 44.5), ('ppfcz1', 15, 9, 45.5),
1068
('ppfcz1', 15, 10, 46.5), ('ppfcz1', 15, 11, 47.5),
1069
( 'ppfcz1', 15, 12, 48.5), ( 'ppfcz1', 15, 13, 49.5), ( 'ppfcz1', 15, 14, 50.5),
1070
( 'ppfcz1', 15, 15, 51.5), ( 'ppfcz1', 15, 16, 52.5), ( 'ppfcz1', 15, 17, 53.5),
1071
( 'ppfcz1', 15, 18, 54.5), ( 'ppfcz1', 15, 19, 55.5), ( 'ppfcz1', 15, 20, 56.5),
1072
( 'ppfcz1', 15, 21, 57.5), ( 'ppfcz1', 15, 22, 58.5), ( 'ppfcz1', 15, 23, 59.5),
1073
( 'ppfcz1', 15, 24, 60.5), ( 'ppfcz1', 15, 25, 61.5), ( 'ppfcz1', 15, 26, 62.5),
1074
( 'ppfcz1', 15, 27, 63.5), ( 'ppfcz1', 15, 28, 64.5), ( 'ppfcz1', 15, 29, 65.5),
1075
( 'ppfcz1', 15, 30, 66.5), ( 'ppfcz2', 15, 17, 77.5), ( 'ppfcz2', 15, 18, 78.5),
1076
( 'ppfcz2', 15, 19, 79.5), ( 'ppfcz2', 15, 20, 80.5), ( 'ppfcz2', 15, 21, 81.5),
1077
( 'ppfcz2', 15, 22, 82.5), ( 'ppfcz2', 15, 23, 83.5), ( 'ppfcz2', 15, 24, 84.5),
1078
( 'ppfcz2', 15, 25, 85.5), ( 'ppfcz2', 15, 26, 86.5), ( 'ppfcz2', 15, 27, 87.5),
1079
( 'ppfcz2', 15, 28, 88.5), ( 'ppfcz2', 15, 29, 89.5), ( 'ppfcz2', 15, 30, 90.5),
1080
( 'ppfcz3', 15, 2, 39.5), ( 'ppfcz3', 15, 3, 45.5), ( 'ppfcz3', 15, 4, 51.5),
1081
( 'ppfcz3', 15, 5, 57.5), ( 'ppfcz3', 15, 6, 63.5), ( 'ppfcz3', 15, 7, 69.5),
1082
( 'ppfcz3', 15, 8, 75.5), ( 'ppfcz3', 15, 9, 81.5), ( 'ppfcz3', 15, 10, 87.5),
1083
( 'ppfcz3', 15, 11, 93.5), ( 'ppfcz3', 15, 12, 99.5), ( 'ppfcz3', 15, 13, 105.5),
1084
( 'ppfcz3', 15, 14, 111.5), ( 'ppfcz3', 15, 15, 117.5), ( 'ppfcz3', 15, 16, 122.5),
1085
( 'ppfcz3', 15, 17, 127.5), ( 'ppfcz3', 15, 18, 132.5), ( 'ppfcz3', 15, 19, 137.5),
1086
( 'ppfcz3', 15, 20, 142.5), ( 'ppfcz3', 15, 21, 146.5), ( 'ppfcz3', 15, 22, 150.5),
1087
( 'ppfcz3', 15, 23, 154.5), ( 'ppfcz3', 15, 24, 158.5), ( 'ppfcz3', 15, 25, 162.5),
1088
( 'ppfcz3', 15, 26, 166.5), ( 'ppfcz3', 15, 27, 170.5), ( 'ppfcz3', 15, 28, 174.5),
1089
( 'ppfcz3', 15, 29, 178.5), ( 'ppfcz3', 15, 30, 182.5), ( 'ppfcz4', 15, 2, 44.5),
1090
( 'ppfcz4', 15, 3, 51.5), ( 'ppfcz4', 15, 4, 58.5), ( 'ppfcz4', 15, 5, 65.5),
1091
( 'ppfcz4', 15, 6, 72.5), ( 'ppfcz4', 15, 7, 79.5), ( 'ppfcz4', 15, 8, 86.5),
1092
( 'ppfcz4', 15, 9, 93.5), ( 'ppfcz4', 15, 10, 100.5), ( 'ppfcz4', 15, 11, 105.5),
1093
( 'ppfcz4', 15, 12, 110.5), ( 'ppfcz4', 15, 13, 115.5), ( 'ppfcz4', 15, 14, 120.5),
1094
( 'ppfcz4', 15, 15, 125.5), ( 'ppfcz4', 15, 16, 130.5), ( 'ppfcz4', 15, 17, 135.5),
1095
( 'ppfcz4', 15, 18, 140.5), ( 'ppfcz4', 15, 19, 145.5), ( 'ppfcz4', 15, 20, 150.5),
1096
( 'ppfcz4', 15, 21, 154.5), ( 'ppfcz4', 15, 22, 158.5), ( 'ppfcz4', 15, 23, 162.5),
1097
( 'ppfcz4', 15, 24, 166.5), ( 'ppfcz4', 15, 25, 170.5), ( 'ppfcz4', 15, 26, 174.5),
1098
( 'ppfcz4', 15, 27, 178.5), ( 'ppfcz4', 15, 28, 182.5), ( 'ppfcz4', 15, 29, 186.5),
1099
( 'ppfcz4', 15, 30, 190.5), ( 'ppfcz5', 15, 2, 48.5), ( 'ppfcz5', 15, 3, 56.5),
1100
( 'ppfcz5', 15, 4, 64.5), ( 'ppfcz5', 15, 5, 72.5), ( 'ppfcz5', 15, 6, 80.5),
1101
( 'ppfcz5', 15, 7, 88.5), ( 'ppfcz5', 15, 8, 96.5), ( 'ppfcz5', 15, 9, 104.5),
1102
( 'ppfcz5', 15, 10, 112.5), ( 'ppfcz5', 15, 11, 119.5), ( 'ppfcz5', 15, 12, 126.5),
1103
( 'ppfcz5', 15, 13, 133.5), ( 'ppfcz5', 15, 14, 140.5), ( 'ppfcz5', 15, 15, 147.5),
1104
( 'ppfcz5', 15, 16, 153.5), ( 'ppfcz5', 15, 17, 161.5), ( 'ppfcz5', 15, 18, 167.5),
1105
( 'ppfcz5', 15, 19, 173.5), ( 'ppfcz5', 15, 20, 179.5), ( 'ppfcz5', 15, 21, 185.5),
1106
( 'ppfcz5', 15, 22, 191.5), ( 'ppfcz5', 15, 23, 197.5), ( 'ppfcz5', 15, 24, 203.5),
1107
( 'ppfcz5', 15, 25, 207.5), ( 'ppfcz5', 15, 26, 212.5), ( 'ppfcz5', 15, 27, 217.5),
1108
( 'ppfcz5', 15, 28, 222.5), ( 'ppfcz5', 15, 29, 227.5), ( 'ppfcz5', 15, 30, 232.5),
1109
( 'ppfcz1', 14, 2, 37.5), ( 'ppfcz1', 14, 3, 41.5), ( 'ppfcz1', 14, 4, 45.5),
1110
( 'ppfcz1', 14, 5, 48.5), ( 'ppfcz1', 14, 6, 52.5), ( 'ppfcz1', 14, 7, 55.5),
1111
( 'ppfcz1', 14, 8, 57.5), ( 'ppfcz1', 14, 9, 59.5), ( 'ppfcz1', 14, 10, 61.5),
1112
( 'ppfcz1', 14, 11, 62.5), ( 'ppfcz1', 14, 12, 63.5), ( 'ppfcz1', 14, 13, 64.5),
1113
( 'ppfcz1', 14, 14, 65.5), ( 'ppfcz1', 14, 15, 66.5), ( 'ppfcz1', 14, 16, 67.5),
1114
( 'ppfcz1', 14, 17, 68.5), ( 'ppfcz1', 14, 18, 69.5), ( 'ppfcz1', 14, 19, 70.5),
1115
( 'ppfcz1', 14, 20, 71.5), ( 'ppfcz1', 14, 21, 72.5), ( 'ppfcz1', 14, 22, 73.5),
1116
( 'ppfcz1', 14, 23, 74.5), ( 'ppfcz1', 14, 24, 75.5), ( 'ppfcz1', 14, 25, 76.5),
1117
( 'ppfcz1', 14, 26, 77.5), ( 'ppfcz1', 14, 27, 78.5), ( 'ppfcz1', 14, 28, 79.5),
1118
( 'ppfcz1', 14, 29, 80.5), ( 'ppfcz1', 14, 30, 81.5), ( 'ppfcz2', 14, 2, 43.5),
1119
( 'ppfcz2', 14, 3, 48.5), ( 'ppfcz2', 14, 4, 53.5), ( 'ppfcz2', 14, 5, 57.5),
1120
( 'ppfcz2', 14, 6, 61.5), ( 'ppfcz2', 14, 7, 65.5), ( 'ppfcz2', 14, 8, 69.5),
1121
( 'ppfcz2', 14, 9, 73.5), ( 'ppfcz2', 14, 10, 77.5), ( 'ppfcz2', 14, 11, 80.5),
1122
( 'ppfcz2', 14, 12, 83.5), ( 'ppfcz2', 14, 13, 86.5), ( 'ppfcz2', 14, 14, 89.5),
1123
( 'ppfcz2', 14, 15, 92.5), ( 'ppfcz2', 14, 16, 94.5), ( 'ppfcz2', 14, 17, 96.5),
1124
( 'ppfcz2', 14, 18, 98.5), ( 'ppfcz2', 14, 19, 99.5), ( 'ppfcz2', 14, 20, 100.5),
1125
( 'ppfcz2', 14, 21, 101.5), ( 'ppfcz2', 14, 22, 102.5), ( 'ppfcz2', 14, 23, 103.5),
1126
( 'ppfcz2', 14, 24, 104.5), ( 'ppfcz2', 14, 25, 105.5), ( 'ppfcz2', 14, 26, 106.5),
1127
( 'ppfcz2', 14, 27, 107.5), ( 'ppfcz2', 14, 28, 108.5), ( 'ppfcz2', 14, 29, 109.5),
1128
( 'ppfcz2', 14, 30, 110.5), ( 'ppfcz3', 14, 2, 47.5), ( 'ppfcz3', 14, 3, 56.5),
1129
( 'ppfcz3', 14, 4, 67.5), ( 'ppfcz3', 14, 5, 78.5), ( 'ppfcz3', 14, 6, 87.5),
1130
( 'ppfcz3', 14, 7, 96.5), ( 'ppfcz3', 14, 8, 105.5), ( 'ppfcz3', 14, 9, 114.5),
1131
( 'ppfcz3', 14, 10, 123.5), ( 'ppfcz3', 14, 11, 131.5), ( 'ppfcz3', 14, 12, 139.5),
1132
( 'ppfcz3', 14, 13, 147.5), ( 'ppfcz3', 14, 14, 155.5), ( 'ppfcz3', 14, 15, 163.5),
1133
( 'ppfcz3', 14, 16, 171.5), ( 'ppfcz3', 14, 17, 179.5), ( 'ppfcz3', 14, 18, 187.5),
1134
( 'ppfcz3', 14, 19, 195.5), ( 'ppfcz3', 14, 20, 203.5), ( 'ppfcz3', 14, 21, 210.5),
1135
( 'ppfcz3', 14, 22, 217.5), ( 'ppfcz3', 14, 23, 224.5), ( 'ppfcz3', 14, 24, 231.5),
1136
( 'ppfcz3', 14, 25, 238.5), ( 'ppfcz3', 14, 26, 245.5), ( 'ppfcz3', 14, 27, 252.5),
1137
( 'ppfcz3', 14, 28, 259.5), ( 'ppfcz3', 14, 29, 266.5), ( 'ppfcz3', 14, 30, 273.5),
1138
( 'ppfcz4', 14, 2, 54.5), ( 'ppfcz4', 14, 3, 68.5), ( 'ppfcz4', 14, 4, 81.5),
1139
( 'ppfcz4', 14, 5, 95.5), ( 'ppfcz4', 14, 6, 108.5), ( 'ppfcz4', 14, 7, 121.5),
1140
( 'ppfcz4', 14, 8, 134.5), ( 'ppfcz4', 14, 9, 147.5), ( 'ppfcz4', 14, 10, 160.5),
1141
( 'ppfcz4', 14, 11, 168.5), ( 'ppfcz4', 14, 12, 178.5), ( 'ppfcz4', 14, 13, 188.5),
1142
( 'ppfcz4', 14, 14, 198.5), ( 'ppfcz4', 14, 15, 208.5), ( 'ppfcz4', 14, 16, 216.5),
1143
( 'ppfcz4', 14, 17, 224.5), ( 'ppfcz4', 14, 18, 232.5), ( 'ppfcz4', 14, 19, 240.5),
1144
( 'ppfcz4', 14, 20, 248.5), ( 'ppfcz4', 14, 21, 256.5), ( 'ppfcz4', 14, 22, 264.5),
1145
( 'ppfcz4', 14, 23, 272.5), ( 'ppfcz4', 14, 24, 280.5), ( 'ppfcz4', 14, 25, 288.5),
1146
( 'ppfcz4', 14, 26, 296.5), ( 'ppfcz4', 14, 27, 304.5), ( 'ppfcz4', 14, 28, 312.5),
1147
( 'ppfcz4', 14, 29, 320.5), ( 'ppfcz4', 14, 30, 328.5), ( 'ppfcz5', 14, 2, 66.5),
1148
( 'ppfcz5', 14, 3, 84.5), ( 'ppfcz5', 14, 4, 102.5), ( 'ppfcz5', 14, 5, 120.5),
1149
( 'ppfcz5', 14, 6, 137.5), ( 'ppfcz5', 14, 7, 154.5), ( 'ppfcz5', 14, 8, 171.5),
1150
( 'ppfcz5', 14, 9, 188.5), ( 'ppfcz5', 14, 10, 205.5), ( 'ppfcz5', 14, 11, 220.5),
1151
( 'ppfcz5', 14, 12, 235.5), ( 'ppfcz5', 14, 13, 250.5), ( 'ppfcz5', 14, 14, 265.5),
1152
( 'ppfcz5', 14, 15, 280.5), ( 'ppfcz5', 14, 16, 295.5), ( 'ppfcz5', 14, 17, 310.5),
1153
( 'ppfcz5', 14, 18, 325.5), ( 'ppfcz5', 14, 19, 340.5), ( 'ppfcz5', 14, 20, 355.5),
1154
( 'ppfcz5', 14, 21, 368.5), ( 'ppfcz5', 14, 22, 381.5), ( 'ppfcz5', 14, 23, 394.5),
1155
( 'ppfcz5', 14, 24, 407.5), ( 'ppfcz5', 14, 25, 420.5), ( 'ppfcz5', 14, 26, 433.5),
1156
( 'ppfcz5', 14, 27, 446.5), ( 'ppfcz5', 14, 28, 459.5), ( 'ppfcz5', 14, 29, 472.5),
1157
( 'ppfcz5', 14, 30, 485.5), ( 'ppfcz1', 30, 0.5, 56.5), ( 'ppfcz1', 30, 1, 63.5),
1158
( 'ppfcz1', 30, 1.5, 69.5), ( 'ppfcz1', 30, 2, 75.5), ( 'ppfcz1', 30, 2.5, 80.5),
1159
( 'ppfcz1', 30, 3, 86.5), ( 'ppfcz1', 30, 3.5, 92.5), ( 'ppfcz1', 30, 4, 99.5),
1160
( 'ppfcz1', 30, 4.5, 105.5), ( 'ppfcz1', 30, 5, 111.5), ( 'ppfcz1', 30, 6, 118.5),
1161
( 'ppfcz1', 30, 7, 126.5), ( 'ppfcz1', 30, 8, 133.5), ( 'ppfcz1', 30, 9, 141.5),
1162
( 'ppfcz1', 30, 10, 148.5), ( 'ppfcz1', 30, 11, 156.5), ( 'ppfcz1', 30, 12, 163.5),
1163
( 'ppfcz1', 30, 13, 171.5), ( 'ppfcz1', 30, 14, 178.5), ( 'ppfcz1', 30, 15, 186.5),
1164
( 'ppfcz1', 30, 16, 193.5), ( 'ppfcz1', 30, 17, 201.5), ( 'ppfcz1', 30, 18, 209.5),
1165
( 'ppfcz1', 30, 19, 216.5), ( 'ppfcz1', 30, 20, 224.5), ( 'ppfcz1', 30, 21, 231.5),
1166
( 'ppfcz1', 30, 22, 239.5), ( 'ppfcz1', 30, 23, 246.5), ( 'ppfcz1', 30, 24, 254.5),
1167
( 'ppfcz1', 30, 25, 261.5), ( 'ppfcz1', 30, 26, 269.5), ( 'ppfcz1', 30, 27, 276.5),
1168
( 'ppfcz1', 30, 28, 284.5), ( 'ppfcz1', 30, 29, 291.5), ( 'ppfcz1', 30, 30, 299.5),
1169
( 'ppfcz2', 30, 0.5, 61.5), ( 'ppfcz2', 30, 1, 65.5), ( 'ppfcz2', 30, 1.5, 75.5),
1170
( 'ppfcz2', 30, 2, 80.5), ( 'ppfcz2', 30, 2.5, 86.5), ( 'ppfcz2', 30, 3, 99.5),
1171
( 'ppfcz2', 30, 3.5, 109.5), ( 'ppfcz2', 30, 4, 113.5), ( 'ppfcz2', 30, 4.5, 121.5),
1172
( 'ppfcz2', 30, 5, 129.5), ( 'ppfcz2', 30, 6, 139.5), ( 'ppfcz2', 30, 7, 149.5),
1173
( 'ppfcz2', 30, 8, 159.5), ( 'ppfcz2', 30, 9, 169.5), ( 'ppfcz2', 30, 10, 180.5),
1174
( 'ppfcz2', 30, 11, 189.5), ( 'ppfcz2', 30, 12, 199.5), ( 'ppfcz2', 30, 13, 210.5),
1175
( 'ppfcz2', 30, 14, 219.5), ( 'ppfcz2', 30, 15, 229.5), ( 'ppfcz2', 30, 16, 240.5),
1176
( 'ppfcz2', 30, 17, 249.5), ( 'ppfcz2', 30, 18, 259.5), ( 'ppfcz2', 30, 19, 270.5),
1177
( 'ppfcz2', 30, 20, 280.5), ( 'ppfcz2', 30, 21, 289.5), ( 'ppfcz2', 30, 22, 300.5),
1178
( 'ppfcz2', 30, 23, 310.5), ( 'ppfcz2', 30, 24, 320.5), ( 'ppfcz2', 30, 25, 330.5),
1179
( 'ppfcz2', 30, 26, 340.5), ( 'ppfcz2', 30, 27, 350.5), ( 'ppfcz2', 30, 28, 360.5),
1180
( 'ppfcz2', 30, 29, 370.5), ( 'ppfcz2', 30, 30, 381.5), ( 'ppfcz3', 30, 0.5, 74.5),
1181
( 'ppfcz3', 30, 1, 83.5), ( 'ppfcz3', 30, 1.5, 90.5), ( 'ppfcz3', 30, 2, 99.5),
1182
( 'ppfcz3', 30, 2.5, 107.5), ( 'ppfcz3', 30, 3, 114.5), ( 'ppfcz3', 30, 3.5, 122.5),
1183
( 'ppfcz3', 30, 4, 130.5), ( 'ppfcz3', 30, 4.5, 140.5), ( 'ppfcz3', 30, 5, 147.5),
1184
( 'ppfcz3', 30, 6, 162.5), ( 'ppfcz3', 30, 7, 174.5), ( 'ppfcz3', 30, 8, 188.5),
1185
( 'ppfcz3', 30, 9, 201.5), ( 'ppfcz3', 30, 10, 213.5), ( 'ppfcz3', 30, 11, 227.5),
1186
( 'ppfcz3', 30, 12, 240.5), ( 'ppfcz3', 30, 13, 252.5), ( 'ppfcz3', 30, 14, 266.5),
1187
( 'ppfcz3', 30, 15, 278.5), ( 'ppfcz3', 30, 16, 290.5), ( 'ppfcz3', 30, 17, 304.5),
1188
( 'ppfcz3', 30, 18, 317.5), ( 'ppfcz3', 30, 19, 330.5), ( 'ppfcz3', 30, 20, 343.5),
1189
( 'ppfcz3', 30, 21, 354.5), ( 'ppfcz3', 30, 22, 363.5), ( 'ppfcz3', 30, 23, 375.5),
1190
( 'ppfcz3', 30, 24, 385.5), ( 'ppfcz3', 30, 25, 396.5), ( 'ppfcz3', 30, 26, 405.5),
1191
( 'ppfcz3', 30, 27, 417.5), ( 'ppfcz3', 30, 28, 428.5), ( 'ppfcz3', 30, 29, 438.5),
1192
( 'ppfcz3', 30, 30, 448.5), ( 'ppfcz4', 30, 0.5, 90.5), ( 'ppfcz4', 30, 1, 104.5),
1193
( 'ppfcz4', 30, 1.5, 118.5), ( 'ppfcz4', 30, 2, 134.5), ( 'ppfcz4', 30, 2.5, 146.5),
1194
( 'ppfcz4', 30, 3, 163.5), ( 'ppfcz4', 30, 3.5, 179.5), ( 'ppfcz4', 30, 4, 195.5),
1195
( 'ppfcz4', 30, 4.5, 211.5), ( 'ppfcz4', 30, 5, 232.5), ( 'ppfcz4', 30, 6, 257.5),
1196
( 'ppfcz4', 30, 7, 278.5), ( 'ppfcz4', 30, 8, 300.5), ( 'ppfcz4', 30, 9, 321.5),
1197
( 'ppfcz4', 30, 10, 343.5), ( 'ppfcz4', 30, 11, 364.5), ( 'ppfcz4', 30, 12, 386.5),
1198
( 'ppfcz4', 30, 13, 407.5), ( 'ppfcz4', 30, 14, 429.5), ( 'ppfcz4', 30, 15, 450.5),
1199
( 'ppfcz4', 30, 16, 472.5), ( 'ppfcz4', 30, 17, 493.5), ( 'ppfcz4', 30, 18, 515.5),
1200
( 'ppfcz4', 30, 19, 536.5), ( 'ppfcz4', 30, 20, 558.5), ( 'ppfcz4', 30, 21, 579.5),
1201
( 'ppfcz4', 30, 22, 601.5), ( 'ppfcz4', 30, 23, 622.5), ( 'ppfcz4', 30, 24, 644.5),
1202
( 'ppfcz4', 30, 25, 665.5), ( 'ppfcz4', 30, 26, 687.5), ( 'ppfcz4', 30, 27, 708.5),
1203
( 'ppfcz4', 30, 28, 730.5), ( 'ppfcz4', 30, 29, 751.5), ( 'ppfcz4', 30, 30, 773.5),
1204
( 'ppfcz5', 30, 0.5, 97.5), ( 'ppfcz5', 30, 1, 114.5), ( 'ppfcz5', 30, 1.5, 131.5),
1205
( 'ppfcz5', 30, 2, 148.5), ( 'ppfcz5', 30, 2.5, 165.5), ( 'ppfcz5', 30, 3, 183.5),
1206
( 'ppfcz5', 30, 3.5, 200.5), ( 'ppfcz5', 30, 4, 221.5), ( 'ppfcz5', 30, 4.5, 243.5),
1207
( 'ppfcz5', 30, 5, 264.5), ( 'ppfcz5', 30, 6, 289.5), ( 'ppfcz5', 30, 7, 313.5),
1208
( 'ppfcz5', 30, 8, 336.5), ( 'ppfcz5', 30, 9, 360.5), ( 'ppfcz5', 30, 10, 384.5),
1209
( 'ppfcz5', 30, 11, 407.5), ( 'ppfcz5', 30, 12, 431.5), ( 'ppfcz5', 30, 13, 455.5),
1210
( 'ppfcz5', 30, 14, 478.5), ( 'ppfcz5', 30, 15, 502.5), ( 'ppfcz5', 30, 16, 526.5),
1211
( 'ppfcz5', 30, 17, 549.5), ( 'ppfcz5', 30, 18, 573.5), ( 'ppfcz5', 30, 19, 597.5),
1212
( 'ppfcz5', 30, 20, 620.5), ( 'ppfcz5', 30, 21, 644.5), ( 'ppfcz5', 30, 22, 668.5),
1213
( 'ppfcz5', 30, 23, 691.5), ( 'ppfcz5', 30, 24, 715.5), ( 'ppfcz5', 30, 25, 738.5),
1214
( 'ppfcz5', 30, 26, 762.5), ( 'ppfcz5', 30, 27, 786.5), ( 'ppfcz5', 30, 28, 809.5),
1215
( 'ppfcz5', 30, 29, 833.5), ( 'ppfcz5', 30, 30, 857.5), ( 'foreign', 13, 30, 0),
1216
( 'all', 32, 10000, 23.2342007434944);
1220
INSERT INTO t3 SELECT * FROM t1;
1222
SELECT d FROM t1, t2
1223
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1224
ORDER BY t2.c LIMIT 1;
1226
SELECT d FROM t3 AS t1, t2 AS t2
1227
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1228
ORDER BY t2.c LIMIT 1;
1230
DROP TABLE t1,t2,t3;
added
removed
tests/t/order_by.test
