← 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

1275
 
250502	250501

1276
 
250503	250501

1277
 
250504	250501

1278
 
250505	250501

1279
 
250501	250502

1280
 
250502	250502

1281
 
250503	250502

1282
 
250504	250502

1283
 
250505	250502

1284
 
250501	250503

1285
 
250502	250503

1286
 
250503	250503

1287
 
250504	250503

1288
 
250505	250503

1289
 
250501	250504

1290
 
250502	250504

1291
 
250503	250504

1292
 
250504	250504

1293
 
250505	250504

1294
 
250501	250505

1295
 
250502	250505

1296
 
250503	250505

1297
 
250504	250505

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

1932
 
show tables from test like "s%";

1933
 
Tables_in_test (s%)

1934
 
show tables from test like "t?";

1935
 
Tables_in_test (t?)

1936
 
show full columns from t2;

1937
 
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
221 by Brian Aker
First pass of removing length types for ints. 
1938
 
auto	int	NULL	NO	PRI	NULL	auto_increment	#
873.2.35 by Monty Taylor
Update tests based on how Toru's latest patch changes create table statements. 
1939
 
fld1	int	NULL	NO	UNI	0		#	

1940
 
companynr	int	NULL	NO		0		#	

1941
 
fld3	varchar(30)	utf8_general_ci	NO	MUL			#	

1942
 
fld4	varchar(35)	utf8_general_ci	NO				#	

1943
 
fld5	varchar(35)	utf8_general_ci	NO				#	

1944
 
fld6	varchar(4)	utf8_general_ci	NO				#
1 by brian
clean slate 
1945
 
show full columns from t2 from test like 'f%';

1946
 
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
873.2.35 by Monty Taylor
Update tests based on how Toru's latest patch changes create table statements. 
1947
 
fld1	int	NULL	NO	UNI	0		#	

1948
 
fld3	varchar(30)	utf8_general_ci	NO	MUL			#	

1949
 
fld4	varchar(35)	utf8_general_ci	NO				#	

1950
 
fld5	varchar(35)	utf8_general_ci	NO				#	

1951
 
fld6	varchar(4)	utf8_general_ci	NO				#
1 by brian
clean slate 
1952
 
show full columns from t2 from test like 's%';

1953
 
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment

1954
 
drop table t4, t3, t2, t1;

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

1958
 
PRIMARY KEY  (id),

1959
 
UNIQUE KEY pseudo (pseudo)

1960
 
);

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

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

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

1964
 
rnd1

1965
 
DROP TABLE t1;
1063.9.24 by Stewart Smith
select.test for MyISAM as temp only 
1966
 
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) binary default NULL,  description varchar(255) default NULL,  UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
1 by brian
clean slate 
1967
 
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 
1968
 
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 
1969
 
INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);

1970
 
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 
1971
 
ERROR HY000: Received an invalid datetime value 'wrong-date-value'.
1 by brian
clean slate 
1972
 
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;

1973
 
gvid	the_success	the_fail	the_size	the_time

1974
 
DROP TABLE t1,t2;
223 by Brian Aker
Cleanup int() work. 
1975
 
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 
1976
 
INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);

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

1978
 
wss_type

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

1980
 
wss_type

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

1982
 
wss_type

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

1984
 
wss_type

1985
 
102935229216544093

1986
 
select wss_type from t1 where wss_type =102935229216544093;

1987
 
wss_type

1988
 
102935229216544093

1989
 
drop table t1;

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

1991
 
select @a;

1992
 
@a

1993
 
3

1994
 
select @b;

1995
 
@b

1996
 
aaaa

1997
 
select @c;

1998
 
@c

1999
 
6.260

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

2001
 
insert into t1 values ();

2002
 
insert into t1 values ();

2003
 
insert into t1 values ();

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

2005
 
a	a

2006
 
1	1

2007
 
2	1

2008
 
3	1

2009
 
1	2

2010
 
2	2

2011
 
3	2

2012
 
1	3

2013
 
2	3

2014
 
3	3

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

2016
 
a	a

2017
 
1	1

2018
 
2	1

2019
 
3	1

2020
 
1	2

2021
 
2	2

2022
 
3	2

2023
 
1	3

2024
 
2	3

2025
 
3	3

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

2027
 
a	a

2028
 
1	1

2029
 
2	1

2030
 
3	1

2031
 
1	2

2032
 
2	2

2033
 
3	2

2034
 
1	3

2035
 
2	3

2036
 
3	3

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

2038
 
a	a

2039
 
1	1

2040
 
2	1

2041
 
3	1

2042
 
1	2

2043
 
2	2

2044
 
3	2

2045
 
1	3

2046
 
2	3

2047
 
3	3

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

2049
 
a	a

2050
 
1	2
201 by Brian Aker
Convert default engine to Innodb 
2051
 
1	3
1 by brian
clean slate 
2052
 
2	2
201 by Brian Aker
Convert default engine to Innodb 
2053
 
2	3
1 by brian
clean slate 
2054
 
3	2

2055
 
3	3

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

2057
 
a	a

2058
 
2	1

2059
 
3	1

2060
 
2	2

2061
 
3	2

2062
 
2	3

2063
 
3	3

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

2065
 
a

2066
 
1

2067
 
2

2068
 
3

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

2070
 
a

2071
 
1

2072
 
2

2073
 
3

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

2075
 
a	a

2076
 
1	2

2077
 
1	3

2078
 
2	2

2079
 
2	3

2080
 
3	2

2081
 
3	3

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

2083
 
a	a

2084
 
1	NULL

2085
 
2	1

2086
 
2	2

2087
 
2	3

2088
 
3	1

2089
 
3	2

2090
 
3	3

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

2092
 
a

2093
 
1

2094
 
2

2095
 
3

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

2097
 
a

2098
 
1

2099
 
2

2100
 
3

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

2102
 
a

2103
 
1

2104
 
2

2105
 
3

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

2107
 
a

2108
 
1

2109
 
2

2110
 
3

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

2112
 
a	a

2113
 
NULL	1

2114
 
1	2

2115
 
2	2

2116
 
3	2

2117
 
1	3

2118
 
2	3

2119
 
3	3

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

2121
 
a	a

2122
 
2	1

2123
 
3	1

2124
 
2	2

2125
 
3	2

2126
 
2	3

2127
 
3	3

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

2129
 
a

2130
 
1

2131
 
2

2132
 
3

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

2134
 
a

2135
 
1

2136
 
2

2137
 
3

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

2139
 
a

2140
 
1

2141
 
2

2142
 
3

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

2144
 
a

2145
 
1

2146
 
2

2147
 
3

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

2149
 
a

2150
 
1

2151
 
2

2152
 
3

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

2154
 
a

2155
 
1

2156
 
2

2157
 
3

2158
 
drop table t1;
1063.9.24 by Stewart Smith
select.test for MyISAM as temp only 
2159
 
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 
2160
 
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 
2161
 
CREATE TEMPORARY TABLE t2 ( id int NOT NULL auto_increment,  PRIMARY KEY  (id)) ENGINE=MyISAM;
1 by brian
clean slate 
2162
 
INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);

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

2164
 
aa	id	t2_id	id

2165
 
2	8299	2517	2517

2166
 
3	8301	2518	2518

2167
 
4	8302	2519	2519

2168
 
5	8303	2520	2520

2169
 
6	8304	2521	2521

2170
 
drop table t1,t2;

2171
 
create table t1 (id1 int NOT NULL);

2172
 
create table t2 (id2 int NOT NULL);

2173
 
create table t3 (id3 int NOT NULL);

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

2175
 
insert into t1 values (1);

2176
 
insert into t1 values (2);

2177
 
insert into t2 values (1);

2178
 
insert into t4 values (1,1);

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

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

2181
 
id1	id2	id3	id4	id44

2182
 
1	1	NULL	NULL	NULL

2183
 
drop table t1,t2,t3,t4;

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

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

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

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

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

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

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

2191
 
s

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

2193
 
s

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

2195
 
s

2196
 
one 

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

2198
 
s	s

2199
 
two		two	

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

2201
 
s	s

2202
 
two		two	

2203
 
drop table t1, t2, t3;

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

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

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

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

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

2209
 
a	b	c	d

2210
 
3	2	3	4

2211
 
4	2	4	4

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

2213
 
a	b	c	d

2214
 
3	2	3	4

2215
 
4	2	4	4

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

2220
 
PRIMARY KEY  (i),

2221
 
UNIQUE KEY c (c)

2222
 
) ENGINE=MyISAM;

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

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

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

2226
 
EXPLAIN SELECT i FROM t1 WHERE i=1;

2227
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

2229
 
DROP TABLE t1;

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

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

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

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

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

2235
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

2239
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2244
 
INSERT INTO t1 VALUES ('London');

2245
 
INSERT INTO t1 VALUES ('Paris');

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

2247
 
city

2248
 
London

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

2250
 
city

2251
 
London

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

2253
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2256
 
city

2257
 
London

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

2259
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2262
 
city

2263
 
London

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

2267
 
select a-b  from t1 order by 1;

2268
 
a-b
358 by Brian Aker
More removal on modes. 
2269
 
-1
1 by brian
clean slate 
2270
 
0

2271
 
1

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

2273
 
a-b 	(a-b < 0)
358 by Brian Aker
More removal on modes. 
2274
 
-1	1
1 by brian
clean slate 
2275
 
0	0

2276
 
1	0

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

2278
 
d	(a-b >= 0)	b

2279
 
1	1	0

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

2282
 
a - b 

2283
 
-1
1 by brian
clean slate 
2284
 
0

2285
 
1

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

2289
 
a

2290
 
select distinct distinct * from t1;

2291
 
a

2292
 
select all distinct * from t1;

2293
 
ERROR HY000: Incorrect usage of ALL and DISTINCT

2294
 
select distinct all * from t1;

2295
 
ERROR HY000: Incorrect usage of ALL and DISTINCT

2296
 
drop table t1;

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

2299
 
kunde_id int NOT NULL default '0',

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

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

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

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

2305
 
firma varchar(128) NOT NULL default ''

2306
 
);

2307
 
INSERT INTO t1 VALUES 

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

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

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

2311
 
WHERE

2312
 
(

2313
 
(

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

2315
 
OR

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

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

2318
 
'Vorname1' != '' AND 'xxxx' != '')

2319
 
)

2320
 
AND

2321
 
(

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

2323
 
)

2324
 
)

2325
 
;

2326
 
kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht

2327
 
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname,

2328
 
geloescht FROM t1

2329
 
WHERE

2330
 
(

2331
 
(

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

2333
 
)

2334
 
AND

2335
 
(

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

2337
 
OR

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

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

2340
 
'xxxx' != '')

2341
 
)

2342
 
)

2343
 
;

2344
 
kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht

2345
 
SELECT COUNT(*) FROM t1 WHERE 

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

2347
 
AND FK_firma_id = 2;

2348
 
COUNT(*)

2349
 
0

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

2352
 
INSERT INTO t1 VALUES (0x4000000000000000);

2353
 
SELECT b FROM t1 WHERE b=0x4000000000000000;
1 by brian
clean slate 
2354
 
b
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2355
 
4611686018427387904
1 by brian
clean slate 
2356
 
DROP TABLE t1;
223 by Brian Aker
Cleanup int() work. 
2357
 
CREATE TABLE `t1` ( `gid` int default NULL, `uid` int default NULL);

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

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

2363
 
id	name	gid	uid	ident	level

2364
 
1	fs	NULL	NULL	0	READ

2365
 
drop table t1,t2,t3;

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

2370
 
KEY t1$profile_id (profile_id)

2371
 
);

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

2373
 
CREATE TABLE t2 (
396 by Brian Aker
Cleanup tiny and small int. 
2374
 
profile_id int default NULL,
223 by Brian Aker
Cleanup int() work. 
2375
 
queue_id int default NULL,

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

2378
 
);

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

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

2382
 
qtype int default NULL,

2383
 
seq int default NULL,

2384
 
warn_lvl int default NULL,

2385
 
crit_lvl int default NULL,
396 by Brian Aker
Cleanup tiny and small int. 
2386
 
rr1 int NOT NULL default '0',
223 by Brian Aker
Cleanup int() work. 
2387
 
rr2 int default NULL,
396 by Brian Aker
Cleanup tiny and small int. 
2388
 
default_queue int NOT NULL default '0',
1 by brian
clean slate 
2389
 
KEY t3$qtype (qtype),

2390
 
KEY t3$id (id)

2391
 
);

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

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

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

2395
 
WHERE 

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

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

2398
 
COUNT(*)

2399
 
4

2400
 
drop table t1,t2,t3;

2401
 
create table t1 (f1 int);

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

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

2404
 
create index idx1 on t2 (f4);

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

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

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

2408
 
f2

2409
 
1

2410
 
NULL

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

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

2415
 
select * from t2 where a > -1;

2416
 
a

2417
 
0

2418
 
254

2419
 
255

2420
 
drop table t2;

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

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

2423
 
a	c

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

2425
 
a	c

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

2427
 
a	c

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

2429
 
a	c

2430
 
drop table t1;

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

2432
 
create table t2(f3 int);

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

2434
 
f1

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

2436
 
f1

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

2438
 
f1

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

2440
 
insert into t2 values(2);

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

2442
 
f1	f2	f3

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

2444
 
f1	f2	f3

2445
 
2	NULL	2

2446
 
drop table t1,t2;

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

2448
 
create table t11 like t1;

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

2450
 
show table status like 't1%';

2451
 
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
590.1.4 by Stewart Smith
remove frm_version from TABLE_SHARE 
2452
 
t1	InnoDB	X	Compact	2	8192	X	X	X	X	X	X	X	X	utf8_general_ci	NULL		

2453
 
t11	InnoDB	X	Compact	0	0	X	X	X	X	X	X	X	X	utf8_general_ci	NULL
1 by brian
clean slate 
2454
 
select 123 as a from t1 where f1 is null;

2455
 
a

2456
 
drop table t1,t11;

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

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

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

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

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

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

2463
 
a	b	c	d

2464
 
1	2	1	1

2465
 
1	2	2	1

2466
 
1	2	3	1

2467
 
1	10		2

2468
 
1	11		2

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

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

2471
 
a	b	c	d

2472
 
1	10		4

2473
 
1	2	1	1

2474
 
1	2	2	1

2475
 
1	2	3	1

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

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

2478
 
a	b	c	d

2479
 
1	2	1	1

2480
 
1	2	2	1

2481
 
1	2	3	1

2482
 
1	10		2

2483
 
1	11		2

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

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

2486
 
a	b	c	d

2487
 
1	2	1	1

2488
 
1	2	2	1

2489
 
1	2	3	1

2490
 
DROP TABLE IF EXISTS t1, t2;

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

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

2493
 
insert into t1 values (1,1);

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

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

2496
 
count(f2) >0

2497
 
1

2498
 
drop table t1,t2;

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

2500
 
insert into t1 values(1,1);

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

2502
 
insert into t2 values(1,1);

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

2504
 
f1	f2

2505
 
1	1

2506
 
drop table t1,t2;

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

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

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

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

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

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

2513
 
DROP TABLE t1,t2,t3;

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

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

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

2517
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2518
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	

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

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

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

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

2523
 
DROP TABLE t1;

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

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

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

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

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

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

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

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

2536
 
a	b	a	b

2537
 
1	NULL	1	1

2538
 
1	NULL	2	1

2539
 
1	NULL	4	10

2540
 
2	10	4	10

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

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

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

2546
 
a	b	a	b

2547
 
1	NULL	1	1

2548
 
1	NULL	2	1

2549
 
1	NULL	4	10

2550
 
2	10	4	10

2551
 
DROP TABLE IF EXISTS t1,t2;

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

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

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

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

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

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

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

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

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

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

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

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

2576
 
min(key1)
166 by Brian Aker
Removal of FLOAT type 
2577
 
0.3762
1 by brian
clean slate 
2578
 
DROP TABLE t1,t2;
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2579
 
CREATE TABLE t1 (i BIGINT NOT NULL);
1 by brian
clean slate 
2580
 
INSERT INTO t1 VALUES (10);

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

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

2583
 
1	1	1	1

2584
 
DROP TABLE t1;

2585
 
CREATE TABLE t1 (c0 int);

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

2588
 
INSERT INTO t2 VALUES(@@server_id);

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

2591
 
X	X

2592
 
DROP TABLE t1, t2;

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

2596
 
K4N4 varchar(4) collate utf8_bin NOT NULL default '0000',
223 by Brian Aker
Cleanup int() work. 
2597
 
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 
2598
 
) ENGINE=MyISAM;
1 by brian
clean slate 
2599
 
INSERT INTO t1 VALUES 

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

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

2602
 
('WART', '0100', 1), 

2603
 
('WART', '0200', 1), 

2604
 
('WERT', '0100', 2), 

2605
 
('WORT','0200', 2), 

2606
 
('WT', '0100', 2), 

2607
 
('W_RT', '0100', 2), 

2608
 
('WaRT', '0100', 3), 

2609
 
('WART', '0300', 3), 

2610
 
('WRT' , '0400', 3), 

2611
 
('WURM', '0500', 3), 

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

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

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

2615
 
SELECT K2C4, K4N4, F2I4 FROM t1

2616
 
WHERE  K2C4 = 'WART' AND 

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

2618
 
K2C4	K4N4	F2I4

2619
 
WART	0200	1

2620
 
SELECT K2C4, K4N4, F2I4 FROM t1

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

2622
 
K2C4	K4N4	F2I4

2623
 
WART	0100	1

2624
 
WART	0200	1

2625
 
WART	0300	3

2626
 
DROP TABLE t1;

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

2628
 
create table t2 like t1;

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

2630
 
a

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

2632
 
a

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

2634
 
a	a	a

2635
 
drop table t1,t2;

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

2637
 
insert into t1 values ('Wall');

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

2639
 
min(s1)

2640
 
Wall

2641
 
Wall

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

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

2646
 
avg(distinct s1)

2647
 
0.0000

2648
 
0.0000

2649
 
drop table t1;

2650
 
create table t1 (s1 int);

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

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

2653
 
x

2654
 
NULL

2655
 
1.0000

2656
 
drop table t1;

2657
 
CREATE TABLE t1 (a int);

2658
 
CREATE TABLE t2 (a int);

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

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

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

2662
 
a

2663
 
2

2664
 
4

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

2666
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2667
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	

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

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

2670
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2671
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	

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

2673
 
DROP TABLE t1,t2;

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

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

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

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

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

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

2682
 
f1	f2	f3	f4

2683
 
 2	2	 2	 one 

2684
 
 2	2	 2	 two 

2685
 
drop table t1,t2;

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

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

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

2689
 
pk

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

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

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

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

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

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

2698
 
a	b	t1_val	t2_val

2699
 
1	1	1	1

2700
 
2	1	2	1

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

2702
 
b	a	t1_val	t2_val

2703
 
1	1	1	1

2704
 
1	2	2	1

2705
 
drop table t1, t2, t3;

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

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

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

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

2710
 
select a from t1 natural join t2;

2711
 
a

2712
 
b

2713
 
c

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

2715
 
a

2716
 
b

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

2719
 
CREATE TABLE t2 (`id` int);

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

2722
 
INSERT INTO t2 VALUES (2);

2723
 
INSERT INTO t3 VALUES (3);

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

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

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

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

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

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

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

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

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

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

2736
 
insert into t2 values (1,10);

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

2738
 
a	b	b

2739
 
1	10	10

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

2741
 
a	b	b

2742
 
1	10	10

2743
 
drop table t1, t2;

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

2745
 
create table t2 (b int);

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

2747
 
create table t4 (c int);

2748
 
insert into t1 values (1,1);

2749
 
insert into t2 values (1);

2750
 
insert into t3 values (1,1);

2751
 
insert into t4 values (1);

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

2753
 
a	c	b	b	a

2754
 
1	1	1	1	1

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

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

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

2758
 
a	c	b	b	a	c

2759
 
1	1	1	1	1	1

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

2761
 
c	a	b

2762
 
1	1	1

2763
 
drop table t1, t2, t3, t4;

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

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

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

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

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

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

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

2771
 
x	x

2772
 
1	1

2773
 
2	1

2774
 
3	1

2775
 
3	2

2776
 
3	3

2777
 
4	3

2778
 
4	4

2779
 
4	5

2780
 
drop table t1,t2,t3;
223 by Brian Aker
Cleanup int() work. 
2781
 
create table t1 (id int not null default '0');
1 by brian
clean slate 
2782
 
insert into t1 values (123),(191),(192);
383.1.28 by Brian Aker
Fix for CREATE TABLE/character sets. 
2783
 
create table t2 (id char(16) not null);
1 by brian
clean slate 
2784
 
insert into t2 values ('58013'),('58014'),('58015'),('58016');
383.1.28 by Brian Aker
Fix for CREATE TABLE/character sets. 
2785
 
create table t3 (a_id int not null, b_id char(16));
1 by brian
clean slate 
2786
 
insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');

2787
 
select count(*)

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

2789
 
count(*)

2790
 
6

2791
 
select count(*)

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

2793
 
count(*)

2794
 
6

2795
 
drop table t1,t2,t3;

2796
 
create table t1 (a int);

2797
 
create table t2 (b int);

2798
 
create table t3 (c int);

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

2800
 
a	b	c

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

2802
 
a	b	c

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

2804
 
a	b	c

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

2806
 
a	b	c

2807
 
drop table t1, t2 ,t3;

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

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

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

2811
 
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 
2812
 
ERROR HY000: Received an invalid datetime value '0'.
1 by brian
clean slate 
2813
 
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 
2814
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
2815
 
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 
2816
 
ERROR HY000: Received an invalid datetime value '2005-09-31'.
1 by brian
clean slate 
2817
 
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 
2818
 
ERROR HY000: Received an invalid datetime value '2005-09-3a'.
1 by brian
clean slate 
2819
 
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 
2820
 
ERROR HY000: Received an invalid datetime value '2005-09-31'.
1 by brian
clean slate 
2821
 
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 
2822
 
ERROR HY000: Received an invalid datetime value '2005-09-3a'.
1 by brian
clean slate 
2823
 
drop table t1;

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

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

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

2827
 
PRIMARY KEY(key_a,key_b));

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

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

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

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

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

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

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

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

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

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

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

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

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

2841
 
SELECT t2.key_a,foo 

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

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

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

2845
 
key_a	foo

2846
 
2	xx

2847
 
EXPLAIN SELECT t2.key_a,foo 

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

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

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

2851
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2854
 
1	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	

2855
 
SELECT t2.key_a,foo 

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

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

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

2859
 
key_a	foo

2860
 
2	xx

2861
 
EXPLAIN SELECT t2.key_a,foo 

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

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

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

2865
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2868
 
1	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	

2869
 
DROP TABLE t1,t2,t3;

2870
 
create  table t1 (f1 int);

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

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

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

2874
 
create table t3 (f4 int not null);

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

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

2877
 
f1	count

2878
 
1	0

2879
 
2	3

2880
 
drop table t1,t2,t3;

2881
 
create table t1 (f1 int unique);

2882
 
create table t2 (f2 int unique);

2883
 
create table t3 (f3 int unique);

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

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

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

2887
 
select * from t3 where f3 is null;

2888
 
f3

2889
 
NULL

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

2891
 
f2

2892
 
1

2893
 
drop table t1,t2,t3;

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

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

2896
 
create table t2 (f2 char not null);

2897
 
insert into t2 values('b');

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

2899
 
f1	f2	f2

2900
 
NULL	a	NULL

2901
 
drop table t1,t2;

2902
 
select * from (select * left join t on f1=f2) tt;
629.2.6 by Monty
Updated test output with new and improved error messages. 
2903
 
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 
2904
 
CREATE TABLE t1 (sku int PRIMARY KEY, pr int);

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

2906
 
INSERT INTO t1 VALUES

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

2908
 
INSERT INTO t2 VALUES 

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

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

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

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

2913
 
sku	sppr	name	sku	pr

2914
 
20	10	bbb	10	10

2915
 
20	10	bbb	20	10

2916
 
EXPLAIN

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

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

2919
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

2923
 
create table t1 (a int);

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

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

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

2927
 
analyze table t2;

2928
 
Table	Op	Msg_type	Msg_text

2929
 
test.t2	analyze	status	OK

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

2931
 
Z

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

2933
 
drop table t1, t2;

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

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

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

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

2938
 
INSERT INTO t2 VALUES

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

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

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

2942
 
EXPLAIN

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

2944
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
2945
 
1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using where; Using index

2946
 
1	SIMPLE	t2	ref	c	c	5	test.t1.a	1
1 by brian
clean slate 
2947
 
EXPLAIN

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

2949
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
2950
 
1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using where; Using index

2951
 
1	SIMPLE	t2	ref	c	c	5	test.t1.a	1
1 by brian
clean slate 
2952
 
DROP TABLE t1, t2;

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

2956
 
c int             not null
1 by brian
clean slate 
2957
 
);

2958
 
create table t2 (
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2959
 
a int    not null auto_increment primary key,
397 by Brian Aker
Remove tiny/small int. 
2960
 
b int             not null,
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
2961
 
c int    not null,
1 by brian
clean slate 
2962
 
d varchar(50)

2963
 
);

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

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

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

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

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

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

2970
 
1	0	1	1	0	1	NULL

2971
 
2	0	1	NULL	NULL	NULL	NULL

2972
 
drop table t1,t2;

2973
 
SELECT 0.9888889889 * 1.011111411911;

2974
 
0.9888889889 * 1.011111411911

2975
 
0.9998769417899202067879

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

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

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

2979
 
INSERT INTO t2 VALUES

2980
 
(1), (1), (1), (1), (1), (1), (1), (1),

2981
 
(2), (2), (2), (2),

2982
 
(3), (3),

2983
 
(4);

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

2985
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2986
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

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

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

2989
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2990
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

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

2992
 
DROP TABLE t1, t2;

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

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

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

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

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

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

2999
 
EXPLAIN

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

3001
 
WHERE t1.id=2;

3002
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3003
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

3004
 
1	SIMPLE	t2	const	idx1	NULL	NULL	NULL	1
201 by Brian Aker
Convert default engine to Innodb 
3005
 
1	SIMPLE	t3	ref	idx1	idx1	5	const	2
1 by brian
clean slate 
3006
 
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id

3007
 
WHERE t1.id=2;

3008
 
id	a	b	c	d	e

3009
 
2	NULL	NULL	NULL	2	10

3010
 
2	NULL	NULL	NULL	2	20

3011
 
2	NULL	NULL	NULL	2	40

3012
 
2	NULL	NULL	NULL	2	50

3013
 
DROP TABLE t1,t2,t3;

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

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

3016
 
INSERT INTO t1 VALUES

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

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

3019
 
INSERT INTO t2 VALUES

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

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

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

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

3024
 
EXPLAIN SELECT t2.* 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3049
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

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

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

3056
 
INSERT INTO t1 VALUES 

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

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

3059
 
INSERT INTO t2 VALUES

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

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

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

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

3064
 
INSERT INTO t3 VALUES

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

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

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

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

3069
 
EXPLAIN

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

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

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

3073
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3077
 
EXPLAIN

3078
 
SELECT t3.a FROM t1,t2,t3

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

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

3081
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

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

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

3089
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3093
 
EXPLAIN 

3094
 
SELECT t3.a FROM t1,t2,t3

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

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

3097
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3101
 
DROP TABLE t1,t2,t3;

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

3103
 
CREATE TABLE t2 ( f11 int PRIMARY KEY );

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

3105
 
INSERT INTO t2 VALUES (62);

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

3107
 
f1	f2	f3	f4	f5	f6	checked_out	f11

3108
 
1	1	1	0	0	0	0	NULL

3109
 
DROP TABLE t1, t2;

3110
 
DROP TABLE IF EXISTS t1;

3111
 
CREATE TABLE t1(a int);

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

3113
 
SELECT * FROM t1 LIMIT 2, -1;
629.2.6 by Monty
Updated test output with new and improved error messages. 
3114
 
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 
3115
 
DROP TABLE t1;

3116
 
CREATE TABLE t1 (

3117
 
ID_with_null int NULL,

3118
 
ID_better int NOT NULL,

3119
 
INDEX idx1 (ID_with_null),

3120
 
INDEX idx2 (ID_better)

3121
 
);

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

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

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

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

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

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

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

3129
 
COUNT(*)

3130
 
128

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

3132
 
COUNT(*)

3133
 
2

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

3135
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3136
 
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 
3137
 
DROP INDEX idx1 ON t1;

3138
 
CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);

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

3140
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3141
 
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 
3142
 
DROP TABLE t1;

3143
 
CREATE TABLE t1 (

3144
 
ID1_with_null int NULL,

3145
 
ID2_with_null int NULL,

3146
 
ID_better int NOT NULL,

3147
 
INDEX idx1 (ID1_with_null, ID2_with_null),

3148
 
INDEX idx2 (ID_better)

3149
 
);

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

3151
 
(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);

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

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

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

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

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

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

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

3159
 
COUNT(*)

3160
 
24

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

3162
 
COUNT(*)

3163
 
24

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

3165
 
COUNT(*)

3166
 
192

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

3168
 
COUNT(*)

3169
 
2

3170
 
EXPLAIN SELECT * FROM t1

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

3172
 
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 
3173
 
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 
3174
 
EXPLAIN SELECT * FROM t1

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

3176
 
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 
3177
 
1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	#	Using where
1 by brian
clean slate 
3178
 
EXPLAIN SELECT * FROM t1

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

3180
 
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 
3181
 
1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	#	Using where
1 by brian
clean slate 
3182
 
DROP INDEX idx1 ON t1;

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

3184
 
EXPLAIN SELECT * FROM t1

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

3186
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3187
 
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 
3188
 
EXPLAIN SELECT * FROM t1

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

3190
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
201 by Brian Aker
Convert default engine to Innodb 
3191
 
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 
3192
 
EXPLAIN SELECT * FROM t1

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

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

3197
 
WHERE ID_better=1 AND ID1_with_null IS NULL AND 

3198
 
(ID2_with_null=1 OR ID2_with_null=2);

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	ref	idx1,idx2	idx2	4	const	2	Using where
1 by brian
clean slate 
3201
 
DROP TABLE t1;

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

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

3204
 
ANALYZE TABLE t1;

3205
 
Table	Op	Msg_type	Msg_text

3206
 
test.t1	analyze	status	OK

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

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

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

3210
 
ANALYZE TABLE t2;

3211
 
Table	Op	Msg_type	Msg_text

3212
 
test.t2	analyze	status	OK

3213
 
EXPLAIN

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

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

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

3217
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3218
 
1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1
1100.1.1 by Brian Aker
Disable MRR 
3219
 
1	SIMPLE	t1	range	ts	ts	5	NULL	1	Using where
1 by brian
clean slate 
3220
 
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30

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

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

3223
 
a	ts	a	dt1	dt2

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

3225
 
DROP TABLE t1,t2;
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
3226
 
create table t1 (a bigint);
1 by brian
clean slate 
3227
 
insert into t1 values
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
3228
 
(if(1, 92233720368547758, 1)),

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

3230
 
(coalesce(92233720368547758, 1));
1 by brian
clean slate 
3231
 
select * from t1;

3232
 
a
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
3233
 
92233720368547758

3234
 
92233720368547758

3235
 
92233720368547758
1 by brian
clean slate 
3236
 
drop table t1;

3237
 
create table t1 select

3238
 
if(1, 9223372036854775808, 1) i,

3239
 
case when 1 then 9223372036854775808 else 1 end c,

3240
 
coalesce(9223372036854775808, 1) co;

3241
 
show create table t1;

3242
 
Table	Create Table
352.2.1 by Harrison Fisk
Fix for bugs 259843 and 256482 
3243
 
t1	CREATE TABLE `t1` (
896.3.6 by Stewart Smith
Read Fields out of proto instead of FRM. 
3244
 
  `i` decimal(19,0) NOT NULL,

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

3246
 
  `co` decimal(19,0) NOT NULL
377.1.4 by Brian Aker
Big, fat, UTF-8 patch. This fixes some of the oddities around only one 
3247
 
) ENGINE=InnoDB
1 by brian
clean slate 
3248
 
drop table t1;

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

3251
 
case when 1 then 1111111111111111111 else 1 end c,

3252
 
coalesce(1111111111111111111, 1) co;
1 by brian
clean slate 
3253
 
i	c	co

3254
 
1111111111111111111	1111111111111111111	1111111111111111111

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

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

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

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

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

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

3261
 
SELECT * FROM t2;

3262
 
name	n

3263
 
bb	1

3264
 
aa	2

3265
 
cc   	3

3266
 
cc 	4

3267
 
cc	5

3268
 
bb 	6

3269
 
cc 	7

3270
 
SELECT * FROM t2 ORDER BY name;

3271
 
name	n

3272
 
aa	2

3273
 
bb	1

3274
 
bb 	6

3275
 
cc 	4

3276
 
cc   	3

3277
 
cc	5

3278
 
cc 	7

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

3280
 
name	LENGTH(name)	n

3281
 
aa	2	2

3282
 
bb	2	1

3283
 
bb 	3	6

3284
 
cc 	4	4

3285
 
cc   	5	3

3286
 
cc	2	5

3287
 
cc 	3	7

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

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

3292
 
name	LENGTH(name)	n

3293
 
cc   	5	3

3294
 
cc	2	5

3295
 
cc 	3	7

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

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

3300
 
name	LENGTH(name)	n

3301
 
cc   	5	3

3302
 
cc 	4	4

3303
 
cc	2	5

3304
 
cc 	3	7

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

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; Using filesort
1 by brian
clean slate 
3308
 
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3309
 
name	LENGTH(name)	n

3310
 
cc 	4	4

3311
 
cc   	5	3

3312
 
cc	2	5

3313
 
cc 	3	7

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

3315
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3319
 
name	name	n

3320
 
ccc	NULL	NULL

3321
 
bb	bb	1

3322
 
bb	bb 	6

3323
 
cc 	cc   	3

3324
 
cc 	cc	5

3325
 
cc 	cc 	7

3326
 
aa  	aa	2

3327
 
aa	aa	2

3328
 
DROP TABLE t1,t2;

3329
 
CREATE TABLE t1 (name text);

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

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

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

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

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

3335
 
SELECT * FROM t2;

3336
 
name	n

3337
 
bb	1

3338
 
aa	2

3339
 
cc   	3

3340
 
cc 	4

3341
 
cc	5

3342
 
bb 	6

3343
 
cc 	7

3344
 
SELECT * FROM t2 ORDER BY name;

3345
 
name	n

3346
 
aa	2

3347
 
bb	1

3348
 
bb 	6

3349
 
cc 	4

3350
 
cc   	3

3351
 
cc	5

3352
 
cc 	7

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

3354
 
name	LENGTH(name)	n

3355
 
aa	2	2

3356
 
bb	2	1

3357
 
bb 	3	6

3358
 
cc 	4	4

3359
 
cc   	5	3

3360
 
cc	2	5

3361
 
cc 	3	7

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

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

3366
 
name	LENGTH(name)	n

3367
 
cc   	5	3

3368
 
cc	2	5

3369
 
cc 	3	7

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

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

3374
 
name	LENGTH(name)	n

3375
 
cc   	5	3

3376
 
cc 	4	4

3377
 
cc	2	5

3378
 
cc 	3	7

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

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; Using filesort
1 by brian
clean slate 
3382
 
SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;

3383
 
name	LENGTH(name)	n

3384
 
cc 	4	4

3385
 
cc   	5	3

3386
 
cc	2	5

3387
 
cc 	3	7

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

3389
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3393
 
name	name	n

3394
 
ccc	NULL	NULL

3395
 
bb	bb	1

3396
 
bb	bb 	6

3397
 
cc 	cc   	3

3398
 
cc 	cc	5

3399
 
cc 	cc 	7

3400
 
aa  	aa	2

3401
 
aa	aa	2

3402
 
DROP TABLE t1,t2;

3403
 
CREATE TABLE t1 (

3404
 
access_id int NOT NULL default '0',

3405
 
name varchar(20) default NULL,

3406
 
rank int NOT NULL default '0',

3407
 
KEY idx (access_id)

3408
 
);

3409
 
CREATE TABLE t2 (

3410
 
faq_group_id int NOT NULL default '0',

3411
 
faq_id int NOT NULL default '0',

3412
 
access_id int default NULL,

3413
 
UNIQUE KEY idx1 (faq_id),

3414
 
KEY idx2 (faq_group_id,faq_id)

3415
 
);

3416
 
INSERT INTO t1 VALUES 

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

3418
 
INSERT INTO t2 VALUES

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

3420
 
SELECT t2.faq_id 

3421
 
FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)

3422
 
ON (t1.access_id = t2.access_id)

3423
 
LEFT JOIN t2 t

3424
 
ON (t.faq_group_id = t2.faq_group_id AND

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

3426
 
WHERE

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

3428
 
faq_id

3429
 
265

3430
 
SELECT t2.faq_id 

3431
 
FROM t1 INNER JOIN t2

3432
 
ON (t1.access_id = t2.access_id)

3433
 
LEFT JOIN t2 t

3434
 
ON (t.faq_group_id = t2.faq_group_id AND

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

3436
 
WHERE

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

3438
 
faq_id

3439
 
265

3440
 
DROP TABLE t1,t2;

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

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

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

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

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

3446
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

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

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

3453
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3455
 
31	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

3456
 
32	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

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

3458
 
ERROR HY000: Too high level of nesting for select

3459
 
DROP TABLE t1;

3460
 
CREATE TABLE t1 (
223 by Brian Aker
Cleanup int() work. 
3461
 
c1 int NOT NULL AUTO_INCREMENT,
1 by brian
clean slate 
3462
 
c2 varchar(1000) DEFAULT NULL,
223 by Brian Aker
Cleanup int() work. 
3463
 
c3 bigint DEFAULT NULL,

3464
 
c4 bigint DEFAULT NULL,
1 by brian
clean slate 
3465
 
PRIMARY KEY (c1)

3466
 
);

3467
 
EXPLAIN EXTENDED 

3468
 
SELECT  join_2.c1  

3469
 
FROM 

3470
 
t1 AS join_0, 

3471
 
t1 AS join_1, 

3472
 
t1 AS join_2, 

3473
 
t1 AS join_3, 

3474
 
t1 AS join_4, 

3475
 
t1 AS join_5, 

3476
 
t1 AS join_6, 

3477
 
t1 AS join_7

3478
 
WHERE 

3479
 
join_0.c1=join_1.c1  AND 

3480
 
join_1.c1=join_2.c1  AND 

3481
 
join_2.c1=join_3.c1  AND 

3482
 
join_3.c1=join_4.c1  AND 

3483
 
join_4.c1=join_5.c1  AND 

3484
 
join_5.c1=join_6.c1  AND 

3485
 
join_6.c1=join_7.c1 

3486
 
OR 

3487
 
join_0.c2 < '?'  AND 

3488
 
join_1.c2 < '?'  AND

3489
 
join_2.c2 > '?'  AND

3490
 
join_2.c2 < '!'  AND

3491
 
join_3.c2 > '?'  AND 

3492
 
join_4.c2 = '?'  AND 

3493
 
join_5.c2 <> '?' AND

3494
 
join_6.c2 <> '?' AND 

3495
 
join_7.c2 >= '?' AND

3496
 
join_0.c1=join_1.c1  AND 

3497
 
join_1.c1=join_2.c1  AND 

3498
 
join_2.c1=join_3.c1  AND

3499
 
join_3.c1=join_4.c1  AND 

3500
 
join_4.c1=join_5.c1  AND 

3501
 
join_5.c1=join_6.c1  AND 

3502
 
join_6.c1=join_7.c1

3503
 
GROUP BY 

3504
 
join_3.c1,

3505
 
join_2.c1,

3506
 
join_7.c1,

3507
 
join_1.c1,

3508
 
join_0.c1;

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

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

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

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

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

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

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

3517
 
1	SIMPLE	join_7	eq_ref	PRIMARY	PRIMARY	4	test.join_5.c1	1	100.00	Using where
1 by brian
clean slate 
3518
 
Warnings:
383.1.16 by Brian Aker
Force client communication into UTF8 
3519
 
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 
3520
 
SHOW WARNINGS;

3521
 
Level	Code	Message
383.1.16 by Brian Aker
Force client communication into UTF8 
3522
 
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 
3523
 
DROP TABLE t1;

3524
 
SELECT 1 AS ` `;

3525
 









3526



1










3527



Warnings:










3528



Warning	1474	Name ' ' has become ''










3529



SELECT 1 AS `  `;










3530












3531



1










3532



Warnings:










3533



Warning	1474	Name '  ' has become ''










3534



SELECT 1 AS ` x`;










3535



x










3536



1










3537



Warnings:










3538



Warning	1466	Leading spaces are removed from name ' x'








413.2.2
by Brian Aker


Removed UNSIGNED from parser.



3539



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










3540



CREATE TABLE t2 (c21 INT NOT NULL, 








1
by brian


clean slate



3541



c22 INT DEFAULT NULL, 










3542



KEY(c21, c22));








413.2.2
by Brian Aker


Removed UNSIGNED from parser.



3543



CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0, 








1
by brian


clean slate



3544



c32 INT DEFAULT NULL, 










3545



c33 INT NOT NULL, 








413.2.2
by Brian Aker


Removed UNSIGNED from parser.



3546



c34 INT DEFAULT 0,








1
by brian


clean slate



3547



KEY (c33, c34, c32));










3548



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










3549



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










3550



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










3551



(2, 2, 0, 0), 










3552



(3, 3, 1, 0), 










3553



(4, 4, 0, 0), 










3554



(5, 5, 1, 0);










3555



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










3556



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










3557



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










3558



ORDER BY c32;










3559



c32










3560



1










3561



1










3562



3










3563



3










3564



5










3565



5










3566



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










3567



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










3568



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










3569



ORDER BY c32 DESC;










3570



c32










3571



5










3572



5










3573



3










3574



3










3575



1










3576



1










3577



DROP TABLE t1, t2, t3;










3578












3579



#










3580



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










3581



# Inserting Data.










3582



#










3583



DROP TABLE IF EXISTS t1;










3584



DROP TABLE IF EXISTS t2;










3585












3586



CREATE TABLE t1(










3587



c1 DECIMAL(10, 2),










3588



c2 FLOAT);










3589












3590



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










3591












3592



CREATE TABLE t2(










3593



c3 DECIMAL(10, 2))










3594



SELECT










3595



c1 * c2 AS c3










3596



FROM t1;










3597












3598



SELECT * FROM t1;










3599



c1	c2










3600



0.00	1










3601



2.00	3










3602



4.00	5










3603












3604



SELECT * FROM t2;










3605



c3










3606



0.00










3607



6.00










3608



20.00










3609












3610



DROP TABLE t1;










3611



DROP TABLE t2;










3612












3613



CREATE TABLE t1 (c1 BIGINT NOT NULL);










3614



INSERT INTO t1 (c1) VALUES (1);










3615



SELECT * FROM t1 WHERE c1 > NULL + 1;










3616



c1










3617



DROP TABLE t1;










3618












3619



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










3620



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










3621



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










3622



a










3623



foo0










3624



DROP TABLE t1;










3625



CREATE TABLE t1 (a INT, b INT);










3626



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










3627



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










3628



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










3629



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










3630



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










3631



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










3632



FLUSH STATUS;










3633



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










3634



b










3635



1










3636



2










3637



SHOW STATUS LIKE 'Handler_read%';










3638



Variable_name	Value








201
by Brian Aker


Convert default engine to Innodb



3639



Handler_read_first	1










3640



Handler_read_key	5








1
by brian


clean slate



3641



Handler_read_next	0










3642



Handler_read_prev	0










3643



Handler_read_rnd	0










3644



Handler_read_rnd_next	6










3645



DROP TABLE t1, t2;










3646



End of 5.0 tests










3647



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










3648



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










3649



SELECT a FROM t1 ORDER BY a LIMIT 2;










3650



a










3651



1










3652



2










3653



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










3654



a










3655



3










3656



4










3657



5










3658



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










3659



a










3660



3










3661



4










3662



5










3663



DROP TABLE t1;