Bug #49591 Conditional definition of Column list partitions has the wrong version
Submitted: 10 Dec 2009 16:03 Modified: 3 Jan 2010 17:40
Reporter: Giuseppe Maxia (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.5 OS:Any
Assigned to: Mikael Ronström
Tags: column list, conditional, partition, partitioning
Triage: Needs Triage: D2 (Serious)

[10 Dec 2009 16:03] Giuseppe Maxia
Description:
when defining a range partition with the COLUMNS() clause and then asking for the CREATE TABLE statement, the server produces a conditional portion with 5.1 as minimum version to execute the SQL statement. 
But the minimum version in this case is 5.5, not 5.1

Example:

CREATE TABLE t1 (dt date)  PARTITION BY RANGE COLUMNS (dt)  (PARTITION p0 VALUES LESS THAN ('2009-01-01'), partition p1 values less than (MAXVALUE));

show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE  COLUMNS(dt)
(PARTITION p0 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */

It should be /*!50500 PARTITION BY RANGE  COLUMNS(dt) ... /*

How to repeat:
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (dt date)  PARTITION BY RANGE COLUMNS (dt)  (PARTITION p0 VALUES LESS THAN ('2009-01-01'), partition p1 values less than (MAXVALUE));

show create table t1\G

Suggested fix:
change the minimum version value
[10 Dec 2009 16:09] Miguel Solorzano
Thank you for the bug report. Verified as described:

miguel@quetzal2:~$ dbs/5.6/bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.0-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE t1 (dt date)  PARTITION BY RANGE COLUMNS (dt)  (PARTITION p0 VALUES LESS
    -> THAN ('2009-01-01'), partition p1 values less than (MAXVALUE));
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE  COLUMNS(dt)
(PARTITION p0 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql>
[16 Dec 2009 18:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94554
[17 Dec 2009 10:16] Mattias Jonsson
This should also be considered for backporting to 5.1 due to bug#42849, which adds UNIX_TIMESTAMP as a partitioning function, to keep the timestamp data type to still be allowed to use in the partitioning expression.
[17 Dec 2009 17:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94813

2926 Mikael Ronstrom	2009-12-17
      BUG#49591, review fixes
[17 Dec 2009 17:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94815

2927 Mikael Ronstrom	2009-12-17 [merge]
      Merge to mysql-trunk-bugfixing BUG#49591
[17 Dec 2009 17:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94816
[17 Dec 2009 17:47] Mikael Ronström
I fixed the column list partitioning to use at least 5.5.
However one should also ensure that new functions introduced
also set the proper version number, in particular I fixed this
for the new function TO_SECONDS which was introduced in 5.5.
[21 Dec 2009 15:39] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091221153807-80nxoli1tw1z9bxn) (version source revid:vvaintroub@mysql.com-20091218212246-frpxcpzra8lalib0) (merge vers: 6.0.14-alpha) (pib:15)
[21 Dec 2009 15:40] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091221153538-ifi3mxf3y10ozxin) (version source revid:mikael@mysql.com-20091217173910-ecubxjy07if7gc05) (merge vers: 5.5.0-beta) (pib:15)
[21 Dec 2009 15:41] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091221153659-d7bt0fh6mhhclxuf) (version source revid:vvaintroub@mysql.com-20091218211356-mvp3vmiia9yogqe1) (pib:15)
[3 Jan 2010 17:40] Jon Stephens
Documented in the 5.5.1 changelog as follows:

        When SHOW CREATE TABLE was invoked for a table that had been
        created using the COLUMNS keyword or the TO_SECONDS() function,
        the output contained the wrong MySQL version number in
        conditional comments.

Documented in the 6.0.14 changelog as follows:

        When SHOW CREATE TABLE was invoked for a table that had been
        created using the TO_SECONDS() function, the output contained
        the wrong MySQL version number in conditional comments.

(Due to lack of support for COLUMNS partitioning in 6.0 tree.)

Closed.