Hi,

I'm using hibernate 3.5.0-CR2 with MySQL server 5.1.44 (through mysql-connector-java-5.1.9) on Windows.
I'm using JPA 2.0 and issuing a native query. The query runs fine when run directly agains the server using MySQL Browser. When I run it through JPA/Hibernate, the number of items returned is correct but the values returned for
each item is not correct. I believe this is being caused by the fact that the column names being returned are the same.

My Object looks like this:

@Entity
public class Relationship {
@Id
private Long id;

@ManyToOne
@JoinColumn(name="supervisorRoleId")
private Role supervisorRole;

@ManyToOne
@JoinColumn(name="subordinateRoleId")
private Role subordinateRole;

...
}


And I issue a query like this:

Query query = entityManager.createNativeQuery("SELECT r1.subordinateRoleId, r2.subordinateRoleId, r3.subordinateRoleId FROM relationship AS r1 LEFT JOIN relationship AS r2 ON r2.supervisorRoleId = r1.subordinateRoleId LEFT JOIN relationship AS r3 ON r3.supervisorRoleId = r2.subordinateRoleId WHERE r1.supervisorRoleId = :roleId" );
query.setParameter( "roleId", roleId );
List<Object[]> result = (List<Object[]>) query.getResultList();

I wind up with results like:
1,1,1
2,2,2
3,3,3

where the first value is repeated. I believe this is because the three fields returned from the query are the same field from different instances (on a self join). I tried changing this to give each return field a different name using "AS r1", etc but this gives a different error that subordinateRoleId field is not found.
I know that the rows being returned are correct because if I changed the fields returned from the same instances but that are not the same field, the values being returned are correct (i.e. the instances from which values are returned are correct).

Is there any way to work around this problem?

Regards,
Len

Hi,
I encountered the same problem today (old Hibernate Version: 3.4, maybe newer version do not have this problem).
If you return multiple columns with the same name in the native SQL query result, hibernate will simply
use the values of the column that is returned first for all other columns with the same name.
This is a really nasty framework behaviour. I think a general purpose framework like Hibernate should
throw an Exeption in this case that expresses the fact that the result set is ambigious and cannot be mapped by the framework.

Anway,... I solved the problem for me by giving each column a distinct name in the result set:
Example (simplified, my original query was rather complex):

Not Worling: SELECT a.name, b.name FROM TABLE_A a, TABLE_B b WHERE a.name = b.name
Working: SELECT a.name AS NAME_A, b.name AS NAME_B FROM TABLE_A a, TABLE_B b WHERE a.name = b.name

HtH
Chris

Hi,

I've had this problem today and, even if the post is old, and I don't know if the issue is related to Hibernate version (I make maintenance of an old application), I will share a simple solution I've found nesting the original query. The alias didn't work for me (I don't know why)

Send as native query parameter, the query surrounded by:

SELECT * FROM (---query with problems in columns with same name---) T1

Bye.