Allow grouping by entity reference (per JPA spec)

Description

JPQL was:

SELECT res.resourceType, count(res.resourceType)
FROM ResourceGroup rg JOIN rg.resources res
WHERE rg.id = :id
GROUP BY res.resourceType

It translated to:

select
resource2_.RESOURCE_TYPE_ID as col_0_0_,
count(resource2_.RESOURCE_TYPE_ID) as col_1_0_,
resourcety3_.ID as ID376_,
resourcety3_.NAME as NAME376_,
resourcety3_.DESCRIPTION as DESCRIPT3_376_,
resourcety3_.CATEGORY as CATEGORY376_,
resourcety3_.PLUGIN as PLUGIN376_,
resourcety3_.CTIME as CTIME376_,
resourcety3_.MTIME as MTIME376_,
resourcety3_.PARENT_RESOURCE_TYPE_ID as PARENT8_376_,
resourcety3_.PLUGIN_CONFIG_DEF_ID as PLUGIN9_376_,
resourcety3_.RES_CONFIG_DEF_ID as RES10_376_
from
public.ON_RESOURCE_GROUP resourcegr0_
inner join
public.ON_RESOURCE_GROUP_RES_MAP resources1_
on resourcegr0_.ID=resources1_.RESOURCE_GROUP_ID
inner join
public.ON_RESOURCE resource2_
on resources1_.RESOURCE_ID=resource2_.ID
inner join
public.ON_RESOURCE_TYPE resourcety3_
on resource2_.RESOURCE_TYPE_ID=resourcety3_.ID
where
resourcegr0_.DTYPE in (
'COMPATIBLE', 'MIXED'
)
and resourcegr0_.ID=?
group by
resource2_.RESOURCE_TYPE_ID

The message was:

"ERROR main org.hibernate.util.JDBCExceptionReporter - ERROR: column "resourcety3_.id must appear in the GROUP BY caluse or be used in an aggregate function"

My issue with this is that I only want to select a single object (along with the count of that object when grouped), but the generated SQL seems to be selecting two different objects - resource2_ and resourcety3_. The jdbc error message is perfectly correct; it's the translated SQL that seems a bit off.

Maybe the JPQL syntax I want to use is not supported? Or perhaps I'm just doing something wrong?

Environment

None

Status

Assignee

DiegoP

Reporter

Joseph Marques

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

antlr-rework

Priority

Major