MySQL Bugs: #31990: MINUTE() and SECOND() return bogus results when used on a DATE

Bug #31990	MINUTE() and SECOND() return bogus results when used on a DATE
Submitted:	31 Oct 2007 15:16	Modified:	11 Jan 2008 15:57
Reporter:	Edam	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Linux
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	date, minute, second

Description:
It appears that using MINUTE() and SECOND() (and perhaps HOUR()?) on a DATE yields some funny results.

The behaviour looks to be as expected. MySQL appears to convert the dates to strings before attempting to extract seconds/minutes. It then tries it's hardest to find minutes/seconds in a string that doesn't really contain any meaningful data and returns it's best-guess along with warnings that the input was truncated in the process.

While this behaviour may seem as expected, it doesn't seem intuitive to me, given the context of a DATE being passed in. I would have expected them to return 0.

The exact version of MySQL I am using is 5.0.32-Debian_7etch1-log, on Debian obviously.

How to repeat:
mysql> SELECT SECOND( CAST( NOW() AS DATE ) ), MINUTE( DATE( NOW() ) );
+---------------------------------+-------------------------+
| SECOND( CAST( NOW() AS DATE ) ) | MINUTE( DATE( NOW() ) ) |
+---------------------------------+-------------------------+
|                               7 |                      20 |
+---------------------------------+-------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '2007-10-31' |
| Warning | 1292 | Truncated incorrect time value: '2007-10-31' |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

Verified, same on 5.1, too.

IMHO HOUR(), MINUTE(), SECOND() on DATE values should return NULL ...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37305

ChangeSet@1.2557, 2007-11-08 06:08:44+01:00, tnurnberg@mysql.com +4 -0
  Bug#31990: MINUTE() and SECOND() return bogus results when used on a DATE
  
  HOUR(), MINUTE(), ... returned spurious results when used on a DATE-cast.
  This happened because DATE-cast object did not overload get_time() method
  in superclass Item. The default method was inappropriate here and
  misinterpreted the data.
  
  Patch adds missing method; get_time() on DATE-casts now returns SQL-NULL
  on NULL input, 0 otherwise. This coincides with the way DATE-columns
  behave.

pushed to 5.0.52, 5.1.23 in opt

Pushed into 5.0.52

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39692

ChangeSet@1.2560, 2007-12-11 10:12:05+01:00, tnurnberg@mysql.com +8 -0
  Bug#31990: MINUTE() and SECOND() return bogus results when used on a DATE
  
  HOUR(), MINUTE(), ... returned spurious results when used on a DATE-cast.
  This happened because DATE-cast object did not overload get_time() method
  in superclass Item. The default method was inappropriate here and
  misinterpreted the data.
  
  Patch adds missing method; get_time() on DATE-casts now returns SQL-NULL
  on NULL input, 0 otherwise. This coincides with the way DATE-columns
  behave.
  
  Also fixes similar bug in Date-Field now.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40250

ChangeSet@1.2674, 2007-12-20 08:15:34+01:00, tnurnberg@mysql.com +1 -0
  Bug#31990: MINUTE() and SECOND() return bogus results when used on a DATE
  
  post-merge fixes

pushed on 12/20 to 5.0.54-opt, 5.1.23-opt, 6.0.5-opt

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.56

Noted in 5.0.56, 5.1.23, 6.0.5 changelogs.

HOUR(), MINUTE(), and SECOND() could return non-zero values for DATE
arguments.