| Bug #32770 | LAST_DAY() returns a DATE, but somehow internally keeps track of the TIME. | ||
|---|---|---|---|
| Submitted: | 27 Nov 2007 14:49 | Modified: | 28 Jan 2008 18:46 |
| Reporter: | Tobias Asplund | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.22/5.0.45 | OS: | Any |
| Assigned to: | Tatjana A. Nuernberg | Target Version: | |
| Tags: | Contribution | ||
| Triage: | D2 (Serious) | ||
[27 Nov 2007 18:31]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[28 Nov 2007 19:21]
Sergei Golubchik
patch: http://lists.mysql.com/internals/35187
[10 Dec 2007 8:57]
Tatjana A. 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 9: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 9:19]
Tatjana A. Nuernberg
Thank you for your patch; patches that come with test-cases rock!
[10 Dec 2007 10: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 7:03]
Tatjana A. Nuernberg
pushed to 5.0.54, 5.1.23-opt, 6.0.5-opt
[11 Jan 2008 13:18]
Bugs System
Pushed into 6.0.5-alpha
[11 Jan 2008 13:21]
Bugs System
Pushed into 5.1.23-rc
[11 Jan 2008 13:22]
Bugs System
Pushed into 5.0.56
[28 Jan 2008 18: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.
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...