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 smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
5
Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
7
INSERT INTO t1 VALUES (9410,9412);
18
auto int not null auto_increment,
19
fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
20
companynr tinyint(2) unsigned zerofill DEFAULT '00' 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
explain select t2.fld3 from t2 where fld3 = 'honeysuckle';
131
id select_type table type possible_keys key key_len ref rows Extra
132
1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
133
explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
134
id select_type table type possible_keys key key_len ref rows Extra
135
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
136
explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
137
id select_type table type possible_keys key key_len ref rows Extra
138
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
139
explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
140
id select_type table type possible_keys key key_len ref rows Extra
141
1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
142
explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';
143
id select_type table type possible_keys key key_len ref rows Extra
144
1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
145
explain select fld3 from t2 ignore index (fld3,not_used);
146
ERROR 42000: Key 'not_used' doesn't exist in table 't2'
147
explain select fld3 from t2 use index (not_used);
148
ERROR 42000: Key 'not_used' doesn't exist in table 't2'
149
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
153
explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
154
id select_type table type possible_keys key key_len ref rows Extra
155
1 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index
156
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
161
select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';
169
select fld1 from t2 where fld1=250501 or fld1="250502";
173
explain select fld1 from t2 where fld1=250501 or fld1="250502";
174
id select_type table type possible_keys key key_len ref rows Extra
175
1 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index
176
select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
182
explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
183
id select_type table type possible_keys key key_len ref rows Extra
184
1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index
185
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
218
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
220
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
223
select fld1,fld3 from t2 where fld1 like "25050%";
230
select fld1,fld3 from t2 where fld1 like "25050_";
237
select distinct companynr from t2;
251
select distinct companynr from t2 order by companynr;
265
select distinct companynr from t2 order by companynr desc;
279
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";
287
select distinct fld3 from t2 where companynr = 34 order by fld3;
359
select distinct fld3 from t2 limit 10;
371
select distinct fld3 from t2 having fld3 like "A%" limit 10;
383
select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
447
select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10;
459
select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
471
select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
485
name char(32) not null,
486
companynr int not null,
492
create temporary table tmp engine = myisam select * from t3;
493
insert into t3 select * from tmp;
494
insert into tmp select * from t3;
495
insert into t3 select * from tmp;
496
insert into tmp select * from t3;
497
insert into t3 select * from tmp;
498
insert into tmp select * from t3;
499
insert into t3 select * from tmp;
500
insert into tmp select * from t3;
501
insert into t3 select * from tmp;
502
insert into tmp select * from t3;
503
insert into t3 select * from tmp;
504
insert into tmp select * from t3;
505
insert into t3 select * from tmp;
506
insert into tmp select * from t3;
507
insert into t3 select * from tmp;
508
insert into tmp select * from t3;
509
insert into t3 select * from tmp;
510
alter table t3 add t2nr int not null auto_increment primary key first;
512
SET SQL_BIG_TABLES=1;
513
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
516
abrogating abrogating
517
admonishing admonishing
522
analyzable analyzable
525
SET SQL_BIG_TABLES=0;
526
select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
527
concat(fld3," ",fld3)
529
abrogating abrogating
530
admonishing admonishing
535
analyzable analyzable
538
select distinct fld5 from t2 limit 10;
550
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
562
SET SQL_BIG_TABLES=1;
563
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
575
SET SQL_BIG_TABLES=0;
576
select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
577
fld3 repeat("a",length(fld3)) count(*)
581
congresswoman aaaaaaaaaaaaa 1
582
contrition aaaaaaaaaa 1
584
cultivation aaaaaaaaaaa 1
585
definiteness aaaaaaaaaaaa 1
586
demultiplex aaaaaaaaaaa 1
587
disappointing aaaaaaaaaaaaa 1
588
select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
589
companynr rtrim(space(512+companynr))
597
select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
599
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
600
id select_type table type possible_keys key key_len ref rows Extra
601
1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
602
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
603
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
604
id select_type table type possible_keys key key_len ref rows Extra
605
1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
606
1 SIMPLE t3 ref period period 4 test.t1.period 4181
607
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
608
id select_type table type possible_keys key key_len ref rows Extra
609
1 SIMPLE t3 index period period 4 NULL 1
610
1 SIMPLE t1 ref period period 4 test.t3.period 4181
611
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
612
id select_type table type possible_keys key key_len ref rows Extra
613
1 SIMPLE t1 index period period 4 NULL 1
614
1 SIMPLE t3 ref period period 4 test.t1.period 4181
615
select period from t1;
618
select period from t1 where period=1900;
620
select fld3,period from t1,t2 where fld1 = 011401 order by period;
623
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
626
explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
627
id select_type table type possible_keys key key_len ref rows Extra
628
1 SIMPLE t2 const fld1 fld1 4 const 1
629
1 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1
630
select fld3,period from t2,t1 where companynr*10 = 37*10;
810
interrelationships 9410
1010
electroencephalography 9410
1220
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;
1221
fld3 period price price2
1222
admonishing 1002 28357832 8723648
1223
analyzable 1002 28357832 8723648
1224
annihilates 1001 5987435 234724
1225
Antares 1002 28357832 8723648
1226
astound 1001 5987435 234724
1227
audiology 1001 5987435 234724
1228
Augustine 1002 28357832 8723648
1229
Baird 1002 28357832 8723648
1230
bewilderingly 1001 5987435 234724
1231
breaking 1001 5987435 234724
1232
Conley 1001 5987435 234724
1233
dentally 1002 28357832 8723648
1234
dissociate 1002 28357832 8723648
1235
elite 1001 5987435 234724
1236
eschew 1001 5987435 234724
1237
Eulerian 1001 5987435 234724
1238
flanking 1001 5987435 234724
1239
foldout 1002 28357832 8723648
1240
funereal 1002 28357832 8723648
1241
galling 1002 28357832 8723648
1242
Graves 1001 5987435 234724
1243
grazing 1001 5987435 234724
1244
groupings 1001 5987435 234724
1245
handgun 1001 5987435 234724
1246
humility 1002 28357832 8723648
1247
impulsive 1002 28357832 8723648
1248
inch 1001 5987435 234724
1249
intelligibility 1001 5987435 234724
1250
jarring 1001 5987435 234724
1251
lawgiver 1001 5987435 234724
1252
lectured 1002 28357832 8723648
1253
Merritt 1002 28357832 8723648
1254
neonatal 1001 5987435 234724
1255
offload 1002 28357832 8723648
1256
parters 1002 28357832 8723648
1257
pityingly 1002 28357832 8723648
1258
puddings 1002 28357832 8723648
1259
Punjab 1001 5987435 234724
1260
quitter 1002 28357832 8723648
1261
realtor 1001 5987435 234724
1262
relaxing 1001 5987435 234724
1263
repetitions 1001 5987435 234724
1264
resumes 1001 5987435 234724
1265
Romans 1002 28357832 8723648
1266
rusting 1001 5987435 234724
1267
scholastics 1001 5987435 234724
1268
skulking 1002 28357832 8723648
1269
stated 1002 28357832 8723648
1270
suites 1002 28357832 8723648
1271
sureties 1001 5987435 234724
1272
testicle 1002 28357832 8723648
1273
tinily 1002 28357832 8723648
1274
tragedies 1001 5987435 234724
1275
trimmings 1001 5987435 234724
1276
vacuuming 1001 5987435 234724
1277
ventilate 1001 5987435 234724
1278
wallet 1001 5987435 234724
1279
Weissmuller 1002 28357832 8723648
1280
Wotan 1002 28357832 8723648
1281
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;
1282
fld1 fld3 period price price2
1283
018201 relaxing 1001 5987435 234724
1284
018601 vacuuming 1001 5987435 234724
1285
018801 inch 1001 5987435 234724
1286
018811 repetitions 1001 5987435 234724
1288
companynr tinyint(2) unsigned zerofill NOT NULL default '00',
1289
companyname char(30) NOT NULL default '',
1290
PRIMARY KEY (companynr),
1291
UNIQUE KEY companyname(companyname)
1292
) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
1293
select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1294
companynr companyname
1307
select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1308
companynr companyname
1321
select * from t1,t1 t12;
1322
Period Varor_period Period Varor_period
1324
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;
1351
insert into t2 (fld1, companynr) values (999999,99);
1352
select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1353
companynr companyname
1355
select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1358
explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1359
id select_type table type possible_keys key key_len ref rows Extra
1360
1 SIMPLE t2 ALL NULL NULL NULL NULL 1200
1361
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists
1362
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1363
id select_type table type possible_keys key key_len ref rows Extra
1364
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1365
1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists
1366
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1367
companynr companyname
1368
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1371
explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1372
id select_type table type possible_keys key key_len ref rows Extra
1373
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1374
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
1375
id select_type table type possible_keys key key_len ref rows Extra
1376
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1377
delete from t2 where fld1=999999;
1378
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1379
id select_type table type possible_keys key key_len ref rows Extra
1380
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1381
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1382
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1383
id select_type table type possible_keys key key_len ref rows Extra
1384
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1385
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1386
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1387
id select_type table type possible_keys key key_len ref rows Extra
1388
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1389
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1390
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
1391
id select_type table type possible_keys key key_len ref rows Extra
1392
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1393
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1394
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1395
id select_type table type possible_keys key key_len ref rows Extra
1396
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1397
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1398
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1399
id select_type table type possible_keys key key_len ref rows Extra
1400
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1401
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1402
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1403
id select_type table type possible_keys key key_len ref rows Extra
1404
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1405
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1406
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1407
id select_type table type possible_keys key key_len ref rows Extra
1408
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12
1409
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1410
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1411
id select_type table type possible_keys key key_len ref rows Extra
1412
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1413
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1414
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1415
id select_type table type possible_keys key key_len ref rows Extra
1416
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1417
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1418
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1419
id select_type table type possible_keys key key_len ref rows Extra
1420
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1421
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1422
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
1423
id select_type table type possible_keys key key_len ref rows Extra
1424
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where
1425
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1426
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1430
explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1431
id select_type table type possible_keys key key_len ref rows Extra
1432
1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary
1433
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer
1434
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;
1435
fld1 companynr fld3 period
1436
038008 37 reporters 1008
1437
038208 37 Selfridge 1008
1438
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;
1439
fld1 companynr fld3 period
1440
038008 37 reporters 1008
1441
038208 37 Selfridge 1008
1442
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;
1443
fld1 companynr fld3 period
1444
038008 37 reporters 1008
1445
038208 37 Selfridge 1008
1446
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);
1449
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)));
1452
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;
1458
select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1462
select fld1 from t2 where fld1 between 250502 and 250504;
1467
select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
1478
select count(*) from t1;
1481
select companynr,count(*),sum(fld1) from t2 group by companynr;
1482
companynr count(*) sum(fld1)
1495
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1502
select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1503
count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1504
70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1505
explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1506
id select_type table type possible_keys key key_len ref rows filtered Extra
1507
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
1509
Note 1003 select count(0) AS "count(*)",min("test"."t2"."fld4") AS "min(fld4)",max("test"."t2"."fld4") AS "max(fld4)",sum("test"."t2"."fld1") AS "sum(fld1)",avg("test"."t2"."fld1") AS "avg(fld1)",std("test"."t2"."fld1") AS "std(fld1)",variance("test"."t2"."fld1") AS "variance(fld1)" from "test"."t2" where (("test"."t2"."companynr" = 34) and ("test"."t2"."fld4" <> ''))
1510
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1511
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1512
00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
1513
29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026
1514
34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1515
select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1516
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1517
37 1 1 5987435 5987435 5987435 5987435.0000
1518
37 2 1 28357832 28357832 28357832 28357832.0000
1519
37 3 1 39654943 39654943 39654943 39654943.0000
1520
37 11 1 5987435 5987435 5987435 5987435.0000
1521
37 12 1 28357832 28357832 28357832 28357832.0000
1522
37 13 1 39654943 39654943 39654943 39654943.0000
1523
37 21 1 5987435 5987435 5987435 5987435.0000
1524
37 22 1 28357832 28357832 28357832 28357832.0000
1525
37 23 1 39654943 39654943 39654943 39654943.0000
1526
37 31 1 5987435 5987435 5987435 5987435.0000
1527
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;
1528
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1529
37 1 1 5987435 5987435 5987435 5987435.0000
1530
37 2 1 28357832 28357832 28357832 28357832.0000
1531
37 3 1 39654943 39654943 39654943 39654943.0000
1532
37 11 1 5987435 5987435 5987435 5987435.0000
1533
37 12 1 28357832 28357832 28357832 28357832.0000
1534
37 13 1 39654943 39654943 39654943 39654943.0000
1535
37 21 1 5987435 5987435 5987435 5987435.0000
1536
37 22 1 28357832 28357832 28357832 28357832.0000
1537
37 23 1 39654943 39654943 39654943 39654943.0000
1538
37 31 1 5987435 5987435 5987435 5987435.0000
1539
select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
1540
companynr count(price) sum(price) min(price) max(price) avg(price)
1541
37 12543 309394878010 5987435 39654943 24666736.6667
1542
78 8362 414611089292 726498 98439034 49582766.0000
1543
101 4181 3489454238 834598 834598 834598.0000
1544
154 4181 4112197254950 983543950 983543950 983543950.0000
1545
311 4181 979599938 234298 234298 234298.0000
1546
447 4181 9929180954 2374834 2374834 2374834.0000
1547
512 4181 3288532102 786542 786542 786542.0000
1548
select distinct mod(companynr,10) from t4 group by companynr;
1559
select distinct 1 from t4 group by companynr;
1562
select count(distinct fld1) from t2;
1563
count(distinct fld1)
1565
select companynr,count(distinct fld1) from t2 group by companynr;
1566
companynr count(distinct fld1)
1579
select companynr,count(*) from t2 group by companynr;
1593
select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1594
companynr count(distinct concat(fld1,repeat(65,1000)))
1607
select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1608
companynr count(distinct concat(fld1,repeat(65,200)))
1621
select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1622
companynr count(distinct floor(fld1/100))
1635
select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
1636
companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))
1649
select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1652
select name,count(*) from t3 where name='cloakroom' group by name;
1655
select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1658
select count(*) from t3 where name='cloakroom' and price2=823742;
1661
select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1664
select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1673
select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1676
select companynr|0,companyname from t4 group by 1;
1677
companynr|0 companyname
1690
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1691
companynr companyname count(*)
1704
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1707
select sum(Period)/count(*) from t1;
1708
sum(Period)/count(*)
1710
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;
1711
companynr count sum diff func
1712
37 12543 309394878010 0.0000 464091
1713
78 8362 414611089292 0.0000 652236
1714
101 4181 3489454238 0.0000 422281
1715
154 4181 4112197254950 0.0000 643874
1716
311 4181 979599938 0.0000 1300291
1717
447 4181 9929180954 0.0000 1868907
1718
512 4181 3288532102 0.0000 2140672
1719
select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
1722
select companynr,count(*) from t2 group by companynr order by 2 desc;
1736
select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1744
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;
1745
fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)
1746
teethe 000001 1 5987435 5987435 5987435 5987435.0000
1747
dreaded 011401 1 5987435 5987435 5987435 5987435.0000
1748
scholastics 011402 1 28357832 28357832 28357832 28357832.0000
1749
audiology 011403 1 39654943 39654943 39654943 39654943.0000
1750
wallet 011501 1 5987435 5987435 5987435 5987435.0000
1751
parters 011701 1 5987435 5987435 5987435 5987435.0000
1752
eschew 011702 1 28357832 28357832 28357832 28357832.0000
1753
quitter 011703 1 39654943 39654943 39654943 39654943.0000
1754
neat 012001 1 5987435 5987435 5987435 5987435.0000
1755
Steinberg 012003 1 39654943 39654943 39654943 39654943.0000
1756
balled 012301 1 5987435 5987435 5987435 5987435.0000
1757
persist 012302 1 28357832 28357832 28357832 28357832.0000
1758
attainments 012303 1 39654943 39654943 39654943 39654943.0000
1759
capably 012501 1 5987435 5987435 5987435 5987435.0000
1760
impulsive 012602 1 28357832 28357832 28357832 28357832.0000
1761
starlet 012603 1 39654943 39654943 39654943 39654943.0000
1762
featherweight 012701 1 5987435 5987435 5987435 5987435.0000
1763
pessimist 012702 1 28357832 28357832 28357832 28357832.0000
1764
daughter 012703 1 39654943 39654943 39654943 39654943.0000
1765
lawgiver 013601 1 5987435 5987435 5987435 5987435.0000
1766
stated 013602 1 28357832 28357832 28357832 28357832.0000
1767
readable 013603 1 39654943 39654943 39654943 39654943.0000
1768
testicle 013801 1 5987435 5987435 5987435 5987435.0000
1769
Parsifal 013802 1 28357832 28357832 28357832 28357832.0000
1770
leavings 013803 1 39654943 39654943 39654943 39654943.0000
1771
squeaking 013901 1 5987435 5987435 5987435 5987435.0000
1772
contrasted 016001 1 5987435 5987435 5987435 5987435.0000
1773
leftover 016201 1 5987435 5987435 5987435 5987435.0000
1774
whiteners 016202 1 28357832 28357832 28357832 28357832.0000
1775
erases 016301 1 5987435 5987435 5987435 5987435.0000
1776
Punjab 016302 1 28357832 28357832 28357832 28357832.0000
1777
Merritt 016303 1 39654943 39654943 39654943 39654943.0000
1778
sweetish 018001 1 5987435 5987435 5987435 5987435.0000
1779
dogging 018002 1 28357832 28357832 28357832 28357832.0000
1780
scornfully 018003 1 39654943 39654943 39654943 39654943.0000
1781
fetters 018012 1 28357832 28357832 28357832 28357832.0000
1782
bivalves 018013 1 39654943 39654943 39654943 39654943.0000
1783
skulking 018021 1 5987435 5987435 5987435 5987435.0000
1784
flint 018022 1 28357832 28357832 28357832 28357832.0000
1785
flopping 018023 1 39654943 39654943 39654943 39654943.0000
1786
Judas 018032 1 28357832 28357832 28357832 28357832.0000
1787
vacuuming 018033 1 39654943 39654943 39654943 39654943.0000
1788
medical 018041 1 5987435 5987435 5987435 5987435.0000
1789
bloodbath 018042 1 28357832 28357832 28357832 28357832.0000
1790
subschema 018043 1 39654943 39654943 39654943 39654943.0000
1791
interdependent 018051 1 5987435 5987435 5987435 5987435.0000
1792
Graves 018052 1 28357832 28357832 28357832 28357832.0000
1793
neonatal 018053 1 39654943 39654943 39654943 39654943.0000
1794
sorters 018061 1 5987435 5987435 5987435 5987435.0000
1795
epistle 018062 1 28357832 28357832 28357832 28357832.0000
1796
Conley 018101 1 5987435 5987435 5987435 5987435.0000
1797
lectured 018102 1 28357832 28357832 28357832 28357832.0000
1798
Abraham 018103 1 39654943 39654943 39654943 39654943.0000
1799
cage 018201 1 5987435 5987435 5987435 5987435.0000
1800
hushes 018202 1 28357832 28357832 28357832 28357832.0000
1801
Simla 018402 1 28357832 28357832 28357832 28357832.0000
1802
reporters 018403 1 39654943 39654943 39654943 39654943.0000
1803
coexist 018601 1 5987435 5987435 5987435 5987435.0000
1804
Beebe 018602 1 28357832 28357832 28357832 28357832.0000
1805
Taoism 018603 1 39654943 39654943 39654943 39654943.0000
1806
Connally 018801 1 5987435 5987435 5987435 5987435.0000
1807
fetched 018802 1 28357832 28357832 28357832 28357832.0000
1808
checkpoints 018803 1 39654943 39654943 39654943 39654943.0000
1809
gritty 018811 1 5987435 5987435 5987435 5987435.0000
1810
firearm 018812 1 28357832 28357832 28357832 28357832.0000
1811
minima 019101 1 5987435 5987435 5987435 5987435.0000
1812
Selfridge 019102 1 28357832 28357832 28357832 28357832.0000
1813
disable 019103 1 39654943 39654943 39654943 39654943.0000
1814
witchcraft 019201 1 5987435 5987435 5987435 5987435.0000
1815
betroth 030501 1 5987435 5987435 5987435 5987435.0000
1816
Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000
1817
imprint 030503 1 39654943 39654943 39654943 39654943.0000
1818
swelling 031901 1 5987435 5987435 5987435 5987435.0000
1819
interrelationships 036001 1 5987435 5987435 5987435 5987435.0000
1820
riser 036002 1 28357832 28357832 28357832 28357832.0000
1821
bee 038001 1 5987435 5987435 5987435 5987435.0000
1822
kanji 038002 1 28357832 28357832 28357832 28357832.0000
1823
dental 038003 1 39654943 39654943 39654943 39654943.0000
1824
railway 038011 1 5987435 5987435 5987435 5987435.0000
1825
validate 038012 1 28357832 28357832 28357832 28357832.0000
1826
normalizes 038013 1 39654943 39654943 39654943 39654943.0000
1827
Kline 038101 1 5987435 5987435 5987435 5987435.0000
1828
Anatole 038102 1 28357832 28357832 28357832 28357832.0000
1829
partridges 038103 1 39654943 39654943 39654943 39654943.0000
1830
recruited 038201 1 5987435 5987435 5987435 5987435.0000
1831
dimensions 038202 1 28357832 28357832 28357832 28357832.0000
1832
Chicana 038203 1 39654943 39654943 39654943 39654943.0000
1833
select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
1834
companynr fld3 sum(price)
1839
512 descendants 786542
1842
512 Micronesia 786542
1845
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;
1846
companynr count(*) min(fld3) max(fld3) sum(price) avg(price)
1847
00 1 Omaha Omaha 5987435 5987435.0000
1848
36 1 dubbed dubbed 28357832 28357832.0000
1849
37 83 Abraham Wotan 1908978016 22999735.1325
1850
50 2 scribbled tapestry 68012775 34006387.5000
1851
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;
1852
t3.companynr+0 t2nr fld3 sum(price)
1854
37 11401 breaking 5987435
1855
37 11402 Romans 28357832
1856
37 11403 intercepted 39654943
1857
37 11501 bewilderingly 5987435
1858
37 11701 astound 5987435
1859
37 11702 admonishing 28357832
1860
37 11703 sumac 39654943
1861
37 12001 flanking 5987435
1862
37 12003 combed 39654943
1863
37 12301 Eulerian 5987435
1864
37 12302 dubbed 28357832
1865
37 12303 Kane 39654943
1866
37 12501 annihilates 5987435
1867
37 12602 Wotan 28357832
1868
37 12603 snatching 39654943
1869
37 12701 grazing 5987435
1870
37 12702 Baird 28357832
1871
37 12703 celery 39654943
1872
37 13601 handgun 5987435
1873
37 13602 foldout 28357832
1874
37 13603 mystic 39654943
1875
37 13801 intelligibility 5987435
1876
37 13802 Augustine 28357832
1877
37 13803 teethe 39654943
1878
37 13901 scholastics 5987435
1879
37 16001 audiology 5987435
1880
37 16201 wallet 5987435
1881
37 16202 parters 28357832
1882
37 16301 eschew 5987435
1883
37 16302 quitter 28357832
1884
37 16303 neat 39654943
1885
37 18001 jarring 5987435
1886
37 18002 tinily 28357832
1887
37 18003 balled 39654943
1888
37 18012 impulsive 28357832
1889
37 18013 starlet 39654943
1890
37 18021 lawgiver 5987435
1891
37 18022 stated 28357832
1892
37 18023 readable 39654943
1893
37 18032 testicle 28357832
1894
37 18033 Parsifal 39654943
1895
37 18041 Punjab 5987435
1896
37 18042 Merritt 28357832
1897
37 18043 Quixotism 39654943
1898
37 18051 sureties 5987435
1899
37 18052 puddings 28357832
1900
37 18053 tapestry 39654943
1901
37 18061 trimmings 5987435
1902
37 18062 humility 28357832
1903
37 18101 tragedies 5987435
1904
37 18102 skulking 28357832
1905
37 18103 flint 39654943
1906
37 18201 relaxing 5987435
1907
37 18202 offload 28357832
1908
37 18402 suites 28357832
1909
37 18403 lists 39654943
1910
37 18601 vacuuming 5987435
1911
37 18602 dentally 28357832
1912
37 18603 humanness 39654943
1913
37 18801 inch 5987435
1914
37 18802 Weissmuller 28357832
1915
37 18803 irresponsibly 39654943
1916
37 18811 repetitions 5987435
1917
37 18812 Antares 28357832
1918
37 19101 ventilate 5987435
1919
37 19102 pityingly 28357832
1920
37 19103 interdependent 39654943
1921
37 19201 Graves 5987435
1922
37 30501 neonatal 5987435
1923
37 30502 scribbled 28357832
1924
37 30503 chafe 39654943
1925
37 31901 realtor 5987435
1926
37 36001 elite 5987435
1927
37 36002 funereal 28357832
1928
37 38001 Conley 5987435
1929
37 38002 lectured 28357832
1930
37 38003 Abraham 39654943
1931
37 38011 groupings 5987435
1932
37 38012 dissociate 28357832
1933
37 38013 coexist 39654943
1934
37 38101 rusting 5987435
1935
37 38102 galling 28357832
1936
37 38103 obliterates 39654943
1937
37 38201 resumes 5987435
1938
37 38202 analyzable 28357832
1939
37 38203 terminator 39654943
1940
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;
1943
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;
1946
explain select fld3 from t2 where 1>2 or 2>3;
1947
id select_type table type possible_keys key key_len ref rows Extra
1948
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1949
explain select fld3 from t2 where fld1=fld1;
1950
id select_type table type possible_keys key key_len ref rows Extra
1951
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1952
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1956
select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1960
select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1967
select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1974
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
1975
companynr companyname count(*)
1983
select count(*) from t2;
1986
select count(*) from t2 where fld1 < 098024;
1989
select min(fld1) from t2 where fld1>= 098024;
1992
select max(fld1) from t2 where fld1>= 098024;
1995
select count(*) from t3 where price2=76234234;
1998
select count(*) from t3 where companynr=512 and price2=76234234;
2001
explain select min(fld1),max(fld1),count(*) from t2;
2002
id select_type table type possible_keys key key_len ref rows Extra
2003
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2004
select min(fld1),max(fld1),count(*) from t2;
2005
min(fld1) max(fld1) count(*)
2007
select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
2010
select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
2011
count(*) min(t2nr) max(t2nr)
2013
select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
2035
select max(t2nr) from t3 where price=983543950;
2038
select t1.period from t3 = t1 limit 1;
2041
select t1.period from t1 as t1 limit 1;
2044
select t1.period as "Nuvarande period" from t1 as t1 limit 1;
2047
select period as ok_period from t1 limit 1;
2050
select period as ok_period from t1 group by ok_period limit 1;
2053
select 1+1 as summa from t1 group by summa limit 1;
2056
select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
2065
show tables from test like "s%";
2067
show tables from test like "t?";
2069
show full columns from t2;
2070
Field Type Collation Null Key Default Extra Privileges Comment
2071
auto int(11) NULL NO PRI NULL auto_increment #
2072
fld1 int(6) unsigned zerofill NULL NO UNI NULL #
2073
companynr tinyint(2) unsigned zerofill NULL NO NULL #
2074
fld3 char(30) latin1_swedish_ci NO MUL NULL #
2075
fld4 char(35) latin1_swedish_ci NO NULL #
2076
fld5 char(35) latin1_swedish_ci NO NULL #
2077
fld6 char(4) latin1_swedish_ci NO NULL #
2078
show full columns from t2 from test like 'f%';
2079
Field Type Collation Null Key Default Extra Privileges Comment
2080
fld1 int(6) unsigned zerofill NULL NO UNI NULL #
2081
fld3 char(30) latin1_swedish_ci NO MUL NULL #
2082
fld4 char(35) latin1_swedish_ci NO NULL #
2083
fld5 char(35) latin1_swedish_ci NO NULL #
2084
fld6 char(4) latin1_swedish_ci NO NULL #
2085
show full columns from t2 from test like 's%';
2086
Field Type Collation Null Key Default Extra Privileges Comment
2088
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
2089
t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE
2090
t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE
2091
t2 1 fld3 1 fld3 A NULL NULL NULL BTREE
2092
drop table t4, t3, t2, t1;
2094
id mediumint(8) unsigned NOT NULL auto_increment,
2095
pseudo varchar(35) NOT NULL default '',
2097
UNIQUE KEY pseudo (pseudo)
2099
INSERT INTO t1 (pseudo) VALUES ('test');
2100
INSERT INTO t1 (pseudo) VALUES ('test1');
2101
SELECT 1 as rnd1 from t1 where rand() > 2;
2104
CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned 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;
2105
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);
2106
CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;
2107
INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);
2108
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;
2109
gvid the_success the_fail the_size the_time
2111
Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1
2112
Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1
2113
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;
2114
gvid the_success the_fail the_size the_time
2116
create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0');
2117
INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);
2118
select wss_type from t1 where wss_type ='102935229216544106';
2120
select wss_type from t1 where wss_type ='102935229216544105';
2122
select wss_type from t1 where wss_type ='102935229216544104';
2124
select wss_type from t1 where wss_type ='102935229216544093';
2127
select wss_type from t1 where wss_type =102935229216544093;
2131
select 1+2,"aaaa",3.13*2.0 into @a,@b,@c;
2141
create table t1 (a int not null auto_increment primary key);
2142
insert into t1 values ();
2143
insert into t1 values ();
2144
insert into t1 values ();
2145
select * from (t1 as t2 left join t1 as t3 using (a)), t1;
2156
select * from t1, (t1 as t2 left join t1 as t3 using (a));
2167
select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;
2178
select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));
2189
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
2197
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2205
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
2210
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2215
select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
2223
select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2232
select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a );
2237
select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2242
select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
2247
select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
2252
select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
2261
select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2269
select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
2274
select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2279
select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1;
2284
select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));
2289
select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
2294
select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
2300
CREATE TABLE t1 ( aa char(2), id int(11) NOT NULL auto_increment, t2_id int(11) NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;
2301
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);
2302
CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;
2303
INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);
2304
select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5;
2312
create table t1 (id1 int NOT NULL);
2313
create table t2 (id2 int NOT NULL);
2314
create table t3 (id3 int NOT NULL);
2315
create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4));
2316
insert into t1 values (1);
2317
insert into t1 values (2);
2318
insert into t2 values (1);
2319
insert into t4 values (1,1);
2320
explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
2321
left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
2322
id select_type table type possible_keys key key_len ref rows Extra
2323
1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found
2324
1 SIMPLE t4 const id4 NULL NULL NULL 1
2325
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
2326
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where
2327
select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
2328
left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
2329
id1 id2 id3 id4 id44
2331
drop table t1,t2,t3,t4;
2332
create table t1(s varchar(10) not null);
2333
create table t2(s varchar(10) not null primary key);
2334
create table t3(s varchar(10) not null primary key);
2335
insert into t1 values ('one\t'), ('two\t');
2336
insert into t2 values ('one\r'), ('two\t');
2337
insert into t3 values ('one '), ('two\t');
2338
select * from t1 where s = 'one';
2340
select * from t2 where s = 'one';
2342
select * from t3 where s = 'one';
2345
select * from t1,t2 where t1.s = t2.s;
2348
select * from t2,t3 where t2.s = t3.s;
2351
drop table t1, t2, t3;
2352
create table t1 (a integer, b integer, index(a), index(b));
2353
create table t2 (c integer, d integer, index(c), index(d));
2354
insert into t1 values (1,2), (2,2), (3,2), (4,2);
2355
insert into t2 values (1,3), (2,3), (3,4), (4,4);
2356
explain select * from t1 left join t2 on a=c where d in (4);
2357
id select_type table type possible_keys key key_len ref rows Extra
2358
1 SIMPLE t2 ref c,d d 5 const 2 Using index condition
2359
1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer
2360
select * from t1 left join t2 on a=c where d in (4);
2364
explain select * from t1 left join t2 on a=c where d = 4;
2365
id select_type table type possible_keys key key_len ref rows Extra
2366
1 SIMPLE t2 ref c,d d 5 const 2 Using index condition
2367
1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer
2368
select * from t1 left join t2 on a=c where d = 4;
2374
i int(11) NOT NULL default '0',
2375
c char(10) NOT NULL default '',
2379
INSERT INTO t1 VALUES (1,'a');
2380
INSERT INTO t1 VALUES (2,'b');
2381
INSERT INTO t1 VALUES (3,'c');
2382
EXPLAIN SELECT i FROM t1 WHERE i=1;
2383
id select_type table type possible_keys key key_len ref rows Extra
2384
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
2386
CREATE TABLE t1 ( a BLOB, INDEX (a(20)) );
2387
CREATE TABLE t2 ( a BLOB, INDEX (a(20)) );
2388
INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
2389
INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
2390
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
2391
id select_type table type possible_keys key key_len ref rows Extra
2392
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2393
1 SIMPLE t2 ref a a 23 test.t1.a 2
2394
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;
2395
id select_type table type possible_keys key key_len ref rows Extra
2396
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2397
1 SIMPLE t2 ref a a 23 test.t1.a 2
2399
CREATE TABLE t1 ( city char(30) );
2400
INSERT INTO t1 VALUES ('London');
2401
INSERT INTO t1 VALUES ('Paris');
2402
SELECT * FROM t1 WHERE city='London';
2405
SELECT * FROM t1 WHERE city='london';
2408
EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london';
2409
id select_type table type possible_keys key key_len ref rows Extra
2410
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
2411
SELECT * FROM t1 WHERE city='London' AND city='london';
2414
EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2415
id select_type table type possible_keys key key_len ref rows Extra
2416
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
2417
SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2421
create table t1 (a int(11) unsigned, b int(11) unsigned);
2422
insert into t1 values (1,0), (1,1), (1,2);
2423
select a-b from t1 order by 1;
2427
18446744073709551615
2428
select a-b , (a-b < 0) from t1 order by 1;
2432
18446744073709551615 0
2433
select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;
2437
18446744073709551615 1 2
2438
select cast((a - b) as unsigned) from t1 order by 1;
2439
cast((a - b) as unsigned)
2442
18446744073709551615
2444
create table t1 (a int(11));
2445
select all all * from t1;
2447
select distinct distinct * from t1;
2449
select all distinct * from t1;
2450
ERROR HY000: Incorrect usage of ALL and DISTINCT
2451
select distinct all * from t1;
2452
ERROR HY000: Incorrect usage of ALL and DISTINCT
2455
kunde_intern_id int(10) unsigned NOT NULL default '0',
2456
kunde_id int(10) unsigned NOT NULL default '0',
2457
FK_firma_id int(10) unsigned NOT NULL default '0',
2458
aktuell enum('Ja','Nein') NOT NULL default 'Ja',
2459
vorname varchar(128) NOT NULL default '',
2460
nachname varchar(128) NOT NULL default '',
2461
geloescht enum('Ja','Nein') NOT NULL default 'Nein',
2462
firma varchar(128) NOT NULL default ''
2464
INSERT INTO t1 VALUES
2465
(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'),
2466
(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX');
2467
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1
2471
( '' != '' AND firma LIKE CONCAT('%', '', '%'))
2473
(vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2474
nachname LIKE CONCAT('%', '1Nachname', '%') AND
2475
'Vorname1' != '' AND 'xxxx' != '')
2479
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2483
kunde_id FK_firma_id aktuell vorname nachname geloescht
2484
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname,
2489
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2493
( '' != '' AND firma LIKE CONCAT('%', '', '%') )
2495
( vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2496
nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND
2501
kunde_id FK_firma_id aktuell vorname nachname geloescht
2502
SELECT COUNT(*) FROM t1 WHERE
2503
( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1))
2504
AND FK_firma_id = 2;
2508
CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b));
2509
INSERT INTO t1 VALUES (0x8000000000000000);
2510
SELECT b FROM t1 WHERE b=0x8000000000000000;
2514
CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL);
2515
CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL);
2516
INSERT INTO `t2` VALUES (0,'READ');
2517
CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL);
2518
INSERT INTO `t3` VALUES (1,'fs');
2519
select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);
2520
id name gid uid ident level
2521
1 fs NULL NULL 0 READ
2522
drop table t1,t2,t3;
2524
acct_id int(11) NOT NULL default '0',
2525
profile_id smallint(6) default NULL,
2526
UNIQUE KEY t1$acct_id (acct_id),
2527
KEY t1$profile_id (profile_id)
2529
INSERT INTO t1 VALUES (132,17),(133,18);
2531
profile_id smallint(6) default NULL,
2532
queue_id int(11) default NULL,
2533
seq int(11) default NULL,
2534
KEY t2$queue_id (queue_id)
2536
INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);
2538
id int(11) NOT NULL default '0',
2539
qtype int(11) default NULL,
2540
seq int(11) default NULL,
2541
warn_lvl int(11) default NULL,
2542
crit_lvl int(11) default NULL,
2543
rr1 tinyint(4) NOT NULL default '0',
2544
rr2 int(11) default NULL,
2545
default_queue tinyint(4) NOT NULL default '0',
2546
KEY t3$qtype (qtype),
2549
INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0),
2550
(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0);
2551
SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q
2553
(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND
2554
(pq.queue_id = q.id) AND (q.rr1 <> 1);
2557
drop table t1,t2,t3;
2558
create table t1 (f1 int);
2559
insert into t1 values (1),(NULL);
2560
create table t2 (f2 int, f3 int, f4 int);
2561
create index idx1 on t2 (f4);
2562
insert into t2 values (1,2,3),(2,4,6);
2563
select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3)
2564
from t2 C where A.f4 = C.f4) or A.f3 IS NULL;
2569
create table t2 (a tinyint unsigned);
2570
create index t2i on t2(a);
2571
insert into t2 values (0), (254), (255);
2572
explain select * from t2 where a > -1;
2573
id select_type table type possible_keys key key_len ref rows Extra
2574
1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index
2575
select * from t2 where a > -1;
2581
CREATE TABLE t1 (a INT, b INT);
2582
(SELECT a, b AS c FROM t1) ORDER BY c+1;
2584
(SELECT a, b AS c FROM t1) ORDER BY b+1;
2586
SELECT a, b AS c FROM t1 ORDER BY c+1;
2588
SELECT a, b AS c FROM t1 ORDER BY b+1;
2591
create table t1(f1 int, f2 int);
2592
create table t2(f3 int);
2593
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1));
2595
select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1));
2597
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL));
2599
insert into t1 values(1,1),(2,null);
2600
insert into t2 values(2);
2601
select * from t1,t2 where f1=f3 and (f1,f2) = (2,null);
2603
select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null);
2607
create table t1 (f1 int not null auto_increment primary key, f2 varchar(10));
2608
create table t11 like t1;
2609
insert into t1 values(1,""),(2,"");
2610
show table status like 't1%';
2611
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
2612
t1 MyISAM 10 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL
2613
t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL
2614
select 123 as a from t1 where f1 is null;
2617
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );
2618
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
2619
CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT );
2620
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);
2621
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2622
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2629
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2630
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c;
2636
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2637
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c;
2644
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1
2645
WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2650
DROP TABLE IF EXISTS t1, t2;
2651
create table t1 (f1 int primary key, f2 int);
2652
create table t2 (f3 int, f4 int, primary key(f3,f4));
2653
insert into t1 values (1,1);
2654
insert into t2 values (1,1),(1,2);
2655
select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1;
2659
create table t1 (f1 int,f2 int);
2660
insert into t1 values(1,1);
2661
create table t2 (f3 int, f4 int, primary key(f3,f4));
2662
insert into t2 values(1,1);
2663
select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2));
2667
CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));
2668
insert into t1 values (1,0,0),(2,0,0);
2669
CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));
2670
insert into t2 values (1,'',''), (2,'','');
2671
CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
2672
insert into t3 values (1,1),(1,2);
2673
explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2
2674
where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and
2675
t2.b like '%%' order by t2.b limit 0,1;
2676
id select_type table type possible_keys key key_len ref rows Extra
2677
1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort
2678
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer
2679
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
2680
DROP TABLE t1,t2,t3;
2681
CREATE TABLE t1 (a int, INDEX idx(a));
2682
INSERT INTO t1 VALUES (2), (3), (1);
2683
EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);
2684
id select_type table type possible_keys key key_len ref rows Extra
2685
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
2686
EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);
2687
ERROR 42000: Key 'a' doesn't exist in table 't1'
2688
EXPLAIN SELECT * FROM t1 FORCE INDEX (a);
2689
ERROR 42000: Key 'a' doesn't exist in table 't1'
2691
CREATE TABLE t1 (a int, b int);
2692
INSERT INTO t1 VALUES (1,1), (2,1), (4,10);
2693
CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b));
2694
INSERT INTO t2 VALUES (1,NULL), (2,10);
2695
ALTER TABLE t1 ENABLE KEYS;
2696
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2697
id select_type table type possible_keys key key_len ref rows Extra
2698
1 SIMPLE t2 index b b 5 NULL 2 Using index
2699
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2700
SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2706
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2707
id select_type table type possible_keys key key_len ref rows Extra
2708
1 SIMPLE t2 index b b 5 NULL 2 Using index
2709
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2710
SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2716
DROP TABLE IF EXISTS t1,t2;
2717
CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1));
2718
CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));
2719
INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);
2720
INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);
2721
explain select max(key1) from t1 where key1 <= 0.6158;
2722
id select_type table type possible_keys key key_len ref rows Extra
2723
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2724
explain select max(key2) from t2 where key2 <= 1.6158;
2725
id select_type table type possible_keys key key_len ref rows Extra
2726
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2727
explain select min(key1) from t1 where key1 >= 0.3762;
2728
id select_type table type possible_keys key key_len ref rows Extra
2729
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2730
explain select min(key2) from t2 where key2 >= 1.3762;
2731
id select_type table type possible_keys key key_len ref rows Extra
2732
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2733
explain select max(key1), min(key2) from t1, t2
2734
where key1 <= 0.6158 and key2 >= 1.3762;
2735
id select_type table type possible_keys key key_len ref rows Extra
2736
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2737
explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
2738
id select_type table type possible_keys key key_len ref rows Extra
2739
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2740
explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
2741
id select_type table type possible_keys key key_len ref rows Extra
2742
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2743
select max(key1) from t1 where key1 <= 0.6158;
2746
select max(key2) from t2 where key2 <= 1.6158;
2749
select min(key1) from t1 where key1 >= 0.3762;
2752
select min(key2) from t2 where key2 >= 1.3762;
2755
select max(key1), min(key2) from t1, t2
2756
where key1 <= 0.6158 and key2 >= 1.3762;
2758
0.6158000230789185 1.3761999607086182
2759
select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
2762
select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
2766
CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
2767
INSERT INTO t1 VALUES (10);
2768
SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;
2769
i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01')
2772
CREATE TABLE t1 (c0 int);
2773
CREATE TABLE t2 (c0 int);
2774
INSERT INTO t1 VALUES(@@connect_timeout);
2775
INSERT INTO t2 VALUES(@@connect_timeout);
2776
SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout);
2782
K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '',
2783
K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000',
2784
F2I4 int(11) NOT NULL default '0'
2785
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2786
INSERT INTO t1 VALUES
2787
('W%RT', '0100', 1),
2788
('W-RT', '0100', 1),
2789
('WART', '0100', 1),
2790
('WART', '0200', 1),
2791
('WERT', '0100', 2),
2794
('W_RT', '0100', 2),
2795
('WaRT', '0100', 3),
2796
('WART', '0300', 3),
2797
('WRT' , '0400', 3),
2798
('WURM', '0500', 3),
2800
('WA%T', '0700', 4),
2801
('WA_T', '0800', 4);
2802
SELECT K2C4, K4N4, F2I4 FROM t1
2803
WHERE K2C4 = 'WART' AND
2804
(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200'));
2807
SELECT K2C4, K4N4, F2I4 FROM t1
2808
WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200');
2814
create table t1 (a int, b int);
2815
create table t2 like t1;
2816
select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1;
2818
select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1;
2820
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;
2823
create table t1 (s1 varchar(5));
2824
insert into t1 values ('Wall');
2825
select min(s1) from t1 group by s1 with rollup;
2830
create table t1 (s1 int) engine=myisam;
2831
insert into t1 values (0);
2832
select avg(distinct s1) from t1 group by s1 with rollup;
2837
create table t1 (s1 int);
2838
insert into t1 values (null),(1);
2839
select distinct avg(s1) as x from t1 group by s1 with rollup;
2844
CREATE TABLE t1 (a int);
2845
CREATE TABLE t2 (a int);
2846
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
2847
INSERT INTO t2 VALUES (2), (4), (6);
2848
SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2852
EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2853
id select_type table type possible_keys key key_len ref rows Extra
2854
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2855
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2856
EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a;
2857
id select_type table type possible_keys key key_len ref rows Extra
2858
1 SIMPLE t2 ALL NULL NULL NULL NULL 3
2859
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer
2861
select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
2862
x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0
2864
create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);
2865
create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));
2866
insert into t1 values (" 2", 2);
2867
insert into t2 values (" 2", " one "),(" 2", " two ");
2868
select * from t1 left join t2 on f1 = f3;
2873
create table t1 (pk int primary key, b int);
2874
create table t2 (pk int primary key, c int);
2875
select pk from t1 inner join t2 using (pk);
2878
create table t1 (a int(10), t1_val int(10));
2879
create table t2 (b int(10), t2_val int(10));
2880
create table t3 (a int(10), b int(10));
2881
insert into t1 values (1,1),(2,2);
2882
insert into t2 values (1,1),(2,2),(3,3);
2883
insert into t3 values (1,1),(2,1),(3,1),(4,1);
2884
select * from t1 natural join t2 natural join t3;
2888
select * from t1 natural join t3 natural join t2;
2892
drop table t1, t2, t3;
2893
create table t1 (a char(1));
2894
create table t2 (a char(1));
2895
insert into t1 values ('a'),('b'),('c');
2896
insert into t2 values ('b'),('c'),('d');
2897
select a from t1 natural join t2;
2901
select * from t1 natural join t2 where a = 'b';
2905
CREATE TABLE t1 (`id` TINYINT);
2906
CREATE TABLE t2 (`id` TINYINT);
2907
CREATE TABLE t3 (`id` TINYINT);
2908
INSERT INTO t1 VALUES (1),(2),(3);
2909
INSERT INTO t2 VALUES (2);
2910
INSERT INTO t3 VALUES (3);
2911
SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
2912
ERROR 23000: Column 'id' in from clause is ambiguous
2913
SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);
2914
ERROR 23000: Column 'id' in from clause is ambiguous
2915
SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
2916
ERROR 23000: Column 'id' in from clause is ambiguous
2917
SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);
2918
ERROR 23000: Column 'id' in from clause is ambiguous
2919
drop table t1, t2, t3;
2920
create table t1 (a int(10),b int(10));
2921
create table t2 (a int(10),b int(10));
2922
insert into t1 values (1,10),(2,20),(3,30);
2923
insert into t2 values (1,10);
2924
select * from t1 inner join t2 using (A);
2927
select * from t1 inner join t2 using (a);
2931
create table t1 (a int, c int);
2932
create table t2 (b int);
2933
create table t3 (b int, a int);
2934
create table t4 (c int);
2935
insert into t1 values (1,1);
2936
insert into t2 values (1);
2937
insert into t3 values (1,1);
2938
insert into t4 values (1);
2939
select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
2942
select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
2943
ERROR 42S22: Unknown column 't1.a' in 'on clause'
2944
select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c);
2947
select * from t1 join t2 join t4 using (c);
2950
drop table t1, t2, t3, t4;
2951
create table t1(x int, y int);
2952
create table t2(x int, y int);
2953
create table t3(x int, primary key(x));
2954
insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6);
2955
insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6);
2956
insert into t3 values (1), (2), (3), (4), (5);
2957
select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y;
2967
drop table t1,t2,t3;
2968
create table t1 (id int(11) not null default '0');
2969
insert into t1 values (123),(191),(192);
2970
create table t2 (id char(16) character set utf8 not null);
2971
insert into t2 values ('58013'),('58014'),('58015'),('58016');
2972
create table t3 (a_id int(11) not null, b_id char(16) character set utf8);
2973
insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');
2975
from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id;
2979
from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id;
2982
drop table t1,t2,t3;
2983
create table t1 (a int);
2984
create table t2 (b int);
2985
create table t3 (c int);
2986
select * from t1 join t2 join t3 on (t1.a=t3.c);
2988
select * from t1 join t2 left join t3 on (t1.a=t3.c);
2990
select * from t1 join t2 right join t3 on (t1.a=t3.c);
2992
select * from t1 join t2 straight_join t3 on (t1.a=t3.c);
2994
drop table t1, t2 ,t3;
2995
create table t1(f1 int, f2 date);
2996
insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),
2997
(4,'2005-10-01'),(5,'2005-12-30');
2998
select * from t1 where f2 >= 0 order by f2;
3005
select * from t1 where f2 >= '0000-00-00' order by f2;
3012
select * from t1 where f2 >= '2005-09-31' order by f2;
3016
select * from t1 where f2 >= '2005-09-3a' order by f2;
3022
Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1
3023
select * from t1 where f2 <= '2005-09-31' order by f2;
3028
select * from t1 where f2 <= '2005-09-3a' order by f2;
3033
Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1
3035
CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a));
3036
CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a));
3037
CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32),
3038
PRIMARY KEY(key_a,key_b));
3039
INSERT INTO t1 VALUES (0,'');
3040
INSERT INTO t1 VALUES (1,'i');
3041
INSERT INTO t1 VALUES (2,'j');
3042
INSERT INTO t1 VALUES (3,'k');
3043
INSERT INTO t2 VALUES (1,'r');
3044
INSERT INTO t2 VALUES (2,'s');
3045
INSERT INTO t2 VALUES (3,'t');
3046
INSERT INTO t3 VALUES (1,5,'x');
3047
INSERT INTO t3 VALUES (1,6,'y');
3048
INSERT INTO t3 VALUES (2,5,'xx');
3049
INSERT INTO t3 VALUES (2,6,'yy');
3050
INSERT INTO t3 VALUES (2,7,'zz');
3051
INSERT INTO t3 VALUES (3,5,'xxx');
3053
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
3054
INNER JOIN t3 ON t1.key_a = t3.key_a
3055
WHERE t2.key_a=2 and key_b=5;
3058
EXPLAIN SELECT t2.key_a,foo
3059
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
3060
INNER JOIN t3 ON t1.key_a = t3.key_a
3061
WHERE t2.key_a=2 and key_b=5;
3062
id select_type table type possible_keys key key_len ref rows Extra
3063
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
3064
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index
3065
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
3067
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
3068
INNER JOIN t3 ON t1.key_a = t3.key_a
3069
WHERE t2.key_a=2 and key_b=5;
3072
EXPLAIN SELECT t2.key_a,foo
3073
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
3074
INNER JOIN t3 ON t1.key_a = t3.key_a
3075
WHERE t2.key_a=2 and key_b=5;
3076
id select_type table type possible_keys key key_len ref rows Extra
3077
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
3078
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index
3079
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
3080
DROP TABLE t1,t2,t3;
3081
create table t1 (f1 int);
3082
insert into t1 values(1),(2);
3083
create table t2 (f2 int, f3 int, key(f2));
3084
insert into t2 values(1,1),(2,2);
3085
create table t3 (f4 int not null);
3086
insert into t3 values (2),(2),(2);
3087
select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1;
3091
drop table t1,t2,t3;
3092
create table t1 (f1 int unique);
3093
create table t2 (f2 int unique);
3094
create table t3 (f3 int unique);
3095
insert into t1 values(1),(2);
3096
insert into t2 values(1),(2);
3097
insert into t3 values(1),(NULL);
3098
select * from t3 where f3 is null;
3101
select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1;
3104
drop table t1,t2,t3;
3105
create table t1(f1 char, f2 char not null);
3106
insert into t1 values(null,'a');
3107
create table t2 (f2 char not null);
3108
insert into t2 values('b');
3109
select * from t1 left join t2 on f1=t2.f2 where t1.f2='a';
3113
select * from (select * left join t on f1=f2) tt;
3114
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
3115
CREATE TABLE t1 (sku int PRIMARY KEY, pr int);
3116
CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255));
3117
INSERT INTO t1 VALUES
3118
(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10);
3119
INSERT INTO t2 VALUES
3120
(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),
3121
(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');
3122
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
3123
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
3124
sku sppr name sku pr
3128
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
3129
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
3130
id select_type table type possible_keys key key_len ref rows Extra
3131
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
3132
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR
3134
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
3135
INSERT t1 SET i = 0;
3136
UPDATE t1 SET i = -1;
3138
Warning 1264 Out of range value for column 'i' at row 1
3142
UPDATE t1 SET i = CAST(i - 1 AS SIGNED);
3144
Warning 1264 Out of range value for column 'i' at row 1
3148
UPDATE t1 SET i = i - 1;
3150
Warning 1264 Out of range value for column 'i' at row 1
3155
create table t1 (a int);
3156
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
3157
create table t2 (a int, b int, c int, e int, primary key(a,b,c));
3158
insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C;
3160
Table Op Msg_type Msg_text
3161
test.t2 analyze status OK
3162
select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
3164
In next EXPLAIN, B.rows must be exactly 10:
3165
explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
3166
and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
3167
id select_type table type possible_keys key key_len ref rows Extra
3168
1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where; Using MRR
3169
1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10
3171
CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
3172
INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),
3173
(3,1), (5,1), (8,9), (2,2), (0,9);
3174
CREATE TABLE t2 (c int, d int, f int, INDEX(c,f));
3175
INSERT INTO t2 VALUES
3176
(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1),
3177
(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1),
3178
(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1);
3180
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
3181
id select_type table type possible_keys key key_len ref rows Extra
3182
1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using MRR
3183
1 SIMPLE t2 ref c c 5 test.t1.a 2
3185
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
3186
id select_type table type possible_keys key key_len ref rows Extra
3187
1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where; Using MRR
3188
1 SIMPLE t2 ref c c 5 test.t1.a 2
3191
a int unsigned not null auto_increment primary key,
3196
a int unsigned not null auto_increment primary key,
3198
c int unsigned not null,
3201
insert into t1 (b,c) values (0,1), (0,1);
3202
insert into t2 (b,c) values (0,1);
3203
select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d
3204
from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1
3205
where t1.b <> 1 order by t1.a;
3206
a t1.b + 0 t1.c + 0 a t2.b + 0 c d
3208
2 0 1 NULL NULL NULL NULL
3210
SELECT 0.9888889889 * 1.011111411911;
3211
0.9888889889 * 1.011111411911
3212
0.9998769417899202067879
3213
CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
3214
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
3215
CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
3216
INSERT INTO t2 VALUES
3217
(1), (1), (1), (1), (1), (1), (1), (1),
3221
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
3222
id select_type table type possible_keys key key_len ref rows Extra
3223
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3224
1 SIMPLE t2 ref idx idx 4 const 7 Using index
3225
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
3226
id select_type table type possible_keys key key_len ref rows Extra
3227
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3228
1 SIMPLE t2 ref idx idx 4 const 1 Using index
3230
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
3231
INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
3232
CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
3233
INSERT INTO t2 VALUES (2,1), (3,2);
3234
CREATE TABLE t3 (d int, e int, INDEX idx1(d));
3235
INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
3237
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3239
id select_type table type possible_keys key key_len ref rows Extra
3240
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3241
1 SIMPLE t2 const idx1 NULL NULL NULL 1
3242
1 SIMPLE t3 ref idx1 idx1 5 const 3
3243
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3246
2 NULL NULL NULL 2 10
3247
2 NULL NULL NULL 2 20
3248
2 NULL NULL NULL 2 40
3249
2 NULL NULL NULL 2 50
3250
DROP TABLE t1,t2,t3;
3251
CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
3252
CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
3253
INSERT INTO t1 VALUES
3254
('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'),
3255
('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
3256
INSERT INTO t2 VALUES
3257
('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
3258
('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
3259
('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
3260
('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
3262
FROM t1 JOIN t2 ON t2.fk=t1.pk
3263
WHERE t2.fk < 'c' AND t2.pk=t1.fk;
3264
id select_type table type possible_keys key key_len ref rows Extra
3265
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR
3266
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
3268
FROM t1 JOIN t2 ON t2.fk=t1.pk
3269
WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
3270
id select_type table type possible_keys key key_len ref rows Extra
3271
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR
3272
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
3274
FROM t1 JOIN t2 ON t2.fk=t1.pk
3275
WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
3276
id select_type table type possible_keys key key_len ref rows Extra
3277
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR
3278
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
3280
CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
3281
CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
3282
PRIMARY KEY (a), UNIQUE KEY (b));
3283
INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
3284
INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
3285
EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
3286
id select_type table type possible_keys key key_len ref rows Extra
3287
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3288
1 SIMPLE t2 const b b 22 const 1 Using index
3290
CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
3291
CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
3292
CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
3293
INSERT INTO t1 VALUES
3294
(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
3295
(6,63,67), (5,55,58), (3,38,39), (8,81,89);
3296
INSERT INTO t2 VALUES
3297
(21,210), (41,410), (82,820), (83,830), (84,840),
3298
(65,650), (51,510), (37,370), (94,940), (76,760),
3299
(22,220), (33,330), (40,400), (95,950), (38,380),
3300
(67,670), (88,880), (57,570), (96,960), (97,970);
3301
INSERT INTO t3 VALUES
3302
(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
3303
(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
3304
(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
3305
(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
3307
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3308
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3309
t3.a=t2.a AND t3.c IN ('bb','ee');
3310
id select_type table type possible_keys key key_len ref rows Extra
3311
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3312
1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR
3313
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3315
SELECT t3.a FROM t1,t2,t3
3316
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3317
t3.a=t2.a AND t3.c IN ('bb','ee') ;
3318
id select_type table type possible_keys key key_len ref rows Extra
3319
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3320
1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR
3321
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3323
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3324
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3325
t3.c IN ('bb','ee');
3326
id select_type table type possible_keys key key_len ref rows Extra
3327
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3328
1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR
3329
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3331
SELECT t3.a FROM t1,t2,t3
3332
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3333
t3.c IN ('bb','ee');
3334
id select_type table type possible_keys key key_len ref rows Extra
3335
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3336
1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using MRR
3337
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3338
DROP TABLE t1,t2,t3;
3339
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
3340
CREATE TABLE t2 ( f11 int PRIMARY KEY );
3341
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);
3342
INSERT INTO t2 VALUES (62);
3343
SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;
3344
f1 f2 f3 f4 f5 f6 checked_out f11
3347
DROP TABLE IF EXISTS t1;
3348
CREATE TABLE t1(a int);
3349
INSERT into t1 values (1), (2), (3);
3350
SELECT * FROM t1 LIMIT 2, -1;
3351
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
3354
ID_with_null int NULL,
3355
ID_better int NOT NULL,
3356
INDEX idx1 (ID_with_null),
3357
INDEX idx2 (ID_better)
3359
INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3);
3360
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3361
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3362
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3363
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3364
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3365
SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL;
3368
SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3371
EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3372
id select_type table type possible_keys key key_len ref rows Extra
3373
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3374
DROP INDEX idx1 ON t1;
3375
CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);
3376
EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3377
id select_type table type possible_keys key key_len ref rows Extra
3378
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3381
ID1_with_null int NULL,
3382
ID2_with_null int NULL,
3383
ID_better int NOT NULL,
3384
INDEX idx1 (ID1_with_null, ID2_with_null),
3385
INDEX idx2 (ID_better)
3387
INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3),
3388
(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);
3389
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3390
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3391
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3392
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3393
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3394
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3395
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
3398
SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL;
3401
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;
3404
SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3407
EXPLAIN SELECT * FROM t1
3408
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3409
id select_type table type possible_keys key key_len ref rows Extra
3410
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3411
EXPLAIN SELECT * FROM t1
3412
WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;
3413
id select_type table type possible_keys key key_len ref rows Extra
3414
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3415
EXPLAIN SELECT * FROM t1
3416
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3417
id select_type table type possible_keys key key_len ref rows Extra
3418
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3419
DROP INDEX idx1 ON t1;
3420
CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);
3421
EXPLAIN SELECT * FROM t1
3422
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3423
id select_type table type possible_keys key key_len ref rows Extra
3424
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3425
EXPLAIN SELECT * FROM t1
3426
WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ;
3427
id select_type table type possible_keys key key_len ref rows Extra
3428
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3429
EXPLAIN SELECT * FROM t1
3430
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3431
id select_type table type possible_keys key key_len ref rows Extra
3432
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3433
EXPLAIN SELECT * FROM t1
3434
WHERE ID_better=1 AND ID1_with_null IS NULL AND
3435
(ID2_with_null=1 OR ID2_with_null=2);
3436
id select_type table type possible_keys key key_len ref rows Extra
3437
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3439
CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
3440
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
3442
Table Op Msg_type Msg_text
3443
test.t1 analyze status OK
3444
CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
3445
INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
3446
INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
3448
Table Op Msg_type Msg_text
3449
test.t2 analyze status OK
3451
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3452
AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3453
AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3454
id select_type table type possible_keys key key_len ref rows Extra
3455
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
3456
1 SIMPLE t1 range ts ts 4 NULL 1 Using index condition; Using where; Using MRR
3458
Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
3459
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3460
AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3461
AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3463
30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00
3465
Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
3467
create table t1 (a bigint unsigned);
3468
insert into t1 values
3469
(if(1, 9223372036854775808, 1)),
3470
(case when 1 then 9223372036854775808 else 1 end),
3471
(coalesce(9223372036854775808, 1));
3478
create table t1 select
3479
if(1, 9223372036854775808, 1) i,
3480
case when 1 then 9223372036854775808 else 1 end c,
3481
coalesce(9223372036854775808, 1) co;
3482
show create table t1;
3484
t1 CREATE TABLE "t1" (
3485
"i" decimal(19,0) NOT NULL,
3486
"c" decimal(19,0) NOT NULL,
3487
"co" decimal(19,0) NOT NULL
3488
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3491
if(1, cast(1111111111111111111 as unsigned), 1) i,
3492
case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,
3493
coalesce(cast(1111111111111111111 as unsigned), 1) co;
3495
1111111111111111111 1111111111111111111 1111111111111111111
3496
CREATE TABLE t1 (name varchar(255));
3497
CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
3498
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
3499
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
3500
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3501
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3511
SELECT * FROM t2 ORDER BY name;
3520
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3529
EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3530
id select_type table type possible_keys key key_len ref rows Extra
3531
1 SIMPLE t2 ref name name 6 const 3 Using where
3532
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3537
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3538
id select_type table type possible_keys key key_len ref rows Extra
3539
1 SIMPLE t2 range name name 6 NULL 3 Using where
3540
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3546
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3547
id select_type table type possible_keys key key_len ref rows Extra
3548
1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort
3549
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3555
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3556
id select_type table type possible_keys key key_len ref rows Extra
3557
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3558
1 SIMPLE t2 ref name name 6 test.t1.name 2
3559
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3570
CREATE TABLE t1 (name text);
3571
CREATE TABLE t2 (name text, n int, KEY (name(3)));
3572
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
3573
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
3574
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3575
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3585
SELECT * FROM t2 ORDER BY name;
3594
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3603
EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3604
id select_type table type possible_keys key key_len ref rows Extra
3605
1 SIMPLE t2 ref name name 6 const 3 Using where
3606
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3611
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3612
id select_type table type possible_keys key key_len ref rows Extra
3613
1 SIMPLE t2 range name name 6 NULL 3 Using where
3614
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3620
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3621
id select_type table type possible_keys key key_len ref rows Extra
3622
1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort
3623
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3629
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3630
id select_type table type possible_keys key key_len ref rows Extra
3631
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3632
1 SIMPLE t2 ref name name 6 test.t1.name 2
3633
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3645
access_id int NOT NULL default '0',
3646
name varchar(20) default NULL,
3647
rank int NOT NULL default '0',
3651
faq_group_id int NOT NULL default '0',
3652
faq_id int NOT NULL default '0',
3653
access_id int default NULL,
3654
UNIQUE KEY idx1 (faq_id),
3655
KEY idx2 (faq_group_id,faq_id)
3657
INSERT INTO t1 VALUES
3658
(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
3659
INSERT INTO t2 VALUES
3660
(261,265,1),(490,494,1);
3662
FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
3663
ON (t1.access_id = t2.access_id)
3665
ON (t.faq_group_id = t2.faq_group_id AND
3666
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3668
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3672
FROM t1 INNER JOIN t2
3673
ON (t1.access_id = t2.access_id)
3675
ON (t.faq_group_id = t2.faq_group_id AND
3676
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3678
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3682
CREATE TABLE t1 (a INT, b INT, KEY inx (b,a));
3683
INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7);
3684
EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2
3685
ON ( f1.b=f2.b AND f1.a<f2.a )
3686
WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);
3687
id select_type table type possible_keys key key_len ref rows Extra
3688
1 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index
3689
1 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index
3691
CREATE TABLE t1 (c1 INT, c2 INT);
3692
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3693
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;
3694
id select_type table type possible_keys key key_len ref rows Extra
3695
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
3696
31 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3697
32 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3698
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;
3699
ERROR HY000: Too high level of nesting for select
3702
c1 int(11) NOT NULL AUTO_INCREMENT,
3703
c2 varchar(1000) DEFAULT NULL,
3704
c3 bigint(20) DEFAULT NULL,
3705
c4 bigint(20) DEFAULT NULL,
3720
join_0.c1=join_1.c1 AND
3721
join_1.c1=join_2.c1 AND
3722
join_2.c1=join_3.c1 AND
3723
join_3.c1=join_4.c1 AND
3724
join_4.c1=join_5.c1 AND
3725
join_5.c1=join_6.c1 AND
3734
join_5.c2 <> '?' AND
3735
join_6.c2 <> '?' AND
3736
join_7.c2 >= '?' AND
3737
join_0.c1=join_1.c1 AND
3738
join_1.c1=join_2.c1 AND
3739
join_2.c1=join_3.c1 AND
3740
join_3.c1=join_4.c1 AND
3741
join_4.c1=join_5.c1 AND
3742
join_5.c1=join_6.c1 AND
3750
id select_type table type possible_keys key key_len ref rows filtered Extra
3751
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3753
Note 1003 select '0' 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 0 group by '0','0','0','0','0'
3756
Note 1003 select '0' 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 0 group by '0','0','0','0','0'
3762
Warning 1474 Name ' ' has become ''
3767
Warning 1474 Name ' ' has become ''
3772
Warning 1466 Leading spaces are removed from name ' x'
3773
CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
3774
CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,
3775
c22 INT DEFAULT NULL,
3777
CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0,
3778
c32 INT DEFAULT NULL,
3780
c34 INT UNSIGNED DEFAULT 0,
3781
KEY (c33, c34, c32));
3782
INSERT INTO t1 values (),(),(),(),();
3783
INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;
3784
INSERT INTO t3 VALUES (1, 1, 1, 0),
3789
SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
3790
t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
3791
t3.c33 = 1 AND t2.c22 in (1, 3)
3800
SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
3801
t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
3802
t3.c33 = 1 AND t2.c22 in (1, 3)
3811
DROP TABLE t1, t2, t3;
3812
select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
3813
and '2007/10/20 00:00:00 GMT';
3814
str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
3815
and '2007/10/20 00:00:00 GMT'
3818
Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT'
3819
Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT'
3820
select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
3821
str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'
3824
Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6'
3825
select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
3826
str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'
3829
Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6'
3830
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
3831
str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'
3834
Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6'
3835
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';
3836
str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'
3839
Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6'
3840
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';
3841
str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'
3844
Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6'
3845
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';
3846
str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'
3849
Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6'
3850
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';
3851
str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'
3854
Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6'
3855
select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
3856
str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
3859
Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
3860
select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
3861
str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
3864
Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
3865
select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';
3866
str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'
3868
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';
3869
str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'
3871
select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';
3872
str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'
3874
select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';
3875
str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'
3878
Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34'
3879
select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';
3880
str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'
3882
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
3883
str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
3885
select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
3886
and '2007/10/20 00:00:00';
3887
str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
3888
and '2007/10/20 00:00:00'
3890
select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
3891
str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
3894
Warning 1292 Truncated incorrect datetime value: ''
3895
select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';
3896
str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'
3898
select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
3899
str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
3901
select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';
3902
str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'
3904
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';
3905
str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''
3908
Warning 1292 Truncated incorrect datetime value: ''
3909
select str_to_date('1','%Y-%m-%d') = '1';
3910
str_to_date('1','%Y-%m-%d') = '1'
3913
Warning 1292 Truncated incorrect date value: '1'
3914
select str_to_date('1','%Y-%m-%d') = '1';
3915
str_to_date('1','%Y-%m-%d') = '1'
3918
Warning 1292 Truncated incorrect date value: '1'
3919
select str_to_date('','%Y-%m-%d') = '';
3920
str_to_date('','%Y-%m-%d') = ''
3923
Warning 1292 Truncated incorrect date value: ''
3924
select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
3925
str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL
3927
select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
3928
str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'
3930
select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
3931
str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL
3935
# Bug#30736: Row Size Too Large Error Creating a Table and
3938
DROP TABLE IF EXISTS t1;
3939
DROP TABLE IF EXISTS t2;
3945
INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5);
3968
CREATE TABLE t1 (c1 BIGINT NOT NULL);
3969
INSERT INTO t1 (c1) VALUES (1);
3970
SELECT * FROM t1 WHERE c1 > NULL + 1;
3974
CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY);
3975
INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0');
3976
SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar');
3980
CREATE TABLE t1 (a INT, b INT);
3981
CREATE TABLE t2 (a INT, c INT, KEY(a));
3982
INSERT INTO t1 VALUES (1, 1), (2, 2);
3983
INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
3984
(2, 1), (2, 2), (2, 3), (2, 4), (2, 5),
3985
(3, 1), (3, 2), (3, 3), (3, 4), (3, 5),
3986
(4, 1), (4, 2), (4, 3), (4, 4), (4, 5);
3988
SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;
3992
SHOW STATUS LIKE 'Handler_read%';
3994
Handler_read_first 0
3999
Handler_read_rnd_next 6
4002
create table t1(a INT, KEY (a));
4003
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
4004
SELECT a FROM t1 ORDER BY a LIMIT 2;
4008
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;
4013
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;
added
removed
mysql-test/r/select.result
