group by can use alias from select list but not the having clause
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><p>hi</p>
<p>my understanding from SQL sequence is:</p>
<p>from - where - group by - having - select - order by - limit</p>
<p>but</p>
<p>with tbase(id)<br/>
as (select 1 union all select 2)<br/>
select id otto from tbase<br/>
group by otto<br/>
order by otto</p>
<p>works (how does the group by know about otto if the sequence is done as described above?)</p>
<p>also the having after the group by doesn't know about otto (which in my thoughts is correct)</p>
<p>So i think its good if</p>
<p>a) the group by also doesn't know about otto</p>
<p>b) also the havings can interpret the alias otto</p>
<p>But today its half/half so its not good.</p>
<p>hape</p></div></body></html>
On Monday, July 3, 2023, hape Hape <postgres-hape@gmx.de> wrote:
a) the group by also doesn't know about otto
The isn’t a bug as we explicitly document our deviation from the standard
for group by. You are correct that you are better off not using output
column aliases in the group by specification. But we are not going to make
doing so,an error and break countless applications relying upon the
documented behavior. But we are also not going to double-down and allow
the undesirable deviation for having.
David J.
hape Hape <postgres-hape@gmx.de> writes:
works (how does the group by know about otto if the sequence is done as
described above?)
This is intentional and documented, eg
https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY
says
An expression used inside a grouping_element can be an input column
name, or the name or ordinal number of an output column (SELECT list
item), or an arbitrary expression formed from input-column values.
whereas the discussion of HAVING does not mention allowing output
columns. The reason for this is mainly that it'd seldom be useful
for a HAVING expression to be just a bare column reference, but
as soon as you write something that's not a bare column reference,
the output-column special case is disallowed anyway.
Yes, it's messy. That's mostly because we are still trying to
preserve some compatibility with SQL92, which did things differently
from later standards in this area. But it's been like that for a
couple of decades now, and we're not going to change it.
regards, tom lane
On Mon, Jul 3, 2023 at 3:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
hape Hape <postgres-hape@gmx.de> writes:
works (how does the group by know about otto if the sequence is done as
described above?)This is intentional and documented, eg
https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY
saysAn expression used inside a grouping_element can be an input column
name, or the name or ordinal number of an output column (SELECT list
item), or an arbitrary expression formed from input-column values.whereas the discussion of HAVING does not mention allowing output
columns. The reason for this is mainly that it'd seldom be useful
for a HAVING expression to be just a bare column reference, but
as soon as you write something that's not a bare column reference,
the output-column special case is disallowed anyway.Tom, the HAVING clause section has:
... Each column referenced in condition must unambiguously reference a
grouping column, ...
I suppose "grouping column" can be read to mean what is called "grouping
element" in GROUP BY section and that it may be either input or output
column, leading to the confusion.
Perhaps this phrase can be improved to make this clearer.
Best regards
Pantelis Theodosiou