Re: Aliased SubSelect in HAVING clause bug -- in progress?

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