Bug #19972 day_add not accepted as valid hash function
Submitted: 20 May 2006 17:36 Modified: 24 May 2006 22:19
Reporter: avi weiss Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.9 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[20 May 2006 17:36] avi weiss
trying to partition table into 365 partitions, one for each day of the year. I want to use the datetime field in the table with date_add to provide a monotonically increasing integer for the hash. 

however, the following returns "invalid hash function" and I dont know why as it returns an integer:

ALTER table pagelog partition by hash (date_add(pl_datetime, interval 0 day)+0) partitions 365

How to repeat:
go to query browser, create a table pagelog with datetime field "pl_datetime" and try to use the above alter command.
[20 May 2006 18:22] Hartmut Holzgraefe
The result of "DATE_ADD(...)+0" may look like an integer 
but the result type is actually float, so you have to convert
it to an integer value:

   partition by hash(cast(date_add(...)+0 as unsigned));
[24 May 2006 22:19] avi weiss
Date functions are somewhat inconsistent, both amongst themselves, and the syntax for typing:

1. date() + 0 should all return the same yyyymmdd format as curdate() + 0, which I already filed as a bug, and seem to have consensus on.

2. I'm failing to see why date() + 0, date_add() + 0 should be returned as a float. The number will never have a decimal component. Additionally, the docs dont point out the fact that they would be a float in section 12.5. In fact sample in partitioning section on hash functions appears to insinuate date functions returning type "integer", so Im thinking it makes more sense to have them as integer and let those that need them as float cast it to float.