MySQL Bugs: #9669: Ordering on IF function with FROM

Bug #9669	Ordering on IF function with FROM_UNIXTIME function fails
Submitted:	6 Apr 2005 7:33	Modified:	11 Jun 2005 2:11
Reporter:	Taco van den Broek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.3-beta-nt	OS:	Any (any)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Suppose the following table:

CREATE TABLE `foo` (
  `foo_id` int(11) NOT NULL auto_increment,
  `bar` int(10) default NULL,
  `baz` varchar(32) NOT NULL,
  PRIMARY KEY  (`foo_id`)
) ENGINE=InnoDB;

Where the column bar contains unix timestamps and the column baz contains normal text. A query that orders by 
IF(bar IS NULL, '-', FROM_UNIXTIME(bar, '%d-%m-%Y'))
will not (correctly) order the result, whereas ordering by bar is not a problem.

How to repeat:
Create the above table, enter some test data like:
1 | 1112344747 | Here's some text
2 | 1112613381 | Text of some kind
3 | 1112708785 | Rather strange table

And run the following queries:
SELECT foo_id, IF(bar IS NULL, '-', FROM_UNIXTIME(bar, '%d-%m-%Y')) AS qux, baz FROM foo ORDER BY qux ASC;
SELECT foo_id, IF(bar IS NULL, '-', FROM_UNIXTIME(bar, '%d-%m-%Y')) AS qux, baz FROM foo ORDER BY qux DESC;

You'll get the same result twice

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/internals/24852

Integer overflow results in wrong calculated sortlength.

Fixed in 5.0.7, changeset 1.1958

Noted in 5.0.7 changelog.