diff options
Diffstat (limited to 'mysql-test/r/order_by.result')
| -rw-r--r-- | mysql-test/r/order_by.result | 105 |
1 files changed, 105 insertions, 0 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 2e9fe1995d0..1a10c9c6ce9 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -546,3 +546,108 @@ a b 1 2 5 NULL DROP TABLE t1; +create table t1(id int not null auto_increment primary key, t char(12)); +explain select id,t from t1 order by id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 Using filesort +explain select id,t from t1 force index (primary) order by id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 1000 +drop table t1; +CREATE TABLE t1 ( +FieldKey varchar(36) NOT NULL default '', +LongVal bigint(20) default NULL, +StringVal mediumtext, +KEY FieldKey (FieldKey), +KEY LongField (FieldKey,LongVal), +KEY StringField (FieldKey,StringVal(32)) +); +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'); +EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 36 const 3 Using where +SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; +FieldKey LongVal StringVal +1 0 2 +1 1 3 +1 2 1 +EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range FieldKey,LongField,StringField FieldKey 36 NULL 4 Using where; Using filesort +SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; +FieldKey LongVal StringVal +3 1 2 +3 2 1 +3 3 3 +EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range FieldKey,LongField,StringField LongField 36 NULL 4 Using where +SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; +FieldKey LongVal StringVal +3 1 2 +3 2 1 +3 3 3 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT); +SET @id=0; +UPDATE t1 SET a=0 ORDER BY (a=@id), b; +DROP TABLE t1; +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; +INSERT INTO t1 VALUES (11384, 2),(11392, 2); +SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ; +id +11392 +drop table t1; +create table t1(a int, b int, index(b)); +insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); +explain select * from t1 where b=1 or b is null order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null b b 5 const 3 Using where; Using filesort +select * from t1 where b=1 or b is null order by a; +a b +1 1 +2 1 +3 NULL +4 NULL +explain select * from t1 where b=2 or b is null order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null b b 5 const 4 Using where; Using filesort +select * from t1 where b=2 or b is null order by a; +a b +3 NULL +4 NULL +5 2 +6 2 +drop table t1; +create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null, +key(a,b,d), key(c,b,a)); +create table t2 like t1; +insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3); +insert into t2 select null, b, c, d from t1; +insert into t1 select null, b, c, d from t2; +insert into t2 select null, b, c, d from t1; +insert into t1 select null, b, c, d from t2; +insert into t2 select null, b, c, d from t1; +insert into t1 select null, b, c, d from t2; +insert into t2 select null, b, c, d from t1; +insert into t1 select null, b, c, d from t2; +insert into t2 select null, b, c, d from t1; +insert into t1 select null, b, c, d from t2; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +set @row=10; +insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10; +select * from t1 where a=1 and b in (1) order by c, b, a; +a b c d +1 1 2 0 +1 1 12 -1 +1 1 52 -5 +1 1 92 -9 +select * from t1 where a=1 and b in (1); +a b c d +1 1 92 -9 +1 1 52 -5 +1 1 12 -1 +1 1 2 0 +drop table t1, t2; |