Jacky Yuk wrote:
This is due to a subtle bug in MySQL involving defining an alias for an
expression, and then ORDERing BY an expression that use those aliases.
See http://bugs.mysql.com/bug.php?id=22457 for a description and examples.
That bug is now reported as fixed. The bug log says the fix will be
releases in 4.1.23, 5.0.32, 5.1.14-beta, but these have not been
released yet. You would have to download the sources from BitKeeper and
compile it yourself to get the fix immediately.
This won't work because you can't use aliases in other column
definitions in the select-list. This is a known limitation of SQL.
Aliases can be used in GROUP BY, HAVING, or ORDER BY clauses, and
nowhere else -- not in the WHERE clause, and not in other column defs in
the SELECT-list. As far as I know, all SQL RDBMS products implement
this behavior the same way; it's not just MySQL.
Think of a SQL query as a series of steps, each of which produces an
intermediate result set as input for the next step. The steps are
ordered as follows:
1. FROM (performs joins)
2. WHERE (eliminates rows based on criteria)
3. select-list (evaluates column expressions)
4. GROUP BY (reduces groups of rows to one row for each group)
5. HAVING (eliminates groups based on criteria)
6. ORDER BY (sorts the final result set)
The rule about column aliases is that they're defined in step 3 above,
as expressions in the select-list are evaluated, but the aliases cannot
be used until _after_ step 3.
By the way, you may see from the above sequence that you can use aliases
to eliminate row by putting them in the HAVING clause instead of the
WHERE clause. I see you're doing this in your query. But this means
that step 3 must evaluate the expressions for a great many more rows
than it would have if the conditions had been applied in step 2 to
eliminate unneeded rows. So your queries will probably do a lot of
potentially expensive calculations for rows that then are discarded.
This is why it's a good idea for performance for you to endure the
inconvenience of putting row-restriction criteria in the WHERE clause,
even though you can't use aliases there.
The workaround for this is to repeat the whole expressions in the "d+e
AS f" column in the select-list. Then you can use f in the ORDER BY
clause. In other words, the following does not work:
SELECT 1+1 AS d, 2+2 AS e, d+e AS f ... ORDER BY f;
But this workaround _does_ work:
SELECT 1+1 AS d, 2+2 AS e, 1+1+2+2 AS f ... ORDER BY f;
Repeating the whole expression can be tedious, but it does work.