Bug #20927 sec_to_time treats big unsigned as signed
Submitted: 9 Jul 2006 12:24 Modified: 13 Nov 2006 19:14
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:4.1.20 and 5.0.22 OS:Linux (Linux, freebsd)
Assigned to: Alexey Kopytov CPU Architecture:Any

[9 Jul 2006 12:24] Martin Friebe
Description:
sec_to_time does not handle "unsigned big int" correct. The Value is always treated as signed.

it also returns incorrect results for values float values, bigger than max unsigned bigint.
No warnings are issued for any of this results

How to repeat:
mysql> select sec_to_time(cast(0xffffffffffffffff as unsigned));
+---------------------------------------------------+
| sec_to_time(cast(0xffffffffffffffff as unsigned)) |
+---------------------------------------------------+
| -00:00:01                                         |
+---------------------------------------------------+
1 row in set (0.00 sec)

expected is either the cut-off value:
838:59:59

or preferable a larger "time as string" select 

--- 
with float values
sec_to_time(1844674407370955161500);
+-------------------------------------+
| sec_to_time(1844674407370955161500) |
+-------------------------------------+
| 1011703407:30:07                    |
+-------------------------------------+
1 row in set (0.00 sec)

differnt number same result
mysql> select sec_to_time(184467440737095516150000);
+---------------------------------------+
| sec_to_time(184467440737095516150000) |
+---------------------------------------+
| 1011703407:30:07                      |
+---------------------------------------+

Suggested fix:
this depends on the outcome of bug #11655
[11 Jul 2006 20:09] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.25    |
+-----------+
1 row in set (0.00 sec)

mysql>  select sec_to_time(cast(0xffffffffffffffff as unsigned));
+---------------------------------------------------+
| sec_to_time(cast(0xffffffffffffffff as unsigned)) |
+---------------------------------------------------+
| -00:00:01                                         |
+---------------------------------------------------+
1 row in set (0.03 sec)

mysql> select sec_to_time(1844674407370955161500);
+-------------------------------------+
| sec_to_time(1844674407370955161500) |
+-------------------------------------+
| 512409557:36:10                     |
+-------------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1292
Message: Truncated incorrect DECIMAL value: ''
1 row in set (0.01 sec)

mysql> select sec_to_time(184467440737095516150000);
+---------------------------------------+
| sec_to_time(184467440737095516150000) |
+---------------------------------------+
| -298651792:18:15                      |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1292
Message: Truncated incorrect DECIMAL value: ''
1 row in set (0.01 sec)
[3 Oct 2006 9:51] 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/12992

ChangeSet@1.2541, 2006-10-03 13:51:21+04:00, kaa@polly.local +8 -0
  Fixes a number of problems with time/datetime <-> string conversion functions:
  
  - bug #11655 "Wrong time is returning from nested selects - maximum time exists
  - input and output TIME values were not validated properly in several conversion functions
  - bug #20927 "sec_to_time treats big unsigned as signed"
  - integer overflows were not checked in several functions. As a result, input values like 2^32 or 3600*2^32 were treated as 0
  - BIGINT UNSIGNED values were treated as SIGNED in several functions
  - in cases where both input string truncation and out-of-range TIME value occur, only 'truncated incorrect time value' warning was produced
[3 Oct 2006 17:02] Timothy Smith
Kaamos,

A few minor comments.

I would like to use something like:

#define TIME_MAX_HOUR   838
#define TIME_MAX_MINUTE  59
#define TIME_MAX_SECOND  59

Minor formatting issue (wrong indent):
@@ -2630,7 +2738,7 @@ String *Item_func_timediff::val_str(Stri
 
   calc_time_from_sec(&l_time3, (long) seconds, microseconds);
 
-  if (!make_datetime(l_time1.second_part || l_time2.second_part ?
+  if (!make_datetime_with_warn(l_time1.second_part || l_time2.second_part ?
 		     TIME_MICROSECOND : TIME_ONLY,
 		     &l_time3, str))

And another missing indent:
   if ((null_value=(args[0]->null_value || 
 		   args[1]->null_value ||
-		   args[2]->null_value || 
-		   minute > 59 || minute < 0 || 
-		   second > 59 || second < 0 ||
+		   args[2]->null_value ||
+		   minute < 0 || minute > 59 ||
+       second < 0 || second > 59 ||

Why is this no longer a warning?  Is the %g conversion still done?

@@ -538,10 +543,7 @@ fractional:
        ((str[1] == '-' || str[1] == '+') &&
         (end - str) > 2 &&
         my_isdigit(&my_charset_latin1, str[2]))))
-  {
-    *was_cut= 1;
     return 1;
-  }
 
   if (internal_format_positions[7] != 255)
   {

Otherwise, it looks great!  Thanks,

Tim
[4 Oct 2006 13:13] 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/13069

ChangeSet@1.2541, 2006-10-04 17:13:32+04:00, kaa@polly.local +8 -0
  Fixes a number of problems with time/datetime <-> string conversion functions:
  
  - bug #11655 "Wrong time is returning from nested selects - maximum time exists
  - input and output TIME values were not validated properly in several conversion functions
  - bug #20927 "sec_to_time treats big unsigned as signed"
  - integer overflows were not checked in several functions. As a result, input values like 2^32 or 3600*2^32 were treated as 0
  - BIGINT UNSIGNED values were treated as SIGNED in several functions
  - in cases where both input string truncation and out-of-range TIME value occur, only 'truncated incorrect time value' warning was produced
[4 Oct 2006 23:28] Timothy Smith
Looks good to me, Alexey.  Thank you for the explanation.
[12 Oct 2006 7:21] 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/13554

ChangeSet@1.2236, 2006-10-12 11:21:12+04:00, kaa@polly.local +2 -0
  5.0-specific fixes when merging the fix for bug #11655 and bug #20927 from 4.1
[13 Nov 2006 19:14] Paul Dubois
Noted in 4.1.23, 5.0.30 (not 5.0.29), 5.1.13 changelogs.

Lack of validation for input and output TIME values resulted in
several problems: SEC_TO_TIME() within subqueries incorrectly clipped
large values; SEC_TO_TIME() treated BIGINT UNSIGNED values as signed;
only truncation warnings were produced when both truncation and
out-of-range TIME values occurred.