We are running into issues with bulk deletes. It works for simple cases such as "delete from MappedClass where mappedProperty = value", but it does not work for more complex HQL. For instance, trying to use an alias in a query results in an exception "unexpected token". Trying to use an association without an alias results in "count not resolve property" exception (see examples below) reported on the association name. Trying to access the properties of the mapped class without an alias results in the generated SQL having no table alias but an alias used in front of the property, which leads to an Oracle exception of invalid query.

Is there a known limitation on the HQL that can be used with bulk delete?
Are there plans to improve it?

Hibernate version:
3.0.3 - 3.0.5

Mapping documents:
Happens with any mapped object. We are using associations, for instance IcdDiagnoses is associated with Pathology that is associated with Patient via man-to-one:
<!-- bi-directional many-to-one association to Pathology -->
<many-to-one
name="pathology"
class="gov.va.med.domain.model.Pathology"
update="false"
insert="false"
>
<column name="PATHOLOGY_ID_SEQ" />
</many-to-one>


Code between sessionFactory.openSession() and session.close():
String hqlDelete = "delete from gov.va.med.domain.model.PathologyIcdDiagnoses dto where dto.id = :userIdSeq";

// or
// String hqlDelete = "delete from gov.va.med.domain.model.PathologyIcdDiagnoses dto where dto.pathology.patient = :userIdSeq";
Query query = getSession().createQuery(hqlDelete);
query.setString("userIdSeq", userIdSeq.toString());
int count = query.executeUpdate();


Full stack trace of any exception that occurs:
<Jul 21, 2005 10:28:19 AM EDT> <Error> <org.hibernate.hql.PARSER> <000000> <*** ERROR: line 1:59: unexpected token: dto>
10:28:19,927 ERROR PARSER:111 - *** ERROR: line 1:59: unexpected token: dto
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: dto near line 1, column 59 [delete from gov.va.med.domain.model.PathologyIcdDiagnoses dto where dto.pathology.patient = :userIdSeq]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:865)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at gov.va.med.domain.persistence.orm.dao.PathologiesDAO.deleteCategoryRecords(PathologiesDAO.java:58)
at gov.va.med.mhv.business.healthrecord.bean.HealthRecordServiceImpl.clearHealthRecords(HealthRecordServiceImpl.java:63)
at gov.va.med.mhv.business.healthrecord.bean.HealthRecordServiceImpl$$FastClassByCGLIB$$f01b0ab5.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:661)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:122)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:606)
at gov.va.med.mhv.business.healthrecord.bean.HealthRecordServiceImpl$$EnhancerByCGLIB$$d1b11d19.clearHealthRecords(<generated>)
at gov.va.med.mhv.business.healthrecord.bean.HealthRecordServiceTest.main(HealthRecordServiceTest.java:26)
Caused by: line 1:59: unexpected token: dto
at org.hibernate.hql.antlr.HqlBaseParser.deleteStatement(HqlBaseParser.java:242)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:139)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)
... 17 more
Name and version of the database you are using:
Oracle 9i

The generated SQL (show_sql=true):
Error is thrown in query parsing

Debug level Hibernate log excerpt:

<Jul 21, 2005 11:17:55 AM EDT> <Error> <org.hibernate.hql.PARSER> <000000> <*** ERROR: line 1:59: unexpected token: dto>
11:17:55,405 ERROR PARSER:111 - *** ERROR: line 1:59: unexpected token: dto
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: dto near line 1, column 59 [delete from gov.va.med.domain.model.PathologyIcdDiagnoses dto where dto.id = :userIdSeq]

----------------------------------------------------
<Jul 21, 2005 10:28:19 AM EDT> <Error> <org.hibernate.hql.PARSER> <000000> <*** ERROR: line 1:59: unexpected token: dto>
10:28:19,927 ERROR PARSER:111 - *** ERROR: line 1:59: unexpected token: dto

org.hibernate.hql.ast.QuerySyntaxError: unexpected token: dto near line 1, column 59 [delete from gov.va.med.domain.model.PathologyIcdDiagnoses dto where dto.pathology.patient = :userIdSeq]


--------------------------
org.hibernate.QueryException: could not resolve property: patient of: gov.va.med.domain.model.PathologyIcdDiagnoses [delete from gov.va.med.domain.model.PathologyIcdDiagnoses where pathology.patient = :userIdSeq]

I should have read the documentation more carefully, it does state that the aliases are not supported. Well, that kind of sucks but I still like Hibernate. However, some of the attempts I made did not rely on aliases, for instance running this bulk delete:

String hqlDelete = "delete from gov.va.med.domain.model.PathologyIcdDiagnoses where id.pathologyId in (" +
"select dto.id from " + getModelClass().getName() + " dto where dto.patient = :userIdSeq)";
Query query = getSession().createQuery(hqlDelete);
query.setString("userIdSeq", userIdSeq.toString());
int count = query.executeUpdate();

I got this exception:


Hibernate: delete from PATHOLOGY_ICD_DIAGNOSES where pathologyi0_.PATHOLOGY_ID_SEQ in (select pathology1_.PATHOLOGY_ID_SEQ from PATHOLOGIES pathology1_ where pathology1_.USER_ID_SEQ=?)
11:27:52,093 WARN JDBCExceptionReporter:100 - SQL Error: 904, SQLState: 42000
<Jul 21, 2005 11:27:52 AM EDT> <Error> <org.hibernate.util.JDBCExceptionReporter> <000000> <ORA-00904: "PATHOLOGYI0_"."PATHOLOGY_ID_SEQ": invalid identifier
>
11:27:52,193 ERROR JDBCExceptionReporter:111 - ORA-00904: "PATHOLOGYI0_"."PATHOLOGY_ID_SEQ": invalid identifier

org.hibernate.exception.SQLGrammarException: could not execute update query

"id" is a key mapped as follows:
<composite-id name="id" class="gov.va.med.domain.model.PathologyIcdDiagnosesPK">
<key-property
name="pathologyId"
column="PATHOLOGY_ID_SEQ"
type="java.lang.Long"
length="22"
>
</key-property>
<key-property
name="icdCode"
column="ICD_CODE"
type="java.lang.String"
length="10"
>
</key-property>
</composite-id>

Current CVS supports aliases.

I have just tried it with Beta 1 and still didn't get HQL to work. Here's what I ran:
String hql = "delete from gov.va.med.domain.model.PathologyIcdDiagnoses object where object.pathology.patient.id = :userIdSeq";
logger.debug("Executing HQL: " + hql);
Query query = getSession().createQuery( hql );
query.setString( "userIdSeq" , userIdSeq.toString());
int count = query.executeUpdate();

and here's what I got:

09:36:19,506 DEBUG PathologiesDAO:84 - Executing HQL: delete from gov.va.med.domain.model.PathologyIcdDiagnoses object where object.pathology.patient.id = :userIdSeq
<Jul 22, 2005 9:36:19 AM EDT> <Error> <org.hibernate.hql.PARSER> <000000> <line 1:59: unexpected token: object>
09:36:20,077 ERROR PARSER:111 - line 1:59: unexpected token: object
java.lang.NoSuchMethodError: org.hibernate.hql.antlr.HqlBaseParser.recover(Lantlr/RecognitionException;Lantlr/collections/impl/BitSet;)V
at org.hibernate.hql.antlr.HqlBaseParser.optionalFromTokenFromClause(HqlBaseParser.java:400)
at org.hibernate.hql.antlr.HqlBaseParser.deleteStatement(HqlBaseParser.java:259)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:148)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:231)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:150)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:100)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:459)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:902)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:883)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)


--------------------------------------------------------------
Running this HQL delete:

"delete from gov.va.med.domain.model.PathologyIcdDiagnoses where pathology.patient.id = :userIdSeq"

(PathologyIcddiagnoses has a many-to-one association with Pathology and Pathology has a many-to-one with Patient) I got this exception:

09:40:14,454 DEBUG PathologiesDAO:84 - Executing HQL: delete from gov.va.med.domain.model.PathologyIcdDiagnoses where pathology.patient.id = :userIdSeq
org.hibernate.QueryException: could not resolve property: patient of: gov.va.med.domain.model.PathologyIcdDiagnoses [delete from gov.va.med.domain.model.PathologyIcdDiagnoses where pathology.patient.id = :userIdSeq]
at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:37)
at org.hibernate.persister.entity.BasicEntityPersister.toType(BasicEntityPersister.java:1131)
at org.hibernate.hql.ast.tree.FromElementType.getPropertyType(FromElementType.java:274)
at org.hibernate.hql.ast.tree.FromElement.getPropertyType(FromElement.java:353)
at org.hibernate.hql.ast.tree.DotNode.getDataType(DotNode.java:517)
at org.hibernate.hql.ast.tree.DotNode.prepareLhs(DotNode.java:217)
at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:172)
at org.hibernate.hql.ast.tree.DotNode.resolveFirstChild(DotNode.java:139)
at org.hibernate.hql.ast.HqlSqlWalker.lookupProperty(HqlSqlWalker.java:394)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.addrExpr(HqlSqlBaseWalker.java:4315)


Mapping document for Pathology:
<!-- bi-directional many-to-one association to Patient -->
<many-to-one
name="patient"
class="gov.va.med.domain.model.Patient"
not-null="true"
>
<column name="USER_ID_SEQ" />
</many-to-one>

"object" is a reserved word