Hi Tom,
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
--
Michael Bouschen Tech_at_Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin