← Back to branch summary

~drizzle-trunk/drizzle/development

496.1.8 by Paul McCullagh
Added PBXT specific result files 
1
 
drop table if exists t1,t2,t3,t4,t11;

2
 
drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa;

3
 
CREATE TABLE t1 (

4
 
Period int DEFAULT '0' NOT NULL,

5
 
Varor_period int DEFAULT '0' NOT NULL

6
 
);

7
 
INSERT INTO t1 VALUES (9410,9412);

8
 
select period from t1;

9
 
period

10
 
9410

11
 
select * from t1;

12
 
Period	Varor_period

13
 
9410	9412

14
 
select t1.* from t1;

15
 
Period	Varor_period

16
 
9410	9412

17
 
CREATE TABLE t2 (

18
 
auto int not null auto_increment,

19
 
fld1 int DEFAULT '0' NOT NULL,

20
 
companynr int DEFAULT '0' NOT NULL,

21
 
fld3 char(30) DEFAULT '' NOT NULL,

22
 
fld4 char(35) DEFAULT '' NOT NULL,

23
 
fld5 char(35) DEFAULT '' NOT NULL,

24
 
fld6 char(4) DEFAULT '' NOT NULL,

25
 
UNIQUE fld1 (fld1),

26
 
KEY fld3 (fld3),

27
 
PRIMARY KEY (auto)

28
 
);

29
 
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";

30
 
fld3

31
 
imaginable

32
 
select fld3 from t2 where fld3 like "%cultivation" ;

33
 
fld3

34
 
cultivation

35
 
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;

36
 
fld3	companynr

37
 
concoct	58

38
 
druggists	58

39
 
engrossing	58

40
 
Eurydice	58

41
 
exclaimers	58

42
 
ferociousness	58

43
 
hopelessness	58

44
 
Huey	58

45
 
imaginable	58

46
 
judges	58

47
 
merging	58

48
 
ostrich	58

49
 
peering	58

50
 
Phelps	58

51
 
presumes	58

52
 
Ruth	58

53
 
sentences	58

54
 
Shylock	58

55
 
straggled	58

56
 
synergy	58

57
 
thanking	58

58
 
tying	58

59
 
unlocks	58

60
 
select fld3,companynr from t2 where companynr = 58 order by fld3;

61
 
fld3	companynr

62
 
concoct	58

63
 
druggists	58

64
 
engrossing	58

65
 
Eurydice	58

66
 
exclaimers	58

67
 
ferociousness	58

68
 
hopelessness	58

69
 
Huey	58

70
 
imaginable	58

71
 
judges	58

72
 
merging	58

73
 
ostrich	58

74
 
peering	58

75
 
Phelps	58

76
 
presumes	58

77
 
Ruth	58

78
 
sentences	58

79
 
Shylock	58

80
 
straggled	58

81
 
synergy	58

82
 
thanking	58

83
 
tying	58

84
 
unlocks	58

85
 
select fld3 from t2 order by fld3 desc limit 10;

86
 
fld3

87
 
youthfulness

88
 
yelped

89
 
Wotan

90
 
workers

91
 
Witt

92
 
witchcraft

93
 
Winsett

94
 
Willy

95
 
willed

96
 
wildcats

97
 
select fld3 from t2 order by fld3 desc limit 5;

98
 
fld3

99
 
youthfulness

100
 
yelped

101
 
Wotan

102
 
workers

103
 
Witt

104
 
select fld3 from t2 order by fld3 desc limit 5,5;

105
 
fld3

106
 
witchcraft

107
 
Winsett

108
 
Willy

109
 
willed

110
 
wildcats

111
 
select t2.fld3 from t2 where fld3 = 'honeysuckle';

112
 
fld3

113
 
honeysuckle

114
 
select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';

115
 
fld3

116
 
honeysuckle

117
 
select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';

118
 
fld3

119
 
honeysuckle

120
 
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';

121
 
fld3

122
 
honeysuckle

123
 
select t2.fld3 from t2 where fld3 LIKE 'h%le';

124
 
fld3

125
 
honeysuckle

126
 
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';

127
 
fld3

128
 
select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';

129
 
fld3

130
 
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;

131
 
fld3

132
 
honeysuckle

133
 
honoring

134
 
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;

135
 
fld1	fld3

136
 
148504	Colombo

137
 
68305	Colombo

138
 
0	nondecreasing

139
 
select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';

140
 
fld1	fld3

141
 
232605	appendixes

142
 
1232605	appendixes

143
 
1232606	appendixes

144
 
1232607	appendixes

145
 
1232608	appendixes

146
 
1232609	appendixes

147
 
select fld1 from t2 where fld1=250501 or fld1="250502";

148
 
fld1

149
 
250501

150
 
250502

151
 
select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;

152
 
fld1

153
 
250501

154
 
250502

155
 
250505

156
 
250601

157
 
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';

158
 
fld1	fld3

159
 
12001	flanking

160
 
13602	foldout

161
 
13606	fingerings

162
 
18007	fanatic

163
 
18017	featherweight

164
 
18054	fetters

165
 
18103	flint

166
 
18104	flopping

167
 
186002	freakish

168
 
188007	flurried

169
 
188505	fitting

170
 
198006	furthermore

171
 
202301	Fitzpatrick

172
 
208101	fiftieth

173
 
208113	freest

174
 
218008	finishers

175
 
218022	feed

176
 
218401	faithful

177
 
226205	foothill

178
 
226209	furnishings

179
 
228306	forthcoming

180
 
228311	fated

181
 
231315	freezes

182
 
232102	forgivably

183
 
238007	filial

184
 
238008	fixedly

185
 
36002	funereal

186
 
38017	fetched

187
 
38205	firearm

188
 
58004	Fenton

189
 
88303	feminine

190
 
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";

191
 
fld3

192
 
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");

193
 
fld3

194
 
Chantilly

195
 
select fld1,fld3 from t2 where fld1 like "25050%";

196
 
fld1	fld3

197
 
250501	poisoning

198
 
250502	Iraqis

199
 
250503	heaving

200
 
250504	population

201
 
250505	bomb

202
 
select fld1,fld3 from t2 where fld1 like "25050_";

203
 
fld1	fld3

204
 
250501	poisoning

205
 
250502	Iraqis

206
 
250503	heaving

207
 
250504	population

208
 
250505	bomb

209
 
select distinct companynr from t2;

210
 
companynr

211
 
0

212
 
37

213
 
36

214
 
50

215
 
58

216
 
29

217
 
40

218
 
53

219
 
65

220
 
41

221
 
34

222
 
68

223
 
select distinct companynr from t2 order by companynr;

224
 
companynr

225
 
0

226
 
29

227
 
34

228
 
36

229
 
37

230
 
40

231
 
41

232
 
50

233
 
53

234
 
58

235
 
65

236
 
68

237
 
select distinct companynr from t2 order by companynr desc;

238
 
companynr

239
 
68

240
 
65

241
 
58

242
 
53

243
 
50

244
 
41

245
 
40

246
 
37

247
 
36

248
 
34

249
 
29

250
 
0

251
 
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";

252
 
fld3	period

253
 
obliterates	9410

254
 
offload	9410

255
 
opaquely	9410

256
 
organizer	9410

257
 
overestimating	9410

258
 
overlay	9410

259
 
select distinct fld3 from t2 where companynr = 34 order by fld3;

260
 
fld3

261
 
absentee

262
 
accessed

263
 
ahead

264
 
alphabetic

265
 
Asiaticizations

266
 
attitude

267
 
aye

268
 
bankruptcies

269
 
belays

270
 
Blythe

271
 
bomb

272
 
boulevard

273
 
bulldozes

274
 
cannot

275
 
caressing

276
 
charcoal

277
 
checksumming

278
 
chess

279
 
clubroom

280
 
colorful

281
 
cosy

282
 
creator

283
 
crying

284
 
Darius

285
 
diffusing

286
 
duality

287
 
Eiffel

288
 
Epiphany

289
 
Ernestine

290
 
explorers

291
 
exterminated

292
 
famine

293
 
forked

294
 
Gershwins

295
 
heaving

296
 
Hodges

297
 
Iraqis

298
 
Italianization

299
 
Lagos

300
 
landslide

301
 
libretto

302
 
Majorca

303
 
mastering

304
 
narrowed

305
 
occurred

306
 
offerers

307
 
Palestine

308
 
Peruvianizes

309
 
pharmaceutic

310
 
poisoning

311
 
population

312
 
Pygmalion

313
 
rats

314
 
realest

315
 
recording

316
 
regimented

317
 
retransmitting

318
 
reviver

319
 
rouses

320
 
scars

321
 
sicker

322
 
sleepwalk

323
 
stopped

324
 
sugars

325
 
translatable

326
 
uncles

327
 
unexpected

328
 
uprisings

329
 
versatility

330
 
vest

331
 
select distinct fld3 from t2 limit 10;

332
 
fld3

333
 
Abraham

334
 
abates

335
 
abiding

336
 
abrogating

337
 
absentee

338
 
abut

339
 
accessed

340
 
accruing

341
 
accumulating

342
 
accuracies

343
 
select distinct fld3 from t2 having fld3 like "A%" limit 10;

344
 
fld3

345
 
abates

346
 
abiding

347
 
Abraham

348
 
abrogating

349
 
absentee

350
 
abut

351
 
accessed

352
 
accruing

353
 
accumulating

354
 
accuracies

355
 
select distinct substring(fld3,1,3) from t2 where fld3 like "A%";

356
 
substring(fld3,1,3)

357
 
aba

358
 
abi

359
 
Abr

360
 
abs

361
 
abu

362
 
acc

363
 
acq

364
 
acu

365
 
Ade

366
 
adj

367
 
Adl

368
 
adm

369
 
Ado

370
 
ads

371
 
adv

372
 
aer

373
 
aff

374
 
afi

375
 
afl

376
 
afo

377
 
agi

378
 
ahe

379
 
aim

380
 
air

381
 
Ald

382
 
alg

383
 
ali

384
 
all

385
 
alp

386
 
alr

387
 
ama

388
 
ame

389
 
amm

390
 
ana

391
 
and

392
 
ane

393
 
Ang

394
 
ani

395
 
Ann

396
 
Ant

397
 
api

398
 
app

399
 
aqu

400
 
Ara

401
 
arc

402
 
Arm

403
 
arr

404
 
Art

405
 
Asi

406
 
ask

407
 
asp

408
 
ass

409
 
ast

410
 
att

411
 
aud

412
 
Aug

413
 
aut

414
 
ave

415
 
avo

416
 
awe

417
 
aye

418
 
Azt

419
 
select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10;

420
 
a

421
 
aba

422
 
abi

423
 
Abr

424
 
abs

425
 
abu

426
 
acc

427
 
acq

428
 
acu

429
 
Ade

430
 
adj

431
 
select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;

432
 
substring(fld3,1,3)

433
 
aba

434
 
abi

435
 
Abr

436
 
abs

437
 
abu

438
 
acc

439
 
acq

440
 
acu

441
 
Ade

442
 
adj

443
 
select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;

444
 
a

445
 
Abr

446
 
Ade

447
 
aba

448
 
abi

449
 
abs

450
 
abu

451
 
acc

452
 
acq

453
 
acu

454
 
adj

455
 
create table t3 (

456
 
period    int not null,

457
 
name      char(32) not null,

458
 
companynr int not null,

459
 
price     double(11,0),

460
 
price2     double(11,0),

461
 
key (period),

462
 
key (name)

463
 
);

464
 
create temporary table tmp engine = myisam select * from t3;

465
 
insert into t3 select * from tmp;

466
 
insert into tmp select * from t3;

467
 
insert into t3 select * from tmp;

468
 
insert into tmp select * from t3;

469
 
insert into t3 select * from tmp;

470
 
insert into tmp select * from t3;

471
 
insert into t3 select * from tmp;

472
 
insert into tmp select * from t3;

473
 
insert into t3 select * from tmp;

474
 
insert into tmp select * from t3;

475
 
insert into t3 select * from tmp;

476
 
insert into tmp select * from t3;

477
 
insert into t3 select * from tmp;

478
 
insert into tmp select * from t3;

479
 
insert into t3 select * from tmp;

480
 
insert into tmp select * from t3;

481
 
insert into t3 select * from tmp;

482
 
alter table t3 add t2nr int not null auto_increment primary key first;

483
 
drop table tmp;

484
 
SET SQL_BIG_TABLES=1;

485
 
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;

486
 
namn

487
 
Abraham Abraham

488
 
abrogating abrogating

489
 
admonishing admonishing

490
 
Adolph Adolph

491
 
afield afield

492
 
aging aging

493
 
ammonium ammonium

494
 
analyzable analyzable

495
 
animals animals

496
 
animized animized

497
 
SET SQL_BIG_TABLES=0;

498
 
select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;

499
 
concat(fld3," ",fld3)

500
 
Abraham Abraham

501
 
abrogating abrogating

502
 
admonishing admonishing

503
 
Adolph Adolph

504
 
afield afield

505
 
aging aging

506
 
ammonium ammonium

507
 
analyzable analyzable

508
 
animals animals

509
 
animized animized

510
 
select distinct fld5 from t2 limit 10;

511
 
fld5

512
 
neat

513
 
Steinberg

514
 
jarring

515
 
tinily

516
 
balled

517
 
persist

518
 
attainments

519
 
fanatic

520
 
measures

521
 
rightfulness

522
 
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;

523
 
fld3	count(*)

524
 
affixed	1

525
 
and	1

526
 
annoyers	1

527
 
Anthony	1

528
 
assayed	1

529
 
assurers	1

530
 
attendants	1

531
 
bedlam	1

532
 
bedpost	1

533
 
boasted	1

534
 
SET SQL_BIG_TABLES=1;

535
 
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;

536
 
fld3	count(*)

537
 
affixed	1

538
 
and	1

539
 
annoyers	1

540
 
Anthony	1

541
 
assayed	1

542
 
assurers	1

543
 
attendants	1

544
 
bedlam	1

545
 
bedpost	1

546
 
boasted	1

547
 
SET SQL_BIG_TABLES=0;

548
 
select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;

549
 
fld3	repeat("a",length(fld3))	count(*)

550
 
circus	aaaaaa	1

551
 
cited	aaaaa	1

552
 
Colombo	aaaaaaa	1

553
 
congresswoman	aaaaaaaaaaaaa	1

554
 
contrition	aaaaaaaaaa	1

555
 
corny	aaaaa	1

556
 
cultivation	aaaaaaaaaaa	1

557
 
definiteness	aaaaaaaaaaaa	1

558
 
demultiplex	aaaaaaaaaaa	1

559
 
disappointing	aaaaaaaaaaaaa	1

560
 
select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;

561
 
companynr	rtrim(space(512+companynr))

562
 
37	

563
 
78	

564
 
101	

565
 
154	

566
 
311	

567
 
447	

568
 
512	

569
 
select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;

570
 
fld3

571
 
select period from t1;

572
 
period

573
 
9410

574
 
select period from t1 where period=1900;

575
 
period

576
 
select fld3,period from t1,t2 where fld1 = 011401 order by period;

577
 
fld3	period

578
 
breaking	9410

579
 
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;

580
 
fld3	period

581
 
breaking	1001

582
 
select fld3,period from t2,t1 where companynr*10 = 37*10;

583
 
fld3	period

584
 
Abraham	9410

585
 
Aden	9410

586
 
Adolph	9410

587
 
Aldrich	9410

588
 
Alison	9410

589
 
Anatole	9410

590
 
Antarctica	9410

591
 
Antares	9410

592
 
Arabia	9410

593
 
Artemia	9410

594
 
Augustine	9410

595
 
Baird	9410

596
 
Beebe	9410

597
 
Butterfield	9410

598
 
CERN	9410

599
 
Cassites	9410

600
 
Chicana	9410

601
 
Chippewa	9410

602
 
Clayton	9410

603
 
Conley	9410

604
 
Connally	9410

605
 
Crays	9410

606
 
DiMaggio	9410

607
 
Dutchman	9410

608
 
Eulerian	9410

609
 
Evanston	9410

610
 
Everhart	9410

611
 
Fenton	9410

612
 
Fitzpatrick	9410

613
 
Galatean	9410

614
 
Gandhian	9410

615
 
Ganymede	9410

616
 
Goldstine	9410

617
 
Gothicism	9410

618
 
Graves	9410

619
 
Greenberg	9410

620
 
Gurkha	9410

621
 
Hawaii	9410

622
 
Hegelian	9410

623
 
Hornblower	9410

624
 
Huffman	9410

625
 
Hunter	9410

626
 
Joplin	9410

627
 
Judas	9410

628
 
Kane	9410

629
 
Kantian	9410

630
 
Kevin	9410

631
 
Kinsey	9410

632
 
Kline	9410

633
 
Lars	9410

634
 
Latinizes	9410

635
 
Lillian	9410

636
 
Lizzy	9410

637
 
Majorca	9410

638
 
Manhattanize	9410

639
 
McGovern	9410

640
 
Melinda	9410

641
 
Merritt	9410

642
 
Micronesia	9410

643
 
Miles	9410

644
 
Miltonism	9410

645
 
Nabisco	9410

646
 
Nazis	9410

647
 
Newtonian	9410

648
 
Norwalk	9410

649
 
Pandora	9410

650
 
Parsifal	9410

651
 
Peruvian	9410

652
 
Punjab	9410

653
 
Pyle	9410

654
 
Quixotism	9410

655
 
Romano	9410

656
 
Romans	9410

657
 
Sabine	9410

658
 
Sault	9410

659
 
Saxony	9410

660
 
Selfridge	9410

661
 
Shanghais	9410

662
 
Simla	9410

663
 
Simon	9410

664
 
Stalin	9410

665
 
Steinberg	9410

666
 
Taoism	9410

667
 
Teresa	9410

668
 
Tipperary	9410

669
 
Weissmuller	9410

670
 
Winsett	9410

671
 
Wotan	9410

672
 
abates	9410

673
 
abrogating	9410

674
 
accessed	9410

675
 
admiring	9410

676
 
admonishing	9410

677
 
afield	9410

678
 
afore	9410

679
 
aging	9410

680
 
airships	9410

681
 
alike	9410

682
 
allot	9410

683
 
already	9410

684
 
amenities	9410

685
 
ammonium	9410

686
 
analogy	9410

687
 
analyzable	9410

688
 
animals	9410

689
 
animized	9410

690
 
annihilates	9410

691
 
announced	9410

692
 
announces	9410

693
 
apiary	9410

694
 
appendixes	9410

695
 
appendixes	9410

696
 
appendixes	9410

697
 
appendixes	9410

698
 
appendixes	9410

699
 
appendixes	9410

700
 
arriving	9410

701
 
arteriole	9410

702
 
assails	9410

703
 
astound	9410

704
 
attainments	9410

705
 
attrition	9410

706
 
audiology	9410

707
 
avenge	9410

708
 
avoidable	9410

709
 
babies	9410

710
 
babysitting	9410

711
 
balled	9410

712
 
beaner	9410

713
 
beaters	9410

714
 
bee	9410

715
 
befouled	9410

716
 
bellow	9410

717
 
bestseller	9410

718
 
betroth	9410

719
 
bewilderingly	9410

720
 
bills	9410

721
 
bitterroot	9410

722
 
bivalves	9410

723
 
bloater	9410

724
 
bloodbath	9410

725
 
boat	9410

726
 
boom	9410

727
 
boorish	9410

728
 
boulder	9410

729
 
breaking	9410

730
 
brunch	9410

731
 
buckboards	9410

732
 
burlesque	9410

733
 
cage	9410

734
 
capably	9410

735
 
capped	9410

736
 
cascade	9410

737
 
causality	9410

738
 
cautioned	9410

739
 
ceiling	9410

740
 
celery	9410

741
 
certificates	9410

742
 
chafe	9410

743
 
chaperone	9410

744
 
charges	9410

745
 
chasm	9410

746
 
checkpoints	9410

747
 
chewing	9410

748
 
chews	9410

749
 
chillingly	9410

750
 
chronicle	9410

751
 
ciphers	9410

752
 
civics	9410

753
 
clamored	9410

754
 
clenched	9410

755
 
clockers	9410

756
 
coexist	9410

757
 
cokes	9410

758
 
combed	9410

759
 
coming	9410

760
 
commencements	9410

761
 
commonplace	9410

762
 
communicants	9410

763
 
compartment	9410

764
 
comprehensive	9410

765
 
comprised	9410

766
 
conceptions	9410

767
 
concludes	9410

768
 
congregates	9410

769
 
contrary	9410

770
 
contrasted	9410

771
 
convenient	9410

772
 
convulsion	9410

773
 
corset	9410

774
 
count	9410

775
 
coverings	9410

776
 
craziness	9410

777
 
creak	9410

778
 
creek	9410

779
 
critiques	9410

780
 
crunches	9410

781
 
culled	9410

782
 
cult	9410

783
 
cupboard	9410

784
 
cured	9410

785
 
cute	9410

786
 
daughter	9410

787
 
decliner	9410

788
 
decomposition	9410

789
 
deductions	9410

790
 
dehydrate	9410

791
 
deludes	9410

792
 
denizen	9410

793
 
denotative	9410

794
 
denounces	9410

795
 
dental	9410

796
 
dentally	9410

797
 
descendants	9410

798
 
despot	9410

799
 
destroyer	9410

800
 
detectably	9410

801
 
dialysis	9410

802
 
dimensions	9410

803
 
disable	9410

804
 
discounts	9410

805
 
disentangle	9410

806
 
disobedience	9410

807
 
dissociate	9410

808
 
dogging	9410

809
 
dopers	9410

810
 
drains	9410

811
 
dreaded	9410

812
 
ducks	9410

813
 
dusted	9410

814
 
effortlessly	9410

815
 
electroencephalography	9410

816
 
elite	9410

817
 
embassies	9410

818
 
employing	9410

819
 
encompass	9410

820
 
encompasses	9410

821
 
environing	9410

822
 
epistle	9410

823
 
equilibrium	9410

824
 
erases	9410

825
 
error	9410

826
 
eschew	9410

827
 
eternal	9410

828
 
evened	9410

829
 
evenhandedly	9410

830
 
eventful	9410

831
 
excises	9410

832
 
exclamation	9410

833
 
excrete	9410

834
 
exhausts	9410

835
 
expelled	9410

836
 
extents	9410

837
 
externally	9410

838
 
extracted	9410

839
 
faithful	9410

840
 
fanatic	9410

841
 
fated	9410

842
 
featherweight	9410

843
 
feed	9410

844
 
feminine	9410

845
 
fetched	9410

846
 
fetters	9410

847
 
fiftieth	9410

848
 
filial	9410

849
 
fingerings	9410

850
 
finishers	9410

851
 
firearm	9410

852
 
fitting	9410

853
 
fixedly	9410

854
 
flanking	9410

855
 
flint	9410

856
 
flopping	9410

857
 
flurried	9410

858
 
foldout	9410

859
 
foothill	9410

860
 
forgivably	9410

861
 
forthcoming	9410

862
 
freakish	9410

863
 
freest	9410

864
 
freezes	9410

865
 
funereal	9410

866
 
furnishings	9410

867
 
furthermore	9410

868
 
gadfly	9410

869
 
gainful	9410

870
 
galling	9410

871
 
garage	9410

872
 
gentleman	9410

873
 
gifted	9410

874
 
gleaning	9410

875
 
glut	9410

876
 
goblins	9410

877
 
governing	9410

878
 
gradually	9410

879
 
grazing	9410

880
 
gritty	9410

881
 
groupings	9410

882
 
guides	9410

883
 
guitars	9410

884
 
handgun	9410

885
 
handy	9410

886
 
heiress	9410

887
 
hoarder	9410

888
 
honoring	9410

889
 
hostess	9410

890
 
humanness	9410

891
 
humiliation	9410

892
 
humility	9410

893
 
hushes	9410

894
 
husky	9410

895
 
hypothesizer	9410

896
 
icon	9410

897
 
ideas	9410

898
 
impelling	9410

899
 
impending	9410

900
 
imperial	9410

901
 
imperiously	9410

902
 
imprint	9410

903
 
impulsive	9410

904
 
inaccuracy	9410

905
 
inch	9410

906
 
incidentals	9410

907
 
incorrectly	9410

908
 
incurring	9410

909
 
index	9410

910
 
indulge	9410

911
 
indulgences	9410

912
 
ineffective	9410

913
 
infallibly	9410

914
 
infest	9410

915
 
inform	9410

916
 
inmate	9410

917
 
insolence	9410

918
 
instruments	9410

919
 
intelligibility	9410

920
 
intentness	9410

921
 
intercepted	9410

922
 
interdependent	9410

923
 
interrelationships	9410

924
 
interrogate	9410

925
 
investigations	9410

926
 
irresponsibly	9410

927
 
jarring	9410

928
 
journalizing	9410

929
 
juveniles	9410

930
 
kanji	9410

931
 
kingdom	9410

932
 
kiting	9410

933
 
labeled	9410

934
 
languages	9410

935
 
laterally	9410

936
 
lawgiver	9410

937
 
leaflet	9410

938
 
leavings	9410

939
 
lectured	9410

940
 
leftover	9410

941
 
lewdly	9410

942
 
lied	9410

943
 
linear	9410

944
 
lists	9410

945
 
lithograph	9410

946
 
lore	9410

947
 
luckily	9410

948
 
males	9410

949
 
marginal	9410

950
 
mastering	9410

951
 
mayoral	9410

952
 
meanwhile	9410

953
 
measures	9410

954
 
measures	9410

955
 
mechanizing	9410

956
 
medical	9410

957
 
meditation	9410

958
 
metaphysically	9410

959
 
mineral	9410

960
 
miniaturizes	9410

961
 
minima	9410

962
 
minion	9410

963
 
minting	9410

964
 
misted	9410

965
 
misunderstander	9410

966
 
mixture	9410

967
 
motors	9410

968
 
mournfulness	9410

969
 
multilayer	9410

970
 
mumbles	9410

971
 
mushrooms	9410

972
 
mystic	9410

973
 
navies	9410

974
 
navigate	9410

975
 
neat	9410

976
 
neonatal	9410

977
 
nested	9410

978
 
noncritical	9410

979
 
normalizes	9410

980
 
obliterates	9410

981
 
offload	9410

982
 
opaquely	9410

983
 
organizer	9410

984
 
overestimating	9410

985
 
overlay	9410

986
 
parametrized	9410

987
 
parenthood	9410

988
 
parters	9410

989
 
participated	9410

990
 
partridges	9410

991
 
peacock	9410

992
 
peeked	9410

993
 
pellagra	9410

994
 
percentage	9410

995
 
percentage	9410

996
 
persist	9410

997
 
perturb	9410

998
 
pessimist	9410

999
 
pests	9410

1000
 
petted	9410

1001
 
pictures	9410

1002
 
pithed	9410

1003
 
pityingly	9410

1004
 
poison	9410

1005
 
posed	9410

1006
 
positioning	9410

1007
 
postulation	9410

1008
 
praised	9410

1009
 
precaution	9410

1010
 
precipitable	9410

1011
 
preclude	9410

1012
 
presentation	9410

1013
 
pressure	9410

1014
 
previewing	9410

1015
 
priceless	9410

1016
 
primary	9410

1017
 
psychic	9410

1018
 
publicly	9410

1019
 
puddings	9410

1020
 
quagmire	9410

1021
 
quitter	9410

1022
 
railway	9410

1023
 
raining	9410

1024
 
rains	9410

1025
 
ravines	9410

1026
 
readable	9410

1027
 
realized	9410

1028
 
realtor	9410

1029
 
reassigned	9410

1030
 
recruited	9410

1031
 
reduce	9410

1032
 
regimented	9410

1033
 
registration	9410

1034
 
relatively	9410

1035
 
relaxing	9410

1036
 
relishing	9410

1037
 
relives	9410

1038
 
renew	9410

1039
 
repelled	9410

1040
 
repetitions	9410

1041
 
reporters	9410

1042
 
reporters	9410

1043
 
repressions	9410

1044
 
resplendent	9410

1045
 
resumes	9410

1046
 
rifles	9410

1047
 
rightful	9410

1048
 
rightfully	9410

1049
 
rightfulness	9410

1050
 
ripeness	9410

1051
 
riser	9410

1052
 
roped	9410

1053
 
rudeness	9410

1054
 
rules	9410

1055
 
rural	9410

1056
 
rusting	9410

1057
 
sadly	9410

1058
 
sags	9410

1059
 
sanding	9410

1060
 
saplings	9410

1061
 
sating	9410

1062
 
save	9410

1063
 
sawtooth	9410

1064
 
scarf	9410

1065
 
scatterbrain	9410

1066
 
scheduling	9410

1067
 
schemer	9410

1068
 
scholastics	9410

1069
 
scornfully	9410

1070
 
secures	9410

1071
 
securing	9410

1072
 
seminaries	9410

1073
 
serializations	9410

1074
 
serpents	9410

1075
 
serving	9410

1076
 
severely	9410

1077
 
sews	9410

1078
 
shapelessly	9410

1079
 
shipyard	9410

1080
 
shooter	9410

1081
 
similarities	9410

1082
 
skulking	9410

1083
 
slaughter	9410

1084
 
sloping	9410

1085
 
smoothed	9410

1086
 
snatching	9410

1087
 
socializes	9410

1088
 
sophomore	9410

1089
 
sorters	9410

1090
 
spatial	9410

1091
 
specification	9410

1092
 
specifics	9410

1093
 
spongers	9410

1094
 
spools	9410

1095
 
sportswriting	9410

1096
 
sporty	9410

1097
 
squabbled	9410

1098
 
squeaking	9410

1099
 
squeezes	9410

1100
 
stabilizes	9410

1101
 
stairway	9410

1102
 
standardizes	9410

1103
 
star	9410

1104
 
starlet	9410

1105
 
stated	9410

1106
 
stint	9410

1107
 
stodgy	9410

1108
 
store	9410

1109
 
straight	9410

1110
 
stranglings	9410

1111
 
subdirectory	9410

1112
 
subjective	9410

1113
 
subschema	9410

1114
 
succumbed	9410

1115
 
suites	9410

1116
 
sumac	9410

1117
 
sureties	9410

1118
 
swaying	9410

1119
 
sweetish	9410

1120
 
swelling	9410

1121
 
syndicate	9410

1122
 
taxonomically	9410

1123
 
techniques	9410

1124
 
teem	9410

1125
 
teethe	9410

1126
 
tempering	9410

1127
 
terminal	9410

1128
 
terminator	9410

1129
 
terminators	9410

1130
 
test	9410

1131
 
testicle	9410

1132
 
textures	9410

1133
 
theorizers	9410

1134
 
throttles	9410

1135
 
tidiness	9410

1136
 
timesharing	9410

1137
 
tinily	9410

1138
 
tinting	9410

1139
 
title	9410

1140
 
tragedies	9410

1141
 
traitor	9410

1142
 
trimmings	9410

1143
 
tropics	9410

1144
 
unaffected	9410

1145
 
uncovering	9410

1146
 
undoes	9410

1147
 
ungrateful	9410

1148
 
universals	9410

1149
 
unplug	9410

1150
 
unruly	9410

1151
 
untying	9410

1152
 
unwilling	9410

1153
 
vacuuming	9410

1154
 
validate	9410

1155
 
vanish	9410

1156
 
ventilate	9410

1157
 
veranda	9410

1158
 
vests	9410

1159
 
wallet	9410

1160
 
waltz	9410

1161
 
warm	9410

1162
 
warningly	9410

1163
 
watering	9410

1164
 
weasels	9410

1165
 
western	9410

1166
 
whiteners	9410

1167
 
widens	9410

1168
 
witchcraft	9410

1169
 
workers	9410

1170
 
yelped	9410

1171
 
youthfulness	9410

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

1173
 
fld3	period	price	price2

1174
 
admonishing	1002	28357832	8723648

1175
 
analyzable	1002	28357832	8723648

1176
 
annihilates	1001	5987435	234724

1177
 
Antares	1002	28357832	8723648

1178
 
astound	1001	5987435	234724

1179
 
audiology	1001	5987435	234724

1180
 
Augustine	1002	28357832	8723648

1181
 
Baird	1002	28357832	8723648

1182
 
bewilderingly	1001	5987435	234724

1183
 
breaking	1001	5987435	234724

1184
 
Conley	1001	5987435	234724

1185
 
dentally	1002	28357832	8723648

1186
 
dissociate	1002	28357832	8723648

1187
 
elite	1001	5987435	234724

1188
 
eschew	1001	5987435	234724

1189
 
Eulerian	1001	5987435	234724

1190
 
flanking	1001	5987435	234724

1191
 
foldout	1002	28357832	8723648

1192
 
funereal	1002	28357832	8723648

1193
 
galling	1002	28357832	8723648

1194
 
Graves	1001	5987435	234724

1195
 
grazing	1001	5987435	234724

1196
 
groupings	1001	5987435	234724

1197
 
handgun	1001	5987435	234724

1198
 
humility	1002	28357832	8723648

1199
 
impulsive	1002	28357832	8723648

1200
 
inch	1001	5987435	234724

1201
 
intelligibility	1001	5987435	234724

1202
 
jarring	1001	5987435	234724

1203
 
lawgiver	1001	5987435	234724

1204
 
lectured	1002	28357832	8723648

1205
 
Merritt	1002	28357832	8723648

1206
 
neonatal	1001	5987435	234724

1207
 
offload	1002	28357832	8723648

1208
 
parters	1002	28357832	8723648

1209
 
pityingly	1002	28357832	8723648

1210
 
puddings	1002	28357832	8723648

1211
 
Punjab	1001	5987435	234724

1212
 
quitter	1002	28357832	8723648

1213
 
realtor	1001	5987435	234724

1214
 
relaxing	1001	5987435	234724

1215
 
repetitions	1001	5987435	234724

1216
 
resumes	1001	5987435	234724

1217
 
Romans	1002	28357832	8723648

1218
 
rusting	1001	5987435	234724

1219
 
scholastics	1001	5987435	234724

1220
 
skulking	1002	28357832	8723648

1221
 
stated	1002	28357832	8723648

1222
 
suites	1002	28357832	8723648

1223
 
sureties	1001	5987435	234724

1224
 
testicle	1002	28357832	8723648

1225
 
tinily	1002	28357832	8723648

1226
 
tragedies	1001	5987435	234724

1227
 
trimmings	1001	5987435	234724

1228
 
vacuuming	1001	5987435	234724

1229
 
ventilate	1001	5987435	234724

1230
 
wallet	1001	5987435	234724

1231
 
Weissmuller	1002	28357832	8723648

1232
 
Wotan	1002	28357832	8723648

1233
 
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
 
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 int 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
 
0	Unknown

1248
 
29	company 1

1249
 
34	company 2

1250
 
36	company 3

1251
 
37	company 4

1252
 
40	company 5

1253
 
41	company 6

1254
 
50	company 11

1255
 
53	company 7

1256
 
58	company 8

1257
 
65	company 9

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
 
0	Unknown

1262
 
29	company 1

1263
 
34	company 2

1264
 
36	company 3

1265
 
37	company 4

1266
 
40	company 5

1267
 
41	company 6

1268
 
50	company 11

1269
 
53	company 7

1270
 
58	company 8

1271
 
65	company 9

1272
 
68	company 10

1273
 
select * from t1,t1 t12;

1274
 
Period	Varor_period	Period	Varor_period

1275
 
9410	9412	9410	9412

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

1277
 
fld1	fld1

1278
 
250501	250501

1279
 
250502	250501

1280
 
250503	250501

1281
 
250504	250501

1282
 
250505	250501

1283
 
250501	250502

1284
 
250502	250502

1285
 
250503	250502

1286
 
250504	250502

1287
 
250505	250502

1288
 
250501	250503

1289
 
250502	250503

1290
 
250503	250503

1291
 
250504	250503

1292
 
250505	250503

1293
 
250501	250504

1294
 
250502	250504

1295
 
250503	250504

1296
 
250504	250504

1297
 
250505	250504

1298
 
250501	250505

1299
 
250502	250505

1300
 
250503	250505

1301
 
250504	250505

1302
 
250505	250505

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

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

1305
 
companynr	companyname

1306
 
99	NULL

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

1308
 
count(*)

1309
 
1199

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

1311
 
companynr	companyname

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

1313
 
count(*)

1314
 
1200

1315
 
delete from t2 where fld1=999999;

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

1317
 
companynr	companynr

1318
 
37	36

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

1335
 
select period from t1 where ((period > 0 and period < 1) or (((period > 0 and period < 100) and (period > 10)) or (period > 10)) or (period > 0 and (period > 5 or period > 6)));

1336
 
period

1337
 
9410

1338
 
select a.fld1 from t2 as a,t2 b where ((a.fld1 = 250501 and a.fld1=b.fld1) or a.fld1=250502 or a.fld1=250503 or (a.fld1=250505 and a.fld1<=b.fld1 and b.fld1>=a.fld1)) and a.fld1=b.fld1;

1339
 
fld1

1340
 
250501

1341
 
250502

1342
 
250503

1343
 
250505

1344
 
select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);

1345
 
fld1

1346
 
250502

1347
 
250503

1348
 
select fld1 from t2 where fld1 between 250502 and 250504;

1349
 
fld1

1350
 
250502

1351
 
250503

1352
 
250504

1353
 
select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;

1354
 
fld3

1355
 
label

1356
 
labeled

1357
 
labeled

1358
 
landslide

1359
 
laterally

1360
 
leaflet

1361
 
lewdly

1362
 
Lillian

1363
 
luckily

1364
 
select count(*) from t1;

1365
 
count(*)

1366
 
1

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

1368
 
companynr	count(*)	sum(fld1)

1369
 
0	82	10355753

1370
 
29	95	14473298

1371
 
34	70	17788966

1372
 
36	215	22786296

1373
 
37	588	83602098

1374
 
40	37	6618386

1375
 
41	52	12816335

1376
 
50	11	1595438

1377
 
53	4	793210

1378
 
58	23	2254293

1379
 
65	10	2284055

1380
 
68	12	3097288

1381
 
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;

1382
 
companynr	count(*)

1383
 
68	12

1384
 
65	10

1385
 
58	23

1386
 
53	4

1387
 
50	11

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

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

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

1397
 
companynr	t2nr	count(price)	sum(price)	min(price)	max(price)	avg(price)

1398
 
37	1	1	5987435	5987435	5987435	5987435.0000

1399
 
37	2	1	28357832	28357832	28357832	28357832.0000

1400
 
37	3	1	39654943	39654943	39654943	39654943.0000

1401
 
37	11	1	5987435	5987435	5987435	5987435.0000

1402
 
37	12	1	28357832	28357832	28357832	28357832.0000

1403
 
37	13	1	39654943	39654943	39654943	39654943.0000

1404
 
37	21	1	5987435	5987435	5987435	5987435.0000

1405
 
37	22	1	28357832	28357832	28357832	28357832.0000

1406
 
37	23	1	39654943	39654943	39654943	39654943.0000

1407
 
37	31	1	5987435	5987435	5987435	5987435.0000

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

1409
 
companynr	t2nr	count(price)	sum(price)	min(price)	max(price)	avg(price)

1410
 
37	1	1	5987435	5987435	5987435	5987435.0000

1411
 
37	2	1	28357832	28357832	28357832	28357832.0000

1412
 
37	3	1	39654943	39654943	39654943	39654943.0000

1413
 
37	11	1	5987435	5987435	5987435	5987435.0000

1414
 
37	12	1	28357832	28357832	28357832	28357832.0000

1415
 
37	13	1	39654943	39654943	39654943	39654943.0000

1416
 
37	21	1	5987435	5987435	5987435	5987435.0000

1417
 
37	22	1	28357832	28357832	28357832	28357832.0000

1418
 
37	23	1	39654943	39654943	39654943	39654943.0000

1419
 
37	31	1	5987435	5987435	5987435	5987435.0000

1420
 
select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;

1421
 
companynr	count(price)	sum(price)	min(price)	max(price)	avg(price)

1422
 
37	12543	309394878010	5987435	39654943	24666736.6667

1423
 
78	8362	414611089292	726498	98439034	49582766.0000

1424
 
101	4181	3489454238	834598	834598	834598.0000

1425
 
154	4181	4112197254950	983543950	983543950	983543950.0000

1426
 
311	4181	979599938	234298	234298	234298.0000

1427
 
447	4181	9929180954	2374834	2374834	2374834.0000

1428
 
512	4181	3288532102	786542	786542	786542.0000

1429
 
select distinct mod(companynr,10) from t4 group by companynr;

1430
 
mod(companynr,10)

1431
 
0

1432
 
9

1433
 
4

1434
 
6

1435
 
7

1436
 
1

1437
 
3

1438
 
8

1439
 
5

1440
 
select distinct 1 from t4 group by companynr;

1441
 
1

1442
 
1

1443
 
select count(distinct fld1) from t2;

1444
 
count(distinct fld1)

1445
 
1199

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

1447
 
companynr	count(distinct fld1)

1448
 
0	82

1449
 
29	95

1450
 
34	70

1451
 
36	215

1452
 
37	588

1453
 
40	37

1454
 
41	52

1455
 
50	11

1456
 
53	4

1457
 
58	23

1458
 
65	10

1459
 
68	12

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

1461
 
companynr	count(*)

1462
 
0	82

1463
 
29	95

1464
 
34	70

1465
 
36	215

1466
 
37	588

1467
 
40	37

1468
 
41	52

1469
 
50	11

1470
 
53	4

1471
 
58	23

1472
 
65	10

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
 
0	82

1477
 
29	95

1478
 
34	70

1479
 
36	215

1480
 
37	588

1481
 
40	37

1482
 
41	52

1483
 
50	11

1484
 
53	4

1485
 
58	23

1486
 
65	10

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
 
0	82

1491
 
29	95

1492
 
34	70

1493
 
36	215

1494
 
37	588

1495
 
40	37

1496
 
41	52

1497
 
50	11

1498
 
53	4

1499
 
58	23

1500
 
65	10

1501
 
68	12

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

1503
 
companynr	count(distinct floor(fld1/100))

1504
 
0	47

1505
 
29	35

1506
 
34	14

1507
 
36	69

1508
 
37	108

1509
 
40	16

1510
 
41	11

1511
 
50	9

1512
 
53	1

1513
 
58	1

1514
 
65	1

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
 
0	47

1519
 
29	35

1520
 
34	14

1521
 
36	69

1522
 
37	108

1523
 
40	16

1524
 
41	11

1525
 
50	9

1526
 
53	1

1527
 
58	1

1528
 
65	1

1529
 
68	1

1530
 
select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;

1531
 
sum(fld1)	fld3

1532
 
11402	Romans

1533
 
select name,count(*) from t3 where name='cloakroom' group by name;

1534
 
name	count(*)

1535
 
cloakroom	4181

1536
 
select name,count(*) from t3 where name='cloakroom' and price>10 group by name;

1537
 
name	count(*)

1538
 
cloakroom	4181

1539
 
select count(*) from t3 where name='cloakroom' and price2=823742;

1540
 
count(*)

1541
 
4181

1542
 
select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;

1543
 
name	count(*)

1544
 
cloakroom	4181

1545
 
select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;

1546
 
name	count(*)

1547
 
extramarital	4181

1548
 
gazer	4181

1549
 
gems	4181

1550
 
Iranizes	4181

1551
 
spates	4181

1552
 
tucked	4181

1553
 
violinist	4181

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

1555
 
fld3	count(*)

1556
 
spates	4181

1557
 
select companynr,companyname from t4 group by 1;

1558
 
companynr	companyname

1559
 
0	Unknown

1560
 
29	company 1

1561
 
34	company 2

1562
 
36	company 3

1563
 
37	company 4

1564
 
40	company 5

1565
 
41	company 6

1566
 
50	company 11

1567
 
53	company 7

1568
 
58	company 8

1569
 
65	company 9

1570
 
68	company 10

1571
 
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;

1572
 
companynr	companyname	count(*)

1573
 
29	company 1	95

1574
 
68	company 10	12

1575
 
50	company 11	11

1576
 
34	company 2	70

1577
 
36	company 3	215

1578
 
37	company 4	588

1579
 
40	company 5	37

1580
 
41	company 6	52

1581
 
53	company 7	4

1582
 
58	company 8	23

1583
 
65	company 9	10

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

1588
 
select sum(Period)/count(*) from t1;

1589
 
sum(Period)/count(*)

1590
 
9410.0000

1591
 
select companynr,count(price) as "count",sum(price) as "sum" ,abs(sum(price)/count(price)-avg(price)) as "diff",(0+count(price))*companynr as func from t3 group by companynr;

1592
 
companynr	count	sum	diff	func

1593
 
37	12543	309394878010	0.0000	464091

1594
 
78	8362	414611089292	0.0000	652236

1595
 
101	4181	3489454238	0.0000	422281

1596
 
154	4181	4112197254950	0.0000	643874

1597
 
311	4181	979599938	0.0000	1300291

1598
 
447	4181	9929180954	0.0000	1868907

1599
 
512	4181	3288532102	0.0000	2140672

1600
 
select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;

1601
 
companynr	avg

1602
 
154	983543950.0000

1603
 
select companynr,count(*) from t2 group by companynr order by 2 desc;

1604
 
companynr	count(*)

1605
 
37	588

1606
 
36	215

1607
 
29	95

1608
 
0	82

1609
 
34	70

1610
 
41	52

1611
 
40	37

1612
 
58	23

1613
 
68	12

1614
 
50	11

1615
 
65	10

1616
 
53	4

1617
 
select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;

1618
 
companynr	count(*)

1619
 
41	52

1620
 
58	23

1621
 
68	12

1622
 
50	11

1623
 
65	10

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

1717
 
512	capably	786542

1718
 
512	cupboard	786542

1719
 
512	decliner	786542

1720
 
512	descendants	786542

1721
 
512	dopers	786542

1722
 
512	erases	786542

1723
 
512	Micronesia	786542

1724
 
512	Miles	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
 
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

1732
 
select t3.companynr+0,t3.t2nr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 37 group by 1,t3.t2nr,fld3,fld3,fld3,fld3,fld3 order by fld1;

1733
 
t3.companynr+0	t2nr	fld3	sum(price)

1734
 
37	1	Omaha	5987435

1735
 
37	11401	breaking	5987435

1736
 
37	11402	Romans	28357832

1737
 
37	11403	intercepted	39654943

1738
 
37	11501	bewilderingly	5987435

1739
 
37	11701	astound	5987435

1740
 
37	11702	admonishing	28357832

1741
 
37	11703	sumac	39654943

1742
 
37	12001	flanking	5987435

1743
 
37	12003	combed	39654943

1744
 
37	12301	Eulerian	5987435

1745
 
37	12302	dubbed	28357832

1746
 
37	12303	Kane	39654943

1747
 
37	12501	annihilates	5987435

1748
 
37	12602	Wotan	28357832

1749
 
37	12603	snatching	39654943

1750
 
37	12701	grazing	5987435

1751
 
37	12702	Baird	28357832

1752
 
37	12703	celery	39654943

1753
 
37	13601	handgun	5987435

1754
 
37	13602	foldout	28357832

1755
 
37	13603	mystic	39654943

1756
 
37	13801	intelligibility	5987435

1757
 
37	13802	Augustine	28357832

1758
 
37	13803	teethe	39654943

1759
 
37	13901	scholastics	5987435

1760
 
37	16001	audiology	5987435

1761
 
37	16201	wallet	5987435

1762
 
37	16202	parters	28357832

1763
 
37	16301	eschew	5987435

1764
 
37	16302	quitter	28357832

1765
 
37	16303	neat	39654943

1766
 
37	18001	jarring	5987435

1767
 
37	18002	tinily	28357832

1768
 
37	18003	balled	39654943

1769
 
37	18012	impulsive	28357832

1770
 
37	18013	starlet	39654943

1771
 
37	18021	lawgiver	5987435

1772
 
37	18022	stated	28357832

1773
 
37	18023	readable	39654943

1774
 
37	18032	testicle	28357832

1775
 
37	18033	Parsifal	39654943

1776
 
37	18041	Punjab	5987435

1777
 
37	18042	Merritt	28357832

1778
 
37	18043	Quixotism	39654943

1779
 
37	18051	sureties	5987435

1780
 
37	18052	puddings	28357832

1781
 
37	18053	tapestry	39654943

1782
 
37	18061	trimmings	5987435

1783
 
37	18062	humility	28357832

1784
 
37	18101	tragedies	5987435

1785
 
37	18102	skulking	28357832

1786
 
37	18103	flint	39654943

1787
 
37	18201	relaxing	5987435

1788
 
37	18202	offload	28357832

1789
 
37	18402	suites	28357832

1790
 
37	18403	lists	39654943

1791
 
37	18601	vacuuming	5987435

1792
 
37	18602	dentally	28357832

1793
 
37	18603	humanness	39654943

1794
 
37	18801	inch	5987435

1795
 
37	18802	Weissmuller	28357832

1796
 
37	18803	irresponsibly	39654943

1797
 
37	18811	repetitions	5987435

1798
 
37	18812	Antares	28357832

1799
 
37	19101	ventilate	5987435

1800
 
37	19102	pityingly	28357832

1801
 
37	19103	interdependent	39654943

1802
 
37	19201	Graves	5987435

1803
 
37	30501	neonatal	5987435

1804
 
37	30502	scribbled	28357832

1805
 
37	30503	chafe	39654943

1806
 
37	31901	realtor	5987435

1807
 
37	36001	elite	5987435

1808
 
37	36002	funereal	28357832

1809
 
37	38001	Conley	5987435

1810
 
37	38002	lectured	28357832

1811
 
37	38003	Abraham	39654943

1812
 
37	38011	groupings	5987435

1813
 
37	38012	dissociate	28357832

1814
 
37	38013	coexist	39654943

1815
 
37	38101	rusting	5987435

1816
 
37	38102	galling	28357832

1817
 
37	38103	obliterates	39654943

1818
 
37	38201	resumes	5987435

1819
 
37	38202	analyzable	28357832

1820
 
37	38203	terminator	39654943

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

1822
 
sum(price)

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
 
38008	234298

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

1828
 
companynr	fld1

1829
 
34	250501

1830
 
34	250502

1831
 
select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;

1832
 
companynr	fld1

1833
 
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
 
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
 
0

1845
 
29

1846
 
34

1847
 
37

1848
 
41

1849
 
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;

1850
 
companynr	companyname	count(*)

1851
 
68	company 10	12

1852
 
50	company 11	11

1853
 
40	company 5	37

1854
 
41	company 6	52

1855
 
53	company 7	4

1856
 
58	company 8	23

1857
 
65	company 9	10

1858
 
select count(*) from t2;

1859
 
count(*)

1860
 
1199

1861
 
select count(*) from t2 where fld1 < 098024;

1862
 
count(*)

1863
 
387

1864
 
select min(fld1) from t2 where fld1>= 098024;

1865
 
min(fld1)

1866
 
98024

1867
 
select max(fld1) from t2 where fld1>= 098024;

1868
 
max(fld1)

1869
 
1232609

1870
 
select count(*) from t3 where price2=76234234;

1871
 
count(*)

1872
 
4181

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

1874
 
count(*)

1875
 
4181

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

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

1878
 
0	1232609	1199

1879
 
select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;

1880
 
min(t2nr)	max(t2nr)

1881
 
2115	2115

1882
 
select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;

1883
 
count(*)	min(t2nr)	max(t2nr)

1884
 
4181	4	41804

1885
 
select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;

1886
 
t2nr	count(*)

1887
 
9	1

1888
 
19	1

1889
 
29	1

1890
 
39	1

1891
 
49	1

1892
 
59	1

1893
 
69	1

1894
 
79	1

1895
 
89	1

1896
 
99	1

1897
 
109	1

1898
 
119	1

1899
 
129	1

1900
 
139	1

1901
 
149	1

1902
 
159	1

1903
 
169	1

1904
 
179	1

1905
 
189	1

1906
 
199	1

1907
 
select max(t2nr) from t3 where price=983543950;

1908
 
max(t2nr)

1909
 
41807

1910
 
select t1.period from t3 = t1 limit 1;

1911
 
period

1912
 
1001

1913
 
select t1.period from t1 as t1 limit 1;

1914
 
period

1915
 
9410

1916
 
select t1.period as "Nuvarande period" from t1 as t1 limit 1;

1917
 
Nuvarande period

1918
 
9410

1919
 
select period as ok_period from t1 limit 1;

1920
 
ok_period

1921
 
9410

1922
 
select period as ok_period from t1 group by ok_period limit 1;

1923
 
ok_period

1924
 
9410

1925
 
select 1+1 as summa from t1 group by summa limit 1;

1926
 
summa

1927
 
2

1928
 
select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;

1929
 
Nuvarande period

1930
 
9410

1931
 
show tables;

1932
 
Tables_in_test

1933
 
t1

1934
 
t2

1935
 
t3

1936
 
t4

1937
 
show tables from test like "s%";

1938
 
Tables_in_test (s%)

1939
 
show tables from test like "t?";

1940
 
Tables_in_test (t?)

1941
 
show full columns from t2;

1942
 
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment

1943
 
auto	int	NULL	NO	PRI	NULL	auto_increment	#	

1944
 
fld1	int	NULL	NO	UNI	NULL		#	

1945
 
companynr	int	NULL	NO		NULL		#	

1946
 
fld3	varchar(30)	utf8_general_ci	NO	MUL	NULL		#	

1947
 
fld4	varchar(35)	utf8_general_ci	NO		NULL		#	

1948
 
fld5	varchar(35)	utf8_general_ci	NO		NULL		#	

1949
 
fld6	varchar(4)	utf8_general_ci	NO		NULL		#	

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

1951
 
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment

1952
 
fld1	int	NULL	NO	UNI	NULL		#	

1953
 
fld3	varchar(30)	utf8_general_ci	NO	MUL	NULL		#	

1954
 
fld4	varchar(35)	utf8_general_ci	NO		NULL		#	

1955
 
fld5	varchar(35)	utf8_general_ci	NO		NULL		#	

1956
 
fld6	varchar(4)	utf8_general_ci	NO		NULL		#	

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 NOT NULL auto_increment,

1962
 
pseudo varchar(35) NOT NULL default '',

1963
 
PRIMARY KEY  (id),

1964
 
UNIQUE KEY pseudo (pseudo)

1965
 
);

1966
 
INSERT INTO t1 (pseudo) VALUES ('test');

1967
 
INSERT INTO t1 (pseudo) VALUES ('test1');

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

1969
 
rnd1

1970
 
DROP TABLE t1;

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

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

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

1977
 
Warnings:

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

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

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

1987
 
select wss_type from t1 where wss_type ='102935229216544105';

1988
 
wss_type

1989
 
select wss_type from t1 where wss_type ='102935229216544104';

1990
 
wss_type

1991
 
select wss_type from t1 where wss_type ='102935229216544093';

1992
 
wss_type

1993
 
102935229216544093

1994
 
select wss_type from t1 where wss_type =102935229216544093;

1995
 
wss_type

1996
 
102935229216544093

1997
 
drop table t1;

1998
 
select 1+2,"aaaa",3.13*2.0 into @a,@b,@c;

1999
 
select @a;

2000
 
@a

2001
 
3

2002
 
select @b;

2003
 
@b

2004
 
aaaa

2005
 
select @c;

2006
 
@c

2007
 
6.260

2008
 
create table t1 (a int not null auto_increment primary key);

2009
 
insert into t1 values ();

2010
 
insert into t1 values ();

2011
 
insert into t1 values ();

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

2013
 
a	a

2014
 
1	1

2015
 
2	1

2016
 
3	1

2017
 
1	2

2018
 
2	2

2019
 
3	2

2020
 
1	3

2021
 
2	3

2022
 
3	3

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

2024
 
a	a

2025
 
1	1

2026
 
2	1

2027
 
3	1

2028
 
1	2

2029
 
2	2

2030
 
3	2

2031
 
1	3

2032
 
2	3

2033
 
3	3

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

2035
 
a	a

2036
 
1	1

2037
 
2	1

2038
 
3	1

2039
 
1	2

2040
 
2	2

2041
 
3	2

2042
 
1	3

2043
 
2	3

2044
 
3	3

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

2046
 
a	a

2047
 
1	1

2048
 
2	1

2049
 
3	1

2050
 
1	2

2051
 
2	2

2052
 
3	2

2053
 
1	3

2054
 
2	3

2055
 
3	3

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

2057
 
a	a

2058
 
1	2

2059
 
1	3

2060
 
2	2

2061
 
2	3

2062
 
3	2

2063
 
3	3

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

2065
 
a	a

2066
 
2	1

2067
 
3	1

2068
 
2	2

2069
 
3	2

2070
 
2	3

2071
 
3	3

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

2073
 
a

2074
 
1

2075
 
2

2076
 
3

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

2078
 
a

2079
 
1

2080
 
2

2081
 
3

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

2083
 
a	a

2084
 
1	2

2085
 
1	3

2086
 
2	2

2087
 
2	3

2088
 
3	2

2089
 
3	3

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

2091
 
a	a

2092
 
1	NULL

2093
 
2	1

2094
 
2	2

2095
 
2	3

2096
 
3	1

2097
 
3	2

2098
 
3	3

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

2100
 
a

2101
 
1

2102
 
2

2103
 
3

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

2105
 
a

2106
 
1

2107
 
2

2108
 
3

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

2110
 
a

2111
 
1

2112
 
2

2113
 
3

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

2115
 
a

2116
 
1

2117
 
2

2118
 
3

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

2120
 
a	a

2121
 
NULL	1

2122
 
1	2

2123
 
2	2

2124
 
3	2

2125
 
1	3

2126
 
2	3

2127
 
3	3

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

2129
 
a	a

2130
 
2	1

2131
 
3	1

2132
 
2	2

2133
 
3	2

2134
 
2	3

2135
 
3	3

2136
 
select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );

2137
 
a

2138
 
1

2139
 
2

2140
 
3

2141
 
select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );

2142
 
a

2143
 
1

2144
 
2

2145
 
3

2146
 
select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1;

2147
 
a

2148
 
1

2149
 
2

2150
 
3

2151
 
select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));

2152
 
a

2153
 
1

2154
 
2

2155
 
3

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

2157
 
a

2158
 
1

2159
 
2

2160
 
3

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

2162
 
a

2163
 
1

2164
 
2

2165
 
3

2166
 
drop table t1;

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

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

2173
 
2	8299	2517	2517

2174
 
3	8301	2518	2518

2175
 
4	8302	2519	2519

2176
 
5	8303	2520	2520

2177
 
6	8304	2521	2521

2178
 
drop table t1,t2;

2179
 
create table t1 (id1 int NOT NULL);

2180
 
create table t2 (id2 int NOT NULL);

2181
 
create table t3 (id3 int NOT NULL);

2182
 
create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4));

2183
 
insert into t1 values (1);

2184
 
insert into t1 values (2);

2185
 
insert into t2 values (1);

2186
 
insert into t4 values (1,1);

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

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

2189
 
id1	id2	id3	id4	id44

2190
 
1	1	NULL	NULL	NULL

2191
 
drop table t1,t2,t3,t4;

2192
 
create table t1(s varchar(10) not null);

2193
 
create table t2(s varchar(10) not null primary key);

2194
 
create table t3(s varchar(10) not null primary key);

2195
 
insert into t1 values ('one\t'), ('two\t');

2196
 
insert into t2 values ('one\r'), ('two\t');

2197
 
insert into t3 values ('one '), ('two\t');

2198
 
select * from t1 where s = 'one';

2199
 
s

2200
 
select * from t2 where s = 'one';

2201
 
s

2202
 
select * from t3 where s = 'one';

2203
 
s

2204
 
one 

2205
 
select * from t1,t2 where t1.s = t2.s;

2206
 
s	s

2207
 
two		two	

2208
 
select * from t2,t3 where t2.s = t3.s;

2209
 
s	s

2210
 
two		two	

2211
 
drop table t1, t2, t3;

2212
 
create table t1 (a integer,  b integer, index(a), index(b));

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

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

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

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

2217
 
a	b	c	d

2218
 
3	2	3	4

2219
 
4	2	4	4

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

2221
 
a	b	c	d

2222
 
3	2	3	4

2223
 
4	2	4	4

2224
 
drop table t1, t2;

2225
 
CREATE TABLE t1 (

2226
 
i int NOT NULL default '0',

2227
 
c char(10) NOT NULL default '',

2228
 
PRIMARY KEY  (i),

2229
 
UNIQUE KEY c (c)

2230
 
) ENGINE=MyISAM;

2231
 
INSERT INTO t1 VALUES (1,'a');

2232
 
INSERT INTO t1 VALUES (2,'b');

2233
 
INSERT INTO t1 VALUES (3,'c');

2234
 
EXPLAIN SELECT i FROM t1 WHERE i=1;

2235
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2236
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index

2237
 
DROP TABLE t1;

2238
 
CREATE TABLE t1 ( a BLOB, INDEX (a(20)) );

2239
 
CREATE TABLE t2 ( a BLOB, INDEX (a(20)) );

2240
 
INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');

2241
 
INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');

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

2243
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2244
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	

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

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

2247
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2248
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	

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

2250
 
DROP TABLE t1, t2;

2251
 
CREATE TABLE t1 ( city char(30) );

2252
 
INSERT INTO t1 VALUES ('London');

2253
 
INSERT INTO t1 VALUES ('Paris');

2254
 
SELECT * FROM t1 WHERE city='London';

2255
 
city

2256
 
London

2257
 
SELECT * FROM t1 WHERE city='london';

2258
 
city

2259
 
London

2260
 
EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london';

2261
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2262
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where

2263
 
SELECT * FROM t1 WHERE city='London' AND city='london';

2264
 
city

2265
 
London

2266
 
EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';

2267
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2268
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where

2269
 
SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';

2270
 
city

2271
 
London

2272
 
DROP TABLE t1;

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

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

2275
 
select a-b  from t1 order by 1;

2276
 
a-b  

2277
 
-1

2278
 
0

2279
 
1

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

2281
 
a-b 	(a-b < 0)

2282
 
-1	1

2283
 
0	0

2284
 
1	0

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

2286
 
d	(a-b >= 0)	b

2287
 
1	1	0

2288
 
0	1	1

2289
 
select a - b from t1 order by 1;

2290
 
a - b 

2291
 
-1

2292
 
0

2293
 
1

2294
 
drop table t1;

2295
 
create table t1 (a int);

2296
 
select all all * from t1;

2297
 
a

2298
 
select distinct distinct * from t1;

2299
 
a

2300
 
select all distinct * from t1;

2301
 
ERROR HY000: Incorrect usage of ALL and DISTINCT

2302
 
select distinct all * from t1;

2303
 
ERROR HY000: Incorrect usage of ALL and DISTINCT

2304
 
drop table t1;

2305
 
CREATE TABLE t1 (

2306
 
kunde_intern_id int NOT NULL default '0',

2307
 
kunde_id int NOT NULL default '0',

2308
 
FK_firma_id int NOT NULL default '0',

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

2310
 
vorname varchar(128) NOT NULL default '',

2311
 
nachname varchar(128) NOT NULL default '',

2312
 
geloescht enum('Ja','Nein') NOT NULL default 'Nein',

2313
 
firma varchar(128) NOT NULL default ''

2314
 
);

2315
 
INSERT INTO t1 VALUES 

2316
 
(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'),

2317
 
(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX');

2318
 
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1

2319
 
WHERE

2320
 
(

2321
 
(

2322
 
( '' != '' AND firma LIKE CONCAT('%', '', '%'))

2323
 
OR

2324
 
(vorname LIKE CONCAT('%', 'Vorname1', '%') AND 

2325
 
nachname LIKE CONCAT('%', '1Nachname', '%') AND 

2326
 
'Vorname1' != '' AND 'xxxx' != '')

2327
 
)

2328
 
AND

2329
 
(

2330
 
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2

2331
 
)

2332
 
)

2333
 
;

2334
 
kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht

2335
 
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname,

2336
 
geloescht FROM t1

2337
 
WHERE

2338
 
(

2339
 
(

2340
 
aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2

2341
 
)

2342
 
AND

2343
 
(

2344
 
( '' != '' AND firma LIKE CONCAT('%', '', '%')  )

2345
 
OR

2346
 
(  vorname LIKE CONCAT('%', 'Vorname1', '%') AND

2347
 
nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND

2348
 
'xxxx' != '')

2349
 
)

2350
 
)

2351
 
;

2352
 
kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht

2353
 
SELECT COUNT(*) FROM t1 WHERE 

2354
 
( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1)) 

2355
 
AND FK_firma_id = 2;

2356
 
COUNT(*)

2357
 
0

2358
 
drop table t1;

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

2360
 
INSERT INTO t1 VALUES (0x4000000000000000);

2361
 
SELECT b FROM t1 WHERE b=0x4000000000000000;

2362
 
b

2363
 
4611686018427387904

2364
 
DROP TABLE t1;

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

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

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

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

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

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

2371
 
id	name	gid	uid	ident	level

2372
 
1	fs	NULL	NULL	0	READ

2373
 
drop table t1,t2,t3;

2374
 
CREATE TABLE t1 (

2375
 
acct_id int NOT NULL default '0',

2376
 
profile_id int default NULL,

2377
 
UNIQUE KEY t1$acct_id (acct_id),

2378
 
KEY t1$profile_id (profile_id)

2379
 
);

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

2381
 
CREATE TABLE t2 (

2382
 
profile_id int default NULL,

2383
 
queue_id int default NULL,

2384
 
seq int default NULL,

2385
 
KEY t2$queue_id (queue_id)

2386
 
);

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

2388
 
CREATE TABLE t3 (

2389
 
id int NOT NULL default '0',

2390
 
qtype int default NULL,

2391
 
seq int default NULL,

2392
 
warn_lvl int default NULL,

2393
 
crit_lvl int default NULL,

2394
 
rr1 int NOT NULL default '0',

2395
 
rr2 int default NULL,

2396
 
default_queue int NOT NULL default '0',

2397
 
KEY t3$qtype (qtype),

2398
 
KEY t3$id (id)

2399
 
);

2400
 
INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0),

2401
 
(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0);

2402
 
SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q 

2403
 
WHERE 

2404
 
(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND 

2405
 
(pq.queue_id = q.id) AND (q.rr1 <> 1);

2406
 
COUNT(*)

2407
 
4

2408
 
drop table t1,t2,t3;

2409
 
create table t1 (f1 int);

2410
 
insert into t1 values (1),(NULL);

2411
 
create table t2 (f2 int, f3 int, f4 int);

2412
 
create index idx1 on t2 (f4);

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

2414
 
select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3)

2415
 
from  t2 C where A.f4 = C.f4) or A.f3 IS NULL;

2416
 
f2

2417
 
1

2418
 
NULL

2419
 
drop table t1,t2;

2420
 
create table t2 (a int);

2421
 
create index t2i on t2(a);

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

2423
 
select * from t2 where a > -1;

2424
 
a

2425
 
0

2426
 
254

2427
 
255

2428
 
drop table t2;

2429
 
CREATE TABLE t1 (a INT, b INT);

2430
 
(SELECT a, b AS c FROM t1) ORDER BY c+1;

2431
 
a	c

2432
 
(SELECT a, b AS c FROM t1) ORDER BY b+1;

2433
 
a	c

2434
 
SELECT a, b AS c FROM t1 ORDER BY c+1;

2435
 
a	c

2436
 
SELECT a, b AS c FROM t1 ORDER BY b+1;

2437
 
a	c

2438
 
drop table t1;

2439
 
create table t1(f1 int, f2 int);

2440
 
create table t2(f3 int);

2441
 
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1));

2442
 
f1

2443
 
select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1));

2444
 
f1

2445
 
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL));

2446
 
f1

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

2448
 
insert into t2 values(2);

2449
 
select * from t1,t2 where f1=f3 and (f1,f2) = (2,null);

2450
 
f1	f2	f3

2451
 
select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null);

2452
 
f1	f2	f3

2453
 
2	NULL	2

2454
 
drop table t1,t2;

2455
 
create table t1 (f1 int not null auto_increment primary key, f2 varchar(10));

2456
 
create table t11 like t1;

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

2458
 
show table status like 't1%';

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

2460
 
t1	PBXT	10	Dynamic	2	60	X	X	X	X	X	X	X	X	utf8_general_ci	NULL		

2461
 
t11	PBXT	10	Dynamic	0	60	X	X	X	X	X	X	X	X	utf8_general_ci	NULL		

2462
 
select 123 as a from t1 where f1 is null;

2463
 
a

2464
 
drop table t1,t11;

2465
 
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );

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

2467
 
CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT );

2468
 
INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3);

2469
 
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN

2470
 
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;

2471
 
a	b	c	d

2472
 
1	2	1	1

2473
 
1	2	2	1

2474
 
1	2	3	1

2475
 
1	10		2

2476
 
1	11		2

2477
 
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN

2478
 
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c;

2479
 
a	b	c	d

2480
 
1	10		4

2481
 
1	2	1	1

2482
 
1	2	2	1

2483
 
1	2	3	1

2484
 
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN

2485
 
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c;

2486
 
a	b	c	d

2487
 
1	2	1	1

2488
 
1	2	2	1

2489
 
1	2	3	1

2490
 
1	10		2

2491
 
1	11		2

2492
 
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1

2493
 
WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;

2494
 
a	b	c	d

2495
 
1	2	1	1

2496
 
1	2	2	1

2497
 
1	2	3	1

2498
 
DROP TABLE IF EXISTS t1, t2;

2499
 
create table t1 (f1 int primary key, f2 int);

2500
 
create table t2 (f3 int, f4 int, primary key(f3,f4));

2501
 
insert into t1 values (1,1);

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

2503
 
select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1;

2504
 
count(f2) >0

2505
 
1

2506
 
drop table t1,t2;

2507
 
create table t1 (f1 int,f2 int);

2508
 
insert into t1 values(1,1);

2509
 
create table t2 (f3 int, f4 int, primary key(f3,f4));

2510
 
insert into t2 values(1,1);

2511
 
select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2));

2512
 
f1	f2

2513
 
1	1

2514
 
drop table t1,t2;

2515
 
CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));

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

2517
 
CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));

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

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

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

2521
 
DROP TABLE t1,t2,t3;

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

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

2524
 
EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);

2525
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2526
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	

2527
 
EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);

2528
 
ERROR 42000: Key 'a' doesn't exist in table 't1'

2529
 
EXPLAIN SELECT * FROM t1 FORCE INDEX (a);

2530
 
ERROR 42000: Key 'a' doesn't exist in table 't1'

2531
 
DROP TABLE t1;

2532
 
CREATE TABLE t1 (a int, b int);

2533
 
INSERT INTO t1 VALUES (1,1), (2,1), (4,10);

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

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

2536
 
ALTER TABLE t1 ENABLE KEYS;

2537
 
Warnings:

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

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

2540
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2541
 
1	SIMPLE	t2	index	b	b	5	NULL	2	Using index

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

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

2544
 
a	b	a	b

2545
 
1	NULL	1	1

2546
 
1	NULL	2	1

2547
 
1	NULL	4	10

2548
 
2	10	4	10

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

2550
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2551
 
1	SIMPLE	t2	index	b	b	5	NULL	2	Using index

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

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

2554
 
a	b	a	b

2555
 
1	NULL	1	1

2556
 
1	NULL	2	1

2557
 
1	NULL	4	10

2558
 
2	10	4	10

2559
 
DROP TABLE IF EXISTS t1,t2;

2560
 
CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1));

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

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

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

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

2565
 
max(key1)

2566
 
0.6158

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

2568
 
max(key2)

2569
 
1.6158

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

2571
 
min(key1)

2572
 
0.3762

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

2574
 
min(key2)

2575
 
1.3762

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

2577
 
where key1 <= 0.6158 and key2 >= 1.3762;

2578
 
max(key1)	min(key2)

2579
 
0.6158	1.3762

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

2581
 
max(key1)

2582
 
0.6158

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

2584
 
min(key1)

2585
 
0.3762

2586
 
DROP TABLE t1,t2;

2587
 
CREATE TABLE t1 (i BIGINT NOT NULL);

2588
 
INSERT INTO t1 VALUES (10);

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

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

2591
 
1	1	1	1

2592
 
DROP TABLE t1;

2593
 
CREATE TABLE t1 (c0 int);

2594
 
CREATE TABLE t2 (c0 int);

2595
 
INSERT INTO t1 VALUES(@@connect_timeout);

2596
 
INSERT INTO t2 VALUES(@@connect_timeout);

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

2598
 
c0	c0

2599
 
X	X

2600
 
DROP TABLE t1, t2;

2601
 
End of 4.1 tests

2602
 
CREATE TABLE t1 ( 

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

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

2605
 
F2I4 int NOT NULL default '0' 

2606
 
) ENGINE=MyISAM;

2607
 
INSERT INTO t1 VALUES 

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

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

2610
 
('WART', '0100', 1), 

2611
 
('WART', '0200', 1), 

2612
 
('WERT', '0100', 2), 

2613
 
('WORT','0200', 2), 

2614
 
('WT', '0100', 2), 

2615
 
('W_RT', '0100', 2), 

2616
 
('WaRT', '0100', 3), 

2617
 
('WART', '0300', 3), 

2618
 
('WRT' , '0400', 3), 

2619
 
('WURM', '0500', 3), 

2620
 
('W%T', '0600', 4), 

2621
 
('WA%T', '0700', 4), 

2622
 
('WA_T', '0800', 4);

2623
 
SELECT K2C4, K4N4, F2I4 FROM t1

2624
 
WHERE  K2C4 = 'WART' AND 

2625
 
(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200'));

2626
 
K2C4	K4N4	F2I4

2627
 
WART	0200	1

2628
 
SELECT K2C4, K4N4, F2I4 FROM t1

2629
 
WHERE  K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200');

2630
 
K2C4	K4N4	F2I4

2631
 
WART	0100	1

2632
 
WART	0200	1

2633
 
WART	0300	3

2634
 
DROP TABLE t1;

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

2636
 
create table t2 like t1;

2637
 
select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1;

2638
 
a

2639
 
select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1;

2640
 
a

2641
 
select x.a, y.a, z.a from ( (t1 x inner join t2 y on x.a=y.a) inner join t2 z on y.a=z.a) WHERE x.a=1;

2642
 
a	a	a

2643
 
drop table t1,t2;

2644
 
create table t1 (s1 varchar(5));

2645
 
insert into t1 values ('Wall');

2646
 
select min(s1) from t1 group by s1 with rollup;

2647
 
min(s1)

2648
 
Wall

2649
 
Wall

2650
 
drop table t1;

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

2652
 
insert into t1 values (0);

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

2654
 
avg(distinct s1)

2655
 
0.0000

2656
 
0.0000

2657
 
drop table t1;

2658
 
create table t1 (s1 int);

2659
 
insert into t1 values (null),(1);

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

2661
 
x

2662
 
NULL

2663
 
1.0000

2664
 
drop table t1;

2665
 
CREATE TABLE t1 (a int);

2666
 
CREATE TABLE t2 (a int);

2667
 
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);

2668
 
INSERT INTO t2 VALUES (2), (4), (6);

2669
 
SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;

2670
 
a

2671
 
2

2672
 
4

2673
 
EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;

2674
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2675
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	

2676
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer

2677
 
EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a;

2678
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2679
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	

2680
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer

2681
 
DROP TABLE t1,t2;

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

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

2684
 
16	16	2	2

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

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

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

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

2689
 
select * from t1 left join t2 on f1 = f3;

2690
 
f1	f2	f3	f4

2691
 
 2	2	 2	 one 

2692
 
 2	2	 2	 two 

2693
 
drop table t1,t2;

2694
 
create table t1 (pk int primary key, b int);

2695
 
create table t2 (pk int primary key, c int);

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

2697
 
pk

2698
 
drop table t1,t2;

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

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

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

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

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

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

2705
 
select * from t1 natural join t2 natural join t3;

2706
 
a	b	t1_val	t2_val

2707
 
1	1	1	1

2708
 
2	1	2	1

2709
 
select * from t1 natural join t3 natural join t2;

2710
 
b	a	t1_val	t2_val

2711
 
1	1	1	1

2712
 
1	2	2	1

2713
 
drop table t1, t2, t3;

2714
 
create table t1 (a char(1));

2715
 
create table t2 (a char(1));

2716
 
insert into t1 values ('a'),('b'),('c');

2717
 
insert into t2 values ('b'),('c'),('d');

2718
 
select a from t1 natural join t2;

2719
 
a

2720
 
b

2721
 
c

2722
 
select * from t1 natural join t2 where a = 'b';

2723
 
a

2724
 
b

2725
 
drop table t1, t2;

2726
 
CREATE TABLE t1 (`id` int);

2727
 
CREATE TABLE t2 (`id` int);

2728
 
CREATE TABLE t3 (`id` int);

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

2730
 
INSERT INTO t2 VALUES (2);

2731
 
INSERT INTO t3 VALUES (3);

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

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

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

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

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

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

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

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

2740
 
drop table t1, t2, t3;

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

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

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

2744
 
insert into t2 values (1,10);

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

2746
 
a	b	b

2747
 
1	10	10

2748
 
select * from t1 inner join t2 using (a);

2749
 
a	b	b

2750
 
1	10	10

2751
 
drop table t1, t2;

2752
 
create table t1 (a int, c int);

2753
 
create table t2 (b int);

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

2755
 
create table t4 (c int);

2756
 
insert into t1 values (1,1);

2757
 
insert into t2 values (1);

2758
 
insert into t3 values (1,1);

2759
 
insert into t4 values (1);

2760
 
select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a);

2761
 
a	c	b	b	a

2762
 
1	1	1	1	1

2763
 
select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a);

2764
 
ERROR 42S22: Unknown column 't1.a' in 'on clause'

2765
 
select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c);

2766
 
a	c	b	b	a	c

2767
 
1	1	1	1	1	1

2768
 
select * from t1 join t2 join t4 using (c);

2769
 
c	a	b

2770
 
1	1	1

2771
 
drop table t1, t2, t3, t4;

2772
 
create table t1(x int, y int);

2773
 
create table t2(x int, y int);

2774
 
create table t3(x int, primary key(x));

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

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

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

2778
 
select t1.x, t3.x from t1, t2, t3  where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y;

2779
 
x	x

2780
 
1	1

2781
 
2	1

2782
 
3	1

2783
 
3	2

2784
 
3	3

2785
 
4	3

2786
 
4	4

2787
 
4	5

2788
 
drop table t1,t2,t3;

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

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

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

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

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

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

2795
 
select count(*)

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

2797
 
count(*)

2798
 
6

2799
 
select count(*)

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

2801
 
count(*)

2802
 
6

2803
 
drop table t1,t2,t3;

2804
 
create table t1 (a int);

2805
 
create table t2 (b int);

2806
 
create table t3 (c int);

2807
 
select * from t1 join t2 join t3 on (t1.a=t3.c);

2808
 
a	b	c

2809
 
select * from t1 join t2 left join t3 on (t1.a=t3.c);

2810
 
a	b	c

2811
 
select * from t1 join t2 right join t3 on (t1.a=t3.c);

2812
 
a	b	c

2813
 
select * from t1 join t2 straight_join t3 on (t1.a=t3.c);

2814
 
a	b	c

2815
 
drop table t1, t2 ,t3;

2816
 
create table t1(f1 int, f2 date);

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

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

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

2820
 
f1	f2

2821
 
1	2005-01-01

2822
 
2	2005-09-01

2823
 
3	2005-09-30

2824
 
4	2005-10-01

2825
 
5	2005-12-30

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

2827
 
f1	f2

2828
 
1	2005-01-01

2829
 
2	2005-09-01

2830
 
3	2005-09-30

2831
 
4	2005-10-01

2832
 
5	2005-12-30

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

2834
 
f1	f2

2835
 
4	2005-10-01

2836
 
5	2005-12-30

2837
 
Warnings:

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

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

2840
 
f1	f2

2841
 
3	2005-09-30

2842
 
4	2005-10-01

2843
 
5	2005-12-30

2844
 
Warnings:

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

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

2847
 
f1	f2

2848
 
1	2005-01-01

2849
 
2	2005-09-01

2850
 
3	2005-09-30

2851
 
Warnings:

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

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

2854
 
f1	f2

2855
 
1	2005-01-01

2856
 
2	2005-09-01

2857
 
Warnings:

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

2859
 
drop table t1;

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

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

2862
 
CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32),

2863
 
PRIMARY KEY(key_a,key_b));

2864
 
INSERT INTO t1 VALUES (0,'');

2865
 
INSERT INTO t1 VALUES (1,'i');

2866
 
INSERT INTO t1 VALUES (2,'j');

2867
 
INSERT INTO t1 VALUES (3,'k');

2868
 
INSERT INTO t2 VALUES (1,'r');

2869
 
INSERT INTO t2 VALUES (2,'s');

2870
 
INSERT INTO t2 VALUES (3,'t');

2871
 
INSERT INTO t3 VALUES (1,5,'x');

2872
 
INSERT INTO t3 VALUES (1,6,'y');

2873
 
INSERT INTO t3 VALUES (2,5,'xx');

2874
 
INSERT INTO t3 VALUES (2,6,'yy');

2875
 
INSERT INTO t3 VALUES (2,7,'zz');

2876
 
INSERT INTO t3 VALUES (3,5,'xxx');

2877
 
SELECT t2.key_a,foo 

2878
 
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a

2879
 
INNER JOIN t3 ON t1.key_a = t3.key_a

2880
 
WHERE t2.key_a=2 and key_b=5;

2881
 
key_a	foo

2882
 
2	xx

2883
 
EXPLAIN SELECT t2.key_a,foo 

2884
 
FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a

2885
 
INNER JOIN t3 ON t1.key_a = t3.key_a

2886
 
WHERE t2.key_a=2 and key_b=5;

2887
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2888
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index

2889
 
1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	Using index

2890
 
1	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	

2891
 
SELECT t2.key_a,foo 

2892
 
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a

2893
 
INNER JOIN t3 ON t1.key_a = t3.key_a

2894
 
WHERE t2.key_a=2 and key_b=5;

2895
 
key_a	foo

2896
 
2	xx

2897
 
EXPLAIN SELECT t2.key_a,foo 

2898
 
FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a

2899
 
INNER JOIN t3 ON t1.key_a = t3.key_a

2900
 
WHERE t2.key_a=2 and key_b=5;

2901
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2902
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index

2903
 
1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	Using index

2904
 
1	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	

2905
 
DROP TABLE t1,t2,t3;

2906
 
create  table t1 (f1 int);

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

2908
 
create table t2 (f2 int, f3 int, key(f2));

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

2910
 
create table t3 (f4 int not null);

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

2912
 
select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1;

2913
 
f1	count

2914
 
1	0

2915
 
2	3

2916
 
drop table t1,t2,t3;

2917
 
create table t1 (f1 int unique);

2918
 
create table t2 (f2 int unique);

2919
 
create table t3 (f3 int unique);

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

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

2922
 
insert into t3 values(1),(NULL);

2923
 
select * from t3 where f3 is null;

2924
 
f3

2925
 
NULL

2926
 
select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1;

2927
 
f2

2928
 
1

2929
 
drop table t1,t2,t3;

2930
 
create table t1(f1 char, f2 char not null);

2931
 
insert into t1 values(null,'a');

2932
 
create table t2 (f2 char not null);

2933
 
insert into t2 values('b');

2934
 
select * from t1 left join t2 on f1=t2.f2 where t1.f2='a';

2935
 
f1	f2	f2

2936
 
NULL	a	NULL

2937
 
drop table t1,t2;

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

2939
 
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

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

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

2942
 
INSERT INTO t1 VALUES

2943
 
(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10);

2944
 
INSERT INTO t2 VALUES 

2945
 
(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),

2946
 
(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');

2947
 
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr

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

2949
 
sku	sppr	name	sku	pr

2950
 
20	10	bbb	10	10

2951
 
20	10	bbb	20	10

2952
 
EXPLAIN

2953
 
SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr

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

2955
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2956
 
1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	

2957
 
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where

2958
 
DROP TABLE t1,t2;

2959
 
create table t1 (a int);

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

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

2962
 
insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C;

2963
 
analyze table t2;

2964
 
Table	Op	Msg_type	Msg_text

2965
 
test.t2	analyze	status	OK

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

2967
 
Z

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

2969
 
drop table t1, t2;

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

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

2972
 
(3,1), (5,1), (8,9), (2,2), (0,9);

2973
 
CREATE TABLE t2 (c int, d int, f int, INDEX(c,f));

2974
 
INSERT INTO t2 VALUES

2975
 
(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1),

2976
 
(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1),

2977
 
(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1);

2978
 
EXPLAIN

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

2980
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2981
 
1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	1	Using where

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

2983
 
EXPLAIN

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

2985
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2986
 
1	SIMPLE	t1	range	PRIMARY,b	PRIMARY	4	NULL	1	Using where

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

2988
 
DROP TABLE t1, t2;

2989
 
create table t1 (

2990
 
a int    not null auto_increment primary key,

2991
 
b int             not null,

2992
 
c int             not null

2993
 
);

2994
 
create table t2 (

2995
 
a int    not null auto_increment primary key,

2996
 
b int             not null,

2997
 
c int    not null,

2998
 
d varchar(50)

2999
 
);

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

3001
 
insert into t2 (b,c) values (0,1);

3002
 
select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d

3003
 
from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1

3004
 
where t1.b <> 1 order by t1.a;

3005
 
a	t1.b + 0	t1.c + 0	a	t2.b + 0	c	d

3006
 
1	0	1	1	0	1	NULL

3007
 
2	0	1	NULL	NULL	NULL	NULL

3008
 
drop table t1,t2;

3009
 
SELECT 0.9888889889 * 1.011111411911;

3010
 
0.9888889889 * 1.011111411911

3011
 
0.9998769417899202067879

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

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

3014
 
CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));

3015
 
INSERT INTO t2 VALUES

3016
 
(1), (1), (1), (1), (1), (1), (1), (1),

3017
 
(2), (2), (2), (2),

3018
 
(3), (3),

3019
 
(4);

3020
 
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;

3021
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3022
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

3023
 
1	SIMPLE	t2	ref	idx	idx	4	const	1	Using index

3024
 
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;

3025
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3026
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

3027
 
1	SIMPLE	t2	ref	idx	idx	4	const	1	Using index

3028
 
DROP TABLE t1, t2;

3029
 
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);

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

3031
 
CREATE TABLE t2 (b int, c INT, INDEX idx1(b));

3032
 
INSERT INTO t2 VALUES (2,1), (3,2);

3033
 
CREATE TABLE t3 (d int,  e int, INDEX idx1(d));

3034
 
INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);

3035
 
EXPLAIN

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

3037
 
WHERE t1.id=2;

3038
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3039
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

3040
 
1	SIMPLE	t2	const	idx1	NULL	NULL	NULL	1	

3041
 
1	SIMPLE	t3	ref	idx1	idx1	5	const	1	

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

3043
 
WHERE t1.id=2;

3044
 
id	a	b	c	d	e

3045
 
2	NULL	NULL	NULL	2	10

3046
 
2	NULL	NULL	NULL	2	20

3047
 
2	NULL	NULL	NULL	2	40

3048
 
2	NULL	NULL	NULL	2	50

3049
 
DROP TABLE t1,t2,t3;

3050
 
CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));

3051
 
CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));

3052
 
INSERT INTO t1 VALUES

3053
 
('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), 

3054
 
('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');

3055
 
INSERT INTO t2 VALUES

3056
 
('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),

3057
 
('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),

3058
 
('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),

3059
 
('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');

3060
 
EXPLAIN SELECT t2.* 

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

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

3063
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3064
 
1	SIMPLE	t1	range	PRIMARY	PRIMARY	42	NULL	1	Using where

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

3066
 
EXPLAIN SELECT t2.* 

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

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

3069
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3070
 
1	SIMPLE	t1	range	PRIMARY	PRIMARY	42	NULL	1	Using where

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

3072
 
EXPLAIN SELECT t2.* 

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

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

3075
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3076
 
1	SIMPLE	t1	range	PRIMARY	PRIMARY	42	NULL	2	Using where

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

3078
 
DROP TABLE t1,t2;

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

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

3081
 
PRIMARY KEY (a), UNIQUE KEY (b));

3082
 
INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');

3083
 
INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');

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

3085
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3086
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

3087
 
1	SIMPLE	t2	const	b	b	82	const	1	Using index

3088
 
DROP TABLE t1,t2;

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

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

3091
 
CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));

3092
 
INSERT INTO t1 VALUES 

3093
 
(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),

3094
 
(6,63,67), (5,55,58), (3,38,39), (8,81,89);

3095
 
INSERT INTO t2 VALUES

3096
 
(21,210), (41,410), (82,820), (83,830), (84,840),

3097
 
(65,650), (51,510), (37,370), (94,940), (76,760),

3098
 
(22,220), (33,330), (40,400), (95,950), (38,380),

3099
 
(67,670), (88,880), (57,570), (96,960), (97,970);

3100
 
INSERT INTO t3 VALUES

3101
 
(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),

3102
 
(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),

3103
 
(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),

3104
 
(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');

3105
 
EXPLAIN

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

3107
 
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 

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

3109
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3110
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

3111
 
1	SIMPLE	t2	range	si	si	5	NULL	1	Using where

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

3113
 
EXPLAIN

3114
 
SELECT t3.a FROM t1,t2,t3

3115
 
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND

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

3117
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3118
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

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

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

3121
 
EXPLAIN 

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

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

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

3125
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3126
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

3127
 
1	SIMPLE	t2	range	si	si	5	NULL	2	Using where

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

3129
 
EXPLAIN 

3130
 
SELECT t3.a FROM t1,t2,t3

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

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

3133
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3134
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

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

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

3137
 
DROP TABLE t1,t2,t3;

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

3139
 
CREATE TABLE t2 ( f11 int PRIMARY KEY );

3140
 
INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0);

3141
 
INSERT INTO t2 VALUES (62);

3142
 
SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;

3143
 
f1	f2	f3	f4	f5	f6	checked_out	f11

3144
 
1	1	1	0	0	0	0	NULL

3145
 
DROP TABLE t1, t2;

3146
 
DROP TABLE IF EXISTS t1;

3147
 
CREATE TABLE t1(a int);

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

3149
 
SELECT * FROM t1 LIMIT 2, -1;

3150
 
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

3151
 
DROP TABLE t1;

3152
 
CREATE TABLE t1 (

3153
 
ID_with_null int NULL,

3154
 
ID_better int NOT NULL,

3155
 
INDEX idx1 (ID_with_null),

3156
 
INDEX idx2 (ID_better)

3157
 
);

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

3159
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;

3160
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;

3161
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;

3162
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;

3163
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;

3164
 
SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL;

3165
 
COUNT(*)

3166
 
128

3167
 
SELECT COUNT(*) FROM t1 WHERE ID_better=1;

3168
 
COUNT(*)

3169
 
2

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

3171
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3172
 
1	SIMPLE	t1	ref	idx1,idx2	idx1	5	const	1	Using where

3173
 
DROP INDEX idx1 ON t1;

3174
 
CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);

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

3176
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3178
 
DROP TABLE t1;

3179
 
CREATE TABLE t1 (

3180
 
ID1_with_null int NULL,

3181
 
ID2_with_null int NULL,

3182
 
ID_better int NOT NULL,

3183
 
INDEX idx1 (ID1_with_null, ID2_with_null),

3184
 
INDEX idx2 (ID_better)

3185
 
);

3186
 
INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3),

3187
 
(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);

3188
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;

3189
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;

3190
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;

3191
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;

3192
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;

3193
 
INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;

3194
 
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;

3195
 
COUNT(*)

3196
 
24

3197
 
SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL;

3198
 
COUNT(*)

3199
 
24

3200
 
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;

3201
 
COUNT(*)

3202
 
192

3203
 
SELECT COUNT(*) FROM t1 WHERE ID_better=1;

3204
 
COUNT(*)

3205
 
2

3206
 
EXPLAIN SELECT * FROM t1

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

3208
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3209
 
1	SIMPLE	t1	ref	idx1,idx2	idx1	10	const,const	#	Using where

3210
 
EXPLAIN SELECT * FROM t1

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

3212
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3213
 
1	SIMPLE	t1	ref	idx1,idx2	idx1	5	const	#	Using where

3214
 
EXPLAIN SELECT * FROM t1

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

3216
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3217
 
1	SIMPLE	t1	ref	idx1,idx2	idx1	10	const,const	#	Using where

3218
 
DROP INDEX idx1 ON t1;

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

3220
 
EXPLAIN SELECT * FROM t1

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

3222
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3224
 
EXPLAIN SELECT * FROM t1

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

3226
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3228
 
EXPLAIN SELECT * FROM t1

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

3230
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3232
 
EXPLAIN SELECT * FROM t1

3233
 
WHERE ID_better=1 AND ID1_with_null IS NULL AND 

3234
 
(ID2_with_null=1 OR ID2_with_null=2);

3235
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3236
 
1	SIMPLE	t1	ref	idx1,idx2	idx1	5	const	11	Using where

3237
 
DROP TABLE t1;

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

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

3240
 
ANALYZE TABLE t1;

3241
 
Table	Op	Msg_type	Msg_text

3242
 
test.t1	analyze	status	OK

3243
 
CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));

3244
 
INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");

3245
 
INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;

3246
 
ANALYZE TABLE t2;

3247
 
Table	Op	Msg_type	Msg_text

3248
 
test.t2	analyze	status	OK

3249
 
EXPLAIN

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

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

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

3253
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3254
 
1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	

3255
 
1	SIMPLE	t1	range	ts	ts	4	NULL	2	Using where

3256
 
Warnings:

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

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

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

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

3261
 
a	ts	a	dt1	dt2

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

3263
 
Warnings:

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

3265
 
DROP TABLE t1,t2;

3266
 
create table t1 (a bigint);

3267
 
insert into t1 values

3268
 
(if(1, 92233720368547758, 1)),

3269
 
(case when 1 then 92233720368547758 else 1 end),

3270
 
(coalesce(92233720368547758, 1));

3271
 
select * from t1;

3272
 
a

3273
 
92233720368547758

3274
 
92233720368547758

3275
 
92233720368547758

3276
 
drop table t1;

3277
 
create table t1 select

3278
 
if(1, 9223372036854775808, 1) i,

3279
 
case when 1 then 9223372036854775808 else 1 end c,

3280
 
coalesce(9223372036854775808, 1) co;

3281
 
show create table t1;

3282
 
Table	Create Table

3283
 
t1	CREATE TABLE `t1` (

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

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

3286
 
  `co` decimal(19,0) NOT NULL

3287
 
) ENGINE=PBXT

3288
 
drop table t1;

3289
 
select 

3290
 
if(1, 1111111111111111111, 1) i,

3291
 
case when 1 then 1111111111111111111 else 1 end c,

3292
 
coalesce(1111111111111111111, 1) co;

3293
 
i	c	co

3294
 
1111111111111111111	1111111111111111111	1111111111111111111

3295
 
CREATE TABLE t1 (name varchar(255));

3296
 
CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));

3297
 
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');

3298
 
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);

3299
 
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);

3300
 
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);

3301
 
SELECT * FROM t2;

3302
 
name	n

3303
 
bb	1

3304
 
aa	2

3305
 
cc   	3

3306
 
cc 	4

3307
 
cc	5

3308
 
bb 	6

3309
 
cc 	7

3310
 
SELECT * FROM t2 ORDER BY name;

3311
 
name	n

3312
 
aa	2

3313
 
bb	1

3314
 
bb 	6

3315
 
cc 	4

3316
 
cc   	3

3317
 
cc	5

3318
 
cc 	7

3319
 
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;

3320
 
name	LENGTH(name)	n

3321
 
aa	2	2

3322
 
bb	2	1

3323
 
bb 	3	6

3324
 
cc 	4	4

3325
 
cc   	5	3

3326
 
cc	2	5

3327
 
cc 	3	7

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

3329
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3330
 
1	SIMPLE	t2	ref	name	name	15	const	1	Using where

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

3332
 
name	LENGTH(name)	n

3333
 
cc   	5	3

3334
 
cc	2	5

3335
 
cc 	3	7

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

3337
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3338
 
1	SIMPLE	t2	range	name	name	15	NULL	1	Using where

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

3340
 
name	LENGTH(name)	n

3341
 
cc   	5	3

3342
 
cc 	4	4

3343
 
cc	2	5

3344
 
cc 	3	7

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

3346
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3347
 
1	SIMPLE	t2	range	name	name	15	NULL	1	Using where; Using filesort

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

3349
 
name	LENGTH(name)	n

3350
 
cc 	4	4

3351
 
cc   	5	3

3352
 
cc	2	5

3353
 
cc 	3	7

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

3355
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3356
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	

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

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

3359
 
name	name	n

3360
 
ccc	NULL	NULL

3361
 
bb	bb	1

3362
 
bb	bb 	6

3363
 
cc 	cc   	3

3364
 
cc 	cc	5

3365
 
cc 	cc 	7

3366
 
aa  	aa	2

3367
 
aa	aa	2

3368
 
DROP TABLE t1,t2;

3369
 
CREATE TABLE t1 (name text);

3370
 
CREATE TABLE t2 (name text, n int, KEY (name(3)));

3371
 
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');

3372
 
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);

3373
 
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);

3374
 
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);

3375
 
SELECT * FROM t2;

3376
 
name	n

3377
 
bb	1

3378
 
aa	2

3379
 
cc   	3

3380
 
cc 	4

3381
 
cc	5

3382
 
bb 	6

3383
 
cc 	7

3384
 
SELECT * FROM t2 ORDER BY name;

3385
 
name	n

3386
 
aa	2

3387
 
bb	1

3388
 
bb 	6

3389
 
cc 	4

3390
 
cc   	3

3391
 
cc	5

3392
 
cc 	7

3393
 
SELECT name, LENGTH(name), n FROM t2 ORDER BY name;

3394
 
name	LENGTH(name)	n

3395
 
aa	2	2

3396
 
bb	2	1

3397
 
bb 	3	6

3398
 
cc 	4	4

3399
 
cc   	5	3

3400
 
cc	2	5

3401
 
cc 	3	7

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

3403
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3404
 
1	SIMPLE	t2	ref	name	name	15	const	1	Using where

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

3406
 
name	LENGTH(name)	n

3407
 
cc   	5	3

3408
 
cc	2	5

3409
 
cc 	3	7

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

3411
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3412
 
1	SIMPLE	t2	range	name	name	15	NULL	1	Using where

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

3414
 
name	LENGTH(name)	n

3415
 
cc   	5	3

3416
 
cc 	4	4

3417
 
cc	2	5

3418
 
cc 	3	7

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

3420
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3421
 
1	SIMPLE	t2	range	name	name	15	NULL	1	Using where; Using filesort

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

3423
 
name	LENGTH(name)	n

3424
 
cc 	4	4

3425
 
cc   	5	3

3426
 
cc	2	5

3427
 
cc 	3	7

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

3429
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3430
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	

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

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

3433
 
name	name	n

3434
 
ccc	NULL	NULL

3435
 
bb	bb	1

3436
 
bb	bb 	6

3437
 
cc 	cc   	3

3438
 
cc 	cc	5

3439
 
cc 	cc 	7

3440
 
aa  	aa	2

3441
 
aa	aa	2

3442
 
DROP TABLE t1,t2;

3443
 
CREATE TABLE t1 (

3444
 
access_id int NOT NULL default '0',

3445
 
name varchar(20) default NULL,

3446
 
rank int NOT NULL default '0',

3447
 
KEY idx (access_id)

3448
 
);

3449
 
CREATE TABLE t2 (

3450
 
faq_group_id int NOT NULL default '0',

3451
 
faq_id int NOT NULL default '0',

3452
 
access_id int default NULL,

3453
 
UNIQUE KEY idx1 (faq_id),

3454
 
KEY idx2 (faq_group_id,faq_id)

3455
 
);

3456
 
INSERT INTO t1 VALUES 

3457
 
(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);

3458
 
INSERT INTO t2 VALUES

3459
 
(261,265,1),(490,494,1);

3460
 
SELECT t2.faq_id 

3461
 
FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)

3462
 
ON (t1.access_id = t2.access_id)

3463
 
LEFT JOIN t2 t

3464
 
ON (t.faq_group_id = t2.faq_group_id AND

3465
 
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))

3466
 
WHERE

3467
 
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);

3468
 
faq_id

3469
 
265

3470
 
SELECT t2.faq_id 

3471
 
FROM t1 INNER JOIN t2

3472
 
ON (t1.access_id = t2.access_id)

3473
 
LEFT JOIN t2 t

3474
 
ON (t.faq_group_id = t2.faq_group_id AND

3475
 
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))

3476
 
WHERE

3477
 
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);

3478
 
faq_id

3479
 
265

3480
 
DROP TABLE t1,t2;

3481
 
CREATE TABLE t1 (a INT, b INT, KEY inx (b,a));

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

3483
 
EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2

3484
 
ON ( f1.b=f2.b AND f1.a<f2.a ) 

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

3486
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3487
 
1	SIMPLE	f1	range	inx	inx	5	NULL	5	Using where; Using index

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

3489
 
DROP TABLE t1;

3490
 
CREATE TABLE t1 (c1 INT, c2 INT);

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

3492
 
EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2)))))))))))))))))))))))))))))))) > 0;

3493
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3494
 
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where

3495
 
31	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

3496
 
32	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

3497
 
EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) > 0;

3498
 
ERROR HY000: Too high level of nesting for select

3499
 
DROP TABLE t1;

3500
 
CREATE TABLE t1 (

3501
 
c1 int NOT NULL AUTO_INCREMENT,

3502
 
c2 varchar(1000) DEFAULT NULL,

3503
 
c3 bigint DEFAULT NULL,

3504
 
c4 bigint DEFAULT NULL,

3505
 
PRIMARY KEY (c1)

3506
 
);

3507
 
EXPLAIN EXTENDED 

3508
 
SELECT  join_2.c1  

3509
 
FROM 

3510
 
t1 AS join_0, 

3511
 
t1 AS join_1, 

3512
 
t1 AS join_2, 

3513
 
t1 AS join_3, 

3514
 
t1 AS join_4, 

3515
 
t1 AS join_5, 

3516
 
t1 AS join_6, 

3517
 
t1 AS join_7

3518
 
WHERE 

3519
 
join_0.c1=join_1.c1  AND 

3520
 
join_1.c1=join_2.c1  AND 

3521
 
join_2.c1=join_3.c1  AND 

3522
 
join_3.c1=join_4.c1  AND 

3523
 
join_4.c1=join_5.c1  AND 

3524
 
join_5.c1=join_6.c1  AND 

3525
 
join_6.c1=join_7.c1 

3526
 
OR 

3527
 
join_0.c2 < '?'  AND 

3528
 
join_1.c2 < '?'  AND

3529
 
join_2.c2 > '?'  AND

3530
 
join_2.c2 < '!'  AND

3531
 
join_3.c2 > '?'  AND 

3532
 
join_4.c2 = '?'  AND 

3533
 
join_5.c2 <> '?' AND

3534
 
join_6.c2 <> '?' AND 

3535
 
join_7.c2 >= '?' AND

3536
 
join_0.c1=join_1.c1  AND 

3537
 
join_1.c1=join_2.c1  AND 

3538
 
join_2.c1=join_3.c1  AND

3539
 
join_3.c1=join_4.c1  AND 

3540
 
join_4.c1=join_5.c1  AND 

3541
 
join_5.c1=join_6.c1  AND 

3542
 
join_6.c1=join_7.c1

3543
 
GROUP BY 

3544
 
join_3.c1,

3545
 
join_2.c1,

3546
 
join_7.c1,

3547
 
join_1.c1,

3548
 
join_0.c1;

3549
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

3550
 
1	SIMPLE	join_0	ALL	PRIMARY	NULL	NULL	NULL	0	0.00	Using temporary; Using filesort

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

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

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

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

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

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

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

3558
 
Warnings:

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

3560
 
SHOW WARNINGS;

3561
 
Level	Code	Message

3562
 
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`

3563
 
DROP TABLE t1;

3564
 
SELECT 1 AS ` `;

3565
 









3566



1










3567



Warnings:










3568



Warning	1474	Name ' ' has become ''










3569



SELECT 1 AS `  `;










3570












3571



1










3572



Warnings:










3573



Warning	1474	Name '  ' has become ''










3574



SELECT 1 AS ` x`;










3575



x










3576



1










3577



Warnings:










3578



Warning	1466	Leading spaces are removed from name ' x'










3579



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










3580



CREATE TABLE t2 (c21 INT NOT NULL, 










3581



c22 INT DEFAULT NULL, 










3582



KEY(c21, c22));










3583



CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0, 










3584



c32 INT DEFAULT NULL, 










3585



c33 INT NOT NULL, 










3586



c34 INT DEFAULT 0,










3587



KEY (c33, c34, c32));










3588



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










3589



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










3590



INSERT INTO t3 VALUES (1, 1, 1, 0), 










3591



(2, 2, 0, 0), 










3592



(3, 3, 1, 0), 










3593



(4, 4, 0, 0), 










3594



(5, 5, 1, 0);










3595



SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 










3596



t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 










3597



t3.c33 = 1 AND t2.c22 in (1, 3) 










3598



ORDER BY c32;










3599



c32










3600



1










3601



1










3602



3










3603



3










3604



5










3605



5










3606



SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 










3607



t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 










3608



t3.c33 = 1 AND t2.c22 in (1, 3) 










3609



ORDER BY c32 DESC;










3610



c32










3611



5










3612



5










3613



3










3614



3










3615



1










3616



1










3617



DROP TABLE t1, t2, t3;










3618



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










3619



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










3620



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










3621



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










3622



1










3623



Warnings:










3624



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










3625



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










3626



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










3627



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










3628



1










3629



Warnings:










3630



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










3631



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










3632



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










3633



1










3634



Warnings:










3635



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










3636



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










3637



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










3638



1










3639



Warnings:










3640



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










3641



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










3642



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










3643



1










3644



Warnings:










3645



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










3646



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










3647



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










3648



1










3649



Warnings:










3650



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










3651



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










3652



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










3653



1










3654



Warnings:










3655



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










3656



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










3657



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










3658



1










3659



Warnings:










3660



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










3661



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










3662



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










3663



1










3664



Warnings:










3665



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










3666



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










3667



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










3668



0










3669



Warnings:










3670



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










3671



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










3672



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










3673



1










3674



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










3675



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










3676



0










3677



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










3678



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










3679



1










3680



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










3681



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










3682



1










3683



Warnings:










3684



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










3685



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










3686



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










3687



0










3688



Warnings:










3689



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










3690



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










3691



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










3692



1










3693



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










3694



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










3695



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










3696



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










3697



1










3698



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










3699



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










3700



1










3701



Warnings:










3702



Warning	1292	Truncated incorrect datetime value: ''










3703



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










3704



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










3705



0










3706



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










3707



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










3708



0










3709



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










3710



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










3711



NULL










3712



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










3713



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










3714



0










3715



Warnings:










3716



Warning	1292	Truncated incorrect datetime value: ''










3717



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










3718



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










3719



0










3720



Warnings:










3721



Warning	1292	Truncated incorrect date value: '1'










3722



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










3723



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










3724



0










3725



Warnings:










3726



Warning	1292	Truncated incorrect date value: '1'










3727



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










3728



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










3729



0










3730



Warnings:










3731



Warning	1292	Truncated incorrect date value: ''










3732



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










3733



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










3734



0










3735



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










3736



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










3737



0










3738



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










3739



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










3740



0










3741












3742



#










3743



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










3744



# Inserting Data.










3745



#










3746



DROP TABLE IF EXISTS t1;










3747



DROP TABLE IF EXISTS t2;










3748












3749



CREATE TABLE t1(










3750



c1 DECIMAL(10, 2),










3751



c2 FLOAT);










3752












3753



INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5);










3754












3755



CREATE TABLE t2(










3756



c3 DECIMAL(10, 2))










3757



SELECT










3758



c1 * c2 AS c3










3759



FROM t1;










3760












3761



SELECT * FROM t1;










3762



c1	c2










3763



0.00	1










3764



2.00	3










3765



4.00	5










3766












3767



SELECT * FROM t2;










3768



c3










3769



0.00










3770



6.00










3771



20.00










3772












3773



DROP TABLE t1;










3774



DROP TABLE t2;










3775












3776



CREATE TABLE t1 (c1 BIGINT NOT NULL);










3777



INSERT INTO t1 (c1) VALUES (1);










3778



SELECT * FROM t1 WHERE c1 > NULL + 1;










3779



c1










3780



DROP TABLE t1;










3781












3782



CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY);










3783



INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0');










3784



SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar');










3785



a










3786



foo0










3787



DROP TABLE t1;










3788



CREATE TABLE t1 (a INT, b INT);










3789



CREATE TABLE t2 (a INT, c INT, KEY(a));










3790



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










3791



INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),










3792



(2, 1), (2, 2), (2, 3), (2, 4), (2, 5),










3793



(3, 1), (3, 2), (3, 3), (3, 4), (3, 5),










3794



(4, 1), (4, 2), (4, 3), (4, 4), (4, 5);










3795



FLUSH STATUS;










3796



SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;










3797



b










3798



1










3799



2










3800



SHOW STATUS LIKE 'Handler_read%';










3801



Variable_name	Value










3802



Handler_read_first	0










3803



Handler_read_key	0










3804



Handler_read_next	0










3805



Handler_read_prev	0










3806



Handler_read_rnd	0










3807



Handler_read_rnd_next	3










3808



DROP TABLE t1, t2;










3809



End of 5.0 tests










3810



create table t1(a INT, KEY (a));










3811



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










3812



SELECT a FROM t1 ORDER BY a LIMIT 2;










3813



a










3814



1










3815



2










3816



SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;










3817



a










3818



3










3819



4










3820



5










3821



SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;










3822



a










3823



3










3824



4










3825



5










3826



DROP TABLE t1;