Description:
It is not allowed to use functions inside the COLUMNS() clause with PARTITION BY RANGE COLUMNS() or PARTITION BY LIST COLUMNS().
However, the error message provided when you try to do this does not tell you that this is the reason the CREATE TABLE or ALTER TABLE failed.
How to repeat:
Start with this:
mysql> create table rc2 (c1 date not null) partition by range columns(c1) (partition p0 values less than (maxvalue));
Query OK, 0 rows affected (0.10 sec)
mysql> drop table rc2;
Query OK, 0 rows affected (0.00 sec)
mysql> create table rc2 (c1 date not null) partition by range columns(year(c1)) (partition p0 values less than (maxvalue));
ERROR 1064 (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 '(c1)) (partition p0 values less than (maxvalue))' at line 1
Error message not helpful, should be
mysql> create table rc2 (c1 date not null) partition by list columns(c1) (partition p0 values in ('2009-11-29'));
Query OK, 0 rows affected (0.05 sec)
mysql> drop table rc2;
Query OK, 0 rows affected (0.00 sec)
mysql> create table rc2 (c1 date not null) partition by list columns(year(c1)) (partition p0 values in ('2009-11-29'));
ERROR 1064 (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 '(c1)) (partition p0 values in ('2009-11-29'))' at line 1
Same problem here: error message doesn't tell you what the problem actually is.
This also happens with ALTER TABLE:
mysql> drop table rc2;
Query OK, 0 rows affected (0.00 sec)
mysql> create table rc2 (c1 date not null) partition by range columns(c1) (partition p0 values less than (maxvalue));
Query OK, 0 rows affected (0.05 sec)
mysql> alter table rc2 partition by list columns(c1) (partition p0 values in ('2009-11-29'));
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table rc2 partition by range columns(c1) (partition p0 values less than (maxvalue));
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table rc2 partition by list columns(year(c1)) (partition p0 values in ('2009-11-29'));
ERROR 1064 (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 '(c1)) (partition p0 values in ('2009-11-29'))' at line 1
Again, the reason for the failure is not made clear.
Suggested fix:
When the user attempts to use a function inside COLUMNS(), we should return an appropriate error message/code.
The closest existing error I could find was this one:
Error: 1521 SQLSTATE: HY000 (ER_PARTITION_FUNCTION_FAILURE)
Message: Partition function not supported in this version for this handler
However, I'd prefer to see something more specific for the message, such as:
"Use of functions within COLUMNS() is not permitted."