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

From: Justin <zzzzz(dot)graf(at)gmail(dot)com>
To: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15658: Window Function in a left join using AS or alias for the cloumn name
Date: 2019-02-27 02:15:35
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: 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(at)tao11(dot)riddles(dot)org(dot)uk>

> >>>>> "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)

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Guy Rouillier 2019-02-27 05:25:08 Re: BUG #15656: Not able to login database
Previous Message Andrew Gierth 2019-02-27 02:13:25 Re: BUG #15648: oracle_fdw extension not able to create