Why does this HQL delete fail, when an HQL select with same terms works?

Question

Why does the following HQL query fail?

string hql = @"delete MyLog log
               where
                    log.UtcTimestamp < :threshold and
                    log.Configuration.Application = :application";

session.CreateQuery(hql)
       .SetDateTime("threshold", threshold)
       .SetEnum("application", this.application)
       .ExecuteUpdate();

The same form of query works when used in a select:

string hql = @"from MyLog log
               where
                    log.UtcTimestamp < :threshold and
                    log.Configuration.Application = :application";
IList<MyLog> log = session.CreateQuery(hql)
    .SetDateTime("threshold", threshold)
    .SetEnum("application", this.application)
    .List<MyLog>();

The mapping for MyLog contains:

References(x => x.Configuration)
     .Columns("CONFIGURATION_ID")
     .ReadOnly();      

The mapping for Configuration contains:

Map(x => x.Application, "APPLICATION_ID");

The error I get is:

delete from MYLOG, CONFIGURATION countercon1_ where UTC_TIMESTAMP<:p0 and APPLICATION_ID=:p1; :p0 = 04/10/2010 17:15:52, :p1 = 7

NHibernate.Exceptions.GenericADOException: could not execute update query [SQL:

delete from MYLOG, CONFIGURATION countercon1_ where UTC_TIMESTAMP< ? and APPLICATION_ID= ?

] ---> Oracle.DataAccess.Client.OracleException: ORA-00933: SQL command not properly ended

Answer 1

The syntax is DELETE FROM MyLog ....

Have in mind that HQL delete does not honour cascades defined with (n)hibernate mappings.

So you can select all the entities and delete them one by one.

Answer 2

From the link submitted by Rafael above:

http://docs.jboss.org/hibernate/stable/core/reference/en/html/batch.html#batch-direct

No joins, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins

Answer 3

Try this:

delete MyLog log
where log.id in
          (select l.id
           from MyLog l
           where l.UtcTimestamp < :threshold and
           and.Configuration.Application = :application)