← 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
2141.4.2 by Andrew Hutchings
Implicit joins of the form "SELECT * FROM t1, t2" without WHERE or ON now error. 
1269
 
select * from t1 CROSS JOIN t1 t12;
1 by brian
clean slate 
1270
 
Period	Varor_period	Period	Varor_period

1271
 
9410	9412	9410	9412

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

1273
 
fld1	fld1

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

1276
 
250501	250503

1277
 
250501	250504

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

1281
 
250502	250503

1282
 
250502	250504

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

1286
 
250503	250503

1287
 
250503	250504

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

1291
 
250504	250503

1292
 
250504	250504

1293
 
250504	250505
1 by brian
clean slate 
1294
 
250505	250501

1295
 
250505	250502

1296
 
250505	250503

1297
 
250505	250504

1298
 
250505	250505

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

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

1301
 
companynr	companyname

1302
 
99	NULL

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

1304
 
count(*)

1305
 
1199

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

1307
 
companynr	companyname

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

1309
 
count(*)

1310
 
1200

1311
 
delete from t2 where fld1=999999;

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

1313
 
companynr	companynr

1314
 
37	36

1315
 
41	40

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

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

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

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

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

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

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

1329
 
period

1330
 
9410

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

1332
 
period

1333
 
9410

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

1335
 
fld1

1336
 
250501

1337
 
250502

1338
 
250503

1339
 
250505

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

1341
 
fld1

1342
 
250502

1343
 
250503

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

1345
 
fld1

1346
 
250502

1347
 
250503

1348
 
250504

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

1350
 
fld3

1351
 
label

1352
 
labeled

1353
 
labeled

1354
 
landslide

1355
 
laterally

1356
 
leaflet

1357
 
lewdly

1358
 
Lillian

1359
 
luckily

1360
 
select count(*) from t1;

1361
 
count(*)

1362
 
1

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

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

1367
 
34	70	17788966

1368
 
36	215	22786296

1369
 
37	588	83602098

1370
 
40	37	6618386

1371
 
41	52	12816335

1372
 
50	11	1595438

1373
 
53	4	793210

1374
 
58	23	2254293

1375
 
65	10	2284055

1376
 
68	12	3097288

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

1378
 
companynr	count(*)

1379
 
68	12

1380
 
65	10

1381
 
58	23

1382
 
53	4

1383
 
50	11

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

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

1386
 
70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069

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

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

1391
 
34	70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069

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

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

1394
 
37	1	1	5987435	5987435	5987435	5987435.0000

1395
 
37	2	1	28357832	28357832	28357832	28357832.0000

1396
 
37	3	1	39654943	39654943	39654943	39654943.0000

1397
 
37	11	1	5987435	5987435	5987435	5987435.0000

1398
 
37	12	1	28357832	28357832	28357832	28357832.0000

1399
 
37	13	1	39654943	39654943	39654943	39654943.0000

1400
 
37	21	1	5987435	5987435	5987435	5987435.0000

1401
 
37	22	1	28357832	28357832	28357832	28357832.0000

1402
 
37	23	1	39654943	39654943	39654943	39654943.0000

1403
 
37	31	1	5987435	5987435	5987435	5987435.0000

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

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

1406
 
37	1	1	5987435	5987435	5987435	5987435.0000

1407
 
37	2	1	28357832	28357832	28357832	28357832.0000

1408
 
37	3	1	39654943	39654943	39654943	39654943.0000

1409
 
37	11	1	5987435	5987435	5987435	5987435.0000

1410
 
37	12	1	28357832	28357832	28357832	28357832.0000

1411
 
37	13	1	39654943	39654943	39654943	39654943.0000

1412
 
37	21	1	5987435	5987435	5987435	5987435.0000

1413
 
37	22	1	28357832	28357832	28357832	28357832.0000

1414
 
37	23	1	39654943	39654943	39654943	39654943.0000

1415
 
37	31	1	5987435	5987435	5987435	5987435.0000

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

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

1418
 
37	12543	309394878010	5987435	39654943	24666736.6667

1419
 
78	8362	414611089292	726498	98439034	49582766.0000

1420
 
101	4181	3489454238	834598	834598	834598.0000

1421
 
154	4181	4112197254950	983543950	983543950	983543950.0000

1422
 
311	4181	979599938	234298	234298	234298.0000

1423
 
447	4181	9929180954	2374834	2374834	2374834.0000

1424
 
512	4181	3288532102	786542	786542	786542.0000

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

1426
 
mod(companynr,10)

1427
 
0

1428
 
9

1429
 
4

1430
 
6

1431
 
7

1432
 
1

1433
 
3

1434
 
8

1435
 
5

1436
 
select distinct 1 from t4 group by companynr;

1437
 
1

1438
 
1

1439
 
select count(distinct fld1) from t2;

1440
 
count(distinct fld1)

1441
 
1199

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

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

1446
 
34	70

1447
 
36	215

1448
 
37	588

1449
 
40	37

1450
 
41	52

1451
 
50	11

1452
 
53	4

1453
 
58	23

1454
 
65	10

1455
 
68	12

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

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

1460
 
34	70

1461
 
36	215

1462
 
37	588

1463
 
40	37

1464
 
41	52

1465
 
50	11

1466
 
53	4

1467
 
58	23

1468
 
65	10

1469
 
68	12

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

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

1474
 
34	70

1475
 
36	215

1476
 
37	588

1477
 
40	37

1478
 
41	52

1479
 
50	11

1480
 
53	4

1481
 
58	23

1482
 
65	10

1483
 
68	12

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

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

1488
 
34	70

1489
 
36	215

1490
 
37	588

1491
 
40	37

1492
 
41	52

1493
 
50	11

1494
 
53	4

1495
 
58	23

1496
 
65	10

1497
 
68	12

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

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

1502
 
34	14

1503
 
36	69

1504
 
37	108

1505
 
40	16

1506
 
41	11

1507
 
50	9

1508
 
53	1

1509
 
58	1

1510
 
65	1

1511
 
68	1

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

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

1516
 
34	14

1517
 
36	69

1518
 
37	108

1519
 
40	16

1520
 
41	11

1521
 
50	9

1522
 
53	1

1523
 
58	1

1524
 
65	1

1525
 
68	1

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

1527
 
sum(fld1)	fld3

1528
 
11402	Romans

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

1530
 
name	count(*)

1531
 
cloakroom	4181

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

1533
 
name	count(*)

1534
 
cloakroom	4181

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

1536
 
count(*)

1537
 
4181

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

1539
 
name	count(*)

1540
 
cloakroom	4181

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

1542
 
name	count(*)

1543
 
extramarital	4181

1544
 
gazer	4181

1545
 
gems	4181

1546
 
Iranizes	4181

1547
 
spates	4181

1548
 
tucked	4181

1549
 
violinist	4181

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

1551
 
fld3	count(*)

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

1554
 
companynr	companyname
1 by brian
clean slate 
1555
 
0	Unknown

1556
 
29	company 1

1557
 
34	company 2

1558
 
36	company 3

1559
 
37	company 4

1560
 
40	company 5

1561
 
41	company 6

1562
 
50	company 11

1563
 
53	company 7

1564
 
58	company 8

1565
 
65	company 9

1566
 
68	company 10

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

1568
 
companynr	companyname	count(*)

1569
 
29	company 1	95

1570
 
68	company 10	12

1571
 
50	company 11	11

1572
 
34	company 2	70

1573
 
36	company 3	215

1574
 
37	company 4	588

1575
 
40	company 5	37

1576
 
41	company 6	52

1577
 
53	company 7	4

1578
 
58	company 8	23

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

1582
 
fld1	count(*)

1583
 
158402	4181

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

1585
 
sum(Period)/count(*)

1586
 
9410.0000

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

1588
 
companynr	count	sum	diff	func

1589
 
37	12543	309394878010	0.0000	464091

1590
 
78	8362	414611089292	0.0000	652236

1591
 
101	4181	3489454238	0.0000	422281

1592
 
154	4181	4112197254950	0.0000	643874

1593
 
311	4181	979599938	0.0000	1300291

1594
 
447	4181	9929180954	0.0000	1868907

1595
 
512	4181	3288532102	0.0000	2140672

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

1597
 
companynr	avg

1598
 
154	983543950.0000

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

1600
 
companynr	count(*)

1601
 
37	588

1602
 
36	215

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

1606
 
41	52

1607
 
40	37

1608
 
58	23

1609
 
68	12

1610
 
50	11

1611
 
65	10

1612
 
53	4

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

1614
 
companynr	count(*)

1615
 
41	52

1616
 
58	23

1617
 
68	12

1618
 
50	11

1619
 
65	10

1620
 
53	4

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

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

1624
 
dreaded	11401	1	5987435	5987435	5987435	5987435.0000

1625
 
scholastics	11402	1	28357832	28357832	28357832	28357832.0000

1626
 
audiology	11403	1	39654943	39654943	39654943	39654943.0000

1627
 
wallet	11501	1	5987435	5987435	5987435	5987435.0000

1628
 
parters	11701	1	5987435	5987435	5987435	5987435.0000

1629
 
eschew	11702	1	28357832	28357832	28357832	28357832.0000

1630
 
quitter	11703	1	39654943	39654943	39654943	39654943.0000

1631
 
neat	12001	1	5987435	5987435	5987435	5987435.0000

1632
 
Steinberg	12003	1	39654943	39654943	39654943	39654943.0000

1633
 
balled	12301	1	5987435	5987435	5987435	5987435.0000

1634
 
persist	12302	1	28357832	28357832	28357832	28357832.0000

1635
 
attainments	12303	1	39654943	39654943	39654943	39654943.0000

1636
 
capably	12501	1	5987435	5987435	5987435	5987435.0000

1637
 
impulsive	12602	1	28357832	28357832	28357832	28357832.0000

1638
 
starlet	12603	1	39654943	39654943	39654943	39654943.0000

1639
 
featherweight	12701	1	5987435	5987435	5987435	5987435.0000

1640
 
pessimist	12702	1	28357832	28357832	28357832	28357832.0000

1641
 
daughter	12703	1	39654943	39654943	39654943	39654943.0000

1642
 
lawgiver	13601	1	5987435	5987435	5987435	5987435.0000

1643
 
stated	13602	1	28357832	28357832	28357832	28357832.0000

1644
 
readable	13603	1	39654943	39654943	39654943	39654943.0000

1645
 
testicle	13801	1	5987435	5987435	5987435	5987435.0000

1646
 
Parsifal	13802	1	28357832	28357832	28357832	28357832.0000

1647
 
leavings	13803	1	39654943	39654943	39654943	39654943.0000

1648
 
squeaking	13901	1	5987435	5987435	5987435	5987435.0000

1649
 
contrasted	16001	1	5987435	5987435	5987435	5987435.0000

1650
 
leftover	16201	1	5987435	5987435	5987435	5987435.0000

1651
 
whiteners	16202	1	28357832	28357832	28357832	28357832.0000

1652
 
erases	16301	1	5987435	5987435	5987435	5987435.0000

1653
 
Punjab	16302	1	28357832	28357832	28357832	28357832.0000

1654
 
Merritt	16303	1	39654943	39654943	39654943	39654943.0000

1655
 
sweetish	18001	1	5987435	5987435	5987435	5987435.0000

1656
 
dogging	18002	1	28357832	28357832	28357832	28357832.0000

1657
 
scornfully	18003	1	39654943	39654943	39654943	39654943.0000

1658
 
fetters	18012	1	28357832	28357832	28357832	28357832.0000

1659
 
bivalves	18013	1	39654943	39654943	39654943	39654943.0000

1660
 
skulking	18021	1	5987435	5987435	5987435	5987435.0000

1661
 
flint	18022	1	28357832	28357832	28357832	28357832.0000

1662
 
flopping	18023	1	39654943	39654943	39654943	39654943.0000

1663
 
Judas	18032	1	28357832	28357832	28357832	28357832.0000

1664
 
vacuuming	18033	1	39654943	39654943	39654943	39654943.0000

1665
 
medical	18041	1	5987435	5987435	5987435	5987435.0000

1666
 
bloodbath	18042	1	28357832	28357832	28357832	28357832.0000

1667
 
subschema	18043	1	39654943	39654943	39654943	39654943.0000

1668
 
interdependent	18051	1	5987435	5987435	5987435	5987435.0000

1669
 
Graves	18052	1	28357832	28357832	28357832	28357832.0000

1670
 
neonatal	18053	1	39654943	39654943	39654943	39654943.0000

1671
 
sorters	18061	1	5987435	5987435	5987435	5987435.0000

1672
 
epistle	18062	1	28357832	28357832	28357832	28357832.0000

1673
 
Conley	18101	1	5987435	5987435	5987435	5987435.0000

1674
 
lectured	18102	1	28357832	28357832	28357832	28357832.0000

1675
 
Abraham	18103	1	39654943	39654943	39654943	39654943.0000

1676
 
cage	18201	1	5987435	5987435	5987435	5987435.0000

1677
 
hushes	18202	1	28357832	28357832	28357832	28357832.0000

1678
 
Simla	18402	1	28357832	28357832	28357832	28357832.0000

1679
 
reporters	18403	1	39654943	39654943	39654943	39654943.0000

1680
 
coexist	18601	1	5987435	5987435	5987435	5987435.0000

1681
 
Beebe	18602	1	28357832	28357832	28357832	28357832.0000

1682
 
Taoism	18603	1	39654943	39654943	39654943	39654943.0000

1683
 
Connally	18801	1	5987435	5987435	5987435	5987435.0000

1684
 
fetched	18802	1	28357832	28357832	28357832	28357832.0000

1685
 
checkpoints	18803	1	39654943	39654943	39654943	39654943.0000

1686
 
gritty	18811	1	5987435	5987435	5987435	5987435.0000

1687
 
firearm	18812	1	28357832	28357832	28357832	28357832.0000

1688
 
minima	19101	1	5987435	5987435	5987435	5987435.0000

1689
 
Selfridge	19102	1	28357832	28357832	28357832	28357832.0000

1690
 
disable	19103	1	39654943	39654943	39654943	39654943.0000

1691
 
witchcraft	19201	1	5987435	5987435	5987435	5987435.0000

1692
 
betroth	30501	1	5987435	5987435	5987435	5987435.0000

1693
 
Manhattanize	30502	1	28357832	28357832	28357832	28357832.0000

1694
 
imprint	30503	1	39654943	39654943	39654943	39654943.0000

1695
 
swelling	31901	1	5987435	5987435	5987435	5987435.0000

1696
 
interrelationships	36001	1	5987435	5987435	5987435	5987435.0000

1697
 
riser	36002	1	28357832	28357832	28357832	28357832.0000

1698
 
bee	38001	1	5987435	5987435	5987435	5987435.0000

1699
 
kanji	38002	1	28357832	28357832	28357832	28357832.0000

1700
 
dental	38003	1	39654943	39654943	39654943	39654943.0000

1701
 
railway	38011	1	5987435	5987435	5987435	5987435.0000

1702
 
validate	38012	1	28357832	28357832	28357832	28357832.0000

1703
 
normalizes	38013	1	39654943	39654943	39654943	39654943.0000

1704
 
Kline	38101	1	5987435	5987435	5987435	5987435.0000

1705
 
Anatole	38102	1	28357832	28357832	28357832	28357832.0000

1706
 
partridges	38103	1	39654943	39654943	39654943	39654943.0000

1707
 
recruited	38201	1	5987435	5987435	5987435	5987435.0000

1708
 
dimensions	38202	1	28357832	28357832	28357832	28357832.0000

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

1711
 
companynr	fld3	sum(price)

1712
 
512	boat	786542

1713
 
512	capably	786542

1714
 
512	cupboard	786542

1715
 
512	decliner	786542

1716
 
512	descendants	786542

1717
 
512	dopers	786542

1718
 
512	erases	786542

1719
 
512	Micronesia	786542

1720
 
512	Miles	786542

1721
 
512	skies	786542

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

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

1726
 
37	83	Abraham	Wotan	1908978016	22999735.1325

1727
 
50	2	scribbled	tapestry	68012775	34006387.5000

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

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

1730
 
37	1	Omaha	5987435

1731
 
37	11401	breaking	5987435

1732
 
37	11402	Romans	28357832

1733
 
37	11403	intercepted	39654943

1734
 
37	11501	bewilderingly	5987435

1735
 
37	11701	astound	5987435

1736
 
37	11702	admonishing	28357832

1737
 
37	11703	sumac	39654943

1738
 
37	12001	flanking	5987435

1739
 
37	12003	combed	39654943

1740
 
37	12301	Eulerian	5987435

1741
 
37	12302	dubbed	28357832

1742
 
37	12303	Kane	39654943

1743
 
37	12501	annihilates	5987435

1744
 
37	12602	Wotan	28357832

1745
 
37	12603	snatching	39654943

1746
 
37	12701	grazing	5987435

1747
 
37	12702	Baird	28357832

1748
 
37	12703	celery	39654943

1749
 
37	13601	handgun	5987435

1750
 
37	13602	foldout	28357832

1751
 
37	13603	mystic	39654943

1752
 
37	13801	intelligibility	5987435

1753
 
37	13802	Augustine	28357832

1754
 
37	13803	teethe	39654943

1755
 
37	13901	scholastics	5987435

1756
 
37	16001	audiology	5987435

1757
 
37	16201	wallet	5987435

1758
 
37	16202	parters	28357832

1759
 
37	16301	eschew	5987435

1760
 
37	16302	quitter	28357832

1761
 
37	16303	neat	39654943

1762
 
37	18001	jarring	5987435

1763
 
37	18002	tinily	28357832

1764
 
37	18003	balled	39654943

1765
 
37	18012	impulsive	28357832

1766
 
37	18013	starlet	39654943

1767
 
37	18021	lawgiver	5987435

1768
 
37	18022	stated	28357832

1769
 
37	18023	readable	39654943

1770
 
37	18032	testicle	28357832

1771
 
37	18033	Parsifal	39654943

1772
 
37	18041	Punjab	5987435

1773
 
37	18042	Merritt	28357832

1774
 
37	18043	Quixotism	39654943

1775
 
37	18051	sureties	5987435

1776
 
37	18052	puddings	28357832

1777
 
37	18053	tapestry	39654943

1778
 
37	18061	trimmings	5987435

1779
 
37	18062	humility	28357832

1780
 
37	18101	tragedies	5987435

1781
 
37	18102	skulking	28357832

1782
 
37	18103	flint	39654943

1783
 
37	18201	relaxing	5987435

1784
 
37	18202	offload	28357832

1785
 
37	18402	suites	28357832

1786
 
37	18403	lists	39654943

1787
 
37	18601	vacuuming	5987435

1788
 
37	18602	dentally	28357832

1789
 
37	18603	humanness	39654943

1790
 
37	18801	inch	5987435

1791
 
37	18802	Weissmuller	28357832

1792
 
37	18803	irresponsibly	39654943

1793
 
37	18811	repetitions	5987435

1794
 
37	18812	Antares	28357832

1795
 
37	19101	ventilate	5987435

1796
 
37	19102	pityingly	28357832

1797
 
37	19103	interdependent	39654943

1798
 
37	19201	Graves	5987435

1799
 
37	30501	neonatal	5987435

1800
 
37	30502	scribbled	28357832

1801
 
37	30503	chafe	39654943

1802
 
37	31901	realtor	5987435

1803
 
37	36001	elite	5987435

1804
 
37	36002	funereal	28357832

1805
 
37	38001	Conley	5987435

1806
 
37	38002	lectured	28357832

1807
 
37	38003	Abraham	39654943

1808
 
37	38011	groupings	5987435

1809
 
37	38012	dissociate	28357832

1810
 
37	38013	coexist	39654943

1811
 
37	38101	rusting	5987435

1812
 
37	38102	galling	28357832

1813
 
37	38103	obliterates	39654943

1814
 
37	38201	resumes	5987435

1815
 
37	38202	analyzable	28357832

1816
 
37	38203	terminator	39654943

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

1818
 
sum(price)

1819
 
234298

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

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

1824
 
companynr	fld1

1825
 
34	250501

1826
 
34	250502

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

1828
 
companynr	fld1

1829
 
34	250501

1830
 
34	250502

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

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

1835
 
34	70	17788966

1836
 
37	588	83602098

1837
 
41	52	12816335

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

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

1842
 
34

1843
 
37

1844
 
41

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

1846
 
companynr	companyname	count(*)

1847
 
68	company 10	12

1848
 
50	company 11	11

1849
 
40	company 5	37

1850
 
41	company 6	52

1851
 
53	company 7	4

1852
 
58	company 8	23

1853
 
65	company 9	10

1854
 
select count(*) from t2;

1855
 
count(*)

1856
 
1199

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

1858
 
count(*)

1859
 
387

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

1861
 
min(fld1)

1862
 
98024

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

1864
 
max(fld1)

1865
 
1232609

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

1867
 
count(*)

1868
 
4181

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

1870
 
count(*)

1871
 
4181

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

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

1874
 
0	1232609	1199

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

1876
 
min(t2nr)	max(t2nr)

1877
 
2115	2115

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

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

1880
 
4181	4	41804

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

1882
 
t2nr	count(*)

1883
 
9	1

1884
 
19	1

1885
 
29	1

1886
 
39	1

1887
 
49	1

1888
 
59	1

1889
 
69	1

1890
 
79	1

1891
 
89	1

1892
 
99	1

1893
 
109	1

1894
 
119	1

1895
 
129	1

1896
 
139	1

1897
 
149	1

1898
 
159	1

1899
 
169	1

1900
 
179	1

1901
 
189	1

1902
 
199	1

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

1904
 
max(t2nr)

1905
 
41807

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

1907
 
period

1908
 
9410

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

1910
 
Nuvarande period

1911
 
9410

1912
 
select period as ok_period from t1 limit 1;

1913
 
ok_period

1914
 
9410

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

1916
 
ok_period

1917
 
9410

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

1919
 
summa

1920
 
2

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

1922
 
Nuvarande period

1923
 
9410

1924
 
show tables;

1925
 
Tables_in_test

1926
 
t1

1927
 
t2

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

1931
 
Tables_in_test (s%)

1932
 
show tables from test like "t?";

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

1937
 
fld1	INTEGER	NO	0	NO	

1938
 
companynr	INTEGER	NO	0	NO	

1939
 
fld3	VARCHAR	NO		NO	

1940
 
fld4	VARCHAR	NO		NO	

1941
 
fld5	VARCHAR	NO		NO	

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

1946
 
fld1	INTEGER	NO	0	NO	

1947
 
companynr	INTEGER	NO	0	NO	

1948
 
fld3	VARCHAR	NO		NO	

1949
 
fld4	VARCHAR	NO		NO	

1950
 
fld5	VARCHAR	NO		NO	

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

1955
 
fld1	INTEGER	NO	0	NO	

1956
 
companynr	INTEGER	NO	0	NO	

1957
 
fld3	VARCHAR	NO		NO	

1958
 
fld4	VARCHAR	NO		NO	

1959
 
fld5	VARCHAR	NO		NO	

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

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

1965
 
PRIMARY KEY  (id),

1966
 
UNIQUE KEY pseudo (pseudo)

1967
 
);

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

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

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

1971
 
rnd1

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

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

1980
 
gvid	the_success	the_fail	the_size	the_time

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

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

1985
 
wss_type

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

1987
 
wss_type

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

1989
 
wss_type

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

1991
 
wss_type

1992
 
102935229216544093

1993
 
select wss_type from t1 where wss_type =102935229216544093;

1994
 
wss_type

1995
 
102935229216544093

1996
 
drop table t1;

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

1998
 
select @a;

1999
 
@a

2000
 
3

2001
 
select @b;

2002
 
@b

2003
 
aaaa

2004
 
select @c;

2005
 
@c

2006
 
6.260

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

2008
 
insert into t1 values ();

2009
 
insert into t1 values ();

2010
 
insert into t1 values ();

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

2012
 
a	a

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

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

2018
 
2	3
1 by brian
clean slate 
2019
 
3	1

2020
 
3	2

2021
 
3	3

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

2023
 
a	a

2024
 
1	1

2025
 
2	1

2026
 
3	1

2027
 
1	2

2028
 
2	2

2029
 
3	2

2030
 
1	3

2031
 
2	3

2032
 
3	3

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

2034
 
a	a

2035
 
1	1

2036
 
2	1

2037
 
3	1

2038
 
1	2

2039
 
2	2

2040
 
3	2

2041
 
1	3

2042
 
2	3

2043
 
3	3

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

2045
 
a	a

2046
 
1	1

2047
 
2	1

2048
 
3	1

2049
 
1	2

2050
 
2	2

2051
 
3	2

2052
 
1	3

2053
 
2	3

2054
 
3	3

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

2056
 
a	a

2057
 
1	2
201 by Brian Aker
Convert default engine to Innodb 
2058
 
1	3
1 by brian
clean slate 
2059
 
2	2
201 by Brian Aker
Convert default engine to Innodb 
2060
 
2	3
1 by brian
clean slate 
2061
 
3	2

2062
 
3	3

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

2064
 
a	a

2065
 
2	1

2066
 
3	1

2067
 
2	2

2068
 
3	2

2069
 
2	3

2070
 
3	3

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

2072
 
a

2073
 
1

2074
 
2

2075
 
3

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

2077
 
a

2078
 
1

2079
 
2

2080
 
3

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

2082
 
a	a

2083
 
1	2

2084
 
1	3

2085
 
2	2

2086
 
2	3

2087
 
3	2

2088
 
3	3

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

2090
 
a	a

2091
 
1	NULL

2092
 
2	1

2093
 
2	2

2094
 
2	3

2095
 
3	1

2096
 
3	2

2097
 
3	3

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

2099
 
a

2100
 
1

2101
 
2

2102
 
3

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

2104
 
a

2105
 
1

2106
 
2

2107
 
3

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

2109
 
a

2110
 
1

2111
 
2

2112
 
3

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

2114
 
a

2115
 
1

2116
 
2

2117
 
3

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

2119
 
a	a

2120
 
NULL	1

2121
 
1	2

2122
 
2	2

2123
 
3	2

2124
 
1	3

2125
 
2	3

2126
 
3	3

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

2128
 
a	a

2129
 
2	1

2130
 
3	1

2131
 
2	2

2132
 
3	2

2133
 
2	3

2134
 
3	3

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

2136
 
a

2137
 
1

2138
 
2

2139
 
3

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

2141
 
a

2142
 
1

2143
 
2

2144
 
3

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

2146
 
a

2147
 
1

2148
 
2

2149
 
3

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

2151
 
a

2152
 
1

2153
 
2

2154
 
3

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

2156
 
a

2157
 
1

2158
 
2

2159
 
3

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

2161
 
a

2162
 
1

2163
 
2

2164
 
3

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

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

2171
 
aa	id	t2_id	id

2172
 
2	8299	2517	2517

2173
 
3	8301	2518	2518

2174
 
4	8302	2519	2519

2175
 
5	8303	2520	2520

2176
 
6	8304	2521	2521

2177
 
drop table t1,t2;

2178
 
create table t1 (id1 int NOT NULL);

2179
 
create table t2 (id2 int NOT NULL);

2180
 
create table t3 (id3 int NOT NULL);

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

2182
 
insert into t1 values (1);

2183
 
insert into t1 values (2);

2184
 
insert into t2 values (1);

2185
 
insert into t4 values (1,1);

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

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

2188
 
id1	id2	id3	id4	id44

2189
 
1	1	NULL	NULL	NULL

2190
 
drop table t1,t2,t3,t4;

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

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

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

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

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

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

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

2198
 
s

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

2200
 
s

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

2202
 
s

2203
 
one 

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

2205
 
s	s

2206
 
two		two	

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

2208
 
s	s

2209
 
two		two	

2210
 
drop table t1, t2, t3;

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

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

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

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

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

2216
 
a	b	c	d

2217
 
3	2	3	4

2218
 
4	2	4	4

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

2220
 
a	b	c	d

2221
 
3	2	3	4

2222
 
4	2	4	4

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

2227
 
PRIMARY KEY  (i),

2228
 
UNIQUE KEY c (c)

2229
 
) ENGINE=MyISAM;

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

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

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

2233
 
EXPLAIN SELECT i FROM t1 WHERE i=1;

2234
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

2236
 
DROP TABLE t1;

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

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

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

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

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

2242
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

2246
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2251
 
INSERT INTO t1 VALUES ('London');

2252
 
INSERT INTO t1 VALUES ('Paris');

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

2254
 
city

2255
 
London

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

2257
 
city

2258
 
London

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

2260
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2263
 
city

2264
 
London

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

2266
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2269
 
city

2270
 
London

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

2274
 
select a-b  from t1 order by 1;

2275
 
a-b
358 by Brian Aker
More removal on modes. 
2276
 
-1
1 by brian
clean slate 
2277
 
0

2278
 
1

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

2280
 
a-b 	(a-b < 0)
358 by Brian Aker
More removal on modes. 
2281
 
-1	1
1 by brian
clean slate 
2282
 
0	0

2283
 
1	0

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

2285
 
d	(a-b >= 0)	b

2286
 
1	1	0

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

2289
 
a - b 

2290
 
-1
1 by brian
clean slate 
2291
 
0

2292
 
1

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

2296
 
a

2297
 
select distinct distinct * from t1;

2298
 
a

2299
 
select all distinct * from t1;

2300
 
ERROR HY000: Incorrect usage of ALL and DISTINCT

2301
 
select distinct all * from t1;

2302
 
ERROR HY000: Incorrect usage of ALL and DISTINCT

2303
 
drop table t1;

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

2306
 
kunde_id int NOT NULL default '0',

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

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

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

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

2312
 
firma varchar(128) NOT NULL default ''

2313
 
);

2314
 
INSERT INTO t1 VALUES 

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

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

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

2318
 
WHERE

2319
 
(

2320
 
(

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

2322
 
OR

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

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

2325
 
'Vorname1' != '' AND 'xxxx' != '')

2326
 
)

2327
 
AND

2328
 
(

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

2330
 
)

2331
 
)

2332
 
;

2333
 
kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht

2334
 
SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname,

2335
 
geloescht FROM t1

2336
 
WHERE

2337
 
(

2338
 
(

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

2340
 
)

2341
 
AND

2342
 
(

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

2344
 
OR

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

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

2347
 
'xxxx' != '')

2348
 
)

2349
 
)

2350
 
;

2351
 
kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht

2352
 
SELECT COUNT(*) FROM t1 WHERE 

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

2354
 
AND FK_firma_id = 2;

2355
 
COUNT(*)

2356
 
0

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

2359
 
INSERT INTO t1 VALUES (0x4000000000000000);

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

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

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

2370
 
id	name	gid	uid	ident	level

2371
 
1	fs	NULL	NULL	0	READ

2372
 
drop table t1,t2,t3;

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

2377
 
KEY t1$profile_id (profile_id)

2378
 
);

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

2380
 
CREATE TABLE t2 (
396 by Brian Aker
Cleanup tiny and small int. 
2381
 
profile_id int default NULL,
223 by Brian Aker
Cleanup int() work. 
2382
 
queue_id int default NULL,

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

2385
 
);

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

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

2389
 
qtype int default NULL,

2390
 
seq int default NULL,

2391
 
warn_lvl int default NULL,

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

2397
 
KEY t3$id (id)

2398
 
);

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

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

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

2402
 
WHERE 

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

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

2405
 
COUNT(*)

2406
 
4

2407
 
drop table t1,t2,t3;

2408
 
create table t1 (f1 int);

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

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

2411
 
create index idx1 on t2 (f4);

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

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

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

2415
 
f2

2416
 
1

2417
 
NULL

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

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

2422
 
select * from t2 where a > -1;

2423
 
a

2424
 
0

2425
 
254

2426
 
255

2427
 
drop table t2;

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

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

2430
 
a	c

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

2432
 
a	c

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

2434
 
a	c

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

2436
 
a	c

2437
 
drop table t1;

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

2439
 
create table t2(f3 int);

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

2441
 
f1

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

2443
 
f1

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

2445
 
f1

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

2447
 
insert into t2 values(2);

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

2449
 
f1	f2	f3

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

2451
 
f1	f2	f3

2452
 
2	NULL	2

2453
 
drop table t1,t2;

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

2455
 
create table t11 like t1;

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

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

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

2461
 
a

2462
 
drop table t1,t11;

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

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

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

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

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

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

2469
 
a	b	c	d

2470
 
1	2	1	1

2471
 
1	2	2	1

2472
 
1	2	3	1

2473
 
1	10		2

2474
 
1	11		2

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

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

2477
 
a	b	c	d

2478
 
1	10		4

2479
 
1	2	1	1

2480
 
1	2	2	1

2481
 
1	2	3	1

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

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

2484
 
a	b	c	d

2485
 
1	2	1	1

2486
 
1	2	2	1

2487
 
1	2	3	1

2488
 
1	10		2

2489
 
1	11		2

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

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

2492
 
a	b	c	d

2493
 
1	2	1	1

2494
 
1	2	2	1

2495
 
1	2	3	1

2496
 
DROP TABLE IF EXISTS t1, t2;

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

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

2499
 
insert into t1 values (1,1);

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

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

2502
 
count(f2) >0

2503
 
1

2504
 
drop table t1,t2;

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

2506
 
insert into t1 values(1,1);

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

2508
 
insert into t2 values(1,1);

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

2510
 
f1	f2

2511
 
1	1

2512
 
drop table t1,t2;

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

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

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

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

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

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

2519
 
DROP TABLE t1,t2,t3;

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

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

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

2523
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2524
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	

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

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

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

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

2529
 
DROP TABLE t1;

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

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

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

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

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

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

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

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

2542
 
a	b	a	b

2543
 
1	NULL	1	1

2544
 
1	NULL	2	1

2545
 
1	NULL	4	10

2546
 
2	10	4	10

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

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

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

2552
 
a	b	a	b

2553
 
1	NULL	1	1

2554
 
1	NULL	2	1

2555
 
1	NULL	4	10

2556
 
2	10	4	10

2557
 
DROP TABLE IF EXISTS t1,t2;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2589
 
1	1	1	1

2590
 
DROP TABLE t1;

2591
 
CREATE TABLE t1 (c0 int);

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

2594
 
INSERT INTO t2 VALUES(@@server_id);

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

2597
 
X	X

2598
 
DROP TABLE t1, t2;

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

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

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

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

2608
 
('WART', '0100', 1), 

2609
 
('WART', '0200', 1), 

2610
 
('WERT', '0100', 2), 

2611
 
('WORT','0200', 2), 

2612
 
('WT', '0100', 2), 

2613
 
('W_RT', '0100', 2), 

2614
 
('WaRT', '0100', 3), 

2615
 
('WART', '0300', 3), 

2616
 
('WRT' , '0400', 3), 

2617
 
('WURM', '0500', 3), 

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

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

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

2621
 
SELECT K2C4, K4N4, F2I4 FROM t1

2622
 
WHERE  K2C4 = 'WART' AND 

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

2624
 
K2C4	K4N4	F2I4

2625
 
WART	0200	1

2626
 
SELECT K2C4, K4N4, F2I4 FROM t1

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

2628
 
K2C4	K4N4	F2I4

2629
 
WART	0100	1

2630
 
WART	0200	1

2631
 
WART	0300	3

2632
 
DROP TABLE t1;

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

2634
 
create table t2 like t1;

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

2636
 
a

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

2638
 
a

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

2640
 
a	a	a

2641
 
drop table t1,t2;

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

2643
 
insert into t1 values ('Wall');

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

2645
 
min(s1)

2646
 
Wall

2647
 
Wall

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

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

2652
 
avg(distinct s1)

2653
 
0.0000

2654
 
0.0000

2655
 
drop table t1;

2656
 
create table t1 (s1 int);

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

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

2659
 
x

2660
 
NULL

2661
 
1.0000

2662
 
drop table t1;

2663
 
CREATE TABLE t1 (a int);

2664
 
CREATE TABLE t2 (a int);

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

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

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

2668
 
a

2669
 
2

2670
 
4

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

2672
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2673
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	

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

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

2676
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2677
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	

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

2679
 
DROP TABLE t1,t2;

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

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

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

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

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

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

2688
 
f1	f2	f3	f4

2689
 
 2	2	 2	 one 

2690
 
 2	2	 2	 two 

2691
 
drop table t1,t2;

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

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

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

2695
 
pk

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

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

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

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

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

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

2704
 
a	b	t1_val	t2_val

2705
 
1	1	1	1

2706
 
2	1	2	1

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

2708
 
b	a	t1_val	t2_val

2709
 
1	1	1	1

2710
 
1	2	2	1

2711
 
drop table t1, t2, t3;

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

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

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

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

2716
 
select a from t1 natural join t2;

2717
 
a

2718
 
b

2719
 
c

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

2721
 
a

2722
 
b

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

2725
 
CREATE TABLE t2 (`id` int);

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

2728
 
INSERT INTO t2 VALUES (2);

2729
 
INSERT INTO t3 VALUES (3);

2730
 
SELECT t1.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
 
SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);

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

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

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

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

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

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

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

2742
 
insert into t2 values (1,10);

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

2744
 
a	b	b

2745
 
1	10	10

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

2747
 
a	b	b

2748
 
1	10	10

2749
 
drop table t1, t2;

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

2751
 
create table t2 (b int);

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

2753
 
create table t4 (c int);

2754
 
insert into t1 values (1,1);

2755
 
insert into t2 values (1);

2756
 
insert into t3 values (1,1);

2757
 
insert into t4 values (1);

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

2759
 
a	c	b	b	a

2760
 
1	1	1	1	1

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

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

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

2764
 
a	c	b	b	a	c

2765
 
1	1	1	1	1	1

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

2767
 
c	a	b

2768
 
1	1	1

2769
 
drop table t1, t2, t3, t4;

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

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

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

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

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

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

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

2777
 
x	x

2778
 
1	1

2779
 
2	1

2780
 
3	1

2781
 
3	2

2782
 
3	3

2783
 
4	3

2784
 
4	4

2785
 
4	5

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

2793
 
select count(*)

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

2795
 
count(*)

2796
 
6

2797
 
select count(*)

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

2799
 
count(*)

2800
 
6

2801
 
drop table t1,t2,t3;

2802
 
create table t1 (a int);

2803
 
create table t2 (b int);

2804
 
create table t3 (c int);

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

2806
 
a	b	c

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

2808
 
a	b	c

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

2810
 
a	b	c

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

2812
 
a	b	c

2813
 
drop table t1, t2 ,t3;

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

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

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

2817
 
select * from t1 where f2 >= 0            order by f2;
2114.5.4 by Brian Aker
Fix error message on date. 
2818
 
ERROR HY000: Received an invalid DATE value '0'.
1 by brian
clean slate 
2819
 
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 
2820
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
2821
 
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 
2822
 
ERROR HY000: Received an invalid datetime value '2005-09-31'.
1 by brian
clean slate 
2823
 
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 
2824
 
ERROR HY000: Received an invalid datetime value '2005-09-3a'.
1 by brian
clean slate 
2825
 
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 
2826
 
ERROR HY000: Received an invalid datetime value '2005-09-31'.
1 by brian
clean slate 
2827
 
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 
2828
 
ERROR HY000: Received an invalid datetime value '2005-09-3a'.
1 by brian
clean slate 
2829
 
drop table t1;

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

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

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

2833
 
PRIMARY KEY(key_a,key_b));

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

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

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

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

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

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

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

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

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

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

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

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

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

2847
 
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
 
key_a	foo

2852
 
2	xx

2853
 
EXPLAIN SELECT t2.key_a,foo 

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

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

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

2857
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2860
 
1	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	

2861
 
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
 
key_a	foo

2866
 
2	xx

2867
 
EXPLAIN SELECT t2.key_a,foo 

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

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

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

2871
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

2874
 
1	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	

2875
 
DROP TABLE t1,t2,t3;

2876
 
create  table t1 (f1 int);

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

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

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

2880
 
create table t3 (f4 int not null);

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

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

2883
 
f1	count

2884
 
1	0

2885
 
2	3

2886
 
drop table t1,t2,t3;

2887
 
create table t1 (f1 int unique);

2888
 
create table t2 (f2 int unique);

2889
 
create table t3 (f3 int unique);

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

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

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

2893
 
select * from t3 where f3 is null;

2894
 
f3

2895
 
NULL

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

2897
 
f2

2898
 
1

2899
 
drop table t1,t2,t3;

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

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

2902
 
create table t2 (f2 char not null);

2903
 
insert into t2 values('b');

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

2905
 
f1	f2	f2

2906
 
NULL	a	NULL

2907
 
drop table t1,t2;

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

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

2912
 
INSERT INTO t1 VALUES

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

2914
 
INSERT INTO t2 VALUES 

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

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

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
 
sku	sppr	name	sku	pr

2920
 
20	10	bbb	10	10

2921
 
20	10	bbb	20	10

2922
 
EXPLAIN

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

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

2925
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

2929
 
create table t1 (a int);

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

2931
 
create table t2 (a int, b int, c int, e int, primary key(a,b,c));
2141.4.2 by Andrew Hutchings
Implicit joins of the form "SELECT * FROM t1, t2" without WHERE or ON now error. 
2932
 
insert into t2 select A.a, B.a, C.a, C.a from t1 A CROSS JOIN t1 B CROSS JOIN t1 C;
1 by brian
clean slate 
2933
 
analyze table t2;

2934
 
Table	Op	Msg_type	Msg_text

2935
 
test.t2	analyze	status	OK

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

2937
 
Z

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

2939
 
drop table t1, t2;

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

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

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

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

2944
 
INSERT INTO t2 VALUES

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

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

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

2948
 
EXPLAIN

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

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

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

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

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

2962
 
c int             not null
1 by brian
clean slate 
2963
 
);

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

2969
 
);

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

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

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

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

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

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

2976
 
1	0	1	1	0	1	NULL

2977
 
2	0	1	NULL	NULL	NULL	NULL

2978
 
drop table t1,t2;

2979
 
SELECT 0.9888889889 * 1.011111411911;

2980
 
0.9888889889 * 1.011111411911

2981
 
0.9998769417899202067879

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

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

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

2985
 
INSERT INTO t2 VALUES

2986
 
(1), (1), (1), (1), (1), (1), (1), (1),

2987
 
(2), (2), (2), (2),

2988
 
(3), (3),

2989
 
(4);

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

2991
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2992
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1
1819.9.76 by Stewart Smith
update select and subselect_mat test results EXPLAIN output due to improvements in innodb stats 
2993
 
1	SIMPLE	t2	ref	idx	idx	4	const	8	Using index
1 by brian
clean slate 
2994
 
EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;

2995
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2996
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

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

2998
 
DROP TABLE t1, t2;

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

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

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

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

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

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

3005
 
EXPLAIN

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	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3009
 
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	

3010
 
1	SIMPLE	t2	const	idx1	NULL	NULL	NULL	1
1819.9.76 by Stewart Smith
update select and subselect_mat test results EXPLAIN output due to improvements in innodb stats 
3011
 
1	SIMPLE	t3	ALL	idx1	NULL	NULL	NULL	5	Using where
1 by brian
clean slate 
3012
 
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id

3013
 
WHERE t1.id=2;

3014
 
id	a	b	c	d	e

3015
 
2	NULL	NULL	NULL	2	10

3016
 
2	NULL	NULL	NULL	2	20

3017
 
2	NULL	NULL	NULL	2	40

3018
 
2	NULL	NULL	NULL	2	50

3019
 
DROP TABLE t1,t2,t3;

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

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

3022
 
INSERT INTO t1 VALUES

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

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

3025
 
INSERT INTO t2 VALUES

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

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

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

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

3030
 
EXPLAIN SELECT t2.* 

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

3032
 
WHERE t2.fk < 'c' 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 BETWEEN 'a' AND '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
 
EXPLAIN SELECT t2.* 

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

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

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

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

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

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

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

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

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

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

3055
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

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

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

3062
 
INSERT INTO t1 VALUES 

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

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

3065
 
INSERT INTO t2 VALUES

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

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

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

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

3070
 
INSERT INTO t3 VALUES

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

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

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

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

3075
 
EXPLAIN

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

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

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

3079
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3083
 
EXPLAIN

3084
 
SELECT t3.a FROM t1,t2,t3

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

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

3087
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

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

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

3095
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3099
 
EXPLAIN 

3100
 
SELECT t3.a FROM t1,t2,t3

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

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

3103
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3107
 
DROP TABLE t1,t2,t3;

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

3109
 
CREATE TABLE t2 ( f11 int PRIMARY KEY );

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

3111
 
INSERT INTO t2 VALUES (62);

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

3113
 
f1	f2	f3	f4	f5	f6	checked_out	f11

3114
 
1	1	1	0	0	0	0	NULL

3115
 
DROP TABLE t1, t2;

3116
 
DROP TABLE IF EXISTS t1;

3117
 
CREATE TABLE t1(a int);

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

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

3122
 
CREATE TABLE t1 (

3123
 
ID_with_null int NULL,

3124
 
ID_better int NOT NULL,

3125
 
INDEX idx1 (ID_with_null),

3126
 
INDEX idx2 (ID_better)

3127
 
);

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

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

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

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

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

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

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

3135
 
COUNT(*)

3136
 
128

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

3138
 
COUNT(*)

3139
 
2

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

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

3144
 
CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);

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

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

3149
 
CREATE TABLE t1 (

3150
 
ID1_with_null int NULL,

3151
 
ID2_with_null int NULL,

3152
 
ID_better int NOT NULL,

3153
 
INDEX idx1 (ID1_with_null, ID2_with_null),

3154
 
INDEX idx2 (ID_better)

3155
 
);

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

3157
 
(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);

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

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

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

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

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

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

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

3165
 
COUNT(*)

3166
 
24

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

3168
 
COUNT(*)

3169
 
24

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

3171
 
COUNT(*)

3172
 
192

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

3174
 
COUNT(*)

3175
 
2

3176
 
EXPLAIN SELECT * FROM t1

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

3178
 
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 
3179
 
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 
3180
 
EXPLAIN SELECT * FROM t1

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

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

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

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

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

3190
 
EXPLAIN SELECT * FROM t1

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

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

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

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

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

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

3203
 
WHERE ID_better=1 AND ID1_with_null IS NULL AND 

3204
 
(ID2_with_null=1 OR ID2_with_null=2);

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

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

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

3210
 
ANALYZE TABLE t1;

3211
 
Table	Op	Msg_type	Msg_text

3212
 
test.t1	analyze	status	OK

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

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

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

3216
 
ANALYZE TABLE t2;

3217
 
Table	Op	Msg_type	Msg_text

3218
 
test.t2	analyze	status	OK

3219
 
EXPLAIN

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
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

3224
 
1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1
1819.9.76 by Stewart Smith
update select and subselect_mat test results EXPLAIN output due to improvements in innodb stats 
3225
 
1	SIMPLE	t1	range	ts	ts	9	NULL	2	Using where
1 by brian
clean slate 
3226
 
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30

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

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

3229
 
a	ts	a	dt1	dt2

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

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

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

3236
 
(coalesce(92233720368547758, 1));
1 by brian
clean slate 
3237
 
select * from t1;

3238
 
a
413.2.2 by Brian Aker
Removed UNSIGNED from parser. 
3239
 
92233720368547758

3240
 
92233720368547758

3241
 
92233720368547758
1 by brian
clean slate 
3242
 
drop table t1;

3243
 
create table t1 select

3244
 
if(1, 9223372036854775808, 1) i,

3245
 
case when 1 then 9223372036854775808 else 1 end c,

3246
 
coalesce(9223372036854775808, 1) co;

3247
 
show create table t1;

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

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

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

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

3257
 
case when 1 then 1111111111111111111 else 1 end c,

3258
 
coalesce(1111111111111111111, 1) co;
1 by brian
clean slate 
3259
 
i	c	co

3260
 
1111111111111111111	1111111111111111111	1111111111111111111

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

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

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

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

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

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

3267
 
SELECT * FROM t2;

3268
 
name	n

3269
 
bb	1

3270
 
aa	2

3271
 
cc   	3

3272
 
cc 	4

3273
 
cc	5

3274
 
bb 	6

3275
 
cc 	7

3276
 
SELECT * FROM t2 ORDER BY name;

3277
 
name	n

3278
 
aa	2

3279
 
bb	1

3280
 
bb 	6

3281
 
cc 	4

3282
 
cc   	3

3283
 
cc	5

3284
 
cc 	7

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

3286
 
name	LENGTH(name)	n

3287
 
aa	2	2

3288
 
bb	2	1

3289
 
bb 	3	6

3290
 
cc 	4	4

3291
 
cc   	5	3

3292
 
cc	2	5

3293
 
cc 	3	7

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

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

3298
 
name	LENGTH(name)	n

3299
 
cc   	5	3

3300
 
cc	2	5

3301
 
cc 	3	7

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

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

3306
 
name	LENGTH(name)	n

3307
 
cc   	5	3

3308
 
cc 	4	4

3309
 
cc	2	5

3310
 
cc 	3	7

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

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

3315
 
name	LENGTH(name)	n

3316
 
cc 	4	4

3317
 
cc   	5	3

3318
 
cc	2	5

3319
 
cc 	3	7

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

3321
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3325
 
name	name	n

3326
 
ccc	NULL	NULL

3327
 
bb	bb	1

3328
 
bb	bb 	6

3329
 
cc 	cc   	3

3330
 
cc 	cc	5

3331
 
cc 	cc 	7

3332
 
aa  	aa	2

3333
 
aa	aa	2

3334
 
DROP TABLE t1,t2;

3335
 
CREATE TABLE t1 (name text);

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

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

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

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

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

3341
 
SELECT * FROM t2;

3342
 
name	n

3343
 
bb	1

3344
 
aa	2

3345
 
cc   	3

3346
 
cc 	4

3347
 
cc	5

3348
 
bb 	6

3349
 
cc 	7

3350
 
SELECT * FROM t2 ORDER BY name;

3351
 
name	n

3352
 
aa	2

3353
 
bb	1

3354
 
bb 	6

3355
 
cc 	4

3356
 
cc   	3

3357
 
cc	5

3358
 
cc 	7

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

3360
 
name	LENGTH(name)	n

3361
 
aa	2	2

3362
 
bb	2	1

3363
 
bb 	3	6

3364
 
cc 	4	4

3365
 
cc   	5	3

3366
 
cc	2	5

3367
 
cc 	3	7

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

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

3372
 
name	LENGTH(name)	n

3373
 
cc   	5	3

3374
 
cc	2	5

3375
 
cc 	3	7

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

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

3380
 
name	LENGTH(name)	n

3381
 
cc   	5	3

3382
 
cc 	4	4

3383
 
cc	2	5

3384
 
cc 	3	7

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

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

3389
 
name	LENGTH(name)	n

3390
 
cc 	4	4

3391
 
cc   	5	3

3392
 
cc	2	5

3393
 
cc 	3	7

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

3395
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3399
 
name	name	n

3400
 
ccc	NULL	NULL

3401
 
bb	bb	1

3402
 
bb	bb 	6

3403
 
cc 	cc   	3

3404
 
cc 	cc	5

3405
 
cc 	cc 	7

3406
 
aa  	aa	2

3407
 
aa	aa	2

3408
 
DROP TABLE t1,t2;

3409
 
CREATE TABLE t1 (

3410
 
access_id int NOT NULL default '0',

3411
 
name varchar(20) default NULL,

3412
 
rank int NOT NULL default '0',

3413
 
KEY idx (access_id)

3414
 
);

3415
 
CREATE TABLE t2 (

3416
 
faq_group_id int NOT NULL default '0',

3417
 
faq_id int NOT NULL default '0',

3418
 
access_id int default NULL,

3419
 
UNIQUE KEY idx1 (faq_id),

3420
 
KEY idx2 (faq_group_id,faq_id)

3421
 
);

3422
 
INSERT INTO t1 VALUES 

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

3424
 
INSERT INTO t2 VALUES

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

3426
 
SELECT t2.faq_id 

3427
 
FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)

3428
 
ON (t1.access_id = t2.access_id)

3429
 
LEFT JOIN t2 t

3430
 
ON (t.faq_group_id = t2.faq_group_id AND

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

3432
 
WHERE

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

3434
 
faq_id

3435
 
265

3436
 
SELECT t2.faq_id 

3437
 
FROM t1 INNER JOIN t2

3438
 
ON (t1.access_id = t2.access_id)

3439
 
LEFT JOIN t2 t

3440
 
ON (t.faq_group_id = t2.faq_group_id AND

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

3442
 
WHERE

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

3444
 
faq_id

3445
 
265

3446
 
DROP TABLE t1,t2;

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

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

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

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

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

3452
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

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

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

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

3459
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

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

3461
 
31	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

3462
 
32	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

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

3464
 
ERROR HY000: Too high level of nesting for select

3465
 
DROP TABLE t1;

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

3470
 
c4 bigint DEFAULT NULL,
1 by brian
clean slate 
3471
 
PRIMARY KEY (c1)

3472
 
);

3473
 
EXPLAIN EXTENDED 

3474
 
SELECT  join_2.c1  

3475
 
FROM 

3476
 
t1 AS join_0, 

3477
 
t1 AS join_1, 

3478
 
t1 AS join_2, 

3479
 
t1 AS join_3, 

3480
 
t1 AS join_4, 

3481
 
t1 AS join_5, 

3482
 
t1 AS join_6, 

3483
 
t1 AS join_7

3484
 
WHERE 

3485
 
join_0.c1=join_1.c1  AND 

3486
 
join_1.c1=join_2.c1  AND 

3487
 
join_2.c1=join_3.c1  AND 

3488
 
join_3.c1=join_4.c1  AND 

3489
 
join_4.c1=join_5.c1  AND 

3490
 
join_5.c1=join_6.c1  AND 

3491
 
join_6.c1=join_7.c1 

3492
 
OR 

3493
 
join_0.c2 < '?'  AND 

3494
 
join_1.c2 < '?'  AND

3495
 
join_2.c2 > '?'  AND

3496
 
join_2.c2 < '!'  AND

3497
 
join_3.c2 > '?'  AND 

3498
 
join_4.c2 = '?'  AND 

3499
 
join_5.c2 <> '?' AND

3500
 
join_6.c2 <> '?' AND 

3501
 
join_7.c2 >= '?' AND

3502
 
join_0.c1=join_1.c1  AND 

3503
 
join_1.c1=join_2.c1  AND 

3504
 
join_2.c1=join_3.c1  AND

3505
 
join_3.c1=join_4.c1  AND 

3506
 
join_4.c1=join_5.c1  AND 

3507
 
join_5.c1=join_6.c1  AND 

3508
 
join_6.c1=join_7.c1

3509
 
GROUP BY 

3510
 
join_3.c1,

3511
 
join_2.c1,

3512
 
join_7.c1,

3513
 
join_1.c1,

3514
 
join_0.c1;

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

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

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

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

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

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

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

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

3527
 
Level	Code	Message
383.1.16 by Brian Aker
Force client communication into UTF8 
3528
 
Note	1003	select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_4`.`c2` = '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`
1 by brian
clean slate 
3529
 
DROP TABLE t1;

3530
 
SELECT 1 AS ` `;

3531
 









3532



1










3533



Warnings:










3534



Warning	1474	Name ' ' has become ''










3535



SELECT 1 AS `  `;










3536












3537



1










3538



Warnings:










3539



Warning	1474	Name '  ' has become ''










3540



SELECT 1 AS ` x`;










3541



x










3542



1










3543



Warnings:










3544



Warning	1466	Leading spaces are removed from name ' x'








413.2.2
by Brian Aker


Removed UNSIGNED from parser.



3545



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










3546



CREATE TABLE t2 (c21 INT NOT NULL, 








1
by brian


clean slate



3547



c22 INT DEFAULT NULL, 










3548



KEY(c21, c22));








413.2.2
by Brian Aker


Removed UNSIGNED from parser.



3549



CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0, 








1
by brian


clean slate



3550



c32 INT DEFAULT NULL, 










3551



c33 INT NOT NULL, 








413.2.2
by Brian Aker


Removed UNSIGNED from parser.



3552



c34 INT DEFAULT 0,








1
by brian


clean slate



3553



KEY (c33, c34, c32));










3554



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








2141.4.2
by Andrew Hutchings


Implicit joins of the form "SELECT * FROM t1, t2" without WHERE or ON now error.



3555



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








1
by brian


clean slate



3556



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










3557



(2, 2, 0, 0), 










3558



(3, 3, 1, 0), 










3559



(4, 4, 0, 0), 










3560



(5, 5, 1, 0);










3561



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










3562



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










3563



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










3564



ORDER BY c32;










3565



c32










3566



1










3567



1










3568



3










3569



3










3570



5










3571



5










3572



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










3573



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










3574



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










3575



ORDER BY c32 DESC;










3576



c32










3577



5










3578



5










3579



3










3580



3










3581



1










3582



1










3583



DROP TABLE t1, t2, t3;










3584












3585



#










3586



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










3587



# Inserting Data.










3588



#










3589



DROP TABLE IF EXISTS t1;










3590



DROP TABLE IF EXISTS t2;










3591












3592



CREATE TABLE t1(










3593



c1 DECIMAL(10, 2),










3594



c2 FLOAT);










3595












3596



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










3597












3598



CREATE TABLE t2(










3599



c3 DECIMAL(10, 2))










3600



SELECT










3601



c1 * c2 AS c3










3602



FROM t1;










3603












3604



SELECT * FROM t1;










3605



c1	c2










3606



0.00	1










3607



2.00	3










3608



4.00	5










3609












3610



SELECT * FROM t2;










3611



c3










3612



0.00










3613



6.00










3614



20.00










3615












3616



DROP TABLE t1;










3617



DROP TABLE t2;










3618












3619



CREATE TABLE t1 (c1 BIGINT NOT NULL);










3620



INSERT INTO t1 (c1) VALUES (1);










3621



SELECT * FROM t1 WHERE c1 > NULL + 1;










3622



c1










3623



DROP TABLE t1;










3624












3625



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










3626



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










3627



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










3628



a










3629



foo0










3630



DROP TABLE t1;










3631



CREATE TABLE t1 (a INT, b INT);










3632



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










3633



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










3634



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










3635



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










3636



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










3637



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










3638



FLUSH STATUS;










3639



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










3640



b










3641



1










3642



2










3643



SHOW STATUS LIKE 'Handler_read%';










3644



Variable_name	Value








201
by Brian Aker


Convert default engine to Innodb



3645



Handler_read_first	1










3646



Handler_read_key	5








1
by brian


clean slate



3647



Handler_read_next	0










3648



Handler_read_prev	0










3649



Handler_read_rnd	0








1561.3.11
by Joe Daly


get tests working



3650



Handler_read_rnd_next	6








1
by brian


clean slate



3651



DROP TABLE t1, t2;










3652



End of 5.0 tests










3653



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










3654



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










3655



SELECT a FROM t1 ORDER BY a LIMIT 2;










3656



a










3657



1










3658



2










3659



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










3660



a










3661



3










3662



4










3663



5










3664



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










3665



a










3666



3










3667



4










3668



5










3669



DROP TABLE t1;








1240.8.8
by Dennis Schoen


record changes to select testcase



3670



CREATE TABLE t1 (a INT);










3671



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










3672



CREATE TABLE t2 (b INT);










3673



INSERT INTO t2 VALUES (2);










3674



SELECT * FROM t1 WHERE a = 1 + 1;










3675



a










3676



2










3677



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










3678



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3679



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










3680



Warnings:










3681



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










3682



SELECT * FROM t1 HAVING a = 1 + 1;










3683



a










3684



2










3685



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










3686



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3687



1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	










3688



Warnings:










3689



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










3690



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










3691



a	b










3692



4	2










3693



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










3694



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3695



1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	










3696



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










3697



Warnings:










3698



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










3699



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










3700



b	a










3701



2	3










3702



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










3703



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3704



1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	










3705



1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	










3706



Warnings:










3707



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










3708



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










3709



id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra










3710



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










3711



Warnings:










3712



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








1435.1.5
by Stewart Smith


select test leaving around tables carelessly



3713



DROP TABLE t1, t2;