~drizzle-trunk/drizzle/development

select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price;

1173

1221

fld3 period price price2

1174

1222

admonishing 1002 28357832 8723648

1232

1280

Wotan 1002 28357832 8723648

1233

1281

select t2.fld1,fld3,period,price,price2 from t2,t3 where t2.fld1>= 18201 and t2.fld1 <= 18811 and t2.fld1=t3.t2nr and period = 1001 and t2.companynr = 37;

1234

1282

fld1 fld3 period price price2

1235

18201 relaxing 1001 5987435 234724

1236

18601 vacuuming 1001 5987435 234724

1237

18801 inch 1001 5987435 234724

1238

18811 repetitions 1001 5987435 234724

1283

018201 relaxing 1001 5987435 234724

1284

018601 vacuuming 1001 5987435 234724

1285

018801 inch 1001 5987435 234724

1286

018811 repetitions 1001 5987435 234724

1239

1287

create table t4 (

1240

companynr int NOT NULL default '0',

1288

companynr tinyint(2) unsigned zerofill NOT NULL default '00',

1241

1289

companyname char(30) NOT NULL default '',

1242

1290

PRIMARY KEY (companynr),

1243

1291

UNIQUE KEY companyname(companyname)

1244

1292

) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';

1245

1293

select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;

1246

1294

companynr companyname

1247

0 Unknown

1295

00 Unknown

1248

1296

29 company 1

1249

1297

34 company 2

1250

1298

36 company 3

1258

1306

68 company 10

1259

1307

select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;

1260

1308

companynr companyname

1261

0 Unknown

1309

00 Unknown

1262

1310

29 company 1

1263

1311

34 company 2

1264

1312

36 company 3

1307

1355

select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;

1308

1356

count(*)

1309

1357

1199

1358

explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;

1359

id select_type table type possible_keys key key_len ref rows Extra

1360

1 SIMPLE t2 ALL NULL NULL NULL NULL 1200

1361

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists

1362

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;

1363

id select_type table type possible_keys key key_len ref rows Extra

1364

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1365

1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists

1310

1366

select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;

1311

1367

companynr companyname

1312

1368

select count(*) from t2 left join t4 using (companynr) where companynr is not null;

1313

1369

count(*)

1314

1370

1200

1371

explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;

1372

id select_type table type possible_keys key key_len ref rows Extra

1373

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1374

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;

1375

id select_type table type possible_keys key key_len ref rows Extra

1376

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1315

1377

delete from t2 where fld1=999999;

1378

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;

1379

id select_type table type possible_keys key key_len ref rows Extra

1380

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1381

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1

1382

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;

1383

id select_type table type possible_keys key key_len ref rows Extra

1384

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1385

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1

1386

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;

1387

id select_type table type possible_keys key key_len ref rows Extra

1388

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1389

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1

1390

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;

1391

id select_type table type possible_keys key key_len ref rows Extra

1392

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1393

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1394

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;

1395

id select_type table type possible_keys key key_len ref rows Extra

1396

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1397

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1398

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;

1399

id select_type table type possible_keys key key_len ref rows Extra

1400

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1401

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1402

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;

1403

id select_type table type possible_keys key key_len ref rows Extra

1404

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1405

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1406

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;

1407

id select_type table type possible_keys key key_len ref rows Extra

1408

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12

1409

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1410

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;

1411

id select_type table type possible_keys key key_len ref rows Extra

1412

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1413

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1414

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;

1415

id select_type table type possible_keys key key_len ref rows Extra

1416

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1417

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1418

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;

1419

id select_type table type possible_keys key key_len ref rows Extra

1420

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

1421

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1422

explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;

1423

id select_type table type possible_keys key key_len ref rows Extra

1424

1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where

1425

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1316

1426

select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;

1317

1427

companynr companynr

1318

1428

37 36

1319

1429

41 40

1430

explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;

1431

id select_type table type possible_keys key key_len ref rows Extra

1432

1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary

1433

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer

1320

1434

select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;

1321

1435

fld1 companynr fld3 period

1322

38008 37 reporters 1008

1323

38208 37 Selfridge 1008

1436

038008 37 reporters 1008

1437

038208 37 Selfridge 1008

1324

1438

select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;

1325

1439

fld1 companynr fld3 period

1326

38008 37 reporters 1008

1327

38208 37 Selfridge 1008

1440

038008 37 reporters 1008

1441

038208 37 Selfridge 1008

1328

1442

select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;

1329

1443

fld1 companynr fld3 period

1330

38008 37 reporters 1008

1331

38208 37 Selfridge 1008

1444

038008 37 reporters 1008

1445

038208 37 Selfridge 1008

1332

1446

select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909);

1333

1447

period

1334

1448

9410

1366

1480

1367

1481

select companynr,count(*),sum(fld1) from t2 group by companynr;

1368

1482

companynr count(*) sum(fld1)

1369

0 82 10355753

1483

00 82 10355753

1370

1484

29 95 14473298

1371

1485

34 70 17788966

1372

1486

36 215 22786296

1388

1502

select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";

1389

1503

count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)

1390

1504

70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069

1505

explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";

1506

id select_type table type possible_keys key key_len ref rows filtered Extra

1507

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where

1508

Warnings:

1509

Note 1003 select count(0) AS "count(*)",min("test"."t2"."fld4") AS "min(fld4)",max("test"."t2"."fld4") AS "max(fld4)",sum("test"."t2"."fld1") AS "sum(fld1)",avg("test"."t2"."fld1") AS "avg(fld1)",std("test"."t2"."fld1") AS "std(fld1)",variance("test"."t2"."fld1") AS "variance(fld1)" from "test"."t2" where (("test"."t2"."companynr" = 34) and ("test"."t2"."fld4" <> ''))

1391

1510

select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;

1392

1511

companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)

1393

0 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087

1512

00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087

1394

1513

29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026

1395

1514

34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069

1396

1515

select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;

1445

1564

1199

1446

1565

select companynr,count(distinct fld1) from t2 group by companynr;

1447

1566

companynr count(distinct fld1)

1448

0 82

1567

00 82

1449

1568

29 95

1450

1569

34 70

1451

1570

36 215

1459

1578

68 12

1460

1579

select companynr,count(*) from t2 group by companynr;

1461

1580

companynr count(*)

1462

0 82

1581

00 82

1463

1582

29 95

1464

1583

34 70

1465

1584

36 215

1473

1592

68 12

1474

1593

select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;

1475

1594

companynr count(distinct concat(fld1,repeat(65,1000)))

1476

0 82

1595

00 82

1477

1596

29 95

1478

1597

34 70

1479

1598

36 215

1487

1606

68 12

1488

1607

select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;

1489

1608

companynr count(distinct concat(fld1,repeat(65,200)))

1490

0 82

1609

00 82

1491

1610

29 95

1492

1611

34 70

1493

1612

36 215

1501

1620

68 12

1502

1621

select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;

1503

1622

companynr count(distinct floor(fld1/100))

1504

0 47

1623

00 47

1505

1624

29 35

1506

1625

34 14

1507

1626

36 69

1515

1634

68 1

1516

1635

select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;

1517

1636

companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))

1518

0 47

1637

00 47

1519

1638

29 35

1520

1639

34 14

1521

1640

36 69

1554

1673

select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;

1555

1674

fld3 count(*)

1556

1675

spates 4181

1557

select companynr,companyname from t4 group by 1;

1558

companynr companyname

1676

select companynr|0,companyname from t4 group by 1;

1677

companynr|0 companyname

1559

1678

0 Unknown

1560

1679

29 company 1

1561

1680

34 company 2

1581

1700

53 company 7 4

1582

1701

58 company 8 23

1583

1702

65 company 9 10

1584

0 Unknown 82

1703

00 Unknown 82

1585

1704

select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;

1586

1705

fld1 count(*)

1587

1706

158402 4181

1605

1724

37 588

1606

1725

36 215

1607

1726

29 95

1608

0 82

1727

00 82

1609

1728

34 70

1610

1729

41 52

1611

1730

40 37

1624

1743

53 4

1625

1744

select t2.fld4,t2.fld1,count(price),sum(price),min(price),max(price),avg(price) from t3,t2 where t3.companynr = 37 and t2.fld1 = t3.t2nr group by fld1,t2.fld4;

1626

1745

fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)

1627

teethe 1 1 5987435 5987435 5987435 5987435.0000

1628

dreaded 11401 1 5987435 5987435 5987435 5987435.0000

1629

scholastics 11402 1 28357832 28357832 28357832 28357832.0000

1630

audiology 11403 1 39654943 39654943 39654943 39654943.0000

1631

wallet 11501 1 5987435 5987435 5987435 5987435.0000

1632

parters 11701 1 5987435 5987435 5987435 5987435.0000

1633

eschew 11702 1 28357832 28357832 28357832 28357832.0000

1634

quitter 11703 1 39654943 39654943 39654943 39654943.0000

1635

neat 12001 1 5987435 5987435 5987435 5987435.0000

1636

Steinberg 12003 1 39654943 39654943 39654943 39654943.0000

1637

balled 12301 1 5987435 5987435 5987435 5987435.0000

1638

persist 12302 1 28357832 28357832 28357832 28357832.0000

1639

attainments 12303 1 39654943 39654943 39654943 39654943.0000

1640

capably 12501 1 5987435 5987435 5987435 5987435.0000

1641

impulsive 12602 1 28357832 28357832 28357832 28357832.0000

1642

starlet 12603 1 39654943 39654943 39654943 39654943.0000

1643

featherweight 12701 1 5987435 5987435 5987435 5987435.0000

1644

pessimist 12702 1 28357832 28357832 28357832 28357832.0000

1645

daughter 12703 1 39654943 39654943 39654943 39654943.0000

1646

lawgiver 13601 1 5987435 5987435 5987435 5987435.0000

1647

stated 13602 1 28357832 28357832 28357832 28357832.0000

1648

readable 13603 1 39654943 39654943 39654943 39654943.0000

1649

testicle 13801 1 5987435 5987435 5987435 5987435.0000

1650

Parsifal 13802 1 28357832 28357832 28357832 28357832.0000

1651

leavings 13803 1 39654943 39654943 39654943 39654943.0000

1652

squeaking 13901 1 5987435 5987435 5987435 5987435.0000

1653

contrasted 16001 1 5987435 5987435 5987435 5987435.0000

1654

leftover 16201 1 5987435 5987435 5987435 5987435.0000

1655

whiteners 16202 1 28357832 28357832 28357832 28357832.0000

1656

erases 16301 1 5987435 5987435 5987435 5987435.0000

1657

Punjab 16302 1 28357832 28357832 28357832 28357832.0000

1658

Merritt 16303 1 39654943 39654943 39654943 39654943.0000

1659

sweetish 18001 1 5987435 5987435 5987435 5987435.0000

1660

dogging 18002 1 28357832 28357832 28357832 28357832.0000

1661

scornfully 18003 1 39654943 39654943 39654943 39654943.0000

1662

fetters 18012 1 28357832 28357832 28357832 28357832.0000

1663

bivalves 18013 1 39654943 39654943 39654943 39654943.0000

1664

skulking 18021 1 5987435 5987435 5987435 5987435.0000

1665

flint 18022 1 28357832 28357832 28357832 28357832.0000

1666

flopping 18023 1 39654943 39654943 39654943 39654943.0000

1667

Judas 18032 1 28357832 28357832 28357832 28357832.0000

1668

vacuuming 18033 1 39654943 39654943 39654943 39654943.0000

1669

medical 18041 1 5987435 5987435 5987435 5987435.0000

1670

bloodbath 18042 1 28357832 28357832 28357832 28357832.0000

1671

subschema 18043 1 39654943 39654943 39654943 39654943.0000

1672

interdependent 18051 1 5987435 5987435 5987435 5987435.0000

1673

Graves 18052 1 28357832 28357832 28357832 28357832.0000

1674

neonatal 18053 1 39654943 39654943 39654943 39654943.0000

1675

sorters 18061 1 5987435 5987435 5987435 5987435.0000

1676

epistle 18062 1 28357832 28357832 28357832 28357832.0000

1677

Conley 18101 1 5987435 5987435 5987435 5987435.0000

1678

lectured 18102 1 28357832 28357832 28357832 28357832.0000

1679

Abraham 18103 1 39654943 39654943 39654943 39654943.0000

1680

cage 18201 1 5987435 5987435 5987435 5987435.0000

1681

hushes 18202 1 28357832 28357832 28357832 28357832.0000

1682

Simla 18402 1 28357832 28357832 28357832 28357832.0000

1683

reporters 18403 1 39654943 39654943 39654943 39654943.0000

1684

coexist 18601 1 5987435 5987435 5987435 5987435.0000

1685

Beebe 18602 1 28357832 28357832 28357832 28357832.0000

1686

Taoism 18603 1 39654943 39654943 39654943 39654943.0000

1687

Connally 18801 1 5987435 5987435 5987435 5987435.0000

1688

fetched 18802 1 28357832 28357832 28357832 28357832.0000

1689

checkpoints 18803 1 39654943 39654943 39654943 39654943.0000

1690

gritty 18811 1 5987435 5987435 5987435 5987435.0000

1691

firearm 18812 1 28357832 28357832 28357832 28357832.0000

1692

minima 19101 1 5987435 5987435 5987435 5987435.0000

1693

Selfridge 19102 1 28357832 28357832 28357832 28357832.0000

1694

disable 19103 1 39654943 39654943 39654943 39654943.0000

1695

witchcraft 19201 1 5987435 5987435 5987435 5987435.0000

1696

betroth 30501 1 5987435 5987435 5987435 5987435.0000

1697

Manhattanize 30502 1 28357832 28357832 28357832 28357832.0000

1698

imprint 30503 1 39654943 39654943 39654943 39654943.0000

1699

swelling 31901 1 5987435 5987435 5987435 5987435.0000

1700

interrelationships 36001 1 5987435 5987435 5987435 5987435.0000

1701

riser 36002 1 28357832 28357832 28357832 28357832.0000

1702

bee 38001 1 5987435 5987435 5987435 5987435.0000

1703

kanji 38002 1 28357832 28357832 28357832 28357832.0000

1704

dental 38003 1 39654943 39654943 39654943 39654943.0000

1705

railway 38011 1 5987435 5987435 5987435 5987435.0000

1706

validate 38012 1 28357832 28357832 28357832 28357832.0000

1707

normalizes 38013 1 39654943 39654943 39654943 39654943.0000

1708

Kline 38101 1 5987435 5987435 5987435 5987435.0000

1709

Anatole 38102 1 28357832 28357832 28357832 28357832.0000

1710

partridges 38103 1 39654943 39654943 39654943 39654943.0000

1711

recruited 38201 1 5987435 5987435 5987435 5987435.0000

1712

dimensions 38202 1 28357832 28357832 28357832 28357832.0000

1713

Chicana 38203 1 39654943 39654943 39654943 39654943.0000

1746

teethe 000001 1 5987435 5987435 5987435 5987435.0000

1747

dreaded 011401 1 5987435 5987435 5987435 5987435.0000

1748

scholastics 011402 1 28357832 28357832 28357832 28357832.0000

1749

audiology 011403 1 39654943 39654943 39654943 39654943.0000

1750

wallet 011501 1 5987435 5987435 5987435 5987435.0000

1751

parters 011701 1 5987435 5987435 5987435 5987435.0000

1752

eschew 011702 1 28357832 28357832 28357832 28357832.0000

1753

quitter 011703 1 39654943 39654943 39654943 39654943.0000

1754

neat 012001 1 5987435 5987435 5987435 5987435.0000

1755

Steinberg 012003 1 39654943 39654943 39654943 39654943.0000

1756

balled 012301 1 5987435 5987435 5987435 5987435.0000

1757

persist 012302 1 28357832 28357832 28357832 28357832.0000

1758

attainments 012303 1 39654943 39654943 39654943 39654943.0000

1759

capably 012501 1 5987435 5987435 5987435 5987435.0000

1760

impulsive 012602 1 28357832 28357832 28357832 28357832.0000

1761

starlet 012603 1 39654943 39654943 39654943 39654943.0000

1762

featherweight 012701 1 5987435 5987435 5987435 5987435.0000

1763

pessimist 012702 1 28357832 28357832 28357832 28357832.0000

1764

daughter 012703 1 39654943 39654943 39654943 39654943.0000

1765

lawgiver 013601 1 5987435 5987435 5987435 5987435.0000

1766

stated 013602 1 28357832 28357832 28357832 28357832.0000

1767

readable 013603 1 39654943 39654943 39654943 39654943.0000

1768

testicle 013801 1 5987435 5987435 5987435 5987435.0000

1769

Parsifal 013802 1 28357832 28357832 28357832 28357832.0000

1770

leavings 013803 1 39654943 39654943 39654943 39654943.0000

1771

squeaking 013901 1 5987435 5987435 5987435 5987435.0000

1772

contrasted 016001 1 5987435 5987435 5987435 5987435.0000

1773

leftover 016201 1 5987435 5987435 5987435 5987435.0000

1774

whiteners 016202 1 28357832 28357832 28357832 28357832.0000

1775

erases 016301 1 5987435 5987435 5987435 5987435.0000

1776

Punjab 016302 1 28357832 28357832 28357832 28357832.0000

1777

Merritt 016303 1 39654943 39654943 39654943 39654943.0000

1778

sweetish 018001 1 5987435 5987435 5987435 5987435.0000

1779

dogging 018002 1 28357832 28357832 28357832 28357832.0000

1780

scornfully 018003 1 39654943 39654943 39654943 39654943.0000

1781

fetters 018012 1 28357832 28357832 28357832 28357832.0000

1782

bivalves 018013 1 39654943 39654943 39654943 39654943.0000

1783

skulking 018021 1 5987435 5987435 5987435 5987435.0000

1784

flint 018022 1 28357832 28357832 28357832 28357832.0000

1785

flopping 018023 1 39654943 39654943 39654943 39654943.0000

1786

Judas 018032 1 28357832 28357832 28357832 28357832.0000

1787

vacuuming 018033 1 39654943 39654943 39654943 39654943.0000

1788

medical 018041 1 5987435 5987435 5987435 5987435.0000

1789

bloodbath 018042 1 28357832 28357832 28357832 28357832.0000

1790

subschema 018043 1 39654943 39654943 39654943 39654943.0000

1791

interdependent 018051 1 5987435 5987435 5987435 5987435.0000

1792

Graves 018052 1 28357832 28357832 28357832 28357832.0000

1793

neonatal 018053 1 39654943 39654943 39654943 39654943.0000

1794

sorters 018061 1 5987435 5987435 5987435 5987435.0000

1795

epistle 018062 1 28357832 28357832 28357832 28357832.0000

1796

Conley 018101 1 5987435 5987435 5987435 5987435.0000

1797

lectured 018102 1 28357832 28357832 28357832 28357832.0000

1798

Abraham 018103 1 39654943 39654943 39654943 39654943.0000

1799

cage 018201 1 5987435 5987435 5987435 5987435.0000

1800

hushes 018202 1 28357832 28357832 28357832 28357832.0000

1801

Simla 018402 1 28357832 28357832 28357832 28357832.0000

1802

reporters 018403 1 39654943 39654943 39654943 39654943.0000

1803

coexist 018601 1 5987435 5987435 5987435 5987435.0000

1804

Beebe 018602 1 28357832 28357832 28357832 28357832.0000

1805

Taoism 018603 1 39654943 39654943 39654943 39654943.0000

1806

Connally 018801 1 5987435 5987435 5987435 5987435.0000

1807

fetched 018802 1 28357832 28357832 28357832 28357832.0000

1808

checkpoints 018803 1 39654943 39654943 39654943 39654943.0000

1809

gritty 018811 1 5987435 5987435 5987435 5987435.0000

1810

firearm 018812 1 28357832 28357832 28357832 28357832.0000

1811

minima 019101 1 5987435 5987435 5987435 5987435.0000

1812

Selfridge 019102 1 28357832 28357832 28357832 28357832.0000

1813

disable 019103 1 39654943 39654943 39654943 39654943.0000

1814

witchcraft 019201 1 5987435 5987435 5987435 5987435.0000

1815

betroth 030501 1 5987435 5987435 5987435 5987435.0000

1816

Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000

1817

imprint 030503 1 39654943 39654943 39654943 39654943.0000

1818

swelling 031901 1 5987435 5987435 5987435 5987435.0000

1819

interrelationships 036001 1 5987435 5987435 5987435 5987435.0000

1820

riser 036002 1 28357832 28357832 28357832 28357832.0000

1821

bee 038001 1 5987435 5987435 5987435 5987435.0000

1822

kanji 038002 1 28357832 28357832 28357832 28357832.0000

1823

dental 038003 1 39654943 39654943 39654943 39654943.0000

1824

railway 038011 1 5987435 5987435 5987435 5987435.0000

1825

validate 038012 1 28357832 28357832 28357832 28357832.0000

1826

normalizes 038013 1 39654943 39654943 39654943 39654943.0000

1827

Kline 038101 1 5987435 5987435 5987435 5987435.0000

1828

Anatole 038102 1 28357832 28357832 28357832 28357832.0000

1829

partridges 038103 1 39654943 39654943 39654943 39654943.0000

1830

recruited 038201 1 5987435 5987435 5987435 5987435.0000

1831

dimensions 038202 1 28357832 28357832 28357832 28357832.0000

1832

Chicana 038203 1 39654943 39654943 39654943 39654943.0000

1714

1833

select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;

1715

1834

companynr fld3 sum(price)

1716

1835

512 boat 786542

1725

1844

512 skies 786542

1726

1845

select t2.companynr,count(*),min(fld3),max(fld3),sum(price),avg(price) from t2,t3 where t3.companynr >= 30 and t3.companynr <= 58 and t3.t2nr = t2.fld1 and 1+1=2 group by t2.companynr;

1727

1846

companynr count(*) min(fld3) max(fld3) sum(price) avg(price)

1728

0 1 Omaha Omaha 5987435 5987435.0000

1847

00 1 Omaha Omaha 5987435 5987435.0000

1729

1848

36 1 dubbed dubbed 28357832 28357832.0000

1730

1849

37 83 Abraham Wotan 1908978016 22999735.1325

1731

1850

50 2 scribbled tapestry 68012775 34006387.5000

1823

1942

234298

1824

1943

select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1;

1825

1944

fld1 sum(price)

1826

38008 234298

1945

038008 234298

1946

explain select fld3 from t2 where 1>2 or 2>3;

1947

id select_type table type possible_keys key key_len ref rows Extra

1948

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1949

explain select fld3 from t2 where fld1=fld1;

1950

id select_type table type possible_keys key key_len ref rows Extra

1951

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199

1827

1952

select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;

1828

1953

companynr fld1

1829

1954

34 250501

1834

1959

34 250502

1835

1960

select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;

1836

1961

companynr count sum

1837

0 82 10355753

1962

00 82 10355753

1838

1963

29 95 14473298

1839

1964

34 70 17788966

1840

1965

37 588 83602098

1841

1966

41 52 12816335

1842

1967

select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;

1843

1968

companynr

1844

1969

1845

1970

1846

1971

1847

1972

1873

1998

select count(*) from t3 where companynr=512 and price2=76234234;

1874

1999

count(*)

1875

2000

4181

2001

explain select min(fld1),max(fld1),count(*) from t2;

2002

id select_type table type possible_keys key key_len ref rows Extra

2003

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

1876

2004

select min(fld1),max(fld1),count(*) from t2;

1877

2005

min(fld1) max(fld1) count(*)

1878

2006

0 1232609 1199

1940

2068

Tables_in_test (t?)

1941

2069

show full columns from t2;

1942

2070

Field Type Collation Null Key Default Extra Privileges Comment

1943

auto int NULL NO PRI NULL auto_increment #

1944

fld1 int NULL NO UNI NULL #

1945

companynr int NULL NO NULL #

1946

fld3 varchar(30) utf8_general_ci NO MUL NULL #

1947

fld4 varchar(35) utf8_general_ci NO NULL #

1948

fld5 varchar(35) utf8_general_ci NO NULL #

1949

fld6 varchar(4) utf8_general_ci NO NULL #

2071

auto int(11) NULL NO PRI NULL auto_increment #

2072

fld1 int(6) unsigned zerofill NULL NO UNI NULL #

2073

companynr tinyint(2) unsigned zerofill NULL NO NULL #

2074

fld3 char(30) latin1_swedish_ci NO MUL NULL #

2075

fld4 char(35) latin1_swedish_ci NO NULL #

2076

fld5 char(35) latin1_swedish_ci NO NULL #

2077

fld6 char(4) latin1_swedish_ci NO NULL #

1950

2078

show full columns from t2 from test like 'f%';

1951

2079

Field Type Collation Null Key Default Extra Privileges Comment

1952

fld1 int NULL NO UNI NULL #

1953

fld3 varchar(30) utf8_general_ci NO MUL NULL #

1954

fld4 varchar(35) utf8_general_ci NO NULL #

1955

fld5 varchar(35) utf8_general_ci NO NULL #

1956

fld6 varchar(4) utf8_general_ci NO NULL #

2080

fld1 int(6) unsigned zerofill NULL NO UNI NULL #

2081

fld3 char(30) latin1_swedish_ci NO MUL NULL #

2082

fld4 char(35) latin1_swedish_ci NO NULL #

2083

fld5 char(35) latin1_swedish_ci NO NULL #

2084

fld6 char(4) latin1_swedish_ci NO NULL #

1957

2085

show full columns from t2 from test like 's%';

1958

2086

Field Type Collation Null Key Default Extra Privileges Comment

2087

show keys from t2;

2088

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

2089

t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE

2090

t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE

2091

t2 1 fld3 1 fld3 A NULL NULL NULL BTREE

1959

2092

drop table t4, t3, t2, t1;

1960

2093

CREATE TABLE t1 (

1961

id bigint NOT NULL auto_increment,

2094

id bigint(8) unsigned NOT NULL auto_increment,

1962

2095

pseudo varchar(35) NOT NULL default '',

1963

2096

PRIMARY KEY (id),

1964

2097

UNIQUE KEY pseudo (pseudo)

1968

2101

SELECT 1 as rnd1 from t1 where rand() > 2;

1969

2102

rnd1

1970

2103

DROP TABLE t1;

1971

CREATE TABLE t1 (gvid int default NULL, hmid int default NULL, volid int default NULL, mmid int default NULL, hdid int default NULL, fsid int default NULL, ctid int default NULL, dtid int default NULL, cost int default NULL, performance int default NULL, serialnumber bigint default NULL, monitored int default '1', removed int default '0', target int default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;

2104

CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;

1972

2105

INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);

1973

CREATE TABLE t2 ( hmid int default NULL, volid int default NULL, sampletid int default NULL, sampletime datetime default NULL, samplevalue bigint default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

2106

CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

1974

2107

INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);

1975

2108

SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;

1976

2109

gvid the_success the_fail the_size the_time

1980

2113

SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;

1981

2114

gvid the_success the_fail the_size the_time

1982

2115

DROP TABLE t1,t2;

1983

create table t1 ( A_Id bigint NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint NOT NULL default '0', A_UpdateSerial int NOT NULL default '0', other_types bigint NOT NULL default '0', wss_type bigint NOT NULL default '0');

2116

create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0');

1984

2117

INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);

1985

2118

select wss_type from t1 where wss_type ='102935229216544106';

1986

2119

wss_type

2056

2189

select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;

2057

2190

a a

2058

2191

1 2

2192

2 2

2193

3 2

2059

2194

1 3

2060

2 2

2061

2195

2 3

2062

3 2

2063

2196

3 3

2064

2197

select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;

2065

2198

a a

2164

2297

2165

2298

2166

2299

drop table t1;

2167

CREATE TABLE t1 ( aa char(2), id int NOT NULL auto_increment, t2_id int NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;

2300

CREATE TABLE t1 ( aa char(2), id int(11) NOT NULL auto_increment, t2_id int(11) NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;

2168

2301

INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);

2169

CREATE TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

2302

CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

2170

2303

INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);

2171

2304

select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5;

2172

2305

aa id t2_id id

2184

2317

insert into t1 values (2);

2185

2318

insert into t2 values (1);

2186

2319

insert into t4 values (1,1);

2320

explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3

2321

left join t4 on id3 = id4 where id2 = 1 or id4 = 1;

2322

id select_type table type possible_keys key key_len ref rows Extra

2323

1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found

2324

1 SIMPLE t4 const id4 NULL NULL NULL 1

2325

1 SIMPLE t1 ALL NULL NULL NULL NULL 2

2326

1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where

2187

2327

select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3

2188

2328

left join t4 on id3 = id4 where id2 = 1 or id4 = 1;

2189

2329

id1 id2 id3 id4 id44

2213

2353

create table t2 (c integer, d integer, index(c), index(d));

2214

2354

insert into t1 values (1,2), (2,2), (3,2), (4,2);

2215

2355

insert into t2 values (1,3), (2,3), (3,4), (4,4);

2356

explain select * from t1 left join t2 on a=c where d in (4);

2357

id select_type table type possible_keys key key_len ref rows Extra

2358

1 SIMPLE t2 ref c,d d 5 const 2 Using index condition

2359

1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer

2216

2360

select * from t1 left join t2 on a=c where d in (4);

2217

2361

a b c d

2218

2362

3 2 3 4

2219

2363

4 2 4 4

2364

explain select * from t1 left join t2 on a=c where d = 4;

2365

id select_type table type possible_keys key key_len ref rows Extra

2366

1 SIMPLE t2 ref c,d d 5 const 2 Using index condition

2367

1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer

2220

2368

select * from t1 left join t2 on a=c where d = 4;

2221

2369

a b c d

2222

2370

3 2 3 4

2223

2371

4 2 4 4

2224

2372

drop table t1, t2;

2225

2373

CREATE TABLE t1 (

2226

i int NOT NULL default '0',

2374

i int(11) NOT NULL default '0',

2227

2375

c char(10) NOT NULL default '',

2228

2376

PRIMARY KEY (i),

2229

2377

UNIQUE KEY c (c)

2242

2390

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;

2243

2391

id select_type table type possible_keys key key_len ref rows Extra

2244

2392

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

2245

1 SIMPLE t2 ref a a 23 test.t1.a 1

2393

1 SIMPLE t2 ref a a 23 test.t1.a 2

2246

2394

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;

2247

2395

id select_type table type possible_keys key key_len ref rows Extra

2248

2396

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

2249

1 SIMPLE t2 ref a a 23 test.t1.a 1

2397

1 SIMPLE t2 ref a a 23 test.t1.a 2

2250

2398

DROP TABLE t1, t2;

2251

2399

CREATE TABLE t1 ( city char(30) );

2252

2400

INSERT INTO t1 VALUES ('London');

2270

2418

city

2271

2419

London

2272

2420

DROP TABLE t1;

2273

create table t1 (a int, b int);

2421

create table t1 (a int(11) unsigned, b int(11) unsigned);

2274

2422

insert into t1 values (1,0), (1,1), (1,2);

2275

2423

select a-b from t1 order by 1;

2276

2424

a-b

2277

-1

2278

2425

2279

2426

2427

18446744073709551615

2280

2428

select a-b , (a-b < 0) from t1 order by 1;

2281

2429

a-b (a-b < 0)

2282

-1 1

2283

2430

0 0

2284

2431

1 0

2432

18446744073709551615 0

2285

2433

select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;

2286

2434

d (a-b >= 0) b

2287

2435

1 1 0

2288

2436

0 1 1

2289

select a - b from t1 order by 1;

2290

a - b

2291

-1

2437

18446744073709551615 1 2

2438

select cast((a - b) as unsigned) from t1 order by 1;

2439

cast((a - b) as unsigned)

2292

2440

2293

2441

2442

18446744073709551615

2294

2443

drop table t1;

2295

create table t1 (a int);

2444

create table t1 (a int(11));

2296

2445

select all all * from t1;

2297

2446

2298

2447

select distinct distinct * from t1;

2303

2452

ERROR HY000: Incorrect usage of ALL and DISTINCT

2304

2453

drop table t1;

2305

2454

CREATE TABLE t1 (

2306

kunde_intern_id int NOT NULL default '0',

2307

kunde_id int NOT NULL default '0',

2308

FK_firma_id int NOT NULL default '0',

2455

kunde_intern_id int(10) unsigned NOT NULL default '0',

2456

kunde_id int(10) unsigned NOT NULL default '0',

2457

FK_firma_id int(10) unsigned NOT NULL default '0',

2309

2458

aktuell enum('Ja','Nein') NOT NULL default 'Ja',

2310

2459

vorname varchar(128) NOT NULL default '',

2311

2460

nachname varchar(128) NOT NULL default '',

2356

2505

COUNT(*)

2357

2506

2358

2507

drop table t1;

2359

CREATE TABLE t1 (b BIGINT NOT NULL, PRIMARY KEY (b));

2360

INSERT INTO t1 VALUES (0x4000000000000000);

2361

SELECT b FROM t1 WHERE b=0x4000000000000000;

2508

CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b));

2509

INSERT INTO t1 VALUES (0x8000000000000000);

2510

SELECT b FROM t1 WHERE b=0x8000000000000000;

2362

2511

2363

4611686018427387904

2512

9223372036854775808

2364

2513

DROP TABLE t1;

2365

CREATE TABLE `t1` ( `gid` int default NULL, `uid` int default NULL);

2366

CREATE TABLE `t2` ( `ident` int default NULL, `level` char(16) default NULL);

2514

CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL);

2515

CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL);

2367

2516

INSERT INTO `t2` VALUES (0,'READ');

2368

CREATE TABLE `t3` ( `id` int default NULL, `name` char(16) default NULL);

2517

CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL);

2369

2518

INSERT INTO `t3` VALUES (1,'fs');

2370

2519

select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);

2371

2520

id name gid uid ident level

2372

2521

1 fs NULL NULL 0 READ

2373

2522

drop table t1,t2,t3;

2374

2523

CREATE TABLE t1 (

2375

acct_id int NOT NULL default '0',

2376

profile_id int default NULL,

2524

acct_id int(11) NOT NULL default '0',

2525

profile_id smallint(6) default NULL,

2377

2526

UNIQUE KEY t1$acct_id (acct_id),

2378

2527

KEY t1$profile_id (profile_id)

2379

2528

);

2380

2529

INSERT INTO t1 VALUES (132,17),(133,18);

2381

2530

CREATE TABLE t2 (

2382

profile_id int default NULL,

2383

queue_id int default NULL,

2384

seq int default NULL,

2531

profile_id smallint(6) default NULL,

2532

queue_id int(11) default NULL,

2533

seq int(11) default NULL,

2385

2534

KEY t2$queue_id (queue_id)

2386

2535

);

2387

2536

INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);

2388

2537

CREATE TABLE t3 (

2389

id int NOT NULL default '0',

2390

qtype int default NULL,

2391

seq int default NULL,

2392

warn_lvl int default NULL,

2393

crit_lvl int default NULL,

2394

rr1 int NOT NULL default '0',

2395

rr2 int default NULL,

2396

default_queue int NOT NULL default '0',

2538

id int(11) NOT NULL default '0',

2539

qtype int(11) default NULL,

2540

seq int(11) default NULL,

2541

warn_lvl int(11) default NULL,

2542

crit_lvl int(11) default NULL,

2543

rr1 tinyint(4) NOT NULL default '0',

2544

rr2 int(11) default NULL,

2545

default_queue tinyint(4) NOT NULL default '0',

2397

2546

KEY t3$qtype (qtype),

2398

2547

KEY t3$id (id)

2399

2548

);

2417

2566

2418

2567

NULL

2419

2568

drop table t1,t2;

2420

create table t2 (a int);

2569

create table t2 (a tinyint unsigned);

2421

2570

create index t2i on t2(a);

2422

2571

insert into t2 values (0), (254), (255);

2572

explain select * from t2 where a > -1;

2573

id select_type table type possible_keys key key_len ref rows Extra

2574

1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index

2423

2575

select * from t2 where a > -1;

2424

2576

2425

2577

2457

2609

insert into t1 values(1,""),(2,"");

2458

2610

show table status like 't1%';

2459

2611

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

2460

t1 InnoDB 10 Compact 2 8192 X X X X X X X X utf8_general_ci NULL

2461

t11 InnoDB 10 Compact 0 0 X X X X X X X X utf8_general_ci NULL

2612

t1 MyISAM 10 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL

2613

t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL

2462

2614

select 123 as a from t1 where f1 is null;

2463

2615

2464

2616

drop table t1,t11;

2518

2670

insert into t2 values (1,'',''), (2,'','');

2519

2671

CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));

2520

2672

insert into t3 values (1,1),(1,2);

2673

explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2

2674

where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and

2675

t2.b like '%%' order by t2.b limit 0,1;

2676

id select_type table type possible_keys key key_len ref rows Extra

2677

1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort

2678

1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer

2679

1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)

2521

2680

DROP TABLE t1,t2,t3;

2522

2681

CREATE TABLE t1 (a int, INDEX idx(a));

2523

2682

INSERT INTO t1 VALUES (2), (3), (1);

2534

2693

CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b));

2535

2694

INSERT INTO t2 VALUES (1,NULL), (2,10);

2536

2695

ALTER TABLE t1 ENABLE KEYS;

2537

Warnings:

2538

Note 1031 Table storage engine for 't1' doesn't have this option

2539

2696

EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;

2540

2697

id select_type table type possible_keys key key_len ref rows Extra

2541

1 SIMPLE t2 index b PRIMARY 4 NULL 2

2698

1 SIMPLE t2 index b b 5 NULL 2 Using index

2542

2699

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer

2543

2700

SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;

2544

2701

a b a b

2548

2705

2 10 4 10

2549

2706

EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;

2550

2707

id select_type table type possible_keys key key_len ref rows Extra

2551

1 SIMPLE t2 index b PRIMARY 4 NULL 2

2708

1 SIMPLE t2 index b b 5 NULL 2 Using index

2552

2709

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer

2553

2710

SELECT STRAIGHT_JOIN * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;

2554

2711

a b a b

2561

2718

CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));

2562

2719

INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);

2563

2720

INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);

2721

explain select max(key1) from t1 where key1 <= 0.6158;

2722

id select_type table type possible_keys key key_len ref rows Extra

2723

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2724

explain select max(key2) from t2 where key2 <= 1.6158;

2725

id select_type table type possible_keys key key_len ref rows Extra

2726

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2727

explain select min(key1) from t1 where key1 >= 0.3762;

2728

id select_type table type possible_keys key key_len ref rows Extra

2729

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2730

explain select min(key2) from t2 where key2 >= 1.3762;

2731

id select_type table type possible_keys key key_len ref rows Extra

2732

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2733

explain select max(key1), min(key2) from t1, t2

2734

where key1 <= 0.6158 and key2 >= 1.3762;

2735

id select_type table type possible_keys key key_len ref rows Extra

2736

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2737

explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;

2738

id select_type table type possible_keys key key_len ref rows Extra

2739

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2740

explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;

2741

id select_type table type possible_keys key key_len ref rows Extra

2742

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

2564

2743

select max(key1) from t1 where key1 <= 0.6158;

2565

2744

max(key1)

2566

2745

0.6158

2584

2763

min(key1)

2585

2764

0.3762

2586

2765

DROP TABLE t1,t2;

2587

CREATE TABLE t1 (i BIGINT NOT NULL);

2766

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2588

2767

INSERT INTO t1 VALUES (10);

2589

2768

SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;

2590

2769

i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01')

2600

2779

DROP TABLE t1, t2;

2601

2780

End of 4.1 tests

2602

2781

CREATE TABLE t1 (

2603

K2C4 varchar(4) collate utf8_bin NOT NULL default '',

2604

K4N4 varchar(4) collate utf8_bin NOT NULL default '0000',

2605

F2I4 int NOT NULL default '0'

2606

) ENGINE=MyISAM;

2782

K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '',

2783

K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000',

2784

F2I4 int(11) NOT NULL default '0'

2785

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2607

2786

INSERT INTO t1 VALUES

2608

2787

('W%RT', '0100', 1),

2609

2788

('W-RT', '0100', 1),

2682

2861

select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;

2683

2862

x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0

2684

2863

16 16 2 2

2685

create table t1 (f1 varchar(6) default NULL, f2 int primary key not null);

2864

create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);

2686

2865

create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));

2687

2866

insert into t1 values (" 2", 2);

2688

2867

insert into t2 values (" 2", " one "),(" 2", " two ");

2696

2875

select pk from t1 inner join t2 using (pk);

2697

2876

2698

2877

drop table t1,t2;

2699

create table t1 (a int, t1_val int);

2700

create table t2 (b int, t2_val int);

2701

create table t3 (a int, b int);

2878

create table t1 (a int(10), t1_val int(10));

2879

create table t2 (b int(10), t2_val int(10));

2880

create table t3 (a int(10), b int(10));

2702

2881

insert into t1 values (1,1),(2,2);

2703

2882

insert into t2 values (1,1),(2,2),(3,3);

2704

2883

insert into t3 values (1,1),(2,1),(3,1),(4,1);

2723

2902

2724

2903

2725

2904

drop table t1, t2;

2726

CREATE TABLE t1 (`id` int);

2727

CREATE TABLE t2 (`id` int);

2728

CREATE TABLE t3 (`id` int);

2905

CREATE TABLE t1 (`id` TINYINT);

2906

CREATE TABLE t2 (`id` TINYINT);

2907

CREATE TABLE t3 (`id` TINYINT);

2729

2908

INSERT INTO t1 VALUES (1),(2),(3);

2730

2909

INSERT INTO t2 VALUES (2);

2731

2910

INSERT INTO t3 VALUES (3);

2738

2917

SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);

2739

2918

ERROR 23000: Column 'id' in from clause is ambiguous

2740

2919

drop table t1, t2, t3;

2741

create table t1 (a int,b int);

2742

create table t2 (a int,b int);

2920

create table t1 (a int(10),b int(10));

2921

create table t2 (a int(10),b int(10));

2743

2922

insert into t1 values (1,10),(2,20),(3,30);

2744

2923

insert into t2 values (1,10);

2745

2924

select * from t1 inner join t2 using (A);

2786

2965

4 4

2787

2966

4 5

2788

2967

drop table t1,t2,t3;

2789

create table t1 (id int not null default '0');

2968

create table t1 (id int(11) not null default '0');

2790

2969

insert into t1 values (123),(191),(192);

2791

create table t2 (id char(16) not null);

2970

create table t2 (id char(16) character set utf8 not null);

2792

2971

insert into t2 values ('58013'),('58014'),('58015'),('58016');

2793

create table t3 (a_id int not null, b_id char(16));

2972

create table t3 (a_id int(11) not null, b_id char(16) character set utf8);

2794

2973

insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');

2795

2974

select count(*)

2796

2975

from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id;

2834

3013

f1 f2

2835

3014

4 2005-10-01

2836

3015

5 2005-12-30

2837

Warnings:

2838

Warning 1292 Incorrect date value: '2005-09-31' for column 'f2' at row 1

2839

3016

select * from t1 where f2 >= '2005-09-3a' order by f2;

2840

3017

f1 f2

2841

3018

3 2005-09-30

2848

3025

1 2005-01-01

2849

3026

2 2005-09-01

2850

3027

3 2005-09-30

2851

Warnings:

2852

Warning 1292 Incorrect date value: '2005-09-31' for column 'f2' at row 1

2853

3028

select * from t1 where f2 <= '2005-09-3a' order by f2;

2854

3029

f1 f2

2855

3030

1 2005-01-01

2954

3129

FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);

2955

3130

id select_type table type possible_keys key key_len ref rows Extra

2956

3131

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

2957

1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where

3132

1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR

2958

3133

DROP TABLE t1,t2;

3134

CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);

3135

INSERT t1 SET i = 0;

3136

UPDATE t1 SET i = -1;

3137

Warnings:

3138

Warning 1264 Out of range value for column 'i' at row 1

3139

SELECT * FROM t1;

3140

3141

3142

UPDATE t1 SET i = CAST(i - 1 AS SIGNED);

3143

Warnings:

3144

Warning 1264 Out of range value for column 'i' at row 1

3145

SELECT * FROM t1;

3146

3147

3148

UPDATE t1 SET i = i - 1;

3149

Warnings:

3150

Warning 1264 Out of range value for column 'i' at row 1

3151

SELECT * FROM t1;

3152

3153

255

3154

DROP TABLE t1;

2959

3155

create table t1 (a int);

2960

3156

insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

2961

3157

create table t2 (a int, b int, c int, e int, primary key(a,b,c));

2966

3162

select 'In next EXPLAIN, B.rows must be exactly 10:' Z;

2967

3163

2968

3164

In next EXPLAIN, B.rows must be exactly 10:

3165

explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5

3166

and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);

3167

id select_type table type possible_keys key key_len ref rows Extra

3168

1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where; Using MRR

3169

1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10

2969

3170

drop table t1, t2;

2970

3171

CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));

2971

3172

INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),

2978

3179

EXPLAIN

2979

3180

SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;

2980

3181

id select_type table type possible_keys key key_len ref rows Extra

2981

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where; Using index

2982

1 SIMPLE t2 ref c c 5 test.t1.a 1

3182

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using MRR

3183

1 SIMPLE t2 ref c c 5 test.t1.a 2

2983

3184

EXPLAIN

2984

3185

SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;

2985

3186

id select_type table type possible_keys key key_len ref rows Extra

2986

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where; Using index

2987

1 SIMPLE t2 ref c c 5 test.t1.a 1

3187

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where; Using MRR

3188

1 SIMPLE t2 ref c c 5 test.t1.a 2

2988

3189

DROP TABLE t1, t2;

2989

3190

create table t1 (

2990

a int not null auto_increment primary key,

2991

b int not null,

2992

c int not null

3191

a int unsigned not null auto_increment primary key,

3192

b bit not null,

3193

c bit not null

2993

3194

);

2994

3195

create table t2 (

2995

a int not null auto_increment primary key,

2996

b int not null,

2997

c int not null,

3196

a int unsigned not null auto_increment primary key,

3197

b bit not null,

3198

c int unsigned not null,

2998

3199

d varchar(50)

2999

3200

);

3000

3201

insert into t1 (b,c) values (0,1), (0,1);

3038

3239

id select_type table type possible_keys key key_len ref rows Extra

3039

3240

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3040

3241

1 SIMPLE t2 const idx1 NULL NULL NULL 1

3041

1 SIMPLE t3 ref idx1 idx1 5 const 2

3242

1 SIMPLE t3 ref idx1 idx1 5 const 3

3042

3243

SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id

3043

3244

WHERE t1.id=2;

3044

3245

id a b c d e

3061

3262

FROM t1 JOIN t2 ON t2.fk=t1.pk

3062

3263

WHERE t2.fk < 'c' AND t2.pk=t1.fk;

3063

3264

id select_type table type possible_keys key key_len ref rows Extra

3064

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3065

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where

3265

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR

3266

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where

3066

3267

EXPLAIN SELECT t2.*

3067

3268

FROM t1 JOIN t2 ON t2.fk=t1.pk

3068

3269

WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;

3069

3270

id select_type table type possible_keys key key_len ref rows Extra

3070

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3071

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where

3271

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR

3272

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where

3072

3273

EXPLAIN SELECT t2.*

3073

3274

FROM t1 JOIN t2 ON t2.fk=t1.pk

3074

3275

WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;

3075

3276

id select_type table type possible_keys key key_len ref rows Extra

3076

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3077

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where

3277

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR

3278

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where

3078

3279

DROP TABLE t1,t2;

3079

3280

CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));

3080

3281

CREATE TABLE t2 (a int, b varchar(20) NOT NULL,

3084

3285

EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;

3085

3286

id select_type table type possible_keys key key_len ref rows Extra

3086

3287

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3087

1 SIMPLE t2 const b b 82 const 1 Using index

3288

1 SIMPLE t2 const b b 22 const 1 Using index

3088

3289

DROP TABLE t1,t2;

3089

3290

CREATE TABLE t1(id int PRIMARY KEY, b int, e int);

3090

3291

CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));

3116

3317

t3.a=t2.a AND t3.c IN ('bb','ee') ;

3117

3318

id select_type table type possible_keys key key_len ref rows Extra

3118

3319

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3119

1 SIMPLE t3 range PRIMARY,ci ci 19 NULL 6 Using where; Using index

3120

1 SIMPLE t2 ref si,ai ai 5 test.t3.a 1 Using where

3320

1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR

3321

1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where

3121

3322

EXPLAIN

3122

3323

SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3

3123

3324

WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND

3169

3370

3170

3371

EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;

3171

3372

id select_type table type possible_keys key key_len ref rows Extra

3172

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,5 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3373

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3173

3374

DROP INDEX idx1 ON t1;

3174

3375

CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);

3175

3376

EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;

3176

3377

id select_type table type possible_keys key key_len ref rows Extra

3177

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,5 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3378

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3178

3379

DROP TABLE t1;

3179

3380

CREATE TABLE t1 (

3180

3381

ID1_with_null int NULL,

3206

3407

EXPLAIN SELECT * FROM t1

3207

3408

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;

3208

3409

id select_type table type possible_keys key key_len ref rows Extra

3209

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL # Using intersect(idx2,idx1); Using where; Using index

3410

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3210

3411

EXPLAIN SELECT * FROM t1

3211

3412

WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;

3212

3413

id select_type table type possible_keys key key_len ref rows Extra

3213

1 SIMPLE t1 ref idx1,idx2 idx2 4 const # Using where

3414

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3214

3415

EXPLAIN SELECT * FROM t1

3215

3416

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;

3216

3417

id select_type table type possible_keys key key_len ref rows Extra

3217

1 SIMPLE t1 ref idx1,idx2 idx2 4 const # Using where

3418

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3218

3419

DROP INDEX idx1 ON t1;

3219

3420

CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);

3220

3421

EXPLAIN SELECT * FROM t1

3221

3422

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;

3222

3423

id select_type table type possible_keys key key_len ref rows Extra

3223

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3424

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3224

3425

EXPLAIN SELECT * FROM t1

3225

3426

WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ;

3226

3427

id select_type table type possible_keys key key_len ref rows Extra

3227

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3428

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3228

3429

EXPLAIN SELECT * FROM t1

3229

3430

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;

3230

3431

id select_type table type possible_keys key key_len ref rows Extra

3231

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3432

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3232

3433

EXPLAIN SELECT * FROM t1

3233

3434

WHERE ID_better=1 AND ID1_with_null IS NULL AND

3234

3435

(ID2_with_null=1 OR ID2_with_null=2);

3235

3436

id select_type table type possible_keys key key_len ref rows Extra

3236

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3437

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where

3237

3438

DROP TABLE t1;

3238

3439

CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));

3239

3440

INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");

3263

3464

Warnings:

3264

3465

Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1

3265

3466

DROP TABLE t1,t2;

3266

create table t1 (a bigint);

3467

create table t1 (a bigint unsigned);

3267

3468

insert into t1 values

3268

(if(1, 92233720368547758, 1)),

3269

(case when 1 then 92233720368547758 else 1 end),

3270

(coalesce(92233720368547758, 1));

3469

(if(1, 9223372036854775808, 1)),

3470

(case when 1 then 9223372036854775808 else 1 end),

3471

(coalesce(9223372036854775808, 1));

3271

3472

select * from t1;

3272

3473

3273

92233720368547758

3274

92233720368547758

3275

92233720368547758

3474

9223372036854775808

3475

9223372036854775808

3476

9223372036854775808

3276

3477

drop table t1;

3277

3478

create table t1 select

3278

3479

if(1, 9223372036854775808, 1) i,

3280

3481

coalesce(9223372036854775808, 1) co;

3281

3482

show create table t1;

3282

3483

Table Create Table

3283

t1 CREATE TABLE `t1` (

3284

`i` decimal(19,0) NOT NULL,

3285

`c` decimal(19,0) NOT NULL,

3286

`co` decimal(19,0) NOT NULL

3287

) ENGINE=InnoDB

3484

t1 CREATE TABLE "t1" (

3485

"i" decimal(19,0) NOT NULL,

3486

"c" decimal(19,0) NOT NULL,

3487

"co" decimal(19,0) NOT NULL

3488

) ENGINE=MyISAM DEFAULT CHARSET=latin1

3288

3489

drop table t1;

3289

3490

select

3290

if(1, 1111111111111111111, 1) i,

3291

case when 1 then 1111111111111111111 else 1 end c,

3292

coalesce(1111111111111111111, 1) co;

3491

if(1, cast(1111111111111111111 as unsigned), 1) i,

3492

case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,

3493

coalesce(cast(1111111111111111111 as unsigned), 1) co;

3293

3494

i c co

3294

3495

1111111111111111111 1111111111111111111 1111111111111111111

3295

3496

CREATE TABLE t1 (name varchar(255));

3327

3528

cc 3 7

3328

3529

EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3329

3530

id select_type table type possible_keys key key_len ref rows Extra

3330

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3531

1 SIMPLE t2 ref name name 6 const 3 Using where

3331

3532

SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3332

3533

name LENGTH(name) n

3333

3534

cc 5 3

3335

3536

cc 3 7

3336

3537

EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3337

3538

id select_type table type possible_keys key key_len ref rows Extra

3338

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3539

1 SIMPLE t2 range name name 6 NULL 3 Using where

3339

3540

SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3340

3541

name LENGTH(name) n

3341

3542

cc 5 3

3344

3545

cc 3 7

3345

3546

EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3346

3547

id select_type table type possible_keys key key_len ref rows Extra

3347

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using filesort

3548

1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort

3348

3549

SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3349

3550

name LENGTH(name) n

3350

3551

cc 4 4

3354

3555

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3355

3556

id select_type table type possible_keys key key_len ref rows Extra

3356

3557

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3357

1 SIMPLE t2 ref name name 15 test.t1.name 1

3558

1 SIMPLE t2 ref name name 6 test.t1.name 2

3358

3559

SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3359

3560

name name n

3360

3561

ccc NULL NULL

3401

3602

cc 3 7

3402

3603

EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3403

3604

id select_type table type possible_keys key key_len ref rows Extra

3404

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3605

1 SIMPLE t2 ref name name 6 const 3 Using where

3405

3606

SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3406

3607

name LENGTH(name) n

3407

3608

cc 5 3

3409

3610

cc 3 7

3410

3611

EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3411

3612

id select_type table type possible_keys key key_len ref rows Extra

3412

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3613

1 SIMPLE t2 range name name 6 NULL 3 Using where

3413

3614

SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3414

3615

name LENGTH(name) n

3415

3616

cc 5 3

3418

3619

cc 3 7

3419

3620

EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3420

3621

id select_type table type possible_keys key key_len ref rows Extra

3421

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using filesort

3622

1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort

3422

3623

SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3423

3624

name LENGTH(name) n

3424

3625

cc 4 4

3428

3629

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3429

3630

id select_type table type possible_keys key key_len ref rows Extra

3430

3631

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3431

1 SIMPLE t2 ref name name 15 test.t1.name 1

3632

1 SIMPLE t2 ref name name 6 test.t1.name 2

3432

3633

SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3433

3634

name name n

3434

3635

ccc NULL NULL

3485

3686

WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);

3486

3687

id select_type table type possible_keys key key_len ref rows Extra

3487

3688

1 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index

3488

1 SIMPLE f2 ref inx inx 5 test.f1.b 3 Using where; Using index

3689

1 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index

3489

3690

DROP TABLE t1;

3490

3691

CREATE TABLE t1 (c1 INT, c2 INT);

3491

3692

INSERT INTO t1 VALUES (1,11), (2,22), (2,22);

3498

3699

ERROR HY000: Too high level of nesting for select

3499

3700

DROP TABLE t1;

3500

3701

CREATE TABLE t1 (

3501

c1 int NOT NULL AUTO_INCREMENT,

3702

c1 int(11) NOT NULL AUTO_INCREMENT,

3502

3703

c2 varchar(1000) DEFAULT NULL,

3503

c3 bigint DEFAULT NULL,

3504

c4 bigint DEFAULT NULL,

3704

c3 bigint(20) DEFAULT NULL,

3705

c4 bigint(20) DEFAULT NULL,

3505

3706

PRIMARY KEY (c1)

3506

3707

);

3507

3708

EXPLAIN EXTENDED

3547

3748

join_1.c1,

3548

3749

join_0.c1;

3549

3750

id select_type table type possible_keys key key_len ref rows filtered Extra

3550

1 SIMPLE join_0 ALL PRIMARY NULL NULL NULL 1 100.00 Using temporary; Using filesort

3551

1 SIMPLE join_1 eq_ref PRIMARY PRIMARY 4 test.join_0.c1 1 100.00

3552

1 SIMPLE join_2 eq_ref PRIMARY PRIMARY 4 test.join_1.c1 1 100.00 Using where

3553

1 SIMPLE join_3 eq_ref PRIMARY PRIMARY 4 test.join_2.c1 1 100.00 Using where

3554

1 SIMPLE join_4 eq_ref PRIMARY PRIMARY 4 test.join_3.c1 1 100.00 Using where

3555

1 SIMPLE join_5 eq_ref PRIMARY PRIMARY 4 test.join_4.c1 1 100.00 Using where

3556

1 SIMPLE join_6 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where

3557

1 SIMPLE join_7 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where

3751

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables

3558

3752

Warnings:

3559

Note 1003 select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_4`.`c2` = '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`

3753

Note 1003 select '0' AS "c1" from "test"."t1" "join_0" join "test"."t1" "join_1" join "test"."t1" "join_2" join "test"."t1" "join_3" join "test"."t1" "join_4" join "test"."t1" "join_5" join "test"."t1" "join_6" join "test"."t1" "join_7" where 0 group by '0','0','0','0','0'

3560

3754

SHOW WARNINGS;

3561

3755

Level Code Message

3562

3756

3563

3757

DROP TABLE t1;

3564

3758

SELECT 1 AS ` `;

3565

3759

3576

3770

3577

3771

Warnings:

3578

3772

Warning 1466 Leading spaces are removed from name ' x'

3579

CREATE TABLE t1 (c11 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

3580

CREATE TABLE t2 (c21 INT NOT NULL,

3773

CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

3774

CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,

3581

3775

c22 INT DEFAULT NULL,

3582

3776

KEY(c21, c22));

3583

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3777

CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0,

3584

3778

c32 INT DEFAULT NULL,

3585

3779

c33 INT NOT NULL,

3586

c34 INT DEFAULT 0,

3780

c34 INT UNSIGNED DEFAULT 0,

3587

3781

KEY (c33, c34, c32));

3588

3782

INSERT INTO t1 values (),(),(),(),();

3589

3783

INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;

3684

3878

Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34'

3685

3879

select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';

3686

3880

str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'

3687

3688

Warnings:

3689

Warning 1292 Truncated incorrect datetime value: '2007-02-30 12:34'

3881

3690

3882

select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';

3691

3883

str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'

3692

3884

3799

3991

3800

3992

SHOW STATUS LIKE 'Handler_read%';

3801

3993

Variable_name Value

3802

Handler_read_first 1

3803

Handler_read_key 5

3994

Handler_read_first 0

3995

Handler_read_key 2

3804

3996

Handler_read_next 0

3805

3997

Handler_read_prev 0

3806

3998

Handler_read_rnd 0

Older »