[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2436) Incorrect SQL generation for some select statements with "group by"
Joseph Marques (JIRA)
noreply at atlassian.com
Wed Feb 21 06:51:33 EST 2007
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=comments#action_26195 ]
Joseph Marques commented on HHH-2436:
-------------------------------------
Well, maybe I'm misreading the grammar, or maybe the spec is borked, but does it not permit grouping by entities?
Here is the grammar I use: http://e-docs.bea.com/kodo/docs41/full/html/ejb3_langref.html#ejb3_langref_bnf
Section 10.2.6 reiterates specifically that "Grouping by an entity is permitted. In this case, the entity must contain no serialized state fields or lob-values state fields."
But as I sit back and reflect on what that means, I wonder how such a function would be implemented. Would that means to generate SQL that grouped by every single field in the entity? I guess it would have to. Now I'm even more curious as to what the spec writers where thinking here.
PS - In the meantime, I already implemented the needed functionally using a single valued path expression, as the Hibernate docs show...but I had to do a second query selecting the full entities matching the information that was returned to me from the statement with the groupby.
> 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
More information about the hibernate-issues
mailing list