Professional Web Applications Themes

Question about alias using select - MySQL

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 ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Question about alias using select

    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.

    Regards,
    Bill K.
    Bill Guest

  3. #3

    Default Re: Question about alias using select

    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 gDG
     
    >
    > 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.
    >
    > Regards,
    > Bill K.[/ref]

    Jacky Guest

Similar Threads

  1. SQL Select question
    By megalith in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 24th, 06:44 PM
  2. question about select
    By Papo in forum PERL Beginners
    Replies: 9
    Last Post: February 19th, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139