NonUniqueDiscoveredSqlAliasException when two table has same column names

Question

I have two tables Item and Nikasa - whose definitions looks like:

Item { id, name, spec} and Nikasa {id, date, item_id}. Here item_id represents Item.id.

I did a simple native SQL join to select only Item.id and Nikasa.id as:

Session s = getSession(); SQLQuery sq = s.createSQLQuery("SELECT it.id, nik.id FROM item it LEFT JOIN nikasa nik ON (it.id = nik.item_id)"); List result = sq.list(); 

But I am getting exception NonUniqueDiscoveredSqlAliasException : Encountered a duplicated sql alias [ID] during auto-discovery of a native-sql query at line List result=sq.list();:

Stack-trace :

org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [ID] during auto-discovery of a native-sql query     at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:594)     at org.hibernate.loader.Loader.getResultSet(Loader.java:1986)     at org.hibernate.loader.Loader.doQuery(Loader.java:829)     at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)     at org.hibernate.loader.Loader.doList(Loader.java:2463)     at org.hibernate.loader.Loader.doList(Loader.java:2449)     at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)     at org.hibernate.loader.Loader.list(Loader.java:2274)     at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:331)     at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1585)     at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:224)     at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:156) 

Can you please suggest what's wrong going on?

Answer 1

You need set the result alias.

SELECT it.id as itemid, nik.id as nikasaid FROM item it LEFT JOIN nikasa nik ON (it.id = nik.item_id) 

Answer 2

If you run your query in SQL workbench you will find that the table name is same as id for two columns. Hibernate treats it as duplicated so an alias is required. Let us assume you have query:

SELECT it.id, nik.id FROM item it LEFT JOIN nikasa nik ON (it.id = nik.item_id) 

Output of the column name on SQL workbench will be:

id | id | 

which works correctly there. But for hibernate it needs unique column name. So you need to add alias in anyone of the column

SELECT it.id as it_id, nik.id FROM item it LEFT JOIN nikasa nik ON (it.id = nik.item_id) 

or

SELECT it.id , nik.id as nik_id FROM item it LEFT JOIN nikasa nik ON (it.id = nik.item_id) 

or giving both of the column an alias.