On Thursday 21 February 2008 2:17 pm, Ed Leafe wrote:
> On Feb 21, 2008, at 4:13 PM, Adrian Klaver wrote:
> > People frequently think that because ORDER BY can refer to the
> > output-column aliases, other clauses should be able to do it too,
> > but this is not so. Conceptually, ORDER BY happens after computation
> > of the output rows, so there's no logical inconsistency in allowing
> > it to do that ... but it's certainly confused enough people that
> > one wonders whether the SQL committee shouldn't have left that wart
> > out of the language.
>
> However, HAVING *must* happen after the computation of the output rows.
>
> -- Ed Leafe
Here is better description of the semantics, followed by info from the MySQL
docs for 5.0 that indicates that they use a different semantic model and do
not follow the SQL standard except in ambiguous cases .
Re: Aliased SubSelect in HAVING clause bug -- in progress?
* From: Tom Lane <tgl ( at ) sss ( dot ) pgh ( dot ) pa ( dot ) us>
* To: josh ( at ) agliodbs ( dot ) com
* Subject: Re: Aliased SubSelect in HAVING clause bug -- in progress?
* Date: Wed, 12 Mar 2003 12:18:40 -0500
Josh Berkus <josh ( at ) agliodbs ( dot ) com> writes:
> Oh. I see what you mean. Given that I (along with at least a dozen
posters
> to the SQL list) was confused that our HAVING/ORDER BY will accept column
> aliases but not sub-select aliases, would this be worthy of a FAQ item?
I think you may still be confused --- whether the SELECT-list item is a
sub-select or not has nothing to do with where you can reference its alias.
IIRC, the actual state of affairs is like this:
1. According to the SQL semantic model, evaluation of SELECT output
columns is almost the last operation in a SELECT; the only subsequent
steps are DISTINCT (which doesn't need any explicit references) and
ORDER BY. So the spec allows you to ORDER BY an output column name or
number (and, indeed, nothing else, in SQL92; SQL99 seems to have made
some incompatible changes here). AFAIK this is the *only* place you can
reference an output column alias per-spec, except for sub-select-in-FROM
constructs like
select myalias from (select ... as myalias from ...) ss
which isn't really what's at issue here (the sub-select itself cannot
refer to myalias, only the outer select can; myalias is an input column
name as far as the outer select is concerned).
2. We extend the spec by allowing ORDER BY to contain an expression
instead of an output column name/number; but as soon as you do, the
expression is an expression over the input column names (ie, it's on
the same semantic level as the output list) and so it cannot reference
output-list aliases. (If a simple name doesn't match any output column
name, we'll try to treat it as an expression, i.e. it will then be
matched against input column names.)
3. We extend the spec by allowing GROUP BY to reference output column
names/numbers; this I think was a mistake, because it's created a lot
of confusion. Again, only a name standing alone will be considered as
a possible output-column alias, not a name appearing in an expression.
(Here, a bare name is first tried as an input column name, and only
if that fails do we try to match against output column names. We have
to do it that way to ensure that the spec-consistent interpretation is
tried first --- but the inconsistency with ORDER BY is one reason why
this was a mistake.)
4. There is no such hack for HAVING: it's always an ordinary expression
over the input-column names. It wouldn't be useful to try to apply the
GROUP BY hack to HAVING, even if we wanted to deviate from spec here,
because HAVING clauses are hardly ever simple names.
Got that? Feel free to try to boil it down into a FAQ entry ...
regards, tom lane
-----------------------------------------------------------------------------------------------------------------------
MySQL
The HAVING clause is applied nearly last, just before items are sent to the
client, with no optimization. (LIMIT is applied after HAVING.)
A HAVING clause can refer to any column or alias named in a select_expr in the
SELECT list or in outer subqueries, and to aggregate functions. However, the
SQL standard requires that HAVING must reference only columns in the GROUP BY
clause or columns used in aggregate functions. To accommodate both standard
SQL and the MySQL-specific behavior of being able to refer columns in the
SELECT list, MySQL 5.0.2 and up allows HAVING to refer to columns in the
SELECT list, columns in the GROUP BY clause, columns in outer subqueries, and
to aggregate functions.
For example, the following statement works in MySQL 5.0.2 but produces an
error for earlier versions:
mysql> SELECT COUNT(*) FROM t GROUP BY col1 HAVING col1 = 2;
If the HAVING clause refers to a column that is ambiguous, a warning occurs.
In the following statement, col2 is ambiguous because it is used as both an
alias and a column name:
SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
Preference is given to standard SQL behavior, so if a HAVING column name is
used both in GROUP BY and as an aliased column in the output column list,
preference is given to the column in the GROUP BY column
--
Adrian Klaver
[EMAIL PROTECTED]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
