Hi

I need to use Hibernate 3.1.3 to create a query that returns an object and a aggregate value like this:

select max (t.test_date), t from TestTo t.

Because I have an aggregate function I have to define the GROUP BY clause:

select max (t.test_date), t from TestTo t group by t

But I get an error:

ORA-00979: not a GROUP BY expression

The generated SQL is:

select max(testto0_.test_date) as col_0_0_,
testto0_.test_value as col_1_0_,
testto0_.test_value as test1_0_,
testto0_.test_date as test2_0_
from TEST testto0_
group by testto0_.test_value

It seems that Hibernate is using all columns of the TestTo class in the SELECT statement but not in the GROUP BY statement.


The TestTo class:

public class TestTo
{
private int value;
private Date date;

public TestTo()
{
}

public int getValue()
{
return value;
}

public void setValue(int value)
{
this.value = value;
}

public Date getDate()
{
return date;
}

public void setDate(Date date)
{
this.date = date;
}
}

The Mapping File:

<hibernate-mapping>
<class name="mypackage.TestTo" table="TEST">
<id name="value" column="test_value">
<generator class="sequence">
<param name="sequence">seq_test</param>
</generator>
</id>
<property name="date" column="test_date"/>
</class>
</hibernate-mapping>

The problem is the query. Use this, instead:

The query should be correct. If I replace r with r.id it works. Using your query works if I only need the max(test_date) but in my case I need more values in the subquery and Oracle does not allow it.

It would be nice if your query would work, but the SQL standard will not allow it. Read up on aggregation functions in your SQL books. Either use separate queries, or subqueries.

Once again, the HQL query is correct. The problem is the expansion of the t TestTo object:

select max(testto0_.test_date) as col_0_0_,
testto0_.test_value as col_1_0_,
testto0_.test_value as test1_0_,
testto0_.test_date as test2_0_
from TEST testto0_
group by testto0_.test_value

If you add the test_date column to the group by clause the query works on Oracle.

select max(testto0_.test_date) as col_0_0_,
testto0_.test_value as col_1_0_,
testto0_.test_value as test1_0_,
testto0_.test_date as test2_0_
from TEST testto0_
group by testto0_.test_value, test_date

So the problem is that Hibernate is not expanding the t object in the group by clause.

repost

Once again, the query is wrong. It may be functional, but it is not correct. That query will return every row, because you are grouping by every column in the table, including the ID. You have to, in order to get every property in your class. And because you're getting every row, max(test_date) is exactly the same as test_date. Because it's the maximum of one value.

This is not what you're looking for, and it's not what your suggested HQL from your first post wants.

Trust me on this one. Or don't, and go and do the research yourself. You cannot get an aggregate and a non-aggregate in the same row of a result set. To do what you need, you must use two result set, hence the suggested subselet.

Getting results isn't the same as getting correct results. Have a look at the output of your "correct" query. Compare it to the actual data in the table. You'll see what you're doing wrong.

This is just a Test Case to demonstrate that Hibernate is not exploding the columns in the group by section of the query.