MySQL Bugs: #32159: Date() function bug

Bug #32159	Date() function bug
Submitted:	7 Nov 2007 8:11	Modified:	8 Dec 2007 2:10
Reporter:	Roberto Cano Ramos	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There is a problem with the date() function, that can be overcome using the trim(date(xxx)) see example.

How to repeat:
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE TEST;
Database changed
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t (
-> ID int(11) NOT NULL auto_increment,
-> dt datetime default NULL,
-> PRIMARY KEY (ID)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (dt, ID) VALUES (NULL, 1),(now(), 2),(now(), 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> Select * from t where date(dt)=current_date;
Empty set (0.00 sec)

HERE WE TRY WITH THE TRIM !!!!!
================================

mysql> Select * from t where trim(date(dt))=current_date;
+----+---------------------+
| ID | dt |
+----+---------------------+
| 2 | 2007-11-06 20:27:26 |
| 3 | 2007-11-06 20:27:26 |
+----+---------------------+
2 rows in set (0.00 sec)

mysql>

EVEN BETTER LOOK AT THIS!!!!
============================

mysql> delete from t where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> Select * from t where date(dt)=current_date;
+----+---------------------+
| ID | dt |
+----+---------------------+
| 2 | 2007-11-06 20:27:26 |
| 3 | 2007-11-06 20:27:26 |
+----+---------------------+
2 rows in set (0.00 sec)

Thank you for a problem report. It looks like a duplicate of bug #31709. Please, check.

Note that bug #31709 is verified on 5.0.45, but it is NOT repeatable on current 5.0-BK code.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I can verify that this bug exists when comparing date(foo) against current_date() . The trim(date(foo)) fix is verified as working.