Incorrect SQL query is generated with Subquery and so the query results

[puneetbehl]

It looks like the subquery has a problem with aliases where the parent query alias (this_) conflicts with the subquery alias.

For instance, consider the following domain model:

class User {

    String email

    static mapping = {
        table 'T_USER'
    }
}

class Group {

    String name
    User supervisor

    static mapping = {
        table 'T_GROUP'
    }
}

class GroupAssignment {

    User user
    Group group

    static mapping = {
        table 'T_GROUP_ASSIGNENT'
    }
}

and the criteria:

String supervisorEmail = 'supervisor@company.com'
        def query = User.where {
            def u = User
            exists GroupAssignment.where {
                return user.id == u.id && group.supervisor.email == supervisorEmail
            }
        }
        def result = query.list()

The SQL generated is as:

SELECT
  THIS_.ID AS ID1_2_0_,
  THIS_.VERSION AS VERSION2_2_0_,
  THIS_.EMAIL AS EMAIL3_2_0_
FROM
  T_USER THIS_
WHERE
  EXISTS (
    SELECT
    FROM
      T_GROUP_ASSIGNENT THIS_
      INNER JOIN T_USER USER_ALIAS1_ ON THIS_.USER_ID = USER_ALIAS1_.ID
      INNER JOIN T_GROUP GROUP_ALIA2_ ON THIS_.GROUP_ID = GROUP_ALIA2_.ID
      INNER JOIN T_USER SUPERVISOR3_ ON GROUP_ALIA2_.SUPERVISOR_ID = SUPERVISOR3_.ID
    WHERE
      [*] (
        (USER_ALIAS1_.ID = THIS_.ID)
        AND ((SUPERVISOR3_.EMAIL = ?))
      )
  )

Please note, the table T_USER and T_GROUP_ASSINENT both have the same alias this_. One way to solve this problem would be to change the subquery as:

        exists GroupAssignment.where {
           def ga = GroupAssignment
           return user.id == u.id && group.supervisor.email == supervisorEmail
        }