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
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
487
abrogating abrogating
488
admonishing admonishing
493
analyzable analyzable
496
select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
497
concat(fld3," ",fld3)
499
abrogating abrogating
500
admonishing admonishing
505
analyzable analyzable
508
select distinct fld5 from t2 limit 10;
520
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
532
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
544
select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
545
fld3 repeat("a",length(fld3)) count(*)
549
congresswoman aaaaaaaaaaaaa 1
550
contrition aaaaaaaaaa 1
552
cultivation aaaaaaaaaaa 1
553
definiteness aaaaaaaaaaaa 1
554
demultiplex aaaaaaaaaaa 1
555
disappointing aaaaaaaaaaaaa 1
556
select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
557
companynr rtrim(space(512+companynr))
565
select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
567
select period from t1;
570
select period from t1 where period=1900;
572
select fld3,period from t1,t2 where fld1 = 011401 order by period;
575
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
578
select fld3,period from t2,t1 where companynr*10 = 37*10;
811
electroencephalography 9410
919
interrelationships 9410
1168
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;
1169
fld3 period price price2
1170
admonishing 1002 28357832 8723648
1171
analyzable 1002 28357832 8723648
1172
annihilates 1001 5987435 234724
1173
Antares 1002 28357832 8723648
1174
astound 1001 5987435 234724
1175
audiology 1001 5987435 234724
1176
Augustine 1002 28357832 8723648
1177
Baird 1002 28357832 8723648
1178
bewilderingly 1001 5987435 234724
1179
breaking 1001 5987435 234724
1180
Conley 1001 5987435 234724
1181
dentally 1002 28357832 8723648
1182
dissociate 1002 28357832 8723648
1183
elite 1001 5987435 234724
1184
eschew 1001 5987435 234724
1185
Eulerian 1001 5987435 234724
1186
flanking 1001 5987435 234724
1187
foldout 1002 28357832 8723648
1188
funereal 1002 28357832 8723648
1189
galling 1002 28357832 8723648
1190
Graves 1001 5987435 234724
1191
grazing 1001 5987435 234724
1192
groupings 1001 5987435 234724
1193
handgun 1001 5987435 234724
1194
humility 1002 28357832 8723648
1195
impulsive 1002 28357832 8723648
1196
inch 1001 5987435 234724
1197
intelligibility 1001 5987435 234724
1198
jarring 1001 5987435 234724
1199
lawgiver 1001 5987435 234724
1200
lectured 1002 28357832 8723648
1201
Merritt 1002 28357832 8723648
1202
neonatal 1001 5987435 234724
1203
offload 1002 28357832 8723648
1204
parters 1002 28357832 8723648
1205
pityingly 1002 28357832 8723648
1206
puddings 1002 28357832 8723648
1207
Punjab 1001 5987435 234724
1208
quitter 1002 28357832 8723648
1209
realtor 1001 5987435 234724
1210
relaxing 1001 5987435 234724
1211
repetitions 1001 5987435 234724
1212
resumes 1001 5987435 234724
1213
Romans 1002 28357832 8723648
1214
rusting 1001 5987435 234724
1215
scholastics 1001 5987435 234724
1216
skulking 1002 28357832 8723648
1217
stated 1002 28357832 8723648
1218
suites 1002 28357832 8723648
1219
sureties 1001 5987435 234724
1220
testicle 1002 28357832 8723648
1221
tinily 1002 28357832 8723648
1222
tragedies 1001 5987435 234724
1223
trimmings 1001 5987435 234724
1224
vacuuming 1001 5987435 234724
1225
ventilate 1001 5987435 234724
1226
wallet 1001 5987435 234724
1227
Weissmuller 1002 28357832 8723648
1228
Wotan 1002 28357832 8723648
1229
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;
1230
fld1 fld3 period price price2
1231
18201 relaxing 1001 5987435 234724
1232
18601 vacuuming 1001 5987435 234724
1233
18801 inch 1001 5987435 234724
1234
18811 repetitions 1001 5987435 234724
1235
create temporary table t4 (
1236
companynr int NOT NULL default '0',
1237
companyname char(30) NOT NULL default '',
1238
PRIMARY KEY (companynr),
1239
UNIQUE KEY companyname(companyname)
1240
) ENGINE=MyISAM COMMENT='companynames';
1241
select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1242
companynr companyname
1255
select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1256
companynr companyname
1269
select * from t1,t1 t12;
1270
Period Varor_period Period Varor_period
1272
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;
1299
insert into t2 (fld1, companynr) values (999999,99);
1300
select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1301
companynr companyname
1303
select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1306
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1307
companynr companyname
1308
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1311
delete from t2 where fld1=999999;
1312
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1316
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;
1317
fld1 companynr fld3 period
1318
38008 37 reporters 1008
1319
38208 37 Selfridge 1008
1320
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;
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 (t3.t2nr = 38208 or t3.t2nr = 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 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);
1331
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)));
1334
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;
1340
select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1344
select fld1 from t2 where fld1 between 250502 and 250504;
1349
select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
1360
select count(*) from t1;
1363
select companynr,count(*),sum(fld1) from t2 group by companynr;
1364
companynr count(*) sum(fld1)
1377
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1384
select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1385
count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1386
70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1387
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1388
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1389
0 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
1390
29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026
1391
34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1392
select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1393
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1394
37 1 1 5987435 5987435 5987435 39654943.0000
1395
37 2 1 28357832 28357832 28357832 39654943.0000
1396
37 3 1 39654943 39654943 39654943 39654943.0000
1397
37 11 1 5987435 5987435 5987435 39654943.0000
1398
37 12 1 28357832 28357832 28357832 39654943.0000
1399
37 13 1 39654943 39654943 39654943 39654943.0000
1400
37 21 1 5987435 5987435 5987435 39654943.0000
1401
37 22 1 28357832 28357832 28357832 39654943.0000
1402
37 23 1 39654943 39654943 39654943 39654943.0000
1403
37 31 1 5987435 5987435 5987435 39654943.0000
1404
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;
1405
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1406
37 1 1 5987435 5987435 5987435 39654943.0000
1407
37 2 1 28357832 28357832 28357832 39654943.0000
1408
37 3 1 39654943 39654943 39654943 39654943.0000
1409
37 11 1 5987435 5987435 5987435 39654943.0000
1410
37 12 1 28357832 28357832 28357832 39654943.0000
1411
37 13 1 39654943 39654943 39654943 39654943.0000
1412
37 21 1 5987435 5987435 5987435 39654943.0000
1413
37 22 1 28357832 28357832 28357832 39654943.0000
1414
37 23 1 39654943 39654943 39654943 39654943.0000
1415
37 31 1 5987435 5987435 5987435 39654943.0000
1416
select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
1417
companynr count(price) sum(price) min(price) max(price) avg(price)
1418
37 12543 309394878010 5987435 39654943 24666736.6667
1419
78 8362 414611089292 726498 98439034 49582766.0000
1420
101 4181 3489454238 834598 834598 834598.0000
1421
154 4181 4112197254950 983543950 983543950 983543950.0000
1422
311 4181 979599938 234298 234298 234298.0000
1423
447 4181 9929180954 2374834 2374834 2374834.0000
1424
512 4181 3288532102 786542 786542 786542.0000
1425
select distinct mod(companynr,10) from t4 group by companynr;
1436
select distinct 1 from t4 group by companynr;
1439
select count(distinct fld1) from t2;
1440
count(distinct fld1)
1442
select companynr,count(distinct fld1) from t2 group by companynr;
1443
companynr count(distinct fld1)
1456
select companynr,count(*) from t2 group by companynr;
1470
select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1471
companynr count(distinct concat(fld1,repeat(65,1000)))
1484
select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1485
companynr count(distinct concat(fld1,repeat(65,200)))
1498
select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1499
companynr count(distinct floor(fld1/100))
1512
select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
1513
companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))
1526
select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1529
select name,count(*) from t3 where name='cloakroom' group by name;
1532
select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1535
select count(*) from t3 where name='cloakroom' and price2=823742;
1538
select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1541
select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1550
select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1553
select companynr,companyname from t4 group by 1;
1554
companynr companyname
1567
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1568
companynr companyname count(*)
1581
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1584
select sum(Period)/count(*) from t1;
1585
sum(Period)/count(*)
1587
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;
1588
companynr count sum diff func
1589
37 12543 309394878010 0.0000 464091
1590
78 8362 414611089292 0.0000 652236
1591
101 4181 3489454238 0.0000 422281
1592
154 4181 4112197254950 0.0000 643874
1593
311 4181 979599938 0.0000 1300291
1594
447 4181 9929180954 0.0000 1868907
1595
512 4181 3288532102 0.0000 2140672
1596
select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
1599
select companynr,count(*) from t2 group by companynr order by 2 desc;
1613
select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1621
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;
1622
fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)
1623
teethe 1 1 5987435 5987435 5987435 5987435.0000
1624
dreaded 11401 1 5987435 5987435 5987435 5987435.0000
1625
scholastics 11402 1 28357832 28357832 28357832 28357832.0000
1626
audiology 11403 1 39654943 39654943 39654943 39654943.0000
1627
wallet 11501 1 5987435 5987435 5987435 5987435.0000
1628
parters 11701 1 5987435 5987435 5987435 5987435.0000
1629
eschew 11702 1 28357832 28357832 28357832 28357832.0000
1630
quitter 11703 1 39654943 39654943 39654943 39654943.0000
1631
neat 12001 1 5987435 5987435 5987435 5987435.0000
1632
Steinberg 12003 1 39654943 39654943 39654943 39654943.0000
1633
balled 12301 1 5987435 5987435 5987435 5987435.0000
1634
persist 12302 1 28357832 28357832 28357832 28357832.0000
1635
attainments 12303 1 39654943 39654943 39654943 39654943.0000
1636
capably 12501 1 5987435 5987435 5987435 5987435.0000
1637
impulsive 12602 1 28357832 28357832 28357832 28357832.0000
1638
starlet 12603 1 39654943 39654943 39654943 39654943.0000
1639
featherweight 12701 1 5987435 5987435 5987435 5987435.0000
1640
pessimist 12702 1 28357832 28357832 28357832 28357832.0000
1641
daughter 12703 1 39654943 39654943 39654943 39654943.0000
1642
lawgiver 13601 1 5987435 5987435 5987435 5987435.0000
1643
stated 13602 1 28357832 28357832 28357832 28357832.0000
1644
readable 13603 1 39654943 39654943 39654943 39654943.0000
1645
testicle 13801 1 5987435 5987435 5987435 5987435.0000
1646
Parsifal 13802 1 28357832 28357832 28357832 28357832.0000
1647
leavings 13803 1 39654943 39654943 39654943 39654943.0000
1648
squeaking 13901 1 5987435 5987435 5987435 5987435.0000
1649
contrasted 16001 1 5987435 5987435 5987435 5987435.0000
1650
leftover 16201 1 5987435 5987435 5987435 5987435.0000
1651
whiteners 16202 1 28357832 28357832 28357832 28357832.0000
1652
erases 16301 1 5987435 5987435 5987435 5987435.0000
1653
Punjab 16302 1 28357832 28357832 28357832 28357832.0000
1654
Merritt 16303 1 39654943 39654943 39654943 39654943.0000
1655
sweetish 18001 1 5987435 5987435 5987435 5987435.0000
1656
dogging 18002 1 28357832 28357832 28357832 28357832.0000
1657
scornfully 18003 1 39654943 39654943 39654943 39654943.0000
1658
fetters 18012 1 28357832 28357832 28357832 28357832.0000
1659
bivalves 18013 1 39654943 39654943 39654943 39654943.0000
1660
skulking 18021 1 5987435 5987435 5987435 5987435.0000
1661
flint 18022 1 28357832 28357832 28357832 28357832.0000
1662
flopping 18023 1 39654943 39654943 39654943 39654943.0000
1663
Judas 18032 1 28357832 28357832 28357832 28357832.0000
1664
vacuuming 18033 1 39654943 39654943 39654943 39654943.0000
1665
medical 18041 1 5987435 5987435 5987435 5987435.0000
1666
bloodbath 18042 1 28357832 28357832 28357832 28357832.0000
1667
subschema 18043 1 39654943 39654943 39654943 39654943.0000
1668
interdependent 18051 1 5987435 5987435 5987435 5987435.0000
1669
Graves 18052 1 28357832 28357832 28357832 28357832.0000
1670
neonatal 18053 1 39654943 39654943 39654943 39654943.0000
1671
sorters 18061 1 5987435 5987435 5987435 5987435.0000
1672
epistle 18062 1 28357832 28357832 28357832 28357832.0000
1673
Conley 18101 1 5987435 5987435 5987435 5987435.0000
1674
lectured 18102 1 28357832 28357832 28357832 28357832.0000
1675
Abraham 18103 1 39654943 39654943 39654943 39654943.0000
1676
cage 18201 1 5987435 5987435 5987435 5987435.0000
1677
hushes 18202 1 28357832 28357832 28357832 28357832.0000
1678
Simla 18402 1 28357832 28357832 28357832 28357832.0000
1679
reporters 18403 1 39654943 39654943 39654943 39654943.0000
1680
coexist 18601 1 5987435 5987435 5987435 5987435.0000
1681
Beebe 18602 1 28357832 28357832 28357832 28357832.0000
1682
Taoism 18603 1 39654943 39654943 39654943 39654943.0000
1683
Connally 18801 1 5987435 5987435 5987435 5987435.0000
1684
fetched 18802 1 28357832 28357832 28357832 28357832.0000
1685
checkpoints 18803 1 39654943 39654943 39654943 39654943.0000
1686
gritty 18811 1 5987435 5987435 5987435 5987435.0000
1687
firearm 18812 1 28357832 28357832 28357832 28357832.0000
1688
minima 19101 1 5987435 5987435 5987435 5987435.0000
1689
Selfridge 19102 1 28357832 28357832 28357832 28357832.0000
1690
disable 19103 1 39654943 39654943 39654943 39654943.0000
1691
witchcraft 19201 1 5987435 5987435 5987435 5987435.0000
1692
betroth 30501 1 5987435 5987435 5987435 5987435.0000
1693
Manhattanize 30502 1 28357832 28357832 28357832 28357832.0000
1694
imprint 30503 1 39654943 39654943 39654943 39654943.0000
1695
swelling 31901 1 5987435 5987435 5987435 5987435.0000
1696
interrelationships 36001 1 5987435 5987435 5987435 5987435.0000
1697
riser 36002 1 28357832 28357832 28357832 28357832.0000
1698
bee 38001 1 5987435 5987435 5987435 5987435.0000
1699
kanji 38002 1 28357832 28357832 28357832 28357832.0000
1700
dental 38003 1 39654943 39654943 39654943 39654943.0000
1701
railway 38011 1 5987435 5987435 5987435 5987435.0000
1702
validate 38012 1 28357832 28357832 28357832 28357832.0000
1703
normalizes 38013 1 39654943 39654943 39654943 39654943.0000
1704
Kline 38101 1 5987435 5987435 5987435 5987435.0000
1705
Anatole 38102 1 28357832 28357832 28357832 28357832.0000
1706
partridges 38103 1 39654943 39654943 39654943 39654943.0000
1707
recruited 38201 1 5987435 5987435 5987435 5987435.0000
1708
dimensions 38202 1 28357832 28357832 28357832 28357832.0000
1709
Chicana 38203 1 39654943 39654943 39654943 39654943.0000
1710
select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
1711
companynr fld3 sum(price)
1716
512 descendants 786542
1719
512 Micronesia 786542
1722
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;
1723
companynr count(*) min(fld3) max(fld3) sum(price) avg(price)
1724
0 1 Omaha Omaha 5987435 5987435.0000
1725
36 1 dubbed dubbed 28357832 28357832.0000
1726
37 83 Abraham Wotan 1908978016 22999735.1325
1727
50 2 scribbled tapestry 68012775 34006387.5000
1728
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;
1729
t3.companynr+0 t2nr fld3 sum(price)
1731
37 11401 breaking 5987435
1732
37 11402 Romans 28357832
1733
37 11403 intercepted 39654943
1734
37 11501 bewilderingly 5987435
1735
37 11701 astound 5987435
1736
37 11702 admonishing 28357832
1737
37 11703 sumac 39654943
1738
37 12001 flanking 5987435
1739
37 12003 combed 39654943
1740
37 12301 Eulerian 5987435
1741
37 12302 dubbed 28357832
1742
37 12303 Kane 39654943
1743
37 12501 annihilates 5987435
1744
37 12602 Wotan 28357832
1745
37 12603 snatching 39654943
1746
37 12701 grazing 5987435
1747
37 12702 Baird 28357832
1748
37 12703 celery 39654943
1749
37 13601 handgun 5987435
1750
37 13602 foldout 28357832
1751
37 13603 mystic 39654943
1752
37 13801 intelligibility 5987435
1753
37 13802 Augustine 28357832
1754
37 13803 teethe 39654943
1755
37 13901 scholastics 5987435
1756
37 16001 audiology 5987435
1757
37 16201 wallet 5987435
1758
37 16202 parters 28357832
1759
37 16301 eschew 5987435
1760
37 16302 quitter 28357832
1761
37 16303 neat 39654943
1762
37 18001 jarring 5987435
1763
37 18002 tinily 28357832
1764
37 18003 balled 39654943
1765
37 18012 impulsive 28357832
1766
37 18013 starlet 39654943
1767
37 18021 lawgiver 5987435
1768
37 18022 stated 28357832
1769
37 18023 readable 39654943
1770
37 18032 testicle 28357832
1771
37 18033 Parsifal 39654943
1772
37 18041 Punjab 5987435
1773
37 18042 Merritt 28357832
1774
37 18043 Quixotism 39654943
1775
37 18051 sureties 5987435
1776
37 18052 puddings 28357832
1777
37 18053 tapestry 39654943
1778
37 18061 trimmings 5987435
1779
37 18062 humility 28357832
1780
37 18101 tragedies 5987435
1781
37 18102 skulking 28357832
1782
37 18103 flint 39654943
1783
37 18201 relaxing 5987435
1784
37 18202 offload 28357832
1785
37 18402 suites 28357832
1786
37 18403 lists 39654943
1787
37 18601 vacuuming 5987435
1788
37 18602 dentally 28357832
1789
37 18603 humanness 39654943
1790
37 18801 inch 5987435
1791
37 18802 Weissmuller 28357832
1792
37 18803 irresponsibly 39654943
1793
37 18811 repetitions 5987435
1794
37 18812 Antares 28357832
1795
37 19101 ventilate 5987435
1796
37 19102 pityingly 28357832
1797
37 19103 interdependent 39654943
1798
37 19201 Graves 5987435
1799
37 30501 neonatal 5987435
1800
37 30502 scribbled 28357832
1801
37 30503 chafe 39654943
1802
37 31901 realtor 5987435
1803
37 36001 elite 5987435
1804
37 36002 funereal 28357832
1805
37 38001 Conley 5987435
1806
37 38002 lectured 28357832
1807
37 38003 Abraham 39654943
1808
37 38011 groupings 5987435
1809
37 38012 dissociate 28357832
1810
37 38013 coexist 39654943
1811
37 38101 rusting 5987435
1812
37 38102 galling 28357832
1813
37 38103 obliterates 39654943
1814
37 38201 resumes 5987435
1815
37 38202 analyzable 28357832
1816
37 38203 terminator 39654943
1817
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;
1820
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;
1823
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1827
select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1831
select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1838
select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1845
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
1846
companynr companyname count(*)
1854
select count(*) from t2;
1857
select count(*) from t2 where fld1 < 098024;
1860
select min(fld1) from t2 where fld1>= 098024;
1863
select max(fld1) from t2 where fld1>= 098024;
1866
select count(*) from t3 where price2=76234234;
1869
select count(*) from t3 where companynr=512 and price2=76234234;
1872
select min(fld1),max(fld1),count(*) from t2;
1873
min(fld1) max(fld1) count(*)
1875
select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
1878
select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
1879
count(*) min(t2nr) max(t2nr)
1881
select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
1903
select max(t2nr) from t3 where price=983543950;
1906
select t1.period from t3 = t1 limit 1;
1909
select t1.period from t1 as t1 limit 1;
1912
select t1.period as "Nuvarande period" from t1 as t1 limit 1;
1915
select period as ok_period from t1 limit 1;
1918
select period as ok_period from t1 group by ok_period limit 1;
1921
select 1+1 as summa from t1 group by summa limit 1;
1924
select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
1933
show tables from test like "s%";
1935
show tables from test like "t?";
1937
show columns from t2;
1938
Field Type Null Default Default_is_NULL On_Update
1939
auto INTEGER FALSE FALSE
1940
fld1 INTEGER FALSE 0 FALSE
1941
companynr INTEGER FALSE 0 FALSE
1942
fld3 VARCHAR FALSE FALSE
1943
fld4 VARCHAR FALSE FALSE
1944
fld5 VARCHAR FALSE FALSE
1945
fld6 VARCHAR FALSE FALSE
1946
show columns from t2 from test like 'f%';
1947
Field Type Null Default Default_is_NULL On_Update
1948
auto INTEGER FALSE FALSE
1949
fld1 INTEGER FALSE 0 FALSE
1950
companynr INTEGER FALSE 0 FALSE
1951
fld3 VARCHAR FALSE FALSE
1952
fld4 VARCHAR FALSE FALSE
1953
fld5 VARCHAR FALSE FALSE
1954
fld6 VARCHAR FALSE FALSE
1955
show columns from t2 from test like 's%';
1956
Field Type Null Default Default_is_NULL On_Update
1957
auto INTEGER FALSE FALSE
1958
fld1 INTEGER FALSE 0 FALSE
1959
companynr INTEGER FALSE 0 FALSE
1960
fld3 VARCHAR FALSE FALSE
1961
fld4 VARCHAR FALSE FALSE
1962
fld5 VARCHAR FALSE FALSE
1963
fld6 VARCHAR FALSE FALSE
1964
drop table t4, t3, t2, t1;
1966
id bigint NOT NULL auto_increment,
1967
pseudo varchar(35) NOT NULL default '',
1969
UNIQUE KEY pseudo (pseudo)
1971
INSERT INTO t1 (pseudo) VALUES ('test');
1972
INSERT INTO t1 (pseudo) VALUES ('test1');
1973
SELECT 1 as rnd1 from t1 where rand() > 2;
1976
CREATE TEMPORARY 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) default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
1977
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);
1978
CREATE TEMPORARY 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;
1979
INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);
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 >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;
1981
ERROR HY000: Received an invalid datetime value 'wrong-date-value'.
1982
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;
1983
gvid the_success the_fail the_size the_time
1985
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');
1986
INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);
1987
select wss_type from t1 where wss_type ='102935229216544106';
1989
select wss_type from t1 where wss_type ='102935229216544105';
1991
select wss_type from t1 where wss_type ='102935229216544104';
1993
select wss_type from t1 where wss_type ='102935229216544093';
1996
select wss_type from t1 where wss_type =102935229216544093;
2000
select 1+2,"aaaa",3.13*2.0 into @a,@b,@c;
2010
create table t1 (a int not null auto_increment primary key);
2011
insert into t1 values ();
2012
insert into t1 values ();
2013
insert into t1 values ();
2014
select * from (t1 as t2 left join t1 as t3 using (a)), t1;
2025
select * from t1, (t1 as t2 left join t1 as t3 using (a));
2036
select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;
2047
select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));
2058
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
2066
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2074
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
2079
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2084
select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
2092
select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2101
select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a );
2106
select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2111
select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
2116
select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
2121
select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
2130
select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2138
select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
2143
select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2148
select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1;
2153
select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));
2158
select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
2163
select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
2169
CREATE TEMPORARY 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;
2170
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);
2171
CREATE TEMPORARY TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;
2172
INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);
2173
select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5;
2181
create table t1 (id1 int NOT NULL);
2182
create table t2 (id2 int NOT NULL);
2183
create table t3 (id3 int NOT NULL);
2184
create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4));
2185
insert into t1 values (1);
2186
insert into t1 values (2);
2187
insert into t2 values (1);
2188
insert into t4 values (1,1);
2189
select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
2190
left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
2191
id1 id2 id3 id4 id44
2193
drop table t1,t2,t3,t4;
2194
create table t1(s varchar(10) not null);
2195
create table t2(s varchar(10) not null primary key);
2196
create table t3(s varchar(10) not null primary key);
2197
insert into t1 values ('one\t'), ('two\t');
2198
insert into t2 values ('one\r'), ('two\t');
2199
insert into t3 values ('one '), ('two\t');
2200
select * from t1 where s = 'one';
2202
select * from t2 where s = 'one';
2204
select * from t3 where s = 'one';
2207
select * from t1,t2 where t1.s = t2.s;
2210
select * from t2,t3 where t2.s = t3.s;
2213
drop table t1, t2, t3;
2214
create table t1 (a integer, b integer, index(a), index(b));
2215
create table t2 (c integer, d integer, index(c), index(d));
2216
insert into t1 values (1,2), (2,2), (3,2), (4,2);
2217
insert into t2 values (1,3), (2,3), (3,4), (4,4);
2218
select * from t1 left join t2 on a=c where d in (4);
2222
select * from t1 left join t2 on a=c where d = 4;
2227
CREATE TEMPORARY TABLE t1 (
2228
i int NOT NULL default '0',
2229
c char(10) NOT NULL default '',
2233
INSERT INTO t1 VALUES (1,'a');
2234
INSERT INTO t1 VALUES (2,'b');
2235
INSERT INTO t1 VALUES (3,'c');
2236
EXPLAIN SELECT i FROM t1 WHERE i=1;
2237
id select_type table type possible_keys key key_len ref rows Extra
2238
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
2240
CREATE TABLE t1 ( a BLOB, INDEX (a(20)) );
2241
CREATE TABLE t2 ( a BLOB, INDEX (a(20)) );
2242
INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
2243
INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
2244
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
2245
id select_type table type possible_keys key key_len ref rows Extra
2246
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2247
1 SIMPLE t2 ref a a 23 test.t1.a 1
2248
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;
2249
id select_type table type possible_keys key key_len ref rows Extra
2250
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2251
1 SIMPLE t2 ref a a 23 test.t1.a 1
2253
CREATE TABLE t1 ( city char(30) );
2254
INSERT INTO t1 VALUES ('London');
2255
INSERT INTO t1 VALUES ('Paris');
2256
SELECT * FROM t1 WHERE city='London';
2259
SELECT * FROM t1 WHERE city='london';
2262
EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london';
2263
id select_type table type possible_keys key key_len ref rows Extra
2264
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
2265
SELECT * FROM t1 WHERE city='London' AND city='london';
2268
EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2269
id select_type table type possible_keys key key_len ref rows Extra
2270
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
2271
SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2275
create table t1 (a int, b int);
2276
insert into t1 values (1,0), (1,1), (1,2);
2277
select a-b from t1 order by 1;
2282
select a-b , (a-b < 0) from t1 order by 1;
2287
select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;
2291
select a - b from t1 order by 1;
2297
create table t1 (a int);
2298
select all all * from t1;
2300
select distinct distinct * from t1;
2302
select all distinct * from t1;
2303
ERROR HY000: Incorrect usage of ALL and DISTINCT
2304
select distinct all * from t1;
2305
ERROR HY000: Incorrect usage of ALL and DISTINCT
2308
kunde_intern_id int NOT NULL default '0',
2309
kunde_id int NOT NULL default '0',
2310
FK_firma_id int NOT NULL default '0',
2311
aktuell enum('Ja','Nein') NOT NULL default 'Ja',
2312
vorname varchar(128) NOT NULL default '',
2313
nachname varchar(128) NOT NULL default '',
2314
geloescht enum('Ja','Nein') NOT NULL default 'Nein',
2315
firma varchar(128) NOT NULL default ''
2317
INSERT INTO t1 VALUES
2318
(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'),
2319
(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX');
2320
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1
2324
( '' != '' AND firma LIKE CONCAT('%', '', '%'))
2326
(vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2327
nachname LIKE CONCAT('%', '1Nachname', '%') AND
2328
'Vorname1' != '' AND 'xxxx' != '')
2332
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2336
kunde_id FK_firma_id aktuell vorname nachname geloescht
2337
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname,
2342
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2346
( '' != '' AND firma LIKE CONCAT('%', '', '%') )
2348
( vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2349
nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND
2354
kunde_id FK_firma_id aktuell vorname nachname geloescht
2355
SELECT COUNT(*) FROM t1 WHERE
2356
( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1))
2357
AND FK_firma_id = 2;
2361
CREATE TABLE t1 (b BIGINT NOT NULL, PRIMARY KEY (b));
2362
INSERT INTO t1 VALUES (0x4000000000000000);
2363
SELECT b FROM t1 WHERE b=0x4000000000000000;
2367
CREATE TABLE `t1` ( `gid` int default NULL, `uid` int default NULL);
2368
CREATE TABLE `t2` ( `ident` int default NULL, `level` char(16) default NULL);
2369
INSERT INTO `t2` VALUES (0,'READ');
2370
CREATE TABLE `t3` ( `id` int default NULL, `name` char(16) default NULL);
2371
INSERT INTO `t3` VALUES (1,'fs');
2372
select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);
2373
id name gid uid ident level
2374
1 fs NULL NULL 0 READ
2375
drop table t1,t2,t3;
2377
acct_id int NOT NULL default '0',
2378
profile_id int default NULL,
2379
UNIQUE KEY t1$acct_id (acct_id),
2380
KEY t1$profile_id (profile_id)
2382
INSERT INTO t1 VALUES (132,17),(133,18);
2384
profile_id int default NULL,
2385
queue_id int default NULL,
2386
seq int default NULL,
2387
KEY t2$queue_id (queue_id)
2389
INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);
2391
id int NOT NULL default '0',
2392
qtype int default NULL,
2393
seq int default NULL,
2394
warn_lvl int default NULL,
2395
crit_lvl int default NULL,
2396
rr1 int NOT NULL default '0',
2397
rr2 int default NULL,
2398
default_queue int NOT NULL default '0',
2399
KEY t3$qtype (qtype),
2402
INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0),
2403
(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0);
2404
SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q
2406
(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND
2407
(pq.queue_id = q.id) AND (q.rr1 <> 1);
2410
drop table t1,t2,t3;
2411
create table t1 (f1 int);
2412
insert into t1 values (1),(NULL);
2413
create table t2 (f2 int, f3 int, f4 int);
2414
create index idx1 on t2 (f4);
2415
insert into t2 values (1,2,3),(2,4,6);
2416
select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3)
2417
from t2 C where A.f4 = C.f4) or A.f3 IS NULL;
2422
create table t2 (a int);
2423
create index t2i on t2(a);
2424
insert into t2 values (0), (254), (255);
2425
select * from t2 where a > -1;
2431
CREATE TABLE t1 (a INT, b INT);
2432
(SELECT a, b AS c FROM t1) ORDER BY c+1;
2434
(SELECT a, b AS c FROM t1) ORDER BY b+1;
2436
SELECT a, b AS c FROM t1 ORDER BY c+1;
2438
SELECT a, b AS c FROM t1 ORDER BY b+1;
2441
create table t1(f1 int, f2 int);
2442
create table t2(f3 int);
2443
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1));
2445
select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1));
2447
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL));
2449
insert into t1 values(1,1),(2,null);
2450
insert into t2 values(2);
2451
select * from t1,t2 where f1=f3 and (f1,f2) = (2,null);
2453
select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null);
2457
create table t1 (f1 int not null auto_increment primary key, f2 varchar(10));
2458
create table t11 like t1;
2459
insert into t1 values(1,""),(2,"");
2460
show table status like 't1%';
2461
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
2462
# test t1 STANDARD PBXT # # # # #
2463
select 123 as a from t1 where f1 is null;
2466
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );
2467
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
2468
CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT );
2469
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);
2470
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2471
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2478
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2479
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c;
2485
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2486
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c;
2493
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1
2494
WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2499
DROP TABLE IF EXISTS t1, t2;
2500
create table t1 (f1 int primary key, f2 int);
2501
create table t2 (f3 int, f4 int, primary key(f3,f4));
2502
insert into t1 values (1,1);
2503
insert into t2 values (1,1),(1,2);
2504
select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1;
2508
create table t1 (f1 int,f2 int);
2509
insert into t1 values(1,1);
2510
create table t2 (f3 int, f4 int, primary key(f3,f4));
2511
insert into t2 values(1,1);
2512
select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2));
2516
CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));
2517
insert into t1 values (1,0,0),(2,0,0);
2518
CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));
2519
insert into t2 values (1,'',''), (2,'','');
2520
CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
2521
insert into t3 values (1,1),(1,2);
2522
DROP TABLE t1,t2,t3;
2523
CREATE TABLE t1 (a int, INDEX idx(a));
2524
INSERT INTO t1 VALUES (2), (3), (1);
2525
EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);
2526
id select_type table type possible_keys key key_len ref rows Extra
2527
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
2528
EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);
2529
ERROR 42000: Key 'a' doesn't exist in table 't1'
2530
EXPLAIN SELECT * FROM t1 FORCE INDEX (a);
2531
ERROR 42000: Key 'a' doesn't exist in table 't1'
2533
CREATE TABLE t1 (a int, b int);
2534
INSERT INTO t1 VALUES (1,1), (2,1), (4,10);
2535
CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b));
2536
INSERT INTO t2 VALUES (1,NULL), (2,10);
2537
ALTER TABLE t1 ENABLE KEYS;
2539
Note 1031 Table storage engine for 't1' doesn't have this option
2540
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2541
id select_type table type possible_keys key key_len ref rows Extra
2542
1 SIMPLE t2 ALL b NULL NULL NULL 2
2543
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2544
SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2550
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2551
id select_type table type possible_keys key key_len ref rows Extra
2552
1 SIMPLE t2 ALL b NULL NULL NULL 2
2553
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2554
SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2560
DROP TABLE IF EXISTS t1,t2;
2561
CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1));
2562
CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));
2563
INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);
2564
INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);
2565
select max(key1) from t1 where key1 <= 0.6158;
2568
select max(key2) from t2 where key2 <= 1.6158;
2571
select min(key1) from t1 where key1 >= 0.3762;
2574
select min(key2) from t2 where key2 >= 1.3762;
2577
select max(key1), min(key2) from t1, t2
2578
where key1 <= 0.6158 and key2 >= 1.3762;
2581
select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
2584
select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
2588
CREATE TABLE t1 (i BIGINT NOT NULL);
2589
INSERT INTO t1 VALUES (10);
2590
SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;
2591
i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01')
2594
CREATE TABLE t1 (c0 int);
2595
CREATE TABLE t2 (c0 int);
2596
INSERT INTO t1 VALUES(@@server_id);
2597
INSERT INTO t2 VALUES(@@server_id);
2598
SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@server_id);
2603
CREATE TEMPORARY TABLE t1 (
2604
K2C4 varchar(4) collate utf8_bin NOT NULL default '',
2605
K4N4 varchar(4) collate utf8_bin NOT NULL default '0000',
2606
F2I4 int NOT NULL default '0'
2608
INSERT INTO t1 VALUES
2609
('W%RT', '0100', 1),
2610
('W-RT', '0100', 1),
2611
('WART', '0100', 1),
2612
('WART', '0200', 1),
2613
('WERT', '0100', 2),
2616
('W_RT', '0100', 2),
2617
('WaRT', '0100', 3),
2618
('WART', '0300', 3),
2619
('WRT' , '0400', 3),
2620
('WURM', '0500', 3),
2622
('WA%T', '0700', 4),
2623
('WA_T', '0800', 4);
2624
SELECT K2C4, K4N4, F2I4 FROM t1
2625
WHERE K2C4 = 'WART' AND
2626
(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200'));
2629
SELECT K2C4, K4N4, F2I4 FROM t1
2630
WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200');
2636
create table t1 (a int, b int);
2637
create table t2 like t1;
2638
select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1;
2640
select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1;
2642
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;
2645
create table t1 (s1 varchar(5));
2646
insert into t1 values ('Wall');
2647
select min(s1) from t1 group by s1 with rollup;
2652
create temporary table t1 (s1 int) engine=myisam;
2653
insert into t1 values (0);
2654
select avg(distinct s1) from t1 group by s1 with rollup;
2659
create table t1 (s1 int);
2660
insert into t1 values (null),(1);
2661
select distinct avg(s1) as x from t1 group by s1 with rollup;
2666
CREATE TABLE t1 (a int);
2667
CREATE TABLE t2 (a int);
2668
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
2669
INSERT INTO t2 VALUES (2), (4), (6);
2670
SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2674
EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2675
id select_type table type possible_keys key key_len ref rows Extra
2676
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2677
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2678
EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a;
2679
id select_type table type possible_keys key key_len ref rows Extra
2680
1 SIMPLE t2 ALL NULL NULL NULL NULL 3
2681
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer
2683
select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
2684
x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0
2686
create table t1 (f1 varchar(6) default NULL, f2 int primary key not null);
2687
create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));
2688
insert into t1 values (" 2", 2);
2689
insert into t2 values (" 2", " one "),(" 2", " two ");
2690
select * from t1 left join t2 on f1 = f3;
2695
create table t1 (pk int primary key, b int);
2696
create table t2 (pk int primary key, c int);
2697
select pk from t1 inner join t2 using (pk);
2700
create table t1 (a int, t1_val int);
2701
create table t2 (b int, t2_val int);
2702
create table t3 (a int, b int);
2703
insert into t1 values (1,1),(2,2);
2704
insert into t2 values (1,1),(2,2),(3,3);
2705
insert into t3 values (1,1),(2,1),(3,1),(4,1);
2706
select * from t1 natural join t2 natural join t3;
2710
select * from t1 natural join t3 natural join t2;
2714
drop table t1, t2, t3;
2715
create table t1 (a char(1));
2716
create table t2 (a char(1));
2717
insert into t1 values ('a'),('b'),('c');
2718
insert into t2 values ('b'),('c'),('d');
2719
select a from t1 natural join t2;
2723
select * from t1 natural join t2 where a = 'b';
2727
CREATE TABLE t1 (`id` int);
2728
CREATE TABLE t2 (`id` int);
2729
CREATE TABLE t3 (`id` int);
2730
INSERT INTO t1 VALUES (1),(2),(3);
2731
INSERT INTO t2 VALUES (2);
2732
INSERT INTO t3 VALUES (3);
2733
SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
2734
ERROR 23000: Column 'id' in from clause is ambiguous
2735
SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);
2736
ERROR 23000: Column 'id' in from clause is ambiguous
2737
SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
2738
ERROR 23000: Column 'id' in from clause is ambiguous
2739
SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);
2740
ERROR 23000: Column 'id' in from clause is ambiguous
2741
drop table t1, t2, t3;
2742
create table t1 (a int,b int);
2743
create table t2 (a int,b int);
2744
insert into t1 values (1,10),(2,20),(3,30);
2745
insert into t2 values (1,10);
2746
select * from t1 inner join t2 using (A);
2749
select * from t1 inner join t2 using (a);
2753
create table t1 (a int, c int);
2754
create table t2 (b int);
2755
create table t3 (b int, a int);
2756
create table t4 (c int);
2757
insert into t1 values (1,1);
2758
insert into t2 values (1);
2759
insert into t3 values (1,1);
2760
insert into t4 values (1);
2761
select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
2764
select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
2765
ERROR 42S22: Unknown column 't1.a' in 'on clause'
2766
select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c);
2769
select * from t1 join t2 join t4 using (c);
2772
drop table t1, t2, t3, t4;
2773
create table t1(x int, y int);
2774
create table t2(x int, y int);
2775
create table t3(x int, primary key(x));
2776
insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6);
2777
insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6);
2778
insert into t3 values (1), (2), (3), (4), (5);
2779
select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y;
2789
drop table t1,t2,t3;
2790
create table t1 (id int not null default '0');
2791
insert into t1 values (123),(191),(192);
2792
create table t2 (id char(16) not null);
2793
insert into t2 values ('58013'),('58014'),('58015'),('58016');
2794
create table t3 (a_id int not null, b_id char(16));
2795
insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');
2797
from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id;
2801
from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id;
2804
drop table t1,t2,t3;
2805
create table t1 (a int);
2806
create table t2 (b int);
2807
create table t3 (c int);
2808
select * from t1 join t2 join t3 on (t1.a=t3.c);
2810
select * from t1 join t2 left join t3 on (t1.a=t3.c);
2812
select * from t1 join t2 right join t3 on (t1.a=t3.c);
2814
select * from t1 join t2 straight_join t3 on (t1.a=t3.c);
2816
drop table t1, t2 ,t3;
2817
create table t1(f1 int, f2 date);
2818
insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),
2819
(4,'2005-10-01'),(5,'2005-12-30');
2820
select * from t1 where f2 >= 0 order by f2;
2821
ERROR HY000: Received an invalid datetime value '0'.
2822
select * from t1 where f2 >= '0000-00-00' order by f2;
2823
ERROR HY000: Received an invalid datetime value '0000-00-00'.
2824
select * from t1 where f2 >= '2005-09-31' order by f2;
2825
ERROR HY000: Received an invalid datetime value '2005-09-31'.
2826
select * from t1 where f2 >= '2005-09-3a' order by f2;
2827
ERROR HY000: Received an invalid datetime value '2005-09-3a'.
2828
select * from t1 where f2 <= '2005-09-31' order by f2;
2829
ERROR HY000: Received an invalid datetime value '2005-09-31'.
2830
select * from t1 where f2 <= '2005-09-3a' order by f2;
2831
ERROR HY000: Received an invalid datetime value '2005-09-3a'.
2833
CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a));
2834
CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a));
2835
CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32),
2836
PRIMARY KEY(key_a,key_b));
2837
INSERT INTO t1 VALUES (0,'');
2838
INSERT INTO t1 VALUES (1,'i');
2839
INSERT INTO t1 VALUES (2,'j');
2840
INSERT INTO t1 VALUES (3,'k');
2841
INSERT INTO t2 VALUES (1,'r');
2842
INSERT INTO t2 VALUES (2,'s');
2843
INSERT INTO t2 VALUES (3,'t');
2844
INSERT INTO t3 VALUES (1,5,'x');
2845
INSERT INTO t3 VALUES (1,6,'y');
2846
INSERT INTO t3 VALUES (2,5,'xx');
2847
INSERT INTO t3 VALUES (2,6,'yy');
2848
INSERT INTO t3 VALUES (2,7,'zz');
2849
INSERT INTO t3 VALUES (3,5,'xxx');
2851
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
2852
INNER JOIN t3 ON t1.key_a = t3.key_a
2853
WHERE t2.key_a=2 and key_b=5;
2856
EXPLAIN SELECT t2.key_a,foo
2857
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
2858
INNER JOIN t3 ON t1.key_a = t3.key_a
2859
WHERE t2.key_a=2 and key_b=5;
2860
id select_type table type possible_keys key key_len ref rows Extra
2861
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
2862
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
2863
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
2865
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
2866
INNER JOIN t3 ON t1.key_a = t3.key_a
2867
WHERE t2.key_a=2 and key_b=5;
2870
EXPLAIN SELECT t2.key_a,foo
2871
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
2872
INNER JOIN t3 ON t1.key_a = t3.key_a
2873
WHERE t2.key_a=2 and key_b=5;
2874
id select_type table type possible_keys key key_len ref rows Extra
2875
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
2876
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
2877
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
2878
DROP TABLE t1,t2,t3;
2879
create table t1 (f1 int);
2880
insert into t1 values(1),(2);
2881
create table t2 (f2 int, f3 int, key(f2));
2882
insert into t2 values(1,1),(2,2);
2883
create table t3 (f4 int not null);
2884
insert into t3 values (2),(2),(2);
2885
select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1;
2889
drop table t1,t2,t3;
2890
create table t1 (f1 int unique);
2891
create table t2 (f2 int unique);
2892
create table t3 (f3 int unique);
2893
insert into t1 values(1),(2);
2894
insert into t2 values(1),(2);
2895
insert into t3 values(1),(NULL);
2896
select * from t3 where f3 is null;
2899
select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1;
2902
drop table t1,t2,t3;
2903
create table t1(f1 char, f2 char not null);
2904
insert into t1 values(null,'a');
2905
create table t2 (f2 char not null);
2906
insert into t2 values('b');
2907
select * from t1 left join t2 on f1=t2.f2 where t1.f2='a';
2911
select * from (select * left join t on f1=f2) tt;
2912
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'on f1=f2) tt' at line 1
2913
CREATE TABLE t1 (sku int PRIMARY KEY, pr int);
2914
CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255));
2915
INSERT INTO t1 VALUES
2916
(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10);
2917
INSERT INTO t2 VALUES
2918
(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),
2919
(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');
2920
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
2921
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
2922
sku sppr name sku pr
2926
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
2927
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
2928
id select_type table type possible_keys key key_len ref rows Extra
2929
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
2930
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where
2932
create table t1 (a int);
2933
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2934
create table t2 (a int, b int, c int, e int, primary key(a,b,c));
2935
insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C;
2937
Table Op Msg_type Msg_text
2938
test.t2 analyze note The storage engine for the table doesn't support analyze
2939
select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
2941
In next EXPLAIN, B.rows must be exactly 10:
2943
CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
2944
INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),
2945
(3,1), (5,1), (8,9), (2,2), (0,9);
2946
CREATE TABLE t2 (c int, d int, f int, INDEX(c,f));
2947
INSERT INTO t2 VALUES
2948
(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1),
2949
(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1),
2950
(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1);
2952
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
2953
id select_type table type possible_keys key key_len ref rows Extra
2954
1 SIMPLE t1 ALL PRIMARY,b NULL NULL NULL 10 Using where
2955
1 SIMPLE t2 ref c c 5 test.t1.a 1
2957
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
2958
id select_type table type possible_keys key key_len ref rows Extra
2959
1 SIMPLE t1 ALL PRIMARY,b NULL NULL NULL 10 Using where
2960
1 SIMPLE t2 ref c c 5 test.t1.a 1
2963
a int not null auto_increment primary key,
2968
a int not null auto_increment primary key,
2973
insert into t1 (b,c) values (0,1), (0,1);
2974
insert into t2 (b,c) values (0,1);
2975
select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d
2976
from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1
2977
where t1.b <> 1 order by t1.a;
2978
a t1.b + 0 t1.c + 0 a t2.b + 0 c d
2980
2 0 1 NULL NULL NULL NULL
2982
SELECT 0.9888889889 * 1.011111411911;
2983
0.9888889889 * 1.011111411911
2984
0.9998769417899202067879
2985
CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
2986
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
2987
CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
2988
INSERT INTO t2 VALUES
2989
(1), (1), (1), (1), (1), (1), (1), (1),
2993
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
2994
id select_type table type possible_keys key key_len ref rows Extra
2995
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
2996
1 SIMPLE t2 ref idx idx 4 const 1
2997
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
2998
id select_type table type possible_keys key key_len ref rows Extra
2999
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3000
1 SIMPLE t2 ref idx idx 4 const 1
3002
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
3003
INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
3004
CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
3005
INSERT INTO t2 VALUES (2,1), (3,2);
3006
CREATE TABLE t3 (d int, e int, INDEX idx1(d));
3007
INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
3009
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3011
id select_type table type possible_keys key key_len ref rows Extra
3012
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3013
1 SIMPLE t2 const idx1 NULL NULL NULL 1
3014
1 SIMPLE t3 ref idx1 idx1 5 const 1
3015
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3018
2 NULL NULL NULL 2 10
3019
2 NULL NULL NULL 2 20
3020
2 NULL NULL NULL 2 40
3021
2 NULL NULL NULL 2 50
3022
DROP TABLE t1,t2,t3;
3023
CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
3024
CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
3025
INSERT INTO t1 VALUES
3026
('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'),
3027
('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
3028
INSERT INTO t2 VALUES
3029
('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
3030
('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
3031
('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
3032
('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
3034
FROM t1 JOIN t2 ON t2.fk=t1.pk
3035
WHERE t2.fk < 'c' AND t2.pk=t1.fk;
3036
id select_type table type possible_keys key key_len ref rows Extra
3037
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 10 Using where
3038
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where
3040
FROM t1 JOIN t2 ON t2.fk=t1.pk
3041
WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
3042
id select_type table type possible_keys key key_len ref rows Extra
3043
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 10 Using where
3044
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where
3046
FROM t1 JOIN t2 ON t2.fk=t1.pk
3047
WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
3048
id select_type table type possible_keys key key_len ref rows Extra
3049
1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where
3050
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where
3052
CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
3053
CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
3054
PRIMARY KEY (a), UNIQUE KEY (b));
3055
INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
3056
INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
3057
EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
3058
id select_type table type possible_keys key key_len ref rows Extra
3059
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3060
1 SIMPLE t2 const b b 82 const 1
3062
CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
3063
CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
3064
CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
3065
INSERT INTO t1 VALUES
3066
(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
3067
(6,63,67), (5,55,58), (3,38,39), (8,81,89);
3068
INSERT INTO t2 VALUES
3069
(21,210), (41,410), (82,820), (83,830), (84,840),
3070
(65,650), (51,510), (37,370), (94,940), (76,760),
3071
(22,220), (33,330), (40,400), (95,950), (38,380),
3072
(67,670), (88,880), (57,570), (96,960), (97,970);
3073
INSERT INTO t3 VALUES
3074
(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
3075
(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
3076
(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
3077
(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
3079
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3080
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3081
t3.a=t2.a AND t3.c IN ('bb','ee');
3082
id select_type table type possible_keys key key_len ref rows Extra
3083
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3084
1 SIMPLE t3 range PRIMARY,ci ci 19 NULL 2 Using where
3085
1 SIMPLE t2 ALL si NULL NULL NULL 20 Using where; Using join buffer
3087
SELECT t3.a FROM t1,t2,t3
3088
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3089
t3.a=t2.a AND t3.c IN ('bb','ee') ;
3090
id select_type table type possible_keys key key_len ref rows Extra
3091
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3092
1 SIMPLE t3 range PRIMARY,ci ci 19 NULL 2 Using where
3093
1 SIMPLE t2 ref si,ai ai 5 test.t3.a 1 Using where
3095
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3096
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3097
t3.c IN ('bb','ee');
3098
id select_type table type possible_keys key key_len ref rows Extra
3099
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3100
1 SIMPLE t2 range si si 5 NULL 2 Using where
3101
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3103
SELECT t3.a FROM t1,t2,t3
3104
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3105
t3.c IN ('bb','ee');
3106
id select_type table type possible_keys key key_len ref rows Extra
3107
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3108
1 SIMPLE t2 range si,ai si 5 NULL 2 Using where
3109
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3110
DROP TABLE t1,t2,t3;
3111
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
3112
CREATE TABLE t2 ( f11 int PRIMARY KEY );
3113
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);
3114
INSERT INTO t2 VALUES (62);
3115
SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;
3116
f1 f2 f3 f4 f5 f6 checked_out f11
3119
DROP TABLE IF EXISTS t1;
3120
CREATE TABLE t1(a int);
3121
INSERT into t1 values (1), (2), (3);
3122
SELECT * FROM t1 LIMIT 2, -1;
3123
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '-1' at line 1
3126
ID_with_null int NULL,
3127
ID_better int NOT NULL,
3128
INDEX idx1 (ID_with_null),
3129
INDEX idx2 (ID_better)
3131
INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3);
3132
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3133
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3134
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3135
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3136
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3137
SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL;
3140
SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3143
EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3144
id select_type table type possible_keys key key_len ref rows Extra
3145
1 SIMPLE t1 ref idx1,idx2 idx1 5 const 1 Using where
3146
DROP INDEX idx1 ON t1;
3147
CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);
3148
EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3149
id select_type table type possible_keys key key_len ref rows Extra
3150
1 SIMPLE t1 ref idx1,idx2 idx1 5 const 1 Using where
3153
ID1_with_null int NULL,
3154
ID2_with_null int NULL,
3155
ID_better int NOT NULL,
3156
INDEX idx1 (ID1_with_null, ID2_with_null),
3157
INDEX idx2 (ID_better)
3159
INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3),
3160
(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);
3161
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3162
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3163
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3164
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3165
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3166
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3167
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
3170
SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL;
3173
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;
3176
SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3179
EXPLAIN SELECT * FROM t1
3180
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3181
id select_type table type possible_keys key key_len ref rows Extra
3182
1 SIMPLE t1 ref idx1,idx2 idx1 10 const,const # Using where
3183
EXPLAIN SELECT * FROM t1
3184
WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;
3185
id select_type table type possible_keys key key_len ref rows Extra
3186
1 SIMPLE t1 ref idx1,idx2 idx1 5 const # Using where
3187
EXPLAIN SELECT * FROM t1
3188
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3189
id select_type table type possible_keys key key_len ref rows Extra
3190
1 SIMPLE t1 ref idx1,idx2 idx1 10 const,const # Using where
3191
DROP INDEX idx1 ON t1;
3192
CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);
3193
EXPLAIN SELECT * FROM t1
3194
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3195
id select_type table type possible_keys key key_len ref rows Extra
3196
1 SIMPLE t1 ref idx1,idx2 idx1 10 const,const 1 Using where
3197
EXPLAIN SELECT * FROM t1
3198
WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ;
3199
id select_type table type possible_keys key key_len ref rows Extra
3200
1 SIMPLE t1 ref idx1,idx2 idx1 10 const,const 1 Using where
3201
EXPLAIN SELECT * FROM t1
3202
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3203
id select_type table type possible_keys key key_len ref rows Extra
3204
1 SIMPLE t1 ref idx1,idx2 idx1 10 const,const 1 Using where
3205
EXPLAIN SELECT * FROM t1
3206
WHERE ID_better=1 AND ID1_with_null IS NULL AND
3207
(ID2_with_null=1 OR ID2_with_null=2);
3208
id select_type table type possible_keys key key_len ref rows Extra
3209
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3211
CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
3212
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
3214
Table Op Msg_type Msg_text
3215
test.t1 analyze note The storage engine for the table doesn't support analyze
3216
CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
3217
INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
3218
INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
3220
Table Op Msg_type Msg_text
3221
test.t2 analyze note The storage engine for the table doesn't support analyze
3223
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3224
AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3225
AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3226
id select_type table type possible_keys key key_len ref rows Extra
3227
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
3228
1 SIMPLE t1 ALL ts NULL NULL NULL 2 Using where
3229
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3230
AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3231
AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3233
30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00
3235
create table t1 (a bigint);
3236
insert into t1 values
3237
(if(1, 92233720368547758, 1)),
3238
(case when 1 then 92233720368547758 else 1 end),
3239
(coalesce(92233720368547758, 1));
3246
create table t1 select
3247
if(1, 9223372036854775808, 1) i,
3248
case when 1 then 9223372036854775808 else 1 end c,
3249
coalesce(9223372036854775808, 1) co;
3250
show create table t1;
3252
t1 CREATE TABLE `t1` (
3253
`i` decimal(19,0) NOT NULL,
3254
`c` decimal(19,0) NOT NULL,
3255
`co` decimal(19,0) NOT NULL
3259
if(1, 1111111111111111111, 1) i,
3260
case when 1 then 1111111111111111111 else 1 end c,
3261
coalesce(1111111111111111111, 1) co;
3263
1111111111111111111 1111111111111111111 1111111111111111111
3264
CREATE TABLE t1 (name varchar(255));
3265
CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
3266
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
3267
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
3268
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3269
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3279
SELECT * FROM t2 ORDER BY name;
3288
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3297
EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3298
id select_type table type possible_keys key key_len ref rows Extra
3299
1 SIMPLE t2 ref name name 15 const 1 Using where
3300
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3305
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3306
id select_type table type possible_keys key key_len ref rows Extra
3307
1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where
3308
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3314
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3315
id select_type table type possible_keys key key_len ref rows Extra
3316
1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using filesort
3317
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3323
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3324
id select_type table type possible_keys key key_len ref rows Extra
3325
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3326
1 SIMPLE t2 ref name name 15 test.t1.name 1
3327
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3338
CREATE TABLE t1 (name text);
3339
CREATE TABLE t2 (name text, n int, KEY (name(3)));
3340
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
3341
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
3342
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3343
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3353
SELECT * FROM t2 ORDER BY name;
3362
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3371
EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3372
id select_type table type possible_keys key key_len ref rows Extra
3373
1 SIMPLE t2 ref name name 15 const 1 Using where
3374
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3379
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3380
id select_type table type possible_keys key key_len ref rows Extra
3381
1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where
3382
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3388
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3389
id select_type table type possible_keys key key_len ref rows Extra
3390
1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using filesort
3391
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3397
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3398
id select_type table type possible_keys key key_len ref rows Extra
3399
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3400
1 SIMPLE t2 ref name name 15 test.t1.name 1
3401
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3413
access_id int NOT NULL default '0',
3414
name varchar(20) default NULL,
3415
rank int NOT NULL default '0',
3419
faq_group_id int NOT NULL default '0',
3420
faq_id int NOT NULL default '0',
3421
access_id int default NULL,
3422
UNIQUE KEY idx1 (faq_id),
3423
KEY idx2 (faq_group_id,faq_id)
3425
INSERT INTO t1 VALUES
3426
(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
3427
INSERT INTO t2 VALUES
3428
(261,265,1),(490,494,1);
3430
FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
3431
ON (t1.access_id = t2.access_id)
3433
ON (t.faq_group_id = t2.faq_group_id AND
3434
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3436
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3440
FROM t1 INNER JOIN t2
3441
ON (t1.access_id = t2.access_id)
3443
ON (t.faq_group_id = t2.faq_group_id AND
3444
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3446
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3450
CREATE TABLE t1 (a INT, b INT, KEY inx (b,a));
3451
INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7);
3452
EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2
3453
ON ( f1.b=f2.b AND f1.a<f2.a )
3454
WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);
3455
id select_type table type possible_keys key key_len ref rows Extra
3456
1 SIMPLE f1 ALL inx NULL NULL NULL 7 Using where
3457
1 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where
3459
CREATE TABLE t1 (c1 INT, c2 INT);
3460
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3461
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;
3462
id select_type table type possible_keys key key_len ref rows Extra
3463
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
3464
31 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3465
32 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3466
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;
3467
ERROR HY000: Too high level of nesting for select
3470
c1 int NOT NULL AUTO_INCREMENT,
3471
c2 varchar(1000) DEFAULT NULL,
3472
c3 bigint DEFAULT NULL,
3473
c4 bigint DEFAULT NULL,
3488
join_0.c1=join_1.c1 AND
3489
join_1.c1=join_2.c1 AND
3490
join_2.c1=join_3.c1 AND
3491
join_3.c1=join_4.c1 AND
3492
join_4.c1=join_5.c1 AND
3493
join_5.c1=join_6.c1 AND
3502
join_5.c2 <> '?' AND
3503
join_6.c2 <> '?' AND
3504
join_7.c2 >= '?' AND
3505
join_0.c1=join_1.c1 AND
3506
join_1.c1=join_2.c1 AND
3507
join_2.c1=join_3.c1 AND
3508
join_3.c1=join_4.c1 AND
3509
join_4.c1=join_5.c1 AND
3510
join_5.c1=join_6.c1 AND
3518
id select_type table type possible_keys key key_len ref rows filtered Extra
3519
1 SIMPLE join_0 ALL PRIMARY NULL NULL NULL 0 0.00 Using temporary; Using filesort
3520
1 SIMPLE join_1 eq_ref PRIMARY PRIMARY 4 test.join_0.c1 1 100.00
3521
1 SIMPLE join_2 eq_ref PRIMARY PRIMARY 4 test.join_1.c1 1 100.00 Using where
3522
1 SIMPLE join_3 eq_ref PRIMARY PRIMARY 4 test.join_2.c1 1 100.00 Using where
3523
1 SIMPLE join_4 eq_ref PRIMARY PRIMARY 4 test.join_3.c1 1 100.00 Using where
3524
1 SIMPLE join_5 eq_ref PRIMARY PRIMARY 4 test.join_4.c1 1 100.00 Using where
3525
1 SIMPLE join_6 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where
3526
1 SIMPLE join_7 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where
3528
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`
3531
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`
3537
Warning 1474 Name ' ' has become ''
3542
Warning 1474 Name ' ' has become ''
3547
Warning 1466 Leading spaces are removed from name ' x'
3548
CREATE TABLE t1 (c11 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
3549
CREATE TABLE t2 (c21 INT NOT NULL,
3550
c22 INT DEFAULT NULL,
3552
CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,
3553
c32 INT DEFAULT NULL,
3556
KEY (c33, c34, c32));
3557
INSERT INTO t1 values (),(),(),(),();
3558
INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;
3559
INSERT INTO t3 VALUES (1, 1, 1, 0),
3564
SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
3565
t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
3566
t3.c33 = 1 AND t2.c22 in (1, 3)
3575
SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
3576
t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
3577
t3.c33 = 1 AND t2.c22 in (1, 3)
3586
DROP TABLE t1, t2, t3;
3589
# Bug#30736: Row Size Too Large Error Creating a Table and
3592
DROP TABLE IF EXISTS t1;
3593
DROP TABLE IF EXISTS t2;
3599
INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5);
3622
CREATE TABLE t1 (c1 BIGINT NOT NULL);
3623
INSERT INTO t1 (c1) VALUES (1);
3624
SELECT * FROM t1 WHERE c1 > NULL + 1;
3628
CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY);
3629
INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0');
3630
SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar');
3634
CREATE TABLE t1 (a INT, b INT);
3635
CREATE TABLE t2 (a INT, c INT, KEY(a));
3636
INSERT INTO t1 VALUES (1, 1), (2, 2);
3637
INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
3638
(2, 1), (2, 2), (2, 3), (2, 4), (2, 5),
3639
(3, 1), (3, 2), (3, 3), (3, 4), (3, 5),
3640
(4, 1), (4, 2), (4, 3), (4, 4), (4, 5);
3642
SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;
3646
SHOW STATUS LIKE 'Handler_read%';
3648
Handler_read_first 0
3653
Handler_read_rnd_next 9
3656
create table t1(a INT, KEY (a));
3657
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
3658
SELECT a FROM t1 ORDER BY a LIMIT 2;
3662
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;
3667
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;
3673
CREATE TABLE t1 (a INT);
3674
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
3675
CREATE TABLE t2 (b INT);
3676
INSERT INTO t2 VALUES (2);
3677
SELECT * FROM t1 WHERE a = 1 + 1;
3680
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1;
3681
id select_type table type possible_keys key key_len ref rows filtered Extra
3682
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where
3684
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1)))
3685
SELECT * FROM t1 HAVING a = 1 + 1;
3688
EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1;
3689
id select_type table type possible_keys key key_len ref rows filtered Extra
3690
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00
3692
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1)))
3693
SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
3696
EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
3697
id select_type table type possible_keys key key_len ref rows filtered Extra
3698
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00
3699
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer
3701
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = (`test`.`t2`.`b` + <cache>((1 + 1))))
3702
SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
3705
EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
3706
id select_type table type possible_keys key key_len ref rows filtered Extra
3707
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00
3708
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00
3710
Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = (`test`.`t2`.`b` + 1))) where 1
3711
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
3712
id select_type table type possible_keys key key_len ref rows filtered Extra
3713
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where
3715
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00')))
added
removed
tests/r/pbxt/select.result
