New subject: [Hibernate-JIRA] Commented: (HHH-2098) Problem deleting entities in hierarchy

Friday, 22 September 2006

Problem deleting entities in hierarchy
--------------------------------------

         Key: HHH-2098
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2098
     Project: Hibernate3
        Type: Bug

  Components: query-hql  
    Versions: 3.2.0.cr3    
 Environment: Hibernate 3.2.0CR3 and Hibernate 3.2.0CR4, on an Oracle DB
    Reporter: Philippe Larouche
    Priority: Blocker

Hi, I'm using Hibernate 3.2.0 CR3 (also tried it with 3.2.0CR4) and when I try to
delete entities in batch (with an HQL query), I got an error in the SQL translation of my
query. I try to delete an entity that is the child of another one. Here are my mapping
files:

Scancode (parent entity)......

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD
3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.cardinal.dal.entity.foundation.ScanCode"
table="SCAN_CD_VW" schema="CDM_FND_1_0">

<composite-id name="id"
class="com.cardinal.dal.entity.generated.foundation.ScanCodeId">
<key-property name="scanCdGuid" type="binary">
<meta attribute="use-in-equals"
inherit="false">true</meta>
<column name="SCAN_CD_GUID"/>
</key-property>

<key-property name="operatingCntxGuid" type="binary">
<meta attribute="use-in-equals"
inherit="false">true</meta>
<column name="OPERATING_CNTX_GUID"/>
</key-property>
</composite-id>

<version name="versionNum" column="VERSION_NUM"
generated="always" unsaved-value="negative"
insert="false"/>
<property name="dataDomainGuid" type="binary">
<column name="DATA_DOMAIN_GUID" not-null="true"/>
</property>

<property name="scanCdCls" type="string">
<column name="SCAN_CD_CLS" length="1"
not-null="true"/>
</property>

<property name="scanCdTxt" type="string">
<column name="SCAN_CD_TXT" not-null="true"/>
</property>

<filter name="operatingCntxFilter" condition=":operatingCntxGuid =
OPERATING_CNTX_GUID"/>
</class>
</hibernate-mapping>

PatientScancode (child entity).............

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD
3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<joined-subclass name="com.cardinal.dal.entity.patient.PatientScanCode"
extends="com.cardinal.dal.entity.foundation.ScanCode"
table="PATIENT_SCAN_CD_VW" schema="CDM_PAT_1_0">

<key>
<column name="PATIENT_SCAN_CD_GUID"/>
<column name="OPERATING_CNTX_GUID"/>
</key>

<property name="patientGuid" type="binary">
<meta attribute="scope-get"
inherit="false">protected</meta>
<meta attribute="scope-set"
inherit="false">protected</meta>
<column name="PATIENT_GUID"/>
</property>

<many-to-one name="patientVw"
class="com.cardinal.dal.entity.patient.Patient" update="false"
insert="false" fetch="select">
<column name="PATIENT_GUID" not-null="true"/>
<column name="OPERATING_CNTX_GUID"/>
</many-to-one>
</joined-subclass>
</hibernate-mapping>

Here's how I execute my delete statement in my session bean:

String queryStr = "delete from PatientScanCode as scancode " +
"where scancode.patientVw.id.partyGuid = ? ";
Query query = session.createQuery(queryStr);
query.setParameter(0, patientId.getPartyGuid());
query.executeUpdate();

I know that the query syntax is correct, the problem comes from the SQL translation made
by Hibernate, here's what it looks like:

delete
from
CDM_PAT_1_0.PATIENT_SCAN_CD_VW
where
(
PATIENT_SCAN_CD_GUID, OPERATING_CNTX_GUID
) IN (
select
PATIENT_SCAN_CD_GUID,
OPERATING_CNTX_GUID
from
HT_PATIENT_SCAN_CD_VW
)

delete
from
CDM_FND_1_0.SCAN_CD_VW
where
(
SCAN_CD_GUID, OPERATING_CNTX_GUID
) IN (
select
PATIENT_SCAN_CD_GUID,
OPERATING_CNTX_GUID
from
HT_PATIENT_SCAN_CD_VW
)

There are 2 delete statements since it has to delete both the child and the parent. In the
from clause in the select statement, the table called HT_PATIENT_SCAN_CD_VW is used, but
it doesn't exist in our model.  I suppose it should be a temporary table created by
hibernate to keep in memory which rows have to be deleted when it's time to delete the
parent table.  The problem is that this table doesn't seem to be created properly
before running delete statements cause I get the following exception when it is called on
the database:

19:04:06,001 ERROR [JDBCExceptionReporter] ORA-00918: column ambiguously defined

19:04:06,017 WARN [MultiTableDeleteExecutor] unable to cleanup temporary id table after
use
java.sql.SQLException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
at
org.hibernate.hql.ast.exec.AbstractStatementExecutor.dropTemporaryTableIfNecessary(AbstractStatementExecutor.java:179)
at
org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:136)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:391)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1134)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at
com.cardinal.dal.session.patient.PatientScanCodeBean.deleteAllByPatient(PatientScanCodeBean.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
at
org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
at
org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.tx.BMTInterceptor.handleStateless(BMTInterceptor.java:71)
at org.jboss.ejb3.tx.BMTInterceptor.invoke(BMTInterceptor.java:131)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:181)
at org.jboss.ejb3.stateless.StatelessLocalProxy.invoke(StatelessLocalProxy.java:79)
at $Proxy866.deleteAllByPatient(Unknown Source)
at
testng.com.cardinal.dal.session.patient.PatientScanCodeTestBean.deleteAllByPatient(PatientScanCodeTestBean.java:101)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
at
org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
at
org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.tx.BMTInterceptor.handleStateless(BMTInterceptor.java:71)
at org.jboss.ejb3.tx.BMTInterceptor.invoke(BMTInterceptor.java:131)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:225)
at org.jboss.aop.Dispatcher.invoke(Dispatcher.java:106)
at
org.jboss.aspects.remoting.AOPRemotingInvocationHandler.invoke(AOPRemotingInvocationHandler.java:82)
at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:828)
at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:681)
at
org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:358)
at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:412)
at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:239)

19:04:06,095 ERROR [PatientScanCodeBean] Error in
com.cardinal.dal.session.patient.PatientScanCodeBean.invoke0():
org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk delete
org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk delete
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at
org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:102)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:391)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1134)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at
com.cardinal.dal.session.patient.PatientScanCodeBean.deleteAllByPatient(PatientScanCodeBean.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
at
org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
at
org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.tx.BMTInterceptor.handleStateless(BMTInterceptor.java:71)
at org.jboss.ejb3.tx.BMTInterceptor.invoke(BMTInterceptor.java:131)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:181)
at org.jboss.ejb3.stateless.StatelessLocalProxy.invoke(StatelessLocalProxy.java:79)
at $Proxy866.deleteAllByPatient(Unknown Source)
at
testng.com.cardinal.dal.session.patient.PatientScanCodeTestBean.deleteAllByPatient(PatientScanCodeTestBean.java:101)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
at
org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
at
org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.tx.BMTInterceptor.handleStateless(BMTInterceptor.java:71)
at org.jboss.ejb3.tx.BMTInterceptor.invoke(BMTInterceptor.java:131)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at
org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:225)
at org.jboss.aop.Dispatcher.invoke(Dispatcher.java:106)
at
org.jboss.aspects.remoting.AOPRemotingInvocationHandler.invoke(AOPRemotingInvocationHandler.java:82)
at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:828)
at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:681)
at
org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:358)
at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:412)
at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:239)

Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
at
org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:93)
... 67 more

Thx to keep me posted on this one
Phil

-- 
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....
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

[Hibernate-JIRA] Created: (HHH-2098) Problem deleting entities in hierarchy