[Hibernate-JIRA] Created: (HHH-2436) Incorrect SQL generation for some select statements with "group by"
5 a.m.
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
5:09 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2436) Incorrect SQL generation for some select statements with "group by"
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=c...
]
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
5:24 a.m.
New subject: [Hibernate-JIRA] Resolved: (HHH-2436) Incorrect SQL generation for some select statements with "group by"
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=all ]
Christian Bauer resolved HHH-2436:
----------------------------------
Resolution: Rejected
FAQ
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
5:26 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2436) Incorrect SQL generation for some select statements with "group by"
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=c...
]
Christian Bauer commented on HHH-2436:
--------------------------------------
http://www.hibernate.org/hib_docs/v3/reference/en/html/queryhql.html#quer...
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
5:51 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2436) Incorrect SQL generation for some select statements with "group by"
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=c...
]
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_langre...
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
8:09 a.m.
New subject: [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
8:13 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-2436) Allow grouping by entity reference (per JPA spec)
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=all ]
Steve Ebersole updated HHH-2436:
--------------------------------
Fix Version: 3.3
Summary: Allow grouping by entity reference (per JPA spec) (was: Incorrect SQL
generation for some select statements with "group by")
Component: query-hql
Allow grouping by entity reference (per JPA spec)
-------------------------------------------------
Key: HHH-2436
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436
Project: Hibernate3
Type: Bug
Components: query-hql
Versions: 3.2.1
Reporter: Joseph Marques
Assignee: Steve Ebersole
Fix For: 3.3
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
10:32 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-2436) Allow grouping by entity reference (per JPA spec)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=c...
]
Steve Ebersole updated HHH-2436:
--------------------------------
Affects Version/s: (was: 3.2.1)
antlr-rework
Allow grouping by entity reference (per JPA spec)
-------------------------------------------------
Key: HHH-2436
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436
Project: Hibernate3
Issue Type: Bug
Components: query-hql
Affects Versions: antlr-rework
Reporter: Joseph Marques
Assignee: Steve Ebersole
Fix For: antlr-rework
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
10:32 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-2436) Allow grouping by entity reference (per JPA spec)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=c...
]
Steve Ebersole updated HHH-2436:
--------------------------------
Fix Version/s: (was: 3.3)
antlr-rework
Allow grouping by entity reference (per JPA spec)
-------------------------------------------------
Key: HHH-2436
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436
Project: Hibernate3
Issue Type: Bug
Components: query-hql
Affects Versions: antlr-rework
Reporter: Joseph Marques
Assignee: Steve Ebersole
Fix For: antlr-rework
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
11:26 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2436) Allow grouping by entity reference (per JPA spec)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=c...
]
Sandeep Tamhankar commented on HHH-2436:
----------------------------------------
See HHH-1615 for my Hibernate patch which attempts to address this. Not sure it covers
all the cases in the world, but it covered my case (and I think it would cover the ones in
this issue report as well).
Allow grouping by entity reference (per JPA spec)
-------------------------------------------------
Key: HHH-2436
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436
Project: Hibernate3
Issue Type: Bug
Components: query-hql
Affects Versions: antlr-rework
Reporter: Joseph Marques
Assignee: Steve Ebersole
Fix For: antlr-rework
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
9:35 p.m.
New subject: [Hibernate-JIRA] Resolved: (HHH-2436) Allow grouping by entity reference (per JPA spec)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436?page=c...
]
Diego Pires Plentz resolved HHH-2436.
-------------------------------------
Assignee: Diego Pires Plentz (was: Steve Ebersole)
Resolution: Duplicate
Allow grouping by entity reference (per JPA spec)
-------------------------------------------------
Key: HHH-2436
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2436
Project: Hibernate3
Issue Type: Bug
Components: query-hql
Affects Versions: antlr-rework
Reporter: Joseph Marques
Assignee: Diego Pires Plentz
Fix For: antlr-rework
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
6226
days inactive
6517
days old
hibernate-issues@lists.jboss.org
10 comments
5 participants
participants (5)
-
Christian Bauer (JIRA) -
Diego Pires Plentz (JIRA)
-
Joseph Marques (JIRA)
-
Sandeep Tamhankar (JIRA)
-
Steve Ebersole (JIRA)