Bug #11655 Wrong time is returning from nested selects - maximum time exists
Submitted: 30 Jun 2005 8:12 Modified: 13 Nov 2006 19:15
Reporter: Dejan Goles Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.12 OS:Any (*)
Assigned to: Alexey Kopytov

[30 Jun 2005 8:12] Dejan Goles
Description:
Wrong time is returning from nested selects. There is a Maximum time limit .

How to repeat:
I'll give you an example:

Simple select:

SELECT sec_to_time(3300000) as res ;

Result is:
916:40:00

After nesting:

SELECT * FROM (SELECT sec_to_time(3300000) as res) as nes;

Gives result:

838:59:59

This is a maximum time you can get from nested selects - 3020399 secs  :

Suggested fix:
There is a workaround - getting seconds from nested selects and than converteng them to time var:

SELECT sec_to_time(res) FROM (SELECT 3300000 as res) as nes;
[30 Jun 2005 11:04] Geert Vanderkelen
Hi,

I could reproduce this, also in 5.0, and on Windows.

Actually the subquery results it more correct! 838:59:59 should be the result as this
is the upper limit of the time field.

Thanks for the report!

Geert
[12 Jul 2005 15:42] Igor Babaev
This bug was investigated by Alexander Ivanov.
Here is his report:
As specified in MySQL Reference Manual (Section 11.3.2):

"TIME values may range from '-838:59:59' to '838:59:59'. ... Values that lie outside the TIME range but are otherwise legal are clipped to the closest endpoint of the range. For example, '-850:00:00' and '850:00:00' are converted to '-838:59:59' and '838:59:59'."

The following examples shows that this is not always the case (note that because of that, one and the same expression may return different results in different contexts):

SELECT SEC_TO_TIME(3300000) AS res;
+-----------+
| res       |
+-----------+
| 916:40:00 |
+-----------+
<incorrect>

SELECT * FROM (SELECT SEC_TO_TIME(3300000) AS res) AS nes;
+-----------+
| res       |
+-----------+
| 838:59:59 |
+-----------+
<correct>

SELECT TIME_TO_SEC('916:40:00') as res;
+---------+
| res     |
+---------+
| 3300000 |
+---------+
<incorrect>

CREATE TABLE t1 (t TIME);
INSERT INTO t1 VALUES('916:40:00');
SELECT t, TIME_TO_SEC(t) AS res FROM t1;
+-----------+---------+
| t         | res     |
+-----------+---------+
| 838:59:59 | 3020399 |
+-----------+---------+
<correct>

SELECT MAKETIME(916,40,00) AS res;
+-----------+
| res       |
+-----------+
| 916:40:00 |
+-----------+
<incorrect>

SELECT * FROM (SELECT MAKETIME(916,40,00) AS res) AS nes;
+-----------+
| res       |
+-----------+
| 838:59:59 |
+-----------+
<correct>

SELECT ADDTIME('500:00:00', '416:40:00') AS res;
+-----------+
| res       |
+-----------+
| 916:40:00 |
+-----------+
<incorrect>

SELECT * FROM (SELECT ADDTIME('500:00:00', '416:40:00') AS res) AS nes;
+-----------+
| res       |
+-----------+
| 916:40:00 |
+-----------+
<incorrect>

CREATE TABLE t1 (t TIME);
INSERT INTO t1 VALUES(ADDTIME('500:00:00', '416:40:00'));
SELECT t AS res FROM t1;
+-----------+
| res       |
+-----------+
| 838:59:59 |
+-----------+
<correct>

SELECT SUBTIME('916:40:00', '416:40:00') AS res;
+-----------+
| res       |
+-----------+
| 500:00:00 |
+-----------+
<incorrect - correct result is 838:59:59 - 416:40:00 = 422:19:59>
[13 Jul 2005 10:53] Dejan Goles
Does it mean that maybe in new version this bug is going to be fixed ? How can you calculate hours  for more than 838:40:00 h than?
[13 Jul 2005 17:47] Sinisa Milivojevic
Strpljenje je majka svih vrlina....
[8 Sep 2005 16:56] 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/internals/29514
[6 Jul 2006 8:58] Martin Friebe
I do not think, this si a bug, I think not clipping is correct.

from the documentation at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes, and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. 

string or numeric context. this function returns either a string or a number, not a date. So it does not need to be clipped.

This would also sove the issue of bug #20203

The only question that this leaves is, how to handle sum(time)? which would return a time-value (but which if clipped, would decrease functionality)
[9 Jul 2006 12:57] Martin Friebe
I added this comment to the closed bug #20203 . I did mean to add it here, so I copied it, sorry for the duplication:

Not treating the value as time-type, has however another drawback.
If evaluated in string context, it will change the ordering (in order or group
by) of negative values.
It wil be fine in numeric context.

Also it needs definition as what it will be treaded if there is neither a
nummeric nor string context (as in a subquery).

I believe mysql should in some way keep the ability of handling values, biger
than the time-type. I would think that many applications are using this.

I can thing of the following scenarios:

- treat it as string or integer, default to string if no context.
(for ordering people can cast the value, to int or time; int would work for
bigger values too)

- tread it as string or integer, if a *direct context* of either type is given;
treat it as time otherwise
this would cut the value, in the given example. but would keep it if the result
was directly used in a cast( as string or int), concat() or numeric function

- treat it as time, if the result is smaller than max time, otherwise as string
(or int, if context requires)
(I dont really thing this one is a good option)

I believe the 2nd solution will break more existing applications (even where no
sub-query is used.)
The first solution on the otherhand, will break order by. (but imho is closer to
the current documentation)
[11 Oct 2006 9:57] 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/13466

ChangeSet@1.2235, 2006-10-11 13:56:58+04:00, kaa@polly.local +7 -0
  Merge of fixes for bug #11655 and #20927 to 5.0
  MERGE: 1.1616.2658.18
[13 Nov 2006 19:15] 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.
[29 Nov 2006 10:16] Costin Bereveanu
The issue still exists in 5.0.27:

I have a function that should have returned a TIME value. The return statement actually consists in a TIMEDIFF call. Although the result is big enough (888 hours and something), the value returned by the function is 838:59:59. Reading the above comments, I've managed to fix the problem by returning the number of seconds instead (TIME_TO_SEC(TIMEDIFF())) and then SEC_TO_TIME(function_call) in my select.
This is not very ok though...
[29 Nov 2006 12:50] Costin Bereveanu
The issue also replicates for variables in a function. I tried assigning the TIMEDIFF result to a TIME variable and return that. Same error.
[29 Nov 2006 13:18] Valerii Kravchuk
Bug #24691 was marked as a duplicate of this one.
[6 Dec 2006 0:19] Timothy Smith
Dejan,

Thank you for your extra testing.  Yes, it still exists in 5.0.27; the actual fix didn't make it in that release, and instead is in 5.0.30.

If you want to spend time to save money:

The source code of the Enterprise server (in tarball format) is available at:
ftp://ftp.mysql.com/pub/mysql/src

Access to this FTP site is anonymous and unrestricted.  You will need to compile and test your own binaries.

If you want to spend money to save time:

The pre-built and tested binaries are available to our paying customers. MySQL Enterprise Server subscriptions start at just $595 a year. For more information, see:
http://www.mysql.com/products/enterprise/

Regards,

Timothy
[26 Nov 2007 20:51] Miguel Zabala
I'm using the 5.0.32-Debian_7etch1-log version on a debian etch and the bug still here!!
[6 Feb 2008 14:05] Andrew Trubnikov
mysql --version
mysql  Ver 14.12 Distrib 5.0.44, for pc-linux-gnu (i686) using readline 5.2

i have this bug ((
[11 Feb 2008 23:00] Timothy Smith
Hi, all.

Several people have reported that they still have this bug, even though it is Closed and fixed.  I think they are reporting that the time value is being truncated at 838:59:59, and they want it to be larger than that.

This truncation is intended behavior, and you should notice that you get a warning about data truncation in this case.

The bug was that, in some cases, the value was NOT truncated.  A TIME value in MySQL does not hold > 838:59:59, and expecting it to could lead to undefined results.

The ChangeLog that accompanied this bug fix was written incorrectly, which may have added to the confusion.  It will be corrected in the manual to read something like (my emphasis on the changed part):

> Lack of validation for input and output TIME values resulted in
> several problems: SEC_TO_TIME() *in some cases did not clip
> large values to the TIME range appropriately*; SEC_TO_TIME() treated
> BIGINT UNSIGNED values as signed;
> only truncation warnings were produced when both truncation and
> out-of-range TIME values occurred.

If you believe you have a bug related to this, please file a new bug report, and provide full details so that it will be repeatable.  If you have the exact same symptoms as this bug (SEC_TO_TIME() may return a result greater than 838:59:59 in a subselect), only then should you ammend this present bug report with additional information.

By the way, a comment above from Martin Friebe suggests several possible changes to MySQL to make working with large time values more convenient.  While his suggestions certainly have merit, they must be seen as requests for future improvements, and not as a fix for the present bug.  If you must work with time values which span more than 35 days, you probably should store seconds in an integer format and do basic time manipulations in the application; or, consider if using DATETIME might work.

Best regards,

Timothy
[25 Feb 2010 10:45] Simone Desantis
Still exists in 5.0.51...
[13 Sep 2010 12:12] Jean-Pierre Gay
Still exists in 5.0.77...
[13 Sep 2010 12:13] Jean-Pierre Gay
Still exists in 5.0.77...