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

[Joseph Marques (JIRA)]

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

Joseph Marques commented on HHH-2436:
-------------------------------------

When I use this alternate JPQL:

select t, count(t)
from ResourceGroup rg join rg.resources res join res.resourceType t
where rg.id = :id
group by t

I get the following generated SQL:

select
  resourcety3_.ID as col_0_0_,
  count(resourcety3_.ID) as col_1_0_,
  resourcety3_.ID as ID648_,
  resourcety3_.NAME as NAME648_,
  resourcety3_.DESCRIPTION as DESCRIPT3_648_,
  resourcety3_.CATEGORY as CATEGORY648_,
  resourcety3_.PLUGIN as PLUGIN648_,
  resourcety3_.CTIME as CTIME648_,
  resourcety3_.MTIME as MTIME648_,
  resourcety3_.PARENT_RESOURCE_TYPE_ID as PARENT9_648_,
  resourcety3_.PLUGIN_CONFIG_DEF_ID as PLUGIN8_648_,
  resourcety3_.RES_CONFIG_DEF_ID as RES10_648_ 
 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
  resourcety3_.ID

This at least has a single object - resourcety3_ - in the select list, but I still get a jdbc error:

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

I find it unlikely that I'm the first one to come upon something like this, but then again the HQL editor doesn't complain about there being any syntax violations in the JPQL...so what am I doing wrong?

> 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

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