← Back to branch summary

~drizzle-trunk/drizzle/development

1 by brian
clean slate 
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 (
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
4
 
Period int DEFAULT '0' NOT NULL,

5
 
Varor_period int DEFAULT '0' NOT NULL
1 by brian
clean slate 
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,
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
19
 
fld1 int DEFAULT '0' NOT NULL,

20
 
companynr int DEFAULT '0' NOT NULL,
1 by brian
clean slate 
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
208 by Brian Aker
Removed zerofil syntax (not internals). 
137
 
68305	Colombo

138
 
0	nondecreasing
1 by brian
clean slate 
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
208 by Brian Aker
Removed zerofil syntax (not internals). 
159
 
12001	flanking

160
 
13602	foldout

161
 
13606	fingerings

162
 
18007	fanatic

163
 
18017	featherweight

164
 
18054	fetters

165
 
18103	flint

166
 
18104	flopping
1 by brian
clean slate 
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
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
185
 
36002	funereal

186
 
38017	fetched

187
 
38205	firearm

188
 
58004	Fenton

189
 
88303	feminine
1 by brian
clean slate 
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
208 by Brian Aker
Removed zerofil syntax (not internals). 
211
 
0
1 by brian
clean slate 
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
208 by Brian Aker
Removed zerofil syntax (not internals). 
225
 
0
1 by brian
clean slate 
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
208 by Brian Aker
Removed zerofil syntax (not internals). 
250
 
0
1 by brian
clean slate 
251
 
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";

252
 
fld3	period
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
253
 
obliterates	9410
201 by Brian Aker
Convert default engine to Innodb 
254
 
offload	9410
1 by brian
clean slate 
255
 
opaquely	9410

256
 
organizer	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
257
 
overestimating	9410

258
 
overlay	9410
1 by brian
clean slate 
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
201 by Brian Aker
Convert default engine to Innodb 
333
 
Omaha
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
334
 
Romans

335
 
admonishing

336
 
astound

337
 
bewilderingly
201 by Brian Aker
Convert default engine to Innodb 
338
 
breaking
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
339
 
combed

340
 
flanking
201 by Brian Aker
Convert default engine to Innodb 
341
 
intercepted

342
 
sumac
1 by brian
clean slate 
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
201 by Brian Aker
Convert default engine to Innodb 
423
 
abr
1 by brian
clean slate 
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
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
445
 
Ado

446
 
Aug
201 by Brian Aker
Convert default engine to Innodb 
447
 
adm
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
448
 
afi

449
 
agi

450
 
amm
201 by Brian Aker
Convert default engine to Innodb 
451
 
ann
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
452
 
ast

453
 
att
201 by Brian Aker
Convert default engine to Innodb 
454
 
aud
1 by brian
clean slate 
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
 
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;

485
 
namn

486
 
Abraham Abraham

487
 
abrogating abrogating

488
 
admonishing admonishing

489
 
Adolph Adolph

490
 
afield afield

491
 
aging aging

492
 
ammonium ammonium

493
 
analyzable analyzable

494
 
animals animals

495
 
animized animized

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

497
 
concat(fld3," ",fld3)

498
 
Abraham Abraham

499
 
abrogating abrogating

500
 
admonishing admonishing

501
 
Adolph Adolph

502
 
afield afield

503
 
aging aging

504
 
ammonium ammonium

505
 
analyzable analyzable

506
 
animals animals

507
 
animized animized

508
 
select distinct fld5 from t2 limit 10;

509
 
fld5

510
 
neat

511
 
Steinberg

512
 
jarring

513
 
tinily

514
 
balled

515
 
persist

516
 
attainments

517
 
fanatic

518
 
measures

519
 
rightfulness

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

521
 
fld3	count(*)

522
 
affixed	1

523
 
and	1

524
 
annoyers	1

525
 
Anthony	1

526
 
assayed	1

527
 
assurers	1

528
 
attendants	1

529
 
bedlam	1

530
 
bedpost	1

531
 
boasted	1

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

533
 
fld3	count(*)

534
 
affixed	1

535
 
and	1

536
 
annoyers	1

537
 
Anthony	1

538
 
assayed	1

539
 
assurers	1

540
 
attendants	1

541
 
bedlam	1

542
 
bedpost	1

543
 
boasted	1

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

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

546
 
circus	aaaaaa	1

547
 
cited	aaaaa	1

548
 
Colombo	aaaaaaa	1

549
 
congresswoman	aaaaaaaaaaaaa	1

550
 
contrition	aaaaaaaaaa	1

551
 
corny	aaaaa	1

552
 
cultivation	aaaaaaaaaaa	1

553
 
definiteness	aaaaaaaaaaaa	1

554
 
demultiplex	aaaaaaaaaaa	1

555
 
disappointing	aaaaaaaaaaaaa	1

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

557
 
companynr	rtrim(space(512+companynr))

558
 
37	

559
 
78	

560
 
101	

561
 
154	

562
 
311	

563
 
447	

564
 
512	

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

566
 
fld3

567
 
select period from t1;

568
 
period

569
 
9410

570
 
select period from t1 where period=1900;

571
 
period

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

573
 
fld3	period

574
 
breaking	9410

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

576
 
fld3	period

577
 
breaking	1001

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

579
 
fld3	period
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
580
 
Abraham	9410

581
 
Aden	9410

582
 
Adolph	9410

583
 
Aldrich	9410

584
 
Alison	9410

585
 
Anatole	9410

586
 
Antarctica	9410

587
 
Antares	9410

588
 
Arabia	9410

589
 
Artemia	9410

590
 
Augustine	9410

591
 
Baird	9410

592
 
Beebe	9410

593
 
Butterfield	9410

594
 
CERN	9410

595
 
Cassites	9410

596
 
Chicana	9410

597
 
Chippewa	9410

598
 
Clayton	9410

599
 
Conley	9410

600
 
Connally	9410

601
 
Crays	9410

602
 
DiMaggio	9410

603
 
Dutchman	9410
1 by brian
clean slate 
604
 
Eulerian	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
605
 
Evanston	9410

606
 
Everhart	9410

607
 
Fenton	9410

608
 
Fitzpatrick	9410

609
 
Galatean	9410

610
 
Gandhian	9410

611
 
Ganymede	9410

612
 
Goldstine	9410

613
 
Gothicism	9410

614
 
Graves	9410

615
 
Greenberg	9410

616
 
Gurkha	9410

617
 
Hawaii	9410

618
 
Hegelian	9410

619
 
Hornblower	9410

620
 
Huffman	9410

621
 
Hunter	9410

622
 
Joplin	9410

623
 
Judas	9410
1 by brian
clean slate 
624
 
Kane	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
625
 
Kantian	9410

626
 
Kevin	9410

627
 
Kinsey	9410

628
 
Kline	9410

629
 
Lars	9410

630
 
Latinizes	9410

631
 
Lillian	9410

632
 
Lizzy	9410

633
 
Majorca	9410

634
 
Manhattanize	9410

635
 
McGovern	9410

636
 
Melinda	9410

637
 
Merritt	9410

638
 
Micronesia	9410

639
 
Miles	9410

640
 
Miltonism	9410

641
 
Nabisco	9410

642
 
Nazis	9410

643
 
Newtonian	9410

644
 
Norwalk	9410

645
 
Pandora	9410

646
 
Parsifal	9410

647
 
Peruvian	9410

648
 
Punjab	9410

649
 
Pyle	9410

650
 
Quixotism	9410

651
 
Romano	9410

652
 
Romans	9410

653
 
Sabine	9410

654
 
Sault	9410

655
 
Saxony	9410

656
 
Selfridge	9410

657
 
Shanghais	9410

658
 
Simla	9410

659
 
Simon	9410

660
 
Stalin	9410

661
 
Steinberg	9410

662
 
Taoism	9410

663
 
Teresa	9410

664
 
Tipperary	9410

665
 
Weissmuller	9410

666
 
Winsett	9410
1 by brian
clean slate 
667
 
Wotan	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
668
 
abates	9410

669
 
abrogating	9410

670
 
accessed	9410

671
 
admiring	9410

672
 
admonishing	9410

673
 
afield	9410

674
 
afore	9410
1 by brian
clean slate 
675
 
aging	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
676
 
airships	9410

677
 
alike	9410

678
 
allot	9410

679
 
already	9410

680
 
amenities	9410
1 by brian
clean slate 
681
 
ammonium	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
682
 
analogy	9410

683
 
analyzable	9410

684
 
animals	9410

685
 
animized	9410

686
 
annihilates	9410

687
 
announced	9410

688
 
announces	9410

689
 
apiary	9410

690
 
appendixes	9410

691
 
appendixes	9410

692
 
appendixes	9410

693
 
appendixes	9410

694
 
appendixes	9410

695
 
appendixes	9410

696
 
arriving	9410

697
 
arteriole	9410

698
 
assails	9410

699
 
astound	9410

700
 
attainments	9410

701
 
attrition	9410
1 by brian
clean slate 
702
 
audiology	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
703
 
avenge	9410

704
 
avoidable	9410

705
 
babies	9410

706
 
babysitting	9410
1 by brian
clean slate 
707
 
balled	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
708
 
beaner	9410

709
 
beaters	9410

710
 
bee	9410

711
 
befouled	9410
1 by brian
clean slate 
712
 
bellow	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
713
 
bestseller	9410

714
 
betroth	9410

715
 
bewilderingly	9410
1 by brian
clean slate 
716
 
bills	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
717
 
bitterroot	9410
1 by brian
clean slate 
718
 
bivalves	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
719
 
bloater	9410
1 by brian
clean slate 
720
 
bloodbath	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
721
 
boat	9410

722
 
boom	9410

723
 
boorish	9410

724
 
boulder	9410

725
 
breaking	9410

726
 
brunch	9410

727
 
buckboards	9410

728
 
burlesque	9410

729
 
cage	9410

730
 
capably	9410

731
 
capped	9410

732
 
cascade	9410

733
 
causality	9410

734
 
cautioned	9410

735
 
ceiling	9410

736
 
celery	9410

737
 
certificates	9410
1 by brian
clean slate 
738
 
chafe	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
739
 
chaperone	9410

740
 
charges	9410

741
 
chasm	9410

742
 
checkpoints	9410

743
 
chewing	9410

744
 
chews	9410

745
 
chillingly	9410

746
 
chronicle	9410

747
 
ciphers	9410

748
 
civics	9410

749
 
clamored	9410

750
 
clenched	9410

751
 
clockers	9410
1 by brian
clean slate 
752
 
coexist	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
753
 
cokes	9410

754
 
combed	9410

755
 
coming	9410

756
 
commencements	9410

757
 
commonplace	9410

758
 
communicants	9410

759
 
compartment	9410

760
 
comprehensive	9410

761
 
comprised	9410

762
 
conceptions	9410

763
 
concludes	9410

764
 
congregates	9410

765
 
contrary	9410

766
 
contrasted	9410

767
 
convenient	9410
1 by brian
clean slate 
768
 
convulsion	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
769
 
corset	9410

770
 
count	9410

771
 
coverings	9410

772
 
craziness	9410

773
 
creak	9410

774
 
creek	9410

775
 
critiques	9410

776
 
crunches	9410

777
 
culled	9410

778
 
cult	9410

779
 
cupboard	9410

780
 
cured	9410

781
 
cute	9410

782
 
daughter	9410

783
 
decliner	9410

784
 
decomposition	9410

785
 
deductions	9410

786
 
dehydrate	9410

787
 
deludes	9410
1 by brian
clean slate 
788
 
denizen	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
789
 
denotative	9410

790
 
denounces	9410

791
 
dental	9410

792
 
dentally	9410

793
 
descendants	9410

794
 
despot	9410

795
 
destroyer	9410

796
 
detectably	9410

797
 
dialysis	9410
1 by brian
clean slate 
798
 
dimensions	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
799
 
disable	9410

800
 
discounts	9410
1 by brian
clean slate 
801
 
disentangle	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
802
 
disobedience	9410

803
 
dissociate	9410

804
 
dogging	9410

805
 
dopers	9410
1 by brian
clean slate 
806
 
drains	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
807
 
dreaded	9410

808
 
ducks	9410

809
 
dusted	9410

810
 
effortlessly	9410

811
 
electroencephalography	9410

812
 
elite	9410

813
 
embassies	9410

814
 
employing	9410

815
 
encompass	9410

816
 
encompasses	9410

817
 
environing	9410

818
 
epistle	9410

819
 
equilibrium	9410

820
 
erases	9410

821
 
error	9410

822
 
eschew	9410

823
 
eternal	9410
1 by brian
clean slate 
824
 
evened	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
825
 
evenhandedly	9410

826
 
eventful	9410

827
 
excises	9410

828
 
exclamation	9410

829
 
excrete	9410

830
 
exhausts	9410

831
 
expelled	9410

832
 
extents	9410
1 by brian
clean slate 
833
 
externally	9410

834
 
extracted	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
835
 
faithful	9410

836
 
fanatic	9410

837
 
fated	9410

838
 
featherweight	9410

839
 
feed	9410
1 by brian
clean slate 
840
 
feminine	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
841
 
fetched	9410

842
 
fetters	9410

843
 
fiftieth	9410

844
 
filial	9410

845
 
fingerings	9410

846
 
finishers	9410

847
 
firearm	9410

848
 
fitting	9410

849
 
fixedly	9410

850
 
flanking	9410

851
 
flint	9410

852
 
flopping	9410

853
 
flurried	9410

854
 
foldout	9410

855
 
foothill	9410

856
 
forgivably	9410

857
 
forthcoming	9410
1 by brian
clean slate 
858
 
freakish	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
859
 
freest	9410

860
 
freezes	9410

861
 
funereal	9410

862
 
furnishings	9410

863
 
furthermore	9410

864
 
gadfly	9410

865
 
gainful	9410

866
 
galling	9410

867
 
garage	9410
1 by brian
clean slate 
868
 
gentleman	9410

869
 
gifted	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
870
 
gleaning	9410

871
 
glut	9410

872
 
goblins	9410

873
 
governing	9410

874
 
gradually	9410

875
 
grazing	9410

876
 
gritty	9410

877
 
groupings	9410

878
 
guides	9410

879
 
guitars	9410

880
 
handgun	9410

881
 
handy	9410

882
 
heiress	9410

883
 
hoarder	9410

884
 
honoring	9410

885
 
hostess	9410

886
 
humanness	9410

887
 
humiliation	9410

888
 
humility	9410

889
 
hushes	9410

890
 
husky	9410

891
 
hypothesizer	9410
1 by brian
clean slate 
892
 
icon	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
893
 
ideas	9410

894
 
impelling	9410

895
 
impending	9410

896
 
imperial	9410

897
 
imperiously	9410

898
 
imprint	9410

899
 
impulsive	9410

900
 
inaccuracy	9410

901
 
inch	9410
1 by brian
clean slate 
902
 
incidentals	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
903
 
incorrectly	9410

904
 
incurring	9410

905
 
index	9410

906
 
indulge	9410

907
 
indulgences	9410

908
 
ineffective	9410

909
 
infallibly	9410

910
 
infest	9410

911
 
inform	9410

912
 
inmate	9410

913
 
insolence	9410

914
 
instruments	9410

915
 
intelligibility	9410

916
 
intentness	9410

917
 
intercepted	9410

918
 
interdependent	9410

919
 
interrelationships	9410

920
 
interrogate	9410

921
 
investigations	9410

922
 
irresponsibly	9410

923
 
jarring	9410

924
 
journalizing	9410

925
 
juveniles	9410

926
 
kanji	9410

927
 
kingdom	9410

928
 
kiting	9410

929
 
labeled	9410

930
 
languages	9410

931
 
laterally	9410

932
 
lawgiver	9410

933
 
leaflet	9410

934
 
leavings	9410

935
 
lectured	9410

936
 
leftover	9410

937
 
lewdly	9410

938
 
lied	9410

939
 
linear	9410

940
 
lists	9410

941
 
lithograph	9410

942
 
lore	9410

943
 
luckily	9410

944
 
males	9410

945
 
marginal	9410

946
 
mastering	9410

947
 
mayoral	9410

948
 
meanwhile	9410

949
 
measures	9410

950
 
measures	9410

951
 
mechanizing	9410

952
 
medical	9410

953
 
meditation	9410

954
 
metaphysically	9410

955
 
mineral	9410

956
 
miniaturizes	9410

957
 
minima	9410

958
 
minion	9410

959
 
minting	9410

960
 
misted	9410

961
 
misunderstander	9410

962
 
mixture	9410

963
 
motors	9410

964
 
mournfulness	9410

965
 
multilayer	9410
1 by brian
clean slate 
966
 
mumbles	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
967
 
mushrooms	9410

968
 
mystic	9410

969
 
navies	9410

970
 
navigate	9410

971
 
neat	9410

972
 
neonatal	9410

973
 
nested	9410

974
 
noncritical	9410

975
 
normalizes	9410

976
 
obliterates	9410

977
 
offload	9410

978
 
opaquely	9410

979
 
organizer	9410

980
 
overestimating	9410

981
 
overlay	9410
1 by brian
clean slate 
982
 
parametrized	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
983
 
parenthood	9410

984
 
parters	9410

985
 
participated	9410

986
 
partridges	9410

987
 
peacock	9410

988
 
peeked	9410

989
 
pellagra	9410

990
 
percentage	9410

991
 
percentage	9410

992
 
persist	9410

993
 
perturb	9410

994
 
pessimist	9410

995
 
pests	9410

996
 
petted	9410

997
 
pictures	9410

998
 
pithed	9410

999
 
pityingly	9410

1000
 
poison	9410

1001
 
posed	9410

1002
 
positioning	9410

1003
 
postulation	9410

1004
 
praised	9410

1005
 
precaution	9410

1006
 
precipitable	9410

1007
 
preclude	9410

1008
 
presentation	9410

1009
 
pressure	9410

1010
 
previewing	9410

1011
 
priceless	9410

1012
 
primary	9410

1013
 
psychic	9410

1014
 
publicly	9410

1015
 
puddings	9410

1016
 
quagmire	9410

1017
 
quitter	9410

1018
 
railway	9410

1019
 
raining	9410

1020
 
rains	9410

1021
 
ravines	9410

1022
 
readable	9410

1023
 
realized	9410

1024
 
realtor	9410

1025
 
reassigned	9410

1026
 
recruited	9410

1027
 
reduce	9410

1028
 
regimented	9410
1 by brian
clean slate 
1029
 
registration	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1030
 
relatively	9410

1031
 
relaxing	9410

1032
 
relishing	9410

1033
 
relives	9410

1034
 
renew	9410

1035
 
repelled	9410

1036
 
repetitions	9410

1037
 
reporters	9410

1038
 
reporters	9410

1039
 
repressions	9410

1040
 
resplendent	9410

1041
 
resumes	9410

1042
 
rifles	9410

1043
 
rightful	9410

1044
 
rightfully	9410

1045
 
rightfulness	9410

1046
 
ripeness	9410

1047
 
riser	9410

1048
 
roped	9410

1049
 
rudeness	9410

1050
 
rules	9410

1051
 
rural	9410

1052
 
rusting	9410
1 by brian
clean slate 
1053
 
sadly	9410

1054
 
sags	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1055
 
sanding	9410

1056
 
saplings	9410

1057
 
sating	9410
1 by brian
clean slate 
1058
 
save	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1059
 
sawtooth	9410

1060
 
scarf	9410

1061
 
scatterbrain	9410

1062
 
scheduling	9410

1063
 
schemer	9410

1064
 
scholastics	9410

1065
 
scornfully	9410

1066
 
secures	9410

1067
 
securing	9410

1068
 
seminaries	9410

1069
 
serializations	9410
1 by brian
clean slate 
1070
 
serpents	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1071
 
serving	9410

1072
 
severely	9410

1073
 
sews	9410

1074
 
shapelessly	9410

1075
 
shipyard	9410

1076
 
shooter	9410
1 by brian
clean slate 
1077
 
similarities	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1078
 
skulking	9410

1079
 
slaughter	9410

1080
 
sloping	9410
1 by brian
clean slate 
1081
 
smoothed	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1082
 
snatching	9410

1083
 
socializes	9410

1084
 
sophomore	9410

1085
 
sorters	9410

1086
 
spatial	9410

1087
 
specification	9410

1088
 
specifics	9410

1089
 
spongers	9410

1090
 
spools	9410

1091
 
sportswriting	9410

1092
 
sporty	9410

1093
 
squabbled	9410

1094
 
squeaking	9410

1095
 
squeezes	9410

1096
 
stabilizes	9410

1097
 
stairway	9410

1098
 
standardizes	9410

1099
 
star	9410

1100
 
starlet	9410

1101
 
stated	9410

1102
 
stint	9410
1 by brian
clean slate 
1103
 
stodgy	9410

1104
 
store	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1105
 
straight	9410

1106
 
stranglings	9410

1107
 
subdirectory	9410

1108
 
subjective	9410

1109
 
subschema	9410

1110
 
succumbed	9410

1111
 
suites	9410

1112
 
sumac	9410

1113
 
sureties	9410

1114
 
swaying	9410

1115
 
sweetish	9410

1116
 
swelling	9410

1117
 
syndicate	9410
1 by brian
clean slate 
1118
 
taxonomically	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1119
 
techniques	9410

1120
 
teem	9410

1121
 
teethe	9410

1122
 
tempering	9410

1123
 
terminal	9410

1124
 
terminator	9410

1125
 
terminators	9410

1126
 
test	9410

1127
 
testicle	9410

1128
 
textures	9410

1129
 
theorizers	9410

1130
 
throttles	9410
1 by brian
clean slate 
1131
 
tidiness	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1132
 
timesharing	9410

1133
 
tinily	9410

1134
 
tinting	9410

1135
 
title	9410

1136
 
tragedies	9410

1137
 
traitor	9410

1138
 
trimmings	9410
1 by brian
clean slate 
1139
 
tropics	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1140
 
unaffected	9410

1141
 
uncovering	9410

1142
 
undoes	9410

1143
 
ungrateful	9410

1144
 
universals	9410

1145
 
unplug	9410

1146
 
unruly	9410

1147
 
untying	9410

1148
 
unwilling	9410

1149
 
vacuuming	9410

1150
 
validate	9410

1151
 
vanish	9410

1152
 
ventilate	9410

1153
 
veranda	9410

1154
 
vests	9410

1155
 
wallet	9410

1156
 
waltz	9410

1157
 
warm	9410
1 by brian
clean slate 
1158
 
warningly	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1159
 
watering	9410
1 by brian
clean slate 
1160
 
weasels	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1161
 
western	9410

1162
 
whiteners	9410
1 by brian
clean slate 
1163
 
widens	9410
496.1.4 by Paul McCullagh
Changes to .result files to run both PBXT and InnoDB 
1164
 
witchcraft	9410

1165
 
workers	9410

1166
 
yelped	9410
1 by brian
clean slate 
1167
 
youthfulness	9410

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

1169
 
fld3	period	price	price2

1170
 
admonishing	1002	28357832	8723648

1171
 
analyzable	1002	28357832	8723648

1172
 
annihilates	1001	5987435	234724

1173
 
Antares	1002	28357832	8723648

1174
 
astound	1001	5987435	234724

1175
 
audiology	1001	5987435	234724

1176
 
Augustine	1002	28357832	8723648

1177
 
Baird	1002	28357832	8723648

1178
 
bewilderingly	1001	5987435	234724

1179
 
breaking	1001	5987435	234724

1180
 
Conley	1001	5987435	234724

1181
 
dentally	1002	28357832	8723648

1182
 
dissociate	1002	28357832	8723648

1183
 
elite	1001	5987435	234724

1184
 
eschew	1001	5987435	234724

1185
 
Eulerian	1001	5987435	234724

1186
 
flanking	1001	5987435	234724

1187
 
foldout	1002	28357832	8723648

1188
 
funereal	1002	28357832	8723648

1189
 
galling	1002	28357832	8723648

1190
 
Graves	1001	5987435	234724

1191
 
grazing	1001	5987435	234724

1192
 
groupings	1001	5987435	234724

1193
 
handgun	1001	5987435	234724

1194
 
humility	1002	28357832	8723648

1195
 
impulsive	1002	28357832	8723648

1196
 
inch	1001	5987435	234724

1197
 
intelligibility	1001	5987435	234724

1198
 
jarring	1001	5987435	234724

1199
 
lawgiver	1001	5987435	234724

1200
 
lectured	1002	28357832	8723648

1201
 
Merritt	1002	28357832	8723648

1202
 
neonatal	1001	5987435	234724

1203
 
offload	1002	28357832	8723648

1204
 
parters	1002	28357832	8723648

1205
 
pityingly	1002	28357832	8723648

1206
 
puddings	1002	28357832	8723648

1207
 
Punjab	1001	5987435	234724

1208
 
quitter	1002	28357832	8723648

1209
 
realtor	1001	5987435	234724

1210
 
relaxing	1001	5987435	234724

1211
 
repetitions	1001	5987435	234724

1212
 
resumes	1001	5987435	234724

1213
 
Romans	1002	28357832	8723648

1214
 
rusting	1001	5987435	234724

1215
 
scholastics	1001	5987435	234724

1216
 
skulking	1002	28357832	8723648

1217
 
stated	1002	28357832	8723648

1218
 
suites	1002	28357832	8723648

1219
 
sureties	1001	5987435	234724

1220
 
testicle	1002	28357832	8723648

1221
 
tinily	1002	28357832	8723648

1222
 
tragedies	1001	5987435	234724

1223
 
trimmings	1001	5987435	234724

1224
 
vacuuming	1001	5987435	234724

1225
 
ventilate	1001	5987435	234724

1226
 
wallet	1001	5987435	234724

1227
 
Weissmuller	1002	28357832	8723648

1228
 
Wotan	1002	28357832	8723648

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

1230
 
fld1	fld3	period	price	price2
208 by Brian Aker
Removed zerofil syntax (not internals). 
1231
 
18201	relaxing	1001	5987435	234724

1232
 
18601	vacuuming	1001	5987435	234724

1233
 
18801	inch	1001	5987435	234724

1234
 
18811	repetitions	1001	5987435	234724
1063.9.24 by Stewart Smith
select.test for MyISAM as temp only 
1235
 
create temporary table t4 (
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
1236
 
companynr int NOT NULL default '0',
1 by brian
clean slate 
1237
 
companyname char(30) NOT NULL default '',

1238
 
PRIMARY KEY (companynr),

1239
 
UNIQUE KEY companyname(companyname)
1117.1.9 by Brian Aker
Merge my branch from Stewart into one branch 
1240
 
) ENGINE=MyISAM COMMENT='companynames';
1 by brian
clean slate 
1241
 
select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;

1242
 
companynr	companyname
208 by Brian Aker
Removed zerofil syntax (not internals). 
1243
 
0	Unknown
1 by brian
clean slate 
1244
 
29	company 1

1245
 
34	company 2

1246
 
36	company 3

1247
 
37	company 4

1248
 
40	company 5

1249
 
41	company 6

1250
 
50	company 11

1251
 
53	company 7

1252
 
58	company 8

1253
 
65	company 9

1254
 
68	company 10

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

1256
 
companynr	companyname
208 by Brian Aker
Removed zerofil syntax (not internals). 
1257
 
0	Unknown
1 by brian
clean slate 
1258
 
29	company 1

1259
 
34	company 2

1260
 
36	company 3

1261
 
37	company 4

1262
 
40	company 5

1263
 
41	company 6

1264
 
50	company 11

1265
 
53	company 7

1266
 
58	company 8

1267
 
65	company 9

1268
 
68	company 10

1269
 
select * from t1,t1 t12;

1270
 
Period	Varor_period	Period	Varor_period

1271
 
9410	9412	9410	9412

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

1273
 
fld1	fld1

1274
 
250501	250501
1718.2.1 by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) 
1275
 
250501	250502

1276
 
250501	250503

1277
 
250501	250504

1278
 
250501	250505
1 by brian
clean slate 
1279
 
250502	250501
1718.2.1 by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) 
1280
 
250502	250502

1281
 
250502	250503

1282
 
250502	250504

1283
 
250502	250505
1 by brian
clean slate 
1284
 
250503	250501
1718.2.1 by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) 
1285
 
250503	250502

1286
 
250503	250503

1287
 
250503	250504

1288
 
250503	250505
1 by brian
clean slate 
1289
 
250504	250501
1718.2.1 by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) 
1290
 
250504	250502

1291
 
250504	250503

1292
 
250504	250504

1293
 
250504	250505
1 by brian
clean slate 
1294
 
250505	250501

1295
 
250505	250502

1296
 
250505	250503

1297
 
250505	250504

1298
 
250505	250505

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

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

1301
 
companynr	companyname

1302
 
99	NULL

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

1304
 
count(*)

1305
 
1199

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

1307
 
companynr	companyname

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

1309
 
count(*)

1310
 
1200

1311
 
delete from t2 where fld1=999999;

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

1313
 
companynr	companynr

1314
 
37	36

1315
 
41	40

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

1317
 
fld1	companynr	fld3	period
208 by Brian Aker
Removed zerofil syntax (not internals). 
1318
 
38008	37	reporters	1008

1319
 
38208	37	Selfridge	1008
1 by brian
clean slate 
1320
 
select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;

1321
 
fld1	companynr	fld3	period
208 by Brian Aker
Removed zerofil syntax (not internals). 
1322
 
38008	37	reporters	1008

1323
 
38208	37	Selfridge	1008
1 by brian
clean slate 
1324
 
select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;

1325
 
fld1	companynr	fld3	period
208 by Brian Aker
Removed zerofil syntax (not internals). 
1326
 
38008	37	reporters	1008

1327
 
38208	37	Selfridge	1008
1 by brian
clean slate 
1328
 
select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909);

1329
 
period

1330
 
9410

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

1332
 
period

1333
 
9410

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

1335
 
fld1

1336
 
250501

1337
 
250502

1338
 
250503

1339
 
250505

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

1341
 
fld1

1342
 
250502

1343
 
250503

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

1345
 
fld1

1346
 
250502

1347
 
250503

1348
 
250504

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

1350
 
fld3

1351
 
label

1352
 
labeled

1353
 
labeled

1354
 
landslide

1355
 
laterally

1356
 
leaflet

1357
 
lewdly

1358
 
Lillian

1359
 
luckily

1360
 
select count(*) from t1;

1361
 
count(*)

1362
 
1

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

1364
 
companynr	count(*)	sum(fld1)
208 by Brian Aker
Removed zerofil syntax (not internals). 
1365
 
0	82	10355753
1 by brian
clean slate 
1366
 
29	95	14473298

1367
 
34	70	17788966

1368
 
36	215	22786296

1369
 
37	588	83602098

1370
 
40	37	6618386

1371
 
41	52	12816335

1372
 
50	11	1595438

1373
 
53	4	793210

1374
 
58	23	2254293

1375
 
65	10	2284055

1376
 
68	12	3097288

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

1378
 
companynr	count(*)

1379
 
68	12

1380
 
65	10

1381
 
58	23

1382
 
53	4

1383
 
50	11

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

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

1386
 
70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069

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

1388
 
companynr	count(*)	min(fld4)	max(fld4)	sum(fld1)	avg(fld1)	std(fld1)	variance(fld1)
208 by Brian Aker
Removed zerofil syntax (not internals). 
1389
 
0	82	Anthony	windmills	10355753	126289.6707	115550.9757	13352027981.7087
1 by brian
clean slate 
1390
 
29	95	abut	wetness	14473298	152350.5053	8368.5480	70032594.9026

1391
 
34	70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069

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

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

1394
 
37	1	1	5987435	5987435	5987435	5987435.0000

1395
 
37	2	1	28357832	28357832	28357832	28357832.0000

1396
 
37	3	1	39654943	39654943	39654943	39654943.0000

1397
 
37	11	1	5987435	5987435	5987435	5987435.0000

1398
 
37	12	1	28357832	28357832	28357832	28357832.0000

1399
 
37	13	1	39654943	39654943	39654943	39654943.0000

1400
 
37	21	1	5987435	5987435	5987435	5987435.0000

1401
 
37	22	1	28357832	28357832	28357832	28357832.0000

1402
 
37	23	1	39654943	39654943	39654943	39654943.0000

1403
 
37	31	1	5987435	5987435	5987435	5987435.0000

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

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

1406
 
37	1	1	5987435	5987435	5987435	5987435.0000

1407
 
37	2	1	28357832	28357832	28357832	28357832.0000

1408
 
37	3	1	39654943	39654943	39654943	39654943.0000

1409
 
37	11	1	5987435	5987435	5987435	5987435.0000

1410
 
37	12	1	28357832	28357832	28357832	28357832.0000

1411
 
37	13	1	39654943	39654943	39654943	39654943.0000

1412
 
37	21	1	5987435	5987435	5987435	5987435.0000

1413
 
37	22	1	28357832	28357832	28357832	28357832.0000

1414
 
37	23	1	39654943	39654943	39654943	39654943.0000

1415
 
37	31	1	5987435	5987435	5987435	5987435.0000

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

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

1418
 
37	12543	309394878010	5987435	39654943	24666736.6667

1419
 
78	8362	414611089292	726498	98439034	49582766.0000

1420
 
101	4181	3489454238	834598	834598	834598.0000

1421
 
154	4181	4112197254950	983543950	983543950	983543950.0000

1422
 
311	4181	979599938	234298	234298	234298.0000

1423
 
447	4181	9929180954	2374834	2374834	2374834.0000

1424
 
512	4181	3288532102	786542	786542	786542.0000

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

1426
 
mod(companynr,10)

1427
 
0

1428
 
9

1429
 
4

1430
 
6

1431
 
7

1432
 
1

1433
 
3

1434
 
8

1435
 
5

1436
 
select distinct 1 from t4 group by companynr;

1437
 
1

1438
 
1

1439
 
select count(distinct fld1) from t2;

1440
 
count(distinct fld1)

1441
 
1199

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

1443
 
companynr	count(distinct fld1)
208 by Brian Aker
Removed zerofil syntax (not internals). 
1444
 
0	82
1 by brian
clean slate 
1445
 
29	95

1446
 
34	70

1447
 
36	215

1448
 
37	588

1449
 
40	37

1450
 
41	52

1451
 
50	11

1452
 
53	4

1453
 
58	23

1454
 
65	10

1455
 
68	12

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

1457
 
companynr	count(*)
208 by Brian Aker
Removed zerofil syntax (not internals). 
1458
 
0	82
1 by brian
clean slate 
1459
 
29	95

1460
 
34	70

1461
 
36	215

1462
 
37	588

1463
 
40	37

1464
 
41	52

1465
 
50	11

1466
 
53	4

1467
 
58	23

1468
 
65	10

1469
 
68	12

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

1471
 
companynr	count(distinct concat(fld1,repeat(65,1000)))
208 by Brian Aker
Removed zerofil syntax (not internals). 
1472
 
0	82
1 by brian
clean slate 
1473
 
29	95

1474
 
34	70

1475
 
36	215

1476
 
37	588

1477
 
40	37

1478
 
41	52

1479
 
50	11

1480
 
53	4

1481
 
58	23

1482
 
65	10

1483
 
68	12

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

1485
 
companynr	count(distinct concat(fld1,repeat(65,200)))
208 by Brian Aker
Removed zerofil syntax (not internals). 
1486
 
0	82
1 by brian
clean slate 
1487
 
29	95

1488
 
34	70

1489
 
36	215

1490
 
37	588

1491
 
40	37

1492
 
41	52

1493
 
50	11

1494
 
53	4

1495
 
58	23

1496
 
65	10

1497
 
68	12

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

1499
 
companynr	count(distinct floor(fld1/100))
208 by Brian Aker
Removed zerofil syntax (not internals). 
1500
 
0	47
1 by brian
clean slate 
1501
 
29	35

1502
 
34	14

1503
 
36	69

1504
 
37	108

1505
 
40	16

1506
 
41	11

1507
 
50	9

1508
 
53	1

1509
 
58	1

1510
 
65	1

1511
 
68	1

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

1513
 
companynr	count(distinct concat(repeat(65,1000),floor(fld1/100)))
208 by Brian Aker
Removed zerofil syntax (not internals). 
1514
 
0	47
1 by brian
clean slate 
1515
 
29	35

1516
 
34	14

1517
 
36	69

1518
 
37	108

1519
 
40	16

1520
 
41	11

1521
 
50	9

1522
 
53	1

1523
 
58	1

1524
 
65	1

1525
 
68	1

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

1527
 
sum(fld1)	fld3

1528
 
11402	Romans

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

1530
 
name	count(*)

1531
 
cloakroom	4181

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

1533
 
name	count(*)

1534
 
cloakroom	4181

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

1536
 
count(*)

1537
 
4181

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

1539
 
name	count(*)

1540
 
cloakroom	4181

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

1542
 
name	count(*)

1543
 
extramarital	4181

1544
 
gazer	4181

1545
 
gems	4181

1546
 
Iranizes	4181

1547
 
spates	4181

1548
 
tucked	4181

1549
 
violinist	4181

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

1551
 
fld3	count(*)

1552
 
spates	4181
257 by Brian Aker
Removed ALTER TABLE UPGRADE. 
1553
 
select companynr,companyname from t4 group by 1;

1554
 
companynr	companyname
1 by brian
clean slate 
1555
 
0	Unknown

1556
 
29	company 1

1557
 
34	company 2

1558
 
36	company 3

1559
 
37	company 4

1560
 
40	company 5

1561
 
41	company 6

1562
 
50	company 11

1563
 
53	company 7

1564
 
58	company 8

1565
 
65	company 9

1566
 
68	company 10

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

1568
 
companynr	companyname	count(*)

1569
 
29	company 1	95

1570
 
68	company 10	12

1571
 
50	company 11	11

1572
 
34	company 2	70

1573
 
36	company 3	215

1574
 
37	company 4	588

1575
 
40	company 5	37

1576
 
41	company 6	52

1577
 
53	company 7	4

1578
 
58	company 8	23

1579
 
65	company 9	10
208 by Brian Aker
Removed zerofil syntax (not internals). 
1580
 
0	Unknown	82
1 by brian
clean slate 
1581
 
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;

1582
 
fld1	count(*)

1583
 
158402	4181

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

1585
 
sum(Period)/count(*)

1586
 
9410.0000

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

1588
 
companynr	count	sum	diff	func

1589
 
37	12543	309394878010	0.0000	464091

1590
 
78	8362	414611089292	0.0000	652236

1591
 
101	4181	3489454238	0.0000	422281

1592
 
154	4181	4112197254950	0.0000	643874

1593
 
311	4181	979599938	0.0000	1300291

1594
 
447	4181	9929180954	0.0000	1868907

1595
 
512	4181	3288532102	0.0000	2140672

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

1597
 
companynr	avg

1598
 
154	983543950.0000

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

1600
 
companynr	count(*)

1601
 
37	588

1602
 
36	215

1603
 
29	95
208 by Brian Aker
Removed zerofil syntax (not internals). 
1604
 
0	82
1 by brian
clean slate 
1605
 
34	70

1606
 
41	52

1607
 
40	37

1608
 
58	23

1609
 
68	12

1610
 
50	11

1611
 
65	10

1612
 
53	4

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

1614
 
companynr	count(*)

1615
 
41	52

1616
 
58	23

1617
 
68	12

1618
 
50	11

1619
 
65	10

1620
 
53	4

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

1622
 
fld4	fld1	count(price)	sum(price)	min(price)	max(price)	avg(price)
208 by Brian Aker
Removed zerofil syntax (not internals). 
1623
 
teethe	1	1	5987435	5987435	5987435	5987435.0000

1624
 
dreaded	11401	1	5987435	5987435	5987435	5987435.0000

1625
 
scholastics	11402	1	28357832	28357832	28357832	28357832.0000

1626
 
audiology	11403	1	39654943	39654943	39654943	39654943.0000

1627
 
wallet	11501	1	5987435	5987435	5987435	5987435.0000

1628
 
parters	11701	1	5987435	5987435	5987435	5987435.0000

1629
 
eschew	11702	1	28357832	28357832	28357832	28357832.0000

1630
 
quitter	11703	1	39654943	39654943	39654943	39654943.0000

1631
 
neat	12001	1	5987435	5987435	5987435	5987435.0000

1632
 
Steinberg	12003	1	39654943	39654943	39654943	39654943.0000

1633
 
balled	12301	1	5987435	5987435	5987435	5987435.0000

1634
 
persist	12302	1	28357832	28357832	28357832	28357832.0000

1635
 
attainments	12303	1	39654943	39654943	39654943	39654943.0000

1636
 
capably	12501	1	5987435	5987435	5987435	5987435.0000

1637
 
impulsive	12602	1	28357832	28357832	28357832	28357832.0000

1638
 
starlet	12603	1	39654943	39654943	39654943	39654943.0000

1639
 
featherweight	12701	1	5987435	5987435	5987435	5987435.0000

1640
 
pessimist	12702	1	28357832	28357832	28357832	28357832.0000

1641
 
daughter	12703	1	39654943	39654943	39654943	39654943.0000

1642
 
lawgiver	13601	1	5987435	5987435	5987435	5987435.0000

1643
 
stated	13602	1	28357832	28357832	28357832	28357832.0000

1644
 
readable	13603	1	39654943	39654943	39654943	39654943.0000

1645
 
testicle	13801	1	5987435	5987435	5987435	5987435.0000

1646
 
Parsifal	13802	1	28357832	28357832	28357832	28357832.0000

1647
 
leavings	13803	1	39654943	39654943	39654943	39654943.0000

1648
 
squeaking	13901	1	5987435	5987435	5987435	5987435.0000

1649
 
contrasted	16001	1	5987435	5987435	5987435	5987435.0000

1650
 
leftover	16201	1	5987435	5987435	5987435	5987435.0000

1651
 
whiteners	16202	1	28357832	28357832	28357832	28357832.0000

1652
 
erases	16301	1	5987435	5987435	5987435	5987435.0000

1653
 
Punjab	16302	1	28357832	28357832	28357832	28357832.0000

1654
 
Merritt	16303	1	39654943	39654943	39654943	39654943.0000

1655
 
sweetish	18001	1	5987435	5987435	5987435	5987435.0000

1656
 
dogging	18002	1	28357832	28357832	28357832	28357832.0000

1657
 
scornfully	18003	1	39654943	39654943	39654943	39654943.0000

1658
 
fetters	18012	1	28357832	28357832	28357832	28357832.0000

1659
 
bivalves	18013	1	39654943	39654943	39654943	39654943.0000

1660
 
skulking	18021	1	5987435	5987435	5987435	5987435.0000

1661
 
flint	18022	1	28357832	28357832	28357832	28357832.0000

1662
 
flopping	18023	1	39654943	39654943	39654943	39654943.0000

1663
 
Judas	18032	1	28357832	28357832	28357832	28357832.0000

1664
 
vacuuming	18033	1	39654943	39654943	39654943	39654943.0000

1665
 
medical	18041	1	5987435	5987435	5987435	5987435.0000

1666
 
bloodbath	18042	1	28357832	28357832	28357832	28357832.0000

1667
 
subschema	18043	1	39654943	39654943	39654943	39654943.0000

1668
 
interdependent	18051	1	5987435	5987435	5987435	5987435.0000

1669
 
Graves	18052	1	28357832	28357832	28357832	28357832.0000

1670
 
neonatal	18053	1	39654943	39654943	39654943	39654943.0000

1671
 
sorters	18061	1	5987435	5987435	5987435	5987435.0000

1672
 
epistle	18062	1	28357832	28357832	28357832	28357832.0000

1673
 
Conley	18101	1	5987435	5987435	5987435	5987435.0000

1674
 
lectured	18102	1	28357832	28357832	28357832	28357832.0000

1675
 
Abraham	18103	1	39654943	39654943	39654943	39654943.0000

1676
 
cage	18201	1	5987435	5987435	5987435	5987435.0000

1677
 
hushes	18202	1	28357832	28357832	28357832	28357832.0000

1678
 
Simla	18402	1	28357832	28357832	28357832	28357832.0000

1679
 
reporters	18403	1	39654943	39654943	39654943	39654943.0000

1680
 
coexist	18601	1	5987435	5987435	5987435	5987435.0000

1681
 
Beebe	18602	1	28357832	28357832	28357832	28357832.0000

1682
 
Taoism	18603	1	39654943	39654943	39654943	39654943.0000

1683
 
Connally	18801	1	5987435	5987435	5987435	5987435.0000

1684
 
fetched	18802	1	28357832	28357832	28357832	28357832.0000

1685
 
checkpoints	18803	1	39654943	39654943	39654943	39654943.0000

1686
 
gritty	18811	1	5987435	5987435	5987435	5987435.0000

1687
 
firearm	18812	1	28357832	28357832	28357832	28357832.0000

1688
 
minima	19101	1	5987435	5987435	5987435	5987435.0000

1689
 
Selfridge	19102	1	28357832	28357832	28357832	28357832.0000

1690
 
disable	19103	1	39654943	39654943	39654943	39654943.0000

1691
 
witchcraft	19201	1	5987435	5987435	5987435	5987435.0000

1692
 
betroth	30501	1	5987435	5987435	5987435	5987435.0000

1693
 
Manhattanize	30502	1	28357832	28357832	28357832	28357832.0000

1694
 
imprint	30503	1	39654943	39654943	39654943	39654943.0000

1695
 
swelling	31901	1	5987435	5987435	5987435	5987435.0000

1696
 
interrelationships	36001	1	5987435	5987435	5987435	5987435.0000

1697
 
riser	36002	1	28357832	28357832	28357832	28357832.0000

1698
 
bee	38001	1	5987435	5987435	5987435	5987435.0000

1699
 
kanji	38002	1	28357832	28357832	28357832	28357832.0000

1700
 
dental	38003	1	39654943	39654943	39654943	39654943.0000

1701
 
railway	38011	1	5987435	5987435	5987435	5987435.0000

1702
 
validate	38012	1	28357832	28357832	28357832	28357832.0000

1703
 
normalizes	38013	1	39654943	39654943	39654943	39654943.0000

1704
 
Kline	38101	1	5987435	5987435	5987435	5987435.0000

1705
 
Anatole	38102	1	28357832	28357832	28357832	28357832.0000

1706
 
partridges	38103	1	39654943	39654943	39654943	39654943.0000

1707
 
recruited	38201	1	5987435	5987435	5987435	5987435.0000

1708
 
dimensions	38202	1	28357832	28357832	28357832	28357832.0000

1709
 
Chicana	38203	1	39654943	39654943	39654943	39654943.0000
1 by brian
clean slate 
1710
 
select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;

1711
 
companynr	fld3	sum(price)

1712
 
512	boat	786542

1713
 
512	capably	786542

1714
 
512	cupboard	786542

1715
 
512	decliner	786542

1716
 
512	descendants	786542

1717
 
512	dopers	786542

1718
 
512	erases	786542

1719
 
512	Micronesia	786542

1720
 
512	Miles	786542

1721
 
512	skies	786542

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

1723
 
companynr	count(*)	min(fld3)	max(fld3)	sum(price)	avg(price)
208 by Brian Aker
Removed zerofil syntax (not internals). 
1724
 
0	1	Omaha	Omaha	5987435	5987435.0000
1 by brian
clean slate 
1725
 
36	1	dubbed	dubbed	28357832	28357832.0000

1726
 
37	83	Abraham	Wotan	1908978016	22999735.1325

1727
 
50	2	scribbled	tapestry	68012775	34006387.5000

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

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

1730
 
37	1	Omaha	5987435

1731
 
37	11401	breaking	5987435

1732
 
37	11402	Romans	28357832

1733
 
37	11403	intercepted	39654943

1734
 
37	11501	bewilderingly	5987435

1735
 
37	11701	astound	5987435

1736
 
37	11702	admonishing	28357832

1737
 
37	11703	sumac	39654943

1738
 
37	12001	flanking	5987435

1739
 
37	12003	combed	39654943

1740
 
37	12301	Eulerian	5987435

1741
 
37	12302	dubbed	28357832

1742
 
37	12303	Kane	39654943

1743
 
37	12501	annihilates	5987435

1744
 
37	12602	Wotan	28357832

1745
 
37	12603	snatching	39654943

1746
 
37	12701	grazing	5987435

1747
 
37	12702	Baird	28357832

1748
 
37	12703	celery	39654943

1749
 
37	13601	handgun	5987435

1750
 
37	13602	foldout	28357832

1751
 
37	13603	mystic	39654943

1752
 
37	13801	intelligibility	5987435

1753
 
37	13802	Augustine	28357832

1754
 
37	13803	teethe	39654943

1755
 
37	13901	scholastics	5987435

1756
 
37	16001	audiology	5987435

1757
 
37	16201	wallet	5987435

1758
 
37	16202	parters	28357832

1759
 
37	16301	eschew	5987435

1760
 
37	16302	quitter	28357832

1761
 
37	16303	neat	39654943

1762
 
37	18001	jarring	5987435

1763
 
37	18002	tinily	28357832

1764
 
37	18003	balled	39654943

1765
 
37	18012	impulsive	28357832

1766
 
37	18013	starlet	39654943

1767
 
37	18021	lawgiver	5987435

1768
 
37	18022	stated	28357832

1769
 
37	18023	readable	39654943

1770
 
37	18032	testicle	28357832

1771
 
37	18033	Parsifal	39654943

1772
 
37	18041	Punjab	5987435

1773
 
37	18042	Merritt	28357832

1774
 
37	18043	Quixotism	39654943

1775
 
37	18051	sureties	5987435

1776
 
37	18052	puddings	28357832

1777
 
37	18053	tapestry	39654943

1778
 
37	18061	trimmings	5987435

1779
 
37	18062	humility	28357832

1780
 
37	18101	tragedies	5987435

1781
 
37	18102	skulking	28357832

1782
 
37	18103	flint	39654943

1783
 
37	18201	relaxing	5987435

1784
 
37	18202	offload	28357832

1785
 
37	18402	suites	28357832

1786
 
37	18403	lists	39654943

1787
 
37	18601	vacuuming	5987435

1788
 
37	18602	dentally	28357832

1789
 
37	18603	humanness	39654943

1790
 
37	18801	inch	5987435

1791
 
37	18802	Weissmuller	28357832

1792
 
37	18803	irresponsibly	39654943

1793
 
37	18811	repetitions	5987435

1794
 
37	18812	Antares	28357832

1795
 
37	19101	ventilate	5987435

1796
 
37	19102	pityingly	28357832

1797
 
37	19103	interdependent	39654943

1798
 
37	19201	Graves	5987435

1799
 
37	30501	neonatal	5987435

1800
 
37	30502	scribbled	28357832

1801
 
37	30503	chafe	39654943

1802
 
37	31901	realtor	5987435

1803
 
37	36001	elite	5987435

1804
 
37	36002	funereal	28357832

1805
 
37	38001	Conley	5987435

1806
 
37	38002	lectured	28357832

1807
 
37	38003	Abraham	39654943

1808
 
37	38011	groupings	5987435

1809
 
37	38012	dissociate	28357832

1810
 
37	38013	coexist	39654943

1811
 
37	38101	rusting	5987435

1812
 
37	38102	galling	28357832

1813
 
37	38103	obliterates	39654943

1814
 
37	38201	resumes	5987435

1815
 
37	38202	analyzable	28357832

1816
 
37	38203	terminator	39654943

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

1818
 
sum(price)

1819
 
234298

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

1821
 
fld1	sum(price)
208 by Brian Aker
Removed zerofil syntax (not internals). 
1822
 
38008	234298
1 by brian
clean slate 
1823
 
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;

1824
 
companynr	fld1

1825
 
34	250501

1826
 
34	250502

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

1828
 
companynr	fld1

1829
 
34	250501

1830
 
34	250502

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

1832
 
companynr	count	sum
208 by Brian Aker
Removed zerofil syntax (not internals). 
1833
 
0	82	10355753
1 by brian
clean slate 
1834
 
29	95	14473298

1835
 
34	70	17788966

1836
 
37	588	83602098

1837
 
41	52	12816335

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

1839
 
companynr
208 by Brian Aker
Removed zerofil syntax (not internals). 
1840
 
0
1 by brian
clean slate 
1841
 
29

1842
 
34

1843
 
37

1844
 
41

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

1846
 
companynr	companyname	count(*)

1847
 
68	company 10	12

1848
 
50	company 11	11

1849
 
40	company 5	37

1850
 
41	company 6	52

1851
 
53	company 7	4

1852
 
58	company 8	23

1853
 
65	company 9	10

1854
 
select count(*) from t2;

1855
 
count(*)

1856
 
1199

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

1858
 
count(*)

1859
 
387

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

1861
 
min(fld1)

1862
 
98024

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

1864
 
max(fld1)

1865
 
1232609

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

1867
 
count(*)

1868
 
4181

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

1870
 
count(*)

1871
 
4181

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

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

1874
 
0	1232609	1199

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

1876
 
min(t2nr)	max(t2nr)

1877
 
2115	2115

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

1879
 
count(*)	min(t2nr)	max(t2nr)

1880
 
4181	4	41804

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

1882
 
t2nr	count(*)

1883
 
9	1

1884
 
19	1

1885
 
29	1

1886
 
39	1

1887
 
49	1

1888
 
59	1

1889
 
69	1

1890
 
79	1

1891
 
89	1

1892
 
99	1

1893
 
109	1

1894
 
119	1

1895
 
129	1

1896
 
139	1

1897
 
149	1

1898
 
159	1

1899
 
169	1

1900
 
179	1

1901
 
189	1

1902
 
199	1

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

1904
 
max(t2nr)

1905
 
41807

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

1907
 
period

1908
 
1001

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

1910
 
period

1911
 
9410

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

1913
 
Nuvarande period

1914
 
9410

1915
 
select period as ok_period from t1 limit 1;

1916
 
ok_period

1917
 
9410

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

1919
 
ok_period

1920
 
9410

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

1922
 
summa

1923
 
2

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

1925
 
Nuvarande period

1926
 
9410

1927
 
show tables;

1928
 
Tables_in_test

1929
 
t1

1930
 
t2

1931
 
t3
1273.19.10 by Brian Aker
Add support for listing temporay tables from show commands. 
1932
 
t4
1 by brian
clean slate 
1933
 
show tables from test like "s%";

1934
 
Tables_in_test (s%)

1935
 
show tables from test like "t?";

1936
 
Tables_in_test (t?)
1273.13.37 by Brian Aker
Remove "full" syntax. 
1937
 
show columns from t2;
1309.2.4 by Brian Aker
New version of show columns code. 
1938
 
Field	Type	Null	Default	Default_is_NULL	On_Update
1660 by Brian Aker
MErge in change to do YES/NO like standard requires. 
1939
 
auto	INTEGER	NO		NO	

1940
 
fld1	INTEGER	NO	0	NO	

1941
 
companynr	INTEGER	NO	0	NO	

1942
 
fld3	VARCHAR	NO		NO	

1943
 
fld4	VARCHAR	NO		NO	

1944
 
fld5	VARCHAR	NO		NO	

1945
 
fld6	VARCHAR	NO		NO
1273.13.37 by Brian Aker
Remove "full" syntax. 
1946
 
show columns from t2 from test like 'f%';
1309.2.4 by Brian Aker
New version of show columns code. 
1947
 
Field	Type	Null	Default	Default_is_NULL	On_Update
1660 by Brian Aker
MErge in change to do YES/NO like standard requires. 
1948
 
auto	INTEGER	NO		NO	

1949
 
fld1	INTEGER	NO	0	NO	

1950
 
companynr	INTEGER	NO	0	NO	

1951
 
fld3	VARCHAR	NO		NO	

1952
 
fld4	VARCHAR	NO		NO	

1953
 
fld5	VARCHAR	NO		NO	

1954
 
fld6	VARCHAR	NO		NO
1273.13.37 by Brian Aker
Remove "full" syntax. 
1955
 
show columns from t2 from test like 's%';
1309.2.4 by Brian Aker
New version of show columns code. 
1956
 
Field	Type	Null	Default	Default_is_NULL	On_Update
1660 by Brian Aker
MErge in change to do YES/NO like standard requires. 
1957
 
auto	INTEGER	NO		NO	

1958
 
fld1	INTEGER	NO	0	NO	

1959
 
companynr	INTEGER	NO	0	NO	

1960
 
fld3	VARCHAR	NO		NO	

1961
 
fld4	VARCHAR	NO		NO	

1962
 
fld5	VARCHAR	NO		NO	

1963
 
fld6	VARCHAR	NO		NO
1 by brian
clean slate 
1964
 
drop table t4, t3, t2, t1;

1965
 
CREATE TABLE t1 (
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
1966
 
id bigint NOT NULL auto_increment,
1 by brian
clean slate 
1967
 
pseudo varchar(35) NOT NULL default '',

1968
 
PRIMARY KEY  (id),

1969
 
UNIQUE KEY pseudo (pseudo)

1970
 
);

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

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

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

1974
 
rnd1

1975
 
DROP TABLE t1;
1217 by Brian Aker
Removed bits of charset support from the parser. 
1976
 
CREATE TEMPORARY TABLE t1 (gvid int default NULL,  hmid int default NULL,  volid int default NULL,  mmid int default NULL,  hdid int default NULL,  fsid int default NULL,  ctid int default NULL,  dtid int default NULL,  cost int default NULL,  performance int default NULL,  serialnumber bigint default NULL,  monitored int default '1',  removed int default '0',  target int default '0',  dt_modified timestamp NOT NULL,  name varchar(255) default NULL,  description varchar(255) default NULL,  UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
1 by brian
clean slate 
1977
 
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);
1063.9.24 by Stewart Smith
select.test for MyISAM as temp only 
1978
 
CREATE TEMPORARY TABLE t2 (  hmid int default NULL,  volid int default NULL,  sampletid int default NULL,  sampletime datetime default NULL,  samplevalue bigint default NULL,  KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;
1 by brian
clean slate 
1979
 
INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);

1980
 
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;
873.1.8 by Jay Pipes
Fixes Arg_comparator::can_compare_as_dates to never, ever allow bad 
1981
 
ERROR HY000: Received an invalid datetime value 'wrong-date-value'.
1 by brian
clean slate 
1982
 
SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;

1983
 
gvid	the_success	the_fail	the_size	the_time

1984
 
DROP TABLE t1,t2;
223 by Brian Aker
Cleanup int() work. 
1985
 
create table  t1 (  A_Id bigint NOT NULL default '0',  A_UpdateBy char(10) NOT NULL default '',  A_UpdateDate bigint NOT NULL default '0',  A_UpdateSerial int NOT NULL default '0',  other_types bigint NOT NULL default '0',  wss_type bigint NOT NULL default '0');
1 by brian
clean slate 
1986
 
INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);

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

1988
 
wss_type

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

1990
 
wss_type

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

1992
 
wss_type

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

1994
 
wss_type

1995
 
102935229216544093

1996
 
select wss_type from t1 where wss_type =102935229216544093;

1997
 
wss_type

1998
 
102935229216544093

1999
 
drop table t1;

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

2001
 
select @a;

2002
 
@a

2003
 
3

2004
 
select @b;

2005
 
@b

2006
 
aaaa

2007
 
select @c;

2008
 
@c

2009
 
6.260

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

2011
 
insert into t1 values ();

2012
 
insert into t1 values ();

2013
 
insert into t1 values ();

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

2015
 
a	a

2016
 
1	1
1718.2.1 by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) 
2017
 
1	2

2018
 
1	3
1 by brian
clean slate 
2019
 
2	1
1718.2.1 by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) 
2020
 
2	2

2021
 
2	3
1 by brian
clean slate 
2022
 
3	1

2023
 
3	2

2024
 
3	3

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

2026
 
a	a

2027
 
1	1

2028
 
2	1

2029
 
3	1

2030
 
1	2

2031
 
2	2

2032
 
3	2

2033
 
1	3

2034
 
2	3

2035
 
3	3

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

2037
 
a	a

2038
 
1	1

2039
 
2	1

2040
 
3	1

2041
 
1	2

2042
 
2	2

2043
 
3	2

2044
 
1	3

2045
 
2	3

2046
 
3	3

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

2048
 
a	a

2049
 
1	1

2050
 
2	1

2051
 
3	1

2052
 
1	2

2053
 
2	2

2054
 
3	2

2055
 
1	3

2056
 
2	3

2057
 
3	3

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

2059
 
a	a

2060
 
1	2
201 by Brian Aker
Convert default engine to Innodb 
2061
 
1	3
1 by brian
clean slate 
2062
 
2	2
201 by Brian Aker
Convert default engine to Innodb 
2063
 
2	3
1 by brian
clean slate 
2064
 
3	2

2065
 
3	3

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

2067
 
a	a

2068
 
2	1

2069
 
3	1

2070
 
2	2

2071
 
3	2

2072
 
2	3

2073
 
3	3

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

2075
 
a

2076
 
1

2077
 
2

2078
 
3

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

2080
 
a

2081
 
1

2082
 
2

2083
 
3

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

2085
 
a	a

2086
 
1	2

2087
 
1	3

2088
 
2	2

2089
 
2	3

2090
 
3	2

2091
 
3	3

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

2093
 
a	a

2094
 
1	NULL

2095
 
2	1

2096
 
2	2

2097
 
2	3

2098
 
3	1

2099
 
3	2

2100
 
3	3

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

2102
 
a

2103
 
1

2104
 
2

2105
 
3

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

2107
 
a

2108
 
1

2109
 
2

2110
 
3

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

2112
 
a

2113
 
1

2114
 
2

2115
 
3

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

2117
 
a

2118
 
1

2119
 
2

2120
 
3

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

2122
 
a	a

2123
 
NULL	1

2124
 
1	2

2125
 
2	2

2126
 
3	2

2127
 
1	3

2128
 
2	3

2129
 
3	3

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

2131
 
a	a

2132
 
2	1

2133
 
3	1

2134
 
2	2

2135
 
3	2

2136
 
2	3

2137
 
3	3

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

2139
 
a

2140
 
1

2141
 
2

2142
 
3

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

2144
 
a

2145
 
1

2146
 
2

2147
 
3

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

2149
 
a

2150
 
1

2151
 
2

2152
 
3

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

2154
 
a

2155
 
1

2156
 
2

2157
 
3

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

2159
 
a

2160
 
1

2161
 
2

2162
 
3

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

2164
 
a

2165
 
1

2166
 
2

2167
 
3

2168
 
drop table t1;
1063.9.24 by Stewart Smith
select.test for MyISAM as temp only 
2169
 
CREATE TEMPORARY TABLE t1 (  aa char(2),  id int NOT NULL auto_increment,  t2_id int NOT NULL default '0',  PRIMARY KEY  (id),  KEY replace_id (t2_id)) ENGINE=MyISAM;
1 by brian
clean slate 
2170
 
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);
1063.9.24 by Stewart Smith
select.test for MyISAM as temp only 
2171
 
CREATE TEMPORARY TABLE t2 ( id int NOT NULL auto_increment,  PRIMARY KEY  (id)) ENGINE=MyISAM;
1 by brian
clean slate 
2172
 
INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);

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

2174
 
aa	id	t2_id	id

2175
 
2	8299	2517	2517

2176
 
3	8301	2518	2518

2177
 
4	8302	2519	2519

2178
 
5	8303	2520	2520

2179
 
6	8304	2521	2521

2180
 
drop table t1,t2;

2181
 
create table t1 (id1 int NOT NULL);

2182
 
create table t2 (id2 int NOT NULL);

2183
 
create table t3 (id3 int NOT NULL);

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

2185
 
insert into t1 values (1);

2186
 
insert into t1 values (2);

2187
 
insert into t2 values (1);

2188
 
insert into t4 values (1,1);

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

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

2191
 
id1	id2	id3	id4	id44

2192
 
1	1	NULL	NULL	NULL

2193
 
drop table t1,t2,t3,t4;

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

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

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

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

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

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

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

2201
 
s

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

2203
 
s

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

2205
 
s

2206
 
one 

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

2208
 
s	s

2209
 
two		two	

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

2211
 
s	s

2212
 
two		two	

2213
 
drop table t1, t2, t3;

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

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

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

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

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

2219
 
a	b	c	d

2220
 
3	2	3	4

2221
 
4	2	4	4

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

2223
 
a	b	c	d

2224
 
3	2	3	4

2225
 
4	2	4	4

2226
 
drop table t1, t2;
1063.9.24 by Stewart Smith
select.test for MyISAM as temp only 
2227
 
CREATE TEMPORARY TABLE t1 (
223 by Brian Aker
Cleanup int() work. 
2228
 
i int NOT NULL default '0',
1 by brian
clean slate 
2229
 
c char(10) NOT NULL default '',

2230
 
PRIMARY KEY  (i),

2231
 
UNIQUE KEY c (c)

2232
 
) ENGINE=MyISAM;

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

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

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

2236
 
EXPLAIN SELECT i FROM t1 WHERE i=1;

2237
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

2239
 
DROP TABLE t1;

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

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

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

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

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

2245
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2246
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5
201 by Brian Aker
Convert default engine to Innodb 
2247
 
1	SIMPLE	t2	ref	a	a	23	test.t1.a	1
1 by brian
clean slate 
2248
 
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;

2249
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2250
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5
201 by Brian Aker
Convert default engine to Innodb 
2251
 
1	SIMPLE	t2	ref	a	a	23	test.t1.a	1
1 by brian
clean slate 
2252
 
DROP TABLE t1, t2;

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

2254
 
INSERT INTO t1 VALUES ('London');

2255
 
INSERT INTO t1 VALUES ('Paris');

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

2257
 
city

2258
 
London

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

2260
 
city

2261
 
London

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

2263
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2266
 
city

2267
 
London

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

2269
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2272
 
city

2273
 
London

2274
 
DROP TABLE t1;
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2275
 
create table t1 (a int, b int);
1 by brian
clean slate 
2276
 
insert into t1 values (1,0), (1,1), (1,2);

2277
 
select a-b  from t1 order by 1;

2278
 
a-b
358 by Brian Aker
More removal on modes. 
2279
 
-1
1 by brian
clean slate 
2280
 
0

2281
 
1

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

2283
 
a-b 	(a-b < 0)
358 by Brian Aker
More removal on modes. 
2284
 
-1	1
1 by brian
clean slate 
2285
 
0	0

2286
 
1	0

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

2288
 
d	(a-b >= 0)	b

2289
 
1	1	0

2290
 
0	1	1
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2291
 
select a - b from t1 order by 1;

2292
 
a - b 

2293
 
-1
1 by brian
clean slate 
2294
 
0

2295
 
1

2296
 
drop table t1;
223 by Brian Aker
Cleanup int() work. 
2297
 
create table t1 (a int);
1 by brian
clean slate 
2298
 
select all all * from t1;

2299
 
a

2300
 
select distinct distinct * from t1;

2301
 
a

2302
 
select all distinct * from t1;

2303
 
ERROR HY000: Incorrect usage of ALL and DISTINCT

2304
 
select distinct all * from t1;

2305
 
ERROR HY000: Incorrect usage of ALL and DISTINCT

2306
 
drop table t1;

2307
 
CREATE TABLE t1 (
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2308
 
kunde_intern_id int NOT NULL default '0',

2309
 
kunde_id int NOT NULL default '0',

2310
 
FK_firma_id int NOT NULL default '0',
1 by brian
clean slate 
2311
 
aktuell enum('Ja','Nein') NOT NULL default 'Ja',

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

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

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

2315
 
firma varchar(128) NOT NULL default ''

2316
 
);

2317
 
INSERT INTO t1 VALUES 

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

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

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

2321
 
WHERE

2322
 
(

2323
 
(

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

2325
 
OR

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

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

2328
 
'Vorname1' != '' AND 'xxxx' != '')

2329
 
)

2330
 
AND

2331
 
(

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

2333
 
)

2334
 
)

2335
 
;

2336
 
kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht

2337
 
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname,

2338
 
geloescht FROM t1

2339
 
WHERE

2340
 
(

2341
 
(

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

2343
 
)

2344
 
AND

2345
 
(

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

2347
 
OR

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

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

2350
 
'xxxx' != '')

2351
 
)

2352
 
)

2353
 
;

2354
 
kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht

2355
 
SELECT COUNT(*) FROM t1 WHERE 

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

2357
 
AND FK_firma_id = 2;

2358
 
COUNT(*)

2359
 
0

2360
 
drop table t1;
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2361
 
CREATE TABLE t1 (b BIGINT NOT NULL, PRIMARY KEY (b));

2362
 
INSERT INTO t1 VALUES (0x4000000000000000);

2363
 
SELECT b FROM t1 WHERE b=0x4000000000000000;
1 by brian
clean slate 
2364
 
b
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2365
 
4611686018427387904
1 by brian
clean slate 
2366
 
DROP TABLE t1;
223 by Brian Aker
Cleanup int() work. 
2367
 
CREATE TABLE `t1` ( `gid` int default NULL, `uid` int default NULL);

2368
 
CREATE TABLE `t2` ( `ident` int default NULL, `level` char(16) default NULL);
1 by brian
clean slate 
2369
 
INSERT INTO `t2` VALUES (0,'READ');
223 by Brian Aker
Cleanup int() work. 
2370
 
CREATE TABLE `t3` ( `id` int default NULL, `name` char(16) default NULL);
1 by brian
clean slate 
2371
 
INSERT INTO `t3` VALUES (1,'fs');

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

2373
 
id	name	gid	uid	ident	level

2374
 
1	fs	NULL	NULL	0	READ

2375
 
drop table t1,t2,t3;

2376
 
CREATE TABLE t1 (
223 by Brian Aker
Cleanup int() work. 
2377
 
acct_id int NOT NULL default '0',
396 by Brian Aker
Cleanup tiny and small int. 
2378
 
profile_id int default NULL,
1 by brian
clean slate 
2379
 
UNIQUE KEY t1$acct_id (acct_id),

2380
 
KEY t1$profile_id (profile_id)

2381
 
);

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

2383
 
CREATE TABLE t2 (
396 by Brian Aker
Cleanup tiny and small int. 
2384
 
profile_id int default NULL,
223 by Brian Aker
Cleanup int() work. 
2385
 
queue_id int default NULL,

2386
 
seq int default NULL,
1 by brian
clean slate 
2387
 
KEY t2$queue_id (queue_id)

2388
 
);

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

2390
 
CREATE TABLE t3 (
223 by Brian Aker
Cleanup int() work. 
2391
 
id int NOT NULL default '0',

2392
 
qtype int default NULL,

2393
 
seq int default NULL,

2394
 
warn_lvl int default NULL,

2395
 
crit_lvl int default NULL,
396 by Brian Aker
Cleanup tiny and small int. 
2396
 
rr1 int NOT NULL default '0',
223 by Brian Aker
Cleanup int() work. 
2397
 
rr2 int default NULL,
396 by Brian Aker
Cleanup tiny and small int. 
2398
 
default_queue int NOT NULL default '0',
1 by brian
clean slate 
2399
 
KEY t3$qtype (qtype),

2400
 
KEY t3$id (id)

2401
 
);

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

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

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

2405
 
WHERE 

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

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

2408
 
COUNT(*)

2409
 
4

2410
 
drop table t1,t2,t3;

2411
 
create table t1 (f1 int);

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

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

2414
 
create index idx1 on t2 (f4);

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

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

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

2418
 
f2

2419
 
1

2420
 
NULL

2421
 
drop table t1,t2;
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2422
 
create table t2 (a int);
1 by brian
clean slate 
2423
 
create index t2i on t2(a);

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

2425
 
select * from t2 where a > -1;

2426
 
a

2427
 
0

2428
 
254

2429
 
255

2430
 
drop table t2;

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

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

2433
 
a	c

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

2435
 
a	c

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

2437
 
a	c

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

2439
 
a	c

2440
 
drop table t1;

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

2442
 
create table t2(f3 int);

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

2444
 
f1

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

2446
 
f1

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

2448
 
f1

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

2450
 
insert into t2 values(2);

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

2452
 
f1	f2	f3

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

2454
 
f1	f2	f3

2455
 
2	NULL	2

2456
 
drop table t1,t2;

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

2458
 
create table t11 like t1;

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

2460
 
show table status like 't1%';
1320.1.18 by Brian Aker
Overhaul of SHOW TABLE STATUS. 
2461
 
Session	Schema	Name	Type	Engine	Version	Rows	Avg_row_length	Table_size	Auto_increment

2462
 
#	test	t1	STANDARD	InnoDB	#	#	#	#	#
1 by brian
clean slate 
2463
 
select 123 as a from t1 where f1 is null;

2464
 
a

2465
 
drop table t1,t11;

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

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

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

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

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

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

2472
 
a	b	c	d

2473
 
1	2	1	1

2474
 
1	2	2	1

2475
 
1	2	3	1

2476
 
1	10		2

2477
 
1	11		2

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

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

2480
 
a	b	c	d

2481
 
1	10		4

2482
 
1	2	1	1

2483
 
1	2	2	1

2484
 
1	2	3	1

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

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

2487
 
a	b	c	d

2488
 
1	2	1	1

2489
 
1	2	2	1

2490
 
1	2	3	1

2491
 
1	10		2

2492
 
1	11		2

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

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

2495
 
a	b	c	d

2496
 
1	2	1	1

2497
 
1	2	2	1

2498
 
1	2	3	1

2499
 
DROP TABLE IF EXISTS t1, t2;

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

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

2502
 
insert into t1 values (1,1);

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

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

2505
 
count(f2) >0

2506
 
1

2507
 
drop table t1,t2;

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

2509
 
insert into t1 values(1,1);

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

2511
 
insert into t2 values(1,1);

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

2513
 
f1	f2

2514
 
1	1

2515
 
drop table t1,t2;

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

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

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

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

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

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

2522
 
DROP TABLE t1,t2,t3;

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

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

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

2526
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2527
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	

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

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

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

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

2532
 
DROP TABLE t1;

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

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

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

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

2537
 
ALTER TABLE t1 ENABLE KEYS;
201 by Brian Aker
Convert default engine to Innodb 
2538
 
Warnings:

2539
 
Note	1031	Table storage engine for 't1' doesn't have this option
1 by brian
clean slate 
2540
 
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;

2541
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
2542
 
1	SIMPLE	t2	index	b	PRIMARY	4	NULL	2
1 by brian
clean slate 
2543
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer

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

2545
 
a	b	a	b

2546
 
1	NULL	1	1

2547
 
1	NULL	2	1

2548
 
1	NULL	4	10

2549
 
2	10	4	10

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

2551
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
2552
 
1	SIMPLE	t2	index	b	PRIMARY	4	NULL	2
1 by brian
clean slate 
2553
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer

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

2555
 
a	b	a	b

2556
 
1	NULL	1	1

2557
 
1	NULL	2	1

2558
 
1	NULL	4	10

2559
 
2	10	4	10

2560
 
DROP TABLE IF EXISTS t1,t2;

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

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

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

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

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

2566
 
max(key1)
166 by Brian Aker
Removal of FLOAT type 
2567
 
0.6158
1 by brian
clean slate 
2568
 
select max(key2) from t2 where key2 <= 1.6158;

2569
 
max(key2)
166 by Brian Aker
Removal of FLOAT type 
2570
 
1.6158
1 by brian
clean slate 
2571
 
select min(key1) from t1 where key1 >= 0.3762;

2572
 
min(key1)
166 by Brian Aker
Removal of FLOAT type 
2573
 
0.3762
1 by brian
clean slate 
2574
 
select min(key2) from t2 where key2 >= 1.3762;

2575
 
min(key2)
166 by Brian Aker
Removal of FLOAT type 
2576
 
1.3762
1 by brian
clean slate 
2577
 
select max(key1), min(key2) from t1, t2

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

2579
 
max(key1)	min(key2)
166 by Brian Aker
Removal of FLOAT type 
2580
 
0.6158	1.3762
1 by brian
clean slate 
2581
 
select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;

2582
 
max(key1)
166 by Brian Aker
Removal of FLOAT type 
2583
 
0.6158
1 by brian
clean slate 
2584
 
select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;

2585
 
min(key1)
166 by Brian Aker
Removal of FLOAT type 
2586
 
0.3762
1 by brian
clean slate 
2587
 
DROP TABLE t1,t2;
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2588
 
CREATE TABLE t1 (i BIGINT NOT NULL);
1 by brian
clean slate 
2589
 
INSERT INTO t1 VALUES (10);

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

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

2592
 
1	1	1	1

2593
 
DROP TABLE t1;

2594
 
CREATE TABLE t1 (c0 int);

2595
 
CREATE TABLE t2 (c0 int);
971.3.60 by Eric Day
Moved connect_timeout, net_*_timeout, and retry_count to plugin. 
2596
 
INSERT INTO t1 VALUES(@@server_id);

2597
 
INSERT INTO t2 VALUES(@@server_id);

2598
 
SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@server_id);
1 by brian
clean slate 
2599
 
c0	c0

2600
 
X	X

2601
 
DROP TABLE t1, t2;

2602
 
End of 4.1 tests
1063.9.24 by Stewart Smith
select.test for MyISAM as temp only 
2603
 
CREATE TEMPORARY TABLE t1 (
383.1.30 by Brian Aker
Removal of latin character set. 
2604
 
K2C4 varchar(4) collate utf8_bin NOT NULL default '', 

2605
 
K4N4 varchar(4) collate utf8_bin NOT NULL default '0000',
223 by Brian Aker
Cleanup int() work. 
2606
 
F2I4 int NOT NULL default '0'
377.1.4 by Brian Aker
Big, fat, UTF-8 patch. This fixes some of the oddities around only one 
2607
 
) ENGINE=MyISAM;
1 by brian
clean slate 
2608
 
INSERT INTO t1 VALUES 

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

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

2611
 
('WART', '0100', 1), 

2612
 
('WART', '0200', 1), 

2613
 
('WERT', '0100', 2), 

2614
 
('WORT','0200', 2), 

2615
 
('WT', '0100', 2), 

2616
 
('W_RT', '0100', 2), 

2617
 
('WaRT', '0100', 3), 

2618
 
('WART', '0300', 3), 

2619
 
('WRT' , '0400', 3), 

2620
 
('WURM', '0500', 3), 

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

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

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

2624
 
SELECT K2C4, K4N4, F2I4 FROM t1

2625
 
WHERE  K2C4 = 'WART' AND 

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

2627
 
K2C4	K4N4	F2I4

2628
 
WART	0200	1

2629
 
SELECT K2C4, K4N4, F2I4 FROM t1

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

2631
 
K2C4	K4N4	F2I4

2632
 
WART	0100	1

2633
 
WART	0200	1

2634
 
WART	0300	3

2635
 
DROP TABLE t1;

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

2637
 
create table t2 like t1;

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

2639
 
a

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

2641
 
a

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

2643
 
a	a	a

2644
 
drop table t1,t2;

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

2646
 
insert into t1 values ('Wall');

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

2648
 
min(s1)

2649
 
Wall

2650
 
Wall

2651
 
drop table t1;
1063.9.24 by Stewart Smith
select.test for MyISAM as temp only 
2652
 
create temporary table t1 (s1 int) engine=myisam;
1 by brian
clean slate 
2653
 
insert into t1 values (0);

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

2655
 
avg(distinct s1)

2656
 
0.0000

2657
 
0.0000

2658
 
drop table t1;

2659
 
create table t1 (s1 int);

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

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

2662
 
x

2663
 
NULL

2664
 
1.0000

2665
 
drop table t1;

2666
 
CREATE TABLE t1 (a int);

2667
 
CREATE TABLE t2 (a int);

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

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

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

2671
 
a

2672
 
2

2673
 
4

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

2675
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2676
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	

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

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

2679
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2680
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	

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

2682
 
DROP TABLE t1,t2;

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

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

2685
 
16	16	2	2
223 by Brian Aker
Cleanup int() work. 
2686
 
create table t1 (f1 varchar(6) default NULL, f2 int primary key not null);
1 by brian
clean slate 
2687
 
create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));

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

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

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

2691
 
f1	f2	f3	f4

2692
 
 2	2	 2	 one 

2693
 
 2	2	 2	 two 

2694
 
drop table t1,t2;

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

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

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

2698
 
pk

2699
 
drop table t1,t2;
223 by Brian Aker
Cleanup int() work. 
2700
 
create table t1 (a int, t1_val int);

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

2702
 
create table t3 (a int, b int);
1 by brian
clean slate 
2703
 
insert into t1 values (1,1),(2,2);

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

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

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

2707
 
a	b	t1_val	t2_val

2708
 
1	1	1	1

2709
 
2	1	2	1

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

2711
 
b	a	t1_val	t2_val

2712
 
1	1	1	1

2713
 
1	2	2	1

2714
 
drop table t1, t2, t3;

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

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

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

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

2719
 
select a from t1 natural join t2;

2720
 
a

2721
 
b

2722
 
c

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

2724
 
a

2725
 
b

2726
 
drop table t1, t2;
396 by Brian Aker
Cleanup tiny and small int. 
2727
 
CREATE TABLE t1 (`id` int);

2728
 
CREATE TABLE t2 (`id` int);

2729
 
CREATE TABLE t3 (`id` int);
1 by brian
clean slate 
2730
 
INSERT INTO t1 VALUES (1),(2),(3);

2731
 
INSERT INTO t2 VALUES (2);

2732
 
INSERT INTO t3 VALUES (3);

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

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

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

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

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

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

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

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

2741
 
drop table t1, t2, t3;
223 by Brian Aker
Cleanup int() work. 
2742
 
create table t1 (a int,b int);

2743
 
create table t2 (a int,b int);
1 by brian
clean slate 
2744
 
insert into t1 values (1,10),(2,20),(3,30);

2745
 
insert into t2 values (1,10);

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

2747
 
a	b	b

2748
 
1	10	10

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

2750
 
a	b	b

2751
 
1	10	10

2752
 
drop table t1, t2;

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

2754
 
create table t2 (b int);

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

2756
 
create table t4 (c int);

2757
 
insert into t1 values (1,1);

2758
 
insert into t2 values (1);

2759
 
insert into t3 values (1,1);

2760
 
insert into t4 values (1);

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

2762
 
a	c	b	b	a

2763
 
1	1	1	1	1

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

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

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

2767
 
a	c	b	b	a	c

2768
 
1	1	1	1	1	1

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

2770
 
c	a	b

2771
 
1	1	1

2772
 
drop table t1, t2, t3, t4;

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

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

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

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

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

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

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

2780
 
x	x

2781
 
1	1

2782
 
2	1

2783
 
3	1

2784
 
3	2

2785
 
3	3

2786
 
4	3

2787
 
4	4

2788
 
4	5

2789
 
drop table t1,t2,t3;
223 by Brian Aker
Cleanup int() work. 
2790
 
create table t1 (id int not null default '0');
1 by brian
clean slate 
2791
 
insert into t1 values (123),(191),(192);
383.1.28 by Brian Aker
Fix for CREATE TABLE/character sets. 
2792
 
create table t2 (id char(16) not null);
1 by brian
clean slate 
2793
 
insert into t2 values ('58013'),('58014'),('58015'),('58016');
383.1.28 by Brian Aker
Fix for CREATE TABLE/character sets. 
2794
 
create table t3 (a_id int not null, b_id char(16));
1 by brian
clean slate 
2795
 
insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');

2796
 
select count(*)

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

2798
 
count(*)

2799
 
6

2800
 
select count(*)

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

2802
 
count(*)

2803
 
6

2804
 
drop table t1,t2,t3;

2805
 
create table t1 (a int);

2806
 
create table t2 (b int);

2807
 
create table t3 (c int);

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

2809
 
a	b	c

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

2811
 
a	b	c

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

2813
 
a	b	c

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

2815
 
a	b	c

2816
 
drop table t1, t2 ,t3;

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

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

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

2820
 
select * from t1 where f2 >= 0            order by f2;
873.1.8 by Jay Pipes
Fixes Arg_comparator::can_compare_as_dates to never, ever allow bad 
2821
 
ERROR HY000: Received an invalid datetime value '0'.
1 by brian
clean slate 
2822
 
select * from t1 where f2 >= '0000-00-00' order by f2;
873.1.8 by Jay Pipes
Fixes Arg_comparator::can_compare_as_dates to never, ever allow bad 
2823
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
2824
 
select * from t1 where f2 >= '2005-09-31' order by f2;
873.1.8 by Jay Pipes
Fixes Arg_comparator::can_compare_as_dates to never, ever allow bad 
2825
 
ERROR HY000: Received an invalid datetime value '2005-09-31'.
1 by brian
clean slate 
2826
 
select * from t1 where f2 >= '2005-09-3a' order by f2;
873.1.8 by Jay Pipes
Fixes Arg_comparator::can_compare_as_dates to never, ever allow bad 
2827
 
ERROR HY000: Received an invalid datetime value '2005-09-3a'.
1 by brian
clean slate 
2828
 
select * from t1 where f2 <= '2005-09-31' order by f2;
873.1.8 by Jay Pipes
Fixes Arg_comparator::can_compare_as_dates to never, ever allow bad 
2829
 
ERROR HY000: Received an invalid datetime value '2005-09-31'.
1 by brian
clean slate 
2830
 
select * from t1 where f2 <= '2005-09-3a' order by f2;
873.1.8 by Jay Pipes
Fixes Arg_comparator::can_compare_as_dates to never, ever allow bad 
2831
 
ERROR HY000: Received an invalid datetime value '2005-09-3a'.
1 by brian
clean slate 
2832
 
drop table t1;

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

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

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

2836
 
PRIMARY KEY(key_a,key_b));

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

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

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

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

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

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

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

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

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

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

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

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

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

2850
 
SELECT t2.key_a,foo 

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

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

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

2854
 
key_a	foo

2855
 
2	xx

2856
 
EXPLAIN SELECT t2.key_a,foo 

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

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

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

2860
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2863
 
1	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	

2864
 
SELECT t2.key_a,foo 

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

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

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

2868
 
key_a	foo

2869
 
2	xx

2870
 
EXPLAIN SELECT t2.key_a,foo 

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

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

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

2874
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2877
 
1	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	

2878
 
DROP TABLE t1,t2,t3;

2879
 
create  table t1 (f1 int);

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

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

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

2883
 
create table t3 (f4 int not null);

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

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

2886
 
f1	count

2887
 
1	0

2888
 
2	3

2889
 
drop table t1,t2,t3;

2890
 
create table t1 (f1 int unique);

2891
 
create table t2 (f2 int unique);

2892
 
create table t3 (f3 int unique);

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

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

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

2896
 
select * from t3 where f3 is null;

2897
 
f3

2898
 
NULL

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

2900
 
f2

2901
 
1

2902
 
drop table t1,t2,t3;

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

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

2905
 
create table t2 (f2 char not null);

2906
 
insert into t2 values('b');

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

2908
 
f1	f2	f2

2909
 
NULL	a	NULL

2910
 
drop table t1,t2;

2911
 
select * from (select * left join t on f1=f2) tt;
629.2.6 by Monty
Updated test output with new and improved error messages. 
2912
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'on f1=f2) tt' at line 1
1 by brian
clean slate 
2913
 
CREATE TABLE t1 (sku int PRIMARY KEY, pr int);

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

2915
 
INSERT INTO t1 VALUES

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

2917
 
INSERT INTO t2 VALUES 

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

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

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

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

2922
 
sku	sppr	name	sku	pr

2923
 
20	10	bbb	10	10

2924
 
20	10	bbb	20	10

2925
 
EXPLAIN

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

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

2928
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2929
 
1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1
201 by Brian Aker
Convert default engine to Innodb 
2930
 
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where
1 by brian
clean slate 
2931
 
DROP TABLE t1,t2;

2932
 
create table t1 (a int);

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

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

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

2936
 
analyze table t2;

2937
 
Table	Op	Msg_type	Msg_text

2938
 
test.t2	analyze	status	OK

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

2940
 
Z

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

2942
 
drop table t1, t2;

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

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

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

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

2947
 
INSERT INTO t2 VALUES

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

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

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

2951
 
EXPLAIN

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

2953
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
2954
 
1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using where; Using index
1685.7.6 by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output 
2955
 
1	SIMPLE	t2	ref	c	c	5	test.t1.a	1	Using where
1 by brian
clean slate 
2956
 
EXPLAIN

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

2958
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
2959
 
1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using where; Using index
1685.7.6 by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output 
2960
 
1	SIMPLE	t2	ref	c	c	5	test.t1.a	1	Using where
1 by brian
clean slate 
2961
 
DROP TABLE t1, t2;

2962
 
create table t1 (
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2963
 
a int    not null auto_increment primary key,
397 by Brian Aker
Remove tiny/small int. 
2964
 
b int             not null,

2965
 
c int             not null
1 by brian
clean slate 
2966
 
);

2967
 
create table t2 (
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2968
 
a int    not null auto_increment primary key,
397 by Brian Aker
Remove tiny/small int. 
2969
 
b int             not null,
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2970
 
c int    not null,
1 by brian
clean slate 
2971
 
d varchar(50)

2972
 
);

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

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

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

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

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

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

2979
 
1	0	1	1	0	1	NULL

2980
 
2	0	1	NULL	NULL	NULL	NULL

2981
 
drop table t1,t2;

2982
 
SELECT 0.9888889889 * 1.011111411911;

2983
 
0.9888889889 * 1.011111411911

2984
 
0.9998769417899202067879

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

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

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

2988
 
INSERT INTO t2 VALUES

2989
 
(1), (1), (1), (1), (1), (1), (1), (1),

2990
 
(2), (2), (2), (2),

2991
 
(3), (3),

2992
 
(4);

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

2994
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2995
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

2996
 
1	SIMPLE	t2	ref	idx	idx	4	const	7	Using index

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

2998
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2999
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

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

3001
 
DROP TABLE t1, t2;

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

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

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

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

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

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

3008
 
EXPLAIN

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

3010
 
WHERE t1.id=2;

3011
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3012
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

3013
 
1	SIMPLE	t2	const	idx1	NULL	NULL	NULL	1
1685.7.6 by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output 
3014
 
1	SIMPLE	t3	ref	idx1	idx1	5	const	2	Using where
1 by brian
clean slate 
3015
 
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id

3016
 
WHERE t1.id=2;

3017
 
id	a	b	c	d	e

3018
 
2	NULL	NULL	NULL	2	10

3019
 
2	NULL	NULL	NULL	2	20

3020
 
2	NULL	NULL	NULL	2	40

3021
 
2	NULL	NULL	NULL	2	50

3022
 
DROP TABLE t1,t2,t3;

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

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

3025
 
INSERT INTO t1 VALUES

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

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

3028
 
INSERT INTO t2 VALUES

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

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

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

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

3033
 
EXPLAIN SELECT t2.* 

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

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

3036
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
383.1.16 by Brian Aker
Force client communication into UTF8 
3037
 
1	SIMPLE	t1	range	PRIMARY	PRIMARY	42	NULL	2	Using where

3038
 
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	66	test.t1.fk	1	Using where
1 by brian
clean slate 
3039
 
EXPLAIN SELECT t2.* 

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

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

3042
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
383.1.16 by Brian Aker
Force client communication into UTF8 
3043
 
1	SIMPLE	t1	range	PRIMARY	PRIMARY	42	NULL	2	Using where

3044
 
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	66	test.t1.fk	1	Using where
1 by brian
clean slate 
3045
 
EXPLAIN SELECT t2.* 

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

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

3048
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
383.1.16 by Brian Aker
Force client communication into UTF8 
3049
 
1	SIMPLE	t1	range	PRIMARY	PRIMARY	42	NULL	2	Using where

3050
 
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	66	test.t1.fk	1	Using where
1 by brian
clean slate 
3051
 
DROP TABLE t1,t2;

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

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

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

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

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

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

3058
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3059
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1
383.1.16 by Brian Aker
Force client communication into UTF8 
3060
 
1	SIMPLE	t2	const	b	b	82	const	1	Using index
1 by brian
clean slate 
3061
 
DROP TABLE t1,t2;

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

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

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

3065
 
INSERT INTO t1 VALUES 

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

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

3068
 
INSERT INTO t2 VALUES

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

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

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

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

3073
 
INSERT INTO t3 VALUES

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

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

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

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

3078
 
EXPLAIN

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

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

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

3082
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3083
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1
1100.1.1 by Brian Aker
Disable MRR 
3084
 
1	SIMPLE	t2	range	si	si	5	NULL	4	Using where
1 by brian
clean slate 
3085
 
1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where

3086
 
EXPLAIN

3087
 
SELECT t3.a FROM t1,t2,t3

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

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

3090
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3091
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1
383.1.16 by Brian Aker
Force client communication into UTF8 
3092
 
1	SIMPLE	t3	range	PRIMARY,ci	ci	19	NULL	6	Using where; Using index
201 by Brian Aker
Convert default engine to Innodb 
3093
 
1	SIMPLE	t2	ref	si,ai	ai	5	test.t3.a	1	Using where
1 by brian
clean slate 
3094
 
EXPLAIN 

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

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

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

3098
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3099
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1
1100.1.1 by Brian Aker
Disable MRR 
3100
 
1	SIMPLE	t2	range	si	si	5	NULL	2	Using where
1 by brian
clean slate 
3101
 
1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where

3102
 
EXPLAIN 

3103
 
SELECT t3.a FROM t1,t2,t3

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

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

3106
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3107
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1
1100.1.1 by Brian Aker
Disable MRR 
3108
 
1	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using where
1 by brian
clean slate 
3109
 
1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where

3110
 
DROP TABLE t1,t2,t3;

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

3112
 
CREATE TABLE t2 ( f11 int PRIMARY KEY );

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

3114
 
INSERT INTO t2 VALUES (62);

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

3116
 
f1	f2	f3	f4	f5	f6	checked_out	f11

3117
 
1	1	1	0	0	0	0	NULL

3118
 
DROP TABLE t1, t2;

3119
 
DROP TABLE IF EXISTS t1;

3120
 
CREATE TABLE t1(a int);

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

3122
 
SELECT * FROM t1 LIMIT 2, -1;
629.2.6 by Monty
Updated test output with new and improved error messages. 
3123
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '-1' at line 1
1 by brian
clean slate 
3124
 
DROP TABLE t1;

3125
 
CREATE TABLE t1 (

3126
 
ID_with_null int NULL,

3127
 
ID_better int NOT NULL,

3128
 
INDEX idx1 (ID_with_null),

3129
 
INDEX idx2 (ID_better)

3130
 
);

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

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

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

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

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

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

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

3138
 
COUNT(*)

3139
 
128

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

3141
 
COUNT(*)

3142
 
2

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

3144
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3145
 
1	SIMPLE	t1	index_merge	idx1,idx2	idx2,idx1	4,5	NULL	1	Using intersect(idx2,idx1); Using where; Using index
1 by brian
clean slate 
3146
 
DROP INDEX idx1 ON t1;

3147
 
CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);

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

3149
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3150
 
1	SIMPLE	t1	index_merge	idx1,idx2	idx2,idx1	4,5	NULL	1	Using intersect(idx2,idx1); Using where; Using index
1 by brian
clean slate 
3151
 
DROP TABLE t1;

3152
 
CREATE TABLE t1 (

3153
 
ID1_with_null int NULL,

3154
 
ID2_with_null int NULL,

3155
 
ID_better int NOT NULL,

3156
 
INDEX idx1 (ID1_with_null, ID2_with_null),

3157
 
INDEX idx2 (ID_better)

3158
 
);

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

3160
 
(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);

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

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

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

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

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

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

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

3168
 
COUNT(*)

3169
 
24

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

3171
 
COUNT(*)

3172
 
24

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

3174
 
COUNT(*)

3175
 
192

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

3177
 
COUNT(*)

3178
 
2

3179
 
EXPLAIN SELECT * FROM t1

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

3181
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
496.1.10 by Paul McCullagh
Fizxed the select.test result file 
3182
 
1	SIMPLE	t1	index_merge	idx1,idx2	idx2,idx1	4,10	NULL	#	Using intersect(idx2,idx1); Using where; Using index
1 by brian
clean slate 
3183
 
EXPLAIN SELECT * FROM t1

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

3185
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
496.1.10 by Paul McCullagh
Fizxed the select.test result file 
3186
 
1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	#	Using where
1 by brian
clean slate 
3187
 
EXPLAIN SELECT * FROM t1

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

3189
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
496.1.10 by Paul McCullagh
Fizxed the select.test result file 
3190
 
1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	#	Using where
1 by brian
clean slate 
3191
 
DROP INDEX idx1 ON t1;

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

3193
 
EXPLAIN SELECT * FROM t1

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

3195
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3196
 
1	SIMPLE	t1	index_merge	idx1,idx2	idx2,idx1	4,10	NULL	1	Using intersect(idx2,idx1); Using where; Using index
1 by brian
clean slate 
3197
 
EXPLAIN SELECT * FROM t1

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

3199
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3200
 
1	SIMPLE	t1	index_merge	idx1,idx2	idx2,idx1	4,10	NULL	1	Using intersect(idx2,idx1); Using where; Using index
1 by brian
clean slate 
3201
 
EXPLAIN SELECT * FROM t1

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

3203
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3204
 
1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	2	Using where
1 by brian
clean slate 
3205
 
EXPLAIN SELECT * FROM t1

3206
 
WHERE ID_better=1 AND ID1_with_null IS NULL AND 

3207
 
(ID2_with_null=1 OR ID2_with_null=2);

3208
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3209
 
1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	2	Using where
1 by brian
clean slate 
3210
 
DROP TABLE t1;

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

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

3213
 
ANALYZE TABLE t1;

3214
 
Table	Op	Msg_type	Msg_text

3215
 
test.t1	analyze	status	OK

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

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

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

3219
 
ANALYZE TABLE t2;

3220
 
Table	Op	Msg_type	Msg_text

3221
 
test.t2	analyze	status	OK

3222
 
EXPLAIN

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

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

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

3226
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3227
 
1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1
1782.4.1 by Brian Aker
Fix for 64bit issue around timestamp 
3228
 
1	SIMPLE	t1	range	ts	ts	9	NULL	1	Using where
1 by brian
clean slate 
3229
 
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30

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

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

3232
 
a	ts	a	dt1	dt2

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

3234
 
DROP TABLE t1,t2;
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
3235
 
create table t1 (a bigint);
1 by brian
clean slate 
3236
 
insert into t1 values
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
3237
 
(if(1, 92233720368547758, 1)),

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

3239
 
(coalesce(92233720368547758, 1));
1 by brian
clean slate 
3240
 
select * from t1;

3241
 
a
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
3242
 
92233720368547758

3243
 
92233720368547758

3244
 
92233720368547758
1 by brian
clean slate 
3245
 
drop table t1;

3246
 
create table t1 select

3247
 
if(1, 9223372036854775808, 1) i,

3248
 
case when 1 then 9223372036854775808 else 1 end c,

3249
 
coalesce(9223372036854775808, 1) co;

3250
 
show create table t1;

3251
 
Table	Create Table
352.2.1 by Harrison Fisk
Fix for bugs 259843 and 256482 
3252
 
t1	CREATE TABLE `t1` (
1743.5.2 by LinuxJedi
Alter many test cases for the new SHOW CREATE TABLE output 
3253
 
  `i` DECIMAL(19,0) NOT NULL,

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

3255
 
  `co` DECIMAL(19,0) NOT NULL
1638.10.82 by Stewart Smith
fix some tests for explicit COLLATE in CREATE TABLE 
3256
 
) ENGINE=InnoDB COLLATE = utf8_general_ci
1 by brian
clean slate 
3257
 
drop table t1;

3258
 
select
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
3259
 
if(1, 1111111111111111111, 1) i,

3260
 
case when 1 then 1111111111111111111 else 1 end c,

3261
 
coalesce(1111111111111111111, 1) co;
1 by brian
clean slate 
3262
 
i	c	co

3263
 
1111111111111111111	1111111111111111111	1111111111111111111

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

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

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

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

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

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

3270
 
SELECT * FROM t2;

3271
 
name	n

3272
 
bb	1

3273
 
aa	2

3274
 
cc   	3

3275
 
cc 	4

3276
 
cc	5

3277
 
bb 	6

3278
 
cc 	7

3279
 
SELECT * FROM t2 ORDER BY name;

3280
 
name	n

3281
 
aa	2

3282
 
bb	1

3283
 
bb 	6

3284
 
cc 	4

3285
 
cc   	3

3286
 
cc	5

3287
 
cc 	7

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

3289
 
name	LENGTH(name)	n

3290
 
aa	2	2

3291
 
bb	2	1

3292
 
bb 	3	6

3293
 
cc 	4	4

3294
 
cc   	5	3

3295
 
cc	2	5

3296
 
cc 	3	7

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

3298
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3299
 
1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where
1 by brian
clean slate 
3300
 
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3301
 
name	LENGTH(name)	n

3302
 
cc   	5	3

3303
 
cc	2	5

3304
 
cc 	3	7

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

3306
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3307
 
1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where
1 by brian
clean slate 
3308
 
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3309
 
name	LENGTH(name)	n

3310
 
cc   	5	3

3311
 
cc 	4	4

3312
 
cc	2	5

3313
 
cc 	3	7

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

3315
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3316
 
1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using filesort
1 by brian
clean slate 
3317
 
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3318
 
name	LENGTH(name)	n

3319
 
cc 	4	4

3320
 
cc   	5	3

3321
 
cc	2	5

3322
 
cc 	3	7

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

3324
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3325
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5
383.1.16 by Brian Aker
Force client communication into UTF8 
3326
 
1	SIMPLE	t2	ref	name	name	15	test.t1.name	1
1 by brian
clean slate 
3327
 
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3328
 
name	name	n

3329
 
ccc	NULL	NULL

3330
 
bb	bb	1

3331
 
bb	bb 	6

3332
 
cc 	cc   	3

3333
 
cc 	cc	5

3334
 
cc 	cc 	7

3335
 
aa  	aa	2

3336
 
aa	aa	2

3337
 
DROP TABLE t1,t2;

3338
 
CREATE TABLE t1 (name text);

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

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

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

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

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

3344
 
SELECT * FROM t2;

3345
 
name	n

3346
 
bb	1

3347
 
aa	2

3348
 
cc   	3

3349
 
cc 	4

3350
 
cc	5

3351
 
bb 	6

3352
 
cc 	7

3353
 
SELECT * FROM t2 ORDER BY name;

3354
 
name	n

3355
 
aa	2

3356
 
bb	1

3357
 
bb 	6

3358
 
cc 	4

3359
 
cc   	3

3360
 
cc	5

3361
 
cc 	7

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

3363
 
name	LENGTH(name)	n

3364
 
aa	2	2

3365
 
bb	2	1

3366
 
bb 	3	6

3367
 
cc 	4	4

3368
 
cc   	5	3

3369
 
cc	2	5

3370
 
cc 	3	7

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

3372
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3373
 
1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where
1 by brian
clean slate 
3374
 
SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';

3375
 
name	LENGTH(name)	n

3376
 
cc   	5	3

3377
 
cc	2	5

3378
 
cc 	3	7

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

3380
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3381
 
1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where
1 by brian
clean slate 
3382
 
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';

3383
 
name	LENGTH(name)	n

3384
 
cc   	5	3

3385
 
cc 	4	4

3386
 
cc	2	5

3387
 
cc 	3	7

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

3389
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3390
 
1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using filesort
1 by brian
clean slate 
3391
 
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3392
 
name	LENGTH(name)	n

3393
 
cc 	4	4

3394
 
cc   	5	3

3395
 
cc	2	5

3396
 
cc 	3	7

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

3398
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3399
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5
383.1.16 by Brian Aker
Force client communication into UTF8 
3400
 
1	SIMPLE	t2	ref	name	name	15	test.t1.name	1
1 by brian
clean slate 
3401
 
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3402
 
name	name	n

3403
 
ccc	NULL	NULL

3404
 
bb	bb	1

3405
 
bb	bb 	6

3406
 
cc 	cc   	3

3407
 
cc 	cc	5

3408
 
cc 	cc 	7

3409
 
aa  	aa	2

3410
 
aa	aa	2

3411
 
DROP TABLE t1,t2;

3412
 
CREATE TABLE t1 (

3413
 
access_id int NOT NULL default '0',

3414
 
name varchar(20) default NULL,

3415
 
rank int NOT NULL default '0',

3416
 
KEY idx (access_id)

3417
 
);

3418
 
CREATE TABLE t2 (

3419
 
faq_group_id int NOT NULL default '0',

3420
 
faq_id int NOT NULL default '0',

3421
 
access_id int default NULL,

3422
 
UNIQUE KEY idx1 (faq_id),

3423
 
KEY idx2 (faq_group_id,faq_id)

3424
 
);

3425
 
INSERT INTO t1 VALUES 

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

3427
 
INSERT INTO t2 VALUES

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

3429
 
SELECT t2.faq_id 

3430
 
FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)

3431
 
ON (t1.access_id = t2.access_id)

3432
 
LEFT JOIN t2 t

3433
 
ON (t.faq_group_id = t2.faq_group_id AND

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

3435
 
WHERE

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

3437
 
faq_id

3438
 
265

3439
 
SELECT t2.faq_id 

3440
 
FROM t1 INNER JOIN t2

3441
 
ON (t1.access_id = t2.access_id)

3442
 
LEFT JOIN t2 t

3443
 
ON (t.faq_group_id = t2.faq_group_id AND

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

3445
 
WHERE

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

3447
 
faq_id

3448
 
265

3449
 
DROP TABLE t1,t2;

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

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

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

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

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

3455
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3456
 
1	SIMPLE	f1	index	inx	inx	10	NULL	7	Using where; Using index
201 by Brian Aker
Convert default engine to Innodb 
3457
 
1	SIMPLE	f2	ref	inx	inx	5	test.f1.b	3	Using where; Using index
1 by brian
clean slate 
3458
 
DROP TABLE t1;

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

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

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

3462
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3464
 
31	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

3465
 
32	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

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

3467
 
ERROR HY000: Too high level of nesting for select

3468
 
DROP TABLE t1;

3469
 
CREATE TABLE t1 (
223 by Brian Aker
Cleanup int() work. 
3470
 
c1 int NOT NULL AUTO_INCREMENT,
1 by brian
clean slate 
3471
 
c2 varchar(1000) DEFAULT NULL,
223 by Brian Aker
Cleanup int() work. 
3472
 
c3 bigint DEFAULT NULL,

3473
 
c4 bigint DEFAULT NULL,
1 by brian
clean slate 
3474
 
PRIMARY KEY (c1)

3475
 
);

3476
 
EXPLAIN EXTENDED 

3477
 
SELECT  join_2.c1  

3478
 
FROM 

3479
 
t1 AS join_0, 

3480
 
t1 AS join_1, 

3481
 
t1 AS join_2, 

3482
 
t1 AS join_3, 

3483
 
t1 AS join_4, 

3484
 
t1 AS join_5, 

3485
 
t1 AS join_6, 

3486
 
t1 AS join_7

3487
 
WHERE 

3488
 
join_0.c1=join_1.c1  AND 

3489
 
join_1.c1=join_2.c1  AND 

3490
 
join_2.c1=join_3.c1  AND 

3491
 
join_3.c1=join_4.c1  AND 

3492
 
join_4.c1=join_5.c1  AND 

3493
 
join_5.c1=join_6.c1  AND 

3494
 
join_6.c1=join_7.c1 

3495
 
OR 

3496
 
join_0.c2 < '?'  AND 

3497
 
join_1.c2 < '?'  AND

3498
 
join_2.c2 > '?'  AND

3499
 
join_2.c2 < '!'  AND

3500
 
join_3.c2 > '?'  AND 

3501
 
join_4.c2 = '?'  AND 

3502
 
join_5.c2 <> '?' AND

3503
 
join_6.c2 <> '?' AND 

3504
 
join_7.c2 >= '?' AND

3505
 
join_0.c1=join_1.c1  AND 

3506
 
join_1.c1=join_2.c1  AND 

3507
 
join_2.c1=join_3.c1  AND

3508
 
join_3.c1=join_4.c1  AND 

3509
 
join_4.c1=join_5.c1  AND 

3510
 
join_5.c1=join_6.c1  AND 

3511
 
join_6.c1=join_7.c1

3512
 
GROUP BY 

3513
 
join_3.c1,

3514
 
join_2.c1,

3515
 
join_7.c1,

3516
 
join_1.c1,

3517
 
join_0.c1;

3518
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
201 by Brian Aker
Convert default engine to Innodb 
3519
 
1	SIMPLE	join_0	ALL	PRIMARY	NULL	NULL	NULL	1	100.00	Using temporary; Using filesort

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

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

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

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

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

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

3526
 
1	SIMPLE	join_7	eq_ref	PRIMARY	PRIMARY	4	test.join_5.c1	1	100.00	Using where
1 by brian
clean slate 
3527
 
Warnings:
383.1.16 by Brian Aker
Force client communication into UTF8 
3528
 
Note	1003	select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_4`.`c2` = '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`
1 by brian
clean slate 
3529
 
SHOW WARNINGS;

3530
 
Level	Code	Message
383.1.16 by Brian Aker
Force client communication into UTF8 
3531
 
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`
1 by brian
clean slate 
3532
 
DROP TABLE t1;

3533
 
SELECT 1 AS ` `;

3534
 









3535



1










3536



Warnings:










3537



Warning	1474	Name ' ' has become ''










3538



SELECT 1 AS `  `;










3539












3540



1










3541



Warnings:










3542



Warning	1474	Name '  ' has become ''










3543



SELECT 1 AS ` x`;










3544



x










3545



1










3546



Warnings:










3547



Warning	1466	Leading spaces are removed from name ' x'








413.2.2
by Brian Aker


Removed UNSIGNED from parser.



3548



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










3549



CREATE TABLE t2 (c21 INT NOT NULL, 








1
by brian


clean slate



3550



c22 INT DEFAULT NULL, 










3551



KEY(c21, c22));








413.2.2
by Brian Aker


Removed UNSIGNED from parser.



3552



CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0, 








1
by brian


clean slate



3553



c32 INT DEFAULT NULL, 










3554



c33 INT NOT NULL, 








413.2.2
by Brian Aker


Removed UNSIGNED from parser.



3555



c34 INT DEFAULT 0,








1
by brian


clean slate



3556



KEY (c33, c34, c32));










3557



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










3558



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










3559



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










3560



(2, 2, 0, 0), 










3561



(3, 3, 1, 0), 










3562



(4, 4, 0, 0), 










3563



(5, 5, 1, 0);










3564



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










3565



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










3566



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










3567



ORDER BY c32;










3568



c32










3569



1










3570



1










3571



3










3572



3










3573



5










3574



5










3575



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










3576



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










3577



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










3578



ORDER BY c32 DESC;










3579



c32










3580



5










3581



5










3582



3










3583



3










3584



1










3585



1










3586



DROP TABLE t1, t2, t3;










3587












3588



#










3589



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










3590



# Inserting Data.










3591



#










3592



DROP TABLE IF EXISTS t1;










3593



DROP TABLE IF EXISTS t2;










3594












3595



CREATE TABLE t1(










3596



c1 DECIMAL(10, 2),










3597



c2 FLOAT);










3598












3599



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










3600












3601



CREATE TABLE t2(










3602



c3 DECIMAL(10, 2))










3603



SELECT










3604



c1 * c2 AS c3










3605



FROM t1;










3606












3607



SELECT * FROM t1;










3608



c1	c2










3609



0.00	1










3610



2.00	3










3611



4.00	5










3612












3613



SELECT * FROM t2;










3614



c3










3615



0.00










3616



6.00










3617



20.00










3618












3619



DROP TABLE t1;










3620



DROP TABLE t2;










3621












3622



CREATE TABLE t1 (c1 BIGINT NOT NULL);










3623



INSERT INTO t1 (c1) VALUES (1);










3624



SELECT * FROM t1 WHERE c1 > NULL + 1;










3625



c1










3626



DROP TABLE t1;










3627












3628



CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY);










3629



INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0');










3630



SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar');










3631



a










3632



foo0










3633



DROP TABLE t1;










3634



CREATE TABLE t1 (a INT, b INT);










3635



CREATE TABLE t2 (a INT, c INT, KEY(a));










3636



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










3637



INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),










3638



(2, 1), (2, 2), (2, 3), (2, 4), (2, 5),










3639



(3, 1), (3, 2), (3, 3), (3, 4), (3, 5),










3640



(4, 1), (4, 2), (4, 3), (4, 4), (4, 5);










3641



FLUSH STATUS;










3642



SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;










3643



b










3644



1










3645



2










3646



SHOW STATUS LIKE 'Handler_read%';










3647



Variable_name	Value








201
by Brian Aker


Convert default engine to Innodb



3648



Handler_read_first	1










3649



Handler_read_key	5








1
by brian


clean slate



3650



Handler_read_next	0










3651



Handler_read_prev	0










3652



Handler_read_rnd	0








1561.3.11
by Joe Daly


get tests working



3653



Handler_read_rnd_next	6








1
by brian


clean slate



3654



DROP TABLE t1, t2;










3655



End of 5.0 tests










3656



create table t1(a INT, KEY (a));










3657



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










3658



SELECT a FROM t1 ORDER BY a LIMIT 2;










3659



a










3660



1










3661



2










3662



SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;










3663



a










3664



3










3665



4










3666



5










3667



SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;










3668



a










3669



3










3670



4










3671



5










3672



DROP TABLE t1;








1240.8.8
by Dennis Schoen


record changes to select testcase



3673



CREATE TABLE t1 (a INT);










3674



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










3675



CREATE TABLE t2 (b INT);










3676



INSERT INTO t2 VALUES (2);










3677



SELECT * FROM t1 WHERE a = 1 + 1;










3678



a










3679



2










3680



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










3681



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3682



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










3683



Warnings:










3684



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










3685



SELECT * FROM t1 HAVING a = 1 + 1;










3686



a










3687



2










3688



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










3689



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3690



1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	










3691



Warnings:










3692



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










3693



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










3694



a	b










3695



4	2










3696



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










3697



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3698



1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	










3699



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










3700



Warnings:










3701



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










3702



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










3703



b	a










3704



2	3










3705



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










3706



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3707



1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	










3708



1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	










3709



Warnings:










3710



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










3711



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










3712



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3713



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










3714



Warnings:










3715



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








1435.1.5
by Stewart Smith


select test leaving around tables carelessly



3716



DROP TABLE t1, t2;