~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;

1169

1221

fld3 period price price2

1170

1222

admonishing 1002 28357832 8723648

1228

1280

Wotan 1002 28357832 8723648

1229

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;

1230

1282

fld1 fld3 period price price2

1231

18201 relaxing 1001 5987435 234724

1232

18601 vacuuming 1001 5987435 234724

1233

18801 inch 1001 5987435 234724

1234

18811 repetitions 1001 5987435 234724

1235

create temporary table t4 (

1236

companynr int NOT NULL default '0',

1283

018201 relaxing 1001 5987435 234724

1284

018601 vacuuming 1001 5987435 234724

1285

018801 inch 1001 5987435 234724

1286

018811 repetitions 1001 5987435 234724

1287

create table t4 (

1288

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

1237

1289

companyname char(30) NOT NULL default '',

1238

1290

PRIMARY KEY (companynr),

1239

1291

UNIQUE KEY companyname(companyname)

1240

) ENGINE=MyISAM COMMENT='companynames';

1292

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

1241

1293

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

1242

1294

companynr companyname

1243

0 Unknown

1295

00 Unknown

1244

1296

29 company 1

1245

1297

34 company 2

1246

1298

36 company 3

1254

1306

68 company 10

1255

1307

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

1256

1308

companynr companyname

1257

0 Unknown

1309

00 Unknown

1258

1310

29 company 1

1259

1311

34 company 2

1260

1312

36 company 3

1272

1324

select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505;

1273

1325

fld1 fld1

1274

1326

250501 250501

1327

250502 250501

1328

250503 250501

1329

250504 250501

1330

250505 250501

1275

1331

250501 250502

1332

250502 250502

1333

250503 250502

1334

250504 250502

1335

250505 250502

1276

1336

250501 250503

1337

250502 250503

1338

250503 250503

1339

250504 250503

1340

250505 250503

1277

1341

250501 250504

1342

250502 250504

1343

250503 250504

1344

250504 250504

1345

250505 250504

1278

1346

250501 250505

1279

250502 250501

1280

250502 250502

1281

250502 250503

1282

250502 250504

1283

1347

250502 250505

1284

250503 250501

1285

250503 250502

1286

250503 250503

1287

250503 250504

1288

1348

250503 250505

1289

250504 250501

1290

250504 250502

1291

250504 250503

1292

250504 250504

1293

1349

250504 250505

1294

250505 250501

1295

250505 250502

1296

250505 250503

1297

250505 250504

1298

1350

250505 250505

1299

1351

insert into t2 (fld1, companynr) values (999999,99);

1300

1352

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

1303

1355

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

1304

1356

count(*)

1305

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

1306

1366

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

1307

1367

companynr companyname

1308

1368

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

1309

1369

count(*)

1310

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

1311

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

1312

1426

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

1313

1427

companynr companynr

1314

1428

37 36

1315

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

1316

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;

1317

1435

fld1 companynr fld3 period

1318

38008 37 reporters 1008

1319

38208 37 Selfridge 1008

1436

038008 37 reporters 1008

1437

038208 37 Selfridge 1008

1320

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;

1321

1439

fld1 companynr fld3 period

1322

38008 37 reporters 1008

1323

38208 37 Selfridge 1008

1440

038008 37 reporters 1008

1441

038208 37 Selfridge 1008

1324

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;

1325

1443

fld1 companynr fld3 period

1326

38008 37 reporters 1008

1327

38208 37 Selfridge 1008

1444

038008 37 reporters 1008

1445

038208 37 Selfridge 1008

1328

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);

1329

1447

period

1330

1448

9410

1362

1480

1363

1481

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

1364

1482

companynr count(*) sum(fld1)

1365

0 82 10355753

1483

00 82 10355753

1366

1484

29 95 14473298

1367

1485

34 70 17788966

1368

1486

36 215 22786296

1384

1502

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

1385

1503

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

1386

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" <> ''))

1387

1510

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

1388

1511

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

1389

0 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087

1512

00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087

1390

1513

29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026

1391

1514

34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069

1392

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;

1441

1564

1199

1442

1565

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

1443

1566

companynr count(distinct fld1)

1444

0 82

1567

00 82

1445

1568

29 95

1446

1569

34 70

1447

1570

36 215

1455

1578

68 12

1456

1579

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

1457

1580

companynr count(*)

1458

0 82

1581

00 82

1459

1582

29 95

1460

1583

34 70

1461

1584

36 215

1469

1592

68 12

1470

1593

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

1471

1594

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

1472

0 82

1595

00 82

1473

1596

29 95

1474

1597

34 70

1475

1598

36 215

1483

1606

68 12

1484

1607

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

1485

1608

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

1486

0 82

1609

00 82

1487

1610

29 95

1488

1611

34 70

1489

1612

36 215

1497

1620

68 12

1498

1621

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

1499

1622

companynr count(distinct floor(fld1/100))

1500

0 47

1623

00 47

1501

1624

29 35

1502

1625

34 14

1503

1626

36 69

1511

1634

68 1

1512

1635

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

1513

1636

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

1514

0 47

1637

00 47

1515

1638

29 35

1516

1639

34 14

1517

1640

36 69

1550

1673

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

1551

1674

fld3 count(*)

1552

1675

spates 4181

1553

select companynr,companyname from t4 group by 1;

1554

companynr companyname

1676

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

1677

companynr|0 companyname

1555

1678

0 Unknown

1556

1679

29 company 1

1557

1680

34 company 2

1577

1700

53 company 7 4

1578

1701

58 company 8 23

1579

1702

65 company 9 10

1580

0 Unknown 82

1703

00 Unknown 82

1581

1704

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

1582

1705

fld1 count(*)

1583

1706

158402 4181

1601

1724

37 588

1602

1725

36 215

1603

1726

29 95

1604

0 82

1727

00 82

1605

1728

34 70

1606

1729

41 52

1607

1730

40 37

1620

1743

53 4

1621

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;

1622

1745

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

1623

teethe 1 1 5987435 5987435 5987435 5987435.0000

1624

dreaded 11401 1 5987435 5987435 5987435 5987435.0000

1625

scholastics 11402 1 28357832 28357832 28357832 28357832.0000

1626

audiology 11403 1 39654943 39654943 39654943 39654943.0000

1627

wallet 11501 1 5987435 5987435 5987435 5987435.0000

1628

parters 11701 1 5987435 5987435 5987435 5987435.0000

1629

eschew 11702 1 28357832 28357832 28357832 28357832.0000

1630

quitter 11703 1 39654943 39654943 39654943 39654943.0000

1631

neat 12001 1 5987435 5987435 5987435 5987435.0000

1632

Steinberg 12003 1 39654943 39654943 39654943 39654943.0000

1633

balled 12301 1 5987435 5987435 5987435 5987435.0000

1634

persist 12302 1 28357832 28357832 28357832 28357832.0000

1635

attainments 12303 1 39654943 39654943 39654943 39654943.0000

1636

capably 12501 1 5987435 5987435 5987435 5987435.0000

1637

impulsive 12602 1 28357832 28357832 28357832 28357832.0000

1638

starlet 12603 1 39654943 39654943 39654943 39654943.0000

1639

featherweight 12701 1 5987435 5987435 5987435 5987435.0000

1640

pessimist 12702 1 28357832 28357832 28357832 28357832.0000

1641

daughter 12703 1 39654943 39654943 39654943 39654943.0000

1642

lawgiver 13601 1 5987435 5987435 5987435 5987435.0000

1643

stated 13602 1 28357832 28357832 28357832 28357832.0000

1644

readable 13603 1 39654943 39654943 39654943 39654943.0000

1645

testicle 13801 1 5987435 5987435 5987435 5987435.0000

1646

Parsifal 13802 1 28357832 28357832 28357832 28357832.0000

1647

leavings 13803 1 39654943 39654943 39654943 39654943.0000

1648

squeaking 13901 1 5987435 5987435 5987435 5987435.0000

1649

contrasted 16001 1 5987435 5987435 5987435 5987435.0000

1650

leftover 16201 1 5987435 5987435 5987435 5987435.0000

1651

whiteners 16202 1 28357832 28357832 28357832 28357832.0000

1652

erases 16301 1 5987435 5987435 5987435 5987435.0000

1653

Punjab 16302 1 28357832 28357832 28357832 28357832.0000

1654

Merritt 16303 1 39654943 39654943 39654943 39654943.0000

1655

sweetish 18001 1 5987435 5987435 5987435 5987435.0000

1656

dogging 18002 1 28357832 28357832 28357832 28357832.0000

1657

scornfully 18003 1 39654943 39654943 39654943 39654943.0000

1658

fetters 18012 1 28357832 28357832 28357832 28357832.0000

1659

bivalves 18013 1 39654943 39654943 39654943 39654943.0000

1660

skulking 18021 1 5987435 5987435 5987435 5987435.0000

1661

flint 18022 1 28357832 28357832 28357832 28357832.0000

1662

flopping 18023 1 39654943 39654943 39654943 39654943.0000

1663

Judas 18032 1 28357832 28357832 28357832 28357832.0000

1664

vacuuming 18033 1 39654943 39654943 39654943 39654943.0000

1665

medical 18041 1 5987435 5987435 5987435 5987435.0000

1666

bloodbath 18042 1 28357832 28357832 28357832 28357832.0000

1667

subschema 18043 1 39654943 39654943 39654943 39654943.0000

1668

interdependent 18051 1 5987435 5987435 5987435 5987435.0000

1669

Graves 18052 1 28357832 28357832 28357832 28357832.0000

1670

neonatal 18053 1 39654943 39654943 39654943 39654943.0000

1671

sorters 18061 1 5987435 5987435 5987435 5987435.0000

1672

epistle 18062 1 28357832 28357832 28357832 28357832.0000

1673

Conley 18101 1 5987435 5987435 5987435 5987435.0000

1674

lectured 18102 1 28357832 28357832 28357832 28357832.0000

1675

Abraham 18103 1 39654943 39654943 39654943 39654943.0000

1676

cage 18201 1 5987435 5987435 5987435 5987435.0000

1677

hushes 18202 1 28357832 28357832 28357832 28357832.0000

1678

Simla 18402 1 28357832 28357832 28357832 28357832.0000

1679

reporters 18403 1 39654943 39654943 39654943 39654943.0000

1680

coexist 18601 1 5987435 5987435 5987435 5987435.0000

1681

Beebe 18602 1 28357832 28357832 28357832 28357832.0000

1682

Taoism 18603 1 39654943 39654943 39654943 39654943.0000

1683

Connally 18801 1 5987435 5987435 5987435 5987435.0000

1684

fetched 18802 1 28357832 28357832 28357832 28357832.0000

1685

checkpoints 18803 1 39654943 39654943 39654943 39654943.0000

1686

gritty 18811 1 5987435 5987435 5987435 5987435.0000

1687

firearm 18812 1 28357832 28357832 28357832 28357832.0000

1688

minima 19101 1 5987435 5987435 5987435 5987435.0000

1689

Selfridge 19102 1 28357832 28357832 28357832 28357832.0000

1690

disable 19103 1 39654943 39654943 39654943 39654943.0000

1691

witchcraft 19201 1 5987435 5987435 5987435 5987435.0000

1692

betroth 30501 1 5987435 5987435 5987435 5987435.0000

1693

Manhattanize 30502 1 28357832 28357832 28357832 28357832.0000

1694

imprint 30503 1 39654943 39654943 39654943 39654943.0000

1695

swelling 31901 1 5987435 5987435 5987435 5987435.0000

1696

interrelationships 36001 1 5987435 5987435 5987435 5987435.0000

1697

riser 36002 1 28357832 28357832 28357832 28357832.0000

1698

bee 38001 1 5987435 5987435 5987435 5987435.0000

1699

kanji 38002 1 28357832 28357832 28357832 28357832.0000

1700

dental 38003 1 39654943 39654943 39654943 39654943.0000

1701

railway 38011 1 5987435 5987435 5987435 5987435.0000

1702

validate 38012 1 28357832 28357832 28357832 28357832.0000

1703

normalizes 38013 1 39654943 39654943 39654943 39654943.0000

1704

Kline 38101 1 5987435 5987435 5987435 5987435.0000

1705

Anatole 38102 1 28357832 28357832 28357832 28357832.0000

1706

partridges 38103 1 39654943 39654943 39654943 39654943.0000

1707

recruited 38201 1 5987435 5987435 5987435 5987435.0000

1708

dimensions 38202 1 28357832 28357832 28357832 28357832.0000

1709

Chicana 38203 1 39654943 39654943 39654943 39654943.0000

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

1710

1833

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

1711

1834

companynr fld3 sum(price)

1712

1835

512 boat 786542

1721

1844

512 skies 786542

1722

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;

1723

1846

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

1724

0 1 Omaha Omaha 5987435 5987435.0000

1847

00 1 Omaha Omaha 5987435 5987435.0000

1725

1848

36 1 dubbed dubbed 28357832 28357832.0000

1726

1849

37 83 Abraham Wotan 1908978016 22999735.1325

1727

1850

50 2 scribbled tapestry 68012775 34006387.5000

1819

1942

234298

1820

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;

1821

1944

fld1 sum(price)

1822

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

1823

1952

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

1824

1953

companynr fld1

1825

1954

34 250501

1830

1959

34 250502

1831

1960

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

1832

1961

companynr count sum

1833

0 82 10355753

1962

00 82 10355753

1834

1963

29 95 14473298

1835

1964

34 70 17788966

1836

1965

37 588 83602098

1837

1966

41 52 12816335

1838

1967

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

1839

1968

companynr

1840

1969

1841

1970

1842

1971

1843

1972

1869

1998

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

1870

1999

count(*)

1871

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

1872

2004

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

1873

2005

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

1874

2006

0 1232609 1199

1934

2066

Tables_in_test (s%)

1935

2067

show tables from test like "t?";

1936

2068

Tables_in_test (t?)

1937

show columns from t2;

1938

Field Type Null Default Default_is_NULL On_Update

1939

auto INTEGER NO NO

1940

fld1 INTEGER NO 0 NO

1941

companynr INTEGER NO 0 NO

1942

fld3 VARCHAR NO NO

1943

fld4 VARCHAR NO NO

1944

fld5 VARCHAR NO NO

1945

fld6 VARCHAR NO NO

1946

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

1947

Field Type Null Default Default_is_NULL On_Update

1948

auto INTEGER NO NO

1949

fld1 INTEGER NO 0 NO

1950

companynr INTEGER NO 0 NO

1951

fld3 VARCHAR NO NO

1952

fld4 VARCHAR NO NO

1953

fld5 VARCHAR NO NO

1954

fld6 VARCHAR NO NO

1955

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

1956

Field Type Null Default Default_is_NULL On_Update

1957

auto INTEGER NO NO

1958

fld1 INTEGER NO 0 NO

1959

companynr INTEGER NO 0 NO

1960

fld3 VARCHAR NO NO

1961

fld4 VARCHAR NO NO

1962

fld5 VARCHAR NO NO

1963

fld6 VARCHAR NO NO

2069

show full columns from t2;

2070

Field Type Collation Null Key Default Extra Privileges Comment

2071

auto int(11) NULL NO PRI NULL auto_increment #

2072

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

2073

companynr tinyint(2) unsigned zerofill NULL NO NULL #

2074

fld3 char(30) latin1_swedish_ci NO MUL NULL #

2075

fld4 char(35) latin1_swedish_ci NO NULL #

2076

fld5 char(35) latin1_swedish_ci NO NULL #

2077

fld6 char(4) latin1_swedish_ci NO NULL #

2078

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

2079

Field Type Collation Null Key Default Extra Privileges Comment

2080

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

2081

fld3 char(30) latin1_swedish_ci NO MUL NULL #

2082

fld4 char(35) latin1_swedish_ci NO NULL #

2083

fld5 char(35) latin1_swedish_ci NO NULL #

2084

fld6 char(4) latin1_swedish_ci NO NULL #

2085

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

2086

Field Type Collation Null Key Default Extra Privileges Comment

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

1964

2092

drop table t4, t3, t2, t1;

1965

2093

CREATE TABLE t1 (

1966

id bigint NOT NULL auto_increment,

2094

id mediumint(8) unsigned NOT NULL auto_increment,

1967

2095

pseudo varchar(35) NOT NULL default '',

1968

2096

PRIMARY KEY (id),

1969

2097

UNIQUE KEY pseudo (pseudo)

1973

2101

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

1974

2102

rnd1

1975

2103

DROP TABLE t1;

1976

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

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;

1977

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);

1978

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

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;

1979

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);

1980

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;

1981

ERROR HY000: Received an invalid datetime value 'wrong-date-value'.

2109

gvid the_success the_fail the_size the_time

2110

Warnings:

2111

Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1

2112

Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1

1982

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;

1983

2114

gvid the_success the_fail the_size the_time

1984

2115

DROP TABLE t1,t2;

1985

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

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');

1986

2117

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

1987

2118

select wss_type from t1 where wss_type ='102935229216544106';

1988

2119

wss_type

2014

2145

select * from (t1 as t2 left join t1 as t3 using (a)), t1;

2015

2146

a a

2016

2147

1 1

2148

2 1

2149

3 1

2017

2150

1 2

2151

2 2

2152

3 2

2018

2153

1 3

2019

2 1

2020

2 2

2021

2154

2 3

2022

3 1

2023

3 2

2024

2155

3 3

2025

2156

select * from t1, (t1 as t2 left join t1 as t3 using (a));

2026

2157

a a

2058

2189

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

2059

2190

a a

2060

2191

1 2

2192

2 2

2193

3 2

2061

2194

1 3

2062

2 2

2063

2195

2 3

2064

3 2

2065

2196

3 3

2066

2197

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

2067

2198

a a

2166

2297

2167

2298

2168

2299

drop table t1;

2169

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

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;

2170

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);

2171

CREATE TEMPORARY 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;

2172

2303

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

2173

2304

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

2174

2305

aa id t2_id id

2186

2317

insert into t1 values (2);

2187

2318

insert into t2 values (1);

2188

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

2189

2327

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

2190

2328

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

2191

2329

id1 id2 id3 id4 id44

2215

2353

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

2216

2354

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

2217

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

2218

2360

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

2219

2361

a b c d

2220

2362

3 2 3 4

2221

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

2222

2368

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

2223

2369

a b c d

2224

2370

3 2 3 4

2225

2371

4 2 4 4

2226

2372

drop table t1, t2;

2227

CREATE TEMPORARY TABLE t1 (

2228

i int NOT NULL default '0',

2373

CREATE TABLE t1 (

2374

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

2229

2375

c char(10) NOT NULL default '',

2230

2376

PRIMARY KEY (i),

2231

2377

UNIQUE KEY c (c)

2244

2390

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

2245

2391

id select_type table type possible_keys key key_len ref rows Extra

2246

2392

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

2247

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

2393

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

2248

2394

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

2249

2395

id select_type table type possible_keys key key_len ref rows Extra

2250

2396

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

2251

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

2397

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

2252

2398

DROP TABLE t1, t2;

2253

2399

CREATE TABLE t1 ( city char(30) );

2254

2400

INSERT INTO t1 VALUES ('London');

2272

2418

city

2273

2419

London

2274

2420

DROP TABLE t1;

2275

create table t1 (a int, b int);

2421

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

2276

2422

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

2277

2423

select a-b from t1 order by 1;

2278

2424

a-b

2279

-1

2280

2425

2281

2426

2427

18446744073709551615

2282

2428

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

2283

2429

a-b (a-b < 0)

2284

-1 1

2285

2430

0 0

2286

2431

1 0

2432

18446744073709551615 0

2287

2433

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

2288

2434

d (a-b >= 0) b

2289

2435

1 1 0

2290

2436

0 1 1

2291

select a - b from t1 order by 1;

2292

a - b

2293

-1

2437

18446744073709551615 1 2

2438

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

2439

cast((a - b) as unsigned)

2294

2440

2295

2441

2442

18446744073709551615

2296

2443

drop table t1;

2297

create table t1 (a int);

2444

create table t1 (a int(11));

2298

2445

select all all * from t1;

2299

2446

2300

2447

select distinct distinct * from t1;

2305

2452

ERROR HY000: Incorrect usage of ALL and DISTINCT

2306

2453

drop table t1;

2307

2454

CREATE TABLE t1 (

2308

kunde_intern_id int NOT NULL default '0',

2309

kunde_id int NOT NULL default '0',

2310

FK_firma_id int NOT NULL default '0',

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',

2311

2458

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

2312

2459

vorname varchar(128) NOT NULL default '',

2313

2460

nachname varchar(128) NOT NULL default '',

2358

2505

COUNT(*)

2359

2506

2360

2507

drop table t1;

2361

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

2362

INSERT INTO t1 VALUES (0x4000000000000000);

2363

SELECT b FROM t1 WHERE b=0x4000000000000000;

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;

2364

2511

2365

4611686018427387904

2512

9223372036854775808

2366

2513

DROP TABLE t1;

2367

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

2368

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

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);

2369

2516

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

2370

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);

2371

2518

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

2372

2519

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

2373

2520

id name gid uid ident level

2374

2521

1 fs NULL NULL 0 READ

2375

2522

drop table t1,t2,t3;

2376

2523

CREATE TABLE t1 (

2377

acct_id int NOT NULL default '0',

2378

profile_id int default NULL,

2524

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

2525

profile_id smallint(6) default NULL,

2379

2526

UNIQUE KEY t1$acct_id (acct_id),

2380

2527

KEY t1$profile_id (profile_id)

2381

2528

);

2382

2529

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

2383

2530

CREATE TABLE t2 (

2384

profile_id int default NULL,

2385

queue_id int default NULL,

2386

seq int default NULL,

2531

profile_id smallint(6) default NULL,

2532

queue_id int(11) default NULL,

2533

seq int(11) default NULL,

2387

2534

KEY t2$queue_id (queue_id)

2388

2535

);

2389

2536

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

2390

2537

CREATE TABLE t3 (

2391

id int NOT NULL default '0',

2392

qtype int default NULL,

2393

seq int default NULL,

2394

warn_lvl int default NULL,

2395

crit_lvl int default NULL,

2396

rr1 int NOT NULL default '0',

2397

rr2 int default NULL,

2398

default_queue int NOT NULL default '0',

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',

2399

2546

KEY t3$qtype (qtype),

2400

2547

KEY t3$id (id)

2401

2548

);

2419

2566

2420

2567

NULL

2421

2568

drop table t1,t2;

2422

create table t2 (a int);

2569

create table t2 (a tinyint unsigned);

2423

2570

create index t2i on t2(a);

2424

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

2425

2575

select * from t2 where a > -1;

2426

2576

2427

2577

2458

2608

create table t11 like t1;

2459

2609

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

2460

2610

show table status like 't1%';

2461

Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment

2462

# test t1 STANDARD InnoDB # # # # #

2611

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

2612

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

2613

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

2463

2614

select 123 as a from t1 where f1 is null;

2464

2615

2465

2616

drop table t1,t11;

2519

2670

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

2520

2671

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

2521

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)

2522

2680

DROP TABLE t1,t2,t3;

2523

2681

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

2524

2682

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

2535

2693

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

2536

2694

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

2537

2695

ALTER TABLE t1 ENABLE KEYS;

2538

Warnings:

2539

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

2540

2696

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

2541

2697

id select_type table type possible_keys key key_len ref rows Extra

2542

1 SIMPLE t2 index b PRIMARY 4 NULL 2

2698

1 SIMPLE t2 index b b 5 NULL 2 Using index

2543

2699

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

2544

2700

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

2545

2701

a b a b

2549

2705

2 10 4 10

2550

2706

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

2551

2707

id select_type table type possible_keys key key_len ref rows Extra

2552

1 SIMPLE t2 index b PRIMARY 4 NULL 2

2708

1 SIMPLE t2 index b b 5 NULL 2 Using index

2553

2709

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

2554

2710

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

2555

2711

a b a b

2562

2718

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

2563

2719

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

2564

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

2565

2743

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

2566

2744

max(key1)

2567

0.6158

2745

0.6158000230789185

2568

2746

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

2569

2747

max(key2)

2570

1.6158

2748

1.6158000230789185

2571

2749

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

2572

2750

min(key1)

2573

0.3762

2751

0.37619999051094055

2574

2752

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

2575

2753

min(key2)

2576

1.3762

2754

1.3761999607086182

2577

2755

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

2578

2756

where key1 <= 0.6158 and key2 >= 1.3762;

2579

2757

max(key1) min(key2)

2580

0.6158 1.3762

2758

0.6158000230789185 1.3761999607086182

2581

2759

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

2582

2760

max(key1)

2583

0.6158

2761

0.6158000230789185

2584

2762

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

2585

2763

min(key1)

2586

0.3762

2764

0.37619999051094055

2587

2765

DROP TABLE t1,t2;

2588

CREATE TABLE t1 (i BIGINT NOT NULL);

2766

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2589

2767

INSERT INTO t1 VALUES (10);

2590

2768

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

2591

2769

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

2593

2771

DROP TABLE t1;

2594

2772

CREATE TABLE t1 (c0 int);

2595

2773

CREATE TABLE t2 (c0 int);

2596

INSERT INTO t1 VALUES(@@server_id);

2597

INSERT INTO t2 VALUES(@@server_id);

2598

SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@server_id);

2774

INSERT INTO t1 VALUES(@@connect_timeout);

2775

INSERT INTO t2 VALUES(@@connect_timeout);

2776

SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout);

2599

2777

c0 c0

2600

2778

X X

2601

2779

DROP TABLE t1, t2;

2602

2780

End of 4.1 tests

2603

CREATE TEMPORARY TABLE t1 (

2604

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

2605

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

2606

F2I4 int NOT NULL default '0'

2607

) ENGINE=MyISAM;

2781

CREATE TABLE t1 (

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;

2608

2786

INSERT INTO t1 VALUES

2609

2787

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

2610

2788

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

2649

2827

Wall

2650

2828

Wall

2651

2829

drop table t1;

2652

create temporary table t1 (s1 int) engine=myisam;

2830

create table t1 (s1 int) engine=myisam;

2653

2831

insert into t1 values (0);

2654

2832

select avg(distinct s1) from t1 group by s1 with rollup;

2655

2833

avg(distinct s1)

2683

2861

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

2684

2862

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

2685

2863

16 16 2 2

2686

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);

2687

2865

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

2688

2866

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

2689

2867

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

2697

2875

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

2698

2876

2699

2877

drop table t1,t2;

2700

create table t1 (a int, t1_val int);

2701

create table t2 (b int, t2_val int);

2702

create table t3 (a int, b int);

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));

2703

2881

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

2704

2882

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

2705

2883

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

2724

2902

2725

2903

2726

2904

drop table t1, t2;

2727

CREATE TABLE t1 (`id` int);

2728

CREATE TABLE t2 (`id` int);

2729

CREATE TABLE t3 (`id` int);

2905

CREATE TABLE t1 (`id` TINYINT);

2906

CREATE TABLE t2 (`id` TINYINT);

2907

CREATE TABLE t3 (`id` TINYINT);

2730

2908

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

2731

2909

INSERT INTO t2 VALUES (2);

2732

2910

INSERT INTO t3 VALUES (3);

2739

2917

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

2740

2918

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

2741

2919

drop table t1, t2, t3;

2742

create table t1 (a int,b int);

2743

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));

2744

2922

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

2745

2923

insert into t2 values (1,10);

2746

2924

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

2787

2965

4 4

2788

2966

4 5

2789

2967

drop table t1,t2,t3;

2790

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

2968

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

2791

2969

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

2792

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

2970

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

2793

2971

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

2794

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);

2795

2973

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

2796

2974

select count(*)

2797

2975

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

2818

2996

insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),

2819

2997

(4,'2005-10-01'),(5,'2005-12-30');

2820

2998

select * from t1 where f2 >= 0 order by f2;

2821

ERROR HY000: Received an invalid datetime value '0'.

2999

f1 f2

3000

1 2005-01-01

3001

2 2005-09-01

3002

3 2005-09-30

3003

4 2005-10-01

3004

5 2005-12-30

2822

3005

select * from t1 where f2 >= '0000-00-00' order by f2;

2823

ERROR HY000: Received an invalid datetime value '0000-00-00'.

3006

f1 f2

3007

1 2005-01-01

3008

2 2005-09-01

3009

3 2005-09-30

3010

4 2005-10-01

3011

5 2005-12-30

2824

3012

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

2825

ERROR HY000: Received an invalid datetime value '2005-09-31'.

3013

f1 f2

3014

4 2005-10-01

3015

5 2005-12-30

2826

3016

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

2827

ERROR HY000: Received an invalid datetime value '2005-09-3a'.

3017

f1 f2

3018

3 2005-09-30

3019

4 2005-10-01

3020

5 2005-12-30

3021

Warnings:

3022

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

2828

3023

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

2829

ERROR HY000: Received an invalid datetime value '2005-09-31'.

3024

f1 f2

3025

1 2005-01-01

3026

2 2005-09-01

3027

3 2005-09-30

2830

3028

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

2831

ERROR HY000: Received an invalid datetime value '2005-09-3a'.

3029

f1 f2

3030

1 2005-01-01

3031

2 2005-09-01

3032

Warnings:

3033

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

2832

3034

drop table t1;

2833

3035

CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a));

2834

3036

CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a));

2909

3111

NULL a NULL

2910

3112

drop table t1,t2;

2911

3113

select * from (select * left join t on f1=f2) tt;

2912

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'on f1=f2) tt' at line 1

3114

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on f1=f2) tt' at line 1

2913

3115

CREATE TABLE t1 (sku int PRIMARY KEY, pr int);

2914

3116

CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255));

2915

3117

INSERT INTO t1 VALUES

2927

3129

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

2928

3130

id select_type table type possible_keys key key_len ref rows Extra

2929

3131

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

2930

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

2931

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;

2932

3155

create table t1 (a int);

2933

3156

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

2934

3157

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

2939

3162

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

2940

3163

2941

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

2942

3170

drop table t1, t2;

2943

3171

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

2944

3172

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

2951

3179

EXPLAIN

2952

3180

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

2953

3181

id select_type table type possible_keys key key_len ref rows Extra

2954

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

2955

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

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

2956

3184

EXPLAIN

2957

3185

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

2958

3186

id select_type table type possible_keys key key_len ref rows Extra

2959

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

2960

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

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

2961

3189

DROP TABLE t1, t2;

2962

3190

create table t1 (

2963

a int not null auto_increment primary key,

2964

b int not null,

2965

c int not null

3191

a int unsigned not null auto_increment primary key,

3192

b bit not null,

3193

c bit not null

2966

3194

);

2967

3195

create table t2 (

2968

a int not null auto_increment primary key,

2969

b int not null,

2970

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,

2971

3199

d varchar(50)

2972

3200

);

2973

3201

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

3011

3239

id select_type table type possible_keys key key_len ref rows Extra

3012

3240

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3013

3241

1 SIMPLE t2 const idx1 NULL NULL NULL 1

3014

1 SIMPLE t3 ref idx1 idx1 5 const 2 Using where

3242

1 SIMPLE t3 ref idx1 idx1 5 const 3

3015

3243

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

3016

3244

WHERE t1.id=2;

3017

3245

id a b c d e

3034

3262

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

3035

3263

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

3036

3264

id select_type table type possible_keys key key_len ref rows Extra

3037

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3038

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

3039

3267

EXPLAIN SELECT t2.*

3040

3268

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

3041

3269

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

3042

3270

id select_type table type possible_keys key key_len ref rows Extra

3043

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3044

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

3045

3273

EXPLAIN SELECT t2.*

3046

3274

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

3047

3275

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

3048

3276

id select_type table type possible_keys key key_len ref rows Extra

3049

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3050

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

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

3051

3279

DROP TABLE t1,t2;

3052

3280

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

3053

3281

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

3057

3285

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

3058

3286

id select_type table type possible_keys key key_len ref rows Extra

3059

3287

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3060

1 SIMPLE t2 const b b 82 const 1 Using index

3288

1 SIMPLE t2 const b b 22 const 1 Using index

3061

3289

DROP TABLE t1,t2;

3062

3290

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

3063

3291

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

3081

3309

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

3082

3310

id select_type table type possible_keys key key_len ref rows Extra

3083

3311

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3084

1 SIMPLE t2 range si si 5 NULL 4 Using where

3312

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

3085

3313

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

3086

3314

EXPLAIN

3087

3315

SELECT t3.a FROM t1,t2,t3

3089

3317

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

3090

3318

id select_type table type possible_keys key key_len ref rows Extra

3091

3319

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3092

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

3093

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

3094

3322

EXPLAIN

3095

3323

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

3096

3324

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

3097

3325

t3.c IN ('bb','ee');

3098

3326

id select_type table type possible_keys key key_len ref rows Extra

3099

3327

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3100

1 SIMPLE t2 range si si 5 NULL 2 Using where

3328

1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR

3101

3329

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

3102

3330

EXPLAIN

3103

3331

SELECT t3.a FROM t1,t2,t3

3105

3333

t3.c IN ('bb','ee');

3106

3334

id select_type table type possible_keys key key_len ref rows Extra

3107

3335

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3108

1 SIMPLE t2 range si,ai si 5 NULL 2 Using where

3336

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

3109

3337

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

3110

3338

DROP TABLE t1,t2,t3;

3111

3339

CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);

3120

3348

CREATE TABLE t1(a int);

3121

3349

INSERT into t1 values (1), (2), (3);

3122

3350

SELECT * FROM t1 LIMIT 2, -1;

3123

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '-1' at line 1

3351

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1

3124

3352

DROP TABLE t1;

3125

3353

CREATE TABLE t1 (

3126

3354

ID_with_null int NULL,

3142

3370

3143

3371

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

3144

3372

id select_type table type possible_keys key key_len ref rows Extra

3145

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

3146

3374

DROP INDEX idx1 ON t1;

3147

3375

CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);

3148

3376

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

3149

3377

id select_type table type possible_keys key key_len ref rows Extra

3150

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

3151

3379

DROP TABLE t1;

3152

3380

CREATE TABLE t1 (

3153

3381

ID1_with_null int NULL,

3179

3407

EXPLAIN SELECT * FROM t1

3180

3408

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

3181

3409

id select_type table type possible_keys key key_len ref rows Extra

3182

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

3183

3411

EXPLAIN SELECT * FROM t1

3184

3412

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

3185

3413

id select_type table type possible_keys key key_len ref rows Extra

3186

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

3414

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

3187

3415

EXPLAIN SELECT * FROM t1

3188

3416

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

3189

3417

id select_type table type possible_keys key key_len ref rows Extra

3190

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

3418

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

3191

3419

DROP INDEX idx1 ON t1;

3192

3420

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

3193

3421

EXPLAIN SELECT * FROM t1

3194

3422

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

3195

3423

id select_type table type possible_keys key key_len ref rows Extra

3196

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

3197

3425

EXPLAIN SELECT * FROM t1

3198

3426

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

3199

3427

id select_type table type possible_keys key key_len ref rows Extra

3200

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

3201

3429

EXPLAIN SELECT * FROM t1

3202

3430

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

3203

3431

id select_type table type possible_keys key key_len ref rows Extra

3204

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

3205

3433

EXPLAIN SELECT * FROM t1

3206

3434

WHERE ID_better=1 AND ID1_with_null IS NULL AND

3207

3435

(ID2_with_null=1 OR ID2_with_null=2);

3208

3436

id select_type table type possible_keys key key_len ref rows Extra

3209

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

3210

3438

DROP TABLE t1;

3211

3439

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

3212

3440

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

3225

3453

AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";

3226

3454

id select_type table type possible_keys key key_len ref rows Extra

3227

3455

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

3228

1 SIMPLE t1 range ts ts 9 NULL 1 Using where

3456

1 SIMPLE t1 range ts ts 4 NULL 1 Using index condition; Using where; Using MRR

3457

Warnings:

3458

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

3229

3459

SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30

3230

3460

AND t1.ts BETWEEN t2.dt1 AND t2.dt2

3231

3461

AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";

3232

3462

a ts a dt1 dt2

3233

3463

30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00

3464

Warnings:

3465

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

3234

3466

DROP TABLE t1,t2;

3235

create table t1 (a bigint);

3467

create table t1 (a bigint unsigned);

3236

3468

insert into t1 values

3237

(if(1, 92233720368547758, 1)),

3238

(case when 1 then 92233720368547758 else 1 end),

3239

(coalesce(92233720368547758, 1));

3469

(if(1, 9223372036854775808, 1)),

3470

(case when 1 then 9223372036854775808 else 1 end),

3471

(coalesce(9223372036854775808, 1));

3240

3472

select * from t1;

3241

3473

3242

92233720368547758

3243

92233720368547758

3244

92233720368547758

3474

9223372036854775808

3475

9223372036854775808

3476

9223372036854775808

3245

3477

drop table t1;

3246

3478

create table t1 select

3247

3479

if(1, 9223372036854775808, 1) i,

3249

3481

coalesce(9223372036854775808, 1) co;

3250

3482

show create table t1;

3251

3483

Table Create Table

3252

t1 CREATE TABLE `t1` (

3253

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

3254

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

3255

`co` DECIMAL(19,0) NOT NULL

3256

) ENGINE=InnoDB COLLATE = utf8_general_ci

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

3257

3489

drop table t1;

3258

3490

select

3259

if(1, 1111111111111111111, 1) i,

3260

case when 1 then 1111111111111111111 else 1 end c,

3261

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;

3262

3494

i c co

3263

3495

1111111111111111111 1111111111111111111 1111111111111111111

3264

3496

CREATE TABLE t1 (name varchar(255));

3296

3528

cc 3 7

3297

3529

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

3298

3530

id select_type table type possible_keys key key_len ref rows Extra

3299

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3531

1 SIMPLE t2 ref name name 6 const 3 Using where

3300

3532

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

3301

3533

name LENGTH(name) n

3302

3534

cc 5 3

3304

3536

cc 3 7

3305

3537

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

3306

3538

id select_type table type possible_keys key key_len ref rows Extra

3307

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3539

1 SIMPLE t2 range name name 6 NULL 3 Using where

3308

3540

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

3309

3541

name LENGTH(name) n

3310

3542

cc 5 3

3313

3545

cc 3 7

3314

3546

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

3315

3547

id select_type table type possible_keys key key_len ref rows Extra

3316

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

3548

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

3317

3549

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

3318

3550

name LENGTH(name) n

3319

3551

cc 4 4

3323

3555

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

3324

3556

id select_type table type possible_keys key key_len ref rows Extra

3325

3557

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3326

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

3558

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

3327

3559

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

3328

3560

name name n

3329

3561

ccc NULL NULL

3370

3602

cc 3 7

3371

3603

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

3372

3604

id select_type table type possible_keys key key_len ref rows Extra

3373

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3605

1 SIMPLE t2 ref name name 6 const 3 Using where

3374

3606

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

3375

3607

name LENGTH(name) n

3376

3608

cc 5 3

3378

3610

cc 3 7

3379

3611

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

3380

3612

id select_type table type possible_keys key key_len ref rows Extra

3381

1 SIMPLE t2 ALL name NULL NULL NULL 7 Using where

3613

1 SIMPLE t2 range name name 6 NULL 3 Using where

3382

3614

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

3383

3615

name LENGTH(name) n

3384

3616

cc 5 3

3387

3619

cc 3 7

3388

3620

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

3389

3621

id select_type table type possible_keys key key_len ref rows Extra

3390

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

3622

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

3391

3623

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

3392

3624

name LENGTH(name) n

3393

3625

cc 4 4

3397

3629

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

3398

3630

id select_type table type possible_keys key key_len ref rows Extra

3399

3631

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3400

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

3632

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

3401

3633

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

3402

3634

name name n

3403

3635

ccc NULL NULL

3454

3686

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

3455

3687

id select_type table type possible_keys key key_len ref rows Extra

3456

3688

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

3457

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

3458

3690

DROP TABLE t1;

3459

3691

CREATE TABLE t1 (c1 INT, c2 INT);

3460

3692

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

3467

3699

ERROR HY000: Too high level of nesting for select

3468

3700

DROP TABLE t1;

3469

3701

CREATE TABLE t1 (

3470

c1 int NOT NULL AUTO_INCREMENT,

3702

c1 int(11) NOT NULL AUTO_INCREMENT,

3471

3703

c2 varchar(1000) DEFAULT NULL,

3472

c3 bigint DEFAULT NULL,

3473

c4 bigint DEFAULT NULL,

3704

c3 bigint(20) DEFAULT NULL,

3705

c4 bigint(20) DEFAULT NULL,

3474

3706

PRIMARY KEY (c1)

3475

3707

);

3476

3708

EXPLAIN EXTENDED

3516

3748

join_1.c1,

3517

3749

join_0.c1;

3518

3750

id select_type table type possible_keys key key_len ref rows filtered Extra

3519

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

3520

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

3521

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

3522

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

3523

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

3524

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

3525

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

3526

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

3751

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

3527

3752

Warnings:

3528

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

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'

3529

3754

SHOW WARNINGS;

3530

3755

Level Code Message

3531

3756

3532

3757

DROP TABLE t1;

3533

3758

SELECT 1 AS ` `;

3534

3759

3545

3770

3546

3771

Warnings:

3547

3772

Warning 1466 Leading spaces are removed from name ' x'

3548

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

3549

CREATE TABLE t2 (c21 INT NOT NULL,

3773

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

3774

CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,

3550

3775

c22 INT DEFAULT NULL,

3551

3776

KEY(c21, c22));

3552

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3777

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

3553

3778

c32 INT DEFAULT NULL,

3554

3779

c33 INT NOT NULL,

3555

c34 INT DEFAULT 0,

3780

c34 INT UNSIGNED DEFAULT 0,

3556

3781

KEY (c33, c34, c32));

3557

3782

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

3558

3783

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

3584

3809

3585

3810

3586

3811

DROP TABLE t1, t2, t3;

3812

select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'

3813

and '2007/10/20 00:00:00 GMT';

3814

str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'

3815

and '2007/10/20 00:00:00 GMT'

3816

3817

Warnings:

3818

Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT'

3819

Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT'

3820

select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';

3821

str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'

3822

3823

Warnings:

3824

Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6'

3825

select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';

3826

str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'

3827

3828

Warnings:

3829

Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6'

3830

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';

3831

str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'

3832

3833

Warnings:

3834

Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6'

3835

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';

3836

str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'

3837

3838

Warnings:

3839

Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6'

3840

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';

3841

str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'

3842

3843

Warnings:

3844

Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6'

3845

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';

3846

str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'

3847

3848

Warnings:

3849

Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6'

3850

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';

3851

str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'

3852

3853

Warnings:

3854

Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6'

3855

select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3856

str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'

3857

3858

Warnings:

3859

Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'

3860

select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3861

str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'

3862

3863

Warnings:

3864

Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'

3865

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

3866

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

3867

3868

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

3869

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

3870

3871

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

3872

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

3873

3874

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

3875

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

3876

3877

Warnings:

3878

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

3879

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

3880

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

3881

3882

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

3883

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

3884

3885

select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'

3886

and '2007/10/20 00:00:00';

3887

str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'

3888

and '2007/10/20 00:00:00'

3889

3890

select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';

3891

str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'

3892

3893

Warnings:

3894

Warning 1292 Truncated incorrect datetime value: ''

3895

select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';

3896

str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'

3897

3898

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

3899

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

3900

3901

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

3902

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

3903

NULL

3904

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

3905

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

3906

3907

Warnings:

3908

Warning 1292 Truncated incorrect datetime value: ''

3909

select str_to_date('1','%Y-%m-%d') = '1';

3910

str_to_date('1','%Y-%m-%d') = '1'

3911

3912

Warnings:

3913

Warning 1292 Truncated incorrect date value: '1'

3914

select str_to_date('1','%Y-%m-%d') = '1';

3915

str_to_date('1','%Y-%m-%d') = '1'

3916

3917

Warnings:

3918

Warning 1292 Truncated incorrect date value: '1'

3919

select str_to_date('','%Y-%m-%d') = '';

3920

str_to_date('','%Y-%m-%d') = ''

3921

3922

Warnings:

3923

Warning 1292 Truncated incorrect date value: ''

3924

select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;

3925

str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL

3926

3927

select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';

3928

str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'

3929

3930

select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;

3931

str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL

3932

3587

3933

3588

3934

3589

3935

# Bug#30736: Row Size Too Large Error Creating a Table and

3645

3991

3646

3992

SHOW STATUS LIKE 'Handler_read%';

3647

3993

Variable_name Value

3648

Handler_read_first 1

3649

Handler_read_key 5

3994

Handler_read_first 0

3995

Handler_read_key 2

3650

3996

Handler_read_next 0

3651

3997

Handler_read_prev 0

3652

3998

Handler_read_rnd 0

3670

4016

3671

4017

3672

4018

DROP TABLE t1;

3673

CREATE TABLE t1 (a INT);

3674

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

3675

CREATE TABLE t2 (b INT);

3676

INSERT INTO t2 VALUES (2);

3677

SELECT * FROM t1 WHERE a = 1 + 1;

3678

3679

3680

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1;

3681

id select_type table type possible_keys key key_len ref rows filtered Extra

3682

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where

3683

Warnings:

3684

Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1)))

3685

SELECT * FROM t1 HAVING a = 1 + 1;

3686

3687

3688

EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1;

3689

id select_type table type possible_keys key key_len ref rows filtered Extra

3690

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00

3691

Warnings:

3692

Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1)))

3693

SELECT * FROM t1, t2 WHERE a = b + (1 + 1);

3694

a b

3695

4 2

3696

EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1);

3697

id select_type table type possible_keys key key_len ref rows filtered Extra

3698

1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00

3699

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer

3700

Warnings:

3701

Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = (`test`.`t2`.`b` + <cache>((1 + 1))))

3702

SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;

3703

b a

3704

2 3

3705

EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;

3706

id select_type table type possible_keys key key_len ref rows filtered Extra

3707

1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00

3708

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00

3709

Warnings:

3710

Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = (`test`.`t2`.`b` + 1))) where 1

3711

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');

3712

id select_type table type possible_keys key key_len ref rows filtered Extra

3713

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where

3714

Warnings:

3715

Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00')))

3716

DROP TABLE t1, t2;

Older »