| 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: | |
| 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 | CPU Architecture: | Any |
| Tags: | regression | ||
[20 Jun 2008 17:51]
Ali Moayeri
[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]
Valeriy 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(<ime);
824 value= !*is_null ? TIME_to_ulonglong_datetime(<ime) : 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)
