|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|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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)
|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|