~drizzle-trunk/drizzle/development

insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');

292

294

explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;

293

295

id select_type table type possible_keys key key_len ref rows Extra

294

1 SIMPLE t1 index a a 52 NULL 11 Using where; Using index

296

1 SIMPLE t1 index a a 22 NULL 11 Using where; Using index

295

297

select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;

296

298

a b c

297

299

1 NULL b

298

300

explain select * from t1 where a >= 1 and a < 3 order by a desc;

299

301

id select_type table type possible_keys key key_len ref rows Extra

300

1 SIMPLE t1 range a a 4 NULL 5 Using where; Using index

302

1 SIMPLE t1 range a a 4 NULL 10 Using where; Using index

301

303

select * from t1 where a >= 1 and a < 3 order by a desc;

302

304

a b c

303

305

2 3 c

331

333

1 NULL b

332

334

explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc;

333

335

id select_type table type possible_keys key key_len ref rows Extra

334

1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index

336

1 SIMPLE t1 range a a 9 NULL 8 Using where; Using index

335

337

explain select * from t1 where a = 2 and b >0 order by a desc,b desc;

336

338

id select_type table type possible_keys key key_len ref rows Extra

337

339

1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index

347

349

1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index

348

350

explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;

349

351

id select_type table type possible_keys key key_len ref rows Extra

350

1 SIMPLE t1 range a a 9 NULL 1 Using where; Using index

352

1 SIMPLE t1 range a a 9 NULL 2 Using where; Using index

351

353

explain select * from t1 where a = 1 order by b desc;

352

354

id select_type table type possible_keys key key_len ref rows Extra

353

355

1 SIMPLE t1 ref a a 4 const 5 Using where; Using index

359

361

1 1 NULL

360

362

1 NULL b

361

363

1 NULL NULL

362

delete from t1 WHERE b IS NULL OR c IS NULL;

363

alter table t1 modify b bigint not null, modify c varchar(100) not null;

364

alter table t1 modify b int not null, modify c varchar(10) not null;

365

Warnings:

366

Warning 1265 Data truncated for column 'b' at row 1

367

Warning 1265 Data truncated for column 'c' at row 1

368

Warning 1265 Data truncated for column 'b' at row 2

369

Warning 1265 Data truncated for column 'c' at row 3

364

370

explain select * from t1 order by a, b, c;

365

371

id select_type table type possible_keys key key_len ref rows Extra

366

1 SIMPLE t1 index NULL a 414 NULL 8 Using index

372

1 SIMPLE t1 index NULL a 20 NULL 11 Using index

367

373

select * from t1 order by a, b, c;

368

374

a b c

375

1 0

376

1 0 b

377

1 1

369

378

1 1 b

370

379

1 1 b

371

380

1 3 b

376

385

2 3 c

377

386

explain select * from t1 order by a desc, b desc, c desc;

378

387

id select_type table type possible_keys key key_len ref rows Extra

379

1 SIMPLE t1 index NULL a 414 NULL 8 Using index

388

1 SIMPLE t1 index NULL a 20 NULL 11 Using index

380

389

select * from t1 order by a desc, b desc, c desc;

381

390

a b c

382

391

2 3 c

387

396

1 3 b

388

397

1 1 b

389

398

1 1 b

399

1 1

400

1 0 b

401

1 0

390

402

explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;

391

403

id select_type table type possible_keys key key_len ref rows Extra

392

1 SIMPLE t1 range a a 414 NULL 3 Using where; Using index

404

1 SIMPLE t1 range a a 20 NULL 3 Using where; Using index

393

405

select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;

394

406

a b c

395

407

1 1 b

396

408

1 1 b

397

409

explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc;

398

410

id select_type table type possible_keys key key_len ref rows Extra

399

1 SIMPLE t1 range a a 4 NULL 3 Using where; Using index

411

1 SIMPLE t1 range a a 4 NULL 6 Using where; Using index

400

412

select * from t1 where a < 2 and b <= 1 order by a desc, b desc;

401

413

a b c

402

414

1 1 b

403

415

1 1 b

416

1 1

417

1 0 b

418

1 0

404

419

select count(*) from t1 where a < 5 and b > 0;

405

420

count(*)

406

421

407

422

select * from t1 where a < 5 and b > 0 order by a desc,b desc;

408

423

a b c

409

424

2 3 c

414

429

1 3 b

415

430

1 1 b

416

431

1 1 b

432

1 1

417

433

explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;

418

434

id select_type table type possible_keys key key_len ref rows Extra

419

1 SIMPLE t1 range a a 12 NULL 4 Using where; Using index

435

1 SIMPLE t1 range a a 8 NULL 10 Using where; Using index

420

436

select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;

421

437

a b c

422

438

2 1 b

423

439

2 1 a

424

440

1 1 b

425

441

1 1 b

442

1 1

443

1 0 b

444

1 0

426

445

explain select * from t1 where a between 0 and 1 order by a desc, b desc;

427

446

id select_type table type possible_keys key key_len ref rows Extra

428

1 SIMPLE t1 range a a 4 NULL 3 Using where; Using index

447

1 SIMPLE t1 range a a 4 NULL 5 Using where; Using index

429

448

select * from t1 where a between 0 and 1 order by a desc, b desc;

430

449

a b c

431

450

1 3 b

432

451

1 1 b

433

452

1 1 b

453

1 1

454

1 0 b

455

1 0

434

456

drop table t1;

435

457

CREATE TABLE t1 (

436

gid int NOT NULL auto_increment,

437

cid int NOT NULL default '0',

458

gid int(10) unsigned NOT NULL auto_increment,

459

cid smallint(5) unsigned NOT NULL default '0',

438

460

PRIMARY KEY (gid),

439

461

KEY component_id (cid)

440

462

) ENGINE=MyISAM;

441

463

INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108);

442

ALTER TABLE t1 add skr int not null;

464

ALTER TABLE t1 add skr int(10) not null;

443

465

CREATE TABLE t2 (

444

gid int NOT NULL default '0',

445

uid int NOT NULL default '1',

446

sid int NOT NULL default '1',

466

gid int(10) unsigned NOT NULL default '0',

467

uid smallint(5) unsigned NOT NULL default '1',

468

sid tinyint(3) unsigned NOT NULL default '1',

447

469

PRIMARY KEY (gid),

448

470

KEY uid (uid),

449

471

KEY status_id (sid)

450

472

) ENGINE=MyISAM;

451

473

INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5);

452

474

CREATE TABLE t3 (

453

uid int NOT NULL auto_increment,

475

uid smallint(6) NOT NULL auto_increment,

454

476

PRIMARY KEY (uid)

455

477

) ENGINE=MyISAM;

456

478

INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250);

457

ALTER TABLE t3 add skr int not null;

479

ALTER TABLE t3 add skr int(10) not null;

458

480

select t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;

459

481

gid sid uid

460

482

104620 5 15

471

493

104619 5 75

472

494

104505 5 117

473

495

103853 5 250

474

select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;

475

gid sid uid

476

103853 5 250

477

103867 5 27

478

103962 5 27

479

104505 5 117

480

104619 5 75

481

104620 5 15

482

SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;

483

gid uid

484

SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;

485

gid sid uid

486

104620 5 15

487

103867 5 27

488

103962 5 27

489

104619 5 75

490

104505 5 117

491

103853 5 250

492

SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;

493

gid uid

494

SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;

495

gid uid

496

EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;

497

id select_type table type possible_keys key key_len ref rows Extra

498

1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort

499

1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index

500

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index

501

EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;

502

id select_type table type possible_keys key key_len ref rows Extra

503

1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort

504

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index

505

EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;

506

id select_type table type possible_keys key key_len ref rows Extra

507

1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort

508

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index

509

1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index

510

EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;

511

id select_type table type possible_keys key key_len ref rows Extra

512

1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort

513

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index

514

EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;

515

id select_type table type possible_keys key key_len ref rows Extra

516

1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort

517

1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition

496

518

drop table t1,t2,t3;

497

519

CREATE TABLE t1 (

498

520

`titre` char(80) NOT NULL default '',

499

`numeropost` int NOT NULL auto_increment,

521

`numeropost` mediumint(8) unsigned NOT NULL auto_increment,

500

522

`date` datetime NOT NULL default '0000-00-00 00:00:00',

501

523

`auteur` char(35) NOT NULL default '',

502

`icone` int NOT NULL default '0',

524

`icone` tinyint(2) unsigned NOT NULL default '0',

503

525

`lastauteur` char(35) NOT NULL default '',

504

`nbrep` int NOT NULL default '0',

526

`nbrep` smallint(6) unsigned NOT NULL default '0',

505

527

`dest` char(35) NOT NULL default '',

506

`lu` int NOT NULL default '0',

507

`vue` int NOT NULL default '0',

508

`ludest` int NOT NULL default '0',

509

`ouvert` int NOT NULL default '1',

528

`lu` tinyint(1) unsigned NOT NULL default '0',

529

`vue` mediumint(8) unsigned NOT NULL default '0',

530

`ludest` tinyint(1) unsigned NOT NULL default '0',

531

`ouvert` tinyint(1) unsigned NOT NULL default '1',

510

532

PRIMARY KEY (`numeropost`),

511

533

KEY `date` (`date`),

512

534

KEY `dest` (`dest`,`ludest`),

515

537

KEY `dest_2` (`dest`,`date`)

516

538

) CHECKSUM=1;

517

539

CREATE TABLE t2 (

518

`numeropost` int NOT NULL default '0',

540

`numeropost` mediumint(8) unsigned NOT NULL default '0',

519

541

`pseudo` char(35) NOT NULL default '',

520

542

PRIMARY KEY (`numeropost`,`pseudo`),

521

543

KEY `pseudo` (`pseudo`)

561

583

5 NULL

562

584

DROP TABLE t1;

563

585

create table t1(id int not null auto_increment primary key, t char(12));

564

select id,t from t1 force index (primary) order by id;

565

id t

566

1 1000

567

2 999

568

3 998

569

4 997

570

5 996

571

6 995

572

7 994

573

8 993

574

9 992

575

10 991

576

11 990

577

12 989

578

13 988

579

14 987

580

15 986

581

16 985

582

17 984

583

18 983

584

19 982

585

20 981

586

21 980

587

22 979

588

23 978

589

24 977

590

25 976

591

26 975

592

27 974

593

28 973

594

29 972

595

30 971

596

31 970

597

32 969

598

33 968

599

34 967

600

35 966

601

36 965

602

37 964

603

38 963

604

39 962

605

40 961

606

41 960

607

42 959

608

43 958

609

44 957

610

45 956

611

46 955

612

47 954

613

48 953

614

49 952

615

50 951

616

51 950

617

52 949

618

53 948

619

54 947

620

55 946

621

56 945

622

57 944

623

58 943

624

59 942

625

60 941

626

61 940

627

62 939

628

63 938

629

64 937

630

65 936

631

66 935

632

67 934

633

68 933

634

69 932

635

70 931

636

71 930

637

72 929

638

73 928

639

74 927

640

75 926

641

76 925

642

77 924

643

78 923

644

79 922

645

80 921

646

81 920

647

82 919

648

83 918

649

84 917

650

85 916

651

86 915

652

87 914

653

88 913

654

89 912

655

90 911

656

91 910

657

92 909

658

93 908

659

94 907

660

95 906

661

96 905

662

97 904

663

98 903

664

99 902

665

100 901

666

101 900

667

102 899

668

103 898

669

104 897

670

105 896

671

106 895

672

107 894

673

108 893

674

109 892

675

110 891

676

111 890

677

112 889

678

113 888

679

114 887

680

115 886

681

116 885

682

117 884

683

118 883

684

119 882

685

120 881

686

121 880

687

122 879

688

123 878

689

124 877

690

125 876

691

126 875

692

127 874

693

128 873

694

129 872

695

130 871

696

131 870

697

132 869

698

133 868

699

134 867

700

135 866

701

136 865

702

137 864

703

138 863

704

139 862

705

140 861

706

141 860

707

142 859

708

143 858

709

144 857

710

145 856

711

146 855

712

147 854

713

148 853

714

149 852

715

150 851

716

151 850

717

152 849

718

153 848

719

154 847

720

155 846

721

156 845

722

157 844

723

158 843

724

159 842

725

160 841

726

161 840

727

162 839

728

163 838

729

164 837

730

165 836

731

166 835

732

167 834

733

168 833

734

169 832

735

170 831

736

171 830

737

172 829

738

173 828

739

174 827

740

175 826

741

176 825

742

177 824

743

178 823

744

179 822

745

180 821

746

181 820

747

182 819

748

183 818

749

184 817

750

185 816

751

186 815

752

187 814

753

188 813

754

189 812

755

190 811

756

191 810

757

192 809

758

193 808

759

194 807

760

195 806

761

196 805

762

197 804

763

198 803

764

199 802

765

200 801

766

201 800

767

202 799

768

203 798

769

204 797

770

205 796

771

206 795

772

207 794

773

208 793

774

209 792

775

210 791

776

211 790

777

212 789

778

213 788

779

214 787

780

215 786

781

216 785

782

217 784

783

218 783

784

219 782

785

220 781

786

221 780

787

222 779

788

223 778

789

224 777

790

225 776

791

226 775

792

227 774

793

228 773

794

229 772

795

230 771

796

231 770

797

232 769

798

233 768

799

234 767

800

235 766

801

236 765

802

237 764

803

238 763

804

239 762

805

240 761

806

241 760

807

242 759

808

243 758

809

244 757

810

245 756

811

246 755

812

247 754

813

248 753

814

249 752

815

250 751

816

251 750

817

252 749

818

253 748

819

254 747

820

255 746

821

256 745

822

257 744

823

258 743

824

259 742

825

260 741

826

261 740

827

262 739

828

263 738

829

264 737

830

265 736

831

266 735

832

267 734

833

268 733

834

269 732

835

270 731

836

271 730

837

272 729

838

273 728

839

274 727

840

275 726

841

276 725

842

277 724

843

278 723

844

279 722

845

280 721

846

281 720

847

282 719

848

283 718

849

284 717

850

285 716

851

286 715

852

287 714

853

288 713

854

289 712

855

290 711

856

291 710

857

292 709

858

293 708

859

294 707

860

295 706

861

296 705

862

297 704

863

298 703

864

299 702

865

300 701

866

301 700

867

302 699

868

303 698

869

304 697

870

305 696

871

306 695

872

307 694

873

308 693

874

309 692

875

310 691

876

311 690

877

312 689

878

313 688

879

314 687

880

315 686

881

316 685

882

317 684

883

318 683

884

319 682

885

320 681

886

321 680

887

322 679

888

323 678

889

324 677

890

325 676

891

326 675

892

327 674

893

328 673

894

329 672

895

330 671

896

331 670

897

332 669

898

333 668

899

334 667

900

335 666

901

336 665

902

337 664

903

338 663

904

339 662

905

340 661

906

341 660

907

342 659

908

343 658

909

344 657

910

345 656

911

346 655

912

347 654

913

348 653

914

349 652

915

350 651

916

351 650

917

352 649

918

353 648

919

354 647

920

355 646

921

356 645

922

357 644

923

358 643

924

359 642

925

360 641

926

361 640

927

362 639

928

363 638

929

364 637

930

365 636

931

366 635

932

367 634

933

368 633

934

369 632

935

370 631

936

371 630

937

372 629

938

373 628

939

374 627

940

375 626

941

376 625

942

377 624

943

378 623

944

379 622

945

380 621

946

381 620

947

382 619

948

383 618

949

384 617

950

385 616

951

386 615

952

387 614

953

388 613

954

389 612

955

390 611

956

391 610

957

392 609

958

393 608

959

394 607

960

395 606

961

396 605

962

397 604

963

398 603

964

399 602

965

400 601

966

401 600

967

402 599

968

403 598

969

404 597

970

405 596

971

406 595

972

407 594

973

408 593

974

409 592

975

410 591

976

411 590

977

412 589

978

413 588

979

414 587

980

415 586

981

416 585

982

417 584

983

418 583

984

419 582

985

420 581

986

421 580

987

422 579

988

423 578

989

424 577

990

425 576

991

426 575

992

427 574

993

428 573

994

429 572

995

430 571

996

431 570

997

432 569

998

433 568

999

434 567

1000

435 566

1001

436 565

1002

437 564

1003

438 563

1004

439 562

1005

440 561

1006

441 560

1007

442 559

1008

443 558

1009

444 557

1010

445 556

1011

446 555

1012

447 554

1013

448 553

1014

449 552

1015

450 551

1016

451 550

1017

452 549

1018

453 548

1019

454 547

1020

455 546

1021

456 545

1022

457 544

1023

458 543

1024

459 542

1025

460 541

1026

461 540

1027

462 539

1028

463 538

1029

464 537

1030

465 536

1031

466 535

1032

467 534

1033

468 533

1034

469 532

1035

470 531

1036

471 530

1037

472 529

1038

473 528

1039

474 527

1040

475 526

1041

476 525

1042

477 524

1043

478 523

1044

479 522

1045

480 521

1046

481 520

1047

482 519

1048

483 518

1049

484 517

1050

485 516

1051

486 515

1052

487 514

1053

488 513

1054

489 512

1055

490 511

1056

491 510

1057

492 509

1058

493 508

1059

494 507

1060

495 506

1061

496 505

1062

497 504

1063

498 503

1064

499 502

1065

500 501

1066

501 500

1067

502 499

1068

503 498

1069

504 497

1070

505 496

1071

506 495

1072

507 494

1073

508 493

1074

509 492

1075

510 491

1076

511 490

1077

512 489

1078

513 488

1079

514 487

1080

515 486

1081

516 485

1082

517 484

1083

518 483

1084

519 482

1085

520 481

1086

521 480

1087

522 479

1088

523 478

1089

524 477

1090

525 476

1091

526 475

1092

527 474

1093

528 473

1094

529 472

1095

530 471

1096

531 470

1097

532 469

1098

533 468

1099

534 467

1100

535 466

1101

536 465

1102

537 464

1103

538 463

1104

539 462

1105

540 461

1106

541 460

1107

542 459

1108

543 458

1109

544 457

1110

545 456

1111

546 455

1112

547 454

1113

548 453

1114

549 452

1115

550 451

1116

551 450

1117

552 449

1118

553 448

1119

554 447

1120

555 446

1121

556 445

1122

557 444

1123

558 443

1124

559 442

1125

560 441

1126

561 440

1127

562 439

1128

563 438

1129

564 437

1130

565 436

1131

566 435

1132

567 434

1133

568 433

1134

569 432

1135

570 431

1136

571 430

1137

572 429

1138

573 428

1139

574 427

1140

575 426

1141

576 425

1142

577 424

1143

578 423

1144

579 422

1145

580 421

1146

581 420

1147

582 419

1148

583 418

1149

584 417

1150

585 416

1151

586 415

1152

587 414

1153

588 413

1154

589 412

1155

590 411

1156

591 410

1157

592 409

1158

593 408

1159

594 407

1160

595 406

1161

596 405

1162

597 404

1163

598 403

1164

599 402

1165

600 401

1166

601 400

1167

602 399

1168

603 398

1169

604 397

1170

605 396

1171

606 395

1172

607 394

1173

608 393

1174

609 392

1175

610 391

1176

611 390

1177

612 389

1178

613 388

1179

614 387

1180

615 386

1181

616 385

1182

617 384

1183

618 383

1184

619 382

1185

620 381

1186

621 380

1187

622 379

1188

623 378

1189

624 377

1190

625 376

1191

626 375

1192

627 374

1193

628 373

1194

629 372

1195

630 371

1196

631 370

1197

632 369

1198

633 368

1199

634 367

1200

635 366

1201

636 365

1202

637 364

1203

638 363

1204

639 362

1205

640 361

1206

641 360

1207

642 359

1208

643 358

1209

644 357

1210

645 356

1211

646 355

1212

647 354

1213

648 353

1214

649 352

1215

650 351

1216

651 350

1217

652 349

1218

653 348

1219

654 347

1220

655 346

1221

656 345

1222

657 344

1223

658 343

1224

659 342

1225

660 341

1226

661 340

1227

662 339

1228

663 338

1229

664 337

1230

665 336

1231

666 335

1232

667 334

1233

668 333

1234

669 332

1235

670 331

1236

671 330

1237

672 329

1238

673 328

1239

674 327

1240

675 326

1241

676 325

1242

677 324

1243

678 323

1244

679 322

1245

680 321

1246

681 320

1247

682 319

1248

683 318

1249

684 317

1250

685 316

1251

686 315

1252

687 314

1253

688 313

1254

689 312

1255

690 311

1256

691 310

1257

692 309

1258

693 308

1259

694 307

1260

695 306

1261

696 305

1262

697 304

1263

698 303

1264

699 302

1265

700 301

1266

701 300

1267

702 299

1268

703 298

1269

704 297

1270

705 296

1271

706 295

1272

707 294

1273

708 293

1274

709 292

1275

710 291

1276

711 290

1277

712 289

1278

713 288

1279

714 287

1280

715 286

1281

716 285

1282

717 284

1283

718 283

1284

719 282

1285

720 281

1286

721 280

1287

722 279

1288

723 278

1289

724 277

1290

725 276

1291

726 275

1292

727 274

1293

728 273

1294

729 272

1295

730 271

1296

731 270

1297

732 269

1298

733 268

1299

734 267

1300

735 266

1301

736 265

1302

737 264

1303

738 263

1304

739 262

1305

740 261

1306

741 260

1307

742 259

1308

743 258

1309

744 257

1310

745 256

1311

746 255

1312

747 254

1313

748 253

1314

749 252

1315

750 251

1316

751 250

1317

752 249

1318

753 248

1319

754 247

1320

755 246

1321

756 245

1322

757 244

1323

758 243

1324

759 242

1325

760 241

1326

761 240

1327

762 239

1328

763 238

1329

764 237

1330

765 236

1331

766 235

1332

767 234

1333

768 233

1334

769 232

1335

770 231

1336

771 230

1337

772 229

1338

773 228

1339

774 227

1340

775 226

1341

776 225

1342

777 224

1343

778 223

1344

779 222

1345

780 221

1346

781 220

1347

782 219

1348

783 218

1349

784 217

1350

785 216

1351

786 215

1352

787 214

1353

788 213

1354

789 212

1355

790 211

1356

791 210

1357

792 209

1358

793 208

1359

794 207

1360

795 206

1361

796 205

1362

797 204

1363

798 203

1364

799 202

1365

800 201

1366

801 200

1367

802 199

1368

803 198

1369

804 197

1370

805 196

1371

806 195

1372

807 194

1373

808 193

1374

809 192

1375

810 191

1376

811 190

1377

812 189

1378

813 188

1379

814 187

1380

815 186

1381

816 185

1382

817 184

1383

818 183

1384

819 182

1385

820 181

1386

821 180

1387

822 179

1388

823 178

1389

824 177

1390

825 176

1391

826 175

1392

827 174

1393

828 173

1394

829 172

1395

830 171

1396

831 170

1397

832 169

1398

833 168

1399

834 167

1400

835 166

1401

836 165

1402

837 164

1403

838 163

1404

839 162

1405

840 161

1406

841 160

1407

842 159

1408

843 158

1409

844 157

1410

845 156

1411

846 155

1412

847 154

1413

848 153

1414

849 152

1415

850 151

1416

851 150

1417

852 149

1418

853 148

1419

854 147

1420

855 146

1421

856 145

1422

857 144

1423

858 143

1424

859 142

1425

860 141

1426

861 140

1427

862 139

1428

863 138

1429

864 137

1430

865 136

1431

866 135

1432

867 134

1433

868 133

1434

869 132

1435

870 131

1436

871 130

1437

872 129

1438

873 128

1439

874 127

1440

875 126

1441

876 125

1442

877 124

1443

878 123

1444

879 122

1445

880 121

1446

881 120

1447

882 119

1448

883 118

1449

884 117

1450

885 116

1451

886 115

1452

887 114

1453

888 113

1454

889 112

1455

890 111

1456

891 110

1457

892 109

1458

893 108

1459

894 107

1460

895 106

1461

896 105

1462

897 104

1463

898 103

1464

899 102

1465

900 101

1466

901 100

1467

902 99

1468

903 98

1469

904 97

1470

905 96

1471

906 95

1472

907 94

1473

908 93

1474

909 92

1475

910 91

1476

911 90

1477

912 89

1478

913 88

1479

914 87

1480

915 86

1481

916 85

1482

917 84

1483

918 83

1484

919 82

1485

920 81

1486

921 80

1487

922 79

1488

923 78

1489

924 77

1490

925 76

1491

926 75

1492

927 74

1493

928 73

1494

929 72

1495

930 71

1496

931 70

1497

932 69

1498

933 68

1499

934 67

1500

935 66

1501

936 65

1502

937 64

1503

938 63

1504

939 62

1505

940 61

1506

941 60

1507

942 59

1508

943 58

1509

944 57

1510

945 56

1511

946 55

1512

947 54

1513

948 53

1514

949 52

1515

950 51

1516

951 50

1517

952 49

1518

953 48

1519

954 47

1520

955 46

1521

956 45

1522

957 44

1523

958 43

1524

959 42

1525

960 41

1526

961 40

1527

962 39

1528

963 38

1529

964 37

1530

965 36

1531

966 35

1532

967 34

1533

968 33

1534

969 32

1535

970 31

1536

971 30

1537

972 29

1538

973 28

1539

974 27

1540

975 26

1541

976 25

1542

977 24

1543

978 23

1544

979 22

1545

980 21

1546

981 20

1547

982 19

1548

983 18

1549

984 17

1550

985 16

1551

986 15

1552

987 14

1553

988 13

1554

989 12

1555

990 11

1556

991 10

1557

992 9

1558

993 8

1559

994 7

1560

995 6

1561

996 5

1562

997 4

1563

998 3

1564

999 2

1565

1000 1

586

explain select id,t from t1 order by id;

587

id select_type table type possible_keys key key_len ref rows Extra

588

1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 Using filesort

589

explain select id,t from t1 force index (primary) order by id;

590

id select_type table type possible_keys key key_len ref rows Extra

591

1 SIMPLE t1 index NULL PRIMARY 4 NULL 1000

1566

592

drop table t1;

1567

593

CREATE TABLE t1 (

1568

594

FieldKey varchar(36) NOT NULL default '',

1569

LongVal bigint default NULL,

1570

StringVal text,

595

LongVal bigint(20) default NULL,

596

StringVal mediumtext,

1571

597

KEY FieldKey (FieldKey),

1572

598

KEY LongField (FieldKey,LongVal),

1573

599

KEY StringField (FieldKey,StringVal(32))

1574

600

);

1575

601

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

602

EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;

603

id select_type table type possible_keys key key_len ref rows Extra

604

1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using where

1576

605

SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;

1577

606

FieldKey LongVal StringVal

1578

607

1 0 2

1579

608

1 1 3

1580

609

1 2 1

610

EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;

611

id select_type table type possible_keys key key_len ref rows Extra

612

1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort

1581

613

SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;

1582

614

FieldKey LongVal StringVal

1583

615

3 1 2

1584

616

3 2 1

1585

617

3 3 3

618

EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;

619

id select_type table type possible_keys key key_len ref rows Extra

620

1 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 4 Using where

1586

621

SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;

1587

622

FieldKey LongVal StringVal

1588

623

3 1 2

1593

628

SET @id=0;

1594

629

UPDATE t1 SET a=0 ORDER BY (a=@id), b;

1595

630

DROP TABLE t1;

1596

CREATE TABLE t1 ( id int NOT NULL default '0', menu int NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM;

631

CREATE TABLE t1 ( id smallint(6) unsigned NOT NULL default '0', menu tinyint(4) NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM;

1597

632

INSERT INTO t1 VALUES (11384, 2),(11392, 2);

1598

633

SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;

1599

634

1603

638

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

1604

639

explain select * from t1 where b=1 or b is null order by a;

1605

640

id select_type table type possible_keys key key_len ref rows Extra

1606

1 SIMPLE t1 ALL b NULL NULL NULL 6 Using where; Using filesort

641

1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort

1607

642

select * from t1 where b=1 or b is null order by a;

1608

643

a b

1609

644

1 1

1612

647

4 NULL

1613

648

explain select * from t1 where b=2 or b is null order by a;

1614

649

id select_type table type possible_keys key key_len ref rows Extra

1615

1 SIMPLE t1 ALL b NULL NULL NULL 6 Using where; Using filesort

650

1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort

1616

651

select * from t1 where b=2 or b is null order by a;

1617

652

a b

1618

653

3 NULL

1720

755

set max_sort_length=20;

1721

756

select a from t1 order by a;

1722

757

758

xxxxxxxxxxxxxxxxxxxab

759

xxxxxxxxxxxxxxxxxxxaa

1723

760

xxxxxxxxxxxxxxxxxxxx

1724

761

xxxxxxxxxxxxxxxxxxxz

1725

xxxxxxxxxxxxxxxxxxxab

1726

xxxxxxxxxxxxxxxxxxxaa

1727

762

drop table t1;

1728

763

create table t1 (

1729

764

`sid` decimal(8,0) default null,

1730

765

`wnid` varchar(11) not null default '',

1731

766

key `wnid14` (`wnid`(4)),

1732

767

key `wnid` (`wnid`)

1733

) engine=myisam;

768

) engine=myisam default charset=latin1;

1734

769

insert into t1 (`sid`, `wnid`) values

1735

770

('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),

1736

771

('39560','01019090000'),('37989','01019000000'),('37990','01019011000'),

1749

784

('25295','02071491000'),('25296','02071491000'),('25297','02071499000');

1750

785

explain select * from t1 where wnid like '0101%' order by wnid;

1751

786

id select_type table type possible_keys key key_len ref rows Extra

1752

1 SIMPLE t1 range wnid14,wnid wnid 46 NULL 10 Using where

787

1 SIMPLE t1 range wnid14,wnid wnid 13 NULL 10 Using where

1753

788

select * from t1 where wnid like '0101%' order by wnid;

1754

789

sid wnid

1755

790

10100 01019000000

1840

875

ERROR 42S22: Unknown column 'num' in 'on clause'

1841

876

DROP TABLE t1;

1842

877

CREATE TABLE bug25126 (

1843

val int NOT NULL AUTO_INCREMENT PRIMARY KEY

878

val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY

1844

879

);

1845

880

UPDATE bug25126 SET MissingCol = MissingCol;

1846

881

ERROR 42S22: Unknown column 'MissingCol' in 'field list'

2007

1042

19 1.3333

2008

1043

9 2.6667

2009

1044

drop table t1;

2010

CREATE TABLE t1 (a INT NOT NULL, b TIME);

1045

CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);

2011

1046

INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);

2012

1047

UPDATE t1 SET b = SEC_TO_TIME(a);

2013

1048

SELECT a, b FROM t1 ORDER BY b DESC;

2033

1068

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

2034

1069

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

2035

1070

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

2036

SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;

2037

2038

2039

1071

EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;

1072

id select_type table type possible_keys key key_len ref rows Extra

1073

1 SIMPLE t1 const PRIMARY,b b 5 const 1

1074

1 SIMPLE t2 ref a a 5 const 2 Using where; Using index

1075

DROP TABLE t1,t2;

1076

CREATE TABLE t1(

1077

id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3));

1078

INSERT INTO t1 (c2,c3) VALUES

1079

(31,34),(35,38),(34,31),(32,35),(31,39),

1080

(11,14),(15,18),(14,11),(12,15),(11,19);

1081

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1082

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1083

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1084

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1085

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1086

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1087

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1088

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1089

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1090

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1091

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1092

INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;

1093

UPDATE t1 SET c2=20 WHERE id%100 = 0;

1094

SELECT COUNT(*) FROM t1;

1095

COUNT(*)

1096

40960

1097

CREATE TABLE t2 LIKE t1;

1098

INSERT INTO t2 SELECT * FROM t1 ORDER BY id;

1099

EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;

1100

id select_type table type possible_keys key key_len ref rows Extra

1101

1 SIMPLE t2 index k2 k3 5 NULL 111 Using where

1102

EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000;

1103

id select_type table type possible_keys key key_len ref rows Extra

1104

1 SIMPLE t2 ref k2 k2 5 const 7341 Using where; Using filesort

1105

EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20;

1106

id select_type table type possible_keys key key_len ref rows Extra

1107

1 SIMPLE t2 index k2 k3 5 NULL 73 Using where

1108

EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000;

1109

id select_type table type possible_keys key key_len ref rows Extra

1110

1 SIMPLE t2 range k2 k2 5 NULL 386 Using index condition; Using where; Using MRR; Using filesort

1111

SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;

1112

id c3

1113

6 14

1114

16 14

1115

26 14

1116

36 14

1117

46 14

1118

56 14

1119

66 14

1120

76 14

1121

86 14

1122

96 14

1123

106 14

1124

116 14

1125

126 14

1126

136 14

1127

146 14

1128

156 14

1129

166 14

1130

176 14

1131

186 14

1132

196 14

2040

1133

DROP TABLE t1,t2;

2041

1134

CREATE TABLE t1 (

2042

1135

a INT,

2050

1143

INSERT INTO t1 SELECT a +16, b +16 FROM t1;

2051

1144

INSERT INTO t1 SELECT a +32, b +32 FROM t1;

2052

1145

INSERT INTO t1 SELECT a +64, b +64 FROM t1;

1146

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;

1147

id select_type table type possible_keys key key_len ref rows Extra

1148

1 SIMPLE t1 range NULL ab 4 NULL 10 Using index for group-by

2053

1149

SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;

2054

1150

2055

1151

2182

1278

128

2183

1279

SELECT @tmp_tables_after = @tmp_tables_before ;

2184

1280

@tmp_tables_after = @tmp_tables_before

2185

NULL

1281

1282

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;

1283

id select_type table type possible_keys key key_len ref rows Extra

1284

1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index

2186

1285

SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;

2187

1286

2188

1287

2315

1414

128

2316

1415

SELECT @tmp_tables_after = @tmp_tables_before;

2317

1416

@tmp_tables_after = @tmp_tables_before

2318

NULL

1417

2319

1418

DROP TABLE t1;

2320

1419

2321

1420

# Bug#31590: Wrong error message on sort buffer being too small.

2322

1421

2323

create table t1(a int, b text);

1422

create table t1(a int, b tinytext);

2324

1423

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

2325

1424

set session sort_buffer_size= 30000;

2326

1425

Warnings:

2327

1426

Warning 1292 Truncated incorrect sort_buffer_size value: '30000'

2328

1427

set session max_sort_length= 2180;

2329

1428

select * from t1 order by b;

2330

a b

2331

1 2

2332

3 2

1429

ERROR HY001: Out of sort memory; increase server sort buffer size

2333

1430

drop table t1;

2334

CREATE TABLE t2 (a varchar(32), b int, c float, d double,

1431

CREATE TABLE t2 (a varchar(32), b int(11), c float, d double,

2335

1432

UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c));

2336

1433

CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));

2337

1434

CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b));

2338

1435

INSERT INTO t3 SELECT * FROM t1;

1436

EXPLAIN

1437

SELECT d FROM t1, t2

1438

WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'

1439

ORDER BY t2.c LIMIT 1;

1440

id select_type table type possible_keys key key_len ref rows Extra

1441

1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using temporary; Using filesort

1442

1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition

2339

1443

SELECT d FROM t1, t2

2340

1444

WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'

2341

1445

ORDER BY t2.c LIMIT 1;

2342

1446

2343

1447

52.5

1448

EXPLAIN

1449

SELECT d FROM t3 AS t1, t2 AS t2

1450

WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'

1451

ORDER BY t2.c LIMIT 1;

1452

id select_type table type possible_keys key key_len ref rows Extra

1453

1 SIMPLE t2 range a,b,c c 5 NULL 420 Using where; Using MRR

1454

1 SIMPLE t1 ref a a 39 test.t2.a,const 10 Using where; Using index

2344

1455

SELECT d FROM t3 AS t1, t2 AS t2

2345

1456

WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'

2346

1457

ORDER BY t2.c LIMIT 1;

Older »