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, 71 insertions, 0 deletions

diff --git a/mysql-test/t/partition_column_prune.test b/mysql-test/t/partition_column_prune.test
new file mode 100644
index 00000000000..52267a66b65
--- /dev/null
+++ b/mysql-test/t/partition_column_prune.test
@@ -0,0 +1,71 @@
+#
+# Partition pruning tests for new COLUMN LIST feature
+#
+-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+--enable_warnings
+
+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')));
+insert into t1 values ('a', NULL, 'd');
+explain partitions select * from t1 where a = 'a' AND c = 'd';
+select * from t1 where a = 'a' AND c = 'd';
+drop table t1;
+
+## COLUMN_LIST partition pruning tests
+create table t1 (a int not null) partition by range column_list(a) (
+  partition p0 values less than (column_list(10)),
+  partition p1 values less than (column_list(20)),
+  partition p2 values less than (column_list(30)),
+  partition p3 values less than (column_list(40)),
+  partition p4 values less than (column_list(50)),
+  partition p5 values less than (column_list(60)),
+  partition p6 values less than (column_list(70))
+);
+insert into t1 values (5),(15),(25),(35),(45),(55),(65);
+insert into t1 values (5),(15),(25),(35),(45),(55),(65);
+
+create table t2 (a int not null) partition by range(a) (
+  partition p0 values less than (10),
+  partition p1 values less than (20),
+  partition p2 values less than (30),
+  partition p3 values less than (40),
+  partition p4 values less than (50),
+  partition p5 values less than (60),
+  partition p6 values less than (70)
+);
+insert into t2 values (5),(15),(25),(35),(45),(55),(65);
+insert into t2 values (5),(15),(25),(35),(45),(55),(65);
+
+explain partitions select * from t1 where a > 35 and a < 45;
+explain partitions select * from t2 where a > 35 and a < 45;
+
+drop table t1, t2;
+
+create table t1 (a int not null, b int not null ) 
+partition by range column_list(a,b) (
+  partition p01 values less than (column_list(2,10)),
+  partition p02 values less than (column_list(2,20)),
+  partition p03 values less than (column_list(2,30)),
+
+  partition p11 values less than (column_list(4,10)),
+  partition p12 values less than (column_list(4,20)),
+  partition p13 values less than (column_list(4,30)),
+
+  partition p21 values less than (column_list(6,10)),
+  partition p22 values less than (column_list(6,20)),
+  partition p23 values less than (column_list(6,30))
+);
+
+insert into t1 values (2,5), (2,15), (2,25),
+  (4,5), (4,15), (4,25), (6,5), (6,15), (6,25);
+insert into t1 select * from t1;
+
+explain partitions select * from t1 where a=2;
+explain partitions select * from t1 where a=4;
+explain partitions select * from t1 where a=2 and b < 22;
+
+drop table t1;