« back to all changes in this revision
Viewing changes to tests/r/order_by.result
- browse files at revision 500
- compare with another revision
- compare with revision 2165.1.3
- download diff
- download tarball
- view history from revision 500
- Committer: Brian Aker
- Date: 2008-10-10 18:07:08 UTC
- Revision ID: brian@tangent.org-20081010180708-dyxthxiyno22s3pb
Re-enabled more tests.
- files removed:
- tests/r/type_bit.result
- files modified:
- tests/Makefile.am
added
removed
tests/r/order_by.result
1
1
drop table if exists t1,t2,t3;
2
2
CREATE TABLE t1 (
3
id int(6) DEFAULT '0' NOT NULL,
4
idservice int(5),
5
clee char(20) NOT NULL,
6
flag char(1),
3
id int DEFAULT '0' NOT NULL,
4
idservice int,
5
clee varchar(20) NOT NULL,
6
flag varchar(1),
7
7
KEY id (id),
8
8
PRIMARY KEY (clee)
9
9
);
15
15
INSERT INTO t1 VALUES (2,7,'60671569','Y');
16
16
INSERT INTO t1 VALUES (2,3,'dd','Y');
17
17
CREATE TABLE t2 (
18
id int(6) NOT NULL auto_increment,
18
id int NOT NULL auto_increment,
19
19
description varchar(40) NOT NULL,
20
20
idform varchar(40),
21
ordre int(6) unsigned DEFAULT '0' NOT NULL,
21
ordre int DEFAULT '0' NOT NULL,
22
22
image varchar(60),
23
23
PRIMARY KEY (id),
24
24
KEY id (id,ordre)
151
151
drop table t1;
152
152
CREATE TABLE t1
153
153
(
154
a int unsigned NOT NULL,
155
b int unsigned NOT NULL,
156
c int unsigned NOT NULL,
154
a int NOT NULL,
155
b int NOT NULL,
156
c int NOT NULL,
157
157
UNIQUE(a),
158
158
INDEX(b),
159
159
INDEX(c)
160
160
);
161
161
CREATE TABLE t2
162
162
(
163
c int unsigned NOT NULL,
164
i int unsigned NOT NULL,
163
c int NOT NULL,
164
i int NOT NULL,
165
165
INDEX(c)
166
166
);
167
167
CREATE TABLE t3
168
168
(
169
c int unsigned NOT NULL,
169
c int NOT NULL,
170
170
v varchar(64),
171
171
INDEX(c)
172
172
);
251
251
1999-07-27 840
252
252
drop table t1,t2,t3;
253
253
CREATE TABLE t1 (
254
member_id int(11) NOT NULL auto_increment,
254
member_id int NOT NULL auto_increment,
255
255
inschrijf_datum varchar(20) NOT NULL default '',
256
256
lastchange_datum varchar(20) NOT NULL default '',
257
257
nickname varchar(20) NOT NULL default '',
280
280
ipnr varchar(30) NOT NULL default '',
281
281
PRIMARY KEY (member_id)
282
282
) ENGINE=MyISAM PACK_KEYS=1;
283
Warnings:
284
Warning 1101 BLOB/TEXT column 'info' can't have a default value
285
283
insert into t1 (member_id) values (1),(2),(3);
286
284
select member_id, nickname, voornaam FROM t1
287
285
ORDER by lastchange_datum DESC LIMIT 2;
293
291
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');
294
292
explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
295
293
id select_type table type possible_keys key key_len ref rows Extra
296
1 SIMPLE t1 index a a 22 NULL 11 Using where; Using index
294
1 SIMPLE t1 index a a 52 NULL 11 Using where; Using index
297
295
select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
298
296
a b c
299
297
1 NULL b
300
298
explain select * from t1 where a >= 1 and a < 3 order by a desc;
301
299
id select_type table type possible_keys key key_len ref rows Extra
302
1 SIMPLE t1 range a a 4 NULL 10 Using where; Using index
300
1 SIMPLE t1 range a a 4 NULL 5 Using where; Using index
303
301
select * from t1 where a >= 1 and a < 3 order by a desc;
304
302
a b c
305
303
2 3 c
333
331
1 NULL b
334
332
explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc;
335
333
id select_type table type possible_keys key key_len ref rows Extra
336
1 SIMPLE t1 range a a 9 NULL 8 Using where; Using index
334
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index
337
335
explain select * from t1 where a = 2 and b >0 order by a desc,b desc;
338
336
id select_type table type possible_keys key key_len ref rows Extra
339
337
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index
349
347
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index
350
348
explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
351
349
id select_type table type possible_keys key key_len ref rows Extra
352
1 SIMPLE t1 range a a 9 NULL 2 Using where; Using index
350
1 SIMPLE t1 range a a 9 NULL 1 Using where; Using index
353
351
explain select * from t1 where a = 1 order by b desc;
354
352
id select_type table type possible_keys key key_len ref rows Extra
355
353
1 SIMPLE t1 ref a a 4 const 5 Using where; Using index
361
359
1 1 NULL
362
360
1 NULL b
363
361
1 NULL 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
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;
370
364
explain select * from t1 order by a, b, c;
371
365
id select_type table type possible_keys key key_len ref rows Extra
372
1 SIMPLE t1 index NULL a 20 NULL 11 Using index
366
1 SIMPLE t1 index NULL a 414 NULL 8 Using index
373
367
select * from t1 order by a, b, c;
374
368
a b c
375
1 0
376
1 0 b
377
1 1
378
369
1 1 b
379
370
1 1 b
380
371
1 3 b
385
376
2 3 c
386
377
explain select * from t1 order by a desc, b desc, c desc;
387
378
id select_type table type possible_keys key key_len ref rows Extra
388
1 SIMPLE t1 index NULL a 20 NULL 11 Using index
379
1 SIMPLE t1 index NULL a 414 NULL 8 Using index
389
380
select * from t1 order by a desc, b desc, c desc;
390
381
a b c
391
382
2 3 c
396
387
1 3 b
397
388
1 1 b
398
389
1 1 b
399
1 1
400
1 0 b
401
1 0
402
390
explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
403
391
id select_type table type possible_keys key key_len ref rows Extra
404
1 SIMPLE t1 range a a 20 NULL 3 Using where; Using index
392
1 SIMPLE t1 range a a 414 NULL 3 Using where; Using index
405
393
select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
406
394
a b c
407
395
1 1 b
408
396
1 1 b
409
397
explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
410
398
id select_type table type possible_keys key key_len ref rows Extra
411
1 SIMPLE t1 range a a 4 NULL 6 Using where; Using index
399
1 SIMPLE t1 range a a 4 NULL 3 Using where; Using index
412
400
select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
413
401
a b c
414
402
1 1 b
415
403
1 1 b
416
1 1
417
1 0 b
418
1 0
419
404
select count(*) from t1 where a < 5 and b > 0;
420
405
count(*)
421
9
406
8
422
407
select * from t1 where a < 5 and b > 0 order by a desc,b desc;
423
408
a b c
424
409
2 3 c
429
414
1 3 b
430
415
1 1 b
431
416
1 1 b
432
1 1
433
417
explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
434
418
id select_type table type possible_keys key key_len ref rows Extra
435
1 SIMPLE t1 range a a 8 NULL 10 Using where; Using index
419
1 SIMPLE t1 range a a 12 NULL 4 Using where; Using index
436
420
select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
437
421
a b c
438
422
2 1 b
439
423
2 1 a
440
424
1 1 b
441
425
1 1 b
442
1 1
443
1 0 b
444
1 0
445
426
explain select * from t1 where a between 0 and 1 order by a desc, b desc;
446
427
id select_type table type possible_keys key key_len ref rows Extra
447
1 SIMPLE t1 range a a 4 NULL 5 Using where; Using index
428
1 SIMPLE t1 range a a 4 NULL 3 Using where; Using index
448
429
select * from t1 where a between 0 and 1 order by a desc, b desc;
449
430
a b c
450
431
1 3 b
451
432
1 1 b
452
433
1 1 b
453
1 1
454
1 0 b
455
1 0
456
434
drop table t1;
457
435
CREATE TABLE t1 (
458
gid int(10) unsigned NOT NULL auto_increment,
459
cid smallint(5) unsigned NOT NULL default '0',
436
gid int NOT NULL auto_increment,
437
cid int NOT NULL default '0',
460
438
PRIMARY KEY (gid),
461
439
KEY component_id (cid)
462
440
) ENGINE=MyISAM;
463
441
INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108);
464
ALTER TABLE t1 add skr int(10) not null;
442
ALTER TABLE t1 add skr int not null;
465
443
CREATE TABLE t2 (
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',
444
gid int NOT NULL default '0',
445
uid int NOT NULL default '1',
446
sid int NOT NULL default '1',
469
447
PRIMARY KEY (gid),
470
448
KEY uid (uid),
471
449
KEY status_id (sid)
472
450
) ENGINE=MyISAM;
473
451
INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5);
474
452
CREATE TABLE t3 (
475
uid smallint(6) NOT NULL auto_increment,
453
uid int NOT NULL auto_increment,
476
454
PRIMARY KEY (uid)
477
455
) ENGINE=MyISAM;
478
456
INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250);
479
ALTER TABLE t3 add skr int(10) not null;
457
ALTER TABLE t3 add skr int not null;
480
458
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;
481
459
gid sid uid
482
460
104620 5 15
496
474
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
475
id select_type table type possible_keys key key_len ref rows Extra
498
476
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
477
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.uid 1 Using index
500
478
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
501
479
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
502
480
id select_type table type possible_keys key key_len ref rows Extra
503
481
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
482
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using index
505
483
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
484
id select_type table type possible_keys key key_len ref rows Extra
507
485
1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
508
486
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
487
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.uid 1 Using index
510
488
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
511
489
id select_type table type possible_keys key key_len ref rows Extra
512
490
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
491
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using index
514
492
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
515
493
id select_type table type possible_keys key key_len ref rows Extra
516
494
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
495
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.skr 1
518
496
drop table t1,t2,t3;
519
497
CREATE TABLE t1 (
520
498
`titre` char(80) NOT NULL default '',
521
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
499
`numeropost` int NOT NULL auto_increment,
522
500
`date` datetime NOT NULL default '0000-00-00 00:00:00',
523
501
`auteur` char(35) NOT NULL default '',
524
`icone` tinyint(2) unsigned NOT NULL default '0',
502
`icone` int NOT NULL default '0',
525
503
`lastauteur` char(35) NOT NULL default '',
526
`nbrep` smallint(6) unsigned NOT NULL default '0',
504
`nbrep` int NOT NULL default '0',
527
505
`dest` char(35) NOT NULL default '',
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',
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',
532
510
PRIMARY KEY (`numeropost`),
533
511
KEY `date` (`date`),
534
512
KEY `dest` (`dest`,`ludest`),
537
515
KEY `dest_2` (`dest`,`date`)
538
516
) CHECKSUM=1;
539
517
CREATE TABLE t2 (
540
`numeropost` mediumint(8) unsigned NOT NULL default '0',
518
`numeropost` int NOT NULL default '0',
541
519
`pseudo` char(35) NOT NULL default '',
542
520
PRIMARY KEY (`numeropost`,`pseudo`),
543
521
KEY `pseudo` (`pseudo`)
583
561
5 NULL
584
562
DROP TABLE t1;
585
563
create table t1(id int not null auto_increment primary key, t char(12));
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
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
592
1566
drop table t1;
593
1567
CREATE TABLE t1 (
594
1568
FieldKey varchar(36) NOT NULL default '',
595
LongVal bigint(20) default NULL,
596
StringVal mediumtext,
1569
LongVal bigint default NULL,
1570
StringVal text,
597
1571
KEY FieldKey (FieldKey),
598
1572
KEY LongField (FieldKey,LongVal),
599
1573
KEY StringField (FieldKey,StringVal(32))
601
1575
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
1576
EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
603
1577
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
1578
1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 146 const 3 Using where
605
1579
SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
606
1580
FieldKey LongVal StringVal
607
1581
1 0 2
609
1583
1 2 1
610
1584
EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;
611
1585
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
1586
1 SIMPLE t1 range StringField StringField 146 NULL 3 Using index condition; Using filesort
613
1587
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
614
1588
FieldKey LongVal StringVal
615
1589
3 1 2
617
1591
3 3 3
618
1592
EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
619
1593
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
1594
1 SIMPLE t1 range FieldKey,LongField,StringField LongField 146 NULL 3 Using where
621
1595
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
622
1596
FieldKey LongVal StringVal
623
1597
3 1 2
628
1602
SET @id=0;
629
1603
UPDATE t1 SET a=0 ORDER BY (a=@id), b;
630
1604
DROP TABLE t1;
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;
1605
CREATE TABLE t1 ( id int NOT NULL default '0', menu int NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM;
632
1606
INSERT INTO t1 VALUES (11384, 2),(11392, 2);
633
1607
SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
634
1608
id
638
1612
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
639
1613
explain select * from t1 where b=1 or b is null order by a;
640
1614
id select_type table type possible_keys key key_len ref rows Extra
641
1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort
1615
1 SIMPLE t1 ALL b NULL NULL NULL 6 Using where; Using filesort
642
1616
select * from t1 where b=1 or b is null order by a;
643
1617
a b
644
1618
1 1
647
1621
4 NULL
648
1622
explain select * from t1 where b=2 or b is null order by a;
649
1623
id select_type table type possible_keys key key_len ref rows Extra
650
1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort
1624
1 SIMPLE t1 ALL b NULL NULL NULL 6 Using where; Using filesort
651
1625
select * from t1 where b=2 or b is null order by a;
652
1626
a b
653
1627
3 NULL
755
1729
set max_sort_length=20;
756
1730
select a from t1 order by a;
757
1731
a
1732
xxxxxxxxxxxxxxxxxxxx
1733
xxxxxxxxxxxxxxxxxxxz
758
1734
xxxxxxxxxxxxxxxxxxxab
759
1735
xxxxxxxxxxxxxxxxxxxaa
760
xxxxxxxxxxxxxxxxxxxx
761
xxxxxxxxxxxxxxxxxxxz
762
1736
drop table t1;
763
1737
create table t1 (
764
1738
`sid` decimal(8,0) default null,
765
1739
`wnid` varchar(11) not null default '',
766
1740
key `wnid14` (`wnid`(4)),
767
1741
key `wnid` (`wnid`)
768
) engine=myisam default charset=latin1;
1742
) engine=myisam;
769
1743
insert into t1 (`sid`, `wnid`) values
770
1744
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
771
1745
('39560','01019090000'),('37989','01019000000'),('37990','01019011000'),
784
1758
('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
785
1759
explain select * from t1 where wnid like '0101%' order by wnid;
786
1760
id select_type table type possible_keys key key_len ref rows Extra
787
1 SIMPLE t1 range wnid14,wnid wnid 13 NULL 10 Using where
1761
1 SIMPLE t1 range wnid14,wnid wnid 46 NULL 10 Using where
788
1762
select * from t1 where wnid like '0101%' order by wnid;
789
1763
sid wnid
790
1764
10100 01019000000
875
1849
ERROR 42S22: Unknown column 'num' in 'on clause'
876
1850
DROP TABLE t1;
877
1851
CREATE TABLE bug25126 (
878
val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
1852
val int NOT NULL AUTO_INCREMENT PRIMARY KEY
879
1853
);
880
1854
UPDATE bug25126 SET MissingCol = MissingCol;
881
1855
ERROR 42S22: Unknown column 'MissingCol' in 'field list'
1042
2016
19 1.3333
1043
2017
9 2.6667
1044
2018
drop table t1;
1045
CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
2019
CREATE TABLE t1 (a INT NOT NULL, b TIME);
1046
2020
INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
1047
2021
UPDATE t1 SET b = SEC_TO_TIME(a);
1048
2022
SELECT a, b FROM t1 ORDER BY b DESC;
1070
2044
INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
1071
2045
EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
1072
2046
id select_type table type possible_keys key key_len ref rows Extra
1073
1 SIMPLE t1 const PRIMARY,b b 5 const 1
2047
1 SIMPLE t1 const PRIMARY,b b 5 const 1 Using index
1074
2048
1 SIMPLE t2 ref a a 5 const 2 Using where; Using index
1075
2049
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
1133
DROP TABLE t1,t2;
1134
2050
CREATE TABLE t1 (
1135
2051
a INT,
1136
2052
b INT,
1145
2061
INSERT INTO t1 SELECT a +64, b +64 FROM t1;
1146
2062
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
1147
2063
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
2064
1 SIMPLE t1 range NULL PRIMARY 4 NULL 65 Using index for group-by
1149
2065
SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
1150
2066
a
1151
2067
1
1278
2194
128
1279
2195
SELECT @tmp_tables_after = @tmp_tables_before ;
1280
2196
@tmp_tables_after = @tmp_tables_before
1281
1
2197
NULL
1282
2198
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
1283
2199
id select_type table type possible_keys key key_len ref rows Extra
1284
2200
1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index
1414
2330
128
1415
2331
SELECT @tmp_tables_after = @tmp_tables_before;
1416
2332
@tmp_tables_after = @tmp_tables_before
1417
1
2333
NULL
1418
2334
DROP TABLE t1;
1419
2335
#
1420
2336
# Bug#31590: Wrong error message on sort buffer being too small.
1421
2337
#
1422
create table t1(a int, b tinytext);
2338
create table t1(a int, b text);
1423
2339
insert into t1 values (1,2),(3,2);
1424
2340
set session sort_buffer_size= 30000;
1425
2341
Warnings:
1426
2342
Warning 1292 Truncated incorrect sort_buffer_size value: '30000'
1427
2343
set session max_sort_length= 2180;
1428
2344
select * from t1 order by b;
1429
ERROR HY001: Out of sort memory; increase server sort buffer size
2345
a b
2346
1 2
2347
3 2
1430
2348
drop table t1;
1431
CREATE TABLE t2 (a varchar(32), b int(11), c float, d double,
2349
CREATE TABLE t2 (a varchar(32), b int, c float, d double,
1432
2350
UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c));
1433
2351
CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));
1434
2352
CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b));
1438
2356
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1439
2357
ORDER BY t2.c LIMIT 1;
1440
2358
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
2359
1 SIMPLE t1 ref a,b b 15 const 4 Using index condition; Using temporary; Using filesort
2360
1 SIMPLE t2 ref a,b,c a 136 test.t1.a,const 1 Using index condition
1443
2361
SELECT d FROM t1, t2
1444
2362
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1445
2363
ORDER BY t2.c LIMIT 1;
1450
2368
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1451
2369
ORDER BY t2.c LIMIT 1;
1452
2370
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
2371
1 SIMPLE t2 range a,b,c c 9 NULL 127 Using where; Using MRR
2372
1 SIMPLE t1 ref a a 146 test.t2.a,const 1 Using where; Using index
1455
2373
SELECT d FROM t3 AS t1, t2 AS t2
1456
2374
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1457
2375
ORDER BY t2.c LIMIT 1;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1