Confirming this as a TopLink bug, updated the issue.
Michael, if you are done with the parsing part of this bug, please assign it
to me (ailitche)
Thanks,
Andrei
----- Original Message -----
From: "Michael Bouschen" <Michael.Bouschen_at_Sun.COM>
To: <persistence_at_glassfish.dev.java.net>
Sent: Wednesday, September 20, 2006 8:21 AM
Subject: Question about delete issue 674
> Hi Tom,
>
> resend using the correct email.
>
> I started looking at the P2 delete issue "EJBQL delete query with IS
> NULL in WHERE clause produces wrong sql":
> https://glassfish.dev.java.net/issues/show_bug.cgi?id=674
>
> The issue is about the generated SQL for a JPA query deleting instances
> where a relationship field is not set, e.g.:
> DELETE FROM Employee e WHERE e.manager IS NULL
>
> Here is the TopLink query code (and I hope it is correct):
> ExpressionBuilder eBuilder = new ExpressionBuilder(Employee.class);
> DeleteAllQuery delete = new DeleteAllQuery(Employee.class, eBuilder);
> delete.setSelectionCriteria(eBuilder.get("manager").isNull());
> delete.setShouldDeferExecutionInUOW(false);
> Object result = em.getActiveSession().executeQuery(delete);
>
> The runtime generates different SQL depending on whether the class is
> mapped to a single table or not. I think the SQL is ok for the original
> scenario described in the issue: the class is mapped to multiple tables,
> see (1) below. But we still have an issue in the case the class is
> mapped to a single table, see (2) below. I was able to reproduce the
> problem using the TopLink query code from above. So maybe the problem is
> in the backend and not in the parser. What do you think. Would it make
> sense that I add my findings to the issue and assign it to you?
>
> (1) The Employee class is mapped to multiple tables, e.g. when using a
> secondary table.
> The runtime generates multiple SQL DELETE statements, one for each
> table. The first DELETE statement removes rows from the secondary table,
> if there is a row in the primary table with a manager being NULL.
> DELETE FROM SALARY_TABLE
> WHERE EXISTS(
> SELECT t0.ID FROM FULL_PERSON_TABLE t0, SALARY_TABLE t1
> WHERE ((t0.MANAGER_ID IS NULL) AND ((t1.ID = t0.ID) AND
> (t0.DISCRIMINATOR = ?))) AND t1.ID = SALARY_TABLE.ID)
> The second DELETE statement removes rows from the primary table where
> there is no corresponding entry in the secondary table:
> DELETE FROM FULL_PERSON_TABLE
> WHERE (DISCRIMINATOR = ?) AND NOT EXISTS(
> SELECT t0.ID FROM FULL_PERSON_TABLE t0, SALARY_TABLE t1
> WHERE ((t1.ID = t0.ID) AND (t0.DISCRIMINATOR = ?)) AND
> t0.ID = FULL_PERSON_TABLE.ID)
> I think this is ok as long as the runtime always inserts a row in the
> secondary table, even if none of the field mapped to the secondary table
> are set.
>
> (2) The Employee class is mapped to a single table.
> The generated SQL includes an invalid IS NULL clause:
> DELETE FROM EMPLOYEE_TABLE WHERE ( IS NULL)
> I was hoping that this was fixed together with the fix for issues 672
> and 675 which describe a similar problem with the generated SQL for
> SELECT queries. I tested the DELETE again and it is still failing. Do
> you have an idea what is going wrong here?
>
> Regards Michael
>
>