Bug #63856 unix_timestamp works in "create table" but not "alter table"

• Submitted: 26 Dec 2011 22:59
• Modified: 26 Dec 2011 23:07
• Reporter: Jed Walker
• Status: Not a Bug
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.5.14
• OS: Linux (Ubuntu)
• CPU Architecture: Any

[26 Dec 2011 22:59] Jed Walker

Description:
I create a table as follows:

CREATE TABLE jed_part (
  loop_name   varchar(45) DEFAULT NULL,
  loop_id     integer NOT NULL,
  ts          timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  obj_id      integer NOT NULL,
  loc_id      integer DEFAULT NULL,
  param_name  varchar(100) NOT NULL,
  param_value varchar(100) DEFAULT NULL,
  KEY jed_part_ndx1 (loop_id,obj_id),
  KEY jed_part_ndx2 (loop_id,ts),
  KEY jed_part_ndx3 (ts)
) ENGINE=InnoDB
PARTITION BY RANGE ( UNIX_TIMESTAMP(ts) ) (
    PARTITION p20111225 VALUES LESS THAN ( UNIX_TIMESTAMP('2011-12-25 00:00:00') ),
    PARTITION p20111226 VALUES LESS THAN ( UNIX_TIMESTAMP('2011-12-26 00:00:00') )
);

I then tried to add a partition:

alter table jed_part add partition (partition p20111227 values then than (unix_timestamp('2011-12-27 00:00:00'));

and got:

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 'then than (unix_timestamp('2011-12-28 00:00:00'))' at line 1

but finally figured out you can do:

alter table jed_part add partition (partition p20111227 values less than (1324969200));

I've looked at the syntax and unless I'm just missing it, it would appear that you can use unix_timestamp in the "create table" but not in the "alter table"

How to repeat:
See above (unless I'm doing something stupid, I am new to MySQL - Oracle since 1997)

Suggested fix:
enable same syntax in alter table add partition

[26 Dec 2011 23:07] Jed Walker

argghhhhh, stupid me, stared at it too long I guess. just missing a right-paren!

[27 Dec 2011 19:29] MySQL Verification Team

also "then than" instead of "less than" :)