Disable local connection query for Hibernate NonUniqueDiscoveredSqlAliasException

Question

I want to join some tables using native queries in Spring Boot. The query looks like this:

SELECT * FROM employee AS e 
INNER JOIN c ON e.id = c.employee_id 
INNER JOIN b ON c.arw_id = b.arw_id 
INNER JOIN a ON b.ap_id = a.id

Now, I'm getting Hibernate NonUniqueDiscoveredSqlAliasException on [id] and [arw_id]. I could fix the second one using the using annotation, but the id of the tables are entities and I don't really want to change their names...

Any suggestions or Hibernate bug?

cheers, Niklas

Edit: To clarify: the first error message I received was Encountered a duplicated sql alias [arw_id] during auto-discovery of a native-sql query; the nested exception was org.hibernate.loader. custom.NonUniqueDiscoveredSqlAliasExce[...]. Then I changed the query to:

SELECT * FROM employee AS e 
INNER JOIN c ON e.id = c.employee_id 
INNER JOIN b ON USING (arw_id) 
INNER JOIN a ON b.ap_id = a.id

and the following error occurs: Duplicate sql alias [id] encountered during autodiscover native SQL query; nested exception is org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasExce[...]

Answer 1

Do you really want all columns from employee, c, b, and a? What do you convert the result into?

If you want to convert it to Employee class, just use

SELECT e.* FROM employee AS e 
INNER JOIN c ON e.id = c.employee_id 
INNER JOIN b ON c.arw_id = b.arw_id 
INNER JOIN a ON b.ap_id = a.id

What makes you confident that the error you are receiving is due to id and arw_id? They are different fields and they shouldn't give you the error you mentioned. Or this is most likely because you have the same column names in your c or b or a In this case your column names become ambiguous.

It would be clearer if you could show us a piece of code where you are using this query

edit As I can see, all you did was use USING instead of ON. This means you are sharing your arw_id between the two tables, which will eliminate duplicate occurrences of arw_id in the result set. If this is the same case with your id, why not do the same with your id?