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: | |
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
[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.