Bug #29822 Wrong ordering of timediff() result

• Submitted: 16 Jul 2007 14:08
• Modified: 17 Jul 2007 9:24
• Reporter: Tomas Telensky
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.45-log
• OS: Linux
• Assigned to: Assigned Account
• CPU Architecture: Any

[16 Jul 2007 14:08] Tomas Telensky

Description:
Result of timediff() function is not sorted numerically, but as strings:

select *, timediff(a, b) as td
...
order by td

will e.g. produce output like this:

383:27:53
402:46:13
42:57:44
50:22:00
56:11:36
655:27:34
69:40:10

I'm not sure if this bug isn't a feature, if it is, then it is a very weird feature :-) (time should be time, not string, with all operations defined correctly - incl. sorting).

How to repeat:
select *, timediff(a, b) as td
...
order by td

[16 Jul 2007 15:10] Valeriy Kravchuk

Thank you for a bug report. Verified just as described:

mysql> select timediff(now(), '2007-07-04 10:10:01') < timediff(now(), '2007-07-14 00:00:00') t;
Field   1:  `t`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      BINARY NUM

+------+
| t    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select timediff(now(), '2007-07-04 10:10:01'), timediff(now(), '2007-07-14 00:00:00') t;
Field   1:  `timediff(now(), '2007-07-04 10:10:01')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TIME
Collation:  binary (63)
Length:     23
Max_length: 9
Decimals:   6
Flags:      BINARY

Field   2:  `t`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TIME
Collation:  binary (63)
Length:     23
Max_length: 8
Decimals:   6
Flags:      BINARY

+----------------------------------------+----------+
| timediff(now(), '2007-07-04 10:10:01') | t        |
+----------------------------------------+----------+
| 295:57:05                              | 66:07:06 |
+----------------------------------------+----------+

So, results are of type TIME but compared as strings, not as periods of time.

[17 Jul 2007 9:24] Evgeny Potemkin

Duplicate of the bug#29555.