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

[Joseph Marques (JIRA)]

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