MySQL Bugs: #42934: Function current_date returns an invalid date.

Bug #42934	Function current_date returns an invalid date.
Submitted:	17 Feb 2009 15:05	Modified:	17 Feb 2009 22:19
Reporter:	Ed Singleton	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	Windows (2003)
Assigned to:		CPU Architecture:	Any

Description:
Received the following error on 2009-02-17.  It should be noted that the query executed successfully.

Warning:   Incorrect date value: '20090187' for column 'period' at row 1
SQLState:  HY000
ErrorCode: 1292
Query 1 of 1 elapsed time (seconds) - Total: 0.063, SQL query: 0.047, Building output: 0.016

How to repeat:
select first_name, last_name, f.name, e.name, d.period, d.hours 
from team a, team_resource b, resource c, timesheet d, task e, project f
where a.name = 'CWS'
and hours > 8
and d.period between (current_date - 30) and current_date
and a.team_id = b.team_id
and c.user_id = b.resource_user_id
and c.user_id = d.resource_user_id
and d.task_id = e.task_id
and e.project_id = f.project_id
order by period

This is not a bug. When you subtract integer from date you subtract integers. Look:

valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.79-debug Source distribution

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

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2009-02-17     | 
+----------------+
1 row in set (0.00 sec)

mysql> select current_date() - 30;
+---------------------+
| current_date() - 30 |
+---------------------+
|            20090187 | 
+---------------------+
1 row in set (0.00 sec)

So, date was implicitly converted to integer and the result may be not a valid date when converted back to date. This is how to do this correctly:

mysql> select current_date() - interval 30 day;
+----------------------------------+
| current_date() - interval 30 day |
+----------------------------------+
| 2009-01-18                       | 
+----------------------------------+
1 row in set (0.00 sec)

Thanks for the info Valeriy.  Our bad.  The user may not have noticed the warning in the past as the query usually returned results.  The function must assume the intent of '- 30' was '- interval 30 day'.