Bug #32770 LAST_DAY() returns a DATE, but somehow internally keeps track of the TIME.
Submitted: 27 Nov 2007 13:49 Modified: 28 Jan 2008 17:46
Reporter: Tobias Asplund Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.22/5.0.45 OS:Any
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Tags: Contribution

[27 Nov 2007 13:49] Tobias Asplund
Description:
LAST_DAY() takes an input of a date or datetime and returns the last day of the month as a DATE.

When doing calculations on this date, however, it's different from a pure DATE.

How to repeat:
mysql> SELECT LAST_DAY('2007-11-27');
+------------------------+
| LAST_DAY('2007-11-27') |
+------------------------+
| 2007-11-30             | 
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT LAST_DAY('2007-11-27') - INTERVAL 1 SECOND;
+--------------------------------------------+
| LAST_DAY('2007-11-27') - INTERVAL 1 SECOND |
+--------------------------------------------+
| 2007-11-29 23:59:59                        | 
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LAST_DAY(CURDATE());
+---------------------+
| LAST_DAY(CURDATE()) |
+---------------------+
| 2007-11-30          | 
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT LAST_DAY(CURDATE()) - INTERVAL 1 SECOND;
+-----------------------------------------+
| LAST_DAY(CURDATE()) - INTERVAL 1 SECOND |
+-----------------------------------------+
| 2007-11-29 23:59:59                     | 
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LAST_DAY(NOW());
+-----------------+
| LAST_DAY(NOW()) |
+-----------------+
| 2007-11-30      | 
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT LAST_DAY(NOW()) - INTERVAL 1 SECOND;
+-------------------------------------+
| LAST_DAY(NOW()) - INTERVAL 1 SECOND |
+-------------------------------------+
| 2007-11-30 08:48:20                 | 
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2007-11-27 08:48:26 | 
+---------------------+
1 row in set (0.00 sec)

Suggested fix:
If it is a true DATETIME, it shouldn't display it as a DATE...
[27 Nov 2007 17:31] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[28 Nov 2007 18:21] Sergei Golubchik
patch: http://lists.mysql.com/internals/35187
[10 Dec 2007 7:57] Tatiana Azundris Nuernberg
The docs are not 100 % clear on this, but in the interest of the principle of the least surprise and given that we fixed this sort of thing for Bug#32180 and Bug#31990, I'll call this a bug.
Setting myself as first reviewer as I'm not the author of the patch.
[10 Dec 2007 8:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39624

ChangeSet@1.2597, 2007-12-10 09:17:18+01:00, tnurnberg@mysql.com +3 -0
  Bug#32770: LAST_DAY() returns a DATE, but somehow internally keeps track of the TIME.
  
  LAST_DAY() says it returns a DATE, not a DATETIME, but didn't zero the time fields.
  Adapted from a patch kindly supplied by Claudio Cherubino.
[10 Dec 2007 8:19] Tatiana Azundris Nuernberg
Thank you for your patch; patches that come with test-cases rock!
[10 Dec 2007 9:33] Claudio Cherubino
It's a pleasure to work with such a great group of people!
This was my first patch, I hope to provide more in the future.
[20 Dec 2007 6:03] Tatiana Azundris Nuernberg
pushed to  5.0.54, 5.1.23-opt, 6.0.5-opt
[11 Jan 2008 12:18] Bugs System
Pushed into 6.0.5-alpha
[11 Jan 2008 12:21] Bugs System
Pushed into 5.1.23-rc
[11 Jan 2008 12:22] Bugs System
Pushed into 5.0.56
[28 Jan 2008 17:46] Paul Dubois
Noted in 5.0.56, 5.1.23, 6.0.5 changelogs.

The LAST_DAY() function returns a DATE value, but internally the
value did not have the time fields zeroed and calculations involving
the value could return incorrect results.