[hibernate-issues] [Hibernate-JIRA] Reopened: (HHH-2436) Incorrect SQL generation for some select statements with "group by"
[ 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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira