moved all partition create table error tests to one test for easier maintenance

8 files changed, 1273 insertions, 1182 deletions

diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 2220fc69602..3136a32d8de 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -87,269 +87,3 @@ partition by list (b*a)
 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 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);
-ERROR 42000: Partitioning can not be used stand-alone in query near 'partition by list (a)
-partitions 3
-(partition x1 values in (1,2,9,4) tablespace ' at line 1
-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;
-ERROR HY000: For LIST partitions each partition must be defined
-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);
-ERROR HY000: The PARTITION function returns the wrong type
-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);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+2)
-partitions 3
-(partition x1 tablespace ts1,
-partition x2 tablespace ts2,
-part' at line 6
-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);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ts1,
-partition x2 tablespace ts2,
-partition x3 tablespace ts3)' at line 8
-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);
-ERROR HY000: Field in list of fields for partition function not found in table
-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);
-ERROR 42S22: Unknown column 'd' in 'partition function'
-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);
-ERROR HY000: The PARTITION function returns the wrong type
-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);
-ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8
-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);
-ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')
-partitions 2
-(partition x1, partition x2)' at line 6
-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));
-ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')
-partitions 2
-(partition x1 values less than (0), partition x2 values less than' at line 6
-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));
-ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')
-partitions 2
-(partition x1 values in (1), partition x2 values in (2))' at line 6
-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));
-ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
-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));
-ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
-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));
-ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
-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);
-ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
-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);
-ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
-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);
-ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
-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);
-ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
-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);
-ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
-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);
-ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
-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));
-ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')' at line 7
-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));
-ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
-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));
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+b)
-(partition x1 values less than (1) (subpartition x11, subpartition x12),
-par' at line 7
-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));
-ERROR HY000: Field in list of fields for partition function not found in table
-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);
-ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
-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));
-ERROR 42S22: Unknown column 'd' in 'partition function'
diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result
new file mode 100644
index 00000000000..cb7c9b8c26e
--- /dev/null
+++ b/mysql-test/r/partition_error.result
@@ -0,0 +1,544 @@
+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);
+ERROR 42000: Partitioning can not be used stand-alone in query near 'partition by list (a)
+partitions 3
+(partition x1 values in (1,2,9,4) tablespace ' at line 1
+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;
+ERROR HY000: For LIST partitions each partition must be defined
+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);
+ERROR HY000: The PARTITION function returns the wrong type
+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);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+2)
+partitions 3
+(partition x1 tablespace ts1,
+partition x2 tablespace ts2,
+part' at line 6
+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);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ts1,
+partition x2 tablespace ts2,
+partition x3 tablespace ts3)' at line 8
+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);
+ERROR HY000: Field in list of fields for partition function not found in table
+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);
+ERROR 42S22: Unknown column 'd' in 'partition function'
+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);
+ERROR HY000: The PARTITION function returns the wrong type
+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);
+ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8
+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);
+ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')
+partitions 2
+(partition x1, partition x2)' at line 6
+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));
+ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')
+partitions 2
+(partition x1 values less than (0), partition x2 values less than' at line 6
+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));
+ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')
+partitions 2
+(partition x1 values in (1), partition x2 values in (2))' at line 6
+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));
+ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
+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));
+ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
+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));
+ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
+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);
+ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
+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);
+ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
+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);
+ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
+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);
+ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
+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);
+ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
+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);
+ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
+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));
+ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')' at line 7
+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));
+ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
+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));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+b)
+(partition x1 values less than (1) (subpartition x11, subpartition x12),
+par' at line 7
+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));
+ERROR HY000: Field in list of fields for partition function not found in table
+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);
+ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
+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));
+ERROR 42S22: Unknown column 'd' in 'partition function'
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by range (a);
+ERROR HY000: For RANGE partitions each partition must be defined
+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);
+ERROR 42S22: Unknown column 'd' in 'partition function'
+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);
+ERROR HY000: VALUES LESS THAN value must be of same type as partition function
+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);
+ERROR HY000: Constant/Random expression in (sub)partitioning function is not allowed
+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);
+ERROR HY000: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition
+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);
+ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
+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));
+ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
+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));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4,
+partition x2 values less than (5))' at line 8
+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));
+ERROR 42000: MAXVALUE can only be used in last partition definition near '))' at line 9
+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);
+ERROR 42000: MAXVALUE can only be used in last partition definition near 'maxvalue)' at line 9
+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));
+ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
+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));
+ERROR HY000: The PARTITION function returns the wrong type
+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)
+);
+ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
+partition x2 values in (3,5,6)
+( subpartition x21 nodegroup 0,
+subpartition x' at line 11
+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)
+);
+ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')
+)' at line 14
+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)
+);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'list (a+b)
+( partition x1
+( subpartition x11 engine myisam,
+subpartition x12 eng' at line 7
+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))
+);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'list (a+b)
+( partition x1
+( subpartition x11 engine myisam values in (0),
+subpar' at line 7
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a);
+ERROR HY000: For LIST partitions each partition must be defined
+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);
+ERROR HY000: Constant/Random expression in (sub)partitioning function is not allowed
+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);
+ERROR 42S22: Unknown column 'd' in 'partition function'
+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);
+ERROR HY000: LIST PARTITIONING requires definition of VALUES IN for each partition
+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));
+ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
+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);
+ERROR HY000: LIST PARTITIONING requires definition of VALUES IN for each partition
+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));
+ERROR HY000: Multiple definition of same constant in list partitioning
+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));
+ERROR HY000: VALUES IN value must be of same type as partition function
+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));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4,
+partition x2 values in (5))' at line 8
diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result
index fc2924c6357..740fec485a3 100644
--- a/mysql-test/r/partition_range.result
+++ b/mysql-test/r/partition_range.result
@@ -108,124 +108,6 @@ CREATE TABLE t1 (
 a int not null,
 b int not null,
 c int not null,
-primary key(a,b))
-partition by range (a);
-ERROR HY000: For RANGE partitions each partition must be defined
-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);
-ERROR 42S22: Unknown column 'd' in 'partition function'
-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);
-ERROR HY000: VALUES LESS THAN value must be of same type as partition function
-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);
-ERROR HY000: Constant/Random expression in (sub)partitioning function is not allowed
-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);
-ERROR HY000: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition
-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);
-ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
-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));
-ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
-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));
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4,
-partition x2 values less than (5))' at line 8
-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));
-ERROR 42000: MAXVALUE can only be used in last partition definition near '))' at line 9
-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);
-ERROR 42000: MAXVALUE can only be used in last partition definition near 'maxvalue)' at line 9
-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));
-ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
-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));
-ERROR HY000: The PARTITION function returns the wrong type
-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) 
@@ -421,35 +303,3 @@ a	b	c
 1	1	1
 4	1	1
 drop table t1;
-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)
-);
-ERROR HY000: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition
-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))
-);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'range (a+b)
-( partition x1
-( subpartition x11 engine myisam values less than (0)' at line 7
diff --git a/mysql-test/t/ndb_partition_key.test b/mysql-test/t/ndb_partition_key.test
index 31d3b63122d..89088c76a85 100644
--- a/mysql-test/t/ndb_partition_key.test
+++ b/mysql-test/t/ndb_partition_key.test
@@ -23,7 +23,7 @@ select * from t1;
 drop table t1;
 
 # only support for partition key on primary key
---error 1453
+--error 1460
 CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b))
   ENGINE = NDB
   PARTITION BY KEY (c);
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index 49a938fa25d..0905413e69d 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -134,361 +134,3 @@ partition by list (b*a)
  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_error.test b/mysql-test/t/partition_error.test
new file mode 100644
index 00000000000..2e1f08abf79
--- /dev/null
+++ b/mysql-test/t/partition_error.test
@@ -0,0 +1,728 @@
+#
+# Simple test for the erroneos create statements using the 
+# partition storage engine
+#
+-- source include/have_partition.inc
+
+#
+# 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 1448
+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 1447
+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 1444
+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 1447
+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 1437
+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 1437
+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 1437
+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 1456
+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 1456
+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 1456
+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 1456
+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 1456
+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 1456
+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 1456
+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 1444
+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 1456
+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));
+
+#
+# Partition by range, no partition => error
+#
+--error 1448
+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 1450
+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 1442
+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 1436
+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 1437
+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 1437
+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 1449
+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 1447
+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, 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, no partition => error
+#
+--error 1448
+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 1442
+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 1436
+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 1437
+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 1436
+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 1451
+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 1450
+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_list.test b/mysql-test/t/partition_list.test
index 6432b8eb747..cb8d06a11ed 100644
--- a/mysql-test/t/partition_list.test
+++ b/mysql-test/t/partition_list.test
@@ -100,86 +100,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,
@@ -190,127 +110,3 @@ 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_range.test b/mysql-test/t/partition_range.test
index e5c1ff795e6..36c97fa4e48 100644
--- a/mysql-test/t/partition_range.test
+++ b/mysql-test/t/partition_range.test
@@ -123,170 +123,6 @@ 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
@@ -519,42 +355,3 @@ subpartition by hash (a+b)
 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))
-);
-