Re: BUG #15658: Window Function in a left join using AS or alias for the cloumn name

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(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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)
>