[eclipselink-users] Unexpected SQL created from JPQL query

[Michael Goth <M.Goth@xxxxxx>]

Hi all!

I'm getting unexpected SQL created from a JPQL query in Eclipselink 2.1.0

# JPQL query:

SELECT DISTINCT p
FROM Project p JOIN p.capacities c
WHERE p.status.id = :some_status_id AND p.user.id = :user_id
OR c.employee.person.id = :user_id AND p.status.id = :other_status_id


# Resulting SQL query:

SELECT DISTINCT t0.*
FROM projectcapacity t5, projectemployee t4, person t3,
person t2, value t1, project t0
WHERE (
(
  -- p.status.id = :some_status_id AND p.user.id = :user_id
  ((t1.ID = ?) AND (t2.ID = ?))

  OR

  -- c.employee.person.id = :user_id AND p.status.id = :other_status_id
  ((t3.ID = ?) AND (t1.ID = ?))
)
AND
((((
  (
  -- join condition for p.status.id
  (t1.ID = t0.STATUS_ID) AND (t1.VALUE_CLASS_NAME = ?)
  )
  -- join condition for p.user.id
  AND (t2.ID = t0.USER_ID)
  )
  -- join conditions for c.employee.person.id
  AND (t5.PROJECT_ID = t0.ID)
  )
  AND (t4.ID = t5.EMPLOYEE_ID)
  )
  AND (t3.ID = t4.PERSON_ID)
))

(comments added for a little more clarity)

This query does not return projects that don't have at least one
capacity assigned, because the join condition for that always fails.

*I expected the join conditions to be added to either side of the OR
statement, as they only appear on one side in the JPQL query.*

# Expected SQL query:

SELECT DISTINCT t0.*
FROM projectcapacity t5, projectemployee t4, person t3,
person t2, value t1, project t0
WHERE
(
  (
  -- p.status.id = :some_status_id AND p.user.id = :user_id
  ((t1.ID = ?) AND (t2.ID = ?))
  -- join condition for p.user.id
  AND (t2.ID = t0.USER_ID)

  OR

  -- cp.employee.person.id = :user_id AND p.status.id = :other_status_id
  ((t3.ID = ?) AND (t1.ID = ?))

  -- join conditions for c.employee.person.id
  AND (t5.PROJECT_ID = t0.ID)
  AND (t4.ID = t5.EMPLOYEE_ID)
  AND (t3.ID = t4.PERSON_ID)
  )
  AND
  -- join condition for p.status.id
  (t1.ID = t0.STATUS_ID) AND (t1.VALUE_CLASS_NAME = ?)
)

This statement returns all the projects I expected.

Is this a bug in Eclipselink or expected behavior?

If it's expected behavior, how can I change the JPQL query to create the
second SQL query?


Cheers,
   Michael