diff options
Diffstat (limited to 'mysql-test/main/partition_order.result')
| -rw-r--r-- | mysql-test/main/partition_order.result | 798 |
1 files changed, 798 insertions, 0 deletions
diff --git a/mysql-test/main/partition_order.result b/mysql-test/main/partition_order.result new file mode 100644 index 00000000..d4a0c133 --- /dev/null +++ b/mysql-test/main/partition_order.result @@ -0,0 +1,798 @@ +CREATE TABLE t1 ( +a int not null, +b int not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 order by b; +a b +1 1 +35 2 +30 4 +2 5 +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b int unsigned not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b tinyint not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b tinyint unsigned not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b smallint not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b smallint unsigned not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b mediumint not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b mediumint unsigned not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b bigint unsigned not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b bigint not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b bigint not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b float not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b double not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b double unsigned not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b float unsigned not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b double precision not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b double precision unsigned not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b decimal not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (2, 5); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b char(10) not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b > 0 order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b varchar(10) not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b > '0' order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b varchar(10) not null, +primary key(a), +index (b(5))) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b > '0' order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b varchar(10) binary not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b > '0' order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b tinytext not null, +primary key(a), +index (b(10))) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b > '0' order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b text not null, +primary key(a), +index (b(10))) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b > '0' order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b mediumtext not null, +primary key(a), +index (b(10))) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b > '0' order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b longtext not null, +primary key(a), +index (b(10))) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b > '0' order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b enum('1','2', '4', '5') not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b >= '1' order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b set('1','2', '4', '5') not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '1'); +INSERT into t1 values (2, '5'); +INSERT into t1 values (30, '4'); +INSERT into t1 values (35, '2'); +select * from t1 force index (b) where b >= '1' order by b; +a b +1 1 +35 2 +30 4 +2 5 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b date not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '2001-01-01'); +INSERT into t1 values (2, '2005-01-01'); +INSERT into t1 values (30, '2004-01-01'); +INSERT into t1 values (35, '2002-01-01'); +select * from t1 force index (b) where b > '2000-01-01' order by b; +a b +1 2001-01-01 +35 2002-01-01 +30 2004-01-01 +2 2005-01-01 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b datetime not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '2001-01-01 00:00:00'); +INSERT into t1 values (2, '2005-01-01 00:00:00'); +INSERT into t1 values (30, '2004-01-01 00:00:00'); +INSERT into t1 values (35, '2002-01-01 00:00:00'); +select * from t1 force index (b) where b > '2000-01-01 00:00:00' order by b; +a b +1 2001-01-01 00:00:00 +35 2002-01-01 00:00:00 +30 2004-01-01 00:00:00 +2 2005-01-01 00:00:00 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b timestamp not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '2001-01-01 00:00:00'); +INSERT into t1 values (2, '2005-01-01 00:00:00'); +INSERT into t1 values (30, '2004-01-01 00:00:00'); +INSERT into t1 values (35, '2002-01-01 00:00:00'); +select * from t1 force index (b) where b > '2000-01-01 00:00:00' order by b; +a b +1 2001-01-01 00:00:00 +35 2002-01-01 00:00:00 +30 2004-01-01 00:00:00 +2 2005-01-01 00:00:00 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b time not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, '01:00:00'); +INSERT into t1 values (2, '05:00:00'); +INSERT into t1 values (30, '04:00:00'); +INSERT into t1 values (35, '02:00:00'); +select * from t1 force index (b) where b > '00:00:00' order by b; +a b +1 01:00:00 +35 02:00:00 +30 04:00:00 +2 05:00:00 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b year not null, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 2001); +INSERT into t1 values (2, 2005); +INSERT into t1 values (30, 2004); +INSERT into t1 values (35, 2002); +select * from t1 force index (b) where b > 2000 order by b; +a b +1 2001 +35 2002 +30 2004 +2 2005 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b bit(5) not null, +c int, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, b'00001', NULL); +INSERT into t1 values (2, b'00101', 2); +INSERT into t1 values (30, b'00100', 2); +INSERT into t1 values (35, b'00010', NULL); +select a from t1 force index (b) where b > b'00000' order by b; +a +1 +35 +30 +2 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b bit(15) not null, +c int, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, b'000000000000001', NULL); +INSERT into t1 values (2, b'001010000000101', 2); +INSERT into t1 values (30, b'001000000000100', 2); +INSERT into t1 values (35, b'000100000000010', NULL); +select a from t1 force index (b) where b > b'000000000000000' order by b; +a +1 +35 +30 +2 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b int, +primary key(a), +index (b)) +partition by range (a) +partitions 2 +(partition x1 values less than (25), +partition x2 values less than (100)); +INSERT into t1 values (1, 1); +INSERT into t1 values (5, NULL); +INSERT into t1 values (2, 4); +INSERT into t1 values (3, 3); +INSERT into t1 values (4, 5); +INSERT into t1 values (7, 1); +INSERT into t1 values (6, 6); +INSERT into t1 values (30, 4); +INSERT into t1 values (35, 2); +INSERT into t1 values (40, NULL); +select * from t1 force index (b) where b < 10 OR b IS NULL order by b; +a b +5 NULL +40 NULL +1 1 +7 1 +35 2 +3 3 +30 4 +2 4 +4 5 +6 6 +select * from t1 force index (b) where b < 10 ORDER BY b; +a b +1 1 +7 1 +35 2 +3 3 +30 4 +2 4 +4 5 +6 6 +select * from t1 force index (b) where b < 10 ORDER BY b DESC; +a b +6 6 +4 5 +2 4 +30 4 +3 3 +35 2 +7 1 +1 1 +drop table t1; +create table t1 (a int not null, b int, c varchar(20), key (a,b,c)) +partition by range (b) +(partition p0 values less than (5), +partition p1 values less than (10)); +INSERT into t1 values (1,1,'1'),(2,2,'2'),(1,3,'3'),(2,4,'4'),(1,5,'5'); +INSERT into t1 values (2,6,'6'),(1,7,'7'),(2,8,'8'),(1,9,'9'); +INSERT into t1 values (1, NULL, NULL), (2, NULL, '10'); +select * from t1 where a = 1 order by a desc, b desc; +a b c +1 9 9 +1 7 7 +1 5 5 +1 3 3 +1 1 1 +1 NULL NULL +select * from t1 where a = 1 order by b desc; +a b c +1 9 9 +1 7 7 +1 5 5 +1 3 3 +1 1 1 +1 NULL NULL +drop table t1; +# +# MDEV-13756 Implement descending index: KEY (a DESC, b ASC) +# +create table t1 (a int, b int, key(a desc)) partition by hash(a) partitions 4; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); +explain select * from t1 order by a limit 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 3 +select * from t1 order by a limit 3; +a b +1 1 +2 2 +3 3 +drop table t1; |