| Bug #13338 | Inconsistence in date calculation | ||
|---|---|---|---|
| Submitted: | 20 Sep 2005 6:19 | Modified: | 20 Sep 2005 9:18 |
| Reporter: | Athens Yan | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 4.1.14 | OS: | Windows (Windows) |
| Assigned to: | CPU Architecture: | Any | |
[20 Sep 2005 9:18]
Valeriy Kravchuk
Thank you for the bug report. Everything works as you described. Moreover: mysql> select curdate() - 1, cast(curdate() as date) - 1; +---------------+-----------------------------+ | curdate() - 1 | cast(curdate() as date) - 1 | +---------------+-----------------------------+ | 20050919 | 2004 | +---------------+-----------------------------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 4.1.14-nt | +-----------+ 1 row in set (0.00 sec) So, the problem is that in the first expression curdate() result was a number representing date, and numeric calculation was performed, but when you convert or cast to date, date calculation is performed, and 1 is interpreted as 1 year interval. I had not found exact description in the manual for this perticular case, though, as well as for many other implicit datatype changes... I will send a documentation request on this issue. But I do not think it is a serious bug. Moreover, if you want to perform date calculations, you should better use date_add and date_sub functions, that work correct: mysql> select curdate() - 1, date_sub(cast(curdate() as date), interval 1 day); +---------------+---------------------------------------------------+ | curdate() - 1 | date_sub(cast(curdate() as date), interval 1 day) | +---------------+---------------------------------------------------+ | 20050919 | 2005-09-19 | +---------------+---------------------------------------------------+ 1 row in set (0.01 sec)
[21 Sep 2005 11:45]
Athens Yan
Thanks for your explanation of this issue. As like as many database application developers, we expect the expressions CURDATE() is equivalent to CONVERT('<today>', date) or CAST('<today>' as date). In fact, they show us expected result when no any date related calculation involved.
I don't agree with that it just is an undocumented feature. It is clearly a bug. Just a simple logic: if A = B, then A - 1 = B - 1. Please correct me if I am wrong.
Description: When date related data comes from conversion or casting, then all date related calculations are incorrect. How to repeat: 1. Get today in string format '%Y-%m-%d', say <today>. 2. Then, substitute the string into the following SQL command: select (curdate() - 1), convert('<today>', date) - 1; e.g.) if today is 20 Sept 2005, then select (curdate() - 1), convert('2005-09-20', date) - 1 Result of the first component is 2005-09-19, but the second component is 2004.