[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1657) hql update generate wrong sql with joined subclass hierarcy

[Carmelo Piccione (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1657?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33065#action_33065 ] 

Carmelo Piccione commented on HHH-1657:
---------------------------------------

Yet another occurence of same issue. I wish the hibernate devs would at least respond to this thread.

delete EquityOptionEntity eoe where eoe.maturity < getDate() and eoe not in  (select sle.security from StrategyLegEntity sle)

4:39:46,138 DEBUG [main] (SQLStatementLogger.java:111) - insert into #EQUITY_OPTIONS select equityopti0_.id as id from EQUITY_OPTIONS equityopti0_ inner join DERIVATIVES equityopti0_1_ on equityopti0_.id=equityopti0_1_.id inner join INSTRUMENTS equityopti0_2_ on equityopti0_.id=equityopti0_2_.id where maturity<getdate() and (id not in  (select strategyle1_.security_id from STRATEGY_LEGS strategyle1_))
 14:39:46,154 ERROR [main] (JDBCExceptionReporter.java:101) - Ambiguous column name 'id'.
 14:39:46,154 ERROR [main] (UnusedExpiredInstrumentPruner.java:90) - persistence error when trying to delete equity options
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not insert/select ids for bulk delete
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
	at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:60)
	at com.optifreeze.Xbook.SecurityMasterAdmin.UnusedExpiredInstrumentPruner.pruneEquityOptions(UnusedExpiredInstrumentPruner.java:76)
	at com.optifreeze.Xbook.SecurityMasterAdmin.UnusedExpiredInstrumentPruner.main(UnusedExpiredInstrumentPruner.java:137)
Caused by: org.hibernate.exception.GenericJDBCException: could not insert/select ids for bulk delete
	at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:125)
	at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:419)
	at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
	at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1168)
	at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
	at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:51)
	... 2 more
Caused by: java.sql.SQLException: Ambiguous column name 'id'.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
	at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
	at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:116)
	... 7 more


> hql update generate wrong sql with joined subclass hierarcy
> -----------------------------------------------------------
>
>                 Key: HHH-1657
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1657
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-hql
>         Environment: Hibernate 3.2.0cr1, Hibernate 3.1.3
>            Reporter: Alexey Romanchuk
>            Assignee: Gail Badner
>
> Let suppose that we have two joined subclass entities: Parent (id PK) and Child (id PK) that mapped with joined subclass method.
> When I try to update Child by id with hql:
> update Child c set c.field = 'value' where c.id = 1234
> hibernate generates joined tables like 
> insert into HT_parent select child0_.id as id from child child0_ inner join parent child0_1_ on child0_.id=child0_1_.id  wher id in = 1234
> look at last condition. hibernate use id WITH OUT tables alias that cause sql exception: column reference "id" is ambiguous

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira