BUG #15658: Window Function in a left join using AS or alias for the cloumn name
От | PG Bug reporting form |
---|---|
Тема | BUG #15658: Window Function in a left join using AS or alias for the cloumn name |
Дата | |
Msg-id | 15658-10b53866d3800951@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15658: Window Function in a left join using AS or alias for the cloumn name
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: