Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Won't Fix
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following expression:
SELECT DATEDIFF( '2004-08-07' , DATE ( '0000-00-00 00:00:00'));
returns NULL in 5.1-micro and 732165 in mysql-5.1
In this case it seems safer and backward compatible to allow people to use the 0000-00-00 date in datediff calculations. It is plausible that such an expression can occur in a real life query.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: DATEDIFF with a 0000-00-00 argument produces different result in 5.1-micro
Typo, sorry. I mean the second behavior is tested in the func_time.test. That is, that test verifies that TO_DAYS() of a zero date or datetime is NULL. So, the bug really was "DATE() function allows to bypass no-zero-date protection of TO_DAYS()"
Launchpad bug id: 738096
Re: DATEDIFF with a 0000-00-00 argument produces different result in 5.1-micro
This is an old inconsistency (DATEDIFF() is implemented internally via TO_DAYS())
mysql> select to_days(date('0000-00-00')), to_days('0000-00-00');
----------------------------
----------------------+----------------------------
----------------------+----------------------------
----------------------+The first behavior is tested in the func_time.test.
TO_DAYS() does not want its argument to be a zero date, and it uses TIME_NO_ZERO_DATE to indicate that:
longlong Item_func_to_days::val_int()
{
MYSQL_TIME ltime;
if (get_arg0_date(<ime, TIME_NO_ZERO_DATE))
return 0;
return (longlong) calc_daynr(ltime.year,ltime.month,ltime.day);
}
But DATE() function (which is Item_date_typecast::get_date()) was ignoring the second argument, basically violating caller's requirements. That's why it was possible to get a zero date via DATE(), but not from a string directly.
The bug appeared because DATE() is now fixed to obey caller's requirements. I see two solutions for this bug: