# # Tests for the new column list partitioning introduced in second # version for partitioning. # --source include/have_partition.inc --disable_warnings drop table if exists t1; --enable_warnings --error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR create table t1 (a int) partition by list (a) ( partition p0 values in (1), partition p1 values in (1)); create table t1 (a int) partition by 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); insert into t1 values (4); insert into t1 values (NULL); --error ER_NO_PARTITION_FOR_GIVEN_VALUE insert into t1 values (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))); insert into t1 values (1); insert into t1 values (2); insert into t1 values (3); insert into t1 values (4); insert into t1 values (NULL); --error ER_NO_PARTITION_FOR_GIVEN_VALUE insert into t1 values (5); 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))); 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'))); 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); insert into t1 values (1,'d','e',1),(2,'d','e',2),(3,'d','e',3); select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR (a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2)))); 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))); insert into t1 values (1, 'A', 1); explain partitions select * from t1 where a = 1 AND b <= 'A' and c = 1; select * from t1 where a = 1 AND b <= 'A' and c = 1; 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'))); insert into t1 (a) values ('a'); select * from t1 where a = 'a'; drop table t1; --error ER_WRONG_TYPE_COLUMN_VALUE_ERROR 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'))); create table t1 (a int, b int) partition by range column_list(a,b) (partition p0 values less than (column_list(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'))); 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'))); 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'))); 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))); insert into t1 values (99,998); select * from t1 where b = 998; drop table t1; create table t1 as select to_seconds(null) as to_seconds; select data_type from information_schema.columns where column_name='to_seconds'; drop table t1; --error ER_PARSE_ERROR create table t1 (a int, b int) partition by list column_list(a,b) (partition p0 values in (column_list(maxvalue,maxvalue))); create table t1 (a int, b int) partition by range column_list(a,b) (partition p0 values less than (column_list(maxvalue,maxvalue))); drop table t1; create table t1 (a int) partition by list column_list(a) (partition p0 values in (column_list(0))); select partition_method from information_schema.partitions where table_name='t1'; 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'))); insert into t1 values ('F23456'); select * from t1; drop table t1; -- error 1054 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))); -- error ER_RANGE_NOT_INCREASING_ERROR 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))); -- error 1064 create table t1 (a int, b int) partition by range column_list(a,b) (partition p0 values less than (10)); -- error ER_PARTITION_COLUMN_LIST_ERROR create table t1 (a int, b int) partition by range column_list(a,b) (partition p0 values less than (column_list(1,1,1)); 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))); -- error ER_NO_PARTITION_FOR_GIVEN_VALUE insert into t1 values (10,0); insert into t1 values (0,1),(1,1),(2,1),(3,1),(3,4),(4,9),(9,1); select * from t1; 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))); explain partitions select * from t1 where b < 2; select * from t1 where b < 2; explain partitions select * from t1 where b < 4; select * from t1 where b < 4; 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))); -- error ER_REORG_OUTSIDE_RANGE 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))); -- error ER_PARTITION_COLUMN_LIST_ERROR alter table t1 reorganize partition p2 into (partition p2 values less than(column_list(9,6,1))); -- error ER_PARTITION_COLUMN_LIST_ERROR alter table t1 reorganize partition p2 into (partition p2 values less than (10)); 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))); explain partitions select * from t1 where b < 4; select * from t1 where b < 4; drop table t1; #create table t1 (a int, b int) #partition by range column_list(a,b) #(partition p0 values less than (column_list(99,99)), # partition p1 values less than (column_list(99,maxvalue))); #drop table t1; 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))); insert into t1 values (1000,1000); #select * from t1 where a = 0 and b = 0; 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'))); alter table t1 add partition (partition p1 values less than (column_list('b','c','d'))); drop table t1;