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

The following bug has been logged on the website:

Bug reference:      15658
Logged by:          Justin G
Email address:      zzzzz.graf@gmail.com
PostgreSQL version: 11.2
Operating system:   Debian 9
Description:

Hello postgresql developers

I believe I found an obscure bug with the window function 

Postgresql version 11.2 
OS Debian 9 
PG Admin 4.1

Have  2 basic tables with a Parent child relationship, a One to Many
relationship.  The child table also makes reference to a 3rd table which can
have a many to many relationship.  This query returns count(*)  of the 3rd
table’s keys in the child table grouped by Parent Table ID.  

The base SQL statement works:

select calprorules_id cid , count(*) over 
 (PARTITION BY calprorules_calprorange_id, calprorules_calprohd_id  ) as
howmany
from mcal.calprorules

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

select counts.count, 
    caldetail.*, calprorules_desired_value, calprorules_stdpreceision,
calprorules_mutpreceision , 
    calprorange_description, calprorange_id, calprorange_from, calprorange_to

    from mcal.caldetail 
    left join mcal.calprorules on calprorules_id = caldetail_calprorules_id
    left join (select calprorules_id as cid , count(*) over 
                (PARTITION BY calprorules_calprorange_id, calprorules_calprohd_id  )  as
howmany
                from mcal.calprorules)  counts 
            on counts.cid = caldetail_calprorules_id
    left join mcal.calprorange on calprorange_id = calprorules_calprorange_id
    where caldetail_calhead_id = 179 
    order by calprorange_description, caldetails_seqence 


Postgresql returns  
ERROR: column "caldetail.caldetail_id" must appear in the GROUP BY clause or
be used in an aggregate function LINE 2: caldetail.*,
calprorules_desired_value, calprorules_stdprec... 
SQL state: 42803 Character: 24

Delete the AS howmany; it works.

I realized after writing this that I should have written the SQL like this,
deleting the LEFT JOIN and moving the window function into the select
statement:

select caldetail.*, calprorules_desired_value, calprorules_stdpreceision,
calprorules_mutpreceision , 
            calprorange_description, calprorange_id, calprorange_from, calprorange_to
, 
            count(*) over (PARTITION BY calprorules_calprorange_id,
calprorules_calprohd_id  ) 
            from mcal.caldetail 
                left join mcal.calprorules on calprorules_id =
caldetail_calprorules_id
                left join mcal.calprorange on calprorange_id =
calprorules_calprorange_id
            where caldetail_calhead_id = 179 
            order by calprorange_description, caldetails_seqence 


Clearly the second SQL statement is better, but i do not believe the window
function should error when put into a join

PG Bug reporting form <noreply@postgresql.org> writes:
> I believe I found an obscure bug with the window function 

Hm, could we see a self-contained example, ie with the underlying
table/view definitions too?  Probably don't need any sample data
for this type of problem, but I'm unexcited about trying to reverse-
engineer your table definitions enough to reproduce the problem.

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

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

I'm wondering why this didn't already fail at "counts.count", because with
the "AS howmany" in place, that sub-select isn't returning any column
named "count".  I kind of suspect user error due to ambiguous column
names, but the details aren't obvious.

            regards, tom lane

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