[SQL] DML with a subquery.notExists() is generating a invalid statement

[Luis Fernando Planella Gonzalez]

I have a very simple test model with Person and Department, where person has a FK to department.

These are the Q-types:
        QPerson p = QPerson.person;
        QDepartment d = QDepartment.department;

The following command:
        DB.delete(d).where(DB.subQuery(p).where(p.departmentId.eq(d.id)).notExists()).execute();
Is generating the following SQL:
delete from department
where not exists (select person
from person person
where person.department_id = id)

It seems there are 2 things wrong:
* The outer delete is not generating an alias, so the inner d.id is being translated as id alone, which is not right, and should be department.id
* The select person alone is invalid. Ideally, the .exists() and .notExists() would generate something like select 1 ...

Likewise, an update has the same error:

    DB.update(d).set(d.name, "AAA").where(DB.subQuery(p).where(p.departmentId.eq(d.id)).notExists()).execute();
Generates (also missing alias and column on the select part):
update department
set name = 'AAA'
where not exists (select person
from person person
where person.department_id = department.id)

[Timo Westkämper (timo-westkamper)]

Thanks for the bug report. It is a bug in the path serialization for DML clauses. Apparently in subqueries the full path needs always to be used.

[Timo Westkämper (timo-westkamper)]

Luis, what's the SQL engine you are using here?

[Timo Westkämper (timo-westkamper)]

Got both fixed :

    @Test
    public void Delete_with_SubQuery_exists(){
        QSurvey survey1 = new QSurvey("s1");
        QEmployee employee = new QEmployee("e");
        SQLDeleteClause delete = new SQLDeleteClause(connection, SQLTemplates.DEFAULT,survey1);
        delete.where(survey1.name.eq("XXX"), new SQLSubQuery().from(employee).where(survey1.id.eq(employee.id)).exists());
        assertEquals("delete from SURVEY\n" +
              "where SURVEY.NAME = ? and exists (select 1\n" +
              "from EMPLOYEE2 e\n" +
              "where SURVEY.ID = e.ID)", delete.toString());
    }

[Timo Westkämper (timo-westkamper)]

Released in 2.0.1