← Back to branch summary

~drizzle-trunk/drizzle/development

1 by brian
clean slate 
1
 
drop table if exists t1,t2,t3,t4;

2
 
CREATE TABLE t1 (

3
 
Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,

4
 
Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL

5
 
);

6
 
INSERT INTO t1 VALUES (9410,9412);

7
 
select period from t1;

8
 
period

9
 
9410

10
 
select * from t1;

11
 
Period	Varor_period

12
 
9410	9412

13
 
select t1.* from t1;

14
 
Period	Varor_period

15
 
9410	9412

16
 
CREATE TABLE t2 (

17
 
auto int not null auto_increment,

18
 
fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,

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

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

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

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

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

24
 
UNIQUE fld1 (fld1),

25
 
KEY fld3 (fld3),

26
 
PRIMARY KEY (auto)

27
 
);

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

29
 
fld3

30
 
imaginable

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

32
 
fld3

33
 
cultivation

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

35
 
fld3	companynr

36
 
concoct	58

37
 
druggists	58

38
 
engrossing	58

39
 
Eurydice	58

40
 
exclaimers	58

41
 
ferociousness	58

42
 
hopelessness	58

43
 
Huey	58

44
 
imaginable	58

45
 
judges	58

46
 
merging	58

47
 
ostrich	58

48
 
peering	58

49
 
Phelps	58

50
 
presumes	58

51
 
Ruth	58

52
 
sentences	58

53
 
Shylock	58

54
 
straggled	58

55
 
synergy	58

56
 
thanking	58

57
 
tying	58

58
 
unlocks	58

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

60
 
fld3	companynr

61
 
concoct	58

62
 
druggists	58

63
 
engrossing	58

64
 
Eurydice	58

65
 
exclaimers	58

66
 
ferociousness	58

67
 
hopelessness	58

68
 
Huey	58

69
 
imaginable	58

70
 
judges	58

71
 
merging	58

72
 
ostrich	58

73
 
peering	58

74
 
Phelps	58

75
 
presumes	58

76
 
Ruth	58

77
 
sentences	58

78
 
Shylock	58

79
 
straggled	58

80
 
synergy	58

81
 
thanking	58

82
 
tying	58

83
 
unlocks	58

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

85
 
fld3

86
 
youthfulness

87
 
yelped

88
 
Wotan

89
 
workers

90
 
Witt

91
 
witchcraft

92
 
Winsett

93
 
Willy

94
 
willed

95
 
wildcats

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

97
 
fld3

98
 
youthfulness

99
 
yelped

100
 
Wotan

101
 
workers

102
 
Witt

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

104
 
fld3

105
 
witchcraft

106
 
Winsett

107
 
Willy

108
 
willed

109
 
wildcats

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

111
 
fld3

112
 
honeysuckle

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

114
 
fld3

115
 
honeysuckle

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

117
 
fld3

118
 
honeysuckle

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

120
 
fld3

121
 
honeysuckle

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

123
 
fld3

124
 
honeysuckle

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

126
 
fld3

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

128
 
fld3

129
 
explain select t2.fld3 from t2 where fld3 = 'honeysuckle';

130
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

131
 
1	SIMPLE	t2	ref	fld3	fld3	30	const	1	Using where; Using index

132
 
explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';

133
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

134
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where

135
 
explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';

136
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

137
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where

138
 
explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';

139
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

140
 
1	SIMPLE	t2	ref	fld3	fld3	30	const	1	Using where; Using index

141
 
explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';

142
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

143
 
1	SIMPLE	t2	ref	fld3	fld3	30	const	1	Using where; Using index

144
 
explain select fld3 from t2 ignore index (fld3,not_used);

145
 
ERROR 42000: Key 'not_used' doesn't exist in table 't2'

146
 
explain select fld3 from t2 use index (not_used);

147
 
ERROR 42000: Key 'not_used' doesn't exist in table 't2'

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

149
 
fld3

150
 
honeysuckle

151
 
honoring

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

153
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

154
 
1	SIMPLE	t2	range	fld3	fld3	30	NULL	2	Using where; Using index

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

156
 
fld1	fld3

157
 
148504	Colombo

158
 
068305	Colombo

159
 
000000	nondecreasing

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

161
 
fld1	fld3

162
 
232605	appendixes

163
 
1232605	appendixes

164
 
1232606	appendixes

165
 
1232607	appendixes

166
 
1232608	appendixes

167
 
1232609	appendixes

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

169
 
fld1

170
 
250501

171
 
250502

172
 
explain select fld1 from t2 where fld1=250501 or fld1="250502";

173
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

174
 
1	SIMPLE	t2	range	fld1	fld1	4	NULL	2	Using where; Using index

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

176
 
fld1

177
 
250501

178
 
250502

179
 
250505

180
 
250601

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

182
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

183
 
1	SIMPLE	t2	range	fld1	fld1	4	NULL	4	Using where; Using index

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

185
 
fld1	fld3

186
 
012001	flanking

187
 
013602	foldout

188
 
013606	fingerings

189
 
018007	fanatic

190
 
018017	featherweight

191
 
018054	fetters

192
 
018103	flint

193
 
018104	flopping

194
 
036002	funereal

195
 
038017	fetched

196
 
038205	firearm

197
 
058004	Fenton

198
 
088303	feminine

199
 
186002	freakish

200
 
188007	flurried

201
 
188505	fitting

202
 
198006	furthermore

203
 
202301	Fitzpatrick

204
 
208101	fiftieth

205
 
208113	freest

206
 
218008	finishers

207
 
218022	feed

208
 
218401	faithful

209
 
226205	foothill

210
 
226209	furnishings

211
 
228306	forthcoming

212
 
228311	fated

213
 
231315	freezes

214
 
232102	forgivably

215
 
238007	filial

216
 
238008	fixedly

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

218
 
fld3

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

220
 
fld3

221
 
Chantilly

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

223
 
fld1	fld3

224
 
250501	poisoning

225
 
250502	Iraqis

226
 
250503	heaving

227
 
250504	population

228
 
250505	bomb

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

230
 
fld1	fld3

231
 
250501	poisoning

232
 
250502	Iraqis

233
 
250503	heaving

234
 
250504	population

235
 
250505	bomb

236
 
select distinct companynr from t2;

237
 
companynr

238
 
00

239
 
37

240
 
36

241
 
50

242
 
58

243
 
29

244
 
40

245
 
53

246
 
65

247
 
41

248
 
34

249
 
68

250
 
select distinct companynr from t2 order by companynr;

251
 
companynr

252
 
00

253
 
29

254
 
34

255
 
36

256
 
37

257
 
40

258
 
41

259
 
50

260
 
53

261
 
58

262
 
65

263
 
68

264
 
select distinct companynr from t2 order by companynr desc;

265
 
companynr

266
 
68

267
 
65

268
 
58

269
 
53

270
 
50

271
 
41

272
 
40

273
 
37

274
 
36

275
 
34

276
 
29

277
 
00

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

279
 
fld3	period

280
 
obliterates	9410

281
 
offload	9410

282
 
opaquely	9410

283
 
organizer	9410

284
 
overestimating	9410

285
 
overlay	9410

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

287
 
fld3

288
 
absentee

289
 
accessed

290
 
ahead

291
 
alphabetic

292
 
Asiaticizations

293
 
attitude

294
 
aye

295
 
bankruptcies

296
 
belays

297
 
Blythe

298
 
bomb

299
 
boulevard

300
 
bulldozes

301
 
cannot

302
 
caressing

303
 
charcoal

304
 
checksumming

305
 
chess

306
 
clubroom

307
 
colorful

308
 
cosy

309
 
creator

310
 
crying

311
 
Darius

312
 
diffusing

313
 
duality

314
 
Eiffel

315
 
Epiphany

316
 
Ernestine

317
 
explorers

318
 
exterminated

319
 
famine

320
 
forked

321
 
Gershwins

322
 
heaving

323
 
Hodges

324
 
Iraqis

325
 
Italianization

326
 
Lagos

327
 
landslide

328
 
libretto

329
 
Majorca

330
 
mastering

331
 
narrowed

332
 
occurred

333
 
offerers

334
 
Palestine

335
 
Peruvianizes

336
 
pharmaceutic

337
 
poisoning

338
 
population

339
 
Pygmalion

340
 
rats

341
 
realest

342
 
recording

343
 
regimented

344
 
retransmitting

345
 
reviver

346
 
rouses

347
 
scars

348
 
sicker

349
 
sleepwalk

350
 
stopped

351
 
sugars

352
 
translatable

353
 
uncles

354
 
unexpected

355
 
uprisings

356
 
versatility

357
 
vest

358
 
select distinct fld3 from t2 limit 10;

359
 
fld3

360
 
abates

361
 
abiding

362
 
Abraham

363
 
abrogating

364
 
absentee

365
 
abut

366
 
accessed

367
 
accruing

368
 
accumulating

369
 
accuracies

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

371
 
fld3

372
 
abates

373
 
abiding

374
 
Abraham

375
 
abrogating

376
 
absentee

377
 
abut

378
 
accessed

379
 
accruing

380
 
accumulating

381
 
accuracies

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

383
 
substring(fld3,1,3)

384
 
aba

385
 
abi

386
 
Abr

387
 
abs

388
 
abu

389
 
acc

390
 
acq

391
 
acu

392
 
Ade

393
 
adj

394
 
Adl

395
 
adm

396
 
Ado

397
 
ads

398
 
adv

399
 
aer

400
 
aff

401
 
afi

402
 
afl

403
 
afo

404
 
agi

405
 
ahe

406
 
aim

407
 
air

408
 
Ald

409
 
alg

410
 
ali

411
 
all

412
 
alp

413
 
alr

414
 
ama

415
 
ame

416
 
amm

417
 
ana

418
 
and

419
 
ane

420
 
Ang

421
 
ani

422
 
Ann

423
 
Ant

424
 
api

425
 
app

426
 
aqu

427
 
Ara

428
 
arc

429
 
Arm

430
 
arr

431
 
Art

432
 
Asi

433
 
ask

434
 
asp

435
 
ass

436
 
ast

437
 
att

438
 
aud

439
 
Aug

440
 
aut

441
 
ave

442
 
avo

443
 
awe

444
 
aye

445
 
Azt

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

447
 
a

448
 
aba

449
 
abi

450
 
Abr

451
 
abs

452
 
abu

453
 
acc

454
 
acq

455
 
acu

456
 
Ade

457
 
adj

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

459
 
substring(fld3,1,3)

460
 
aba

461
 
abi

462
 
Abr

463
 
abs

464
 
abu

465
 
acc

466
 
acq

467
 
acu

468
 
Ade

469
 
adj

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

471
 
a

472
 
aba

473
 
abi

474
 
Abr

475
 
abs

476
 
abu

477
 
acc

478
 
acq

479
 
acu

480
 
Ade

481
 
adj

482
 
create table t3 (

483
 
period    int not null,

484
 
name      char(32) not null,

485
 
companynr int not null,

486
 
price     double(11,0),

487
 
price2     double(11,0),

488
 
key (period),

489
 
key (name)

490
 
);

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

492
 
insert into t3 select * from tmp;

493
 
insert into tmp select * from t3;

494
 
insert into t3 select * from tmp;

495
 
insert into tmp select * from t3;

496
 
insert into t3 select * from tmp;

497
 
insert into tmp select * from t3;

498
 
insert into t3 select * from tmp;

499
 
insert into tmp select * from t3;

500
 
insert into t3 select * from tmp;

501
 
insert into tmp select * from t3;

502
 
insert into t3 select * from tmp;

503
 
insert into tmp select * from t3;

504
 
insert into t3 select * from tmp;

505
 
insert into tmp select * from t3;

506
 
insert into t3 select * from tmp;

507
 
insert into tmp select * from t3;

508
 
insert into t3 select * from tmp;

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

510
 
drop table tmp;

511
 
SET SQL_BIG_TABLES=1;

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

513
 
namn

514
 
Abraham Abraham

515
 
abrogating abrogating

516
 
admonishing admonishing

517
 
Adolph Adolph

518
 
afield afield

519
 
aging aging

520
 
ammonium ammonium

521
 
analyzable analyzable

522
 
animals animals

523
 
animized animized

524
 
SET SQL_BIG_TABLES=0;

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

526
 
concat(fld3," ",fld3)

527
 
Abraham Abraham

528
 
abrogating abrogating

529
 
admonishing admonishing

530
 
Adolph Adolph

531
 
afield afield

532
 
aging aging

533
 
ammonium ammonium

534
 
analyzable analyzable

535
 
animals animals

536
 
animized animized

537
 
select distinct fld5 from t2 limit 10;

538
 
fld5

539
 
neat

540
 
Steinberg

541
 
jarring

542
 
tinily

543
 
balled

544
 
persist

545
 
attainments

546
 
fanatic

547
 
measures

548
 
rightfulness

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

550
 
fld3	count(*)

551
 
affixed	1

552
 
and	1

553
 
annoyers	1

554
 
Anthony	1

555
 
assayed	1

556
 
assurers	1

557
 
attendants	1

558
 
bedlam	1

559
 
bedpost	1

560
 
boasted	1

561
 
SET SQL_BIG_TABLES=1;

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

563
 
fld3	count(*)

564
 
affixed	1

565
 
and	1

566
 
annoyers	1

567
 
Anthony	1

568
 
assayed	1

569
 
assurers	1

570
 
attendants	1

571
 
bedlam	1

572
 
bedpost	1

573
 
boasted	1

574
 
SET SQL_BIG_TABLES=0;

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

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

577
 
circus	aaaaaa	1

578
 
cited	aaaaa	1

579
 
Colombo	aaaaaaa	1

580
 
congresswoman	aaaaaaaaaaaaa	1

581
 
contrition	aaaaaaaaaa	1

582
 
corny	aaaaa	1

583
 
cultivation	aaaaaaaaaaa	1

584
 
definiteness	aaaaaaaaaaaa	1

585
 
demultiplex	aaaaaaaaaaa	1

586
 
disappointing	aaaaaaaaaaaaa	1

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

588
 
companynr	rtrim(space(512+companynr))

589
 
37	

590
 
78	

591
 
101	

592
 
154	

593
 
311	

594
 
447	

595
 
512	

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

597
 
fld3

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

599
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

600
 
1	SIMPLE	t2	ALL	fld1	NULL	NULL	NULL	1199	Using where; Using temporary; Using filesort

601
 
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.fld1	1	Using where; Using index

602
 
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;

603
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

604
 
1	SIMPLE	t1	ALL	period	NULL	NULL	NULL	41810	Using temporary; Using filesort

605
 
1	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	

606
 
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;

607
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

608
 
1	SIMPLE	t3	index	period	period	4	NULL	1	

609
 
1	SIMPLE	t1	ref	period	period	4	test.t3.period	4181	

610
 
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;

611
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

612
 
1	SIMPLE	t1	index	period	period	4	NULL	1	

613
 
1	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	

614
 
select period from t1;

615
 
period

616
 
9410

617
 
select period from t1 where period=1900;

618
 
period

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

620
 
fld3	period

621
 
breaking	9410

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

623
 
fld3	period

624
 
breaking	1001

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

626
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

627
 
1	SIMPLE	t2	const	fld1	fld1	4	const	1	

628
 
1	SIMPLE	t3	const	PRIMARY,period	PRIMARY	4	const	1	

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

630
 
fld3	period

631
 
breaking	9410

632
 
Romans	9410

633
 
intercepted	9410

634
 
bewilderingly	9410

635
 
astound	9410

636
 
admonishing	9410

637
 
sumac	9410

638
 
flanking	9410

639
 
combed	9410

640
 
subjective	9410

641
 
scatterbrain	9410

642
 
Eulerian	9410

643
 
Kane	9410

644
 
overlay	9410

645
 
perturb	9410

646
 
goblins	9410

647
 
annihilates	9410

648
 
Wotan	9410

649
 
snatching	9410

650
 
concludes	9410

651
 
laterally	9410

652
 
yelped	9410

653
 
grazing	9410

654
 
Baird	9410

655
 
celery	9410

656
 
misunderstander	9410

657
 
handgun	9410

658
 
foldout	9410

659
 
mystic	9410

660
 
succumbed	9410

661
 
Nabisco	9410

662
 
fingerings	9410

663
 
aging	9410

664
 
afield	9410

665
 
ammonium	9410

666
 
boat	9410

667
 
intelligibility	9410

668
 
Augustine	9410

669
 
teethe	9410

670
 
dreaded	9410

671
 
scholastics	9410

672
 
audiology	9410

673
 
wallet	9410

674
 
parters	9410

675
 
eschew	9410

676
 
quitter	9410

677
 
neat	9410

678
 
Steinberg	9410

679
 
jarring	9410

680
 
tinily	9410

681
 
balled	9410

682
 
persist	9410

683
 
attainments	9410

684
 
fanatic	9410

685
 
measures	9410

686
 
rightfulness	9410

687
 
capably	9410

688
 
impulsive	9410

689
 
starlet	9410

690
 
terminators	9410

691
 
untying	9410

692
 
announces	9410

693
 
featherweight	9410

694
 
pessimist	9410

695
 
daughter	9410

696
 
decliner	9410

697
 
lawgiver	9410

698
 
stated	9410

699
 
readable	9410

700
 
attrition	9410

701
 
cascade	9410

702
 
motors	9410

703
 
interrogate	9410

704
 
pests	9410

705
 
stairway	9410

706
 
dopers	9410

707
 
testicle	9410

708
 
Parsifal	9410

709
 
leavings	9410

710
 
postulation	9410

711
 
squeaking	9410

712
 
contrasted	9410

713
 
leftover	9410

714
 
whiteners	9410

715
 
erases	9410

716
 
Punjab	9410

717
 
Merritt	9410

718
 
Quixotism	9410

719
 
sweetish	9410

720
 
dogging	9410

721
 
scornfully	9410

722
 
bellow	9410

723
 
bills	9410

724
 
cupboard	9410

725
 
sureties	9410

726
 
puddings	9410

727
 
fetters	9410

728
 
bivalves	9410

729
 
incurring	9410

730
 
Adolph	9410

731
 
pithed	9410

732
 
Miles	9410

733
 
trimmings	9410

734
 
tragedies	9410

735
 
skulking	9410

736
 
flint	9410

737
 
flopping	9410

738
 
relaxing	9410

739
 
offload	9410

740
 
suites	9410

741
 
lists	9410

742
 
animized	9410

743
 
multilayer	9410

744
 
standardizes	9410

745
 
Judas	9410

746
 
vacuuming	9410

747
 
dentally	9410

748
 
humanness	9410

749
 
inch	9410

750
 
Weissmuller	9410

751
 
irresponsibly	9410

752
 
luckily	9410

753
 
culled	9410

754
 
medical	9410

755
 
bloodbath	9410

756
 
subschema	9410

757
 
animals	9410

758
 
Micronesia	9410

759
 
repetitions	9410

760
 
Antares	9410

761
 
ventilate	9410

762
 
pityingly	9410

763
 
interdependent	9410

764
 
Graves	9410

765
 
neonatal	9410

766
 
chafe	9410

767
 
honoring	9410

768
 
realtor	9410

769
 
elite	9410

770
 
funereal	9410

771
 
abrogating	9410

772
 
sorters	9410

773
 
Conley	9410

774
 
lectured	9410

775
 
Abraham	9410

776
 
Hawaii	9410

777
 
cage	9410

778
 
hushes	9410

779
 
Simla	9410

780
 
reporters	9410

781
 
Dutchman	9410

782
 
descendants	9410

783
 
groupings	9410

784
 
dissociate	9410

785
 
coexist	9410

786
 
Beebe	9410

787
 
Taoism	9410

788
 
Connally	9410

789
 
fetched	9410

790
 
checkpoints	9410

791
 
rusting	9410

792
 
galling	9410

793
 
obliterates	9410

794
 
traitor	9410

795
 
resumes	9410

796
 
analyzable	9410

797
 
terminator	9410

798
 
gritty	9410

799
 
firearm	9410

800
 
minima	9410

801
 
Selfridge	9410

802
 
disable	9410

803
 
witchcraft	9410

804
 
betroth	9410

805
 
Manhattanize	9410

806
 
imprint	9410

807
 
peeked	9410

808
 
swelling	9410

809
 
interrelationships	9410

810
 
riser	9410

811
 
Gandhian	9410

812
 
peacock	9410

813
 
bee	9410

814
 
kanji	9410

815
 
dental	9410

816
 
scarf	9410

817
 
chasm	9410

818
 
insolence	9410

819
 
syndicate	9410

820
 
alike	9410

821
 
imperial	9410

822
 
convulsion	9410

823
 
railway	9410

824
 
validate	9410

825
 
normalizes	9410

826
 
comprehensive	9410

827
 
chewing	9410

828
 
denizen	9410

829
 
schemer	9410

830
 
chronicle	9410

831
 
Kline	9410

832
 
Anatole	9410

833
 
partridges	9410

834
 
brunch	9410

835
 
recruited	9410

836
 
dimensions	9410

837
 
Chicana	9410

838
 
announced	9410

839
 
praised	9410

840
 
employing	9410

841
 
linear	9410

842
 
quagmire	9410

843
 
western	9410

844
 
relishing	9410

845
 
serving	9410

846
 
scheduling	9410

847
 
lore	9410

848
 
eventful	9410

849
 
arteriole	9410

850
 
disentangle	9410

851
 
cured	9410

852
 
Fenton	9410

853
 
avoidable	9410

854
 
drains	9410

855
 
detectably	9410

856
 
husky	9410

857
 
impelling	9410

858
 
undoes	9410

859
 
evened	9410

860
 
squeezes	9410

861
 
destroyer	9410

862
 
rudeness	9410

863
 
beaner	9410

864
 
boorish	9410

865
 
Everhart	9410

866
 
encompass	9410

867
 
mushrooms	9410

868
 
Alison	9410

869
 
externally	9410

870
 
pellagra	9410

871
 
cult	9410

872
 
creek	9410

873
 
Huffman	9410

874
 
Majorca	9410

875
 
governing	9410

876
 
gadfly	9410

877
 
reassigned	9410

878
 
intentness	9410

879
 
craziness	9410

880
 
psychic	9410

881
 
squabbled	9410

882
 
burlesque	9410

883
 
capped	9410

884
 
extracted	9410

885
 
DiMaggio	9410

886
 
exclamation	9410

887
 
subdirectory	9410

888
 
Gothicism	9410

889
 
feminine	9410

890
 
metaphysically	9410

891
 
sanding	9410

892
 
Miltonism	9410

893
 
freakish	9410

894
 
index	9410

895
 
straight	9410

896
 
flurried	9410

897
 
denotative	9410

898
 
coming	9410

899
 
commencements	9410

900
 
gentleman	9410

901
 
gifted	9410

902
 
Shanghais	9410

903
 
sportswriting	9410

904
 
sloping	9410

905
 
navies	9410

906
 
leaflet	9410

907
 
shooter	9410

908
 
Joplin	9410

909
 
babies	9410

910
 
assails	9410

911
 
admiring	9410

912
 
swaying	9410

913
 
Goldstine	9410

914
 
fitting	9410

915
 
Norwalk	9410

916
 
analogy	9410

917
 
deludes	9410

918
 
cokes	9410

919
 
Clayton	9410

920
 
exhausts	9410

921
 
causality	9410

922
 
sating	9410

923
 
icon	9410

924
 
throttles	9410

925
 
communicants	9410

926
 
dehydrate	9410

927
 
priceless	9410

928
 
publicly	9410

929
 
incidentals	9410

930
 
commonplace	9410

931
 
mumbles	9410

932
 
furthermore	9410

933
 
cautioned	9410

934
 
parametrized	9410

935
 
registration	9410

936
 
sadly	9410

937
 
positioning	9410

938
 
babysitting	9410

939
 
eternal	9410

940
 
hoarder	9410

941
 
congregates	9410

942
 
rains	9410

943
 
workers	9410

944
 
sags	9410

945
 
unplug	9410

946
 
garage	9410

947
 
boulder	9410

948
 
specifics	9410

949
 
Teresa	9410

950
 
Winsett	9410

951
 
convenient	9410

952
 
buckboards	9410

953
 
amenities	9410

954
 
resplendent	9410

955
 
sews	9410

956
 
participated	9410

957
 
Simon	9410

958
 
certificates	9410

959
 
Fitzpatrick	9410

960
 
Evanston	9410

961
 
misted	9410

962
 
textures	9410

963
 
save	9410

964
 
count	9410

965
 
rightful	9410

966
 
chaperone	9410

967
 
Lizzy	9410

968
 
clenched	9410

969
 
effortlessly	9410

970
 
accessed	9410

971
 
beaters	9410

972
 
Hornblower	9410

973
 
vests	9410

974
 
indulgences	9410

975
 
infallibly	9410

976
 
unwilling	9410

977
 
excrete	9410

978
 
spools	9410

979
 
crunches	9410

980
 
overestimating	9410

981
 
ineffective	9410

982
 
humiliation	9410

983
 
sophomore	9410

984
 
star	9410

985
 
rifles	9410

986
 
dialysis	9410

987
 
arriving	9410

988
 
indulge	9410

989
 
clockers	9410

990
 
languages	9410

991
 
Antarctica	9410

992
 
percentage	9410

993
 
ceiling	9410

994
 
specification	9410

995
 
regimented	9410

996
 
ciphers	9410

997
 
pictures	9410

998
 
serpents	9410

999
 
allot	9410

1000
 
realized	9410

1001
 
mayoral	9410

1002
 
opaquely	9410

1003
 
hostess	9410

1004
 
fiftieth	9410

1005
 
incorrectly	9410

1006
 
decomposition	9410

1007
 
stranglings	9410

1008
 
mixture	9410

1009
 
electroencephalography	9410

1010
 
similarities	9410

1011
 
charges	9410

1012
 
freest	9410

1013
 
Greenberg	9410

1014
 
tinting	9410

1015
 
expelled	9410

1016
 
warm	9410

1017
 
smoothed	9410

1018
 
deductions	9410

1019
 
Romano	9410

1020
 
bitterroot	9410

1021
 
corset	9410

1022
 
securing	9410

1023
 
environing	9410

1024
 
cute	9410

1025
 
Crays	9410

1026
 
heiress	9410

1027
 
inform	9410

1028
 
avenge	9410

1029
 
universals	9410

1030
 
Kinsey	9410

1031
 
ravines	9410

1032
 
bestseller	9410

1033
 
equilibrium	9410

1034
 
extents	9410

1035
 
relatively	9410

1036
 
pressure	9410

1037
 
critiques	9410

1038
 
befouled	9410

1039
 
rightfully	9410

1040
 
mechanizing	9410

1041
 
Latinizes	9410

1042
 
timesharing	9410

1043
 
Aden	9410

1044
 
embassies	9410

1045
 
males	9410

1046
 
shapelessly	9410

1047
 
mastering	9410

1048
 
Newtonian	9410

1049
 
finishers	9410

1050
 
abates	9410

1051
 
teem	9410

1052
 
kiting	9410

1053
 
stodgy	9410

1054
 
feed	9410

1055
 
guitars	9410

1056
 
airships	9410

1057
 
store	9410

1058
 
denounces	9410

1059
 
Pyle	9410

1060
 
Saxony	9410

1061
 
serializations	9410

1062
 
Peruvian	9410

1063
 
taxonomically	9410

1064
 
kingdom	9410

1065
 
stint	9410

1066
 
Sault	9410

1067
 
faithful	9410

1068
 
Ganymede	9410

1069
 
tidiness	9410

1070
 
gainful	9410

1071
 
contrary	9410

1072
 
Tipperary	9410

1073
 
tropics	9410

1074
 
theorizers	9410

1075
 
renew	9410

1076
 
already	9410

1077
 
terminal	9410

1078
 
Hegelian	9410

1079
 
hypothesizer	9410

1080
 
warningly	9410

1081
 
journalizing	9410

1082
 
nested	9410

1083
 
Lars	9410

1084
 
saplings	9410

1085
 
foothill	9410

1086
 
labeled	9410

1087
 
imperiously	9410

1088
 
reporters	9410

1089
 
furnishings	9410

1090
 
precipitable	9410

1091
 
discounts	9410

1092
 
excises	9410

1093
 
Stalin	9410

1094
 
despot	9410

1095
 
ripeness	9410

1096
 
Arabia	9410

1097
 
unruly	9410

1098
 
mournfulness	9410

1099
 
boom	9410

1100
 
slaughter	9410

1101
 
Sabine	9410

1102
 
handy	9410

1103
 
rural	9410

1104
 
organizer	9410

1105
 
shipyard	9410

1106
 
civics	9410

1107
 
inaccuracy	9410

1108
 
rules	9410

1109
 
juveniles	9410

1110
 
comprised	9410

1111
 
investigations	9410

1112
 
stabilizes	9410

1113
 
seminaries	9410

1114
 
Hunter	9410

1115
 
sporty	9410

1116
 
test	9410

1117
 
weasels	9410

1118
 
CERN	9410

1119
 
tempering	9410

1120
 
afore	9410

1121
 
Galatean	9410

1122
 
techniques	9410

1123
 
error	9410

1124
 
veranda	9410

1125
 
severely	9410

1126
 
Cassites	9410

1127
 
forthcoming	9410

1128
 
guides	9410

1129
 
vanish	9410

1130
 
lied	9410

1131
 
sawtooth	9410

1132
 
fated	9410

1133
 
gradually	9410

1134
 
widens	9410

1135
 
preclude	9410

1136
 
evenhandedly	9410

1137
 
percentage	9410

1138
 
disobedience	9410

1139
 
humility	9410

1140
 
gleaning	9410

1141
 
petted	9410

1142
 
bloater	9410

1143
 
minion	9410

1144
 
marginal	9410

1145
 
apiary	9410

1146
 
measures	9410

1147
 
precaution	9410

1148
 
repelled	9410

1149
 
primary	9410

1150
 
coverings	9410

1151
 
Artemia	9410

1152
 
navigate	9410

1153
 
spatial	9410

1154
 
Gurkha	9410

1155
 
meanwhile	9410

1156
 
Melinda	9410

1157
 
Butterfield	9410

1158
 
Aldrich	9410

1159
 
previewing	9410

1160
 
glut	9410

1161
 
unaffected	9410

1162
 
inmate	9410

1163
 
mineral	9410

1164
 
impending	9410

1165
 
meditation	9410

1166
 
ideas	9410

1167
 
miniaturizes	9410

1168
 
lewdly	9410

1169
 
title	9410

1170
 
youthfulness	9410

1171
 
creak	9410

1172
 
Chippewa	9410

1173
 
clamored	9410

1174
 
freezes	9410

1175
 
forgivably	9410

1176
 
reduce	9410

1177
 
McGovern	9410

1178
 
Nazis	9410

1179
 
epistle	9410

1180
 
socializes	9410

1181
 
conceptions	9410

1182
 
Kevin	9410

1183
 
uncovering	9410

1184
 
chews	9410

1185
 
appendixes	9410

1186
 
appendixes	9410

1187
 
appendixes	9410

1188
 
appendixes	9410

1189
 
appendixes	9410

1190
 
appendixes	9410

1191
 
raining	9410

1192
 
infest	9410

1193
 
compartment	9410

1194
 
minting	9410

1195
 
ducks	9410

1196
 
roped	9410

1197
 
waltz	9410

1198
 
Lillian	9410

1199
 
repressions	9410

1200
 
chillingly	9410

1201
 
noncritical	9410

1202
 
lithograph	9410

1203
 
spongers	9410

1204
 
parenthood	9410

1205
 
posed	9410

1206
 
instruments	9410

1207
 
filial	9410

1208
 
fixedly	9410

1209
 
relives	9410

1210
 
Pandora	9410

1211
 
watering	9410

1212
 
ungrateful	9410

1213
 
secures	9410

1214
 
poison	9410

1215
 
dusted	9410

1216
 
encompasses	9410

1217
 
presentation	9410

1218
 
Kantian	9410

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

1220
 
fld3	period	price	price2

1221
 
admonishing	1002	28357832	8723648

1222
 
analyzable	1002	28357832	8723648

1223
 
annihilates	1001	5987435	234724

1224
 
Antares	1002	28357832	8723648

1225
 
astound	1001	5987435	234724

1226
 
audiology	1001	5987435	234724

1227
 
Augustine	1002	28357832	8723648

1228
 
Baird	1002	28357832	8723648

1229
 
bewilderingly	1001	5987435	234724

1230
 
breaking	1001	5987435	234724

1231
 
Conley	1001	5987435	234724

1232
 
dentally	1002	28357832	8723648

1233
 
dissociate	1002	28357832	8723648

1234
 
elite	1001	5987435	234724

1235
 
eschew	1001	5987435	234724

1236
 
Eulerian	1001	5987435	234724

1237
 
flanking	1001	5987435	234724

1238
 
foldout	1002	28357832	8723648

1239
 
funereal	1002	28357832	8723648

1240
 
galling	1002	28357832	8723648

1241
 
Graves	1001	5987435	234724

1242
 
grazing	1001	5987435	234724

1243
 
groupings	1001	5987435	234724

1244
 
handgun	1001	5987435	234724

1245
 
humility	1002	28357832	8723648

1246
 
impulsive	1002	28357832	8723648

1247
 
inch	1001	5987435	234724

1248
 
intelligibility	1001	5987435	234724

1249
 
jarring	1001	5987435	234724

1250
 
lawgiver	1001	5987435	234724

1251
 
lectured	1002	28357832	8723648

1252
 
Merritt	1002	28357832	8723648

1253
 
neonatal	1001	5987435	234724

1254
 
offload	1002	28357832	8723648

1255
 
parters	1002	28357832	8723648

1256
 
pityingly	1002	28357832	8723648

1257
 
puddings	1002	28357832	8723648

1258
 
Punjab	1001	5987435	234724

1259
 
quitter	1002	28357832	8723648

1260
 
realtor	1001	5987435	234724

1261
 
relaxing	1001	5987435	234724

1262
 
repetitions	1001	5987435	234724

1263
 
resumes	1001	5987435	234724

1264
 
Romans	1002	28357832	8723648

1265
 
rusting	1001	5987435	234724

1266
 
scholastics	1001	5987435	234724

1267
 
skulking	1002	28357832	8723648

1268
 
stated	1002	28357832	8723648

1269
 
suites	1002	28357832	8723648

1270
 
sureties	1001	5987435	234724

1271
 
testicle	1002	28357832	8723648

1272
 
tinily	1002	28357832	8723648

1273
 
tragedies	1001	5987435	234724

1274
 
trimmings	1001	5987435	234724

1275
 
vacuuming	1001	5987435	234724

1276
 
ventilate	1001	5987435	234724

1277
 
wallet	1001	5987435	234724

1278
 
Weissmuller	1002	28357832	8723648

1279
 
Wotan	1002	28357832	8723648

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

1281
 
fld1	fld3	period	price	price2

1282
 
018201	relaxing	1001	5987435	234724

1283
 
018601	vacuuming	1001	5987435	234724

1284
 
018801	inch	1001	5987435	234724

1285
 
018811	repetitions	1001	5987435	234724

1286
 
create table t4 (

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

1288
 
companyname char(30) NOT NULL default '',

1289
 
PRIMARY KEY (companynr),

1290
 
UNIQUE KEY companyname(companyname)

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

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

1293
 
companynr	companyname

1294
 
00	Unknown

1295
 
29	company 1

1296
 
34	company 2

1297
 
36	company 3

1298
 
37	company 4

1299
 
40	company 5

1300
 
41	company 6

1301
 
50	company 11

1302
 
53	company 7

1303
 
58	company 8

1304
 
65	company 9

1305
 
68	company 10

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

1307
 
companynr	companyname

1308
 
00	Unknown

1309
 
29	company 1

1310
 
34	company 2

1311
 
36	company 3

1312
 
37	company 4

1313
 
40	company 5

1314
 
41	company 6

1315
 
50	company 11

1316
 
53	company 7

1317
 
58	company 8

1318
 
65	company 9

1319
 
68	company 10

1320
 
select * from t1,t1 t12;

1321
 
Period	Varor_period	Period	Varor_period

1322
 
9410	9412	9410	9412

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

1324
 
fld1	fld1

1325
 
250501	250501

1326
 
250502	250501

1327
 
250503	250501

1328
 
250504	250501

1329
 
250505	250501

1330
 
250501	250502

1331
 
250502	250502

1332
 
250503	250502

1333
 
250504	250502

1334
 
250505	250502

1335
 
250501	250503

1336
 
250502	250503

1337
 
250503	250503

1338
 
250504	250503

1339
 
250505	250503

1340
 
250501	250504

1341
 
250502	250504

1342
 
250503	250504

1343
 
250504	250504

1344
 
250505	250504

1345
 
250501	250505

1346
 
250502	250505

1347
 
250503	250505

1348
 
250504	250505

1349
 
250505	250505

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

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

1352
 
companynr	companyname

1353
 
99	NULL

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

1355
 
count(*)

1356
 
1199

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

1358
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1359
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	

1360
 
1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using where; Not exists

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

1362
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1363
 
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	

1364
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	Using where; Not exists

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

1366
 
companynr	companyname

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

1368
 
count(*)

1369
 
1200

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

1371
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1372
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

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

1374
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1375
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

1376
 
delete from t2 where fld1=999999;

1377
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;

1378
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1379
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where

1380
 
1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	

1381
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;

1382
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1383
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where

1384
 
1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	

1385
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;

1386
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1387
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where

1388
 
1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	

1389
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;

1390
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1391
 
1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where

1392
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	

1393
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;

1394
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1395
 
1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where

1396
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	

1397
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;

1398
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1399
 
1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where

1400
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	

1401
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;

1402
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1403
 
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	

1404
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where

1405
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;

1406
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1407
 
1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	

1408
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where

1409
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;

1410
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1411
 
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	

1412
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where

1413
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;

1414
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1415
 
1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where

1416
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	

1417
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;

1418
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1419
 
1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where

1420
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	

1421
 
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;

1422
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1423
 
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where

1424
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	

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

1426
 
companynr	companynr

1427
 
37	36

1428
 
41	40

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

1430
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1431
 
1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary

1432
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer

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

1434
 
fld1	companynr	fld3	period

1435
 
038008	37	reporters	1008

1436
 
038208	37	Selfridge	1008

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

1438
 
fld1	companynr	fld3	period

1439
 
038008	37	reporters	1008

1440
 
038208	37	Selfridge	1008

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

1442
 
fld1	companynr	fld3	period

1443
 
038008	37	reporters	1008

1444
 
038208	37	Selfridge	1008

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

1446
 
period

1447
 
9410

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

1449
 
period

1450
 
9410

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

1452
 
fld1

1453
 
250501

1454
 
250502

1455
 
250503

1456
 
250505

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

1458
 
fld1

1459
 
250502

1460
 
250503

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

1462
 
fld1

1463
 
250502

1464
 
250503

1465
 
250504

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

1467
 
fld3

1468
 
label

1469
 
labeled

1470
 
labeled

1471
 
landslide

1472
 
laterally

1473
 
leaflet

1474
 
lewdly

1475
 
Lillian

1476
 
luckily

1477
 
select count(*) from t1;

1478
 
count(*)

1479
 
1

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

1481
 
companynr	count(*)	sum(fld1)

1482
 
00	82	10355753

1483
 
29	95	14473298

1484
 
34	70	17788966

1485
 
36	215	22786296

1486
 
37	588	83602098

1487
 
40	37	6618386

1488
 
41	52	12816335

1489
 
50	11	1595438

1490
 
53	4	793210

1491
 
58	23	2254293

1492
 
65	10	2284055

1493
 
68	12	3097288

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

1495
 
companynr	count(*)

1496
 
68	12

1497
 
65	10

1498
 
58	23

1499
 
53	4

1500
 
50	11

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

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

1503
 
70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069

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

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

1506
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	100.00	Using where

1507
 
Warnings:

1508
 
Note	1003	select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))

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

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

1511
 
00	82	Anthony	windmills	10355753	126289.6707	115550.9757	13352027981.7087

1512
 
29	95	abut	wetness	14473298	152350.5053	8368.5480	70032594.9026

1513
 
34	70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069

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

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

1516
 
37	1	1	5987435	5987435	5987435	5987435.0000

1517
 
37	2	1	28357832	28357832	28357832	28357832.0000

1518
 
37	3	1	39654943	39654943	39654943	39654943.0000

1519
 
37	11	1	5987435	5987435	5987435	5987435.0000

1520
 
37	12	1	28357832	28357832	28357832	28357832.0000

1521
 
37	13	1	39654943	39654943	39654943	39654943.0000

1522
 
37	21	1	5987435	5987435	5987435	5987435.0000

1523
 
37	22	1	28357832	28357832	28357832	28357832.0000

1524
 
37	23	1	39654943	39654943	39654943	39654943.0000

1525
 
37	31	1	5987435	5987435	5987435	5987435.0000

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

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

1528
 
37	1	1	5987435	5987435	5987435	5987435.0000

1529
 
37	2	1	28357832	28357832	28357832	28357832.0000

1530
 
37	3	1	39654943	39654943	39654943	39654943.0000

1531
 
37	11	1	5987435	5987435	5987435	5987435.0000

1532
 
37	12	1	28357832	28357832	28357832	28357832.0000

1533
 
37	13	1	39654943	39654943	39654943	39654943.0000

1534
 
37	21	1	5987435	5987435	5987435	5987435.0000

1535
 
37	22	1	28357832	28357832	28357832	28357832.0000

1536
 
37	23	1	39654943	39654943	39654943	39654943.0000

1537
 
37	31	1	5987435	5987435	5987435	5987435.0000

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

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

1540
 
37	12543	309394878010	5987435	39654943	24666736.6667

1541
 
78	8362	414611089292	726498	98439034	49582766.0000

1542
 
101	4181	3489454238	834598	834598	834598.0000

1543
 
154	4181	4112197254950	983543950	983543950	983543950.0000

1544
 
311	4181	979599938	234298	234298	234298.0000

1545
 
447	4181	9929180954	2374834	2374834	2374834.0000

1546
 
512	4181	3288532102	786542	786542	786542.0000

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

1548
 
mod(companynr,10)

1549
 
0

1550
 
9

1551
 
4

1552
 
6

1553
 
7

1554
 
1

1555
 
3

1556
 
8

1557
 
5

1558
 
select distinct 1 from t4 group by companynr;

1559
 
1

1560
 
1

1561
 
select count(distinct fld1) from t2;

1562
 
count(distinct fld1)

1563
 
1199

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

1565
 
companynr	count(distinct fld1)

1566
 
00	82

1567
 
29	95

1568
 
34	70

1569
 
36	215

1570
 
37	588

1571
 
40	37

1572
 
41	52

1573
 
50	11

1574
 
53	4

1575
 
58	23

1576
 
65	10

1577
 
68	12

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

1579
 
companynr	count(*)

1580
 
00	82

1581
 
29	95

1582
 
34	70

1583
 
36	215

1584
 
37	588

1585
 
40	37

1586
 
41	52

1587
 
50	11

1588
 
53	4

1589
 
58	23

1590
 
65	10

1591
 
68	12

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

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

1594
 
00	82

1595
 
29	95

1596
 
34	70

1597
 
36	215

1598
 
37	588

1599
 
40	37

1600
 
41	52

1601
 
50	11

1602
 
53	4

1603
 
58	23

1604
 
65	10

1605
 
68	12

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

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

1608
 
00	82

1609
 
29	95

1610
 
34	70

1611
 
36	215

1612
 
37	588

1613
 
40	37

1614
 
41	52

1615
 
50	11

1616
 
53	4

1617
 
58	23

1618
 
65	10

1619
 
68	12

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

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

1622
 
00	47

1623
 
29	35

1624
 
34	14

1625
 
36	69

1626
 
37	108

1627
 
40	16

1628
 
41	11

1629
 
50	9

1630
 
53	1

1631
 
58	1

1632
 
65	1

1633
 
68	1

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

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

1636
 
00	47

1637
 
29	35

1638
 
34	14

1639
 
36	69

1640
 
37	108

1641
 
40	16

1642
 
41	11

1643
 
50	9

1644
 
53	1

1645
 
58	1

1646
 
65	1

1647
 
68	1

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

1649
 
sum(fld1)	fld3

1650
 
11402	Romans

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

1652
 
name	count(*)

1653
 
cloakroom	4181

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

1655
 
name	count(*)

1656
 
cloakroom	4181

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

1658
 
count(*)

1659
 
4181

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

1661
 
name	count(*)

1662
 
cloakroom	4181

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

1664
 
name	count(*)

1665
 
extramarital	4181

1666
 
gazer	4181

1667
 
gems	4181

1668
 
Iranizes	4181

1669
 
spates	4181

1670
 
tucked	4181

1671
 
violinist	4181

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

1673
 
fld3	count(*)

1674
 
spates	4181

1675
 
select companynr|0,companyname from t4 group by 1;

1676
 
companynr|0	companyname

1677
 
0	Unknown

1678
 
29	company 1

1679
 
34	company 2

1680
 
36	company 3

1681
 
37	company 4

1682
 
40	company 5

1683
 
41	company 6

1684
 
50	company 11

1685
 
53	company 7

1686
 
58	company 8

1687
 
65	company 9

1688
 
68	company 10

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

1690
 
companynr	companyname	count(*)

1691
 
29	company 1	95

1692
 
68	company 10	12

1693
 
50	company 11	11

1694
 
34	company 2	70

1695
 
36	company 3	215

1696
 
37	company 4	588

1697
 
40	company 5	37

1698
 
41	company 6	52

1699
 
53	company 7	4

1700
 
58	company 8	23

1701
 
65	company 9	10

1702
 
00	Unknown	82

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

1704
 
fld1	count(*)

1705
 
158402	4181

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

1707
 
sum(Period)/count(*)

1708
 
9410.0000

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

1710
 
companynr	count	sum	diff	func

1711
 
37	12543	309394878010	0.0000	464091

1712
 
78	8362	414611089292	0.0000	652236

1713
 
101	4181	3489454238	0.0000	422281

1714
 
154	4181	4112197254950	0.0000	643874

1715
 
311	4181	979599938	0.0000	1300291

1716
 
447	4181	9929180954	0.0000	1868907

1717
 
512	4181	3288532102	0.0000	2140672

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

1719
 
companynr	avg

1720
 
154	983543950.0000

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

1722
 
companynr	count(*)

1723
 
37	588

1724
 
36	215

1725
 
29	95

1726
 
00	82

1727
 
34	70

1728
 
41	52

1729
 
40	37

1730
 
58	23

1731
 
68	12

1732
 
50	11

1733
 
65	10

1734
 
53	4

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

1736
 
companynr	count(*)

1737
 
41	52

1738
 
58	23

1739
 
68	12

1740
 
50	11

1741
 
65	10

1742
 
53	4

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

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

1745
 
teethe	000001	1	5987435	5987435	5987435	5987435.0000

1746
 
dreaded	011401	1	5987435	5987435	5987435	5987435.0000

1747
 
scholastics	011402	1	28357832	28357832	28357832	28357832.0000

1748
 
audiology	011403	1	39654943	39654943	39654943	39654943.0000

1749
 
wallet	011501	1	5987435	5987435	5987435	5987435.0000

1750
 
parters	011701	1	5987435	5987435	5987435	5987435.0000

1751
 
eschew	011702	1	28357832	28357832	28357832	28357832.0000

1752
 
quitter	011703	1	39654943	39654943	39654943	39654943.0000

1753
 
neat	012001	1	5987435	5987435	5987435	5987435.0000

1754
 
Steinberg	012003	1	39654943	39654943	39654943	39654943.0000

1755
 
balled	012301	1	5987435	5987435	5987435	5987435.0000

1756
 
persist	012302	1	28357832	28357832	28357832	28357832.0000

1757
 
attainments	012303	1	39654943	39654943	39654943	39654943.0000

1758
 
capably	012501	1	5987435	5987435	5987435	5987435.0000

1759
 
impulsive	012602	1	28357832	28357832	28357832	28357832.0000

1760
 
starlet	012603	1	39654943	39654943	39654943	39654943.0000

1761
 
featherweight	012701	1	5987435	5987435	5987435	5987435.0000

1762
 
pessimist	012702	1	28357832	28357832	28357832	28357832.0000

1763
 
daughter	012703	1	39654943	39654943	39654943	39654943.0000

1764
 
lawgiver	013601	1	5987435	5987435	5987435	5987435.0000

1765
 
stated	013602	1	28357832	28357832	28357832	28357832.0000

1766
 
readable	013603	1	39654943	39654943	39654943	39654943.0000

1767
 
testicle	013801	1	5987435	5987435	5987435	5987435.0000

1768
 
Parsifal	013802	1	28357832	28357832	28357832	28357832.0000

1769
 
leavings	013803	1	39654943	39654943	39654943	39654943.0000

1770
 
squeaking	013901	1	5987435	5987435	5987435	5987435.0000

1771
 
contrasted	016001	1	5987435	5987435	5987435	5987435.0000

1772
 
leftover	016201	1	5987435	5987435	5987435	5987435.0000

1773
 
whiteners	016202	1	28357832	28357832	28357832	28357832.0000

1774
 
erases	016301	1	5987435	5987435	5987435	5987435.0000

1775
 
Punjab	016302	1	28357832	28357832	28357832	28357832.0000

1776
 
Merritt	016303	1	39654943	39654943	39654943	39654943.0000

1777
 
sweetish	018001	1	5987435	5987435	5987435	5987435.0000

1778
 
dogging	018002	1	28357832	28357832	28357832	28357832.0000

1779
 
scornfully	018003	1	39654943	39654943	39654943	39654943.0000

1780
 
fetters	018012	1	28357832	28357832	28357832	28357832.0000

1781
 
bivalves	018013	1	39654943	39654943	39654943	39654943.0000

1782
 
skulking	018021	1	5987435	5987435	5987435	5987435.0000

1783
 
flint	018022	1	28357832	28357832	28357832	28357832.0000

1784
 
flopping	018023	1	39654943	39654943	39654943	39654943.0000

1785
 
Judas	018032	1	28357832	28357832	28357832	28357832.0000

1786
 
vacuuming	018033	1	39654943	39654943	39654943	39654943.0000

1787
 
medical	018041	1	5987435	5987435	5987435	5987435.0000

1788
 
bloodbath	018042	1	28357832	28357832	28357832	28357832.0000

1789
 
subschema	018043	1	39654943	39654943	39654943	39654943.0000

1790
 
interdependent	018051	1	5987435	5987435	5987435	5987435.0000

1791
 
Graves	018052	1	28357832	28357832	28357832	28357832.0000

1792
 
neonatal	018053	1	39654943	39654943	39654943	39654943.0000

1793
 
sorters	018061	1	5987435	5987435	5987435	5987435.0000

1794
 
epistle	018062	1	28357832	28357832	28357832	28357832.0000

1795
 
Conley	018101	1	5987435	5987435	5987435	5987435.0000

1796
 
lectured	018102	1	28357832	28357832	28357832	28357832.0000

1797
 
Abraham	018103	1	39654943	39654943	39654943	39654943.0000

1798
 
cage	018201	1	5987435	5987435	5987435	5987435.0000

1799
 
hushes	018202	1	28357832	28357832	28357832	28357832.0000

1800
 
Simla	018402	1	28357832	28357832	28357832	28357832.0000

1801
 
reporters	018403	1	39654943	39654943	39654943	39654943.0000

1802
 
coexist	018601	1	5987435	5987435	5987435	5987435.0000

1803
 
Beebe	018602	1	28357832	28357832	28357832	28357832.0000

1804
 
Taoism	018603	1	39654943	39654943	39654943	39654943.0000

1805
 
Connally	018801	1	5987435	5987435	5987435	5987435.0000

1806
 
fetched	018802	1	28357832	28357832	28357832	28357832.0000

1807
 
checkpoints	018803	1	39654943	39654943	39654943	39654943.0000

1808
 
gritty	018811	1	5987435	5987435	5987435	5987435.0000

1809
 
firearm	018812	1	28357832	28357832	28357832	28357832.0000

1810
 
minima	019101	1	5987435	5987435	5987435	5987435.0000

1811
 
Selfridge	019102	1	28357832	28357832	28357832	28357832.0000

1812
 
disable	019103	1	39654943	39654943	39654943	39654943.0000

1813
 
witchcraft	019201	1	5987435	5987435	5987435	5987435.0000

1814
 
betroth	030501	1	5987435	5987435	5987435	5987435.0000

1815
 
Manhattanize	030502	1	28357832	28357832	28357832	28357832.0000

1816
 
imprint	030503	1	39654943	39654943	39654943	39654943.0000

1817
 
swelling	031901	1	5987435	5987435	5987435	5987435.0000

1818
 
interrelationships	036001	1	5987435	5987435	5987435	5987435.0000

1819
 
riser	036002	1	28357832	28357832	28357832	28357832.0000

1820
 
bee	038001	1	5987435	5987435	5987435	5987435.0000

1821
 
kanji	038002	1	28357832	28357832	28357832	28357832.0000

1822
 
dental	038003	1	39654943	39654943	39654943	39654943.0000

1823
 
railway	038011	1	5987435	5987435	5987435	5987435.0000

1824
 
validate	038012	1	28357832	28357832	28357832	28357832.0000

1825
 
normalizes	038013	1	39654943	39654943	39654943	39654943.0000

1826
 
Kline	038101	1	5987435	5987435	5987435	5987435.0000

1827
 
Anatole	038102	1	28357832	28357832	28357832	28357832.0000

1828
 
partridges	038103	1	39654943	39654943	39654943	39654943.0000

1829
 
recruited	038201	1	5987435	5987435	5987435	5987435.0000

1830
 
dimensions	038202	1	28357832	28357832	28357832	28357832.0000

1831
 
Chicana	038203	1	39654943	39654943	39654943	39654943.0000

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

1833
 
companynr	fld3	sum(price)

1834
 
512	boat	786542

1835
 
512	capably	786542

1836
 
512	cupboard	786542

1837
 
512	decliner	786542

1838
 
512	descendants	786542

1839
 
512	dopers	786542

1840
 
512	erases	786542

1841
 
512	Micronesia	786542

1842
 
512	Miles	786542

1843
 
512	skies	786542

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

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

1846
 
00	1	Omaha	Omaha	5987435	5987435.0000

1847
 
36	1	dubbed	dubbed	28357832	28357832.0000

1848
 
37	83	Abraham	Wotan	1908978016	22999735.1325

1849
 
50	2	scribbled	tapestry	68012775	34006387.5000

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

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

1852
 
37	1	Omaha	5987435

1853
 
37	11401	breaking	5987435

1854
 
37	11402	Romans	28357832

1855
 
37	11403	intercepted	39654943

1856
 
37	11501	bewilderingly	5987435

1857
 
37	11701	astound	5987435

1858
 
37	11702	admonishing	28357832

1859
 
37	11703	sumac	39654943

1860
 
37	12001	flanking	5987435

1861
 
37	12003	combed	39654943

1862
 
37	12301	Eulerian	5987435

1863
 
37	12302	dubbed	28357832

1864
 
37	12303	Kane	39654943

1865
 
37	12501	annihilates	5987435

1866
 
37	12602	Wotan	28357832

1867
 
37	12603	snatching	39654943

1868
 
37	12701	grazing	5987435

1869
 
37	12702	Baird	28357832

1870
 
37	12703	celery	39654943

1871
 
37	13601	handgun	5987435

1872
 
37	13602	foldout	28357832

1873
 
37	13603	mystic	39654943

1874
 
37	13801	intelligibility	5987435

1875
 
37	13802	Augustine	28357832

1876
 
37	13803	teethe	39654943

1877
 
37	13901	scholastics	5987435

1878
 
37	16001	audiology	5987435

1879
 
37	16201	wallet	5987435

1880
 
37	16202	parters	28357832

1881
 
37	16301	eschew	5987435

1882
 
37	16302	quitter	28357832

1883
 
37	16303	neat	39654943

1884
 
37	18001	jarring	5987435

1885
 
37	18002	tinily	28357832

1886
 
37	18003	balled	39654943

1887
 
37	18012	impulsive	28357832

1888
 
37	18013	starlet	39654943

1889
 
37	18021	lawgiver	5987435

1890
 
37	18022	stated	28357832

1891
 
37	18023	readable	39654943

1892
 
37	18032	testicle	28357832

1893
 
37	18033	Parsifal	39654943

1894
 
37	18041	Punjab	5987435

1895
 
37	18042	Merritt	28357832

1896
 
37	18043	Quixotism	39654943

1897
 
37	18051	sureties	5987435

1898
 
37	18052	puddings	28357832

1899
 
37	18053	tapestry	39654943

1900
 
37	18061	trimmings	5987435

1901
 
37	18062	humility	28357832

1902
 
37	18101	tragedies	5987435

1903
 
37	18102	skulking	28357832

1904
 
37	18103	flint	39654943

1905
 
37	18201	relaxing	5987435

1906
 
37	18202	offload	28357832

1907
 
37	18402	suites	28357832

1908
 
37	18403	lists	39654943

1909
 
37	18601	vacuuming	5987435

1910
 
37	18602	dentally	28357832

1911
 
37	18603	humanness	39654943

1912
 
37	18801	inch	5987435

1913
 
37	18802	Weissmuller	28357832

1914
 
37	18803	irresponsibly	39654943

1915
 
37	18811	repetitions	5987435

1916
 
37	18812	Antares	28357832

1917
 
37	19101	ventilate	5987435

1918
 
37	19102	pityingly	28357832

1919
 
37	19103	interdependent	39654943

1920
 
37	19201	Graves	5987435

1921
 
37	30501	neonatal	5987435

1922
 
37	30502	scribbled	28357832

1923
 
37	30503	chafe	39654943

1924
 
37	31901	realtor	5987435

1925
 
37	36001	elite	5987435

1926
 
37	36002	funereal	28357832

1927
 
37	38001	Conley	5987435

1928
 
37	38002	lectured	28357832

1929
 
37	38003	Abraham	39654943

1930
 
37	38011	groupings	5987435

1931
 
37	38012	dissociate	28357832

1932
 
37	38013	coexist	39654943

1933
 
37	38101	rusting	5987435

1934
 
37	38102	galling	28357832

1935
 
37	38103	obliterates	39654943

1936
 
37	38201	resumes	5987435

1937
 
37	38202	analyzable	28357832

1938
 
37	38203	terminator	39654943

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

1940
 
sum(price)

1941
 
234298

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

1943
 
fld1	sum(price)

1944
 
038008	234298

1945
 
explain select fld3 from t2 where 1>2 or 2>3;

1946
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1947
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

1948
 
explain select fld3 from t2 where fld1=fld1;

1949
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1950
 
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	

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

1952
 
companynr	fld1

1953
 
34	250501

1954
 
34	250502

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

1956
 
companynr	fld1

1957
 
34	250501

1958
 
34	250502

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

1960
 
companynr	count	sum

1961
 
00	82	10355753

1962
 
29	95	14473298

1963
 
34	70	17788966

1964
 
37	588	83602098

1965
 
41	52	12816335

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

1967
 
companynr

1968
 
00

1969
 
29

1970
 
34

1971
 
37

1972
 
41

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

1974
 
companynr	companyname	count(*)

1975
 
68	company 10	12

1976
 
50	company 11	11

1977
 
40	company 5	37

1978
 
41	company 6	52

1979
 
53	company 7	4

1980
 
58	company 8	23

1981
 
65	company 9	10

1982
 
select count(*) from t2;

1983
 
count(*)

1984
 
1199

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

1986
 
count(*)

1987
 
387

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

1989
 
min(fld1)

1990
 
98024

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

1992
 
max(fld1)

1993
 
1232609

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

1995
 
count(*)

1996
 
4181

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

1998
 
count(*)

1999
 
4181

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

2001
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2002
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away

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

2004
 
min(fld1)	max(fld1)	count(*)

2005
 
0	1232609	1199

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

2007
 
min(t2nr)	max(t2nr)

2008
 
2115	2115

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

2010
 
count(*)	min(t2nr)	max(t2nr)

2011
 
4181	4	41804

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

2013
 
t2nr	count(*)

2014
 
9	1

2015
 
19	1

2016
 
29	1

2017
 
39	1

2018
 
49	1

2019
 
59	1

2020
 
69	1

2021
 
79	1

2022
 
89	1

2023
 
99	1

2024
 
109	1

2025
 
119	1

2026
 
129	1

2027
 
139	1

2028
 
149	1

2029
 
159	1

2030
 
169	1

2031
 
179	1

2032
 
189	1

2033
 
199	1

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

2035
 
max(t2nr)

2036
 
41807

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

2038
 
period

2039
 
1001

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

2041
 
period

2042
 
9410

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

2044
 
Nuvarande period

2045
 
9410

2046
 
select period as ok_period from t1 limit 1;

2047
 
ok_period

2048
 
9410

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

2050
 
ok_period

2051
 
9410

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

2053
 
summa

2054
 
2

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

2056
 
Nuvarande period

2057
 
9410

2058
 
show tables;

2059
 
Tables_in_test

2060
 
t1

2061
 
t2

2062
 
t3

2063
 
t4

2064
 
show tables from test like "s%";

2065
 
Tables_in_test (s%)

2066
 
show tables from test like "t?";

2067
 
Tables_in_test (t?)

2068
 
show full columns from t2;

2069
 
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment

2070
 
auto	int(11)	NULL	NO	PRI	NULL	auto_increment	#	

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

2072
 
companynr	tinyint(2) unsigned zerofill	NULL	NO		00		#	

2073
 
fld3	char(30)	latin1_swedish_ci	NO	MUL			#	

2074
 
fld4	char(35)	latin1_swedish_ci	NO				#	

2075
 
fld5	char(35)	latin1_swedish_ci	NO				#	

2076
 
fld6	char(4)	latin1_swedish_ci	NO				#	

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

2078
 
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment

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

2080
 
fld3	char(30)	latin1_swedish_ci	NO	MUL			#	

2081
 
fld4	char(35)	latin1_swedish_ci	NO				#	

2082
 
fld5	char(35)	latin1_swedish_ci	NO				#	

2083
 
fld6	char(4)	latin1_swedish_ci	NO				#	

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

2085
 
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment

2086
 
show keys from t2;

2087
 
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_Comment

2088
 
t2	0	PRIMARY	1	auto	A	1199	NULL	NULL		BTREE		

2089
 
t2	0	fld1	1	fld1	A	1199	NULL	NULL		BTREE		

2090
 
t2	1	fld3	1	fld3	A	NULL	NULL	NULL		BTREE		

2091
 
drop table t4, t3, t2, t1;

2092
 
CREATE TABLE t1 (

2093
 
cont_nr int(11) NOT NULL auto_increment,

2094
 
ver_nr int(11) NOT NULL default '0',

2095
 
aufnr int(11) NOT NULL default '0',

2096
 
username varchar(50) NOT NULL default '',

2097
 
hdl_nr int(11) NOT NULL default '0',

2098
 
eintrag date NOT NULL default '0000-00-00',

2099
 
st_klasse varchar(40) NOT NULL default '',

2100
 
st_wert varchar(40) NOT NULL default '',

2101
 
st_zusatz varchar(40) NOT NULL default '',

2102
 
st_bemerkung varchar(255) NOT NULL default '',

2103
 
kunden_art varchar(40) NOT NULL default '',

2104
 
mcbs_knr int(11) default NULL,

2105
 
mcbs_aufnr int(11) NOT NULL default '0',

2106
 
schufa_status char(1) default '?',

2107
 
bemerkung text,

2108
 
wirknetz text,

2109
 
wf_igz int(11) NOT NULL default '0',

2110
 
tarifcode varchar(80) default NULL,

2111
 
recycle char(1) default NULL,

2112
 
sim varchar(30) default NULL,

2113
 
mcbs_tpl varchar(30) default NULL,

2114
 
emp_nr int(11) NOT NULL default '0',

2115
 
laufzeit int(11) default NULL,

2116
 
hdl_name varchar(30) default NULL,

2117
 
prov_hdl_nr int(11) NOT NULL default '0',

2118
 
auto_wirknetz varchar(50) default NULL,

2119
 
auto_billing varchar(50) default NULL,

2120
 
touch timestamp NOT NULL,

2121
 
kategorie varchar(50) default NULL,

2122
 
kundentyp varchar(20) NOT NULL default '',

2123
 
sammel_rech_msisdn varchar(30) NOT NULL default '',

2124
 
p_nr varchar(9) NOT NULL default '',

2125
 
suffix char(3) NOT NULL default '',

2126
 
PRIMARY KEY (cont_nr),

2127
 
KEY idx_aufnr(aufnr),

2128
 
KEY idx_hdl_nr(hdl_nr),

2129
 
KEY idx_st_klasse(st_klasse),

2130
 
KEY ver_nr(ver_nr),

2131
 
KEY eintrag_idx(eintrag),

2132
 
KEY emp_nr_idx(emp_nr),

2133
 
KEY wf_igz(wf_igz),

2134
 
KEY touch(touch),

2135
 
KEY hdl_tag(eintrag,hdl_nr),

2136
 
KEY prov_hdl_nr(prov_hdl_nr),

2137
 
KEY mcbs_aufnr(mcbs_aufnr),

2138
 
KEY kundentyp(kundentyp),

2139
 
KEY p_nr(p_nr,suffix)

2140
 
) ENGINE=MyISAM;

2141
 
INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');

2142
 
INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');

2143
 
INSERT INTO t1 VALUES (3359358,407,3359358,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1501358,2137473,'N','','N',1909159,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');

2144
 
INSERT INTO t1 VALUES (3359359,468,3359359,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1507831,2143894,'+','','P',1909162,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');

2145
 
INSERT INTO t1 VALUES (3359360,0,0,'Mustermann Musterfrau',29674907,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1900169997,2414578,'+',NULL,'N',1909148,'',NULL,NULL,'RV99066_2',20,NULL,'POS',29674907,NULL,NULL,20010202105916,'Mobilfunk','','','97317481','007');

2146
 
INSERT INTO t1 VALUES (3359361,406,3359361,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag storniert','','(7001-84):Storno, Kd. möchte nicht mehr','privat',NULL,0,'+','','P',1909150,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');

2147
 
INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1509984,2145874,'+','','P',1909154,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');

2148
 
SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie;

2149
 
Kundentyp	kategorie

2150
 
Privat (Private Nutzung)	Mobilfunk

2151
 
Warnings:

2152
 
Warning	1052	Column 'kundentyp' in group statement is ambiguous

2153
 
drop table t1;

2154
 
mysqld is alive

2155
 
End of 5.0 tests.