PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15658
PG Version11.2
OSDebian 9
Opened2019-02-26 18:55:59+00
Reported byJustin G
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15658
Logged by:          Justin G
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-02-26 18:55:59+00PG Bug reporting formBUG #15658: Window Function in a left join using AS or alias for the cloumn name
2019-02-26 22:56:26+00Tom LaneRe: BUG #15658: Window Function in a left join using AS or alias for the cloumn name
2019-02-27 01:15:41+00JustinRe: BUG #15658: Window Function in a left join using AS or alias for the cloumn name
2019-02-27 01:56:29+00Andrew GierthRe: BUG #15658: Window Function in a left join using AS or alias for the cloumn name
2019-02-27 02:15:35+00JustinRe: BUG #15658: Window Function in a left join using AS or alias for the cloumn name