WL#3352, Introducing Column list partitioning, makes it possible to partition on most data types, makes it possible to prune on multi-field partitioning

1 files changed, 209 insertions, 0 deletions

diff --git a/mysql-test/t/partition_column.test b/mysql-test/t/partition_column.test
new file mode 100644
index 00000000000..b1f5f4abfcf
--- /dev/null
+++ b/mysql-test/t/partition_column.test
@@ -0,0 +1,209 @@
+#
+# 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
+
+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;