I have the below HQL Query (with postgres)

select up from UserPromotion up where up.user = :user and
up.createTimestamp = (select max(u.createTimeStamp) from UserPromotion u where u.user = :user);

Unfortunately, when translated, the SQL query becomes,

select userpromot0_.id as id, userpromot0_.version as version, userpromot0_.credit as credit, userpromot0_.Promotion as Promotion, userpromot0_.user_info as user_info, userpromot0_.last_mod_by as last_mod6_, userpromot0_.last_mod_time as last_mod7_, userpromot0_.created_by as created_by, userpromot0_.create_time as create_t9_ from usr_promotion userpromot0_
where
(userpromot0_.user_info=42324 )and
(userpromot0_.create_time=(select userpromot1_.id from usr_promotion userpromot1_ where (userpromot1_.user_info=42324 )))

which returns multiple columns which then crashes my app. I have a work around with order by. But, i would like to use the max()
aggregate functionality.

Can you tell me if i am supposed to do something differently or is this a bug?

Thanks.

Maybe someone would start reading your incomplete postings (read the red box) if you would stop calling your mistakes a bug.

I will say, my bad indeed and post the necessary information.
Your rudeness was most unwelcome and unnecessary.


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:
Database returns multiple results,. I actually posted all this on Sunday before you all were hijacked. There is an exception due to getting multiple results where it expects one as you can tell from the sub correlated query return.

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

I have almost the same query but I'm getting the following error:
No type for aggregateNode

Thank you in advance

I have almost the same query but I'm getting the following error:
No type for aggregateNode

Thank you in advance