1
drop table if exists t1,t2,t3,t4,t11;
2
drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa;
4
Period int DEFAULT '0' NOT NULL,
5
Varor_period int DEFAULT '0' NOT NULL
7
INSERT INTO t1 VALUES (9410,9412);
18
auto int not null auto_increment,
19
fld1 int DEFAULT '0' NOT NULL,
20
companynr int DEFAULT '0' NOT NULL,
21
fld3 char(30) DEFAULT '' NOT NULL,
22
fld4 char(35) DEFAULT '' NOT NULL,
23
fld5 char(35) DEFAULT '' NOT NULL,
24
fld6 char(4) DEFAULT '' NOT NULL,
29
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
32
select fld3 from t2 where fld3 like "%cultivation" ;
35
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
60
select fld3,companynr from t2 where companynr = 58 order by fld3;
85
select fld3 from t2 order by fld3 desc limit 10;
97
select fld3 from t2 order by fld3 desc limit 5;
104
select fld3 from t2 order by fld3 desc limit 5,5;
111
select t2.fld3 from t2 where fld3 = 'honeysuckle';
114
select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
117
select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
120
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
123
select t2.fld3 from t2 where fld3 LIKE 'h%le';
126
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
128
select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
130
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
134
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
139
select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';
147
select fld1 from t2 where fld1=250501 or fld1="250502";
151
select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
157
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
190
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
192
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
195
select fld1,fld3 from t2 where fld1 like "25050%";
202
select fld1,fld3 from t2 where fld1 like "25050_";
209
select distinct companynr from t2;
223
select distinct companynr from t2 order by companynr;
237
select distinct companynr from t2 order by companynr desc;
251
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";
259
select distinct fld3 from t2 where companynr = 34 order by fld3;
331
select distinct fld3 from t2 limit 10;
343
select distinct fld3 from t2 having fld3 like "A%" limit 10;
355
select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
419
select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10;
431
select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
443
select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
457
name char(32) not null,
458
companynr int not null,
464
create temporary table tmp engine = myisam select * from t3;
465
insert into t3 select * from tmp;
466
insert into tmp select * from t3;
467
insert into t3 select * from tmp;
468
insert into tmp select * from t3;
469
insert into t3 select * from tmp;
470
insert into tmp select * from t3;
471
insert into t3 select * from tmp;
472
insert into tmp select * from t3;
473
insert into t3 select * from tmp;
474
insert into tmp select * from t3;
475
insert into t3 select * from tmp;
476
insert into tmp select * from t3;
477
insert into t3 select * from tmp;
478
insert into tmp select * from t3;
479
insert into t3 select * from tmp;
480
insert into tmp select * from t3;
481
insert into t3 select * from tmp;
482
alter table t3 add t2nr int not null auto_increment primary key first;
484
SET SQL_BIG_TABLES=1;
485
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
488
abrogating abrogating
489
admonishing admonishing
494
analyzable analyzable
497
SET SQL_BIG_TABLES=0;
498
select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
499
concat(fld3," ",fld3)
501
abrogating abrogating
502
admonishing admonishing
507
analyzable analyzable
510
select distinct fld5 from t2 limit 10;
522
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
534
SET SQL_BIG_TABLES=1;
535
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
547
SET SQL_BIG_TABLES=0;
548
select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
549
fld3 repeat("a",length(fld3)) count(*)
553
congresswoman aaaaaaaaaaaaa 1
554
contrition aaaaaaaaaa 1
556
cultivation aaaaaaaaaaa 1
557
definiteness aaaaaaaaaaaa 1
558
demultiplex aaaaaaaaaaa 1
559
disappointing aaaaaaaaaaaaa 1
560
select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
561
companynr rtrim(space(512+companynr))
569
select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
571
select period from t1;
574
select period from t1 where period=1900;
576
select fld3,period from t1,t2 where fld1 = 011401 order by period;
579
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
582
select fld3,period from t2,t1 where companynr*10 = 37*10;
815
electroencephalography 9410
923
interrelationships 9410
1172
select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price;
1173
fld3 period price price2
1174
admonishing 1002 28357832 8723648
1175
analyzable 1002 28357832 8723648
1176
annihilates 1001 5987435 234724
1177
Antares 1002 28357832 8723648
1178
astound 1001 5987435 234724
1179
audiology 1001 5987435 234724
1180
Augustine 1002 28357832 8723648
1181
Baird 1002 28357832 8723648
1182
bewilderingly 1001 5987435 234724
1183
breaking 1001 5987435 234724
1184
Conley 1001 5987435 234724
1185
dentally 1002 28357832 8723648
1186
dissociate 1002 28357832 8723648
1187
elite 1001 5987435 234724
1188
eschew 1001 5987435 234724
1189
Eulerian 1001 5987435 234724
1190
flanking 1001 5987435 234724
1191
foldout 1002 28357832 8723648
1192
funereal 1002 28357832 8723648
1193
galling 1002 28357832 8723648
1194
Graves 1001 5987435 234724
1195
grazing 1001 5987435 234724
1196
groupings 1001 5987435 234724
1197
handgun 1001 5987435 234724
1198
humility 1002 28357832 8723648
1199
impulsive 1002 28357832 8723648
1200
inch 1001 5987435 234724
1201
intelligibility 1001 5987435 234724
1202
jarring 1001 5987435 234724
1203
lawgiver 1001 5987435 234724
1204
lectured 1002 28357832 8723648
1205
Merritt 1002 28357832 8723648
1206
neonatal 1001 5987435 234724
1207
offload 1002 28357832 8723648
1208
parters 1002 28357832 8723648
1209
pityingly 1002 28357832 8723648
1210
puddings 1002 28357832 8723648
1211
Punjab 1001 5987435 234724
1212
quitter 1002 28357832 8723648
1213
realtor 1001 5987435 234724
1214
relaxing 1001 5987435 234724
1215
repetitions 1001 5987435 234724
1216
resumes 1001 5987435 234724
1217
Romans 1002 28357832 8723648
1218
rusting 1001 5987435 234724
1219
scholastics 1001 5987435 234724
1220
skulking 1002 28357832 8723648
1221
stated 1002 28357832 8723648
1222
suites 1002 28357832 8723648
1223
sureties 1001 5987435 234724
1224
testicle 1002 28357832 8723648
1225
tinily 1002 28357832 8723648
1226
tragedies 1001 5987435 234724
1227
trimmings 1001 5987435 234724
1228
vacuuming 1001 5987435 234724
1229
ventilate 1001 5987435 234724
1230
wallet 1001 5987435 234724
1231
Weissmuller 1002 28357832 8723648
1232
Wotan 1002 28357832 8723648
1233
select t2.fld1,fld3,period,price,price2 from t2,t3 where t2.fld1>= 18201 and t2.fld1 <= 18811 and t2.fld1=t3.t2nr and period = 1001 and t2.companynr = 37;
1234
fld1 fld3 period price price2
1235
18201 relaxing 1001 5987435 234724
1236
18601 vacuuming 1001 5987435 234724
1237
18801 inch 1001 5987435 234724
1238
18811 repetitions 1001 5987435 234724
1240
companynr int NOT NULL default '0',
1241
companyname char(30) NOT NULL default '',
1242
PRIMARY KEY (companynr),
1243
UNIQUE KEY companyname(companyname)
1244
) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
1245
select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1246
companynr companyname
1259
select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1260
companynr companyname
1273
select * from t1,t1 t12;
1274
Period Varor_period Period Varor_period
1276
select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505;
1303
insert into t2 (fld1, companynr) values (999999,99);
1304
select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1305
companynr companyname
1307
select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1310
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1311
companynr companyname
1312
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1315
delete from t2 where fld1=999999;
1316
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1320
select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
1321
fld1 companynr fld3 period
1322
38008 37 reporters 1008
1323
38208 37 Selfridge 1008
1324
select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1325
fld1 companynr fld3 period
1326
38008 37 reporters 1008
1327
38208 37 Selfridge 1008
1328
select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1329
fld1 companynr fld3 period
1330
38008 37 reporters 1008
1331
38208 37 Selfridge 1008
1332
select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909);
1335
select period from t1 where ((period > 0 and period < 1) or (((period > 0 and period < 100) and (period > 10)) or (period > 10)) or (period > 0 and (period > 5 or period > 6)));
1338
select a.fld1 from t2 as a,t2 b where ((a.fld1 = 250501 and a.fld1=b.fld1) or a.fld1=250502 or a.fld1=250503 or (a.fld1=250505 and a.fld1<=b.fld1 and b.fld1>=a.fld1)) and a.fld1=b.fld1;
1344
select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1348
select fld1 from t2 where fld1 between 250502 and 250504;
1353
select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
1364
select count(*) from t1;
1367
select companynr,count(*),sum(fld1) from t2 group by companynr;
1368
companynr count(*) sum(fld1)
1381
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1388
select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1389
count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1390
70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1391
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1392
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1393
0 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
1394
29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026
1395
34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1396
select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1397
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1398
37 1 1 5987435 5987435 5987435 5987435.0000
1399
37 2 1 28357832 28357832 28357832 28357832.0000
1400
37 3 1 39654943 39654943 39654943 39654943.0000
1401
37 11 1 5987435 5987435 5987435 5987435.0000
1402
37 12 1 28357832 28357832 28357832 28357832.0000
1403
37 13 1 39654943 39654943 39654943 39654943.0000
1404
37 21 1 5987435 5987435 5987435 5987435.0000
1405
37 22 1 28357832 28357832 28357832 28357832.0000
1406
37 23 1 39654943 39654943 39654943 39654943.0000
1407
37 31 1 5987435 5987435 5987435 5987435.0000
1408
select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1409
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1410
37 1 1 5987435 5987435 5987435 5987435.0000
1411
37 2 1 28357832 28357832 28357832 28357832.0000
1412
37 3 1 39654943 39654943 39654943 39654943.0000
1413
37 11 1 5987435 5987435 5987435 5987435.0000
1414
37 12 1 28357832 28357832 28357832 28357832.0000
1415
37 13 1 39654943 39654943 39654943 39654943.0000
1416
37 21 1 5987435 5987435 5987435 5987435.0000
1417
37 22 1 28357832 28357832 28357832 28357832.0000
1418
37 23 1 39654943 39654943 39654943 39654943.0000
1419
37 31 1 5987435 5987435 5987435 5987435.0000
1420
select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
1421
companynr count(price) sum(price) min(price) max(price) avg(price)
1422
37 12543 309394878010 5987435 39654943 24666736.6667
1423
78 8362 414611089292 726498 98439034 49582766.0000
1424
101 4181 3489454238 834598 834598 834598.0000
1425
154 4181 4112197254950 983543950 983543950 983543950.0000
1426
311 4181 979599938 234298 234298 234298.0000
1427
447 4181 9929180954 2374834 2374834 2374834.0000
1428
512 4181 3288532102 786542 786542 786542.0000
1429
select distinct mod(companynr,10) from t4 group by companynr;
1440
select distinct 1 from t4 group by companynr;
1443
select count(distinct fld1) from t2;
1444
count(distinct fld1)
1446
select companynr,count(distinct fld1) from t2 group by companynr;
1447
companynr count(distinct fld1)
1460
select companynr,count(*) from t2 group by companynr;
1474
select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1475
companynr count(distinct concat(fld1,repeat(65,1000)))
1488
select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1489
companynr count(distinct concat(fld1,repeat(65,200)))
1502
select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1503
companynr count(distinct floor(fld1/100))
1516
select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
1517
companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))
1530
select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1533
select name,count(*) from t3 where name='cloakroom' group by name;
1536
select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1539
select count(*) from t3 where name='cloakroom' and price2=823742;
1542
select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1545
select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1554
select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1557
select companynr,companyname from t4 group by 1;
1558
companynr companyname
1571
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1572
companynr companyname count(*)
1585
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1588
select sum(Period)/count(*) from t1;
1589
sum(Period)/count(*)
1591
select companynr,count(price) as "count",sum(price) as "sum" ,abs(sum(price)/count(price)-avg(price)) as "diff",(0+count(price))*companynr as func from t3 group by companynr;
1592
companynr count sum diff func
1593
37 12543 309394878010 0.0000 464091
1594
78 8362 414611089292 0.0000 652236
1595
101 4181 3489454238 0.0000 422281
1596
154 4181 4112197254950 0.0000 643874
1597
311 4181 979599938 0.0000 1300291
1598
447 4181 9929180954 0.0000 1868907
1599
512 4181 3288532102 0.0000 2140672
1600
select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
1603
select companynr,count(*) from t2 group by companynr order by 2 desc;
1617
select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1625
select t2.fld4,t2.fld1,count(price),sum(price),min(price),max(price),avg(price) from t3,t2 where t3.companynr = 37 and t2.fld1 = t3.t2nr group by fld1,t2.fld4;
1626
fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)
1627
teethe 1 1 5987435 5987435 5987435 5987435.0000
1628
dreaded 11401 1 5987435 5987435 5987435 5987435.0000
1629
scholastics 11402 1 28357832 28357832 28357832 28357832.0000
1630
audiology 11403 1 39654943 39654943 39654943 39654943.0000
1631
wallet 11501 1 5987435 5987435 5987435 5987435.0000
1632
parters 11701 1 5987435 5987435 5987435 5987435.0000
1633
eschew 11702 1 28357832 28357832 28357832 28357832.0000
1634
quitter 11703 1 39654943 39654943 39654943 39654943.0000
1635
neat 12001 1 5987435 5987435 5987435 5987435.0000
1636
Steinberg 12003 1 39654943 39654943 39654943 39654943.0000
1637
balled 12301 1 5987435 5987435 5987435 5987435.0000
1638
persist 12302 1 28357832 28357832 28357832 28357832.0000
1639
attainments 12303 1 39654943 39654943 39654943 39654943.0000
1640
capably 12501 1 5987435 5987435 5987435 5987435.0000
1641
impulsive 12602 1 28357832 28357832 28357832 28357832.0000
1642
starlet 12603 1 39654943 39654943 39654943 39654943.0000
1643
featherweight 12701 1 5987435 5987435 5987435 5987435.0000
1644
pessimist 12702 1 28357832 28357832 28357832 28357832.0000
1645
daughter 12703 1 39654943 39654943 39654943 39654943.0000
1646
lawgiver 13601 1 5987435 5987435 5987435 5987435.0000
1647
stated 13602 1 28357832 28357832 28357832 28357832.0000
1648
readable 13603 1 39654943 39654943 39654943 39654943.0000
1649
testicle 13801 1 5987435 5987435 5987435 5987435.0000
1650
Parsifal 13802 1 28357832 28357832 28357832 28357832.0000
1651
leavings 13803 1 39654943 39654943 39654943 39654943.0000
1652
squeaking 13901 1 5987435 5987435 5987435 5987435.0000
1653
contrasted 16001 1 5987435 5987435 5987435 5987435.0000
1654
leftover 16201 1 5987435 5987435 5987435 5987435.0000
1655
whiteners 16202 1 28357832 28357832 28357832 28357832.0000
1656
erases 16301 1 5987435 5987435 5987435 5987435.0000
1657
Punjab 16302 1 28357832 28357832 28357832 28357832.0000
1658
Merritt 16303 1 39654943 39654943 39654943 39654943.0000
1659
sweetish 18001 1 5987435 5987435 5987435 5987435.0000
1660
dogging 18002 1 28357832 28357832 28357832 28357832.0000
1661
scornfully 18003 1 39654943 39654943 39654943 39654943.0000
1662
fetters 18012 1 28357832 28357832 28357832 28357832.0000
1663
bivalves 18013 1 39654943 39654943 39654943 39654943.0000
1664
skulking 18021 1 5987435 5987435 5987435 5987435.0000
1665
flint 18022 1 28357832 28357832 28357832 28357832.0000
1666
flopping 18023 1 39654943 39654943 39654943 39654943.0000
1667
Judas 18032 1 28357832 28357832 28357832 28357832.0000
1668
vacuuming 18033 1 39654943 39654943 39654943 39654943.0000
1669
medical 18041 1 5987435 5987435 5987435 5987435.0000
1670
bloodbath 18042 1 28357832 28357832 28357832 28357832.0000
1671
subschema 18043 1 39654943 39654943 39654943 39654943.0000
1672
interdependent 18051 1 5987435 5987435 5987435 5987435.0000
1673
Graves 18052 1 28357832 28357832 28357832 28357832.0000
1674
neonatal 18053 1 39654943 39654943 39654943 39654943.0000
1675
sorters 18061 1 5987435 5987435 5987435 5987435.0000
1676
epistle 18062 1 28357832 28357832 28357832 28357832.0000
1677
Conley 18101 1 5987435 5987435 5987435 5987435.0000
1678
lectured 18102 1 28357832 28357832 28357832 28357832.0000
1679
Abraham 18103 1 39654943 39654943 39654943 39654943.0000
1680
cage 18201 1 5987435 5987435 5987435 5987435.0000
1681
hushes 18202 1 28357832 28357832 28357832 28357832.0000
1682
Simla 18402 1 28357832 28357832 28357832 28357832.0000
1683
reporters 18403 1 39654943 39654943 39654943 39654943.0000
1684
coexist 18601 1 5987435 5987435 5987435 5987435.0000
1685
Beebe 18602 1 28357832 28357832 28357832 28357832.0000
1686
Taoism 18603 1 39654943 39654943 39654943 39654943.0000
1687
Connally 18801 1 5987435 5987435 5987435 5987435.0000
1688
fetched 18802 1 28357832 28357832 28357832 28357832.0000
1689
checkpoints 18803 1 39654943 39654943 39654943 39654943.0000
1690
gritty 18811 1 5987435 5987435 5987435 5987435.0000
1691
firearm 18812 1 28357832 28357832 28357832 28357832.0000
1692
minima 19101 1 5987435 5987435 5987435 5987435.0000
1693
Selfridge 19102 1 28357832 28357832 28357832 28357832.0000
1694
disable 19103 1 39654943 39654943 39654943 39654943.0000
1695
witchcraft 19201 1 5987435 5987435 5987435 5987435.0000
1696
betroth 30501 1 5987435 5987435 5987435 5987435.0000
1697
Manhattanize 30502 1 28357832 28357832 28357832 28357832.0000
1698
imprint 30503 1 39654943 39654943 39654943 39654943.0000
1699
swelling 31901 1 5987435 5987435 5987435 5987435.0000
1700
interrelationships 36001 1 5987435 5987435 5987435 5987435.0000
1701
riser 36002 1 28357832 28357832 28357832 28357832.0000
1702
bee 38001 1 5987435 5987435 5987435 5987435.0000
1703
kanji 38002 1 28357832 28357832 28357832 28357832.0000
1704
dental 38003 1 39654943 39654943 39654943 39654943.0000
1705
railway 38011 1 5987435 5987435 5987435 5987435.0000
1706
validate 38012 1 28357832 28357832 28357832 28357832.0000
1707
normalizes 38013 1 39654943 39654943 39654943 39654943.0000
1708
Kline 38101 1 5987435 5987435 5987435 5987435.0000
1709
Anatole 38102 1 28357832 28357832 28357832 28357832.0000
1710
partridges 38103 1 39654943 39654943 39654943 39654943.0000
1711
recruited 38201 1 5987435 5987435 5987435 5987435.0000
1712
dimensions 38202 1 28357832 28357832 28357832 28357832.0000
1713
Chicana 38203 1 39654943 39654943 39654943 39654943.0000
1714
select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
1715
companynr fld3 sum(price)
1720
512 descendants 786542
1723
512 Micronesia 786542
1726
select t2.companynr,count(*),min(fld3),max(fld3),sum(price),avg(price) from t2,t3 where t3.companynr >= 30 and t3.companynr <= 58 and t3.t2nr = t2.fld1 and 1+1=2 group by t2.companynr;
1727
companynr count(*) min(fld3) max(fld3) sum(price) avg(price)
1728
0 1 Omaha Omaha 5987435 5987435.0000
1729
36 1 dubbed dubbed 28357832 28357832.0000
1730
37 83 Abraham Wotan 1908978016 22999735.1325
1731
50 2 scribbled tapestry 68012775 34006387.5000
1732
select t3.companynr+0,t3.t2nr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 37 group by 1,t3.t2nr,fld3,fld3,fld3,fld3,fld3 order by fld1;
1733
t3.companynr+0 t2nr fld3 sum(price)
1735
37 11401 breaking 5987435
1736
37 11402 Romans 28357832
1737
37 11403 intercepted 39654943
1738
37 11501 bewilderingly 5987435
1739
37 11701 astound 5987435
1740
37 11702 admonishing 28357832
1741
37 11703 sumac 39654943
1742
37 12001 flanking 5987435
1743
37 12003 combed 39654943
1744
37 12301 Eulerian 5987435
1745
37 12302 dubbed 28357832
1746
37 12303 Kane 39654943
1747
37 12501 annihilates 5987435
1748
37 12602 Wotan 28357832
1749
37 12603 snatching 39654943
1750
37 12701 grazing 5987435
1751
37 12702 Baird 28357832
1752
37 12703 celery 39654943
1753
37 13601 handgun 5987435
1754
37 13602 foldout 28357832
1755
37 13603 mystic 39654943
1756
37 13801 intelligibility 5987435
1757
37 13802 Augustine 28357832
1758
37 13803 teethe 39654943
1759
37 13901 scholastics 5987435
1760
37 16001 audiology 5987435
1761
37 16201 wallet 5987435
1762
37 16202 parters 28357832
1763
37 16301 eschew 5987435
1764
37 16302 quitter 28357832
1765
37 16303 neat 39654943
1766
37 18001 jarring 5987435
1767
37 18002 tinily 28357832
1768
37 18003 balled 39654943
1769
37 18012 impulsive 28357832
1770
37 18013 starlet 39654943
1771
37 18021 lawgiver 5987435
1772
37 18022 stated 28357832
1773
37 18023 readable 39654943
1774
37 18032 testicle 28357832
1775
37 18033 Parsifal 39654943
1776
37 18041 Punjab 5987435
1777
37 18042 Merritt 28357832
1778
37 18043 Quixotism 39654943
1779
37 18051 sureties 5987435
1780
37 18052 puddings 28357832
1781
37 18053 tapestry 39654943
1782
37 18061 trimmings 5987435
1783
37 18062 humility 28357832
1784
37 18101 tragedies 5987435
1785
37 18102 skulking 28357832
1786
37 18103 flint 39654943
1787
37 18201 relaxing 5987435
1788
37 18202 offload 28357832
1789
37 18402 suites 28357832
1790
37 18403 lists 39654943
1791
37 18601 vacuuming 5987435
1792
37 18602 dentally 28357832
1793
37 18603 humanness 39654943
1794
37 18801 inch 5987435
1795
37 18802 Weissmuller 28357832
1796
37 18803 irresponsibly 39654943
1797
37 18811 repetitions 5987435
1798
37 18812 Antares 28357832
1799
37 19101 ventilate 5987435
1800
37 19102 pityingly 28357832
1801
37 19103 interdependent 39654943
1802
37 19201 Graves 5987435
1803
37 30501 neonatal 5987435
1804
37 30502 scribbled 28357832
1805
37 30503 chafe 39654943
1806
37 31901 realtor 5987435
1807
37 36001 elite 5987435
1808
37 36002 funereal 28357832
1809
37 38001 Conley 5987435
1810
37 38002 lectured 28357832
1811
37 38003 Abraham 39654943
1812
37 38011 groupings 5987435
1813
37 38012 dissociate 28357832
1814
37 38013 coexist 39654943
1815
37 38101 rusting 5987435
1816
37 38102 galling 28357832
1817
37 38103 obliterates 39654943
1818
37 38201 resumes 5987435
1819
37 38202 analyzable 28357832
1820
37 38203 terminator 39654943
1821
select sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1= t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008;
1824
select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1;
1827
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1831
select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1835
select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1842
select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1849
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
1850
companynr companyname count(*)
1858
select count(*) from t2;
1861
select count(*) from t2 where fld1 < 098024;
1864
select min(fld1) from t2 where fld1>= 098024;
1867
select max(fld1) from t2 where fld1>= 098024;
1870
select count(*) from t3 where price2=76234234;
1873
select count(*) from t3 where companynr=512 and price2=76234234;
1876
select min(fld1),max(fld1),count(*) from t2;
1877
min(fld1) max(fld1) count(*)
1879
select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
1882
select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
1883
count(*) min(t2nr) max(t2nr)
1885
select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
1907
select max(t2nr) from t3 where price=983543950;
1910
select t1.period from t3 = t1 limit 1;
1913
select t1.period from t1 as t1 limit 1;
1916
select t1.period as "Nuvarande period" from t1 as t1 limit 1;
1919
select period as ok_period from t1 limit 1;
1922
select period as ok_period from t1 group by ok_period limit 1;
1925
select 1+1 as summa from t1 group by summa limit 1;
1928
select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
1937
show tables from test like "s%";
1939
show tables from test like "t?";
1941
show full columns from t2;
1942
Field Type Collation Null Key Default Extra Privileges Comment
1943
auto int NULL NO PRI NULL auto_increment #
1944
fld1 int NULL NO UNI NULL #
1945
companynr int NULL NO NULL #
1946
fld3 varchar(30) utf8_general_ci NO MUL NULL #
1947
fld4 varchar(35) utf8_general_ci NO NULL #
1948
fld5 varchar(35) utf8_general_ci NO NULL #
1949
fld6 varchar(4) utf8_general_ci NO NULL #
1950
show full columns from t2 from test like 'f%';
1951
Field Type Collation Null Key Default Extra Privileges Comment
1952
fld1 int NULL NO UNI NULL #
1953
fld3 varchar(30) utf8_general_ci NO MUL NULL #
1954
fld4 varchar(35) utf8_general_ci NO NULL #
1955
fld5 varchar(35) utf8_general_ci NO NULL #
1956
fld6 varchar(4) utf8_general_ci NO NULL #
1957
show full columns from t2 from test like 's%';
1958
Field Type Collation Null Key Default Extra Privileges Comment
1959
drop table t4, t3, t2, t1;
1961
id bigint NOT NULL auto_increment,
1962
pseudo varchar(35) NOT NULL default '',
1964
UNIQUE KEY pseudo (pseudo)
1966
INSERT INTO t1 (pseudo) VALUES ('test');
1967
INSERT INTO t1 (pseudo) VALUES ('test1');
1968
SELECT 1 as rnd1 from t1 where rand() > 2;
1971
CREATE TABLE t1 (gvid int default NULL, hmid int default NULL, volid int default NULL, mmid int default NULL, hdid int default NULL, fsid int default NULL, ctid int default NULL, dtid int default NULL, cost int default NULL, performance int default NULL, serialnumber bigint default NULL, monitored int default '1', removed int default '0', target int default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
1972
INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);
1973
CREATE TABLE t2 ( hmid int default NULL, volid int default NULL, sampletid int default NULL, sampletime datetime default NULL, samplevalue bigint default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;
1974
INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);
1975
SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;
1976
gvid the_success the_fail the_size the_time
1978
Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1
1979
Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1
1980
SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;
1981
gvid the_success the_fail the_size the_time
1983
create table t1 ( A_Id bigint NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint NOT NULL default '0', A_UpdateSerial int NOT NULL default '0', other_types bigint NOT NULL default '0', wss_type bigint NOT NULL default '0');
1984
INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);
1985
select wss_type from t1 where wss_type ='102935229216544106';
1987
select wss_type from t1 where wss_type ='102935229216544105';
1989
select wss_type from t1 where wss_type ='102935229216544104';
1991
select wss_type from t1 where wss_type ='102935229216544093';
1994
select wss_type from t1 where wss_type =102935229216544093;
1998
select 1+2,"aaaa",3.13*2.0 into @a,@b,@c;
2008
create table t1 (a int not null auto_increment primary key);
2009
insert into t1 values ();
2010
insert into t1 values ();
2011
insert into t1 values ();
2012
select * from (t1 as t2 left join t1 as t3 using (a)), t1;
2023
select * from t1, (t1 as t2 left join t1 as t3 using (a));
2034
select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;
2045
select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));
2056
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
2064
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2072
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
2077
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2082
select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
2090
select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2099
select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a );
2104
select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2109
select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
2114
select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
2119
select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
2128
select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2136
select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
2141
select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2146
select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1;
2151
select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));
2156
select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
2161
select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
2167
CREATE TABLE t1 ( aa char(2), id int NOT NULL auto_increment, t2_id int NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;
2168
INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);
2169
CREATE TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;
2170
INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);
2171
select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5;
2179
create table t1 (id1 int NOT NULL);
2180
create table t2 (id2 int NOT NULL);
2181
create table t3 (id3 int NOT NULL);
2182
create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4));
2183
insert into t1 values (1);
2184
insert into t1 values (2);
2185
insert into t2 values (1);
2186
insert into t4 values (1,1);
2187
select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
2188
left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
2189
id1 id2 id3 id4 id44
2191
drop table t1,t2,t3,t4;
2192
create table t1(s varchar(10) not null);
2193
create table t2(s varchar(10) not null primary key);
2194
create table t3(s varchar(10) not null primary key);
2195
insert into t1 values ('one\t'), ('two\t');
2196
insert into t2 values ('one\r'), ('two\t');
2197
insert into t3 values ('one '), ('two\t');
2198
select * from t1 where s = 'one';
2200
select * from t2 where s = 'one';
2202
select * from t3 where s = 'one';
2205
select * from t1,t2 where t1.s = t2.s;
2208
select * from t2,t3 where t2.s = t3.s;
2211
drop table t1, t2, t3;
2212
create table t1 (a integer, b integer, index(a), index(b));
2213
create table t2 (c integer, d integer, index(c), index(d));
2214
insert into t1 values (1,2), (2,2), (3,2), (4,2);
2215
insert into t2 values (1,3), (2,3), (3,4), (4,4);
2216
select * from t1 left join t2 on a=c where d in (4);
2220
select * from t1 left join t2 on a=c where d = 4;
2226
i int NOT NULL default '0',
2227
c char(10) NOT NULL default '',
2231
INSERT INTO t1 VALUES (1,'a');
2232
INSERT INTO t1 VALUES (2,'b');
2233
INSERT INTO t1 VALUES (3,'c');
2234
EXPLAIN SELECT i FROM t1 WHERE i=1;
2235
id select_type table type possible_keys key key_len ref rows Extra
2236
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
2238
CREATE TABLE t1 ( a BLOB, INDEX (a(20)) );
2239
CREATE TABLE t2 ( a BLOB, INDEX (a(20)) );
2240
INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
2241
INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
2242
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
2243
id select_type table type possible_keys key key_len ref rows Extra
2244
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2245
1 SIMPLE t2 ref a a 23 test.t1.a 2
2246
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;
2247
id select_type table type possible_keys key key_len ref rows Extra
2248
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2249
1 SIMPLE t2 ref a a 23 test.t1.a 2
2251
CREATE TABLE t1 ( city char(30) );
2252
INSERT INTO t1 VALUES ('London');
2253
INSERT INTO t1 VALUES ('Paris');
2254
SELECT * FROM t1 WHERE city='London';
2257
SELECT * FROM t1 WHERE city='london';
2260
EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london';
2261
id select_type table type possible_keys key key_len ref rows Extra
2262
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
2263
SELECT * FROM t1 WHERE city='London' AND city='london';
2266
EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2267
id select_type table type possible_keys key key_len ref rows Extra
2268
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
2269
SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2273
create table t1 (a int, b int);
2274
insert into t1 values (1,0), (1,1), (1,2);
2275
select a-b from t1 order by 1;
2280
select a-b , (a-b < 0) from t1 order by 1;
2285
select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;
2289
select a - b from t1 order by 1;
2295
create table t1 (a int);
2296
select all all * from t1;
2298
select distinct distinct * from t1;
2300
select all distinct * from t1;
2301
ERROR HY000: Incorrect usage of ALL and DISTINCT
2302
select distinct all * from t1;
2303
ERROR HY000: Incorrect usage of ALL and DISTINCT
2306
kunde_intern_id int NOT NULL default '0',
2307
kunde_id int NOT NULL default '0',
2308
FK_firma_id int NOT NULL default '0',
2309
aktuell enum('Ja','Nein') NOT NULL default 'Ja',
2310
vorname varchar(128) NOT NULL default '',
2311
nachname varchar(128) NOT NULL default '',
2312
geloescht enum('Ja','Nein') NOT NULL default 'Nein',
2313
firma varchar(128) NOT NULL default ''
2315
INSERT INTO t1 VALUES
2316
(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'),
2317
(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX');
2318
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1
2322
( '' != '' AND firma LIKE CONCAT('%', '', '%'))
2324
(vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2325
nachname LIKE CONCAT('%', '1Nachname', '%') AND
2326
'Vorname1' != '' AND 'xxxx' != '')
2330
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2334
kunde_id FK_firma_id aktuell vorname nachname geloescht
2335
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname,
2340
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2344
( '' != '' AND firma LIKE CONCAT('%', '', '%') )
2346
( vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2347
nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND
2352
kunde_id FK_firma_id aktuell vorname nachname geloescht
2353
SELECT COUNT(*) FROM t1 WHERE
2354
( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1))
2355
AND FK_firma_id = 2;
2359
CREATE TABLE t1 (b BIGINT NOT NULL, PRIMARY KEY (b));
2360
INSERT INTO t1 VALUES (0x4000000000000000);
2361
SELECT b FROM t1 WHERE b=0x4000000000000000;
2365
CREATE TABLE `t1` ( `gid` int default NULL, `uid` int default NULL);
2366
CREATE TABLE `t2` ( `ident` int default NULL, `level` char(16) default NULL);
2367
INSERT INTO `t2` VALUES (0,'READ');
2368
CREATE TABLE `t3` ( `id` int default NULL, `name` char(16) default NULL);
2369
INSERT INTO `t3` VALUES (1,'fs');
2370
select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);
2371
id name gid uid ident level
2372
1 fs NULL NULL 0 READ
2373
drop table t1,t2,t3;
2375
acct_id int NOT NULL default '0',
2376
profile_id int default NULL,
2377
UNIQUE KEY t1$acct_id (acct_id),
2378
KEY t1$profile_id (profile_id)
2380
INSERT INTO t1 VALUES (132,17),(133,18);
2382
profile_id int default NULL,
2383
queue_id int default NULL,
2384
seq int default NULL,
2385
KEY t2$queue_id (queue_id)
2387
INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);
2389
id int NOT NULL default '0',
2390
qtype int default NULL,
2391
seq int default NULL,
2392
warn_lvl int default NULL,
2393
crit_lvl int default NULL,
2394
rr1 int NOT NULL default '0',
2395
rr2 int default NULL,
2396
default_queue int NOT NULL default '0',
2397
KEY t3$qtype (qtype),
2400
INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0),
2401
(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0);
2402
SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q
2404
(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND
2405
(pq.queue_id = q.id) AND (q.rr1 <> 1);
2408
drop table t1,t2,t3;
2409
create table t1 (f1 int);
2410
insert into t1 values (1),(NULL);
2411
create table t2 (f2 int, f3 int, f4 int);
2412
create index idx1 on t2 (f4);
2413
insert into t2 values (1,2,3),(2,4,6);
2414
select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3)
2415
from t2 C where A.f4 = C.f4) or A.f3 IS NULL;
2420
create table t2 (a int);
2421
create index t2i on t2(a);
2422
insert into t2 values (0), (254), (255);
2423
select * from t2 where a > -1;
2429
CREATE TABLE t1 (a INT, b INT);
2430
(SELECT a, b AS c FROM t1) ORDER BY c+1;
2432
(SELECT a, b AS c FROM t1) ORDER BY b+1;
2434
SELECT a, b AS c FROM t1 ORDER BY c+1;
2436
SELECT a, b AS c FROM t1 ORDER BY b+1;
2439
create table t1(f1 int, f2 int);
2440
create table t2(f3 int);
2441
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1));
2443
select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1));
2445
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL));
2447
insert into t1 values(1,1),(2,null);
2448
insert into t2 values(2);
2449
select * from t1,t2 where f1=f3 and (f1,f2) = (2,null);
2451
select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null);
2455
create table t1 (f1 int not null auto_increment primary key, f2 varchar(10));
2456
create table t11 like t1;
2457
insert into t1 values(1,""),(2,"");
2458
show table status like 't1%';
2459
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
2460
t1 PBXT 10 Dynamic 2 60 X X X X X X X X utf8_general_ci NULL
2461
t11 PBXT 10 Dynamic 0 60 X X X X X X X X utf8_general_ci NULL
2462
select 123 as a from t1 where f1 is null;
2465
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );
2466
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
2467
CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT );
2468
INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3);
2469
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2470
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2477
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2478
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c;
2484
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2485
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c;
2492
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1
2493
WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2498
DROP TABLE IF EXISTS t1, t2;
2499
create table t1 (f1 int primary key, f2 int);
2500
create table t2 (f3 int, f4 int, primary key(f3,f4));
2501
insert into t1 values (1,1);
2502
insert into t2 values (1,1),(1,2);
2503
select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1;
2507
create table t1 (f1 int,f2 int);
2508
insert into t1 values(1,1);
2509
create table t2 (f3 int, f4 int, primary key(f3,f4));
2510
insert into t2 values(1,1);
2511
select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2));
2515
CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));
2516
insert into t1 values (1,0,0),(2,0,0);
2517
CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));
2518
insert into t2 values (1,'',''), (2,'','');
2519
CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
2520
insert into t3 values (1,1),(1,2);
2521
DROP TABLE t1,t2,t3;
2522
CREATE TABLE t1 (a int, INDEX idx(a));
2523
INSERT INTO t1 VALUES (2), (3), (1);
2524
EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);
2525
id select_type table type possible_keys key key_len ref rows Extra
2526
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
2527
EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);
2528
ERROR 42000: Key 'a' doesn't exist in table 't1'
2529
EXPLAIN SELECT * FROM t1 FORCE INDEX (a);
2530
ERROR 42000: Key 'a' doesn't exist in table 't1'
2532
CREATE TABLE t1 (a int, b int);
2533
INSERT INTO t1 VALUES (1,1), (2,1), (4,10);
2534
CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b));
2535
INSERT INTO t2 VALUES (1,NULL), (2,10);
2536
ALTER TABLE t1 ENABLE KEYS;
2538
Note 1031 Table storage engine for 't1' doesn't have this option
2539
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2540
id select_type table type possible_keys key key_len ref rows Extra
2541
1 SIMPLE t2 index b b 5 NULL 2 Using index
2542
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2543
SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2549
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2550
id select_type table type possible_keys key key_len ref rows Extra
2551
1 SIMPLE t2 index b b 5 NULL 2 Using index
2552
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2553
SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2559
DROP TABLE IF EXISTS t1,t2;
2560
CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1));
2561
CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));
2562
INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);
2563
INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);
2564
select max(key1) from t1 where key1 <= 0.6158;
2567
select max(key2) from t2 where key2 <= 1.6158;
2570
select min(key1) from t1 where key1 >= 0.3762;
2573
select min(key2) from t2 where key2 >= 1.3762;
2576
select max(key1), min(key2) from t1, t2
2577
where key1 <= 0.6158 and key2 >= 1.3762;
2580
select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
2583
select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
2587
CREATE TABLE t1 (i BIGINT NOT NULL);
2588
INSERT INTO t1 VALUES (10);
2589
SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;
2590
i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01')
2593
CREATE TABLE t1 (c0 int);
2594
CREATE TABLE t2 (c0 int);
2595
INSERT INTO t1 VALUES(@@connect_timeout);
2596
INSERT INTO t2 VALUES(@@connect_timeout);
2597
SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout);
2603
K2C4 varchar(4) collate utf8_bin NOT NULL default '',
2604
K4N4 varchar(4) collate utf8_bin NOT NULL default '0000',
2605
F2I4 int NOT NULL default '0'
2607
INSERT INTO t1 VALUES
2608
('W%RT', '0100', 1),
2609
('W-RT', '0100', 1),
2610
('WART', '0100', 1),
2611
('WART', '0200', 1),
2612
('WERT', '0100', 2),
2615
('W_RT', '0100', 2),
2616
('WaRT', '0100', 3),
2617
('WART', '0300', 3),
2618
('WRT' , '0400', 3),
2619
('WURM', '0500', 3),
2621
('WA%T', '0700', 4),
2622
('WA_T', '0800', 4);
2623
SELECT K2C4, K4N4, F2I4 FROM t1
2624
WHERE K2C4 = 'WART' AND
2625
(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200'));
2628
SELECT K2C4, K4N4, F2I4 FROM t1
2629
WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200');
2635
create table t1 (a int, b int);
2636
create table t2 like t1;
2637
select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1;
2639
select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1;
2641
select x.a, y.a, z.a from ( (t1 x inner join t2 y on x.a=y.a) inner join t2 z on y.a=z.a) WHERE x.a=1;
2644
create table t1 (s1 varchar(5));
2645
insert into t1 values ('Wall');
2646
select min(s1) from t1 group by s1 with rollup;
2651
create table t1 (s1 int) engine=myisam;
2652
insert into t1 values (0);
2653
select avg(distinct s1) from t1 group by s1 with rollup;
2658
create table t1 (s1 int);
2659
insert into t1 values (null),(1);
2660
select distinct avg(s1) as x from t1 group by s1 with rollup;
2665
CREATE TABLE t1 (a int);
2666
CREATE TABLE t2 (a int);
2667
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
2668
INSERT INTO t2 VALUES (2), (4), (6);
2669
SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2673
EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2674
id select_type table type possible_keys key key_len ref rows Extra
2675
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2676
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2677
EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a;
2678
id select_type table type possible_keys key key_len ref rows Extra
2679
1 SIMPLE t2 ALL NULL NULL NULL NULL 3
2680
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer
2682
select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
2683
x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0
2685
create table t1 (f1 varchar(6) default NULL, f2 int primary key not null);
2686
create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));
2687
insert into t1 values (" 2", 2);
2688
insert into t2 values (" 2", " one "),(" 2", " two ");
2689
select * from t1 left join t2 on f1 = f3;
2694
create table t1 (pk int primary key, b int);
2695
create table t2 (pk int primary key, c int);
2696
select pk from t1 inner join t2 using (pk);
2699
create table t1 (a int, t1_val int);
2700
create table t2 (b int, t2_val int);
2701
create table t3 (a int, b int);
2702
insert into t1 values (1,1),(2,2);
2703
insert into t2 values (1,1),(2,2),(3,3);
2704
insert into t3 values (1,1),(2,1),(3,1),(4,1);
2705
select * from t1 natural join t2 natural join t3;
2709
select * from t1 natural join t3 natural join t2;
2713
drop table t1, t2, t3;
2714
create table t1 (a char(1));
2715
create table t2 (a char(1));
2716
insert into t1 values ('a'),('b'),('c');
2717
insert into t2 values ('b'),('c'),('d');
2718
select a from t1 natural join t2;
2722
select * from t1 natural join t2 where a = 'b';
2726
CREATE TABLE t1 (`id` int);
2727
CREATE TABLE t2 (`id` int);
2728
CREATE TABLE t3 (`id` int);
2729
INSERT INTO t1 VALUES (1),(2),(3);
2730
INSERT INTO t2 VALUES (2);
2731
INSERT INTO t3 VALUES (3);
2732
SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
2733
ERROR 23000: Column 'id' in from clause is ambiguous
2734
SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);
2735
ERROR 23000: Column 'id' in from clause is ambiguous
2736
SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
2737
ERROR 23000: Column 'id' in from clause is ambiguous
2738
SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);
2739
ERROR 23000: Column 'id' in from clause is ambiguous
2740
drop table t1, t2, t3;
2741
create table t1 (a int,b int);
2742
create table t2 (a int,b int);
2743
insert into t1 values (1,10),(2,20),(3,30);
2744
insert into t2 values (1,10);
2745
select * from t1 inner join t2 using (A);
2748
select * from t1 inner join t2 using (a);
2752
create table t1 (a int, c int);
2753
create table t2 (b int);
2754
create table t3 (b int, a int);
2755
create table t4 (c int);
2756
insert into t1 values (1,1);
2757
insert into t2 values (1);
2758
insert into t3 values (1,1);
2759
insert into t4 values (1);
2760
select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
2763
select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
2764
ERROR 42S22: Unknown column 't1.a' in 'on clause'
2765
select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c);
2768
select * from t1 join t2 join t4 using (c);
2771
drop table t1, t2, t3, t4;
2772
create table t1(x int, y int);
2773
create table t2(x int, y int);
2774
create table t3(x int, primary key(x));
2775
insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6);
2776
insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6);
2777
insert into t3 values (1), (2), (3), (4), (5);
2778
select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y;
2788
drop table t1,t2,t3;
2789
create table t1 (id int not null default '0');
2790
insert into t1 values (123),(191),(192);
2791
create table t2 (id char(16) not null);
2792
insert into t2 values ('58013'),('58014'),('58015'),('58016');
2793
create table t3 (a_id int not null, b_id char(16));
2794
insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');
2796
from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id;
2800
from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id;
2803
drop table t1,t2,t3;
2804
create table t1 (a int);
2805
create table t2 (b int);
2806
create table t3 (c int);
2807
select * from t1 join t2 join t3 on (t1.a=t3.c);
2809
select * from t1 join t2 left join t3 on (t1.a=t3.c);
2811
select * from t1 join t2 right join t3 on (t1.a=t3.c);
2813
select * from t1 join t2 straight_join t3 on (t1.a=t3.c);
2815
drop table t1, t2 ,t3;
2816
create table t1(f1 int, f2 date);
2817
insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),
2818
(4,'2005-10-01'),(5,'2005-12-30');
2819
select * from t1 where f2 >= 0 order by f2;
2826
select * from t1 where f2 >= '0000-00-00' order by f2;
2833
select * from t1 where f2 >= '2005-09-31' order by f2;
2838
Warning 1292 Incorrect date value: '2005-09-31' for column 'f2' at row 1
2839
select * from t1 where f2 >= '2005-09-3a' order by f2;
2845
Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1
2846
select * from t1 where f2 <= '2005-09-31' order by f2;
2852
Warning 1292 Incorrect date value: '2005-09-31' for column 'f2' at row 1
2853
select * from t1 where f2 <= '2005-09-3a' order by f2;
2858
Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1
2860
CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a));
2861
CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a));
2862
CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32),
2863
PRIMARY KEY(key_a,key_b));
2864
INSERT INTO t1 VALUES (0,'');
2865
INSERT INTO t1 VALUES (1,'i');
2866
INSERT INTO t1 VALUES (2,'j');
2867
INSERT INTO t1 VALUES (3,'k');
2868
INSERT INTO t2 VALUES (1,'r');
2869
INSERT INTO t2 VALUES (2,'s');
2870
INSERT INTO t2 VALUES (3,'t');
2871
INSERT INTO t3 VALUES (1,5,'x');
2872
INSERT INTO t3 VALUES (1,6,'y');
2873
INSERT INTO t3 VALUES (2,5,'xx');
2874
INSERT INTO t3 VALUES (2,6,'yy');
2875
INSERT INTO t3 VALUES (2,7,'zz');
2876
INSERT INTO t3 VALUES (3,5,'xxx');
2878
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
2879
INNER JOIN t3 ON t1.key_a = t3.key_a
2880
WHERE t2.key_a=2 and key_b=5;
2883
EXPLAIN SELECT t2.key_a,foo
2884
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
2885
INNER JOIN t3 ON t1.key_a = t3.key_a
2886
WHERE t2.key_a=2 and key_b=5;
2887
id select_type table type possible_keys key key_len ref rows Extra
2888
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
2889
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index
2890
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
2892
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
2893
INNER JOIN t3 ON t1.key_a = t3.key_a
2894
WHERE t2.key_a=2 and key_b=5;
2897
EXPLAIN SELECT t2.key_a,foo
2898
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
2899
INNER JOIN t3 ON t1.key_a = t3.key_a
2900
WHERE t2.key_a=2 and key_b=5;
2901
id select_type table type possible_keys key key_len ref rows Extra
2902
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
2903
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index
2904
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
2905
DROP TABLE t1,t2,t3;
2906
create table t1 (f1 int);
2907
insert into t1 values(1),(2);
2908
create table t2 (f2 int, f3 int, key(f2));
2909
insert into t2 values(1,1),(2,2);
2910
create table t3 (f4 int not null);
2911
insert into t3 values (2),(2),(2);
2912
select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1;
2916
drop table t1,t2,t3;
2917
create table t1 (f1 int unique);
2918
create table t2 (f2 int unique);
2919
create table t3 (f3 int unique);
2920
insert into t1 values(1),(2);
2921
insert into t2 values(1),(2);
2922
insert into t3 values(1),(NULL);
2923
select * from t3 where f3 is null;
2926
select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1;
2929
drop table t1,t2,t3;
2930
create table t1(f1 char, f2 char not null);
2931
insert into t1 values(null,'a');
2932
create table t2 (f2 char not null);
2933
insert into t2 values('b');
2934
select * from t1 left join t2 on f1=t2.f2 where t1.f2='a';
2938
select * from (select * left join t on f1=f2) tt;
2939
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on f1=f2) tt' at line 1
2940
CREATE TABLE t1 (sku int PRIMARY KEY, pr int);
2941
CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255));
2942
INSERT INTO t1 VALUES
2943
(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10);
2944
INSERT INTO t2 VALUES
2945
(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),
2946
(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');
2947
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
2948
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
2949
sku sppr name sku pr
2953
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
2954
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
2955
id select_type table type possible_keys key key_len ref rows Extra
2956
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
2957
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where
2959
create table t1 (a int);
2960
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2961
create table t2 (a int, b int, c int, e int, primary key(a,b,c));
2962
insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C;
2964
Table Op Msg_type Msg_text
2965
test.t2 analyze status OK
2966
select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
2968
In next EXPLAIN, B.rows must be exactly 10:
2970
CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
2971
INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),
2972
(3,1), (5,1), (8,9), (2,2), (0,9);
2973
CREATE TABLE t2 (c int, d int, f int, INDEX(c,f));
2974
INSERT INTO t2 VALUES
2975
(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1),
2976
(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1),
2977
(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1);
2979
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
2980
id select_type table type possible_keys key key_len ref rows Extra
2981
1 SIMPLE t1 range PRIMARY,b b 5 NULL 1 Using where
2982
1 SIMPLE t2 ref c c 5 test.t1.a 2
2984
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
2985
id select_type table type possible_keys key key_len ref rows Extra
2986
1 SIMPLE t1 range PRIMARY,b PRIMARY 4 NULL 1 Using where
2987
1 SIMPLE t2 ref c c 5 test.t1.a 2
2990
a int not null auto_increment primary key,
2995
a int not null auto_increment primary key,
3000
insert into t1 (b,c) values (0,1), (0,1);
3001
insert into t2 (b,c) values (0,1);
3002
select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d
3003
from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1
3004
where t1.b <> 1 order by t1.a;
3005
a t1.b + 0 t1.c + 0 a t2.b + 0 c d
3007
2 0 1 NULL NULL NULL NULL
3009
SELECT 0.9888889889 * 1.011111411911;
3010
0.9888889889 * 1.011111411911
3011
0.9998769417899202067879
3012
CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
3013
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
3014
CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
3015
INSERT INTO t2 VALUES
3016
(1), (1), (1), (1), (1), (1), (1), (1),
3020
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
3021
id select_type table type possible_keys key key_len ref rows Extra
3022
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3023
1 SIMPLE t2 ref idx idx 4 const 1 Using index
3024
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
3025
id select_type table type possible_keys key key_len ref rows Extra
3026
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3027
1 SIMPLE t2 ref idx idx 4 const 1 Using index
3029
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
3030
INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
3031
CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
3032
INSERT INTO t2 VALUES (2,1), (3,2);
3033
CREATE TABLE t3 (d int, e int, INDEX idx1(d));
3034
INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
3036
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3038
id select_type table type possible_keys key key_len ref rows Extra
3039
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3040
1 SIMPLE t2 const idx1 NULL NULL NULL 1
3041
1 SIMPLE t3 ref idx1 idx1 5 const 1
3042
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3045
2 NULL NULL NULL 2 10
3046
2 NULL NULL NULL 2 20
3047
2 NULL NULL NULL 2 40
3048
2 NULL NULL NULL 2 50
3049
DROP TABLE t1,t2,t3;
3050
CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
3051
CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
3052
INSERT INTO t1 VALUES
3053
('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'),
3054
('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
3055
INSERT INTO t2 VALUES
3056
('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
3057
('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
3058
('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
3059
('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
3061
FROM t1 JOIN t2 ON t2.fk=t1.pk
3062
WHERE t2.fk < 'c' AND t2.pk=t1.fk;
3063
id select_type table type possible_keys key key_len ref rows Extra
3064
1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 1 Using where
3065
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where
3067
FROM t1 JOIN t2 ON t2.fk=t1.pk
3068
WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
3069
id select_type table type possible_keys key key_len ref rows Extra
3070
1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 1 Using where
3071
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where
3073
FROM t1 JOIN t2 ON t2.fk=t1.pk
3074
WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
3075
id select_type table type possible_keys key key_len ref rows Extra
3076
1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where
3077
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where
3079
CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
3080
CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
3081
PRIMARY KEY (a), UNIQUE KEY (b));
3082
INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
3083
INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
3084
EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
3085
id select_type table type possible_keys key key_len ref rows Extra
3086
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3087
1 SIMPLE t2 const b b 82 const 1 Using index
3089
CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
3090
CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
3091
CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
3092
INSERT INTO t1 VALUES
3093
(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
3094
(6,63,67), (5,55,58), (3,38,39), (8,81,89);
3095
INSERT INTO t2 VALUES
3096
(21,210), (41,410), (82,820), (83,830), (84,840),
3097
(65,650), (51,510), (37,370), (94,940), (76,760),
3098
(22,220), (33,330), (40,400), (95,950), (38,380),
3099
(67,670), (88,880), (57,570), (96,960), (97,970);
3100
INSERT INTO t3 VALUES
3101
(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
3102
(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
3103
(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
3104
(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
3106
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3107
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3108
t3.a=t2.a AND t3.c IN ('bb','ee');
3109
id select_type table type possible_keys key key_len ref rows Extra
3110
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3111
1 SIMPLE t2 range si si 5 NULL 1 Using where
3112
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3114
SELECT t3.a FROM t1,t2,t3
3115
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3116
t3.a=t2.a AND t3.c IN ('bb','ee') ;
3117
id select_type table type possible_keys key key_len ref rows Extra
3118
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3119
1 SIMPLE t2 range si,ai si 5 NULL 1 Using where
3120
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3122
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3123
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3124
t3.c IN ('bb','ee');
3125
id select_type table type possible_keys key key_len ref rows Extra
3126
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3127
1 SIMPLE t2 range si si 5 NULL 2 Using where
3128
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3130
SELECT t3.a FROM t1,t2,t3
3131
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3132
t3.c IN ('bb','ee');
3133
id select_type table type possible_keys key key_len ref rows Extra
3134
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3135
1 SIMPLE t2 range si,ai si 5 NULL 2 Using where
3136
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3137
DROP TABLE t1,t2,t3;
3138
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
3139
CREATE TABLE t2 ( f11 int PRIMARY KEY );
3140
INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0);
3141
INSERT INTO t2 VALUES (62);
3142
SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;
3143
f1 f2 f3 f4 f5 f6 checked_out f11
3146
DROP TABLE IF EXISTS t1;
3147
CREATE TABLE t1(a int);
3148
INSERT into t1 values (1), (2), (3);
3149
SELECT * FROM t1 LIMIT 2, -1;
3150
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1
3153
ID_with_null int NULL,
3154
ID_better int NOT NULL,
3155
INDEX idx1 (ID_with_null),
3156
INDEX idx2 (ID_better)
3158
INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3);
3159
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3160
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3161
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3162
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3163
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3164
SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL;
3167
SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3170
EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3171
id select_type table type possible_keys key key_len ref rows Extra
3172
1 SIMPLE t1 ref idx1,idx2 idx1 5 const 1 Using where
3173
DROP INDEX idx1 ON t1;
3174
CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);
3175
EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3176
id select_type table type possible_keys key key_len ref rows Extra
3177
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 50 Using where
3180
ID1_with_null int NULL,
3181
ID2_with_null int NULL,
3182
ID_better int NOT NULL,
3183
INDEX idx1 (ID1_with_null, ID2_with_null),
3184
INDEX idx2 (ID_better)
3186
INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3),
3187
(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);
3188
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3189
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3190
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3191
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3192
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3193
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3194
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
3197
SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL;
3200
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;
3203
SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3206
EXPLAIN SELECT * FROM t1
3207
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3208
id select_type table type possible_keys key key_len ref rows Extra
3209
1 SIMPLE t1 ref idx1,idx2 idx1 10 const,const # Using where
3210
EXPLAIN SELECT * FROM t1
3211
WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;
3212
id select_type table type possible_keys key key_len ref rows Extra
3213
1 SIMPLE t1 ref idx1,idx2 idx1 5 const # Using where
3214
EXPLAIN SELECT * FROM t1
3215
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3216
id select_type table type possible_keys key key_len ref rows Extra
3217
1 SIMPLE t1 ref idx1,idx2 idx1 10 const,const # Using where
3218
DROP INDEX idx1 ON t1;
3219
CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);
3220
EXPLAIN SELECT * FROM t1
3221
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3222
id select_type table type possible_keys key key_len ref rows Extra
3223
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 50 Using where
3224
EXPLAIN SELECT * FROM t1
3225
WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ;
3226
id select_type table type possible_keys key key_len ref rows Extra
3227
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 50 Using where
3228
EXPLAIN SELECT * FROM t1
3229
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3230
id select_type table type possible_keys key key_len ref rows Extra
3231
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 50 Using where
3232
EXPLAIN SELECT * FROM t1
3233
WHERE ID_better=1 AND ID1_with_null IS NULL AND
3234
(ID2_with_null=1 OR ID2_with_null=2);
3235
id select_type table type possible_keys key key_len ref rows Extra
3236
1 SIMPLE t1 ref idx1,idx2 idx1 5 const 11 Using where
3238
CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
3239
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
3241
Table Op Msg_type Msg_text
3242
test.t1 analyze status OK
3243
CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
3244
INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
3245
INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
3247
Table Op Msg_type Msg_text
3248
test.t2 analyze status OK
3250
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3251
AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3252
AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3253
id select_type table type possible_keys key key_len ref rows Extra
3254
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
3255
1 SIMPLE t1 range ts ts 4 NULL 2 Using where
3257
Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
3258
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3259
AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3260
AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3262
30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00
3264
Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
3266
create table t1 (a bigint);
3267
insert into t1 values
3268
(if(1, 92233720368547758, 1)),
3269
(case when 1 then 92233720368547758 else 1 end),
3270
(coalesce(92233720368547758, 1));
3277
create table t1 select
3278
if(1, 9223372036854775808, 1) i,
3279
case when 1 then 9223372036854775808 else 1 end c,
3280
coalesce(9223372036854775808, 1) co;
3281
show create table t1;
3283
t1 CREATE TABLE `t1` (
3284
`i` decimal(19,0) NOT NULL,
3285
`c` decimal(19,0) NOT NULL,
3286
`co` decimal(19,0) NOT NULL
3290
if(1, 1111111111111111111, 1) i,
3291
case when 1 then 1111111111111111111 else 1 end c,
3292
coalesce(1111111111111111111, 1) co;
3294
1111111111111111111 1111111111111111111 1111111111111111111
3295
CREATE TABLE t1 (name varchar(255));
3296
CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
3297
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
3298
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
3299
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3300
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3310
SELECT * FROM t2 ORDER BY name;
3319
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3328
EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3329
id select_type table type possible_keys key key_len ref rows Extra
3330
1 SIMPLE t2 ref name name 15 const 1 Using where
3331
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3336
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3337
id select_type table type possible_keys key key_len ref rows Extra
3338
1 SIMPLE t2 range name name 15 NULL 1 Using where
3339
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3345
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3346
id select_type table type possible_keys key key_len ref rows Extra
3347
1 SIMPLE t2 range name name 15 NULL 1 Using where; Using filesort
3348
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3354
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3355
id select_type table type possible_keys key key_len ref rows Extra
3356
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3357
1 SIMPLE t2 ref name name 15 test.t1.name 2
3358
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3369
CREATE TABLE t1 (name text);
3370
CREATE TABLE t2 (name text, n int, KEY (name(3)));
3371
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
3372
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
3373
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3374
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3384
SELECT * FROM t2 ORDER BY name;
3393
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3402
EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3403
id select_type table type possible_keys key key_len ref rows Extra
3404
1 SIMPLE t2 ref name name 15 const 1 Using where
3405
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3410
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3411
id select_type table type possible_keys key key_len ref rows Extra
3412
1 SIMPLE t2 range name name 15 NULL 1 Using where
3413
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3419
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3420
id select_type table type possible_keys key key_len ref rows Extra
3421
1 SIMPLE t2 range name name 15 NULL 1 Using where; Using filesort
3422
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3428
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3429
id select_type table type possible_keys key key_len ref rows Extra
3430
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3431
1 SIMPLE t2 ref name name 15 test.t1.name 2
3432
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3444
access_id int NOT NULL default '0',
3445
name varchar(20) default NULL,
3446
rank int NOT NULL default '0',
3450
faq_group_id int NOT NULL default '0',
3451
faq_id int NOT NULL default '0',
3452
access_id int default NULL,
3453
UNIQUE KEY idx1 (faq_id),
3454
KEY idx2 (faq_group_id,faq_id)
3456
INSERT INTO t1 VALUES
3457
(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
3458
INSERT INTO t2 VALUES
3459
(261,265,1),(490,494,1);
3461
FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
3462
ON (t1.access_id = t2.access_id)
3464
ON (t.faq_group_id = t2.faq_group_id AND
3465
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3467
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3471
FROM t1 INNER JOIN t2
3472
ON (t1.access_id = t2.access_id)
3474
ON (t.faq_group_id = t2.faq_group_id AND
3475
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3477
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3481
CREATE TABLE t1 (a INT, b INT, KEY inx (b,a));
3482
INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7);
3483
EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2
3484
ON ( f1.b=f2.b AND f1.a<f2.a )
3485
WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);
3486
id select_type table type possible_keys key key_len ref rows Extra
3487
1 SIMPLE f1 range inx inx 5 NULL 5 Using where; Using index
3488
1 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index
3490
CREATE TABLE t1 (c1 INT, c2 INT);
3491
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3492
EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2)))))))))))))))))))))))))))))))) > 0;
3493
id select_type table type possible_keys key key_len ref rows Extra
3494
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
3495
31 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3496
32 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3497
EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) > 0;
3498
ERROR HY000: Too high level of nesting for select
3501
c1 int NOT NULL AUTO_INCREMENT,
3502
c2 varchar(1000) DEFAULT NULL,
3503
c3 bigint DEFAULT NULL,
3504
c4 bigint DEFAULT NULL,
3519
join_0.c1=join_1.c1 AND
3520
join_1.c1=join_2.c1 AND
3521
join_2.c1=join_3.c1 AND
3522
join_3.c1=join_4.c1 AND
3523
join_4.c1=join_5.c1 AND
3524
join_5.c1=join_6.c1 AND
3533
join_5.c2 <> '?' AND
3534
join_6.c2 <> '?' AND
3535
join_7.c2 >= '?' AND
3536
join_0.c1=join_1.c1 AND
3537
join_1.c1=join_2.c1 AND
3538
join_2.c1=join_3.c1 AND
3539
join_3.c1=join_4.c1 AND
3540
join_4.c1=join_5.c1 AND
3541
join_5.c1=join_6.c1 AND
3549
id select_type table type possible_keys key key_len ref rows filtered Extra
3550
1 SIMPLE join_0 ALL PRIMARY NULL NULL NULL 0 0.00 Using temporary; Using filesort
3551
1 SIMPLE join_1 eq_ref PRIMARY PRIMARY 4 test.join_0.c1 1 100.00
3552
1 SIMPLE join_2 eq_ref PRIMARY PRIMARY 4 test.join_1.c1 1 100.00 Using where
3553
1 SIMPLE join_3 eq_ref PRIMARY PRIMARY 4 test.join_2.c1 1 100.00 Using where
3554
1 SIMPLE join_4 eq_ref PRIMARY PRIMARY 4 test.join_3.c1 1 100.00 Using where
3555
1 SIMPLE join_5 eq_ref PRIMARY PRIMARY 4 test.join_4.c1 1 100.00 Using where
3556
1 SIMPLE join_6 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where
3557
1 SIMPLE join_7 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where
3559
Note 1003 select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_4`.`c2` = '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`
3562
Note 1003 select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_4`.`c2` = '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`
3568
Warning 1474 Name ' ' has become ''
3573
Warning 1474 Name ' ' has become ''
3578
Warning 1466 Leading spaces are removed from name ' x'
3579
CREATE TABLE t1 (c11 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
3580
CREATE TABLE t2 (c21 INT NOT NULL,
3581
c22 INT DEFAULT NULL,
3583
CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,
3584
c32 INT DEFAULT NULL,
3587
KEY (c33, c34, c32));
3588
INSERT INTO t1 values (),(),(),(),();
3589
INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;
3590
INSERT INTO t3 VALUES (1, 1, 1, 0),
3595
SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
3596
t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
3597
t3.c33 = 1 AND t2.c22 in (1, 3)
3606
SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
3607
t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
3608
t3.c33 = 1 AND t2.c22 in (1, 3)
3617
DROP TABLE t1, t2, t3;
3618
select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
3619
and '2007/10/20 00:00:00 GMT';
3620
str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
3621
and '2007/10/20 00:00:00 GMT'
3624
Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT'
3625
Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT'
3626
select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
3627
str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'
3630
Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6'
3631
select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
3632
str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'
3635
Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6'
3636
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
3637
str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'
3640
Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6'
3641
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';
3642
str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'
3645
Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6'
3646
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';
3647
str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'
3650
Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6'
3651
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';
3652
str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'
3655
Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6'
3656
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';
3657
str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'
3660
Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6'
3661
select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
3662
str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
3665
Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
3666
select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
3667
str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
3670
Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
3671
select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';
3672
str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'
3674
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';
3675
str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'
3677
select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';
3678
str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'
3680
select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';
3681
str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'
3684
Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34'
3685
select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';
3686
str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'
3689
Warning 1292 Truncated incorrect datetime value: '2007-02-30 12:34'
3690
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
3691
str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
3693
select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
3694
and '2007/10/20 00:00:00';
3695
str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
3696
and '2007/10/20 00:00:00'
3698
select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
3699
str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
3702
Warning 1292 Truncated incorrect datetime value: ''
3703
select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';
3704
str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'
3706
select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
3707
str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
3709
select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';
3710
str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'
3712
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';
3713
str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''
3716
Warning 1292 Truncated incorrect datetime value: ''
3717
select str_to_date('1','%Y-%m-%d') = '1';
3718
str_to_date('1','%Y-%m-%d') = '1'
3721
Warning 1292 Truncated incorrect date value: '1'
3722
select str_to_date('1','%Y-%m-%d') = '1';
3723
str_to_date('1','%Y-%m-%d') = '1'
3726
Warning 1292 Truncated incorrect date value: '1'
3727
select str_to_date('','%Y-%m-%d') = '';
3728
str_to_date('','%Y-%m-%d') = ''
3731
Warning 1292 Truncated incorrect date value: ''
3732
select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
3733
str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL
3735
select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
3736
str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'
3738
select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
3739
str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL
3743
# Bug#30736: Row Size Too Large Error Creating a Table and
3746
DROP TABLE IF EXISTS t1;
3747
DROP TABLE IF EXISTS t2;
3753
INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5);
3776
CREATE TABLE t1 (c1 BIGINT NOT NULL);
3777
INSERT INTO t1 (c1) VALUES (1);
3778
SELECT * FROM t1 WHERE c1 > NULL + 1;
3782
CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY);
3783
INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0');
3784
SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar');
3788
CREATE TABLE t1 (a INT, b INT);
3789
CREATE TABLE t2 (a INT, c INT, KEY(a));
3790
INSERT INTO t1 VALUES (1, 1), (2, 2);
3791
INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
3792
(2, 1), (2, 2), (2, 3), (2, 4), (2, 5),
3793
(3, 1), (3, 2), (3, 3), (3, 4), (3, 5),
3794
(4, 1), (4, 2), (4, 3), (4, 4), (4, 5);
3796
SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;
3800
SHOW STATUS LIKE 'Handler_read%';
3802
Handler_read_first 0
3807
Handler_read_rnd_next 3
3810
create table t1(a INT, KEY (a));
3811
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
3812
SELECT a FROM t1 ORDER BY a LIMIT 2;
3816
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;
3821
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;
added
removed
tests/suite/broken/r/pbxt/select.result
