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;
3
drop view if exists v1;
5
Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
6
Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
8
INSERT INTO t1 VALUES (9410,9412);
19
auto int not null auto_increment,
20
fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
21
companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
22
fld3 char(30) DEFAULT '' NOT NULL,
23
fld4 char(35) DEFAULT '' NOT NULL,
24
fld5 char(35) DEFAULT '' NOT NULL,
25
fld6 char(4) DEFAULT '' NOT NULL,
30
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
33
select fld3 from t2 where fld3 like "%cultivation" ;
36
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
61
select fld3,companynr from t2 where companynr = 58 order by fld3;
86
select fld3 from t2 order by fld3 desc limit 10;
98
select fld3 from t2 order by fld3 desc limit 5;
105
select fld3 from t2 order by fld3 desc limit 5,5;
112
select t2.fld3 from t2 where fld3 = 'honeysuckle';
115
select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
118
select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
121
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
124
select t2.fld3 from t2 where fld3 LIKE 'h%le';
127
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
129
select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
131
explain select t2.fld3 from t2 where fld3 = 'honeysuckle';
132
id select_type table type possible_keys key key_len ref rows Extra
133
1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
134
explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
135
id select_type table type possible_keys key key_len ref rows Extra
136
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
137
explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
138
id select_type table type possible_keys key key_len ref rows Extra
139
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
140
explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
141
id select_type table type possible_keys key key_len ref rows Extra
142
1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
143
explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';
144
id select_type table type possible_keys key key_len ref rows Extra
145
1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
146
explain select fld3 from t2 ignore index (fld3,not_used);
147
ERROR 42000: Key 'not_used' doesn't exist in table 't2'
148
explain select fld3 from t2 use index (not_used);
149
ERROR 42000: Key 'not_used' doesn't exist in table 't2'
150
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
154
explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
155
id select_type table type possible_keys key key_len ref rows Extra
156
1 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index
157
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
162
select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';
170
select fld1 from t2 where fld1=250501 or fld1="250502";
174
explain select fld1 from t2 where fld1=250501 or fld1="250502";
175
id select_type table type possible_keys key key_len ref rows Extra
176
1 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index
177
select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
183
explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
184
id select_type table type possible_keys key key_len ref rows Extra
185
1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index
186
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
219
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
221
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
224
select fld1,fld3 from t2 where fld1 like "25050%";
231
select fld1,fld3 from t2 where fld1 like "25050_";
238
select distinct companynr from t2;
252
select distinct companynr from t2 order by companynr;
266
select distinct companynr from t2 order by companynr desc;
280
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";
288
select distinct fld3 from t2 where companynr = 34 order by fld3;
360
select distinct fld3 from t2 limit 10;
372
select distinct fld3 from t2 having fld3 like "A%" limit 10;
384
select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
448
select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10;
460
select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
472
select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
486
name char(32) not null,
487
companynr int not null,
493
create temporary table tmp engine = myisam select * from t3;
494
insert into t3 select * from tmp;
495
insert into tmp select * from t3;
496
insert into t3 select * from tmp;
497
insert into tmp select * from t3;
498
insert into t3 select * from tmp;
499
insert into tmp select * from t3;
500
insert into t3 select * from tmp;
501
insert into tmp select * from t3;
502
insert into t3 select * from tmp;
503
insert into tmp select * from t3;
504
insert into t3 select * from tmp;
505
insert into tmp select * from t3;
506
insert into t3 select * from tmp;
507
insert into tmp select * from t3;
508
insert into t3 select * from tmp;
509
insert into tmp select * from t3;
510
insert into t3 select * from tmp;
511
alter table t3 add t2nr int not null auto_increment primary key first;
513
SET SQL_BIG_TABLES=1;
514
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
517
abrogating abrogating
518
admonishing admonishing
523
analyzable analyzable
526
SET SQL_BIG_TABLES=0;
527
select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
528
concat(fld3," ",fld3)
530
abrogating abrogating
531
admonishing admonishing
536
analyzable analyzable
539
select distinct fld5 from t2 limit 10;
551
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
563
SET SQL_BIG_TABLES=1;
564
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
576
SET SQL_BIG_TABLES=0;
577
select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
578
fld3 repeat("a",length(fld3)) count(*)
582
congresswoman aaaaaaaaaaaaa 1
583
contrition aaaaaaaaaa 1
585
cultivation aaaaaaaaaaa 1
586
definiteness aaaaaaaaaaaa 1
587
demultiplex aaaaaaaaaaa 1
588
disappointing aaaaaaaaaaaaa 1
589
select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
590
companynr rtrim(space(512+companynr))
598
select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
600
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
601
id select_type table type possible_keys key key_len ref rows Extra
602
1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
603
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
604
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
605
id select_type table type possible_keys key key_len ref rows Extra
606
1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
607
1 SIMPLE t3 ref period period 4 test.t1.period 4181
608
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
609
id select_type table type possible_keys key key_len ref rows Extra
610
1 SIMPLE t3 index period period 4 NULL 1
611
1 SIMPLE t1 ref period period 4 test.t3.period 4181
612
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
613
id select_type table type possible_keys key key_len ref rows Extra
614
1 SIMPLE t1 index period period 4 NULL 1
615
1 SIMPLE t3 ref period period 4 test.t1.period 4181
616
select period from t1;
619
select period from t1 where period=1900;
621
select fld3,period from t1,t2 where fld1 = 011401 order by period;
624
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
627
explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
628
id select_type table type possible_keys key key_len ref rows Extra
629
1 SIMPLE t2 const fld1 fld1 4 const 1
630
1 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1
631
select fld3,period from t2,t1 where companynr*10 = 37*10;
811
interrelationships 9410
1011
electroencephalography 9410
1221
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;
1222
fld3 period price price2
1223
admonishing 1002 28357832 8723648
1224
analyzable 1002 28357832 8723648
1225
annihilates 1001 5987435 234724
1226
Antares 1002 28357832 8723648
1227
astound 1001 5987435 234724
1228
audiology 1001 5987435 234724
1229
Augustine 1002 28357832 8723648
1230
Baird 1002 28357832 8723648
1231
bewilderingly 1001 5987435 234724
1232
breaking 1001 5987435 234724
1233
Conley 1001 5987435 234724
1234
dentally 1002 28357832 8723648
1235
dissociate 1002 28357832 8723648
1236
elite 1001 5987435 234724
1237
eschew 1001 5987435 234724
1238
Eulerian 1001 5987435 234724
1239
flanking 1001 5987435 234724
1240
foldout 1002 28357832 8723648
1241
funereal 1002 28357832 8723648
1242
galling 1002 28357832 8723648
1243
Graves 1001 5987435 234724
1244
grazing 1001 5987435 234724
1245
groupings 1001 5987435 234724
1246
handgun 1001 5987435 234724
1247
humility 1002 28357832 8723648
1248
impulsive 1002 28357832 8723648
1249
inch 1001 5987435 234724
1250
intelligibility 1001 5987435 234724
1251
jarring 1001 5987435 234724
1252
lawgiver 1001 5987435 234724
1253
lectured 1002 28357832 8723648
1254
Merritt 1002 28357832 8723648
1255
neonatal 1001 5987435 234724
1256
offload 1002 28357832 8723648
1257
parters 1002 28357832 8723648
1258
pityingly 1002 28357832 8723648
1259
puddings 1002 28357832 8723648
1260
Punjab 1001 5987435 234724
1261
quitter 1002 28357832 8723648
1262
realtor 1001 5987435 234724
1263
relaxing 1001 5987435 234724
1264
repetitions 1001 5987435 234724
1265
resumes 1001 5987435 234724
1266
Romans 1002 28357832 8723648
1267
rusting 1001 5987435 234724
1268
scholastics 1001 5987435 234724
1269
skulking 1002 28357832 8723648
1270
stated 1002 28357832 8723648
1271
suites 1002 28357832 8723648
1272
sureties 1001 5987435 234724
1273
testicle 1002 28357832 8723648
1274
tinily 1002 28357832 8723648
1275
tragedies 1001 5987435 234724
1276
trimmings 1001 5987435 234724
1277
vacuuming 1001 5987435 234724
1278
ventilate 1001 5987435 234724
1279
wallet 1001 5987435 234724
1280
Weissmuller 1002 28357832 8723648
1281
Wotan 1002 28357832 8723648
1282
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;
1283
fld1 fld3 period price price2
1284
018201 relaxing 1001 5987435 234724
1285
018601 vacuuming 1001 5987435 234724
1286
018801 inch 1001 5987435 234724
1287
018811 repetitions 1001 5987435 234724
1289
companynr tinyint(2) unsigned zerofill NOT NULL default '00',
1290
companyname char(30) NOT NULL default '',
1291
PRIMARY KEY (companynr),
1292
UNIQUE KEY companyname(companyname)
1293
) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
1294
select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1295
companynr companyname
1308
select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1309
companynr companyname
1322
select * from t1,t1 t12;
1323
Period Varor_period Period Varor_period
1325
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;
1352
insert into t2 (fld1, companynr) values (999999,99);
1353
select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1354
companynr companyname
1356
select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1359
explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1360
id select_type table type possible_keys key key_len ref rows Extra
1361
1 SIMPLE t2 ALL NULL NULL NULL NULL 1200
1362
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists
1363
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1364
id select_type table type possible_keys key key_len ref rows Extra
1365
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1366
1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists
1367
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1368
companynr companyname
1369
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1372
explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1373
id select_type table type possible_keys key key_len ref rows Extra
1374
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1375
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
1376
id select_type table type possible_keys key key_len ref rows Extra
1377
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1378
delete from t2 where fld1=999999;
1379
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1380
id select_type table type possible_keys key key_len ref rows Extra
1381
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1382
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1383
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1384
id select_type table type possible_keys key key_len ref rows Extra
1385
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1386
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1387
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1388
id select_type table type possible_keys key key_len ref rows Extra
1389
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1390
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1391
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
1392
id select_type table type possible_keys key key_len ref rows Extra
1393
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1394
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1395
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1396
id select_type table type possible_keys key key_len ref rows Extra
1397
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1398
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1399
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1400
id select_type table type possible_keys key key_len ref rows Extra
1401
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1402
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1403
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1404
id select_type table type possible_keys key key_len ref rows Extra
1405
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1406
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1407
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1408
id select_type table type possible_keys key key_len ref rows Extra
1409
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12
1410
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1411
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1412
id select_type table type possible_keys key key_len ref rows Extra
1413
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1414
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1415
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1416
id select_type table type possible_keys key key_len ref rows Extra
1417
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1418
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1419
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1420
id select_type table type possible_keys key key_len ref rows Extra
1421
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1422
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1423
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
1424
id select_type table type possible_keys key key_len ref rows Extra
1425
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where
1426
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1427
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1431
explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1432
id select_type table type possible_keys key key_len ref rows Extra
1433
1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary
1434
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer
1435
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;
1436
fld1 companynr fld3 period
1437
038008 37 reporters 1008
1438
038208 37 Selfridge 1008
1439
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;
1440
fld1 companynr fld3 period
1441
038008 37 reporters 1008
1442
038208 37 Selfridge 1008
1443
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;
1444
fld1 companynr fld3 period
1445
038008 37 reporters 1008
1446
038208 37 Selfridge 1008
1447
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);
1450
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)));
1453
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;
1459
select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1463
select fld1 from t2 where fld1 between 250502 and 250504;
1468
select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
1479
select count(*) from t1;
1482
select companynr,count(*),sum(fld1) from t2 group by companynr;
1483
companynr count(*) sum(fld1)
1496
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1503
select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1504
count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1505
70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1506
explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1507
id select_type table type possible_keys key key_len ref rows filtered Extra
1508
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
1510
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` <> ''))
1511
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1512
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1513
00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
1514
29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026
1515
34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1516
select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1517
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1518
37 1 1 5987435 5987435 5987435 5987435.0000
1519
37 2 1 28357832 28357832 28357832 28357832.0000
1520
37 3 1 39654943 39654943 39654943 39654943.0000
1521
37 11 1 5987435 5987435 5987435 5987435.0000
1522
37 12 1 28357832 28357832 28357832 28357832.0000
1523
37 13 1 39654943 39654943 39654943 39654943.0000
1524
37 21 1 5987435 5987435 5987435 5987435.0000
1525
37 22 1 28357832 28357832 28357832 28357832.0000
1526
37 23 1 39654943 39654943 39654943 39654943.0000
1527
37 31 1 5987435 5987435 5987435 5987435.0000
1528
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;
1529
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1530
37 1 1 5987435 5987435 5987435 5987435.0000
1531
37 2 1 28357832 28357832 28357832 28357832.0000
1532
37 3 1 39654943 39654943 39654943 39654943.0000
1533
37 11 1 5987435 5987435 5987435 5987435.0000
1534
37 12 1 28357832 28357832 28357832 28357832.0000
1535
37 13 1 39654943 39654943 39654943 39654943.0000
1536
37 21 1 5987435 5987435 5987435 5987435.0000
1537
37 22 1 28357832 28357832 28357832 28357832.0000
1538
37 23 1 39654943 39654943 39654943 39654943.0000
1539
37 31 1 5987435 5987435 5987435 5987435.0000
1540
select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
1541
companynr count(price) sum(price) min(price) max(price) avg(price)
1542
37 12543 309394878010 5987435 39654943 24666736.6667
1543
78 8362 414611089292 726498 98439034 49582766.0000
1544
101 4181 3489454238 834598 834598 834598.0000
1545
154 4181 4112197254950 983543950 983543950 983543950.0000
1546
311 4181 979599938 234298 234298 234298.0000
1547
447 4181 9929180954 2374834 2374834 2374834.0000
1548
512 4181 3288532102 786542 786542 786542.0000
1549
select distinct mod(companynr,10) from t4 group by companynr;
1560
select distinct 1 from t4 group by companynr;
1563
select count(distinct fld1) from t2;
1564
count(distinct fld1)
1566
select companynr,count(distinct fld1) from t2 group by companynr;
1567
companynr count(distinct fld1)
1580
select companynr,count(*) from t2 group by companynr;
1594
select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1595
companynr count(distinct concat(fld1,repeat(65,1000)))
1608
select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1609
companynr count(distinct concat(fld1,repeat(65,200)))
1622
select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1623
companynr count(distinct floor(fld1/100))
1636
select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
1637
companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))
1650
select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1653
select name,count(*) from t3 where name='cloakroom' group by name;
1656
select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1659
select count(*) from t3 where name='cloakroom' and price2=823742;
1662
select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1665
select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1674
select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1677
select companynr|0,companyname from t4 group by 1;
1678
companynr|0 companyname
1691
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1692
companynr companyname count(*)
1705
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1708
select sum(Period)/count(*) from t1;
1709
sum(Period)/count(*)
1711
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;
1712
companynr count sum diff func
1713
37 12543 309394878010 0.0000 464091
1714
78 8362 414611089292 0.0000 652236
1715
101 4181 3489454238 0.0000 422281
1716
154 4181 4112197254950 0.0000 643874
1717
311 4181 979599938 0.0000 1300291
1718
447 4181 9929180954 0.0000 1868907
1719
512 4181 3288532102 0.0000 2140672
1720
select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
1723
select companynr,count(*) from t2 group by companynr order by 2 desc;
1737
select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1745
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;
1746
fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)
1747
teethe 000001 1 5987435 5987435 5987435 5987435.0000
1748
dreaded 011401 1 5987435 5987435 5987435 5987435.0000
1749
scholastics 011402 1 28357832 28357832 28357832 28357832.0000
1750
audiology 011403 1 39654943 39654943 39654943 39654943.0000
1751
wallet 011501 1 5987435 5987435 5987435 5987435.0000
1752
parters 011701 1 5987435 5987435 5987435 5987435.0000
1753
eschew 011702 1 28357832 28357832 28357832 28357832.0000
1754
quitter 011703 1 39654943 39654943 39654943 39654943.0000
1755
neat 012001 1 5987435 5987435 5987435 5987435.0000
1756
Steinberg 012003 1 39654943 39654943 39654943 39654943.0000
1757
balled 012301 1 5987435 5987435 5987435 5987435.0000
1758
persist 012302 1 28357832 28357832 28357832 28357832.0000
1759
attainments 012303 1 39654943 39654943 39654943 39654943.0000
1760
capably 012501 1 5987435 5987435 5987435 5987435.0000
1761
impulsive 012602 1 28357832 28357832 28357832 28357832.0000
1762
starlet 012603 1 39654943 39654943 39654943 39654943.0000
1763
featherweight 012701 1 5987435 5987435 5987435 5987435.0000
1764
pessimist 012702 1 28357832 28357832 28357832 28357832.0000
1765
daughter 012703 1 39654943 39654943 39654943 39654943.0000
1766
lawgiver 013601 1 5987435 5987435 5987435 5987435.0000
1767
stated 013602 1 28357832 28357832 28357832 28357832.0000
1768
readable 013603 1 39654943 39654943 39654943 39654943.0000
1769
testicle 013801 1 5987435 5987435 5987435 5987435.0000
1770
Parsifal 013802 1 28357832 28357832 28357832 28357832.0000
1771
leavings 013803 1 39654943 39654943 39654943 39654943.0000
1772
squeaking 013901 1 5987435 5987435 5987435 5987435.0000
1773
contrasted 016001 1 5987435 5987435 5987435 5987435.0000
1774
leftover 016201 1 5987435 5987435 5987435 5987435.0000
1775
whiteners 016202 1 28357832 28357832 28357832 28357832.0000
1776
erases 016301 1 5987435 5987435 5987435 5987435.0000
1777
Punjab 016302 1 28357832 28357832 28357832 28357832.0000
1778
Merritt 016303 1 39654943 39654943 39654943 39654943.0000
1779
sweetish 018001 1 5987435 5987435 5987435 5987435.0000
1780
dogging 018002 1 28357832 28357832 28357832 28357832.0000
1781
scornfully 018003 1 39654943 39654943 39654943 39654943.0000
1782
fetters 018012 1 28357832 28357832 28357832 28357832.0000
1783
bivalves 018013 1 39654943 39654943 39654943 39654943.0000
1784
skulking 018021 1 5987435 5987435 5987435 5987435.0000
1785
flint 018022 1 28357832 28357832 28357832 28357832.0000
1786
flopping 018023 1 39654943 39654943 39654943 39654943.0000
1787
Judas 018032 1 28357832 28357832 28357832 28357832.0000
1788
vacuuming 018033 1 39654943 39654943 39654943 39654943.0000
1789
medical 018041 1 5987435 5987435 5987435 5987435.0000
1790
bloodbath 018042 1 28357832 28357832 28357832 28357832.0000
1791
subschema 018043 1 39654943 39654943 39654943 39654943.0000
1792
interdependent 018051 1 5987435 5987435 5987435 5987435.0000
1793
Graves 018052 1 28357832 28357832 28357832 28357832.0000
1794
neonatal 018053 1 39654943 39654943 39654943 39654943.0000
1795
sorters 018061 1 5987435 5987435 5987435 5987435.0000
1796
epistle 018062 1 28357832 28357832 28357832 28357832.0000
1797
Conley 018101 1 5987435 5987435 5987435 5987435.0000
1798
lectured 018102 1 28357832 28357832 28357832 28357832.0000
1799
Abraham 018103 1 39654943 39654943 39654943 39654943.0000
1800
cage 018201 1 5987435 5987435 5987435 5987435.0000
1801
hushes 018202 1 28357832 28357832 28357832 28357832.0000
1802
Simla 018402 1 28357832 28357832 28357832 28357832.0000
1803
reporters 018403 1 39654943 39654943 39654943 39654943.0000
1804
coexist 018601 1 5987435 5987435 5987435 5987435.0000
1805
Beebe 018602 1 28357832 28357832 28357832 28357832.0000
1806
Taoism 018603 1 39654943 39654943 39654943 39654943.0000
1807
Connally 018801 1 5987435 5987435 5987435 5987435.0000
1808
fetched 018802 1 28357832 28357832 28357832 28357832.0000
1809
checkpoints 018803 1 39654943 39654943 39654943 39654943.0000
1810
gritty 018811 1 5987435 5987435 5987435 5987435.0000
1811
firearm 018812 1 28357832 28357832 28357832 28357832.0000
1812
minima 019101 1 5987435 5987435 5987435 5987435.0000
1813
Selfridge 019102 1 28357832 28357832 28357832 28357832.0000
1814
disable 019103 1 39654943 39654943 39654943 39654943.0000
1815
witchcraft 019201 1 5987435 5987435 5987435 5987435.0000
1816
betroth 030501 1 5987435 5987435 5987435 5987435.0000
1817
Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000
1818
imprint 030503 1 39654943 39654943 39654943 39654943.0000
1819
swelling 031901 1 5987435 5987435 5987435 5987435.0000
1820
interrelationships 036001 1 5987435 5987435 5987435 5987435.0000
1821
riser 036002 1 28357832 28357832 28357832 28357832.0000
1822
bee 038001 1 5987435 5987435 5987435 5987435.0000
1823
kanji 038002 1 28357832 28357832 28357832 28357832.0000
1824
dental 038003 1 39654943 39654943 39654943 39654943.0000
1825
railway 038011 1 5987435 5987435 5987435 5987435.0000
1826
validate 038012 1 28357832 28357832 28357832 28357832.0000
1827
normalizes 038013 1 39654943 39654943 39654943 39654943.0000
1828
Kline 038101 1 5987435 5987435 5987435 5987435.0000
1829
Anatole 038102 1 28357832 28357832 28357832 28357832.0000
1830
partridges 038103 1 39654943 39654943 39654943 39654943.0000
1831
recruited 038201 1 5987435 5987435 5987435 5987435.0000
1832
dimensions 038202 1 28357832 28357832 28357832 28357832.0000
1833
Chicana 038203 1 39654943 39654943 39654943 39654943.0000
1834
select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
1835
companynr fld3 sum(price)
1840
512 descendants 786542
1843
512 Micronesia 786542
1846
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;
1847
companynr count(*) min(fld3) max(fld3) sum(price) avg(price)
1848
00 1 Omaha Omaha 5987435 5987435.0000
1849
36 1 dubbed dubbed 28357832 28357832.0000
1850
37 83 Abraham Wotan 1908978016 22999735.1325
1851
50 2 scribbled tapestry 68012775 34006387.5000
1852
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;
1853
t3.companynr+0 t2nr fld3 sum(price)
1855
37 11401 breaking 5987435
1856
37 11402 Romans 28357832
1857
37 11403 intercepted 39654943
1858
37 11501 bewilderingly 5987435
1859
37 11701 astound 5987435
1860
37 11702 admonishing 28357832
1861
37 11703 sumac 39654943
1862
37 12001 flanking 5987435
1863
37 12003 combed 39654943
1864
37 12301 Eulerian 5987435
1865
37 12302 dubbed 28357832
1866
37 12303 Kane 39654943
1867
37 12501 annihilates 5987435
1868
37 12602 Wotan 28357832
1869
37 12603 snatching 39654943
1870
37 12701 grazing 5987435
1871
37 12702 Baird 28357832
1872
37 12703 celery 39654943
1873
37 13601 handgun 5987435
1874
37 13602 foldout 28357832
1875
37 13603 mystic 39654943
1876
37 13801 intelligibility 5987435
1877
37 13802 Augustine 28357832
1878
37 13803 teethe 39654943
1879
37 13901 scholastics 5987435
1880
37 16001 audiology 5987435
1881
37 16201 wallet 5987435
1882
37 16202 parters 28357832
1883
37 16301 eschew 5987435
1884
37 16302 quitter 28357832
1885
37 16303 neat 39654943
1886
37 18001 jarring 5987435
1887
37 18002 tinily 28357832
1888
37 18003 balled 39654943
1889
37 18012 impulsive 28357832
1890
37 18013 starlet 39654943
1891
37 18021 lawgiver 5987435
1892
37 18022 stated 28357832
1893
37 18023 readable 39654943
1894
37 18032 testicle 28357832
1895
37 18033 Parsifal 39654943
1896
37 18041 Punjab 5987435
1897
37 18042 Merritt 28357832
1898
37 18043 Quixotism 39654943
1899
37 18051 sureties 5987435
1900
37 18052 puddings 28357832
1901
37 18053 tapestry 39654943
1902
37 18061 trimmings 5987435
1903
37 18062 humility 28357832
1904
37 18101 tragedies 5987435
1905
37 18102 skulking 28357832
1906
37 18103 flint 39654943
1907
37 18201 relaxing 5987435
1908
37 18202 offload 28357832
1909
37 18402 suites 28357832
1910
37 18403 lists 39654943
1911
37 18601 vacuuming 5987435
1912
37 18602 dentally 28357832
1913
37 18603 humanness 39654943
1914
37 18801 inch 5987435
1915
37 18802 Weissmuller 28357832
1916
37 18803 irresponsibly 39654943
1917
37 18811 repetitions 5987435
1918
37 18812 Antares 28357832
1919
37 19101 ventilate 5987435
1920
37 19102 pityingly 28357832
1921
37 19103 interdependent 39654943
1922
37 19201 Graves 5987435
1923
37 30501 neonatal 5987435
1924
37 30502 scribbled 28357832
1925
37 30503 chafe 39654943
1926
37 31901 realtor 5987435
1927
37 36001 elite 5987435
1928
37 36002 funereal 28357832
1929
37 38001 Conley 5987435
1930
37 38002 lectured 28357832
1931
37 38003 Abraham 39654943
1932
37 38011 groupings 5987435
1933
37 38012 dissociate 28357832
1934
37 38013 coexist 39654943
1935
37 38101 rusting 5987435
1936
37 38102 galling 28357832
1937
37 38103 obliterates 39654943
1938
37 38201 resumes 5987435
1939
37 38202 analyzable 28357832
1940
37 38203 terminator 39654943
1941
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;
1944
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;
1947
explain select fld3 from t2 where 1>2 or 2>3;
1948
id select_type table type possible_keys key key_len ref rows Extra
1949
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1950
explain select fld3 from t2 where fld1=fld1;
1951
id select_type table type possible_keys key key_len ref rows Extra
1952
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1953
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1957
select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1961
select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1968
select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1975
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
1976
companynr companyname count(*)
1984
select count(*) from t2;
1987
select count(*) from t2 where fld1 < 098024;
1990
select min(fld1) from t2 where fld1>= 098024;
1993
select max(fld1) from t2 where fld1>= 098024;
1996
select count(*) from t3 where price2=76234234;
1999
select count(*) from t3 where companynr=512 and price2=76234234;
2002
explain select min(fld1),max(fld1),count(*) from t2;
2003
id select_type table type possible_keys key key_len ref rows Extra
2004
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2005
select min(fld1),max(fld1),count(*) from t2;
2006
min(fld1) max(fld1) count(*)
2008
select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
2011
select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
2012
count(*) min(t2nr) max(t2nr)
2014
select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
2036
select max(t2nr) from t3 where price=983543950;
2039
select t1.period from t3 = t1 limit 1;
2042
select t1.period from t1 as t1 limit 1;
2045
select t1.period as "Nuvarande period" from t1 as t1 limit 1;
2048
select period as ok_period from t1 limit 1;
2051
select period as ok_period from t1 group by ok_period limit 1;
2054
select 1+1 as summa from t1 group by summa limit 1;
2057
select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
2066
show tables from test like "s%";
2068
show tables from test like "t?";
2070
show full columns from t2;
2071
Field Type Collation Null Key Default Extra Privileges Comment
2072
auto int(11) NULL NO PRI NULL auto_increment #
2073
fld1 int(6) unsigned zerofill NULL NO UNI 000000 #
2074
companynr tinyint(2) unsigned zerofill NULL NO 00 #
2075
fld3 char(30) latin1_swedish_ci NO MUL #
2076
fld4 char(35) latin1_swedish_ci NO #
2077
fld5 char(35) latin1_swedish_ci NO #
2078
fld6 char(4) latin1_swedish_ci NO #
2079
show full columns from t2 from test like 'f%';
2080
Field Type Collation Null Key Default Extra Privileges Comment
2081
fld1 int(6) unsigned zerofill NULL NO UNI 000000 #
2082
fld3 char(30) latin1_swedish_ci NO MUL #
2083
fld4 char(35) latin1_swedish_ci NO #
2084
fld5 char(35) latin1_swedish_ci NO #
2085
fld6 char(4) latin1_swedish_ci NO #
2086
show full columns from t2 from test like 's%';
2087
Field Type Collation Null Key Default Extra Privileges Comment
2089
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
2090
t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE
2091
t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE
2092
t2 1 fld3 1 fld3 A NULL NULL NULL BTREE
2093
drop table t4, t3, t2, t1;
2095
DO benchmark(100,1+1),1,1;
2097
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 '' at line 1
2099
ERROR 42S22: Unknown column 'foobar' in 'field list'
2101
id mediumint(8) unsigned NOT NULL auto_increment,
2102
pseudo varchar(35) NOT NULL default '',
2104
UNIQUE KEY pseudo (pseudo)
2106
INSERT INTO t1 (pseudo) VALUES ('test');
2107
INSERT INTO t1 (pseudo) VALUES ('test1');
2108
SELECT 1 as rnd1 from t1 where rand() > 2;
2111
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;
2112
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);
2113
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;
2114
INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);
2115
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;
2116
gvid the_success the_fail the_size the_time
2118
Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1
2119
Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1
2120
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;
2121
gvid the_success the_fail the_size the_time
2123
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');
2124
INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);
2125
select wss_type from t1 where wss_type ='102935229216544106';
2127
select wss_type from t1 where wss_type ='102935229216544105';
2129
select wss_type from t1 where wss_type ='102935229216544104';
2131
select wss_type from t1 where wss_type ='102935229216544093';
2134
select wss_type from t1 where wss_type =102935229216544093;
2138
select 1+2,"aaaa",3.13*2.0 into @a,@b,@c;
2148
create table t1 (a int not null auto_increment primary key);
2149
insert into t1 values ();
2150
insert into t1 values ();
2151
insert into t1 values ();
2152
select * from (t1 as t2 left join t1 as t3 using (a)), t1;
2163
select * from t1, (t1 as t2 left join t1 as t3 using (a));
2174
select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;
2185
select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));
2196
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
2204
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2212
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
2217
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2222
select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
2230
select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2239
select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a );
2244
select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2249
select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
2254
select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
2259
select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
2268
select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2276
select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
2281
select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2286
select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1;
2291
select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));
2296
select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
2301
select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
2307
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;
2308
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);
2309
CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;
2310
INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);
2311
select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5;
2319
create table t1 (id1 int NOT NULL);
2320
create table t2 (id2 int NOT NULL);
2321
create table t3 (id3 int NOT NULL);
2322
create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4));
2323
insert into t1 values (1);
2324
insert into t1 values (2);
2325
insert into t2 values (1);
2326
insert into t4 values (1,1);
2327
explain 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
id select_type table type possible_keys key key_len ref rows Extra
2330
1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found
2331
1 SIMPLE t4 const id4 NULL NULL NULL 1
2332
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
2333
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where
2334
select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
2335
left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
2336
id1 id2 id3 id4 id44
2338
drop table t1,t2,t3,t4;
2339
create table t1(s varchar(10) not null);
2340
create table t2(s varchar(10) not null primary key);
2341
create table t3(s varchar(10) not null primary key);
2342
insert into t1 values ('one\t'), ('two\t');
2343
insert into t2 values ('one\r'), ('two\t');
2344
insert into t3 values ('one '), ('two\t');
2345
select * from t1 where s = 'one';
2347
select * from t2 where s = 'one';
2349
select * from t3 where s = 'one';
2352
select * from t1,t2 where t1.s = t2.s;
2355
select * from t2,t3 where t2.s = t3.s;
2358
drop table t1, t2, t3;
2359
create table t1 (a integer, b integer, index(a), index(b));
2360
create table t2 (c integer, d integer, index(c), index(d));
2361
insert into t1 values (1,2), (2,2), (3,2), (4,2);
2362
insert into t2 values (1,3), (2,3), (3,4), (4,4);
2363
explain select * from t1 left join t2 on a=c where d in (4);
2364
id select_type table type possible_keys key key_len ref rows Extra
2365
1 SIMPLE t2 ref c,d d 5 const 2 Using where
2366
1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer
2367
select * from t1 left join t2 on a=c where d in (4);
2371
explain select * from t1 left join t2 on a=c where d = 4;
2372
id select_type table type possible_keys key key_len ref rows Extra
2373
1 SIMPLE t2 ref c,d d 5 const 2 Using where
2374
1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer
2375
select * from t1 left join t2 on a=c where d = 4;
2381
i int(11) NOT NULL default '0',
2382
c char(10) NOT NULL default '',
2386
INSERT INTO t1 VALUES (1,'a');
2387
INSERT INTO t1 VALUES (2,'b');
2388
INSERT INTO t1 VALUES (3,'c');
2389
EXPLAIN SELECT i FROM t1 WHERE i=1;
2390
id select_type table type possible_keys key key_len ref rows Extra
2391
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
2393
CREATE TABLE t1 ( a BLOB, INDEX (a(20)) );
2394
CREATE TABLE t2 ( a BLOB, INDEX (a(20)) );
2395
INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
2396
INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
2397
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
2398
id select_type table type possible_keys key key_len ref rows Extra
2399
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2400
1 SIMPLE t2 ref a a 23 test.t1.a 2
2401
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;
2402
id select_type table type possible_keys key key_len ref rows Extra
2403
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2404
1 SIMPLE t2 ref a a 23 test.t1.a 2
2406
CREATE TABLE t1 ( city char(30) );
2407
INSERT INTO t1 VALUES ('London');
2408
INSERT INTO t1 VALUES ('Paris');
2409
SELECT * FROM t1 WHERE city='London';
2412
SELECT * FROM t1 WHERE city='london';
2415
EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london';
2416
id select_type table type possible_keys key key_len ref rows Extra
2417
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
2418
SELECT * FROM t1 WHERE city='London' AND city='london';
2421
EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2422
id select_type table type possible_keys key key_len ref rows Extra
2423
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
2424
SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2428
create table t1 (a int(11) unsigned, b int(11) unsigned);
2429
insert into t1 values (1,0), (1,1), (1,2);
2430
select a-b from t1 order by 1;
2434
18446744073709551615
2435
select a-b , (a-b < 0) from t1 order by 1;
2439
18446744073709551615 0
2440
select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;
2444
18446744073709551615 1 2
2445
select cast((a - b) as unsigned) from t1 order by 1;
2446
cast((a - b) as unsigned)
2449
18446744073709551615
2451
create table t1 (a int(11));
2452
select all all * from t1;
2454
select distinct distinct * from t1;
2456
select all distinct * from t1;
2457
ERROR HY000: Incorrect usage of ALL and DISTINCT
2458
select distinct all * from t1;
2459
ERROR HY000: Incorrect usage of ALL and DISTINCT
2462
kunde_intern_id int(10) unsigned NOT NULL default '0',
2463
kunde_id int(10) unsigned NOT NULL default '0',
2464
FK_firma_id int(10) unsigned NOT NULL default '0',
2465
aktuell enum('Ja','Nein') NOT NULL default 'Ja',
2466
vorname varchar(128) NOT NULL default '',
2467
nachname varchar(128) NOT NULL default '',
2468
geloescht enum('Ja','Nein') NOT NULL default 'Nein',
2469
firma varchar(128) NOT NULL default ''
2471
INSERT INTO t1 VALUES
2472
(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'),
2473
(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX');
2474
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1
2478
( '' != '' AND firma LIKE CONCAT('%', '', '%'))
2480
(vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2481
nachname LIKE CONCAT('%', '1Nachname', '%') AND
2482
'Vorname1' != '' AND 'xxxx' != '')
2486
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2490
kunde_id FK_firma_id aktuell vorname nachname geloescht
2491
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname,
2496
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2500
( '' != '' AND firma LIKE CONCAT('%', '', '%') )
2502
( vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2503
nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND
2508
kunde_id FK_firma_id aktuell vorname nachname geloescht
2509
SELECT COUNT(*) FROM t1 WHERE
2510
( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1))
2511
AND FK_firma_id = 2;
2515
CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b));
2516
INSERT INTO t1 VALUES (0x8000000000000000);
2517
SELECT b FROM t1 WHERE b=0x8000000000000000;
2521
CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL);
2522
CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL);
2523
INSERT INTO `t2` VALUES (0,'READ');
2524
CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL);
2525
INSERT INTO `t3` VALUES (1,'fs');
2526
select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);
2527
id name gid uid ident level
2528
1 fs NULL NULL 0 READ
2529
drop table t1,t2,t3;
2531
acct_id int(11) NOT NULL default '0',
2532
profile_id smallint(6) default NULL,
2533
UNIQUE KEY t1$acct_id (acct_id),
2534
KEY t1$profile_id (profile_id)
2536
INSERT INTO t1 VALUES (132,17),(133,18);
2538
profile_id smallint(6) default NULL,
2539
queue_id int(11) default NULL,
2540
seq int(11) default NULL,
2541
KEY t2$queue_id (queue_id)
2543
INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);
2545
id int(11) NOT NULL default '0',
2546
qtype int(11) default NULL,
2547
seq int(11) default NULL,
2548
warn_lvl int(11) default NULL,
2549
crit_lvl int(11) default NULL,
2550
rr1 tinyint(4) NOT NULL default '0',
2551
rr2 int(11) default NULL,
2552
default_queue tinyint(4) NOT NULL default '0',
2553
KEY t3$qtype (qtype),
2556
INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0),
2557
(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0);
2558
SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q
2560
(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND
2561
(pq.queue_id = q.id) AND (q.rr1 <> 1);
2564
drop table t1,t2,t3;
2565
create table t1 (f1 int);
2566
insert into t1 values (1),(NULL);
2567
create table t2 (f2 int, f3 int, f4 int);
2568
create index idx1 on t2 (f4);
2569
insert into t2 values (1,2,3),(2,4,6);
2570
select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3)
2571
from t2 C where A.f4 = C.f4) or A.f3 IS NULL;
2576
create table t2 (a tinyint unsigned);
2577
create index t2i on t2(a);
2578
insert into t2 values (0), (254), (255);
2579
explain select * from t2 where a > -1;
2580
id select_type table type possible_keys key key_len ref rows Extra
2581
1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index
2582
select * from t2 where a > -1;
2588
CREATE TABLE t1 (a int, b int, c int);
2590
SELECT 50, 3, 3 FROM DUAL
2592
(SELECT * FROM t1 WHERE a = 50 AND b = 3);
2597
SELECT 50, 3, 3 FROM DUAL
2599
(SELECT * FROM t1 WHERE a = 50 AND b = 3);
2600
select found_rows();
2606
select count(*) from t1;
2609
select found_rows();
2612
select count(*) from t1 limit 2,3;
2614
select found_rows();
2617
select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3;
2619
select found_rows();
2623
CREATE TABLE t1 (a INT, b INT);
2624
(SELECT a, b AS c FROM t1) ORDER BY c+1;
2626
(SELECT a, b AS c FROM t1) ORDER BY b+1;
2628
SELECT a, b AS c FROM t1 ORDER BY c+1;
2630
SELECT a, b AS c FROM t1 ORDER BY b+1;
2633
create table t1(f1 int, f2 int);
2634
create table t2(f3 int);
2635
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1));
2637
select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1));
2639
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL));
2641
insert into t1 values(1,1),(2,null);
2642
insert into t2 values(2);
2643
select * from t1,t2 where f1=f3 and (f1,f2) = (2,null);
2645
select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null);
2649
create table t1 (f1 int not null auto_increment primary key, f2 varchar(10));
2650
create table t11 like t1;
2651
insert into t1 values(1,""),(2,"");
2652
show table status like 't1%';
2653
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
2654
t1 MyISAM 10 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL
2655
t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL
2656
select 123 as a from t1 where f1 is null;
2659
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );
2660
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
2661
CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT );
2662
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);
2663
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2664
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2671
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2672
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c;
2678
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2679
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c;
2686
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1
2687
WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2692
DROP TABLE IF EXISTS t1, t2;
2693
create table t1 (f1 int primary key, f2 int);
2694
create table t2 (f3 int, f4 int, primary key(f3,f4));
2695
insert into t1 values (1,1);
2696
insert into t2 values (1,1),(1,2);
2697
select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1;
2701
create table t1 (f1 int,f2 int);
2702
insert into t1 values(1,1);
2703
create table t2 (f3 int, f4 int, primary key(f3,f4));
2704
insert into t2 values(1,1);
2705
select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2));
2709
CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));
2710
insert into t1 values (1,0,0),(2,0,0);
2711
CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));
2712
insert into t2 values (1,'',''), (2,'','');
2713
CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
2714
insert into t3 values (1,1),(1,2);
2715
explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2
2716
where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and
2717
t2.b like '%%' order by t2.b limit 0,1;
2718
id select_type table type possible_keys key key_len ref rows Extra
2719
1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort
2720
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer
2721
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
2722
DROP TABLE t1,t2,t3;
2723
CREATE TABLE t1 (a int, INDEX idx(a));
2724
INSERT INTO t1 VALUES (2), (3), (1);
2725
EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);
2726
id select_type table type possible_keys key key_len ref rows Extra
2727
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
2728
EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);
2729
ERROR 42000: Key 'a' doesn't exist in table 't1'
2730
EXPLAIN SELECT * FROM t1 FORCE INDEX (a);
2731
ERROR 42000: Key 'a' doesn't exist in table 't1'
2733
CREATE TABLE t1 (a int, b int);
2734
INSERT INTO t1 VALUES (1,1), (2,1), (4,10);
2735
CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b));
2736
INSERT INTO t2 VALUES (1,NULL), (2,10);
2737
ALTER TABLE t1 ENABLE KEYS;
2738
EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2739
id select_type table type possible_keys key key_len ref rows Extra
2740
1 SIMPLE t2 index b b 5 NULL 2 Using index
2741
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2742
SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2748
EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2749
id select_type table type possible_keys key key_len ref rows Extra
2750
1 SIMPLE t2 index b b 5 NULL 2 Using index
2751
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2752
SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2758
DROP TABLE IF EXISTS t1,t2;
2759
CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1));
2760
CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));
2761
INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);
2762
INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);
2763
explain select max(key1) from t1 where key1 <= 0.6158;
2764
id select_type table type possible_keys key key_len ref rows Extra
2765
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2766
explain select max(key2) from t2 where key2 <= 1.6158;
2767
id select_type table type possible_keys key key_len ref rows Extra
2768
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2769
explain select min(key1) from t1 where key1 >= 0.3762;
2770
id select_type table type possible_keys key key_len ref rows Extra
2771
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2772
explain select min(key2) from t2 where key2 >= 1.3762;
2773
id select_type table type possible_keys key key_len ref rows Extra
2774
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2775
explain select max(key1), min(key2) from t1, t2
2776
where key1 <= 0.6158 and key2 >= 1.3762;
2777
id select_type table type possible_keys key key_len ref rows Extra
2778
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2779
explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
2780
id select_type table type possible_keys key key_len ref rows Extra
2781
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2782
explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
2783
id select_type table type possible_keys key key_len ref rows Extra
2784
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2785
select max(key1) from t1 where key1 <= 0.6158;
2788
select max(key2) from t2 where key2 <= 1.6158;
2791
select min(key1) from t1 where key1 >= 0.3762;
2794
select min(key2) from t2 where key2 >= 1.3762;
2797
select max(key1), min(key2) from t1, t2
2798
where key1 <= 0.6158 and key2 >= 1.3762;
2800
0.615800023078918 1.37619996070862
2801
select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
2804
select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
2808
CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
2809
INSERT INTO t1 VALUES (10);
2810
SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;
2811
i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01')
2814
create table t1(a bigint unsigned, b bigint);
2815
insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff),
2816
(0x10000000000000000, 0x10000000000000000),
2817
(0x8fffffffffffffff, 0x8fffffffffffffff);
2819
Warning 1264 Out of range value for column 'a' at row 1
2820
Warning 1264 Out of range value for column 'b' at row 1
2821
Warning 1264 Out of range value for column 'a' at row 2
2822
Warning 1264 Out of range value for column 'b' at row 2
2823
Warning 1264 Out of range value for column 'b' at row 3
2824
select hex(a), hex(b) from t1;
2826
FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF
2827
FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF
2828
8FFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF
2830
CREATE TABLE t1 (c0 int);
2831
CREATE TABLE t2 (c0 int);
2832
INSERT INTO t1 VALUES(@@connect_timeout);
2833
INSERT INTO t2 VALUES(@@connect_timeout);
2834
SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout);
2840
K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '',
2841
K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000',
2842
F2I4 int(11) NOT NULL default '0'
2843
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2844
INSERT INTO t1 VALUES
2845
('W%RT', '0100', 1),
2846
('W-RT', '0100', 1),
2847
('WART', '0100', 1),
2848
('WART', '0200', 1),
2849
('WERT', '0100', 2),
2852
('W_RT', '0100', 2),
2853
('WaRT', '0100', 3),
2854
('WART', '0300', 3),
2855
('WRT' , '0400', 3),
2856
('WURM', '0500', 3),
2858
('WA%T', '0700', 4),
2859
('WA_T', '0800', 4);
2860
SELECT K2C4, K4N4, F2I4 FROM t1
2861
WHERE K2C4 = 'WART' AND
2862
(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200'));
2865
SELECT K2C4, K4N4, F2I4 FROM t1
2866
WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200');
2872
create table t1 (a int, b int);
2873
create table t2 like t1;
2874
select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1;
2876
select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1;
2878
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;
2881
create table t1 (s1 varchar(5));
2882
insert into t1 values ('Wall');
2883
select min(s1) from t1 group by s1 with rollup;
2888
create table t1 (s1 int) engine=myisam;
2889
insert into t1 values (0);
2890
select avg(distinct s1) from t1 group by s1 with rollup;
2895
create table t1 (s1 int);
2896
insert into t1 values (null),(1);
2897
select distinct avg(s1) as x from t1 group by s1 with rollup;
2902
CREATE TABLE t1 (a int);
2903
CREATE TABLE t2 (a int);
2904
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
2905
INSERT INTO t2 VALUES (2), (4), (6);
2906
SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2910
EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2911
id select_type table type possible_keys key key_len ref rows Extra
2912
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2913
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
2914
EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a;
2915
id select_type table type possible_keys key key_len ref rows Extra
2916
1 SIMPLE t2 ALL NULL NULL NULL NULL 3
2917
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer
2919
select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
2920
x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0
2922
create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);
2923
create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));
2924
insert into t1 values (" 2", 2);
2925
insert into t2 values (" 2", " one "),(" 2", " two ");
2926
select * from t1 left join t2 on f1 = f3;
2931
create table t1 (empnum smallint, grp int);
2932
create table t2 (empnum int, name char(5));
2933
insert into t1 values(1,1);
2934
insert into t2 values(1,'bob');
2935
create view v1 as select * from t2 inner join t1 using (empnum);
2941
create table t1 (pk int primary key, b int);
2942
create table t2 (pk int primary key, c int);
2943
select pk from t1 inner join t2 using (pk);
2946
create table t1 (s1 int, s2 char(5), s3 decimal(10));
2947
create view v1 as select s1, s2, 'x' as s3 from t1;
2948
select * from t1 natural join v1;
2950
insert into t1 values (1,'x',5);
2951
select * from t1 natural join v1;
2954
Warning 1292 Truncated incorrect DOUBLE value: 'x'
2957
create table t1(a1 int);
2958
create table t2(a2 int);
2959
insert into t1 values(1),(2);
2960
insert into t2 values(1),(2);
2961
create view v2 (c) as select a1 from t1;
2962
select * from t1 natural left join t2;
2968
select * from t1 natural right join t2;
2974
select * from v2 natural left join t2;
2980
select * from v2 natural right join t2;
2988
create table t1 (a int(10), t1_val int(10));
2989
create table t2 (b int(10), t2_val int(10));
2990
create table t3 (a int(10), b int(10));
2991
insert into t1 values (1,1),(2,2);
2992
insert into t2 values (1,1),(2,2),(3,3);
2993
insert into t3 values (1,1),(2,1),(3,1),(4,1);
2994
select * from t1 natural join t2 natural join t3;
2998
select * from t1 natural join t3 natural join t2;
3002
drop table t1, t2, t3;
3003
DO IFNULL(NULL, NULL);
3004
SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL);
3005
CAST(IFNULL(NULL, NULL) AS DECIMAL)
3007
SELECT ABS(IFNULL(NULL, NULL));
3008
ABS(IFNULL(NULL, NULL))
3010
SELECT IFNULL(NULL, NULL);
3013
SET @OLD_SQL_MODE12595=@@SQL_MODE, @@SQL_MODE='';
3014
SHOW LOCAL VARIABLES LIKE 'SQL_MODE';
3017
CREATE TABLE BUG_12595(a varchar(100));
3018
INSERT INTO BUG_12595 VALUES ('hakan%'), ('hakank'), ("ha%an");
3019
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%';
3022
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*';
3025
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**';
3026
ERROR HY000: Incorrect arguments to ESCAPE
3027
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE '';
3031
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '';
3033
SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c;
3036
SELECT * FROM BUG_12595 WHERE a LIKE 'ha%%an' ESCAPE '%';
3039
SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE '\\';
3042
SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|';
3045
SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
3046
SHOW LOCAL VARIABLES LIKE 'SQL_MODE';
3048
sql_mode NO_BACKSLASH_ESCAPES
3049
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%';
3051
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*';
3054
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**';
3055
ERROR HY000: Incorrect arguments to ESCAPE
3056
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\';
3057
ERROR HY000: Incorrect arguments to ESCAPE
3058
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE '';
3059
ERROR HY000: Incorrect arguments to ESCAPE
3060
SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c;
3063
SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|';
3066
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n';
3067
ERROR HY000: Incorrect arguments to ESCAPE
3068
SET @@SQL_MODE=@OLD_SQL_MODE12595;
3069
DROP TABLE BUG_12595;
3070
create table t1 (a char(1));
3071
create table t2 (a char(1));
3072
insert into t1 values ('a'),('b'),('c');
3073
insert into t2 values ('b'),('c'),('d');
3074
select a from t1 natural join t2;
3078
select * from t1 natural join t2 where a = 'b';
3082
CREATE TABLE t1 (`id` TINYINT);
3083
CREATE TABLE t2 (`id` TINYINT);
3084
CREATE TABLE t3 (`id` TINYINT);
3085
INSERT INTO t1 VALUES (1),(2),(3);
3086
INSERT INTO t2 VALUES (2);
3087
INSERT INTO t3 VALUES (3);
3088
SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
3089
ERROR 23000: Column 'id' in from clause is ambiguous
3090
SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);
3091
ERROR 23000: Column 'id' in from clause is ambiguous
3092
SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
3093
ERROR 23000: Column 'id' in from clause is ambiguous
3094
SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);
3095
ERROR 23000: Column 'id' in from clause is ambiguous
3096
drop table t1, t2, t3;
3097
create table t1 (a int(10),b int(10));
3098
create table t2 (a int(10),b int(10));
3099
insert into t1 values (1,10),(2,20),(3,30);
3100
insert into t2 values (1,10);
3101
select * from t1 inner join t2 using (A);
3104
select * from t1 inner join t2 using (a);
3108
create table t1 (a int, c int);
3109
create table t2 (b int);
3110
create table t3 (b int, a int);
3111
create table t4 (c int);
3112
insert into t1 values (1,1);
3113
insert into t2 values (1);
3114
insert into t3 values (1,1);
3115
insert into t4 values (1);
3116
select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
3119
select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
3120
ERROR 42S22: Unknown column 't1.a' in 'on clause'
3121
select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c);
3124
select * from t1 join t2 join t4 using (c);
3127
drop table t1, t2, t3, t4;
3128
create table t1(x int, y int);
3129
create table t2(x int, y int);
3130
create table t3(x int, primary key(x));
3131
insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6);
3132
insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6);
3133
insert into t3 values (1), (2), (3), (4), (5);
3134
select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y;
3144
drop table t1,t2,t3;
3145
create table t1 (id char(16) not null default '', primary key (id));
3146
insert into t1 values ('100'),('101'),('102');
3147
create table t2 (id char(16) default null);
3148
insert into t2 values (1);
3149
create view v1 as select t1.id from t1;
3150
create view v2 as select t2.id from t2;
3151
create view v3 as select (t1.id+2) as id from t1 natural left join t2;
3152
select t1.id from t1 left join v2 using (id);
3157
select t1.id from v2 right join t1 using (id);
3162
select t1.id from t1 left join v3 using (id);
3167
select * from t1 left join v2 using (id);
3172
select * from v2 right join t1 using (id);
3177
select * from t1 left join v3 using (id);
3182
select v1.id from v1 left join v2 using (id);
3187
select v1.id from v2 right join v1 using (id);
3192
select v1.id from v1 left join v3 using (id);
3197
select * from v1 left join v2 using (id);
3202
select * from v2 right join v1 using (id);
3207
select * from v1 left join v3 using (id);
3213
drop view v1, v2, v3;
3214
create table t1 (id int(11) not null default '0');
3215
insert into t1 values (123),(191),(192);
3216
create table t2 (id char(16) character set utf8 not null);
3217
insert into t2 values ('58013'),('58014'),('58015'),('58016');
3218
create table t3 (a_id int(11) not null, b_id char(16) character set utf8);
3219
insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');
3221
from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id;
3225
from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id;
3228
drop table t1,t2,t3;
3229
create table t1 (a int);
3230
create table t2 (b int);
3231
create table t3 (c int);
3232
select * from t1 join t2 join t3 on (t1.a=t3.c);
3234
select * from t1 join t2 left join t3 on (t1.a=t3.c);
3236
select * from t1 join t2 right join t3 on (t1.a=t3.c);
3238
select * from t1 join t2 straight_join t3 on (t1.a=t3.c);
3240
drop table t1, t2 ,t3;
3241
create table t1(f1 int, f2 date);
3242
insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),
3243
(4,'2005-10-01'),(5,'2005-12-30');
3244
select * from t1 where f2 >= 0 order by f2;
3251
select * from t1 where f2 >= '0000-00-00' order by f2;
3258
select * from t1 where f2 >= '2005-09-31' order by f2;
3262
select * from t1 where f2 >= '2005-09-3a' order by f2;
3268
Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1
3269
select * from t1 where f2 <= '2005-09-31' order by f2;
3274
select * from t1 where f2 <= '2005-09-3a' order by f2;
3279
Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1
3281
create table t1 (f1 int, f2 int);
3282
insert into t1 values (1, 30), (2, 20), (3, 10);
3283
create algorithm=merge view v1 as select f1, f2 from t1;
3284
create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1;
3285
create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1;
3286
select t1.f1 as x1, f1 from t1 order by t1.f1;
3291
select v1.f1 as x1, f1 from v1 order by v1.f1;
3296
select v2.f1 as x1, f1 from v2 order by v2.f1;
3301
select v3.f1 as x1, f1 from v3 order by v3.f1;
3306
select f1, f2, v1.f1 as x1 from v1 order by v1.f1;
3311
select f1, f2, v2.f1 as x1 from v2 order by v2.f1;
3316
select f1, f2, v3.f1 as x1 from v3 order by v3.f1;
3322
drop view v1, v2, v3;
3323
CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a));
3324
CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a));
3325
CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32),
3326
PRIMARY KEY(key_a,key_b));
3327
INSERT INTO t1 VALUES (0,'');
3328
INSERT INTO t1 VALUES (1,'i');
3329
INSERT INTO t1 VALUES (2,'j');
3330
INSERT INTO t1 VALUES (3,'k');
3331
INSERT INTO t2 VALUES (1,'r');
3332
INSERT INTO t2 VALUES (2,'s');
3333
INSERT INTO t2 VALUES (3,'t');
3334
INSERT INTO t3 VALUES (1,5,'x');
3335
INSERT INTO t3 VALUES (1,6,'y');
3336
INSERT INTO t3 VALUES (2,5,'xx');
3337
INSERT INTO t3 VALUES (2,6,'yy');
3338
INSERT INTO t3 VALUES (2,7,'zz');
3339
INSERT INTO t3 VALUES (3,5,'xxx');
3341
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
3342
INNER JOIN t3 ON t1.key_a = t3.key_a
3343
WHERE t2.key_a=2 and key_b=5;
3346
EXPLAIN SELECT t2.key_a,foo
3347
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
3348
INNER JOIN t3 ON t1.key_a = t3.key_a
3349
WHERE t2.key_a=2 and key_b=5;
3350
id select_type table type possible_keys key key_len ref rows Extra
3351
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
3352
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index
3353
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
3355
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
3356
INNER JOIN t3 ON t1.key_a = t3.key_a
3357
WHERE t2.key_a=2 and key_b=5;
3360
EXPLAIN SELECT t2.key_a,foo
3361
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
3362
INNER JOIN t3 ON t1.key_a = t3.key_a
3363
WHERE t2.key_a=2 and key_b=5;
3364
id select_type table type possible_keys key key_len ref rows Extra
3365
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
3366
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index
3367
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
3368
DROP TABLE t1,t2,t3;
3369
create table t1 (f1 int);
3370
insert into t1 values(1),(2);
3371
create table t2 (f2 int, f3 int, key(f2));
3372
insert into t2 values(1,1),(2,2);
3373
create table t3 (f4 int not null);
3374
insert into t3 values (2),(2),(2);
3375
select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1;
3379
drop table t1,t2,t3;
3380
create table t1 (f1 int unique);
3381
create table t2 (f2 int unique);
3382
create table t3 (f3 int unique);
3383
insert into t1 values(1),(2);
3384
insert into t2 values(1),(2);
3385
insert into t3 values(1),(NULL);
3386
select * from t3 where f3 is null;
3389
select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1;
3392
drop table t1,t2,t3;
3393
create table t1(f1 char, f2 char not null);
3394
insert into t1 values(null,'a');
3395
create table t2 (f2 char not null);
3396
insert into t2 values('b');
3397
select * from t1 left join t2 on f1=t2.f2 where t1.f2='a';
3401
select * from (select * left join t on f1=f2) tt;
3402
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
3403
CREATE TABLE t1 (sku int PRIMARY KEY, pr int);
3404
CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255));
3405
INSERT INTO t1 VALUES
3406
(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10);
3407
INSERT INTO t2 VALUES
3408
(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),
3409
(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');
3410
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
3411
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
3412
sku sppr name sku pr
3416
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
3417
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
3418
id select_type table type possible_keys key key_len ref rows Extra
3419
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
3420
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where
3422
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
3423
INSERT t1 SET i = 0;
3424
UPDATE t1 SET i = -1;
3426
Warning 1264 Out of range value for column 'i' at row 1
3430
UPDATE t1 SET i = CAST(i - 1 AS SIGNED);
3432
Warning 1264 Out of range value for column 'i' at row 1
3436
UPDATE t1 SET i = i - 1;
3438
Warning 1264 Out of range value for column 'i' at row 1
3443
create table t1 (a int);
3444
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
3445
create table t2 (a int, b int, c int, e int, primary key(a,b,c));
3446
insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C;
3448
Table Op Msg_type Msg_text
3449
test.t2 analyze status OK
3450
select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
3452
In next EXPLAIN, B.rows must be exactly 10:
3453
explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
3454
and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
3455
id select_type table type possible_keys key key_len ref rows Extra
3456
1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using where
3457
1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10
3459
CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
3460
INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),
3461
(3,1), (5,1), (8,9), (2,2), (0,9);
3462
CREATE TABLE t2 (c int, d int, f int, INDEX(c,f));
3463
INSERT INTO t2 VALUES
3464
(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1),
3465
(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1),
3466
(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1);
3468
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
3469
id select_type table type possible_keys key key_len ref rows Extra
3470
1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where
3471
1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where
3473
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
3474
id select_type table type possible_keys key key_len ref rows Extra
3475
1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where
3476
1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where
3479
a int unsigned not null auto_increment primary key,
3484
a int unsigned not null auto_increment primary key,
3486
c int unsigned not null,
3489
insert into t1 (b,c) values (0,1), (0,1);
3490
insert into t2 (b,c) values (0,1);
3491
select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d
3492
from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1
3493
where t1.b <> 1 order by t1.a;
3494
a t1.b + 0 t1.c + 0 a t2.b + 0 c d
3496
2 0 1 NULL NULL NULL NULL
3498
SELECT 0.9888889889 * 1.011111411911;
3499
0.9888889889 * 1.011111411911
3500
0.9998769417899202067879
3501
prepare stmt from 'select 1 as " a "';
3503
Warning 1466 Leading spaces are removed from name ' a '
3507
CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
3508
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
3509
CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
3510
INSERT INTO t2 VALUES
3511
(1), (1), (1), (1), (1), (1), (1), (1),
3515
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
3516
id select_type table type possible_keys key key_len ref rows Extra
3517
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3518
1 SIMPLE t2 ref idx idx 4 const 7 Using index
3519
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
3520
id select_type table type possible_keys key key_len ref rows Extra
3521
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3522
1 SIMPLE t2 ref idx idx 4 const 1 Using index
3524
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
3525
INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
3526
CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
3527
INSERT INTO t2 VALUES (2,1), (3,2);
3528
CREATE TABLE t3 (d int, e int, INDEX idx1(d));
3529
INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
3531
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3533
id select_type table type possible_keys key key_len ref rows Extra
3534
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3535
1 SIMPLE t2 const idx1 NULL NULL NULL 1
3536
1 SIMPLE t3 ref idx1 idx1 5 const 3 Using where
3537
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3540
2 NULL NULL NULL 2 10
3541
2 NULL NULL NULL 2 20
3542
2 NULL NULL NULL 2 40
3543
2 NULL NULL NULL 2 50
3544
DROP TABLE t1,t2,t3;
3545
create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
3546
c7 int, c8 int, c9 int, fulltext key (`c1`));
3547
select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8
3548
from t1 where c9=1 order by c2, c2;
3549
match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8
3551
CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
3552
CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
3553
INSERT INTO t1 VALUES
3554
('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'),
3555
('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
3556
INSERT INTO t2 VALUES
3557
('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
3558
('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
3559
('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
3560
('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
3562
FROM t1 JOIN t2 ON t2.fk=t1.pk
3563
WHERE t2.fk < 'c' AND t2.pk=t1.fk;
3564
id select_type table type possible_keys key key_len ref rows Extra
3565
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where
3566
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
3568
FROM t1 JOIN t2 ON t2.fk=t1.pk
3569
WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
3570
id select_type table type possible_keys key key_len ref rows Extra
3571
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
3572
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
3574
FROM t1 JOIN t2 ON t2.fk=t1.pk
3575
WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
3576
id select_type table type possible_keys key key_len ref rows Extra
3577
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
3578
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
3580
CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
3581
CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
3582
PRIMARY KEY (a), UNIQUE KEY (b));
3583
INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
3584
INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
3585
EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
3586
id select_type table type possible_keys key key_len ref rows Extra
3587
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3588
1 SIMPLE t2 const b b 22 const 1 Using index
3590
CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
3591
CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
3592
CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
3593
INSERT INTO t1 VALUES
3594
(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
3595
(6,63,67), (5,55,58), (3,38,39), (8,81,89);
3596
INSERT INTO t2 VALUES
3597
(21,210), (41,410), (82,820), (83,830), (84,840),
3598
(65,650), (51,510), (37,370), (94,940), (76,760),
3599
(22,220), (33,330), (40,400), (95,950), (38,380),
3600
(67,670), (88,880), (57,570), (96,960), (97,970);
3601
INSERT INTO t3 VALUES
3602
(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
3603
(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
3604
(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
3605
(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
3607
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3608
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3609
t3.a=t2.a AND t3.c IN ('bb','ee');
3610
id select_type table type possible_keys key key_len ref rows Extra
3611
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3612
1 SIMPLE t2 range si si 5 NULL 4 Using where
3613
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3615
SELECT t3.a FROM t1,t2,t3
3616
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3617
t3.a=t2.a AND t3.c IN ('bb','ee') ;
3618
id select_type table type possible_keys key key_len ref rows Extra
3619
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3620
1 SIMPLE t2 range si,ai si 5 NULL 4 Using where
3621
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3623
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3624
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3625
t3.c IN ('bb','ee');
3626
id select_type table type possible_keys key key_len ref rows Extra
3627
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3628
1 SIMPLE t2 range si si 5 NULL 2 Using where
3629
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3631
SELECT t3.a FROM t1,t2,t3
3632
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3633
t3.c IN ('bb','ee');
3634
id select_type table type possible_keys key key_len ref rows Extra
3635
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3636
1 SIMPLE t2 range si,ai si 5 NULL 2 Using where
3637
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
3638
DROP TABLE t1,t2,t3;
3639
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
3640
CREATE TABLE t2 ( f11 int PRIMARY KEY );
3641
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);
3642
INSERT INTO t2 VALUES (62);
3643
SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;
3644
f1 f2 f3 f4 f5 f6 checked_out f11
3647
DROP TABLE IF EXISTS t1;
3648
CREATE TABLE t1(a int);
3649
INSERT into t1 values (1), (2), (3);
3650
SELECT * FROM t1 LIMIT 2, -1;
3651
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
3654
ID_with_null int NULL,
3655
ID_better int NOT NULL,
3656
INDEX idx1 (ID_with_null),
3657
INDEX idx2 (ID_better)
3659
INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3);
3660
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3661
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3662
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3663
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3664
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3665
SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL;
3668
SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3671
EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3672
id select_type table type possible_keys key key_len ref rows Extra
3673
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3674
DROP INDEX idx1 ON t1;
3675
CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);
3676
EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3677
id select_type table type possible_keys key key_len ref rows Extra
3678
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3681
ID1_with_null int NULL,
3682
ID2_with_null int NULL,
3683
ID_better int NOT NULL,
3684
INDEX idx1 (ID1_with_null, ID2_with_null),
3685
INDEX idx2 (ID_better)
3687
INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3),
3688
(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);
3689
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3690
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3691
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3692
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3693
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3694
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3695
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
3698
SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL;
3701
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;
3704
SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3707
EXPLAIN SELECT * FROM t1
3708
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3709
id select_type table type possible_keys key key_len ref rows Extra
3710
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3711
EXPLAIN SELECT * FROM t1
3712
WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;
3713
id select_type table type possible_keys key key_len ref rows Extra
3714
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3715
EXPLAIN SELECT * FROM t1
3716
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3717
id select_type table type possible_keys key key_len ref rows Extra
3718
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3719
DROP INDEX idx1 ON t1;
3720
CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);
3721
EXPLAIN SELECT * FROM t1
3722
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3723
id select_type table type possible_keys key key_len ref rows Extra
3724
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3725
EXPLAIN SELECT * FROM t1
3726
WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ;
3727
id select_type table type possible_keys key key_len ref rows Extra
3728
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3729
EXPLAIN SELECT * FROM t1
3730
WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3731
id select_type table type possible_keys key key_len ref rows Extra
3732
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3733
EXPLAIN SELECT * FROM t1
3734
WHERE ID_better=1 AND ID1_with_null IS NULL AND
3735
(ID2_with_null=1 OR ID2_with_null=2);
3736
id select_type table type possible_keys key key_len ref rows Extra
3737
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
3739
CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
3740
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
3742
Table Op Msg_type Msg_text
3743
test.t1 analyze status OK
3744
CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
3745
INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
3746
INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
3748
Table Op Msg_type Msg_text
3749
test.t2 analyze status OK
3751
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3752
AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3753
AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3754
id select_type table type possible_keys key key_len ref rows Extra
3755
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
3756
1 SIMPLE t1 range ts ts 4 NULL 1 Using where
3758
Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
3759
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3760
AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3761
AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3763
30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00
3765
Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
3767
create table t1 (a bigint unsigned);
3768
insert into t1 values
3769
(if(1, 9223372036854775808, 1)),
3770
(case when 1 then 9223372036854775808 else 1 end),
3771
(coalesce(9223372036854775808, 1));
3778
create table t1 select
3779
if(1, 9223372036854775808, 1) i,
3780
case when 1 then 9223372036854775808 else 1 end c,
3781
coalesce(9223372036854775808, 1) co;
3782
show create table t1;
3784
t1 CREATE TABLE `t1` (
3785
`i` decimal(19,0) NOT NULL DEFAULT '0',
3786
`c` decimal(19,0) NOT NULL DEFAULT '0',
3787
`co` decimal(19,0) NOT NULL DEFAULT '0'
3788
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3791
if(1, cast(1111111111111111111 as unsigned), 1) i,
3792
case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,
3793
coalesce(cast(1111111111111111111 as unsigned), 1) co;
3795
1111111111111111111 1111111111111111111 1111111111111111111
3796
CREATE TABLE t1 (name varchar(255));
3797
CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
3798
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
3799
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
3800
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3801
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3811
SELECT * FROM t2 ORDER BY name;
3820
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3829
EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3830
id select_type table type possible_keys key key_len ref rows Extra
3831
1 SIMPLE t2 ref name name 6 const 3 Using where
3832
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3837
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3838
id select_type table type possible_keys key key_len ref rows Extra
3839
1 SIMPLE t2 range name name 6 NULL 3 Using where
3840
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3846
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3847
id select_type table type possible_keys key key_len ref rows Extra
3848
1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort
3849
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3855
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3856
id select_type table type possible_keys key key_len ref rows Extra
3857
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3858
1 SIMPLE t2 ref name name 6 test.t1.name 2
3859
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3870
CREATE TABLE t1 (name text);
3871
CREATE TABLE t2 (name text, n int, KEY (name(3)));
3872
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
3873
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
3874
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3875
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3885
SELECT * FROM t2 ORDER BY name;
3894
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3903
EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3904
id select_type table type possible_keys key key_len ref rows Extra
3905
1 SIMPLE t2 ref name name 6 const 3 Using where
3906
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3911
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3912
id select_type table type possible_keys key key_len ref rows Extra
3913
1 SIMPLE t2 range name name 6 NULL 3 Using where
3914
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3920
EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3921
id select_type table type possible_keys key key_len ref rows Extra
3922
1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort
3923
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3929
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3930
id select_type table type possible_keys key key_len ref rows Extra
3931
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3932
1 SIMPLE t2 ref name name 6 test.t1.name 2
3933
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3945
access_id int NOT NULL default '0',
3946
name varchar(20) default NULL,
3947
rank int NOT NULL default '0',
3951
faq_group_id int NOT NULL default '0',
3952
faq_id int NOT NULL default '0',
3953
access_id int default NULL,
3954
UNIQUE KEY idx1 (faq_id),
3955
KEY idx2 (faq_group_id,faq_id)
3957
INSERT INTO t1 VALUES
3958
(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
3959
INSERT INTO t2 VALUES
3960
(261,265,1),(490,494,1);
3962
FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
3963
ON (t1.access_id = t2.access_id)
3965
ON (t.faq_group_id = t2.faq_group_id AND
3966
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3968
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3972
FROM t1 INNER JOIN t2
3973
ON (t1.access_id = t2.access_id)
3975
ON (t.faq_group_id = t2.faq_group_id AND
3976
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3978
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3982
CREATE TABLE t1 (a INT, b INT, KEY inx (b,a));
3983
INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7);
3984
EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2
3985
ON ( f1.b=f2.b AND f1.a<f2.a )
3986
WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);
3987
id select_type table type possible_keys key key_len ref rows Extra
3988
1 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index
3989
1 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index
3991
CREATE TABLE t1 (c1 INT, c2 INT);
3992
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3993
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;
3994
id select_type table type possible_keys key key_len ref rows Extra
3995
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
3996
31 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3997
32 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3998
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;
3999
ERROR HY000: Too high level of nesting for select
4002
c1 int(11) NOT NULL AUTO_INCREMENT,
4003
c2 varchar(1000) DEFAULT NULL,
4004
c3 bigint(20) DEFAULT NULL,
4005
c4 bigint(20) DEFAULT NULL,
4020
join_0.c1=join_1.c1 AND
4021
join_1.c1=join_2.c1 AND
4022
join_2.c1=join_3.c1 AND
4023
join_3.c1=join_4.c1 AND
4024
join_4.c1=join_5.c1 AND
4025
join_5.c1=join_6.c1 AND
4034
join_5.c2 <> '?' AND
4035
join_6.c2 <> '?' AND
4036
join_7.c2 >= '?' AND
4037
join_0.c1=join_1.c1 AND
4038
join_1.c1=join_2.c1 AND
4039
join_2.c1=join_3.c1 AND
4040
join_3.c1=join_4.c1 AND
4041
join_4.c1=join_5.c1 AND
4042
join_5.c1=join_6.c1 AND
4050
id select_type table type possible_keys key key_len ref rows filtered Extra
4051
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
4053
Note 1003 select NULL 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 NULL,NULL,NULL,NULL,NULL
4056
Note 1003 select NULL 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 NULL,NULL,NULL,NULL,NULL
4062
Warning 1474 Name ' ' has become ''
4067
Warning 1474 Name ' ' has become ''
4072
Warning 1466 Leading spaces are removed from name ' x'
4073
CREATE VIEW v1 AS SELECT 1 AS ``;
4074
ERROR 42000: Incorrect column name ''
4075
CREATE VIEW v1 AS SELECT 1 AS ` `;
4076
ERROR 42000: Incorrect column name ' '
4077
CREATE VIEW v1 AS SELECT 1 AS ` `;
4078
ERROR 42000: Incorrect column name ' '
4079
CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `);
4080
ERROR 42000: Incorrect column name ' '
4081
CREATE VIEW v1 AS SELECT 1 AS ` x`;
4083
Warning 1466 Leading spaces are removed from name ' x'
4087
ALTER VIEW v1 AS SELECT 1 AS ` `;
4088
ERROR 42000: Incorrect column name ' '
4090
select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
4091
and '2007/10/20 00:00:00 GMT';
4092
str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
4093
and '2007/10/20 00:00:00 GMT'
4096
Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT'
4097
Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT'
4098
select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
4099
str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'
4102
Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6'
4103
select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
4104
str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'
4107
Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6'
4108
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
4109
str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'
4112
Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6'
4113
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';
4114
str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'
4117
Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6'
4118
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';
4119
str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'
4122
Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6'
4123
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';
4124
str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'
4127
Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6'
4128
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';
4129
str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'
4132
Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6'
4133
select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
4134
str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
4137
Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
4138
select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
4139
str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
4142
Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
4143
select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';
4144
str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'
4146
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';
4147
str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'
4149
select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';
4150
str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'
4152
select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';
4153
str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'
4156
Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34'
4157
select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';
4158
str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'
4160
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
4161
str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
4163
select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
4164
and '2007/10/20 00:00:00';
4165
str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
4166
and '2007/10/20 00:00:00'
4168
set SQL_MODE=TRADITIONAL;
4169
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
4170
str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
4173
Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34'
4174
select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
4175
str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
4178
Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34'
4179
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
4180
str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
4183
Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34:00'
4184
select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
4186
str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
4190
Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/09/01' at row 1
4191
Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/10/20' at row 1
4192
set SQL_MODE=DEFAULT;
4193
select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
4194
str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
4197
Warning 1292 Truncated incorrect datetime value: ''
4198
select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';
4199
str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'
4201
select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
4202
str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
4204
select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';
4205
str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'
4207
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';
4208
str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''
4211
Warning 1292 Truncated incorrect datetime value: ''
4212
select str_to_date('1','%Y-%m-%d') = '1';
4213
str_to_date('1','%Y-%m-%d') = '1'
4216
Warning 1292 Truncated incorrect date value: '1'
4217
select str_to_date('1','%Y-%m-%d') = '1';
4218
str_to_date('1','%Y-%m-%d') = '1'
4221
Warning 1292 Truncated incorrect date value: '1'
4222
select str_to_date('','%Y-%m-%d') = '';
4223
str_to_date('','%Y-%m-%d') = ''
4226
Warning 1292 Truncated incorrect date value: ''
4227
select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
4228
str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL
4230
select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
4231
str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'
4233
select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
4234
str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL
4236
CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
4237
CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,
4238
c22 INT DEFAULT NULL,
4240
CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0,
4241
c32 INT DEFAULT NULL,
4243
c34 INT UNSIGNED DEFAULT 0,
4244
KEY (c33, c34, c32));
4245
INSERT INTO t1 values (),(),(),(),();
4246
INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;
4247
INSERT INTO t3 VALUES (1, 1, 1, 0),
4252
SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
4253
t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
4254
t3.c33 = 1 AND t2.c22 in (1, 3)
4263
SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
4264
t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
4265
t3.c33 = 1 AND t2.c22 in (1, 3)
4274
DROP TABLE t1, t2, t3;
4277
# Bug#30736: Row Size Too Large Error Creating a Table and
4280
DROP TABLE IF EXISTS t1;
4281
DROP TABLE IF EXISTS t2;
4287
INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5);
4310
CREATE TABLE t1 (c1 BIGINT NOT NULL);
4311
INSERT INTO t1 (c1) VALUES (1);
4312
SELECT * FROM t1 WHERE c1 > NULL + 1;
4316
CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY);
4317
INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0');
4318
SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar');
4322
CREATE TABLE t1 (a INT, b INT);
4323
CREATE TABLE t2 (a INT, c INT, KEY(a));
4324
INSERT INTO t1 VALUES (1, 1), (2, 2);
4325
INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
4326
(2, 1), (2, 2), (2, 3), (2, 4), (2, 5),
4327
(3, 1), (3, 2), (3, 3), (3, 4), (3, 5),
4328
(4, 1), (4, 2), (4, 3), (4, 4), (4, 5);
4330
SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;
4334
SHOW STATUS LIKE 'Handler_read%';
4336
Handler_read_first 0
4341
Handler_read_rnd_next 6
4343
CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0',
4344
f2 int(11) NOT NULL default '0',
4345
f3 bigint(20) NOT NULL default '0',
4346
f4 varchar(255) NOT NULL default '',
4350
CREATE TABLE t2 (f1 int(11) NOT NULL default '0',
4351
f2 enum('A1','A2','A3') NOT NULL default 'A1',
4352
f3 int(11) NOT NULL default '0',
4355
CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0',
4356
f2 datetime NOT NULL default '1980-01-01 00:00:00',
4358
insert into t1 values (1, 1, 1, 'abc');
4359
insert into t1 values (2, 1, 2, 'def');
4360
insert into t1 values (3, 1, 2, 'def');
4361
insert into t2 values (1, 'A1', 1);
4362
insert into t3 values (1, '1980-01-01');
4363
SELECT a.f3, cr.f4, count(*) count
4365
STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1
4368
JOIN t3 ae2 ON cr2.f3 = ae2.f1
4369
) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND
4371
GROUP BY a.f3, cr.f4;
4375
drop table t1, t2, t3;
4376
CREATE TABLE t1 (a INT KEY, b INT);
4377
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
4378
EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2;
4379
id select_type table type possible_keys key key_len ref rows filtered Extra
4380
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
4382
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2
4383
EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
4384
id select_type table type possible_keys key key_len ref rows filtered Extra
4385
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
4387
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
4390
# Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when
4391
# forcing a spatial index
4393
CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a));
4394
INSERT INTO t1 VALUES
4395
(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')),
4396
(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'));
4397
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
4398
id select_type table type possible_keys key key_len ref rows Extra
4399
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
4400
1 SIMPLE t2 ALL a NULL NULL NULL 2
4401
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
4407
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
4408
id select_type table type possible_keys key key_len ref rows Extra
4409
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
4410
1 SIMPLE t2 ALL a NULL NULL NULL 2
4411
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
4419
# Bug #48291 : crash with row() operator,select into @var, and
4420
# subquery returning multiple rows
4422
CREATE TABLE t1(a INT);
4423
INSERT INTO t1 VALUES (2),(3);
4425
SELECT 1 FROM t1 WHERE a <> 1 AND NOT
4426
ROW(a,a) <=> ROW((SELECT 1 FROM t1 WHERE 1=2),(SELECT 1 FROM t1))
4428
ERROR 21000: Subquery returns more than 1 row
4431
# Bug #48458: simple query tries to allocate enormous amount of
4434
CREATE TABLE t1(a INT NOT NULL, b YEAR);
4435
INSERT INTO t1 VALUES ();
4437
Warning 1364 Field 'a' doesn't have a default value
4438
CREATE TABLE t2(c INT);
4439
# Should not err out because of out-of-memory
4440
SELECT 1 FROM t2 JOIN t1 ON 1=1
4441
WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a);
4445
# Bug #49199: Optimizer handles incorrectly:
4446
# field='const1' AND field='const2' in some cases
4448
CREATE TABLE t1(a DATETIME NOT NULL);
4449
INSERT INTO t1 VALUES('2001-01-01');
4450
SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4453
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4454
id select_type table type possible_keys key key_len ref rows filtered Extra
4455
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
4457
Note 1003 select '2001-01-01 00:00:00' AS `a` from `test`.`t1` where 1
4459
CREATE TABLE t1(a DATE NOT NULL);
4460
INSERT INTO t1 VALUES('2001-01-01');
4461
SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4464
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4465
id select_type table type possible_keys key key_len ref rows filtered Extra
4466
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
4468
Note 1003 select '2001-01-01' AS `a` from `test`.`t1` where 1
4470
CREATE TABLE t1(a TIMESTAMP NOT NULL);
4471
INSERT INTO t1 VALUES('2001-01-01');
4472
SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4475
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4476
id select_type table type possible_keys key key_len ref rows filtered Extra
4477
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
4479
Note 1003 select '2001-01-01 00:00:00' AS `a` from `test`.`t1` where 1
4481
CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
4482
INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
4483
SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
4485
2001-01-01 00:00:00 2001-01-01
4486
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
4487
id select_type table type possible_keys key key_len ref rows filtered Extra
4488
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
4490
Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from `test`.`t1` where 1
4492
CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL);
4493
INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
4494
SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
4496
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
4497
id select_type table type possible_keys key key_len ref rows filtered Extra
4498
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
4500
Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from `test`.`t1` where 0
4501
SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
4503
2001-01-01 00:00:00 2001-01-01
4504
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
4505
id select_type table type possible_keys key key_len ref rows filtered Extra
4506
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
4508
Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from `test`.`t1` where 1
4510
CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
4511
INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
4512
SELECT x.a, y.a, z.a FROM t1 x
4513
JOIN t1 y ON x.a=y.a
4514
JOIN t1 z ON y.a=z.a
4515
WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
4517
2001-01-01 00:00:00 2001-01-01 00:00:00 2001-01-01 00:00:00
4518
EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x
4519
JOIN t1 y ON x.a=y.a
4520
JOIN t1 z ON y.a=z.a
4521
WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
4522
id select_type table type possible_keys key key_len ref rows filtered Extra
4523
1 SIMPLE x system NULL NULL NULL NULL 1 100.00
4524
1 SIMPLE y system NULL NULL NULL NULL 1 100.00
4525
1 SIMPLE z system NULL NULL NULL NULL 1 100.00
4527
Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a` from `test`.`t1` `x` join `test`.`t1` `y` join `test`.`t1` `z` where 1
4530
# Bug #49897: crash in ptr_compare when char(0) NOT NULL
4531
# column is used for ORDER BY
4533
SET @old_sort_buffer_size= @@session.sort_buffer_size;
4534
SET @@sort_buffer_size= 40000;
4535
CREATE TABLE t1(a CHAR(0) NOT NULL);
4536
INSERT INTO t1 VALUES (0), (0), (0);
4537
INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12;
4538
INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12;
4539
INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12;
4540
EXPLAIN SELECT a FROM t1 ORDER BY a;
4541
id select_type table type possible_keys key key_len ref rows Extra
4542
1 SIMPLE t1 ALL NULL NULL NULL NULL 24492
4543
SELECT a FROM t1 ORDER BY a;
4545
CREATE TABLE t1(a CHAR(0) NOT NULL, b CHAR(0) NOT NULL, c int);
4546
INSERT INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1);
4547
INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12;
4548
INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12;
4549
INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12;
4550
EXPLAIN SELECT a FROM t1 ORDER BY a LIMIT 5;
4551
id select_type table type possible_keys key key_len ref rows Extra
4552
1 SIMPLE t1 ALL NULL NULL NULL NULL 24492
4553
SELECT a FROM t1 ORDER BY a LIMIT 5;
4560
EXPLAIN SELECT * FROM t1 ORDER BY a, b LIMIT 5;
4561
id select_type table type possible_keys key key_len ref rows Extra
4562
1 SIMPLE t1 ALL NULL NULL NULL NULL 24492
4563
SELECT * FROM t1 ORDER BY a, b LIMIT 5;
4570
EXPLAIN SELECT * FROM t1 ORDER BY a, b, c LIMIT 5;
4571
id select_type table type possible_keys key key_len ref rows Extra
4572
1 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort
4573
SELECT * FROM t1 ORDER BY a, b, c LIMIT 5;
4580
EXPLAIN SELECT * FROM t1 ORDER BY c, a LIMIT 5;
4581
id select_type table type possible_keys key key_len ref rows Extra
4582
1 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort
4583
SELECT * FROM t1 ORDER BY c, a LIMIT 5;
4590
SET @@sort_buffer_size= @old_sort_buffer_size;
4593
create table t1(a INT, KEY (a));
4594
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
4595
SELECT a FROM t1 ORDER BY a LIMIT 2;
4599
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;
4604
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;
4610
CREATE TABLE A (date_key date);
4615
date_key date NOT NULL,
4617
varchar_key varchar(1)
4619
INSERT INTO C VALUES
4620
(1,1,1,'0000-00-00',NULL,NULL),
4621
(1,1,1,'0000-00-00',NULL,NULL);
4622
SELECT 1 FROM C WHERE pk > ANY (SELECT 1 FROM C);
4624
SELECT COUNT(DISTINCT 1) FROM C
4625
WHERE date_key = (SELECT 1 FROM A WHERE C.date_key IS NULL) GROUP BY pk;
4627
SELECT date_nokey FROM C
4628
WHERE int_key IN (SELECT 1 FROM A)
4629
HAVING date_nokey = '10:41:7'
4633
Warning 1292 Incorrect date value: '10:41:7' for column 'date_nokey' at row 1
4635
CREATE TABLE t1 (a INT NOT NULL, b INT);
4636
INSERT INTO t1 VALUES (1, 1);
4637
EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
4638
id select_type table type possible_keys key key_len ref rows filtered Extra
4639
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
4641
Note 1003 select '1' AS `a`,'1' AS `b` from `test`.`t1` where 1
4642
SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
4646
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL);
4647
EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20;
4648
id select_type table type possible_keys key key_len ref rows filtered Extra
4649
1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found
4651
Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1
4652
EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20;
4653
id select_type table type possible_keys key key_len ref rows filtered Extra
4654
1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found
4656
Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1
4657
EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20;
4658
id select_type table type possible_keys key key_len ref rows filtered Extra
4659
1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found
4661
Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1
4664
# Bug#45266: Uninitialized variable lead to an empty result.
4666
drop table if exists A,AA,B,BB;
4668
`pk` int(11) NOT NULL AUTO_INCREMENT,
4669
`date_key` date NOT NULL,
4670
`date_nokey` date NOT NULL,
4671
`datetime_key` datetime NOT NULL,
4672
`int_nokey` int(11) NOT NULL,
4673
`time_key` time NOT NULL,
4674
`time_nokey` time NOT NULL,
4676
KEY `date_key` (`date_key`),
4677
KEY `time_key` (`time_key`),
4678
KEY `datetime_key` (`datetime_key`)
4681
`pk` int(11) NOT NULL AUTO_INCREMENT,
4682
`int_nokey` int(11) NOT NULL,
4683
`time_key` time NOT NULL,
4684
KEY `time_key` (`time_key`),
4688
`date_nokey` date NOT NULL,
4689
`date_key` date NOT NULL,
4690
`time_key` time NOT NULL,
4691
`datetime_nokey` datetime NOT NULL,
4692
`varchar_key` varchar(1) NOT NULL,
4693
KEY `date_key` (`date_key`),
4694
KEY `time_key` (`time_key`),
4695
KEY `varchar_key` (`varchar_key`)
4697
INSERT INTO `B` VALUES ('2003-07-28','2003-07-28','15:13:38','0000-00-00 00:00:00','f'),('0000-00-00','0000-00-00','00:05:48','2004-07-02 14:34:13','x');
4699
`pk` int(11) NOT NULL AUTO_INCREMENT,
4700
`int_nokey` int(11) NOT NULL,
4701
`date_key` date NOT NULL,
4702
`varchar_nokey` varchar(1) NOT NULL,
4703
`date_nokey` date NOT NULL,
4705
KEY `date_key` (`date_key`)
4707
INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18');
4708
SELECT table1 . `pk` AS field1
4710
(BB AS table1 INNER JOIN
4711
(AA AS table2 STRAIGHT_JOIN A AS table3
4712
ON ( table3 . `date_key` = table2 . `pk` ))
4713
ON ( table3 . `datetime_key` = table2 . `int_nokey` ))
4714
WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`)
4717
SELECT table3 .`date_key` field1
4719
B table1 LEFT JOIN B table3 JOIN
4720
(BB table6 JOIN A table7 ON table6 .`varchar_nokey`)
4721
ON table6 .`int_nokey` ON table6 .`date_key`
4722
WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1;
4725
SELECT table4 . `time_nokey` AS field1 FROM
4726
(AA AS table1 CROSS JOIN
4727
(AA AS table2 STRAIGHT_JOIN
4728
(B AS table3 STRAIGHT_JOIN A AS table4
4729
ON ( table4 . `date_key` = table3 . `time_key` ))
4730
ON ( table4 . `pk` = table3 . `date_nokey` ))
4731
ON ( table4 . `time_key` = table3 . `datetime_nokey` ))
4732
WHERE ( table4 . `time_key` < table1 . `time_key` AND
4733
table1 . `int_nokey` != 'f')
4734
GROUP BY field1 ORDER BY field1 , field1;
4736
SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2;
4740
drop table A,AA,B,BB;
4741
#end of test for bug#45266
4743
# BUG#48052: Valgrind warning - uninitialized value in init_read_record()
4746
pk int(11) NOT NULL,
4747
i int(11) DEFAULT NULL,
4748
v varchar(1) DEFAULT NULL,
4751
INSERT INTO t1 VALUES (2,7,'m');
4752
INSERT INTO t1 VALUES (3,9,'m');
4761
# Bug#49489 Uninitialized cache led to a wrong result.
4763
CREATE TABLE t1(c1 DOUBLE(5,4));
4764
INSERT INTO t1 VALUES (9.1234);
4765
SELECT * FROM t1 WHERE c1 < 9.12345;
4769
# End of test for bug#49489.
4771
# Bug #49517: Inconsistent behavior while using
4772
# NULLable BIGINT and INT columns in comparison
4774
CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL);
4775
INSERT INTO t1 VALUES(105, NULL, NULL);
4776
SELECT * FROM t1 WHERE b < 102;
4778
SELECT * FROM t1 WHERE c < 102;
4780
SELECT * FROM t1 WHERE 102 < b;
4782
SELECT * FROM t1 WHERE 102 < c;
4786
# Bug #54459: Assertion failed: param.sort_length,
4787
# file .\filesort.cc, line 149 (part II)
4789
CREATE TABLE t1(a ENUM('') NOT NULL);
4790
INSERT INTO t1 VALUES (), (), ();
4791
EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci;
4792
id select_type table type possible_keys key key_len ref rows Extra
4793
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
4794
SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci;
added
removed
mysql-test/r/select.result
