I have the below HQL query. There is a problem with the group by section when it is translated into sql.

Basically, database complains (postgres) that in the select section, we have

select introducti1_.id as x0_0_,
user7_.id as x1_0_, user7_.username as x2_0_,
user9_.id as x3_0_, user9_.username as x4_0_,

but in group by section, this is translated as
person6_.user_info ,
user7_.username ,
person8_.user_info ,
user9_.username ,

since
introducti1_.introducee2=person8_.id
and introducti1_.introducee2=person8_.id
and person8_.user_info=user9_.id

Therefore, hibernate subsitutes person8.user_info for user9_.id which in turn creates problems for the database, because it expects exact naming for the columns specified in select aggregate columns for group by section.

I have seen in one place a similar bug to this. Has this been fixed already or is there a solution for this that i couldn't seem to find.

Thanks.

The HQL query

"select i.id , i.firstIntroducee.user.id,
i.firstIntroducee.user.username,
i.secondIntroducee.user.id,
i.secondIntroducee.user.username,
i.anonymous,
i.createTimestamp,
count(dm.sender.id),
dm.sender.id ,
dm.recipient.id

from DirectEmail dm, Introduction i

where
(dm.sender.id in (select i1.firstIntroducee.user.id from Introduction i1 where
i1.introducer.id = :currentUserId and i1.createTimestamp >= :sixtyDaysAgo ))
or
(dm.sender.id in (select i2.secondIntroducee.user.id from Introduction i2 where
i2.introducer.id = :currentUserId and i2.createTimestamp >= :sixtyDaysAgo ))

group by i.id,
i.anonymous,
i.firstIntroducee.user.id,
i.firstIntroducee.user.username,
i.secondIntroducee.user.id,
i.secondIntroducee.user.username,
i.createTimestamp,
dm.sender.id ,
dm.recipient.id


Which produces below sql query after translation by hibernate.

select introducti1_.id as x0_0_,
user7_.id as x1_0_, user7_.username as x2_0_,
user9_.id as x3_0_, user9_.username as x4_0_,
introducti1_.anonymous as x5_0_, introducti1_.create_time as x6_0_, count(directemai0_.sender) as x7_0_, directemai0_.sender as x8_0_,
directemai0_.recipient as x9_0_

from direct_email directemai0_, intro introducti1_, person person6_, user_info user7_, person person8_, user_info user9_

where introducti1_.introducee1=person6_.id
and introducti1_.introducee1=person6_.id and person6_.user_info=user7_.id and
introducti1_.introducee2=person8_.id
and introducti1_.introducee2=person8_.id
and person8_.user_info=user9_.id
and
(((directemai0_.sender in(select person3_.user_info from intro introducti2_, person person3_ where introducti2_.introducee1=person3_.id
and
((introducti2_.introducer=? )and(introducti2_.create_time>=? )))))or
((directemai0_.sender in(select person5_.user_info from intro introducti4_, person person5_ where introducti4_.introducee2=person5_.id
and ((introducti4_.introducer=? )and(introducti4_.create_time>=? ))))))

group by
introducti1_.id ,
introducti1_.anonymous ,
person6_.user_info ,
user7_.username ,
person8_.user_info ,
user9_.username ,
introducti1_.create_time ,
directemai0_.sender ,
directemai0_.recipient

Hibernate version:
Hibernate 2.1.6 (Used with Spring 1.1.1)

Mapping documents:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping
>
<class
name="xxx.xxx.xxx.UserPromotion"
table="user_promotion"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version"
>

<id
name="id"
column="id"
type="long"
unsaved-value="-1"
>
<generator class="native">
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-UserPromotion.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<version
name="version"
type="long"
column="version"
access="property"
unsaved-value="negative"
/>

<property
name="daysLeft"
type="int"
update="true"
insert="true"
access="property"
column="credit"
/>

<many-to-one
name="promo"
class="xxx.xxx.xxx.Promotion"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="Promotion"
/>

<many-to-one
name="user"
class="xxx.xxx.xxx.User"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="user_info"
/>

<many-to-one
name="lastModifiedBy"
class="xxx.xxx.xxx.User"
cascade="none"
outer-join="false"
update="true"
insert="true"
access="property"
column="last_mod_by"
/>

<property
name="lastModifiedTimestamp"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="last_mod_time"
/>

<many-to-one
name="createdBy"
class="xxx.xxx.xxx.User"
cascade="none"
outer-join="false"
update="true"
insert="true"
access="property"
column="created_by"
/>

<property
name="createTimestamp"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="create_time"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-UserPromotion.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Managed through getHibernateTemplate() from HibernateDaoSupport in Spring.

Full stack trace of any exception that occurs:
column "user7_.id" must appear in the GROUP BY clause or be used in an aggregate function


Name and version of the database you are using:
postgres 8.0 beta2 dev3