MySQL Bugs: #26672: Incorrect SEC_TO_TIME() casting in ORDER BY

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:	Windows (Win/Linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	qc

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;

Thank you for the report.

Verified as described. All version are affected.

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.

Pushed in 5.0.40, 5.1.17

Noted in 5.0.40, 5.1.17 changelogs.