Bug #37553 MySql Error Compare TimeDiff & Time
Submitted: 20 Jun 2008 17:51 Modified: 8 Dec 2008 17:02
Reporter: Ali Moayeri Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.25-rc, 5.0.50, 5.0.62 OS:Any
Assigned to: Tatiana Azundris Nuernberg
Tags: regression
Triage: Triaged: D2 (Serious)

[20 Jun 2008 17:51] Ali Moayeri
Description:
Error in mysql timediff & time in Sql Query

ScreenShot Old & new Version
http://www.zarrinsms.com/mysql_error.PNG

How to repeat:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  select timediff('2008-06-18 20:10:10',now()),time('00:00:00');
+---------------------------------------+------------------+
| timediff('2008-06-18 20:10:10',now()) | time('00:00:00') |
+---------------------------------------+------------------+
| -50:07:25                             | 00:00:00         |
+---------------------------------------+------------------+
1 row in set (0.00 sec)

mysql>  select timediff('2008-06-18 20:10:10',now())>time('00:00:00');
+--------------------------------------------------------+
| timediff('2008-06-18 20:10:10',now())>time('00:00:00') |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
[20 Jun 2008 18:45] Ali Moayeri
Result in Mysql 5.0
E:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12340
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select timediff('2008-06-18 20:10:10',now()),time('00:00:00');
+---------------------------------------+------------------+
| timediff('2008-06-18 20:10:10',now()) | time('00:00:00') |
+---------------------------------------+------------------+
| -50:28:27                             | 00:00:00         |
+---------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> select timediff('2008-06-18 20:10:10',now())>time('00:00:00');
+--------------------------------------------------------+
| timediff('2008-06-18 20:10:10',now())>time('00:00:00') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
[21 Jun 2008 19:04] Valerii Kravchuk
Thank you for a bug report. Verified just as described. The problem is that (as explained in the manual) result of time - time is treated as double, but for some reason sign is NOT taken into account in the recent versions:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 -T test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.62-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select (time('-73:42:12') - time('00:00:00')) > 0;
Field   1:  `(time('-73:42:12') - time('00:00:00')) > 0`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      BINARY NUM

+--------------------------------------------+
| (time('-73:42:12') - time('00:00:00')) > 0 |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.02 sec)

mysql> select time('-73:42:12') - time('00:00:00');
Field   1:  `time('-73:42:12') - time('00:00:00')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 6
Decimals:   31
Flags:      BINARY NUM

+--------------------------------------+
| time('-73:42:12') - time('00:00:00') |
+--------------------------------------+
|                               734212 |
+--------------------------------------+
1 row in set (0.03 sec)

mysql> select time('-73:42:12');
Field   1:  `time('-73:42:12')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TIME
Collation:  binary (63)
Length:     27
Max_length: 9
Decimals:   31
Flags:      BINARY

+-------------------+
| time('-73:42:12') |
+-------------------+
| -73:42:12         |
+-------------------+
1 row in set (0.00 sec)

mysql> select time('-73:42:12') + 0;
Field   1:  `time('-73:42:12') + 0`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 6
Decimals:   31
Flags:      BINARY NUM

+-----------------------+
| time('-73:42:12') + 0 |
+-----------------------+
|                734212 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select time('73:42:12') + 0;
Field   1:  `time('73:42:12') + 0`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 6
Decimals:   31
Flags:      BINARY NUM

+----------------------+
| time('73:42:12') + 0 |
+----------------------+
|               734212 |
+----------------------+
1 row in set (0.00 sec)

Note same results for last two statements. This is a bug.
[25 Jun 2008 14:42] Tatiana Azundris Nuernberg
#0  Item_func_timediff::val_str (this=0x8f2cb50, str=0xb520473c) at item_timefunc.cc:2859
#1  0x081fbda6 in Item::get_time (this=0x8f2cb50, ltime=0xb520478c) at item.cc:940
#2  0x08234bb1 in get_time_value (thd=0x8f01228, item_arg=0x8f2cd38, cache_arg=0x8f2cd64, warn_item=0x8f2cc48, is_null=0xb5204827) at item_cmpfunc.cc:823
#3  0x08228084 in Arg_comparator::compare_datetime (this=0x8f2cd38) at item_cmpfunc.cc:1043
#4  0x0823744b in Arg_comparator::compare (this=0x8f2cd38) at item_cmpfunc.h:71
#5  0x0822c82f in Item_func_gt::val_int (this=0x8f2ccc0) at item_cmpfunc.cc:1667
#6  0x081ec610 in Item::send (this=0x8f2ccc0, protocol=0x8f01534, buffer=0xb5204c6c) at item.cc:5265
#7  0x08299033 in select_send::send_data (this=0x8f2ceb8, items=@0x8f02628) at sql_class.cc:1550
#8  0x083411ba in JOIN::exec (this=0x8f2ced0) at sql_select.cc:1669
#9  0x0833e667 in mysql_select (thd=0x8f01228, rref_pointer_array=0x8f0268c, tables=0x0, wild_num=0, fields=@0x8f02628, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x8f2ceb8, unit=0x8f0231c, select_lex=0x8f02594) at sql_select.cc:2358
#10 0x0834333c in handle_select (thd=0x8f01228, lex=0x8f022c0, result=0x8f2ceb8, setup_tables_done_option=0) at sql_select.cc:269
#11 0x082be7bc in execute_sqlcom_select (thd=0x8f01228, all_tables=0x0) at sql_parse.cc:4765
#12 0x082c048e in mysql_execute_command (thd=0x8f01228) at sql_parse.cc:2073
#13 0x082c9110 in mysql_parse (thd=0x8f01228, inBuf=0x8f2c870 "select timediff('2008-06-18 20:10:10',now())>time('00:00:00')", length=61, found_semicolon=0xb520626c) at sql_parse.cc:5645
#14 0x082c9cf6 in dispatch_command (command=COM_QUERY, thd=0x8f01228, packet=0x8f19531 "", packet_length=61) at sql_parse.cc:1137
#15 0x082cae61 in do_command (thd=0x8f01228) at sql_parse.cc:794
#16 0x082b85b5 in handle_one_connection (arg=0x8f01228) at sql_connect.cc:1115

item_cmpfunc.cc
 822   {
 823     *is_null= item->get_time(&ltime);
 824     value= !*is_null ? TIME_to_ulonglong_datetime(&ltime) : 0;
 825   }

We convert to ulonglong; sign is lost.
get_time_value() also ulonglong. So are the vars in compare_datetime.
[2 Oct 2008 13:30] 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/55077

2685 Tatiana A. Nurnberg	2008-10-01
      Bug#37553: MySql Error Compare TimeDiff & Time
      
      some calculations lost the sign of negative time values
[26 Nov 2008 8:34] 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/59874

2725 Tatiana A. Nurnberg	2008-11-26
      Bug#37553: MySql Error Compare TimeDiff & Time
      
      We pretended that TIMEDIFF() would always return positive results;
      this gave strange results in comparisons of the TIMEDIFF(low,hi)<TIME(0)
      type that rendered a negative result, but still gave false in comparison.
      We also inadvertantly dropped the sign when converting times to
      decimal.
      
      CAST(time AS DECIMAL) handles signs of the times correctly.
      TIMEDIFF() marked up as signed. Time/date comparison code switched to
      signed for clarity.
[27 Nov 2008 19:44] Tatiana Azundris Nuernberg
queued for 5.0.74, 5.1.31, 6.0.9 in -bugteam
[28 Nov 2008 3:59] James Day
Testing with:

SELECT 
TIMEDIFF('12:00:00','13:00:00') <= TIME(0) AS LessThanResultShouldBeTrue,
TIMEDIFF('14:00:00','13:00:00') <= TIME(0) AS LessThanResultShouldBeFalse,
TIMEDIFF('12:00:00','13:00:00') >= TIME(0) AS GreaterThanResultShouldBeFalse,
TIMEDIFF('14:00:00','13:00:00') >= TIME(0) AS GreaterThanResultShouldBeTrue;

shows that 5.0.27 through 5.0.46 give the correct result while and 5.0.50 and 5.0.50sp1 give the wrong one. 5.0.48 (withdrawn from release) not tested. Possible candidate for introducing this is the fix for bug #31221 in 5.0.50. 5.0.50 was released on 19 October 2007 so this bug has been around for at least 13 months.

James Day, MySQL Senior Support Engineer, Sun Microsystems
[2 Dec 2008 13:01] Bugs System
Pushed into 5.0.74  (revid:azundris@mysql.com-20081126082817-2ffapw6rugvg9vmo) (version source revid:matthias.leich@sun.com-20081128184708-uyjg2dfrj2va5o3d) (pib:5)
[3 Dec 2008 20:19] Paul Dubois
Noted in 5.0.74 changelog.

TIMEDIFF() was erroneously treated as always returning a positive
result. Also, CAST() of TIME values to DECIMAL dropped the sign of
negative values.

Resetting report to NDI pending push into 5.1.x, 6.0.x.
[8 Dec 2008 10:21] Bugs System
Pushed into 5.1.31  (revid:azundris@mysql.com-20081126082817-2ffapw6rugvg9vmo) (version source revid:ingo.struewing@sun.com-20081127193954-eli419ld8v3cyg8s) (pib:5)
[8 Dec 2008 11:31] Bugs System
Pushed into 6.0.9-alpha  (revid:azundris@mysql.com-20081126082817-2ffapw6rugvg9vmo) (version source revid:ingo.struewing@sun.com-20081127214152-x1avx1b1cyfshx1a) (pib:5)
[8 Dec 2008 17:02] Paul Dubois
Noted in 5.1.31, 6.0.9 changelogs.
[19 Jan 2009 11:27] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:05] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:11] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)