MySQL Bugs: #49591: Conditional definition of Column list partitions has the wrong version

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	CPU Architecture:	Any
Tags:	column list, conditional, partition, partitioning

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

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>

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

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.

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

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

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

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.

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)

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20091221153659-d7bt0fh6mhhclxuf) (version source revid:vvaintroub@mysql.com-20091218211356-mvp3vmiia9yogqe1) (pib:15)

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.