Bug #72222 Strange interaction of SQL_MODE=ANSI and partition by TIMESTAMP
Submitted: 3 Apr 2014 14:21 Modified: 3 Apr 2014 16:42
Reporter: Hartmut Holzgraefe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:mysql-5.5.35; mysql-5.6.16 OS:Linux
Assigned to: CPU Architecture:Any

[3 Apr 2014 14:21] Hartmut Holzgraefe
Description:
A table created with ansi-quoted identifiers and partitioned by UNIX_TIMESTAMP() of a timestamp column can't be dumped with mysqldump unless it is opened in table_cache 

On closer look it turned out that if the table was created with SQL_MODE='ANSI_QUOTES' and indeed using ANSI quotes around identifiers
it is not possible to open it with ANSI_QUOTES mode being off
(mysqldump does "SET @@sql_mode='';" as its very first statement
after connecting)

The error message thrown on any attempt to open the table is

ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

although UNIX_TIMESTAMP(timestamp_column) should not be
treated as timezone-dependent in >= 5.5, and isn't in
other circumstances 

How to repeat:
DROP TABLE IF EXISTS t1;
SET @@SQL_MODE='ANSI_QUOTES';
CREATE TABLE "t1" ( "id" int NOT NULL
                  , "t" timestamp NOT NULL
                  , PRIMARY KEY ("id","t") 
) PARTITION BY RANGE ( UNIX_TIMESTAMP("t")) 
( PARTITION p0 VALUES LESS THAN (1371513600)
, PARTITION p1 VALUES LESS THAN (1371600000));

FLUSH TABLES;
SET @@SQL_MODE='';
SHOW CREATE TABLE t1\G -- fails 

SET @@SQL_MODE='ANSI_QUOTES';
SHOW CREATE TABLE t1\G -- works, table is now in open tables cache

SET @@SQL_MODE='';
SHOW CREATE TABLE t1\G -- now works, too, as table is still open / cached

FLUSH TABLES;
SHOW CREATE TABLE t1\G -- table no longer in cache: fails again

Suggested fix:
Remove whatever side effect is causing this ...
[3 Apr 2014 14:27] Hartmut Holzgraefe
The problem seems to be in the ansi qotes in 

  PARTITION BY RANGE ( UNIX_TIMESTAMP("t")) 

only, when removing these everything works fine
[3 Apr 2014 16:42] MySQL Verification Team
Thank you for the bug report. Verified as described.