Re: BUG #15658: Window Function in a left join using AS or alias forthe cloumn name - Mailing list pgsql-bugs

From Justin
Subject Re: BUG #15658: Window Function in a left join using AS or alias forthe cloumn name
Date
Msg-id CALL-XeM-ONqKnibaUsbZTu3P4-JbxPC0cwQAGbtiP36i3CdgQg@mail.gmail.com
Whole thread Raw
In response to Re: BUG #15658: Window Function in a left join using AS or alias for the cloumn name  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
List pgsql-bugs
Hi Andrew
I think I understand what your stating.  I realized after re-reading Tom's responds and playing with the SQL statement i see my mistake.
I just did not catch it when i was writing the SQL statement. 

I figured out a work around pretty quickly and later realized i was being an idiot putting the window function in a JOIN for this query

Then i wrote the bug report.. Thank you            

On Tue, Feb 26, 2019 at 8:56 PM Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 >> The error occurs when I put the query into a LEFT JOIN:

 >> select counts.count,
 >> caldetail.*, calprorules_desired_value, calprorules_stdpreceision,

 Tom> I'm wondering why this didn't already fail at "counts.count",

Because counts.count resolves as count(counts), obviously. That makes
the query an aggregation query with implied GROUP BY (), hence the error.

Justin: the problem is nothing to do with the join, but it _is_ to do
with the AS alias. For historical compatibility reasons, PostgreSQL
tries to treat x.y and y(x) as though they were somewhat equivalent; so
you can do (under some conditions) x.function or columnname(table).
Needless to say actually _using_ this facility is a very bad idea.

So in this example, if you have a column called "count", then
counts.count resolves to that column. But if there's no column called
"count", then counts.count is resolved as count(counts) (which works
because count() is one of the few functions that can take any parameter
type), and since count() is an aggregate function, that forces the query
to behave as if there were an implied GROUP BY (), just as doing
something like  select count(*) from table;  does.

So this is not a bug, just a historical landmine.

--
Andrew (irc:RhodiumToad)

pgsql-bugs by date:

Previous
From: Andrew Gierth
Date:
Subject: Re: BUG #15648: oracle_fdw extension not able to create
Next
From: "Guy Rouillier"
Date:
Subject: Re: BUG #15656: Not able to login database