Bug #32195 DATE() is not working properly
Submitted: 8 Nov 2007 20:40 Modified: 9 Nov 2007 12:02
Reporter: [ name withheld ] Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: CPU Architecture:Any
Tags: date, datetime

[8 Nov 2007 20:40] [ name withheld ] 
Description:
i have a table with a datetime field and date() function doesnt work properly for some reason.
Mysql server and client 5.0.45 intel, x64 (provided by mysql, now own build)
OS is linux with kernel 2.6

the following query has no results:
select username from userdebug2 where date(premium) = "2007-11-14";

and any of the following ones work fine:
select username from userdebug2 where binary(date(premium)) = "2007-11-14";
select username from userdebug2 where trim(date(premium)) = "2007-11-14";
select username from userdebug2 where substring(date(premium),0,10) = "2007-11-14";
select username from userdebug2 where substring(premium,0,10) = "2007-11-14";

How to repeat:
you can reproduce easily repeat the issue with the following sql dump:
http://tequila.sda.bme.hu/~rsc/datebug.sql.gz

select username from userdebug2 where date(premium) = "2007-11-14";
empty
select username from userdebug2 where binary(date(premium)) = "2007-11-14";
3 hits
[9 Nov 2007 12:02] Hartmut Holzgraefe 
Duplicate of bug #29898 which was already fixed in MySQL 5.0.46