Bug #84369 lack of domain validation when adding partitions
Submitted: 29 Dec 2016 11:10 Modified: 29 Dec 2016 11:21
Reporter: 帅 Bang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.6, 5.7.17 OS:Linux
Assigned to: CPU Architecture:Any

[29 Dec 2016 11:10] 帅 Bang
Description:
mysql> use test;
Database changed
mysql>  create table longshao(c1 int unsigned, c2 int unsigned) partition by range(c1) (partition p0 values less than (10), partition p1 values less than (20), partition p3 values less than (-1));

ERROR 1563 (HY000): Partition constant is out of partition function domain

OK, this is quite good. well, let's move on.

mysql> create table longshao(c1 int unsigned, c2 int unsigned) partition by range(c1) (partition p0 values less than (10), partition p1 values less than (20));
Query OK, 0 rows affected (0.01 sec)

mysql> alter table longshao add partition (partition p3 values less than(-1));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Wow, it succeed. IHMO, it should have been failed.

How to repeat:
create table longshao(c1 int unsigned, c2 int unsigned) partition by range(c1) (partition p0 values less than (10), partition p1 values less than (20), partition p3 values less than (-1));

create table longshao(c1 int unsigned, c2 int unsigned) partition by range(c1) (partition p0 values less than (10), partition p1 values less than (20));

alter table longshao add partition (partition p3 values less than(-1));

Suggested fix:
alter table hualong3 add partition (partition p3 values less than(-1));  failed with a message 'Partition constant is out of partition function domain'
[29 Dec 2016 11:21] Umesh Shastry
Hello Bang,

Thank you for the report and test case.
Verified as described with 5.7.17 build.

Thanks,
Umesh
[29 Dec 2016 11:26] Umesh Shastry
-- 5.7.17

root@localhost [test]> create table longshao(c1 int unsigned, c2 int unsigned) partition by range(c1) (partition p0 values less than (10), partition p1 values less than (20), partition p3 values less than (-1));
ERROR 1563 (HY000): Partition constant is out of partition function domain
root@localhost [test]> show errors;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1563 | Partition constant is out of partition function domain |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]> create table longshao(c1 int unsigned, c2 int unsigned) partition by range(c1) (partition p0 values less than (10), partition p1 values less than (20));
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> alter table longshao add partition (partition p3 values less than(-1));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> show create table longshao\G
ERROR 1563 (HY000): Partition constant is out of partition function domain
root@localhost [test]> show errors;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1563 | Partition constant is out of partition function domain |
| Error | 1033 | Incorrect information in file: './test/longshao.frm'   |
+-------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [test]> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.17                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.17-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.5               |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)