I have a SQLQuery like this:

select count(*) , avg(customer.salary)
from customer
where customer.salary > 1000.0 and customer.salary < 10000;

and I'm using hibernate session.createSQLQuery and list function. it returns List<Object[]>. first element of Object array has Integer type (because of count(*)) and second element has Double type (because of avg). but the problem is that both elements has the equal values. I mean all the elements have the value of first element. and this is not true. I don't know what may cause this problem. can anyone help me?

Using Hibernate 3.3.2.GA

I have exactly the same probelm:

my SQLQuery goes like:

SELECT a.a, b.b, c.c, d.d, d.e FROM a
LEFT JOIN b ON a.fk = b.pk AND b.x = 0
LEFT JOIN c ON a.fk2 = c.pk AND c.x = 0
LEFT JOIN d ON a.fk3 = d.pk AND d.x = 0
WHERE a.a = 'someString'

-> I get [ a.a, a.a, a.a, d.d, d.e ]
instead of [ a.a, b.b, c.c, d.d, d.e ]
as result.

When I put the query sent (and logged) by hibernate into pgSQL I get the result I whished for, so it necessarily has to be a bug in Hibernate...

So I'll first try to upgrade to Hibernate 3.5 and post whether the problem still occurs...

Cheers, Arndt

Ok, it seems the Error is related to

http://opensource.atlassian.com/project ... tion_33546

Ok,

the supplied workaround in HHH-3988: adding an alias to all selected columns works for me.

Think this should also resolve atfmova's original issue ;)

Cheers, Arndt

I know this problem has been around a long time, but the workaround supplied in HHH-3988 doesn't seem to work for everyone. (It didn't for me.) So, I thought I'd share my own workaround...and that is to use a @SqlResultSetMapping. So for the OP's query, annotate an entity (probably the one most logically related to your native query, but it doesn't matter which entity...a shortcoming of the spec in my opinion) as follows:



Then, your query should look like this:



Then, you need to make sure you reference your mapping when you create your native query:



You'll notice the aliases, customerCount and averageSalary, used in the query match the names used in the corresponding @ColumnResults. It's a shame this issue still exists in 3.5. I'm using 3.5.3-Final myself. Hope this saves someone some time.

- Matt