problem in a delete where clause on a OneToOne relationship

zhamdi · **Posted:** Wed Jan 02, 2008 10:26 am

3.3

The generated SQL (show_sql=true):
delete from WorkExecution where plan_id=? and (id is null) and state=?

this sql is generated for the following query:

Code:

Query query = em.createQuery( "DELETE FROM " + WorkExecution.class.getSimpleName() + " e WHERE e.entriesPlan.id = :id AND e.invoiceWorkDetail=null AND e.state = :temp " );

where e.invoiceWorkDetail is as follows:

WorkExecution.java

Code:

   @OneToOne(mappedBy = "workExecution", fetch = FetchType.EAGER, cascade = {
         CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH
   })
   public InvoiceWorkDetail getInvoiceWorkDetail() {
      return invoiceWorkDetail;
   }

So as you see, the relationship I'm trying to write in the where clause is to say: the workExecution doesn't have any child in the table InvoiceWorkDetail. (As I'd do in java)

Unfortunately it is translated to a non sens (id is null)

Anyone has an idea? any suggestion is very welcome

Regards,
Zied

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Zhamdi,

I don't like this

Quote:

AND e.invoiceWorkDetail=null

Shouldn't an "is null' be there?

zhamdi · **Posted:** Fri Jan 04, 2008 5:27 am

Hi gonzao,

I gave up because I'm too busy: I resolved it in java.

I don't think it's the problem: it is translated to "id is null". The problem is that hibernate doesn't do an inner select but translates simple to "id is null" notice it's e.invoiceWorkDetail that must be null: that means in sql: there is no child in the child table.

Thanks for your suggestion.