MySQL Bugs: #42866: CURDATE cannot report proper date if a subtraction breaches beginning of year.

Bug #42866	CURDATE cannot report proper date if a subtraction breaches beginning of year.
Submitted:	15 Feb 2009 16:49	Modified:	15 Feb 2009 19:46
Reporter:	Rik Davis	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux (Fedora Core 7)
Assigned to:		CPU Architecture:	Any

Description:
I was in the processes of trying to perform a SELECT query to confirm that a date range I wish to delete from my table fell within the specified range I was desiring. When I ran the query, I noticed results that just didn't seem to add up to what, at the very least, should have been present.

So, I then proceeded to create just a simple SELECT query that would show me just the result of a CURDATE() expression. I was extremely shocked to notice that the result that was returned fully explained the rather odd discrepancy that I saw earlier.

So, I believe what I have come across is an actual bug in date handling/processing. I'll provide a replication example.

How to repeat:
The best way to replicate this bug is simply to perform the basic query here;
(NOTE: This was performed on 2009-02-15)

SELECT CURDATE() - 60;

Your resulting answer will be -> 2009-01-55

If this is not a bug then I will be extremely eager for the explanation that clears this up as I cannot imagine why January should be shown with any days in excess of 31 days.

Suggested fix:
As for suggested fixes...I have no idea how you folks are processing your dates, but I should guess that they're all being based on some degree of Unix timestamps and then converted prior to providing a return value back from a call like CURDATE or what have you.

Be that the case, I should think that a simple subtraction should not be hindered by year boundries then.

Just corrected missing space in the synopsis field for search visibility.

Sorry, but this is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html. When you mix date and number, date is treated as number:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

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

mysql> SELECT CURDATE() - 60;
+----------------+
| CURDATE() - 60 |
+----------------+
|       20090155 |
+----------------+
1 row in set (0.01 sec)

mysql> SELECT CURDATE() - 0;
+---------------+
| CURDATE() - 0 |
+---------------+
|      20090215 |
+---------------+
1 row in set (0.00 sec)

If you want to subtract 60 days from current date, please, use DATE_SUB (see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-sub):

mysql> SELECT date_sub(CURDATE(), interval 60 day);
+--------------------------------------+
| date_sub(CURDATE(), interval 60 day) |
+--------------------------------------+
| 2008-12-17                           |
+--------------------------------------+
1 row in set (0.03 sec)

Thank you for that response.

Perhaps the manual was exactly why I felt it was a bug, because it specifically highlighted the usage I displayed here which was why I chose that particular methodology.

I am not one to submit bug reports without having read through enough documentation prior to the submission. Forgive me if my interpretation of the manual appeared to denote this sort of usage.

Thank you

The manual is highlighting

  CURDATE() - INTERVAL 60 DAY

not

  CURDATE() - 60

If you'll find that the latter is documented to work, don't hesitate to submit it as a bug (or reopen this one).