MySQL Bugs: #66958: impossible to create range partition on a Timestamp with miliseconds

Bug #66958	impossible to create range partition on a Timestamp with miliseconds
Submitted:	25 Sep 2012 15:24	Modified:	25 Sep 2012 22:00
Reporter:	Marc BERGER	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	Mysql server 5.6.6	OS:	Linux (Xubuntu 12.04)
Assigned to:		CPU Architecture:	Any

Description:
Hello,
I am playing around with mysql 5.6.
Im especially interested in this possibility:
A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision.

What i would like is to partition a table using such a timestamp.

Example:

    CREATE TABLE `VALUE_BIS` (
      `TSTAMP` timestamp(3) NOT NULL,
      `ATTRIBUTE_ID` int(11) NOT NULL,
      `VAL` int(11) unsigned NOT NULL,
      PRIMARY KEY (`ATTRIBUTE_ID`,`TSTAMP`)
    )  PARTITION BY RANGE (UNIX_TIMESTAMP(TSTAMP))
         (PARTITION p_s18 VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-18 00:00:00')),
         PARTITION p_s19 VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-19 00:00:00')),
         PARTITION p_Max VALUES LESS THAN (UNIX_TIMESTAMP('2020-09-26 00:00:00' )));

==>ERROR 1491 (HY000): The PARTITION function returns the wrong type 

*I think its normal as it returns unixtimestamp_seconds.microseconds which is not an integer*

If add the miliseconds like

    PARTITION p_s18 VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-18 00:00:00.000')

==> ERROR 1697 (HY000): VALUES value for partition 'p_s18' must have type INT

*OK same story as before*

If i change the definition of my tstamp column and do not use miliseconds

    `TSTAMP` timestamp

==> It works just fine but i wont store my milliseconds and its not what i want.

So i think there is a problem with the timestamp including a fractional part and the partitioning feature

How to repeat:
Create a table with a TIMESTAMP(3) column and try to add a range partition on this column.

Thank you for the bug report. Timestamp(N) isn't supported as documented:

http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

" Incompatible change: In very old versions of MySQL (prior to 4.1), the TIMESTAMP data type supported a display width, which was silently ignored beginning with MySQL 4.1. This is deprecated in MySQL 5.1, and removed altogether in MySQL 5.5. These changes in behavior can lead to two problem scenarios when trying to use TIMESTAMP(N) columns with a MySQL 5.5 or later server:

When importing a dump file (for example, one created using mysqldump) created in a MySQL 5.0 or earlier server into a server from a newer release series, a CREATE TABLE or ALTER TABLE statement containing TIMESTAMP(N) causes the import to fail with a syntax error."

experiencing the same issue with v.5.6.14

why status of this issue is "Not a Bug"?

Please explain what for the link to the "upgrade to v.5.5" article is provided?

IMHO the problem is caused by UNIX_TIMESTAMP function that changed its behavior and for some reason retruns now FRACTIONAL number of seconds. (Bug #72761)

is anyone interested in UNIX_TIMESTAMP with mili/microseconds?

This makes impossible creating partitional this way:

PARTITION BY RANGE (UNIX_TIMESTAMP(col1))
where col1 is timestamp(N)