Why doesn't ORDER BY take CAST value into account?

[Philippe A]

ORDER BY does not seem to take the result of CAST function into account. I wonder if this is a bug.

Here's an example. Let's create a single column table with two records. One record is null. The other one is not.

create table test (c int(10) unsigned default null);
insert into test set c=null;
insert into test set c=1;

At this point, the result of a sort on c is well known. But what if we translate null values into numbers, like so:

select ifnull(c, cast(-1 as unsigned integer)) as v from test order by v asc;

The result I get is this:
+----------------------+
| v |
+----------------------+
| 18446744073709551615 |
| 1 |
+----------------------+

No need to say, it's wrong.

If I use an arbitrary value instead of CAST, the ORDER BY will operate as expected. This is my workaround.

Using CAST( -1 AS UNSIGNED INTEGER ) is useful for getting the largest possible value of a field.

What do you all think about that? Thanks for letting me know!