MySQL Bugs: #22529: TIMEDIFF() returns incorrectly in multiple-row SELECTS

Bug #22529	TIMEDIFF() returns incorrectly in multiple-row SELECTS
Submitted:	20 Sep 2006 18:18	Modified:	4 Dec 2006 11:08
Reporter:	Michael Furdyk	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.29-BK, 5.0.24	OS:	Linux (Linux, RHEL4)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	timediff

Description:
Here's the query:

SELECT cu.MemberID, cu.ClassUserType, cu.flGuest, u.Username, u.DisplayName, u.Name, u.DOB, u.smImageName, u.LastLogin, TIMEDIFF( NOW( ) , u.LastLogin ) AS LastLoginDiff
FROM (
tig.TIGedClassUsers AS cu, tig.Users AS u
)
LEFT JOIN tig.UserPW AS upw ON u.UserID = upw.UserID
WHERE cu.MemberID = u.UserID
AND cu.ClassID = '9'
ORDER BY u.DisplayName

It returns a list, but a number of the rows with LastLogins that are different and valid all return the result '838:59:59' -- some rows work fine. Doing just a TIMEDIFF query on that one row -- for '2006-07-22 20:42:40' for example.

How to repeat:
Let me know if you'd like a data sample.

I have isolated the issue: If I remove the ORDER BY clause, the query works fine. So ORDER BY is somehow messing with the TIMEDIFF()

Thank you for the bug report. Yes please provide the data sample, the wrong
result you got and the expected result. Thanks in advance.

the following will show the diff: (and is likely the same as what the original submitter experienced)

select timediff('2004-06-09 00:01','2004-04-08 01:00');
+-------------------------------------------------+
| timediff('2004-06-09 00:01','2004-04-08 01:00') |
+-------------------------------------------------+
| 1487:01:00                                      |
+-------------------------------------------------+
1 row in set (0.00 sec)

select * from (select timediff('2004-06-09 00:01','2004-04-08 01:00') ) x;
+-------------------------------------------------+
| timediff('2004-06-09 00:01','2004-04-08 01:00') |
+-------------------------------------------------+
| 838:59:59                                       |
+-------------------------------------------------+
1 row in set, 1 warning (0.03 sec)

There are several relevant bugs: bug #20293 , bug #20293 , bug #11655 , probably more.

If I understand this correct, the bug is in the first result. Timediff returns a time-value, which should be cut off at 838:59:59. But this only happens if mysql is forced towrite the value to a (temporary) table, like in group by, order by, views, or nested queries

also happens in 4.1

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Yes, Martin is correct. Hopefully this provides the info needed to fix?

Verified just as described by Martin with 5.0.29_BK on Linux:

mysql> select timediff('2004-06-09 00:01','2004-04-08 01:00');
+-------------------------------------------------+
| timediff('2004-06-09 00:01','2004-04-08 01:00') |
+-------------------------------------------------+
| 1487:01:00                                      |
+-------------------------------------------------+
1 row in set (0.08 sec)

mysql> select * from (select timediff('2004-06-09 00:01','2004-04-08 01:00') )
x;
+-------------------------------------------------+
| timediff('2004-06-09 00:01','2004-04-08 01:00') |
+-------------------------------------------------+
| 838:59:59                                       |
+-------------------------------------------------+
1 row in set, 1 warning (0.05 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect time value: '1487:01:00' for column 'timediff('2004-06-09 00:
01','2004-04-08 01:00')' at row 1
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.29-debug |
+--------------+
1 row in set (0.04 sec)

Couldn't repeat using the latest trees.