Bug #42525 TIMEDIFF function
Submitted: 2 Feb 2009 1:45 Modified: 26 Feb 2009 18:43
Reporter: Jason Daiger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.79, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Tags: regression, time, timediff

[2 Feb 2009 1:45] Jason Daiger 
Description:
The result of the TIMEDIFF function comparison against TIME is inconsistent and/or incorrect. The TIMEDIFF function does not take into account the minutes or seconds when compared to the result of the TIME function.  Run the query below in order to see the results.

How to repeat:

SELECT TIMEDIFF(TIME('17:00:00'),TIME( '17:00:00')) = TIME('00:00:00') AS Equals1,
TIMEDIFF(TIME('17:59:00'),TIME( '17:00:00')) = TIME('00:00:00') AS NotEquals1a,
TIMEDIFF(TIME('18:00:00'),TIME( '17:00:00')) = TIME('00:00:00') AS NotEquals1b,
TIMEDIFF(TIME('17:00:00'),TIME( '17:00:00')) = '00:00:00' AS Equals2,
TIMEDIFF(TIME('17:59:00'),TIME( '17:00:00')) = '00:00:00' AS NotEquals2a,
TIMEDIFF(TIME('18:00:00'),TIME( '17:00:00')) = '00:00:00' AS NotEquals2b,
TIMEDIFF(TIME('17:00:00'),TIME( '17:00:00')) = TIME(0) AS Equals3,
TIMEDIFF(TIME('17:59:00'),TIME( '17:00:00')) = TIME(0) AS NotEquals3a,
TIMEDIFF(TIME('18:00:00'),TIME( '17:00:00')) = TIME(0) AS NotEquals3b,
TIME(0) AS Time0, TIME('00:00:00') AS Time00, '00:00:00' AS Time0000
[2 Feb 2009 5:17] Valeriy Kravchuk 
Although I can repeat your results on 5.1.30, I'd like to note that TIMEDIFF function returns correct results:

mysql> SELECT TIMEDIFF(TIME('17:00:00'),TIME( '17:00:00')) AS Equals1,
    -> TIMEDIFF(TIME('17:59:00'),TIME( '17:00:00')) AS NotEquals1a,
    -> TIMEDIFF(TIME('18:00:00'),TIME( '17:00:00')) AS NotEquals1b;
+----------+-------------+-------------+
| Equals1  | NotEquals1a | NotEquals1b |
+----------+-------------+-------------+
| 00:00:00 | 00:59:00    | 01:00:00    |
+----------+-------------+-------------+
1 row in set (0.00 sec)

So, the problem, if any, is related to comparing TIMEDIFF results to TIME results and constants.
[2 Feb 2009 6:38] Sveta Smirnova 
Thank you for the report.

Verified as described:

SELECT TIMEDIFF(TIME('17:00:00'),TIME( '17:00:00')) = TIME('00:00:00') AS Equals1,
TIMEDIFF(TIME('17:59:00'),TIME( '17:00:00')) = TIME('00:00:00') AS NotEquals1a,
TIMEDIFF(TIME('18:00:00'),TIME( '17:00:00')) = TIME('00:00:00') AS NotEquals1b,
TIMEDIFF(TIME('17:00:00'),TIME( '17:00:00')) = '00:00:00' AS Equals2,
TIMEDIFF(TIME('17:59:00'),TIME( '17:00:00')) = '00:00:00' AS NotEquals2a,
TIMEDIFF(TIME('18:00:00'),TIME( '17:00:00')) = '00:00:00' AS NotEquals2b,
TIMEDIFF(TIME('17:00:00'),TIME( '17:00:00')) = TIME(0) AS Equals3,
TIMEDIFF(TIME('17:59:00'),TIME( '17:00:00')) = TIME(0) AS NotEquals3a,
TIMEDIFF(TIME('18:00:00'),TIME( '17:00:00')) = TIME(0) AS NotEquals3b,
TIME(0) AS Time0, TIME('00:00:00') AS Time00, '00:00:00' AS Time0000,
TIMEDIFF(TIME('17:59:00'),TIME( '17:00:00')),
TIMEDIFF(TIME('17:00:00'),TIME( '17:59:00'))
;
Equals1 1
NotEquals1a     1
NotEquals1b     0
Equals2 1
NotEquals2a     0
NotEquals2b     0
Equals3 1
NotEquals3a     1
NotEquals3b     0
Time0   00:00:00
Time00  00:00:00
Time0000        00:00:00
TIMEDIFF(TIME('17:59:00'),TIME( '17:00:00'))    00:59:00
TIMEDIFF(TIME('17:00:00'),TIME( '17:59:00'))    -00:59:00

Bug was introduced after 5.1.30 is released, probably after fix of bug #37553
[4 Feb 2009 19:07] Bugs System 
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/65225

2736 Tatiana A. Nurnberg	2009-02-04
      Bug#42525: TIMEDIFF function
      
      Combination of time-arithmetic result-types led to string of form
      "12:34:56" being parsed as normal int-string, causing parsing to
      be aborted at first unexpected character ":".
      
      Make return-type declaration less broad; handle signedness correctly.
[4 Feb 2009 19:11] Tatiana Azundris Nuernberg 
Above patch fixes.
Setting to "in progress" while investigating further however, to see whether this is "a solution" or "the solution."
Please do not review until set "patch pending" once more, thanks.
[6 Feb 2009 5:47] Timour Katchaounov 
The fix is correct, but it should result in less efficient code
because other code (e.g. sorting) can no longer assume that the
result of Item_str_timefunc has the more compact longlong form
(check all uses of result_as_longlong().

If we assume that indeed having result_as_longlong() return TRUE
is correct, then the way to correct this problem is to instead
implement Item_str_timefunc::val_int(). Adding the following method
works:
longlong val_int() { return val_int_from_decimal(); }

Please check if val_int_from_decimal() indeed always computes the
correct result for Item_func_timediff. If not, then your fix is
correct, otherwise consider using my suggestion.
[6 Feb 2009 17:32] Bugs System 
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/65508

2736 Tatiana A. Nurnberg	2009-02-06
      Bug#42525: TIMEDIFF function
      
      In 37553 we declared longlong results for
      class Item_str_timefunc as per comments/docs,
      but didn't add a method for that. And the
      default just wasn't good enough for some
      cases.
      
      Changeset adds dedicated val_int() to class.
[7 Feb 2009 4:40] Bugs System 
Pushed into 5.0.78 (revid:timothy.smith@sun.com-20090206183839-zahwcwyruitcrt1k) (version source revid:timothy.smith@sun.com-20090206183839-zahwcwyruitcrt1k) (merge vers: 5.0.78) (pib:6)
[9 Feb 2009 22:33] Bugs System 
Pushed into 5.1.32 (revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (version source revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (merge vers: 5.1.32) (pib:6)
[14 Feb 2009 13:00] Bugs System 
Pushed into 6.0.10-alpha (revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy) (version source revid:timothy.smith@sun.com-20090207160057-z28xuwf7hac4sc6h) (merge vers: 6.0.10-alpha) (pib:6)
[17 Feb 2009 14:58] Bugs System 
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090216083408-rmvyaxjt6mk8sg1y) (merge vers: 5.1.32-ndb-6.3.23) (pib:6)
[17 Feb 2009 16:46] Bugs System 
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090216083646-m8st11oj1hhfuuh5) (merge vers: 5.1.32-ndb-6.4.3) (pib:6)
[17 Feb 2009 18:22] Bugs System 
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090211111208-wf0acl7c1vl5653e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)
[26 Feb 2009 18:43] Paul DuBois 
Noted in 5.0.78, 5.1.32, 6.0.10 changelogs.

An optimization introduced for Bug#37553 required an explicit cast to
be added for some uses of TIMEDIFF() because automatic casting could
produce incorrect results. (It was necessary to use
TIME(TIMEDIFF(...)).)