From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | zzzzz(dot)graf(at)gmail(dot)com |
Subject: | BUG #15658: Window Function in a left join using AS or alias for the cloumn name |
Date: | 2019-02-26 18:55:59 |
Message-ID: | 15658-10b53866d3800951@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15658
Logged by: Justin G
Email address: zzzzz(dot)graf(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-02-26 22:56:26 | Re: BUG #15658: Window Function in a left join using AS or alias for the cloumn name |
Previous Message | Sandeep Thakkar | 2019-02-26 16:04:21 | Re: BUG #15647: pgagent file missing |