[MDEV-3607] LP:738096 - DATEDIFF with a 0000-00-00 argument produces different result in 5.1-micro Created: 2011-03-19  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Philip Stoev (Inactive) Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug738096.xml    

 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.

 Comments   
Comment by Sergei Golubchik [ 2011-03-19 ]

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');
--------------------------------------------------+

to_days(date('0000-00-00')) to_days('0000-00-00')

--------------------------------------------------+

0 NULL

--------------------------------------------------+

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(&ltime, 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:

  • allow TO_DAYS() to take a zero date
  • change the status of this bug to be "Won't Fix"
Comment by Sergei Golubchik [ 2011-03-19 ]

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()"

Comment by Rasmus Johansson (Inactive) [ 2011-10-28 ]

Launchpad bug id: 738096

Generated at Thu Feb 08 06:49:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.