[hibernate-issues] [Hibernate-JIRA] Reopened: (HHH-2436) Incorrect SQL generation for some select statements with "group by"

[Steve Ebersole (JIRA)]

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

     Assign To: Steve Ebersole

The specification does in fact allow grouping by what it terms an "identification_variable", which we call an alias.  Historically HQL did not allow this because we did not collect the contextual information need to translate that properly.  In fact we still do not.  However, support for this should be rolled into the translator redesign.

> Incorrect SQL generation for some select statements with "group by"
> -------------------------------------------------------------------
>
>          Key: HHH-2436
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436
>      Project: Hibernate3
>         Type: Bug

>     Versions: 3.2.1
>     Reporter: Joseph Marques
>     Assignee: Steve Ebersole

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