Hibernate version:3.2.5ga


I am experiencing what "appears" to be a bug in the SQL generation during a bulk update HQL query.

Given the following two HQL statements:


A) update MailServer m set m.journal=null where m.journal.id=:mailboxId

AND

B) update MailServer m set m.journal=null where m.journal.sync=:enabled

The former works, while the latter fails with the following error:



The key differences between the two queries are:

1. Query A is using an integer value, which is also a primary key value,
2. Query B is using a boolean field as the parameter

The raw SQL produced by hibernate in each case is:

A) update Recipient set mailboxId=null where mailboxId=?
B) update Recipient, set mailboxId=null where sync=?

We can see that in the latter query there is a weird comma in the middle of the sql, which causes the error.

This is running on an Apache Derby database (v10.3) using the org.hibernate.dialect.DerbyDialect dialect.

Assuming this is a hibernate problem, does anyone know of a work around? I need/want to do a bulk update because the current solution (one-by-one) is just too slow (to the point it's unworkable for us).

Thanks.[/list]

Hi,

Did you found the sollution?
I have the same problem.

Thanks in advance,
Pieter

Nope.. as far as I know it's still a bug

I found it.

It is because I used a join in the where clause.

For example:
update table set column = 1 where columnB.subcolumnA.z = 1

this will result in the error we had.

It's beter to change the query in:
update table set column = 1 where columnB.id in (select id from table.....)



Thanks for the reply,
Pieter

Hmmm.. yeah. I was initially a bit concerned that your "IN" clause may result in an unneccessary row scan, but I think most modern databases "should" be smart enough to realise this is basically a join.

Good work around