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

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