Bug #47772 Partition by RANGE COLUMN_LIST not accepting YEAR(), etc for definition

• Submitted: 1 Oct 2009 18:54
• Modified: 1 Oct 2009 21:24
• Reporter: Patrick Crews
• Status: Not a Bug
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.1/5.4
• OS: Any
• Assigned to: Mikael Ronström
• CPU Architecture: Any
• Tags: partitioning, RANGE COLUMN_LIST

[1 Oct 2009 18:54] Patrick Crews

Description:
The new RANGE COLUMN_LIST partitioning type is not accepting YEAR(), TO_SECONDS(), etc as a valid definition.  Normal RANGE partitioning *does* accept this.

We receive this error:
failed: 1064: 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 '(`date`))

The table description below:
CREATE TABLE `PP_E` (
`date` date,
`varchar_10_key` varchar(10),
`varchar_5` varchar(5),
`varchar_10` varchar(10),
`datetime` datetime,
`datetime_key` datetime,
`int_signed` int signed,
`date_key` date,
`varchar_5_key` varchar(5),
`int_signed_key` int signed,
        key (`varchar_10_key` ),
key (`datetime_key` ),
key (`date_key` ),
key (`varchar_5_key` ),
key (`int_signed_key` )) ENGINE=myisam /*!50100 PARTITION BY range column_list( year(date))
                          ( partition p0 values less than (column_list(1000)),
                          partition p1 values less than (column_list(maxvalue))) */

How to repeat:
Try this DDL:
This can be successful if:
1)  You change to RANGE partitioning (no COLUMN_LIST usage)
2)  You remove the YEAR() function

CREATE TABLE `PP_E` (
`date` date,
`varchar_10_key` varchar(10),
`varchar_5` varchar(5),
`varchar_10` varchar(10),
`datetime` datetime,
`datetime_key` datetime,
`int_signed` int signed,
`date_key` date,
`varchar_5_key` varchar(5),
`int_signed_key` int signed,
        key (`varchar_10_key` ),
key (`datetime_key` ),
key (`date_key` ),
key (`varchar_5_key` ),
key (`int_signed_key` )) ENGINE=myisam /*!50100 PARTITION BY range column_list( year(date))
                          ( partition p0 values less than (column_list(1000)),
                          partition p1 values less than (column_list(maxvalue))) */;

Suggested fix:
Ensure that RANGE COLUMN_LIST partitioning behaves the same as RANGE partitioning or make sure that it is documented.

[1 Oct 2009 21:24] Mikael Ronström

The new partitioning variant only accepts a list of fields, not a list of
functions.

So the old variant still works:
partition by range (function(a,b))
and the new variant is
partition by range column_list(a,b)

so column_list(..) can only contain a list of fields
in the table.