[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-2436) Allow grouping by entity reference (per JPA spec)

[Steve Ebersole (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=all ]

Steve Ebersole updated HHH-2436:
--------------------------------

    Fix Version: 3.3
        Summary: Allow grouping by entity reference (per JPA spec)  (was: Incorrect SQL generation for some select statements with "group by")
      Component: query-hql

> Allow grouping by entity reference (per JPA spec)
> -------------------------------------------------
>
>          Key: HHH-2436
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436
>      Project: Hibernate3
>         Type: Bug

>   Components: query-hql
>     Versions: 3.2.1
>     Reporter: Joseph Marques
>     Assignee: Steve Ebersole
>      Fix For: 3.3

>
>
> 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?  

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira