7 files changed, 2419 insertions, 0 deletions
diff --git a/mysql-test/t/ndb_partition_key.test b/mysql-test/t/ndb_partition_key.test
new file mode 100644
index 00000000000..31d3b63122d
--- /dev/null
+++ b/mysql-test/t/ndb_partition_key.test
@@ -0,0 +1,58 @@
+-- source include/have_ndb.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+#
+# Basic syntax test
+#
+
+# Support for partition key verified
+CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b,c))
+  ENGINE = NDB
+  PARTITION BY KEY (a,b);
+
+insert into t1 values (1,1,1,1);
+select * from t1;
+update t1 set d = 2 where a = 1 and b = 1 and c = 1;
+select * from t1;
+delete from t1;
+select * from t1;
+
+drop table t1;
+
+# only support for partition key on primary key
+--error 1453
+CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b))
+  ENGINE = NDB
+  PARTITION BY KEY (c);
+
+CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY(a,b))
+  ENGINE = NDB
+  PARTITION BY KEY (a);
+
+insert into t1 values 
+       (1,1,3),(1,2,3),(1,3,3),(1,4,3),(1,5,3),(1,6,3),
+       (1,7,3),(1,8,3),(1,9,3),(1,10,3),(1,11,3),(1,12,3);
+
+select * from t1 order by b;
+
+DROP TABLE t1;
+
+#
+# Test partition and char support
+#
+
+CREATE TABLE t1 (a INT, b CHAR(10) COLLATE latin1_bin, c INT, d INT,
+                 PRIMARY KEY USING HASH (a,b,c))
+                 ENGINE=NDB
+                 DEFAULT CHARSET=latin1
+                 PARTITION BY KEY (b);
+
+insert into t1 values (1,"a",1,1),(2,"a",1,1),(3,"a",1,1);
+
+# should show only one attribute with DISTRIBUTION KEY
+--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | sed 's/Version: [0-9]*//'
+
+DROP TABLE t1;
diff --git a/mysql-test/t/ndb_partition_range.test b/mysql-test/t/ndb_partition_range.test
new file mode 100644
index 00000000000..35d2d33a722
--- /dev/null
+++ b/mysql-test/t/ndb_partition_range.test
@@ -0,0 +1,86 @@
+-- source include/have_ndb.inc
+#--disable_abort_on_error
+#
+# Simple test for the partition storage engine
+# Focuses on range partitioning tests
+# 
+#-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Partition by range, basic
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b),
+index (a))
+engine = ndb
+partition by range (a)
+partitions 3
+(partition x1 values less than (5),
+ partition x2 values less than (10),
+ partition x3 values less than (20));
+
+# Simple insert and verify test
+INSERT into t1 values (1, 1, 1);
+INSERT into t1 values (6, 1, 1);
+INSERT into t1 values (10, 1, 1);
+INSERT into t1 values (15, 1, 1);
+
+select * from t1 order by a;
+
+select * from t1 where a=1 order by a;
+select * from t1 where a=15 and b=1 order by a;
+select * from t1 where a=21 and b=1 order by a;
+select * from t1 where a=21 order by a;
+select * from t1 where a in (1,6,10,21) order by a;
+select * from t1 where b=1 and a in (1,6,10,21) order by a;
+
+drop table t1;
+
+#
+# Partition by range, basic
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(b),
+unique (a))
+engine = ndb
+partition by range (b)
+partitions 3
+(partition x1 values less than (5),
+ partition x2 values less than (10),
+ partition x3 values less than (20));
+
+# Simple insert and verify test
+INSERT into t1 values (1, 1, 1);
+INSERT into t1 values (2, 6, 1);
+INSERT into t1 values (3, 10, 1);
+INSERT into t1 values (4, 15, 1);
+
+select * from t1 order by a;
+UPDATE t1 set a = 5 WHERE b = 15;
+select * from t1 order by a;
+UPDATE t1 set a = 6 WHERE a = 5;
+select * from t1 order by a;
+
+select * from t1 where b=1 order by b;
+select * from t1 where b=15 and a=1 order by b;
+select * from t1 where b=21 and a=1 order by b;
+select * from t1 where b=21 order by b;
+select * from t1 where b in (1,6,10,21) order by b;
+select * from t1 where a in (1,2,5,6) order by b;
+select * from t1 where a=1 and b in (1,6,10,21) order by b;
+
+DELETE from t1 WHERE b = 6;
+DELETE from t1 WHERE a = 6;
+
+drop table t1;
+
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
new file mode 100644
index 00000000000..49a938fa25d
--- /dev/null
+++ b/mysql-test/t/partition.test
@@ -0,0 +1,494 @@
+#--disable_abort_on_error
+#
+# Simple test for the partition storage engine
+# Taken fromm the select test
+#
+-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+#
+# Partition by key no partition defined => OK
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a);
+
+drop table t1;
+#
+# Partition by key no partition, list of fields
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a, b);
+
+drop table t1;
+#
+# Partition by key specified 3 partitions and defined 3 => ok
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a)
+partitions 3
+(partition x1, partition x2, partition x3);
+
+drop table t1;
+#
+# Partition by key specifying nodegroup
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a)
+partitions 3
+(partition x1 nodegroup 0,
+ partition x2 nodegroup 1,
+ partition x3 nodegroup 2);
+
+drop table t1;
+#
+# Partition by key specifying engine
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a)
+partitions 3
+(partition x1 engine myisam,
+ partition x2 engine myisam,
+ partition x3 engine myisam);
+
+drop table t1;
+#
+# Partition by key specifying tablespace
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a)
+partitions 3
+(partition x1 tablespace ts1,
+ partition x2 tablespace ts2,
+ partition x3 tablespace ts3);
+
+drop table t1;
+
+#
+# Partition by key list, basic
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+partitions 3
+(partition x1 values in (1,2,9,4) tablespace ts1,
+ partition x2 values in (3, 11, 5, 7) tablespace ts2,
+ partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
+
+drop table t1;
+#
+# Partition by key list, list function
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (b*a)
+partitions 3
+(partition x1 values in (1,2,9,4) tablespace ts1,
+ partition x2 values in (3, 11, 5, 7) tablespace ts2,
+ partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
+
+drop table t1;
+
+#
+# Partition by key list, list function, no spec of #partitions
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (b*a)
+(partition x1 values in (1) tablespace ts1,
+ partition x2 values in (3, 11, 5, 7) tablespace ts2,
+ partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
+
+drop table t1;
+
+#
+# Partition by key stand-alone error
+#
+--error 1064
+partition by list (a)
+partitions 3
+(partition x1 values in (1,2,9,4) tablespace ts1,
+ partition x2 values in (3, 11, 5, 7) tablespace ts2,
+ partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
+
+#
+# Partition by key list, number of partitions defined, no partition defined
+#
+--error 1441
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+partitions 2;
+
+#
+# Partition by key list, wrong result type
+#
+--error 1440
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (sin(a))
+partitions 3
+(partition x1 values in (1,2,9,4) tablespace ts1,
+ partition x2 values in (3, 11, 5, 7) tablespace ts2,
+ partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
+
+#
+# Partition by key, partition function not allowed
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a+2)
+partitions 3
+(partition x1 tablespace ts1,
+ partition x2 tablespace ts2,
+ partition x3 tablespace ts3);
+
+#
+# Partition by key, no partition name
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a)
+partitions 3
+(partition tablespace ts1,
+ partition x2 tablespace ts2,
+ partition x3 tablespace ts3);
+
+#
+# Partition by key, invalid field in field list
+#
+--error 1437
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a,d)
+partitions 3
+(partition x1 tablespace ts1,
+ partition x2 tablespace ts2,
+ partition x3 tablespace ts3);
+#
+# Partition by hash, invalid field in function
+#
+--error 1054
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (a + d)
+partitions 3
+(partition x1 tablespace ts1,
+ partition x2 tablespace ts2,
+ partition x3 tablespace ts3);
+
+#
+# Partition by hash, invalid result type
+#
+--error 1440
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (sin(a))
+partitions 3
+(partition x1 tablespace ts1,
+ partition x2 tablespace ts2,
+ partition x3 tablespace ts3);
+
+#
+# Partition by key specified 3 partitions but only defined 2 => error
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a)
+partitions 3
+(partition x1, partition x2);
+
+#
+# Partition by key specified 3 partitions but only defined 2 => error
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (rand(a))
+partitions 2
+(partition x1, partition x2);
+
+#
+# Partition by key specified 3 partitions but only defined 2 => error
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (rand(a))
+partitions 2
+(partition x1 values less than (0), partition x2 values less than (2));
+
+#
+# Partition by key specified 3 partitions but only defined 2 => error
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (rand(a))
+partitions 2
+(partition x1 values in (1), partition x2 values in (2));
+
+#
+# Partition by hash, values less than error
+#
+--error 1430
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (a)
+partitions 2
+(partition x1 values less than (4),
+ partition x2 values less than (5));
+
+#
+# Partition by hash, values in error
+#
+--error 1430
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (a)
+partitions 2
+(partition x1 values in (4),
+ partition x2 values in (5));
+
+#
+# Partition by hash, values in error
+#
+--error 1430
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (a)
+partitions 2
+(partition x1 values in (4,6),
+ partition x2 values in (5,7));
+
+#
+# Subpartition by key, no partitions defined, single field
+#
+--error 1449
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by key (b);
+
+#
+# Subpartition by key, no partitions defined, list of fields
+#
+--error 1449
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by key (a, b);
+
+#
+# Subpartition by hash, no partitions defined
+#
+--error 1449
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by hash (a+b);
+
+#
+# Subpartition by key, no partitions defined, single field
+#
+--error 1449
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by key (b);
+
+#
+# Subpartition by key, no partitions defined, list of fields
+#
+--error 1449
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by key (a, b);
+
+#
+# Subpartition by hash, no partitions defined
+#
+--error 1449
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by hash (a+b);
+
+#
+# Subpartition by hash, no partitions defined, wrong subpartition function
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by hash (rand(a+b));
+
+#
+# Subpartition by hash, wrong subpartition function
+#
+--error 1449
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by hash (sin(a+b))
+(partition x1 (subpartition x11, subpartition x12),
+ partition x2 (subpartition x21, subpartition x22));
+
+#
+# Subpartition by hash, no partitions defined, wrong subpartition function
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by key (a+b)
+(partition x1 values less than (1) (subpartition x11, subpartition x12),
+ partition x2 values less than (2) (subpartition x21, subpartition x22));
+
+#
+# Subpartition by hash, no partitions defined, wrong subpartition function
+#
+--error 1437
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by key (a,d)
+(partition x1 values less than (1) (subpartition x11, subpartition x12),
+ partition x2 values less than (2) (subpartition x21, subpartition x22));
+
+#
+# Subpartition by hash, no partitions defined, wrong subpartition function
+#
+--error 1449
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by hash (3+4);
+
+#
+# Subpartition by hash, no partitions defined, wrong subpartition function
+#
+--error 1054
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by hash (a+d)
+(partition x1 values less than (1) (subpartition x11, subpartition x12),
+ partition x2 values less than (2) (subpartition x21, subpartition x22));
+
diff --git a/mysql-test/t/partition_hash.test b/mysql-test/t/partition_hash.test
new file mode 100644
index 00000000000..aa1acfe891f
--- /dev/null
+++ b/mysql-test/t/partition_hash.test
@@ -0,0 +1,77 @@
+#--disable_abort_on_error
+#
+# Simple test for the partition storage engine
+# Taken fromm the select test
+#
+-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Partition by hash, basic
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (a + 2)
+partitions 3
+(partition x1 tablespace ts1,
+ partition x2 tablespace ts2,
+ partition x3 tablespace ts3);
+
+insert into t1 values (1,1,1);
+insert into t1 values (2,1,1);
+insert into t1 values (3,1,1);
+insert into t1 values (4,1,1);
+insert into t1 values (5,1,1);
+
+select * from t1;
+
+update t1 set c=3 where b=1;
+select * from t1;
+
+select b from t1 where a=3;
+select b,c from t1 where a=1 AND b=1;
+
+delete from t1 where a=1;
+delete from t1 where c=3;
+
+select * from t1;
+
+ALTER TABLE t1
+partition by hash (a + 3)
+partitions 3
+(partition x1 tablespace ts1,
+ partition x2 tablespace ts2,
+ partition x3 tablespace ts3);
+select * from t1;
+drop table t1;
+
+#
+# Partition by hash, only one partition
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (a)
+(partition x1);
+
+drop table t1;
+#
+# Partition by key, only one partition
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a)
+(partition x1);
+
+drop table t1;
diff --git a/mysql-test/t/partition_list.test b/mysql-test/t/partition_list.test
new file mode 100644
index 00000000000..6432b8eb747
--- /dev/null
+++ b/mysql-test/t/partition_list.test
@@ -0,0 +1,316 @@
+#--disable_abort_on_error
+#
+# Simple test for the partition storage engine
+# testing list partitioning
+#
+-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Test ordinary list partitioning that it works ok
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null)
+partition by list(a)
+partitions 2
+(partition x123 values in (1,5,6),
+ partition x234 values in (4,7,8));
+
+INSERT into t1 VALUES (1,1,1);
+--error 1030
+INSERT into t1 VALUES (2,1,1);
+--error 1030
+INSERT into t1 VALUES (3,1,1);
+INSERT into t1 VALUES (4,1,1);
+INSERT into t1 VALUES (5,1,1);
+INSERT into t1 VALUES (6,1,1);
+INSERT into t1 VALUES (7,1,1);
+INSERT into t1 VALUES (8,1,1);
+--error 1030
+INSERT into t1 VALUES (9,1,1);
+INSERT into t1 VALUES (1,2,1);
+INSERT into t1 VALUES (1,3,1);
+INSERT into t1 VALUES (1,4,1);
+INSERT into t1 VALUES (7,2,1);
+INSERT into t1 VALUES (7,3,1);
+INSERT into t1 VALUES (7,4,1);
+
+SELECT * from t1;
+SELECT * from t1 WHERE a=1;
+SELECT * from t1 WHERE a=7;
+SELECT * from t1 WHERE b=2;
+
+UPDATE t1 SET a=8 WHERE a=7 AND b=3;
+SELECT * from t1;
+UPDATE t1 SET a=8 WHERE a=5 AND b=1;
+SELECT * from t1;
+
+DELETE from t1 WHERE a=8;
+SELECT * from t1;
+DELETE from t1 WHERE a=2;
+SELECT * from t1;
+DELETE from t1 WHERE a=5 OR a=6;
+SELECT * from t1;
+
+ALTER TABLE t1
+partition by list(a)
+partitions 2
+(partition x123 values in (1,5,6),
+ partition x234 values in (4,7,8));
+SELECT * from t1;
+INSERT into t1 VALUES (6,2,1);
+--error 1030
+INSERT into t1 VALUES (2,2,1);
+
+drop table t1;
+#
+# Subpartition by hash, two partitions and two subpartitions
+# Defined node group
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by list (a)
+subpartition by hash (a+b)
+( partition x1 values in (1,2,3)
+  ( subpartition x11 nodegroup 0,
+    subpartition x12 nodegroup 1),
+  partition x2 values in (4,5,6)
+  ( subpartition x21 nodegroup 0,
+    subpartition x22 nodegroup 1)
+);
+
+INSERT into t1 VALUES (1,1,1);
+INSERT into t1 VALUES (4,1,1);
+--error 1030
+INSERT into t1 VALUES (7,1,1);
+UPDATE t1 SET a=5 WHERE a=1;
+SELECT * from t1;
+UPDATE t1 SET a=6 WHERE a=4;
+SELECT * from t1;
+DELETE from t1 WHERE a=6;
+SELECT * from t1;
+
+drop table t1;
+
+#
+# Subpartition by hash, wrong number of subpartitions
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by list (a)
+subpartition by hash (a+b)
+subpartitions 3
+( partition x1 values in (1,2,4)
+  ( subpartition x11 nodegroup 0,
+    subpartition x12 nodegroup 1),
+  partition x2 values in (3,5,6)
+  ( subpartition x21 nodegroup 0,
+    subpartition x22 nodegroup 1)
+);
+
+#
+# Subpartition by hash, wrong number of subpartitions
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by list (a)
+subpartition by hash (a+b)
+( partition x1 values in (1)
+  ( subpartition x11 nodegroup 0,
+    subpartition xextra,
+    subpartition x12 nodegroup 1),
+  partition x2 values in (2)
+  ( subpartition x21 nodegroup 0,
+    subpartition x22 nodegroup 1)
+);
+
+#
+# Subpartition by list => error
+#
+--error 1064 
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a) 
+subpartition by list (a+b)
+( partition x1
+  ( subpartition x11 engine myisam,
+    subpartition x12 engine myisam),
+   partition x2 
+   ( subpartition x21 engine myisam,
+     subpartition x22 engine myisam)
+);
+
+#
+# Subpartition by list => error
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by list (a+b)
+( partition x1
+  ( subpartition x11 engine myisam values in (0),
+    subpartition x12 engine myisam values in (1)),
+  partition x2
+  ( subpartition x21 engine myisam values in (0),
+    subpartition x22 engine myisam values in (1))
+);
+
+#
+# Partition by list, only one partition => ok
+# 
+CREATE TABLE t1 ( 
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+(partition x1 values in (1,2,9,4) tablespace ts1);
+
+drop table t1;
+#
+# Partition by list, no partition => error
+#
+--error 1441
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a);
+
+#
+# Partition by list, constant partition function not allowed
+#
+--error 1435
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (3+4)
+partitions 2 
+(partition x1 values in (4) tablespace ts1,
+ partition x2 values in (8) tablespace ts2);
+
+#
+# Partition by list, invalid field in function
+#
+--error 1054
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a+d)
+partitions 2
+(partition x1 values in (4) tablespace ts1,
+ partition x2 values in (8) tablespace ts2);
+
+# 
+# Partition by list, no values in definition
+# 
+--error 1429
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+partitions 2
+(partition x1 values in (4),
+ partition x2);
+
+#
+# Partition by list, values less than error
+#
+--error 1430
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+partitions 2
+(partition x1 values in (4),
+ partition x2 values less than (5));
+
+#
+# Partition by list, no values in definition
+#
+--error 1429
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+partitions 2
+(partition x1 values in (4,6),
+ partition x2);
+
+#
+# Partition by list, duplicate values
+#
+--error 1444
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+partitions 2
+(partition x1 values in (4, 12+9),
+ partition x2 values in (3, 21));
+
+#
+# Partition by list, wrong constant result type (not INT)
+#
+--error 1443
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+partitions 2
+(partition x1 values in (4.0, 12+8),
+ partition x2 values in (3, 21));
+
+#
+# Partition by list, missing parenthesis
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+partitions 2
+(partition x1 values in 4,
+ partition x2 values in (5));
+
+
diff --git a/mysql-test/t/partition_order.test b/mysql-test/t/partition_order.test
new file mode 100644
index 00000000000..1e1b3339d64
--- /dev/null
+++ b/mysql-test/t/partition_order.test
@@ -0,0 +1,828 @@
+#--disable_abort_on_error
+#
+# Simple test for the partition storage engine
+# Focuses on tests of ordered index read
+# 
+-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Ordered index read, int type
+#
+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 a couple of tuples
+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;
+
+select * from t1 force index (b) where b > 0 order by b;
+
+drop table t1;
+
+#
+# Ordered index read, unsigned int type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, tiny int type
+#
+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 a couple of tuples
+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;
+
+drop table t1; 
+
+#
+# Ordered index read, unsigned tinyint type
+#
+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 a couple of tuples
+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;
+
+drop table t1; 
+
+#
+# Ordered index read, smallint type
+#
+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 a couple of tuples
+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;
+
+drop table t1; 
+
+#
+# Ordered index read, unsigned smallint type
+#
+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 a couple of tuples
+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;
+
+drop table t1; 
+#
+# Ordered index read, mediumint type
+#
+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 a couple of tuples
+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;
+
+drop table t1; 
+
+#
+# Ordered index read, unsigned int type
+#
+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 a couple of tuples
+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;
+
+drop table t1; 
+
+#
+# Ordered index read, unsigned bigint type
+#
+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 a couple of tuples
+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;
+
+drop table t1; 
+
+#
+# Ordered index read, bigint type
+#
+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 a couple of tuples
+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;
+
+drop table t1; 
+#
+# Ordered index read, bigint type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, float type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, double type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, unsigned double type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, unsigned float type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, double precision type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, unsigned double precision type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, decimal type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, char type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, varchar type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, varchar type limited index size
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, varchar binary type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, tinytext type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, text type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, mediumtext type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, longtext type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, enum type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, set type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, date type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, datetime type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, timestamp type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, time type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, year type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, bit(5) type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+#
+# Ordered index read, bit(15) type
+#
+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 a couple of tuples
+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;
+
+drop table t1;
+
+#
+# Ordered index read, NULL values
+#
+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 a couple of tuples
+INSERT into t1 values (1, 1);
+INSERT into t1 values (5, NULL);
+INSERT into t1 values (2, 5);
+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;
+
+drop table t1;
diff --git a/mysql-test/t/partition_range.test b/mysql-test/t/partition_range.test
new file mode 100644
index 00000000000..e5c1ff795e6
--- /dev/null
+++ b/mysql-test/t/partition_range.test
@@ -0,0 +1,560 @@
+#--disable_abort_on_error
+#
+# Simple test for the partition storage engine
+# Focuses on range partitioning tests
+# 
+-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Partition by range, basic
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+partitions 3
+(partition x1 values less than (5) tablespace ts1,
+ partition x2 values less than (10) tablespace ts2,
+ partition x3 values less than maxvalue tablespace ts3);
+
+# Simple insert and verify test
+INSERT into t1 values (1, 1, 1);
+INSERT into t1 values (6, 1, 1);
+INSERT into t1 values (10, 1, 1);
+INSERT into t1 values (15, 1, 1);
+
+select * from t1;
+
+ALTER TABLE t1
+partition by range (a)
+partitions 3
+(partition x1 values less than (5) tablespace ts1,
+ partition x2 values less than (10) tablespace ts2,
+ partition x3 values less than maxvalue tablespace ts3);
+
+select * from t1;
+
+drop table if exists t1;
+
+#
+# Partition by range, basic
+# No primary key
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null)
+partition by range (a)
+partitions 3
+(partition x1 values less than (5) tablespace ts1,
+ partition x2 values less than (10) tablespace ts2,
+ partition x3 values less than maxvalue tablespace ts3);
+
+# Simple insert and verify test
+INSERT into t1 values (1, 1, 1);
+INSERT into t1 values (6, 1, 1);
+INSERT into t1 values (10, 1, 1);
+INSERT into t1 values (15, 1, 1);
+
+select * from t1;
+
+ALTER TABLE t1
+partition by range (a)
+partitions 3
+(partition x1 values less than (5) tablespace ts1,
+ partition x2 values less than (10) tablespace ts2,
+ partition x3 values less than maxvalue tablespace ts3);
+
+select * from t1;
+
+drop table if exists t1;
+
+#
+# Partition by range, basic
+# No max value used
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+partitions 3
+(partition x1 values less than (5) tablespace ts1,
+ partition x2 values less than (10) tablespace ts2,
+ partition x3 values less than (15) tablespace ts3);
+
+
+# Simple insert and verify test
+INSERT into t1 values (1, 1, 1);
+INSERT into t1 values (6, 1, 1);
+INSERT into t1 values (10, 1, 1);
+--error 1030
+INSERT into t1 values (15, 1, 1);
+
+select * from t1;
+
+ALTER TABLE t1
+partition by range (a)
+partitions 3
+(partition x1 values less than (5) tablespace ts1,
+ partition x2 values less than (10) tablespace ts2,
+ partition x3 values less than (15) tablespace ts3);
+
+select * from t1;
+
+drop table t1;
+
+#
+# Partition by range, only one partition
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+(partition x1 values less than (1));
+
+drop table t1;
+#
+# Partition by range, no partition => error
+#
+--error 1441
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a);
+
+#
+# Partition by range, invalid field in function
+#
+--error 1054
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a+d)
+partitions 2
+(partition x1 values less than (4) tablespace ts1,
+ partition x2 values less than (8) tablespace ts2);
+
+#
+# Partition by range, inconsistent partition function and constants
+#
+--error 1443
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+partitions 2
+(partition x1 values less than (4.0) tablespace ts1,
+ partition x2 values less than (8) tablespace ts2);
+
+#
+# Partition by range, constant partition function not allowed
+#
+--error 1435
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (3+4)
+partitions 2
+(partition x1 values less than (4) tablespace ts1,
+ partition x2 values less than (8) tablespace ts2);
+
+# 
+# Partition by range, no values less than definition
+# 
+--error 1429
+CREATE TABLE t1 ( 
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+partitions 2
+(partition x1 values less than (4),
+ partition x2); 
+
+#
+# Partition by range, no values in definition allowed
+#
+--error 1430
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+partitions 2
+(partition x1 values in (4),
+ partition x2);
+
+#
+# Partition by range, values in error
+#
+--error 1430
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+partitions 2
+(partition x1 values in (4),
+ partition x2 values less than (5));
+
+#
+# Partition by range, missing parenthesis
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+partitions 2
+(partition x1 values less than 4,
+ partition x2 values less than (5));
+
+#
+# Partition by range, maxvalue in wrong place
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+partitions 2
+(partition x1 values less than maxvalue,
+ partition x2 values less than (5));
+
+#
+# Partition by range, maxvalue in several places
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+partitions 2
+(partition x1 values less than maxvalue,
+ partition x2 values less than maxvalue);
+
+#
+# Partition by range, not increasing ranges
+#
+--error 1442
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a)
+partitions 2
+(partition x1 values less than (4),
+ partition x2 values less than (3));
+
+#
+# Partition by range, wrong result type of partition function
+#
+--error 1440
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (sin(a))
+partitions 2
+(partition x1 values less than (4),
+ partition x2 values less than (5));
+
+#
+# Subpartition by hash, two partitions and two subpartitions
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b)) 
+partition by range (a)
+subpartition by hash (a+b) 
+( partition x1 values less than (1)
+  ( subpartition x11,
+    subpartition x12),
+   partition x2 values less than (5)
+   ( subpartition x21,
+     subpartition x22)
+);
+
+SELECT * from t1;
+
+drop table t1;
+
+#
+# Subpartition by hash, two partitions and two subpartitions
+# Defined tablespace, engine and node group
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 tablespace t1 engine myisam nodegroup 0,
+    subpartition x12 tablespace t2 engine myisam nodegroup 1),
+   partition x2 values less than (5)
+   ( subpartition x21 tablespace t1 engine myisam nodegroup 0,
+     subpartition x22 tablespace t2 engine myisam nodegroup 1)
+);
+
+SELECT * from t1;
+
+drop table t1;
+
+#
+# Subpartition by hash, two partitions and two subpartitions
+# Defined tablespace, node group
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 tablespace t1 nodegroup 0,
+    subpartition x12 tablespace t2 nodegroup 1),
+   partition x2 values less than (5)
+   ( subpartition x21 tablespace t1 nodegroup 0,
+     subpartition x22 tablespace t2 nodegroup 1)
+);
+
+SELECT * from t1;
+
+drop table t1;
+
+#
+# Subpartition by hash, two partitions and two subpartitions
+# Defined engine and node group
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 engine myisam nodegroup 0,
+    subpartition x12 engine myisam nodegroup 1),
+   partition x2 values less than (5)
+   ( subpartition x21 engine myisam nodegroup 0,
+     subpartition x22 engine myisam nodegroup 1)
+);
+
+INSERT into t1 VALUES (1,1,1);
+INSERT into t1 VALUES (4,1,1);
+--error 1030
+INSERT into t1 VALUES (5,1,1);
+
+SELECT * from t1;
+
+ALTER TABLE t1
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 engine myisam nodegroup 0,
+    subpartition x12 engine myisam nodegroup 1),
+   partition x2 values less than (5)
+   ( subpartition x21 engine myisam nodegroup 0,
+     subpartition x22 engine myisam nodegroup 1)
+);
+
+SELECT * from t1;
+
+drop table t1;
+
+#
+# Subpartition by hash, two partitions and two subpartitions
+# Defined tablespace, engine
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 tablespace t1 engine myisam,
+    subpartition x12 tablespace t2 engine myisam),
+  partition x2 values less than (5)
+  ( subpartition x21 tablespace t1 engine myisam,
+    subpartition x22 tablespace t2 engine myisam)
+);
+
+INSERT into t1 VALUES (1,1,1);
+INSERT into t1 VALUES (4,1,1);
+--error 1030
+INSERT into t1 VALUES (5,1,1);
+
+SELECT * from t1;
+
+ALTER TABLE t1
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 tablespace t1 engine myisam,
+    subpartition x12 tablespace t2 engine myisam),
+  partition x2 values less than (5)
+  ( subpartition x21 tablespace t1 engine myisam,
+    subpartition x22 tablespace t2 engine myisam)
+);
+
+SELECT * from t1;
+
+drop table t1;
+
+#
+# Subpartition by hash, two partitions and two subpartitions
+# Defined tablespace
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 tablespace t1,
+    subpartition x12 tablespace t2),
+   partition x2 values less than (5)
+  ( subpartition x21 tablespace t1,
+    subpartition x22 tablespace t2)
+);
+
+INSERT into t1 VALUES (1,1,1);
+INSERT into t1 VALUES (4,1,1);
+--error 1030
+INSERT into t1 VALUES (5,1,1);
+
+SELECT * from t1;
+
+ALTER TABLE t1
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 tablespace t1 engine myisam,
+    subpartition x12 tablespace t2 engine myisam),
+  partition x2 values less than (5)
+  ( subpartition x21 tablespace t1 engine myisam,
+    subpartition x22 tablespace t2 engine myisam)
+);
+
+SELECT * from t1;
+
+drop table t1;
+
+#
+# Subpartition by hash, two partitions and two subpartitions
+# Defined engine
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 engine myisam,
+    subpartition x12 engine myisam),
+  partition x2 values less than (5)
+  ( subpartition x21 engine myisam,
+    subpartition x22 engine myisam)
+);
+
+INSERT into t1 VALUES (1,1,1);
+INSERT into t1 VALUES (4,1,1);
+--error 1030
+INSERT into t1 VALUES (5,1,1);
+
+SELECT * from t1;
+
+ALTER TABLE t1
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 engine myisam,
+    subpartition x12 engine myisam),
+  partition x2 values less than (5)
+  ( subpartition x21 engine myisam,
+    subpartition x22 engine myisam)
+);
+
+SELECT * from t1;
+
+drop table t1;
+
+#
+# Subpartition with range => error
+#
+--error 1429
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a+b)
+subpartition by key (a)
+( partition x1
+  ( subpartition x11 engine myisam,
+    subpartition x12 engine myisam),
+   partition x2
+   ( subpartition x21 engine myisam,
+     subpartition x22 engine myisam)
+);
+
+#
+# Subpartition with range => error
+#
+--error 1064
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by key (a)
+subpartition by range (a+b)
+( partition x1
+  ( subpartition x11 engine myisam values less than (0),
+    subpartition x12 engine myisam values less than (1)),
+  partition x2
+  ( subpartition x21 engine myisam values less than (0),
+    subpartition x22 engine myisam values less than (1))
+);
+