Bug #60430 Wrong ordering of timediff(a, b)
Submitted: 11 Mar 2011 9:30 Modified: 10 Aug 2011 19:32
Reporter: Tomas Telensky Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.77-log OS:Linux
Assigned to: CPU Architecture:Any

[11 Mar 2011 9:30] Tomas Telensky
Description:
The ordering of result of timediff() function is wrong, see:

select TimeEnd, TimeStart, timediff(TimeEnd, TimeStart) from ObsLists where timediff(TimeEnd, TimeStart) > '0:00:00' order by timediff(TimeEnd, TimeStart);

+----------+-----------+------------------------------+
| TimeEnd  | TimeStart | timediff(TimeEnd, TimeStart) |
+----------+-----------+------------------------------+
| 18:00:00 | 08:00:00  | 10:00:00                     |
| 17:30:00 | 06:45:00  | 10:45:00                     |
| 17:00:00 | 07:00:00  | 10:00:00                     |
| 18:00:00 | 08:00:00  | 10:00:00                     |
| 20:15:00 | 09:40:00  | 10:35:00                     |
| 15:40:00 | 05:00:00  | 10:40:00                     |
| 16:30:00 | 06:15:00  | 10:15:00                     |
| 16:38:00 | 05:53:00  | 10:45:00                     |
| 18:00:00 | 07:00:00  | 11:00:00                     |
| 20:00:00 | 08:00:00  | 12:00:00                     |
| 20:00:00 | 08:00:00  | 12:00:00                     |
| 19:30:00 | 06:30:00  | 13:00:00                     |
| 22:00:00 | 07:00:00  | 15:00:00                     |
+----------+-----------+------------------------------+
13 rows in set (0.02 sec)

Maybe there is some workaround, but according to what is written in the doc:

"TIMEDIFF() returns expr1 – expr2 expressed as a time value."

one expects the results to be of type TIME. Anyway, if the resulting type is string, the ordering is wrong anyway!!!

How to repeat:
select TimeEnd, TimeStart, timediff(TimeEnd, TimeStart) from ObsLists where timediff(TimeEnd, TimeStart) > '0:00:00' order by timediff(TimeEnd, TimeStart);
[11 Mar 2011 12:56] Valeriy Kravchuk
Please, check if the same problem happens with a newer version, 5.0.91 at least. If it is still repeatable, please, send the output of:

explain select TimeEnd, TimeStart, timediff(TimeEnd, TimeStart) from ObsLists where timediff(TimeEnd, TimeStart) > '0:00:00' order by timediff(TimeEnd, TimeStart);
[11 Mar 2011 13:16] Tomas Telensky
Sorry, none of our providers has newer version of mysql.

But you can try it, this is code for reproduce (I just created a table that corresponds to the first 3 lines of prevous bug output):

create table ObsLists ( TimeStart TIME, TimeEnd TIME );

insert into ObsLists values ('08:00:00', '18:00:00'), ('06:45:00', '17:30:00'), ('07:00:00', '17:00:00');

select TimeEnd, TimeStart, timediff(TimeEnd, TimeStart) from ObsLists where timediff(TimeEnd, TimeStart) > '0:00:00' order by timediff(TimeEnd, TimeStart) limit 0, 10;
[11 Mar 2011 13:17] Tomas Telensky
By the way, some time ago I contributed to this bug:

http://bugs.mysql.com/bug.php?id=29822

which is very similar - but this time it is even worse, the ordering is wrong even if the time is compared like strings.
[11 Mar 2011 14:15] Valeriy Kravchuk
For me everything looks correct in your last test case on current 5.0.x:

macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.93-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table ObsLists ( TimeStart TIME, TimeEnd TIME );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into ObsLists values ('08:00:00', '18:00:00'), ('06:45:00',
    -> '17:30:00'), ('07:00:00', '17:00:00');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select TimeEnd, TimeStart, timediff(TimeEnd, TimeStart) from ObsLists
    -> where timediff(TimeEnd, TimeStart) > '0:00:00' order by
    -> timediff(TimeEnd, TimeStart) limit 0, 10;
+----------+-----------+------------------------------+
| TimeEnd  | TimeStart | timediff(TimeEnd, TimeStart) |
+----------+-----------+------------------------------+
| 18:00:00 | 08:00:00  | 10:00:00                     | 
| 17:00:00 | 07:00:00  | 10:00:00                     | 
| 17:30:00 | 06:45:00  | 10:45:00                     | 
+----------+-----------+------------------------------+
3 rows in set (0.00 sec)

Do I miss something?
[11 Apr 2011 23:00] Bugs System
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".
[26 Jul 2011 8:56] Everest Huang
I have similar problem for Mysql version 5.0.77, it doesn't even correct as string comparison:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 313
Server version: 5.0.77 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  select id as 'id',timediff(complete_date,start_date) as td from `zb_async_operation` order by td desc;
+----------------------------------+----------+
| id                               | td       |
+----------------------------------+----------+
| 402892463150e87d0131514634d3010d | 00:02:42 | 
| 40289246314ab6b901314ac44cb701b2 | 00:00:08 | 
| 40289246314a9de701314aa0595601a3 | 00:00:01 | 
| 40289246314a9de701314a9fdbb7019e | 00:00:01 | 
| 40289246314a9de701314a9fd1d9019b | 00:00:00 | 
| 40289246314a9de701314a9fbccd0198 | 00:00:02 | 
| 40289246314a97f601314a999285019e | 00:00:01 | 
| 40289246314a97f601314a9988d8019b | 00:00:00 | 
| 40289246314a97f601314a9973d10198 | 00:00:02 | 
| 40289246314ab6b901314ac489a901b5 | 00:00:01 | 
| 40289246314ab6b9013150cbdccb05cf | 00:00:02 | 
| 40289246314ac1b701314ac4218d0352 | 00:00:01 | 
| 40289246314ac1b701314ac4075b027c | 00:00:00 | 
| 40289246314ac1b701314ac3fa690279 | 00:00:01 | 
| 40289246314ac1b701314ac3b88c01a3 | 00:00:00 | 
| 40289246314ac1b701314ac3afe8019e | 00:00:01 | 
| 40289246314ac1b701314ac3a9f5019b | 00:00:00 | 
| 40289246314ac1b701314ac394810198 | 00:00:03 | 
| 40289246314ab6b9013150cc7e7405d2 | 00:03:34 | 
| 40289246314a8c3201314a8db880019e | 00:00:00 | 
| 40289246314a8c3201314a8daea8019b | 00:00:01 | 
| 40289246314a8c3201314a8d9a770198 | 00:00:03 | 
| 40289246314531430131453310fa0350 | 00:00:01 | 
| 4028924631453143013145330431027a | 00:00:00 | 
| 402892463145314301314532fdd70277 | 00:00:00 | 
| 402892463145314301314532ee7701a1 | 00:00:00 | 
| 402892463145314301314532e8bd019e | 00:00:00 | 
| 402892463145314301314532e268019b | 00:00:00 | 
| 402892463145314301314532ccdf0198 | 00:00:03 | 
| 4028924631451940013145334aa3057d | 00:00:01 | 
| 40289246314a58aa01314a5cd25d0198 | 00:00:03 | 
| 40289246314a58aa01314a5cea54019b | 00:00:00 | 
| 40289246314a862001314a87ba08019e | 00:00:00 | 
| 40289246314a862001314a87b055019b | 00:00:01 | 
| 40289246314a862001314a879be30198 | 00:00:02 | 
| 40289246314a7dbf01314a804bc1019e | 00:00:01 | 
| 40289246314a7dbf01314a803cd7019b | 00:00:01 | 
| 40289246314a7dbf01314a7f48250198 | 00:00:18 | 
| 40289246314a58aa01314a5cf64401a1 | 00:00:00 | 
| 40289246314a58aa01314a5cf026019e | 00:00:00 | 
| 4028924631451940013145332aa7054f | 00:00:04 | 
+----------------------------------+----------+
41 rows in set (0.01 sec)
[10 Aug 2011 19:32] Sveta Smirnova
Thank you for the feedback.

I can not repeat this with version 5.0.92 too. Please upgrade.