Bug #49187 Nondescriptive error message for function in expression with columns()
Submitted: 29 Nov 2009 19:47 Modified: 14 Jan 2010 10:58
Reporter: Jon Stephens Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5.0 OS:Linux (openSUSE11.1/64bit)
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: error messages, functions, list columns(), partitioning, range columns()

[29 Nov 2009 19:47] Jon Stephens
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."
[29 Nov 2009 22:31] MySQL Verification Team
Thank you for the bug report. Verified as described.
[14 Jan 2010 11:13] Jon Stephens
Mikael is correct but wrong IMO.

By Mikael's reasoning, we should tell the user that he's used an invalid column name.

This would at least provide some clue as to the real nature of the problem.

Generic 'Syntax error' message should only be used as an absolute last resort, not because it's convenient for us to do so.