Bug #26672 Incorrect SEC_TO_TIME() casting in ORDER BY
Submitted: 27 Feb 2007 9:34 Modified: 19 Mar 2007 17:13
Reporter: Felix Geerinckx (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.27, 5.1.14, 4.1 BK, 5.2-falcon OS:Microsoft Windows (Win/Linux)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: qc

[27 Feb 2007 9:34] Felix Geerinckx
Description:
When using the result of SEC_TO_TIME() for times over 24 hours in an ORDER BY, either directly or through a column alias, the row are sorted (wrongly) as strings.

How to repeat:
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (secs INT UNSIGNED NOT NULL, ts TIME);
INSERT INTO t1 (secs) VALUES 
(100000), (0), (100), (1000000),(10000), (1000), (10);
UPDATE t1 SET ts = SEC_TO_TIME(secs);

-- Correct ORDER
SELECT secs, ts
FROM t1
ORDER BY ts DESC;

-- Incorrect ORDER (ordered as strings)
SELECT secs, SEC_TO_TIME(secs) AS X
FROM t1
ORDER BY X DESC;

-- Same here
SELECT secs, ts
FROM t1
ORDER BY SEC_TO_TIME(secs) DESC;
[27 Feb 2007 12:15] Sveta Smirnova
Thank you for the report.

Verified as described. All version are affected.
[7 Mar 2007 12:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21358

ChangeSet@1.2433, 2007-03-07 14:51:45+02:00, gkodinov@macbook.gmz +3 -0
  Bug #26672:
   DATE/DATETIME values are out of the currently supported
   4 basic value types (INT,STRING,REAL and DECIMAL).
   So expressions (not fields) of compile type DATE/DATETIME are 
   generally considered as STRING values. This is not so
   when they are compared : then they are compared as 
   INTEGER values.
   But the rule for comparison as INTEGERS must be checked
   explicitly each time when a comparison is to be performed.
   filesort is one such place. However there the check was 
   not done and hence the expressions (not fields) of type 
   DATE/DATETIME were sorted by their string representation.
   Fixed to compare them as INTEGER values for filesort.
[17 Mar 2007 18:05] Alexey Botchkov
Pushed in 5.0.40, 5.1.17
[19 Mar 2007 17:13] Paul Dubois
Noted in 5.0.40, 5.1.17 changelogs.