NonUniqueDiscoveredSqlAliasException when two table has same column names

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

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)

Advertisement

Answer