MySQL Bugs: #24349: date/time function behavior with zero '0000-00-00' date

Bug #24349	date/time function behavior with zero '0000-00-00' date
Submitted:	16 Nov 2006 0:50
Reporter:	Timothy Smith	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Prompted by bug #23653.

MySQL isn't consistent in how the zero date is handled by date functions.

How to repeat:
See attachment for test case.

Suggested fix:
These results are wrong:

-- This is bug #23653
LAST_DAY('0000-00-00 00:00:00')
0000-00-58

EXTRACT(WEEK FROM '0000-00-00 00:00:00')
                               613462402

I'm not sure if we should allow this, or return NULL as with other
TIMEDIFF results:

TIMEDIFF('0000-00-00 00:00:00', '0000-00-00 00:00:00')
00:00:00

I think these should be NULL, not 0:

QUARTER('0000-00-00 00:00:00')
                             0

EXTRACT(QUARTER FROM '0000-00-00 00:00:00')
                                          0

For DATE_FORMAT, I'll comment on each format separately.

These are probably OK:

DATE_FORMAT('0000-00-00 00:00:00', '%c')
0

-- Not positive about this one
DATE_FORMAT('0000-00-00 00:00:00', '%D')
0th

DATE_FORMAT('0000-00-00 00:00:00', '%d')
00

DATE_FORMAT('0000-00-00 00:00:00', '%e')
0

DATE_FORMAT('0000-00-00 00:00:00', '%m')
00

DATE_FORMAT('0000-00-00 00:00:00', '%Y')
0000

DATE_FORMAT('0000-00-00 00:00:00', '%y')
00

These should be NULL:

DATE_FORMAT('0000-00-00 00:00:00', '%a')
Sat

DATE_FORMAT('0000-00-00 00:00:00', '%j')
4294236812

DATE_FORMAT('0000-00-00 00:00:00', '%U')
613462402

DATE_FORMAT('0000-00-00 00:00:00', '%u')
613462402

DATE_FORMAT('0000-00-00 00:00:00', '%V')
01

DATE_FORMAT('0000-00-00 00:00:00', '%v')
613462402

DATE_FORMAT('0000-00-00 00:00:00', '%W')
Saturday

DATE_FORMAT('0000-00-00 00:00:00', '%w')
6

DATE_FORMAT('0000-00-00 00:00:00', '%X')
0001

DATE_FORMAT('0000-00-00 00:00:00', '%x')
0000

I guess the TIME formats are OK returning 0, but it would be cleaner to
return NULL for them.  It seems wrong to return 12 for %h, for example;
yet, why return NULL for %h, but not for %H?  They are:

DATE_FORMAT('0000-00-00 00:00:00', '%f')
000000

DATE_FORMAT('0000-00-00 00:00:00', '%H')
00

DATE_FORMAT('0000-00-00 00:00:00', '%h')
12

DATE_FORMAT('0000-00-00 00:00:00', '%I')
12

DATE_FORMAT('0000-00-00 00:00:00', '%i')
00

DATE_FORMAT('0000-00-00 00:00:00', '%k')
0

DATE_FORMAT('0000-00-00 00:00:00', '%l')
12

DATE_FORMAT('0000-00-00 00:00:00', '%p')
AM

DATE_FORMAT('0000-00-00 00:00:00', '%r')
12:00:00 AM

DATE_FORMAT('0000-00-00 00:00:00', '%S')
00

DATE_FORMAT('0000-00-00 00:00:00', '%s')
00

DATE_FORMAT('0000-00-00 00:00:00', '%T')
00:00:00

These are missing a Warning:

DATE_FORMAT('0000-00-00 00:00:00', '%b')
NULL

DATE_FORMAT('0000-00-00 00:00:00', '%M')
NULL

These results are missing a Warning:

ADDTIME('0000-00-00 00:00:00', '00:00:00')
NULL

ADDTIME('0000-00-00 00:00:00', '01:02:03')
NULL

MONTHNAME('0000-00-00 00:00:00')
NULL

SUBTIME('0000-00-00 00:00:00', '00:00:00')
NULL

SUBTIME('0000-00-00 00:00:00', '01:02:03')
NULL

TIMEDIFF('0000-00-00 00:00:00', '1981-02-03 04:05:06')
NULL

TIMEDIFF('1981-02-03 04:05:06', '0000-00-00 00:00:00')
NULL

TIMESTAMP('0000-00-00 00:00:00', '00:00:00')
NULL

TIMESTAMP('0000-00-00 00:00:00', '01:02:03')
NULL

test of date/time functions with 0000-00-00 00:00:00

Attachment: zero-date.sql (text/x-sql), 13.10 KiB.

When in doubt, return NULL.  So:

Return NULL for everything except the following DATE_FORMAT formats:

DATE_FORMAT('0000-00-00 00:00:00', '%c')
DATE_FORMAT('0000-00-00 00:00:00', '%D')
DATE_FORMAT('0000-00-00 00:00:00', '%d')
DATE_FORMAT('0000-00-00 00:00:00', '%e')
DATE_FORMAT('0000-00-00 00:00:00', '%m')
DATE_FORMAT('0000-00-00 00:00:00', '%Y')
DATE_FORMAT('0000-00-00 00:00:00', '%y')

And, ensure that a Warning is emitted for all cases.

Regards,

Timothy

Any progress on this ? Why isn't the connector returning DateTime.Min like it's doing in the explicit operator implementation for DateTime ?

On a closer look this doesn't really apply to me. Sorry.