~drizzle-trunk/drizzle/development

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

fld1 fld3 period price price2

1235

018201 relaxing 1001 5987435 234724

1236

018601 vacuuming 1001 5987435 234724

1237

018801 inch 1001 5987435 234724

1238

018811 repetitions 1001 5987435 234724

1235

18201 relaxing 1001 5987435 234724

1236

18601 vacuuming 1001 5987435 234724

1237

18801 inch 1001 5987435 234724

1238

18811 repetitions 1001 5987435 234724

1239

create table t4 (

1240

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

1240

companynr tinyint unsigned NOT NULL default '0',

1241

companyname char(30) NOT NULL default '',

1242

PRIMARY KEY (companynr),

1243

UNIQUE KEY companyname(companyname)

1244

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

1245

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

1246

companynr companyname

1247

00 Unknown

1247

0 Unknown

1248

29 company 1

1249

34 company 2

1250

36 company 3

1258

68 company 10

1259

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

1260

companynr companyname

1261

00 Unknown

1261

0 Unknown

1262

29 company 1

1263

34 company 2

1264

36 company 3

1319

41 40

1320

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

fld1 companynr fld3 period

1322

038008 37 reporters 1008

1323

038208 37 Selfridge 1008

1322

38008 37 reporters 1008

1323

38208 37 Selfridge 1008

1324

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

fld1 companynr fld3 period

1326

038008 37 reporters 1008

1327

038208 37 Selfridge 1008

1326

38008 37 reporters 1008

1327

38208 37 Selfridge 1008

1328

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

fld1 companynr fld3 period

1330

038008 37 reporters 1008

1331

038208 37 Selfridge 1008

1330

38008 37 reporters 1008

1331

38208 37 Selfridge 1008

1332

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

period

1334

9410

1366

1367

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

1368

companynr count(*) sum(fld1)

1369

00 82 10355753

1369

0 82 10355753

1370

29 95 14473298

1371

34 70 17788966

1372

36 215 22786296

1390

70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069

1391

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

1392

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

1393

00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087

1393

0 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087

1394

29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026

1395

34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069

1396

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

1199

1446

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

1447

companynr count(distinct fld1)

1448

00 82

1448

0 82

1449

29 95

1450

34 70

1451

36 215

1459

68 12

1460

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

1461

companynr count(*)

1462

00 82

1462

0 82

1463

29 95

1464

34 70

1465

36 215

1473

68 12

1474

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

1475

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

1476

00 82

1476

0 82

1477

29 95

1478

34 70

1479

36 215

1487

68 12

1488

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

1489

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

1490

00 82

1490

0 82

1491

29 95

1492

34 70

1493

36 215

1501

68 12

1502

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

1503

companynr count(distinct floor(fld1/100))

1504

00 47

1504

0 47

1505

29 35

1506

34 14

1507

36 69

1515

68 1

1516

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

1517

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

1518

00 47

1518

0 47

1519

29 35

1520

34 14

1521

36 69

1581

53 company 7 4

1582

58 company 8 23

1583

65 company 9 10

1584

00 Unknown 82

1584

0 Unknown 82

1585

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

1586

fld1 count(*)

1587

158402 4181

1605

37 588

1606

36 215

1607

29 95

1608

00 82

1608

0 82

1609

34 70

1610

41 52

1611

40 37

1624

53 4

1625

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

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

1627

teethe 000001 1 5987435 5987435 5987435 5987435.0000

1628

dreaded 011401 1 5987435 5987435 5987435 5987435.0000

1629

scholastics 011402 1 28357832 28357832 28357832 28357832.0000

1630

audiology 011403 1 39654943 39654943 39654943 39654943.0000

1631

wallet 011501 1 5987435 5987435 5987435 5987435.0000

1632

parters 011701 1 5987435 5987435 5987435 5987435.0000

1633

eschew 011702 1 28357832 28357832 28357832 28357832.0000

1634

quitter 011703 1 39654943 39654943 39654943 39654943.0000

1635

neat 012001 1 5987435 5987435 5987435 5987435.0000

1636

Steinberg 012003 1 39654943 39654943 39654943 39654943.0000

1637

balled 012301 1 5987435 5987435 5987435 5987435.0000

1638

persist 012302 1 28357832 28357832 28357832 28357832.0000

1639

attainments 012303 1 39654943 39654943 39654943 39654943.0000

1640

capably 012501 1 5987435 5987435 5987435 5987435.0000

1641

impulsive 012602 1 28357832 28357832 28357832 28357832.0000

1642

starlet 012603 1 39654943 39654943 39654943 39654943.0000

1643

featherweight 012701 1 5987435 5987435 5987435 5987435.0000

1644

pessimist 012702 1 28357832 28357832 28357832 28357832.0000

1645

daughter 012703 1 39654943 39654943 39654943 39654943.0000

1646

lawgiver 013601 1 5987435 5987435 5987435 5987435.0000

1647

stated 013602 1 28357832 28357832 28357832 28357832.0000

1648

readable 013603 1 39654943 39654943 39654943 39654943.0000

1649

testicle 013801 1 5987435 5987435 5987435 5987435.0000

1650

Parsifal 013802 1 28357832 28357832 28357832 28357832.0000

1651

leavings 013803 1 39654943 39654943 39654943 39654943.0000

1652

squeaking 013901 1 5987435 5987435 5987435 5987435.0000

1653

contrasted 016001 1 5987435 5987435 5987435 5987435.0000

1654

leftover 016201 1 5987435 5987435 5987435 5987435.0000

1655

whiteners 016202 1 28357832 28357832 28357832 28357832.0000

1656

erases 016301 1 5987435 5987435 5987435 5987435.0000

1657

Punjab 016302 1 28357832 28357832 28357832 28357832.0000

1658

Merritt 016303 1 39654943 39654943 39654943 39654943.0000

1659

sweetish 018001 1 5987435 5987435 5987435 5987435.0000

1660

dogging 018002 1 28357832 28357832 28357832 28357832.0000

1661

scornfully 018003 1 39654943 39654943 39654943 39654943.0000

1662

fetters 018012 1 28357832 28357832 28357832 28357832.0000

1663

bivalves 018013 1 39654943 39654943 39654943 39654943.0000

1664

skulking 018021 1 5987435 5987435 5987435 5987435.0000

1665

flint 018022 1 28357832 28357832 28357832 28357832.0000

1666

flopping 018023 1 39654943 39654943 39654943 39654943.0000

1667

Judas 018032 1 28357832 28357832 28357832 28357832.0000

1668

vacuuming 018033 1 39654943 39654943 39654943 39654943.0000

1669

medical 018041 1 5987435 5987435 5987435 5987435.0000

1670

bloodbath 018042 1 28357832 28357832 28357832 28357832.0000

1671

subschema 018043 1 39654943 39654943 39654943 39654943.0000

1672

interdependent 018051 1 5987435 5987435 5987435 5987435.0000

1673

Graves 018052 1 28357832 28357832 28357832 28357832.0000

1674

neonatal 018053 1 39654943 39654943 39654943 39654943.0000

1675

sorters 018061 1 5987435 5987435 5987435 5987435.0000

1676

epistle 018062 1 28357832 28357832 28357832 28357832.0000

1677

Conley 018101 1 5987435 5987435 5987435 5987435.0000

1678

lectured 018102 1 28357832 28357832 28357832 28357832.0000

1679

Abraham 018103 1 39654943 39654943 39654943 39654943.0000

1680

cage 018201 1 5987435 5987435 5987435 5987435.0000

1681

hushes 018202 1 28357832 28357832 28357832 28357832.0000

1682

Simla 018402 1 28357832 28357832 28357832 28357832.0000

1683

reporters 018403 1 39654943 39654943 39654943 39654943.0000

1684

coexist 018601 1 5987435 5987435 5987435 5987435.0000

1685

Beebe 018602 1 28357832 28357832 28357832 28357832.0000

1686

Taoism 018603 1 39654943 39654943 39654943 39654943.0000

1687

Connally 018801 1 5987435 5987435 5987435 5987435.0000

1688

fetched 018802 1 28357832 28357832 28357832 28357832.0000

1689

checkpoints 018803 1 39654943 39654943 39654943 39654943.0000

1690

gritty 018811 1 5987435 5987435 5987435 5987435.0000

1691

firearm 018812 1 28357832 28357832 28357832 28357832.0000

1692

minima 019101 1 5987435 5987435 5987435 5987435.0000

1693

Selfridge 019102 1 28357832 28357832 28357832 28357832.0000

1694

disable 019103 1 39654943 39654943 39654943 39654943.0000

1695

witchcraft 019201 1 5987435 5987435 5987435 5987435.0000

1696

betroth 030501 1 5987435 5987435 5987435 5987435.0000

1697

Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000

1698

imprint 030503 1 39654943 39654943 39654943 39654943.0000

1699

swelling 031901 1 5987435 5987435 5987435 5987435.0000

1700

interrelationships 036001 1 5987435 5987435 5987435 5987435.0000

1701

riser 036002 1 28357832 28357832 28357832 28357832.0000

1702

bee 038001 1 5987435 5987435 5987435 5987435.0000

1703

kanji 038002 1 28357832 28357832 28357832 28357832.0000

1704

dental 038003 1 39654943 39654943 39654943 39654943.0000

1705

railway 038011 1 5987435 5987435 5987435 5987435.0000

1706

validate 038012 1 28357832 28357832 28357832 28357832.0000

1707

normalizes 038013 1 39654943 39654943 39654943 39654943.0000

1708

Kline 038101 1 5987435 5987435 5987435 5987435.0000

1709

Anatole 038102 1 28357832 28357832 28357832 28357832.0000

1710

partridges 038103 1 39654943 39654943 39654943 39654943.0000

1711

recruited 038201 1 5987435 5987435 5987435 5987435.0000

1712

dimensions 038202 1 28357832 28357832 28357832 28357832.0000

1713

Chicana 038203 1 39654943 39654943 39654943 39654943.0000

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

1714

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

1715

companynr fld3 sum(price)

1716

512 boat 786542

1725

512 skies 786542

1726

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

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

1728

00 1 Omaha Omaha 5987435 5987435.0000

1728

0 1 Omaha Omaha 5987435 5987435.0000

1729

36 1 dubbed dubbed 28357832 28357832.0000

1730

37 83 Abraham Wotan 1908978016 22999735.1325

1731

50 2 scribbled tapestry 68012775 34006387.5000

1823

234298

1824

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

fld1 sum(price)

1826

038008 234298

1826

38008 234298

1827

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

1828

companynr fld1

1829

34 250501

1834

34 250502

1835

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

1836

companynr count sum

1837

00 82 10355753

1837

0 82 10355753

1838

29 95 14473298

1839

34 70 17788966

1840

37 588 83602098

1841

41 52 12816335

1842

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

1843

companynr

1844

1845

1846

1847

1940

Tables_in_test (t?)

1941

show full columns from t2;

1942

Field Type Collation Null Key Default Extra Privileges Comment

1943

auto int(11) NULL NO PRI NULL auto_increment #

1944

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

1945

companynr tinyint(2) unsigned zerofill NULL NO NULL #

1946

fld3 char(30) latin1_swedish_ci NO MUL NULL #

1947

fld4 char(35) latin1_swedish_ci NO NULL #

1948

fld5 char(35) latin1_swedish_ci NO NULL #

1949

fld6 char(4) latin1_swedish_ci NO NULL #

1943

auto int NULL NO PRI NULL auto_increment #

1944

fld1 int unsigned NULL NO UNI NULL #

1945

companynr tinyint(3) unsigned NULL NO NULL #

1946

fld3 varchar(30) latin1_swedish_ci NO MUL NULL #

1947

fld4 varchar(35) latin1_swedish_ci NO NULL #

1948

fld5 varchar(35) latin1_swedish_ci NO NULL #

1949

fld6 varchar(4) latin1_swedish_ci NO NULL #

1950

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

1951

Field Type Collation Null Key Default Extra Privileges Comment

1952

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

1953

fld3 char(30) latin1_swedish_ci NO MUL NULL #

1954

fld4 char(35) latin1_swedish_ci NO NULL #

1955

fld5 char(35) latin1_swedish_ci NO NULL #

1956

fld6 char(4) latin1_swedish_ci NO NULL #

1952

fld1 int unsigned NULL NO UNI NULL #

1953

fld3 varchar(30) latin1_swedish_ci NO MUL NULL #

1954

fld4 varchar(35) latin1_swedish_ci NO NULL #

1955

fld5 varchar(35) latin1_swedish_ci NO NULL #

1956

fld6 varchar(4) latin1_swedish_ci NO NULL #

1957

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

1958

Field Type Collation Null Key Default Extra Privileges Comment

1959

drop table t4, t3, t2, t1;

1960

CREATE TABLE t1 (

1961

id bigint(8) unsigned NOT NULL auto_increment,

1961

id bigint unsigned NOT NULL auto_increment,

1962

pseudo varchar(35) NOT NULL default '',

1963

PRIMARY KEY (id),

1964

UNIQUE KEY pseudo (pseudo)

1968

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

1969

rnd1

1970

DROP TABLE t1;

1971

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;

1971

CREATE TABLE t1 (gvid int unsigned default NULL, hmid int unsigned default NULL, volid int unsigned default NULL, mmid int unsigned default NULL, hdid int unsigned default NULL, fsid int unsigned default NULL, ctid int unsigned default NULL, dtid int unsigned default NULL, cost int unsigned default NULL, performance int unsigned default NULL, serialnumber bigint unsigned default NULL, monitored tinyint unsigned default '1', removed tinyint unsigned default '0', target tinyint 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

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

1973

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

1974

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

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

gvid the_success the_fail the_size the_time

1980

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

1981

gvid the_success the_fail the_size the_time

1982

DROP TABLE t1,t2;

1983

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

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

1984

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

1985

select wss_type from t1 where wss_type ='102935229216544106';

1986

wss_type

2164

2165

2166

drop table t1;

2167

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;

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;

2168

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(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

2169

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

2170

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

2171

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

2172

aa id t2_id id

2223

4 2 4 4

2224

drop table t1, t2;

2225

CREATE TABLE t1 (

2226

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

2226

i int NOT NULL default '0',

2227

c char(10) NOT NULL default '',

2228

PRIMARY KEY (i),

2229

UNIQUE KEY c (c)

2270

city

2271

London

2272

DROP TABLE t1;

2273

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

2273

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

2274

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

2275

select a-b from t1 order by 1;

2276

a-b

2293

2294

18446744073709551615

2295

drop table t1;

2296

create table t1 (a int(11));

2296

create table t1 (a int);

2297

select all all * from t1;

2298

2299

select distinct distinct * from t1;

2304

ERROR HY000: Incorrect usage of ALL and DISTINCT

2305

drop table t1;

2306

CREATE TABLE t1 (

2307

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

2308

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

2309

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

2307

kunde_intern_id int unsigned NOT NULL default '0',

2308

kunde_id int unsigned NOT NULL default '0',

2309

FK_firma_id int unsigned NOT NULL default '0',

2310

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

2311

vorname varchar(128) NOT NULL default '',

2312

nachname varchar(128) NOT NULL default '',

2357

COUNT(*)

2358

2359

drop table t1;

2360

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

2360

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

2361

INSERT INTO t1 VALUES (0x8000000000000000);

2362

SELECT b FROM t1 WHERE b=0x8000000000000000;

2363

2364

9223372036854775808

2365

DROP TABLE t1;

2366

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

2367

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

2366

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

2367

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

2368

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

2369

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

2369

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

2370

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

2371

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

2372

id name gid uid ident level

2373

1 fs NULL NULL 0 READ

2374

drop table t1,t2,t3;

2375

CREATE TABLE t1 (

2376

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

2377

profile_id smallint(6) default NULL,

2376

acct_id int NOT NULL default '0',

2377

profile_id smallint default NULL,

2378

UNIQUE KEY t1$acct_id (acct_id),

2379

KEY t1$profile_id (profile_id)

2380

);

2381

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

2382

CREATE TABLE t2 (

2383

profile_id smallint(6) default NULL,

2384

queue_id int(11) default NULL,

2385

seq int(11) default NULL,

2383

profile_id smallint default NULL,

2384

queue_id int default NULL,

2385

seq int default NULL,

2386

KEY t2$queue_id (queue_id)

2387

);

2388

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

2389

CREATE TABLE t3 (

2390

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

2391

qtype int(11) default NULL,

2392

seq int(11) default NULL,

2393

warn_lvl int(11) default NULL,

2394

crit_lvl int(11) default NULL,

2395

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

2396

rr2 int(11) default NULL,

2397

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

2390

id int NOT NULL default '0',

2391

qtype int default NULL,

2392

seq int default NULL,

2393

warn_lvl int default NULL,

2394

crit_lvl int default NULL,

2395

rr1 tinyint NOT NULL default '0',

2396

rr2 int default NULL,

2397

default_queue tinyint NOT NULL default '0',

2398

KEY t3$qtype (qtype),

2399

KEY t3$id (id)

2400

);

2603

CREATE TABLE t1 (

2604

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

2605

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

2606

F2I4 int(11) NOT NULL default '0'

2606

F2I4 int NOT NULL default '0'

2607

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2608

INSERT INTO t1 VALUES

2609

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

2683

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

2684

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

2685

16 16 2 2

2686

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

2686

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

2687

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

2688

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

2689

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

2697

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

2698

2699

drop table t1,t2;

2700

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

2701

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

2702

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

2700

create table t1 (a int, t1_val int);

2701

create table t2 (b int, t2_val int);

2702

create table t3 (a int, b int);

2703

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

2704

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

2705

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

2739

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

2740

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

2741

drop table t1, t2, t3;

2742

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

2743

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

2742

create table t1 (a int,b int);

2743

create table t2 (a int,b int);

2744

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

2745

insert into t2 values (1,10);

2746

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

2787

4 4

2788

4 5

2789

drop table t1,t2,t3;

2790

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

2790

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

2791

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

2792

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

2793

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

2794

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

2794

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

2795

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

2796

select count(*)

2797

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

3134

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

3135

id select_type table type possible_keys key key_len ref rows Extra

3136

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3137

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

3137

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

3138

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

3139

EXPLAIN

3140

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

3516

ERROR HY000: Too high level of nesting for select

3517

DROP TABLE t1;

3518

CREATE TABLE t1 (

3519

c1 int(11) NOT NULL AUTO_INCREMENT,

3519

c1 int NOT NULL AUTO_INCREMENT,

3520

c2 varchar(1000) DEFAULT NULL,

3521

c3 bigint(20) DEFAULT NULL,

3522

c4 bigint(20) DEFAULT NULL,

3521

c3 bigint DEFAULT NULL,

3522

c4 bigint DEFAULT NULL,

3523

PRIMARY KEY (c1)

3524

);

3525

EXPLAIN EXTENDED

Older »