← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/order_by.test

  • Committer: Brian Aker
  • Date: 2010-02-07 01:33:54 UTC
  • Revision ID: brian@gaz-20100207013354-d2pg1n68u5c09pgo
Remove giant include header to its own file.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/order_by.test
7
7
--enable_warnings
8
8
 
9
9
CREATE TABLE t1 (
10
 
  id int(6) DEFAULT '0' NOT NULL,
11
 
  idservice int(5),
12
 
  clee char(20) NOT NULL,
13
 
  flag char(1),
 
10
  id int DEFAULT '0' NOT NULL,
 
11
  idservice int,
 
12
  clee varchar(20) NOT NULL,
 
13
  flag varchar(1),
14
14
  KEY id (id),
15
15
  PRIMARY KEY (clee)
16
16
);
25
25
INSERT INTO t1 VALUES (2,3,'dd','Y');
26
26
 
27
27
CREATE TABLE t2 (
28
 
  id int(6) NOT NULL auto_increment,
 
28
  id int NOT NULL auto_increment,
29
29
  description varchar(40) NOT NULL,
30
30
  idform varchar(40),
31
 
  ordre int(6) unsigned DEFAULT '0' NOT NULL,
 
31
  ordre int DEFAULT '0' NOT NULL,
32
32
  image varchar(60),
33
33
  PRIMARY KEY (id),
34
34
  KEY id (id,ordre)
120
120
 
121
121
CREATE TABLE t1
122
122
(
123
 
  a          int unsigned       NOT NULL,
124
 
  b          int unsigned       NOT NULL,
125
 
  c          int unsigned       NOT NULL,
 
123
  a          int       NOT NULL,
 
124
  b          int       NOT NULL,
 
125
  c          int       NOT NULL,
126
126
  UNIQUE(a),
127
127
  INDEX(b),
128
128
  INDEX(c)
130
130
 
131
131
CREATE TABLE t2
132
132
(
133
 
  c          int unsigned       NOT NULL,
134
 
  i          int unsigned       NOT NULL,
 
133
  c          int       NOT NULL,
 
134
  i          int       NOT NULL,
135
135
  INDEX(c)
136
136
);
137
137
 
138
138
CREATE TABLE t3
139
139
(
140
 
  c          int unsigned       NOT NULL,
 
140
  c          int       NOT NULL,
141
141
  v          varchar(64),
142
142
  INDEX(c)
143
143
);
191
191
 
192
192
#bug reported by Wouter de Jong
193
193
 
194
 
CREATE TABLE t1 (
195
 
  member_id int(11) NOT NULL auto_increment,
 
194
CREATE TEMPORARY TABLE t1 (
 
195
  member_id int NOT NULL auto_increment,
196
196
  inschrijf_datum varchar(20) NOT NULL default '',
197
197
  lastchange_datum varchar(20) NOT NULL default '',
198
198
  nickname varchar(20) NOT NULL default '',
205
205
  wijk varchar(40) NOT NULL default '',
206
206
  plaats varchar(50) NOT NULL default '',
207
207
  telefoon varchar(10) NOT NULL default '',
208
 
  geboortedatum date NOT NULL default '0000-00-00',
 
208
  geboortedatum date,
209
209
  geslacht varchar(5) NOT NULL default '',
210
210
  email varchar(80) NOT NULL default '',
211
211
  uin varchar(15) NOT NULL default '',
220
220
  info text NOT NULL default '',
221
221
  ipnr varchar(30) NOT NULL default '',
222
222
  PRIMARY KEY  (member_id)
223
 
) ENGINE=MyISAM PACK_KEYS=1;
 
223
) ENGINE=MyISAM;
224
224
 
225
225
insert into t1 (member_id) values (1),(2),(3);
226
226
select member_id, nickname, voornaam FROM t1
255
255
# Test things when we don't have NULL keys
256
256
#
257
257
 
258
 
alter table t1 modify b int not null, modify c varchar(10) not null;
 
258
delete from t1 WHERE b IS NULL OR c IS NULL;
 
259
alter table t1 modify b bigint not null, modify c varchar(100) not null;
259
260
explain select * from t1 order by a, b, c;
260
261
select * from t1 order by a, b, c;
261
262
explain select * from t1 order by a desc, b desc, c desc;
277
278
drop table t1;
278
279
 
279
280
 
280
 
CREATE TABLE t1 (
281
 
  gid int(10) unsigned NOT NULL auto_increment,
282
 
  cid smallint(5) unsigned NOT NULL default '0',
 
281
CREATE TEMPORARY TABLE t1 (
 
282
  gid int NOT NULL auto_increment,
 
283
  cid int NOT NULL default '0',
283
284
  PRIMARY KEY  (gid),
284
285
  KEY component_id (cid)
285
286
) ENGINE=MyISAM;
286
287
INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108);
287
 
ALTER TABLE t1 add skr int(10) not null;
 
288
ALTER TABLE t1 add skr int not null;
288
289
 
289
 
CREATE TABLE t2 (
290
 
  gid int(10) unsigned NOT NULL default '0',
291
 
  uid smallint(5) unsigned NOT NULL default '1',
292
 
  sid tinyint(3) unsigned NOT NULL default '1',
 
290
CREATE TEMPORARY TABLE t2 (
 
291
  gid int NOT NULL default '0',
 
292
  uid int NOT NULL default '1',
 
293
  sid int NOT NULL default '1',
293
294
  PRIMARY KEY  (gid),
294
295
  KEY uid (uid),
295
296
  KEY status_id (sid)
296
297
) ENGINE=MyISAM;
297
298
INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5);
298
299
 
299
 
CREATE TABLE t3 (
300
 
  uid smallint(6) NOT NULL auto_increment,
 
300
CREATE TEMPORARY TABLE t3 (
 
301
  uid int NOT NULL auto_increment,
301
302
  PRIMARY KEY  (uid)
302
303
) ENGINE=MyISAM;
303
304
INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250);
304
 
ALTER TABLE t3 add skr int(10) not null;
 
305
ALTER TABLE t3 add skr int not null;
305
306
 
306
307
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;
307
308
select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
308
309
 
309
310
# The following ORDER BY can be optimimized
310
 
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;
311
 
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
 
311
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;
 
312
SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
312
313
 
313
314
# The following ORDER BY can't be optimimized
314
 
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;
315
 
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
316
 
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
 
315
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;
 
316
SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
 
317
SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
317
318
drop table t1,t2,t3;
318
319
 
319
320
#
322
323
 
323
324
CREATE TABLE t1 (
324
325
  `titre` char(80) NOT NULL default '',
325
 
  `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
326
 
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
 
326
  `numeropost` int NOT NULL auto_increment,
 
327
  `date` datetime,
327
328
  `auteur` char(35) NOT NULL default '',
328
 
  `icone` tinyint(2) unsigned NOT NULL default '0',
 
329
  `icone` int NOT NULL default '0',
329
330
  `lastauteur` char(35) NOT NULL default '',
330
 
  `nbrep` smallint(6) unsigned NOT NULL default '0',
 
331
  `nbrep` int NOT NULL default '0',
331
332
  `dest` char(35) NOT NULL default '',
332
 
  `lu` tinyint(1) unsigned NOT NULL default '0',
333
 
  `vue` mediumint(8) unsigned NOT NULL default '0',
334
 
  `ludest` tinyint(1) unsigned NOT NULL default '0',
335
 
  `ouvert` tinyint(1) unsigned NOT NULL default '1',
 
333
  `lu` int NOT NULL default '0',
 
334
  `vue` int NOT NULL default '0',
 
335
  `ludest` int NOT NULL default '0',
 
336
  `ouvert` int NOT NULL default '1',
336
337
  PRIMARY KEY  (`numeropost`),
337
338
  KEY `date` (`date`),
338
339
  KEY `dest` (`dest`,`ludest`),
339
340
  KEY `auteur` (`auteur`,`lu`),
340
341
  KEY `auteur_2` (`auteur`,`date`),
341
342
  KEY `dest_2` (`dest`,`date`)
342
 
) CHECKSUM=1;
 
343
);
343
344
 
344
345
CREATE TABLE t2 (
345
 
  `numeropost` mediumint(8) unsigned NOT NULL default '0',
 
346
  `numeropost` int NOT NULL default '0',
346
347
  `pseudo` char(35) NOT NULL default '',
347
348
  PRIMARY KEY  (`numeropost`,`pseudo`),
348
349
  KEY `pseudo` (`pseudo`)
376
377
create table t1(id int not null auto_increment primary key, t char(12));
377
378
disable_query_log;
378
379
let $1 = 1000;
 
380
begin;
379
381
while ($1)
380
382
 {
381
383
  eval insert into t1(t) values ('$1'); 
382
384
  dec $1;
383
385
 }
 
386
commit;
384
387
enable_query_log;
385
 
explain select id,t from t1 order by id;
386
 
explain select id,t from t1 force index (primary) order by id;
 
388
select id,t from t1 force index (primary) order by id;
387
389
drop table t1;
388
390
 
389
391
#
391
393
#
392
394
CREATE TABLE t1 (
393
395
  FieldKey varchar(36) NOT NULL default '',
394
 
  LongVal bigint(20) default NULL,
395
 
  StringVal mediumtext,
 
396
  LongVal bigint default NULL,
 
397
  StringVal text,
396
398
  KEY FieldKey (FieldKey),
397
399
  KEY LongField (FieldKey,LongVal),
398
400
  KEY StringField (FieldKey,StringVal(32))
399
401
);
400
402
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');
401
 
EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
402
403
SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
403
 
EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;
404
404
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
405
 
EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
406
405
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
407
406
DROP TABLE t1;
408
407
#
417
416
# Bug when doing an order by on a 1 byte string (Bug #2147)
418
417
#
419
418
 
420
 
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;
 
419
CREATE TEMPORARY TABLE t1 (  id int NOT NULL default '0',  menu int NOT NULL default '0',  KEY id (id),  KEY menu (menu)) ENGINE=MyISAM;
421
420
INSERT INTO t1 VALUES (11384, 2),(11392, 2);
422
421
SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
423
422
drop table t1;
452
451
insert into t1 select null, b, c, d from t2;
453
452
insert into t2 select null, b, c, d from t1;
454
453
insert into t1 select null, b, c, d from t2;
455
 
optimize table t1;
 
454
alter table t1 engine="default";
456
455
set @row=10;
457
456
insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10;
458
457
select * from t1 where a=1 and b in (1) order by c, b, a;
518
517
# Bug #7331
519
518
#
520
519
 
521
 
create table t1 (
 
520
create temporary table t1 (
522
521
  `sid` decimal(8,0) default null,
523
522
  `wnid` varchar(11) not null default '',
524
523
  key `wnid14` (`wnid`(4)),
525
524
  key `wnid` (`wnid`)
526
 
) engine=myisam default charset=latin1;
 
525
) engine=myisam;
527
526
 
528
527
insert into t1 (`sid`, `wnid`) values
529
528
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
593
592
#       crashes server
594
593
#
595
594
CREATE TABLE bug25126 (
596
 
  val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
 
595
  val int NOT NULL AUTO_INCREMENT PRIMARY KEY
597
596
);
598
597
--error 1054
599
598
UPDATE bug25126 SET MissingCol = MissingCol;
713
712
drop table t1;
714
713
 
715
714
#
716
 
# Bug#26672: Incorrect SEC_TO_TIME() casting in ORDER BY
717
 
#
718
 
CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
719
 
INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
720
 
UPDATE t1 SET b = SEC_TO_TIME(a);
721
 
 
722
 
# Correct ORDER
723
 
SELECT a, b FROM t1 ORDER BY b DESC;
724
 
 
725
 
# must be ordered as the above
726
 
SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
727
 
 
728
 
DROP TABLE t1;
729
 
 
730
 
#
731
715
# BUG#16590: Optimized does not do right "const" table pre-read
732
716
#
733
717
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
736
720
CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
737
721
INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
738
722
 
739
 
EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
 
723
SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
740
724
 
741
725
DROP TABLE t1,t2;
742
726
 
743
727
# End of 5.0
744
728
 
745
 
#
746
 
# Bug #28404: query with ORDER BY and ref access
747
 
#
748
 
 
749
 
CREATE TABLE t1(
750
 
  id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3));
751
 
 
752
 
INSERT INTO t1 (c2,c3) VALUES
753
 
 (31,34),(35,38),(34,31),(32,35),(31,39),
754
 
 (11,14),(15,18),(14,11),(12,15),(11,19);
755
 
 
756
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
757
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
758
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
759
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
760
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
761
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
762
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
763
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
764
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
765
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
766
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
767
 
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
768
 
UPDATE t1 SET c2=20 WHERE id%100 = 0;
769
 
SELECT COUNT(*) FROM t1;
770
 
 
771
 
CREATE TABLE t2 LIKE t1;
772
 
INSERT INTO t2 SELECT * FROM t1 ORDER BY id;
773
 
 
774
 
EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;
775
 
EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000;
776
 
EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20;
777
 
EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000;
778
 
 
779
 
SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;
780
 
 
781
 
DROP TABLE t1,t2;
782
 
 
783
729
# 
784
730
# Bug #30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
785
731
#
796
742
INSERT INTO t1 SELECT a +32, b +32 FROM t1;
797
743
INSERT INTO t1 SELECT a +64, b +64 FROM t1;
798
744
 
799
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
800
 
 
801
 
--disable_query_log
802
 
--let $q = `show status like 'Created_tmp_tables';`
803
 
eval set @tmp_tables_before = 
804
 
  CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
805
 
--enable_query_log
806
 
 
807
745
SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
808
746
 
809
 
# this query creates one temporary table in itself, which we are not
810
 
# interested in.
811
 
 
812
 
--disable_query_log
813
 
--let $q = `show status like 'Created_tmp_tables';`
814
 
eval set @tmp_tables_after = 
815
 
  CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
816
 
--enable_query_log
817
 
 
818
747
SELECT @tmp_tables_after = @tmp_tables_before ;
819
748
 
820
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
821
 
 
822
 
--disable_query_log
823
 
--let $q = `show status like 'Created_tmp_tables';`
824
 
eval set @tmp_tables_before = 
825
 
  CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
826
 
--enable_query_log
827
 
 
828
749
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
829
750
 
830
 
--disable_query_log
831
 
--let $q = `show status like 'Created_tmp_tables';`
832
 
eval set @tmp_tables_after = 
833
 
  CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
834
 
--enable_query_log
835
 
 
836
751
SELECT @tmp_tables_after = @tmp_tables_before;
837
752
 
838
753
DROP TABLE t1;
839
754
--echo #
840
755
--echo # Bug#31590: Wrong error message on sort buffer being too small.
841
756
--echo #
842
 
create table t1(a int, b tinytext);
 
757
create table t1(a int, b text);
843
758
insert into t1 values (1,2),(3,2);
844
759
set session sort_buffer_size= 30000;
845
760
set session max_sort_length= 2180;
846
 
--error 1038
847
761
select * from t1 order by b;
848
762
drop table t1;
849
763
 
851
765
# Bug #35206: select query result different if the key is indexed or not
852
766
#
853
767
 
854
 
CREATE TABLE t2 (a varchar(32), b int(11), c float, d double, 
 
768
CREATE TABLE t2 (a varchar(32), b int, c float, d double, 
855
769
  UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c));
856
770
 
857
771
CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));
1292
1206
 
1293
1207
INSERT INTO t3 SELECT * FROM t1;
1294
1208
 
1295
 
EXPLAIN
1296
 
SELECT d FROM t1, t2
1297
 
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1298
 
ORDER BY t2.c LIMIT 1;
1299
1209
SELECT d FROM t1, t2
1300
1210
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1301
1211
ORDER BY t2.c LIMIT 1;
1302
1212
 
1303
 
EXPLAIN
1304
 
SELECT d FROM t3 AS t1, t2 AS t2 
1305
 
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1306
 
ORDER BY t2.c LIMIT 1;
1307
1213
SELECT d FROM t3 AS t1, t2 AS t2 
1308
1214
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
1309
1215
ORDER BY t2.c LIMIT 1;