DATEDIFF with a 0000-00-00 argument produces different result in 5.1-micro

[Philip Stoev]

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.

[Sergei Golubchik (sergii)]

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"

[Sergei Golubchik (sergii)]

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