diff options
Diffstat (limited to 'mysql-test/r/partition_column.result')
| -rw-r--r-- | mysql-test/r/partition_column.result | 134 |
1 files changed, 68 insertions, 66 deletions
diff --git a/mysql-test/r/partition_column.result b/mysql-test/r/partition_column.result index fb7f815fd98..f4e7cd9bf94 100644 --- a/mysql-test/r/partition_column.result +++ b/mysql-test/r/partition_column.result @@ -4,7 +4,7 @@ partition by key (a,a); ERROR HY000: Duplicate partition field name a create table t1 (a int, b int) partition by list column_list(a,a) -( partition p values in (column_list(1,1))); +( partition p values in ((1,1))); ERROR HY000: Duplicate partition field name a create table t1 (a int signed) partition by list (a) @@ -26,10 +26,8 @@ a drop table t1; create table t1 (a int signed) partition by list column_list(a) -( partition p0 values in (column_list(1), column_list(3), column_list(5), -column_list(7), column_list(9), column_list(NULL)), -partition p1 values in (column_list(2), column_list(4), column_list(6), -column_list(8), column_list(0))); +( partition p0 values in (1, 3, 5, 7, 9, NULL), +partition p1 values in (2, 4, 6, 8, 0)); insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8); select * from t1 where a <= NULL; a @@ -46,10 +44,9 @@ a drop table t1; create table t1 (a int, b int) partition by list column_list(a,b) -( partition p0 values in (column_list(1, NULL), column_list(2, NULL), -column_list(NULL, NULL)), -partition p1 values in (column_list(1,1), column_list(2,2)), -partition p2 values in (column_list(3, NULL), column_list(NULL, 1))); +( partition p0 values in ((1, NULL), (2, NULL), (NULL, NULL)), +partition p1 values in ((1,1), (2,2)), +partition p2 values in ((3, NULL), (NULL, 1))); insert into t1 values (3, NULL); insert into t1 values (NULL, 1); insert into t1 values (NULL, NULL); @@ -81,9 +78,9 @@ t1 CREATE TABLE `t1` ( `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST COLUMN_LIST(a,b) -(PARTITION p0 VALUES IN ( COLUMN_LIST(1,NULL), COLUMN_LIST(2,NULL), COLUMN_LIST(NULL,NULL)) ENGINE = MyISAM, - PARTITION p1 VALUES IN ( COLUMN_LIST(1,1), COLUMN_LIST(2,2)) ENGINE = MyISAM, - PARTITION p2 VALUES IN ( COLUMN_LIST(3,NULL), COLUMN_LIST(NULL,1)) ENGINE = MyISAM) */ +(PARTITION p0 VALUES IN ((1,NULL),(2,NULL),(NULL,NULL)) ENGINE = MyISAM, + PARTITION p1 VALUES IN ((1,1),(2,2)) ENGINE = MyISAM, + PARTITION p2 VALUES IN ((3,NULL),(NULL,1)) ENGINE = MyISAM) */ drop table t1; create table t1 (a int) partition by list (a) @@ -104,8 +101,13 @@ ERROR HY000: Table has no partition for value 5 drop table t1; create table t1 (a int) partition by list column_list(a) -( partition p0 values in (column_list(2), column_list(1)), -partition p1 values in (column_list(4), column_list(NULL), column_list(3))); +( partition p0 values in (2, 1), +partition p1 values in ((4), (NULL), (3))); +ERROR 42000: Row expressions in VALUES IN only allowed for multi-field column partitioning near '))' at line 4 +create table t1 (a int) +partition by list column_list(a) +( partition p0 values in (2, 1), +partition p1 values in (4, NULL, 3)); insert into t1 values (1); insert into t1 values (2); insert into t1 values (3); @@ -119,26 +121,26 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST COLUMN_LIST(a) -(PARTITION p0 VALUES IN ( COLUMN_LIST(2), COLUMN_LIST(1)) ENGINE = MyISAM, - PARTITION p1 VALUES IN ( COLUMN_LIST(4), COLUMN_LIST(NULL), COLUMN_LIST(3)) ENGINE = MyISAM) */ +(PARTITION p0 VALUES IN (2,1) ENGINE = MyISAM, + PARTITION p1 VALUES IN (4,NULL,3) ENGINE = MyISAM) */ drop table t1; create table t1 (a int, b char(10), c varchar(25), d datetime) partition by range column_list(a,b,c,d) subpartition by hash (to_seconds(d)) subpartitions 4 -( partition p0 values less than (column_list(1, NULL, MAXVALUE, NULL)), -partition p1 values less than (column_list(1, 'a', MAXVALUE, TO_DAYS('1999-01-01'))), -partition p2 values less than (column_list(1, 'a', MAXVALUE, MAXVALUE)), -partition p3 values less than (column_list(1, MAXVALUE, MAXVALUE, MAXVALUE))); +( partition p0 values less than (1, NULL, MAXVALUE, NULL), +partition p1 values less than (1, 'a', MAXVALUE, TO_DAYS('1999-01-01')), +partition p2 values less than (1, 'a', MAXVALUE, MAXVALUE), +partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE)); drop table t1; create table t1 (a int, b char(10), c varchar(5), d int) partition by range column_list(a,b,c) subpartition by key (c,d) subpartitions 3 -( partition p0 values less than (column_list(1,'abc','abc')), -partition p1 values less than (column_list(2,'abc','abc')), -partition p2 values less than (column_list(3,'abc','abc')), -partition p3 values less than (column_list(4,'abc','abc'))); +( partition p0 values less than (1,'abc','abc'), +partition p1 values less than (2,'abc','abc'), +partition p2 values less than (3,'abc','abc'), +partition p3 values less than (4,'abc','abc')); insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3); insert into t1 values (1,'b','c',1),(2,'b','c',2),(3,'b','c',3); insert into t1 values (1,'c','d',1),(2,'c','d',2),(3,'c','d',3); @@ -151,8 +153,8 @@ a b c d drop table t1; create table t1 (a int, b varchar(2), c int) partition by range column_list (a, b, c) -(partition p0 values less than (column_list(1, 'A', 1)), -partition p1 values less than (column_list(1, 'B', 1))); +(partition p0 values less than (1, 'A', 1), +partition p1 values less than (1, 'B', 1)); insert into t1 values (1, 'A', 1); explain partitions select * from t1 where a = 1 AND b <= 'A' and c = 1; id select_type table partitions type possible_keys key key_len ref rows Extra @@ -163,7 +165,7 @@ a b c drop table t1; create table t1 (a char, b char, c char) partition by list column_list(a) -( partition p0 values in (column_list('a'))); +( partition p0 values in ('a')); insert into t1 (a) values ('a'); select * from t1 where a = 'a'; a b c @@ -171,32 +173,32 @@ a NULL NULL drop table t1; create table t1 (d timestamp) partition by range column_list(d) -( partition p0 values less than (column_list('2000-01-01')), -partition p1 values less than (column_list('2040-01-01'))); +( partition p0 values less than ('2000-01-01'), +partition p1 values less than ('2040-01-01')); ERROR HY000: Partition column values of incorrect type create table t1 (a int, b int) partition by range column_list(a,b) -(partition p0 values less than (column_list(null, 10))); +(partition p0 values less than (null, 10)); drop table t1; create table t1 (d date) partition by range column_list(d) -( partition p0 values less than (column_list('2000-01-01')), -partition p1 values less than (column_list('2009-01-01'))); +( partition p0 values less than ('2000-01-01'), +partition p1 values less than ('2009-01-01')); drop table t1; create table t1 (d date) partition by range column_list(d) -( partition p0 values less than (column_list('1999-01-01')), -partition p1 values less than (column_list('2000-01-01'))); +( partition p0 values less than ('1999-01-01'), +partition p1 values less than ('2000-01-01')); drop table t1; create table t1 (d date) partition by range column_list(d) -( partition p0 values less than (column_list('2000-01-01')), -partition p1 values less than (column_list('3000-01-01'))); +( partition p0 values less than ('2000-01-01'), +partition p1 values less than ('3000-01-01')); drop table t1; create table t1 (a int, b int) partition by range column_list(a,b) -(partition p2 values less than (column_list(99,99)), -partition p1 values less than (column_list(99,999))); +(partition p2 values less than (99,99), +partition p1 values less than (99,999)); insert into t1 values (99,998); select * from t1 where b = 998; a b @@ -210,23 +212,23 @@ int drop table t1; create table t1 (a int, b int) partition by list column_list(a,b) -(partition p0 values in (column_list(maxvalue,maxvalue))); +(partition p0 values in ((maxvalue,maxvalue))); ERROR 42000: Cannot use MAXVALUE as value in List partitioning near 'maxvalue,maxvalue)))' at line 3 create table t1 (a int, b int) partition by range column_list(a,b) -(partition p0 values less than (column_list(maxvalue,maxvalue))); +(partition p0 values less than (maxvalue,maxvalue)); drop table t1; create table t1 (a int) partition by list column_list(a) -(partition p0 values in (column_list(0))); +(partition p0 values in (0)); select partition_method from information_schema.partitions where table_name='t1'; partition_method LIST COLUMN_LIST drop table t1; create table t1 (a char(6)) partition by range column_list(a) -(partition p0 values less than (column_list('H23456')), -partition p1 values less than (column_list('M23456'))); +(partition p0 values less than ('H23456'), +partition p1 values less than ('M23456')); insert into t1 values ('F23456'); select * from t1; a @@ -234,13 +236,13 @@ F23456 drop table t1; create table t1 (a char(6)) partition by range column_list(a) -(partition p0 values less than (column_list(H23456)), -partition p1 values less than (column_list(M23456))); +(partition p0 values less than (H23456), +partition p1 values less than (M23456)); ERROR 42S22: Unknown column 'H23456' in 'field list' create table t1 (a char(6)) partition by range column_list(a) -(partition p0 values less than (column_list(23456)), -partition p1 values less than (column_list(23456))); +(partition p0 values less than (23456), +partition p1 values less than (23456)); ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition create table t1 (a int, b int) partition by range column_list(a,b) @@ -248,14 +250,14 @@ partition by range column_list(a,b) ERROR 42000: Inconsistency in usage of column lists for partitioning near '))' at line 3 create table t1 (a int, b int) partition by range column_list(a,b) -(partition p0 values less than (column_list(1,1,1)); +(partition p0 values less than (1,1,1); ERROR HY000: Inconsistency in usage of column lists for partitioning create table t1 (a int, b int) partition by range column_list(a,b) -(partition p0 values less than (column_list(1, NULL)), -partition p1 values less than (column_list(2, maxvalue)), -partition p2 values less than (column_list(3, 3)), -partition p3 values less than (column_list(10, NULL))); +(partition p0 values less than (1, NULL), +partition p1 values less than (2, maxvalue), +partition p2 values less than (3, 3), +partition p3 values less than (10, NULL)); insert into t1 values (10,0); ERROR HY000: Table has no partition for value from column_list insert into t1 values (0,1),(1,1),(2,1),(3,1),(3,4),(4,9),(9,1); @@ -270,9 +272,9 @@ a b 9 1 alter table t1 partition by range column_list(b,a) -(partition p0 values less than (column_list(1,2)), -partition p1 values less than (column_list(3,3)), -partition p2 values less than (column_list(9,5))); +(partition p0 values less than (1,2), +partition p1 values less than (3,3), +partition p2 values less than (9,5)); explain partitions select * from t1 where b < 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 7 Using where @@ -294,21 +296,21 @@ a b 3 1 9 1 alter table t1 reorganize partition p1 into -(partition p11 values less than (column_list(2,2)), -partition p12 values less than (column_list(3,3))); +(partition p11 values less than (2,2), +partition p12 values less than (3,3)); alter table t1 reorganize partition p0 into -(partition p01 values less than (column_list(0,3)), -partition p02 values less than (column_list(1,1))); +(partition p01 values less than (0,3), +partition p02 values less than (1,1)); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range alter table t1 reorganize partition p2 into -(partition p2 values less than(column_list(9,6,1))); +(partition p2 values less than(9,6,1)); ERROR HY000: Inconsistency in usage of column lists for partitioning alter table t1 reorganize partition p2 into (partition p2 values less than (10)); ERROR HY000: Inconsistency in usage of column lists for partitioning alter table t1 reorganize partition p2 into -(partition p21 values less than (column_list(4,7)), -partition p22 values less than (column_list(9,5))); +(partition p21 values less than (4,7), +partition p22 values less than (9,5)); explain partitions select * from t1 where b < 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p11,p12,p21 ALL NULL NULL NULL NULL 7 Using where @@ -324,13 +326,13 @@ create table t1 (a int, b int) partition by list column_list(a,b) subpartition by hash (b) subpartitions 2 -(partition p0 values in (column_list(0,0), column_list(1,1)), -partition p1 values in (column_list(1000,1000))); +(partition p0 values in ((0,0), (1,1)), +partition p1 values in ((1000,1000))); insert into t1 values (1000,1000); drop table t1; create table t1 (a char, b char, c char) partition by range column_list(a,b,c) -( partition p0 values less than (column_list('a','b','c'))); +( partition p0 values less than ('a','b','c')); alter table t1 add partition -(partition p1 values less than (column_list('b','c','d'))); +(partition p1 values less than ('b','c','d')); drop table t1; |