MySQL Bugs: #60430: Wrong ordering of timediff(a, b)

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

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);

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);

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;

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.

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?

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".

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)

Thank you for the feedback.

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