Bug #20927 sec_to_time treats big unsigned as signed

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

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)

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

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

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

Looks good to me, Alexey.  Thank you for the explanation.

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

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.