Strange interaction of SQL_MODE=ANSI and partition by TIMESTAMP

see also http://bugs.mysql.com/bug.php?id=72222

[3 Apr 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 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