Re: BETWEEN a DATE and a hard place

[Rick James]

The use of the function UNIX_TIMESTAMP() was added in 5.5.1:

CREATE TABLE t (c TIMESTAMP) 
PARTITION BY RANGE ( UNIX_TIMESTAMP(c) ) (
    PARTITION p0 VALUES LESS THAN (631148400),
    PARTITION p1 VALUES LESS THAN (946681200),
    PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

(Hence, your 5.5.37 has it.)

> I'd say KEY works perfectly

OK, but only for "WHERE day_of_year = 200", not for a range of days.

> BY RANGE(DAYOFYEAR(date_date))
> WHERE day_of_year = 200
> # ...partitions: p0,p1,p2 =====>SEARCH ALL PARTITIONS? WHY?

Submit a bug at bugs.mysql.com if you like.

> WHERE DAYOFYEAR(date_date) = 200
> WHERE date_date = '2014-03-02'

In general, it is best (at least in a WHERE clause) not to hide a column inside a function.