Question about alias using select

Dear all,

When I try to perform some queries like:

SELECT expr1 AS d, expr2 AS e FROM table HAVING d>10 and e<1000 ORDER
BY d+e;
or
SELECT expr1 AS d, expr2 AS e, d+e AS f FROM table HAVING d>10 and
e<1000 ORDER BY f;

Both of them give me the error unknown column "d"("e"). I would like to
ask, if I don't want to write the query like
"SELECT expr1 AS d, expr2 AS e FROM table HAVING d>10 and e<1000 ORDER
BY expr1+expr2;",
is there any way to perform such query with the results sorted by d+e
?

Thank you so much.

Best regards,
Jacky

Jacky Yuk wrote:
> SELECT expr1 AS d, expr2 AS e FROM table HAVING d>10 and e<1000 ORDER
> BY d+e;

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.

> SELECT expr1 AS d, expr2 AS e, d+e AS f FROM table HAVING d>10 and
> e<1000 ORDER BY f;

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.

Regards,
Bill K.

Dear Bill,

Thank you for the reply. It's very very informatic and helpful. I find
that I can perform such query by subquery:

SELECT d+e AS s from (SELECT expr1 AS d, expr2 AS e FROM table HAVING
d>10 and e<1000) AS t ORDER BY s

But, perhaps, this will make the system even slower..

Thank you very much for the help.

Best regards,
Jacky

Bill Karwin ¼g¹D¡G

> Jacky Yuk wrote:
> > SELECT expr1 AS d, expr2 AS e FROM table HAVING d>10 and e<1000 ORDER
> > BY d+e;
>
> 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.
>
> > SELECT expr1 AS d, expr2 AS e, d+e AS f FROM table HAVING d>10 and
> > e<1000 ORDER BY f;
>
> 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.
>
> Regards,
> Bill K.