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

Bug #653210 reported by Luis Fernando Planella Gonzalez on 2010-10-01
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Querydsl
Fix Released
High
Unassigned

Bug Description

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)

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.

Changed in querydsl:
importance: Undecided → High

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

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());
    }

Changed in querydsl:
status: New → Fix Committed

Released in 2.0.1

Changed in querydsl:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers