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

http://docs.jboss.org/hibernate/stable/core/reference/en/html/batch.html#batch-direct — rebelliard, Oct 05 '10 at 12:06
Can you try http://www.codepaste.net/nmrne1 ? Or perhaps http://www.codepaste.net/q5m8on ? It would also rock if you used http://nhprof.com/ — rebelliard, Oct 05 '10 at 13:31
+ 1. Thanks Fafael. It looks like the problem might be: '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.' — Thomas Bratt, Oct 05 '10 at 14:21
Thanks for the code examples. I've tried both but I believe the implicit join is causing the problems. — Thomas Bratt, Oct 05 '10 at 14:23

score 5 · Accepted Answer · answered Oct 06 '10 at 15:35

5

Try this:

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

answered Oct 06 '10 at 15:35

Diego Mijelshon

52,548
16
116
154

Just came back to this problem and your answer worked first time :) – Thomas Bratt Oct 06 '10 at 17:33

score 3 · Answer 2 · answered Oct 05 '10 at 14:44

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

Bozho · Answer 3 · 2010-10-05T14:40:26.860

2

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.

edited Oct 05 '10 at 14:40

answered Oct 05 '10 at 12:03

Bozho

588,226
146
1,060
1,140

@Thomas The FROM is indeed optional when using [DML-style operations](http://docs.jboss.org/hibernate/stable/core/reference/en/html/batch.html#batch-direct). – Pascal Thivent Oct 05 '10 at 19:29

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

3 Answers3

Linked