Re: Column names: where, group by, having inconsistent behaviour?

Jan Grant <Jan(dot)Grant(at)bristol(dot)ac(dot)uk> writes:
> http://developer.postgresql.org/docs/postgres/sql-select.html
>> ... PostgreSQL also allows both clauses to specify
>> arbitrary expressions. Note that names appearing in an expression will
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>> always be taken as input-column names, not as result-column names.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

> select col1 as x, count(col2) as y from table1
> group by x
> having x = 1;
> should work - it's hardly ambiguous.

It violates the above-underlined restriction though.

In retrospect, the fact that we allow "GROUP BY output-column-name"
was a mistake; it extended an ugly-but-defensible frammish of ORDER BY
into a place where it should never have gone. ORDER BY is the only one
of the SELECT modifiers that is logically executed after forming the
SELECT output columns, and so it's the only one that should legitimately
have access to their values.

We're kinda stuck with this mistake now on backwards-compatibility
grounds, but I don't much want to spread the mistake any further.

regards, tom lane