Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Unexpected SQL created from JPQL query 
James Sutherland schrieb:

No, the SQL is correct.  JPQL always defaults to an INNER join, and any null
or empty results will be removed.  You must use an OUTER join, in JPQL use
LEFT OUTER JOIN

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


Works perfectly. Thanks!

  Michael









Michael Goth-2 wrote:

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






-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland http://www.eclipse.org/eclipselink/ 
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink , http://wiki.oracle.com/page/TopLink TopLink Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , http://www.nabble.com/EclipseLink-f26430.html EclipseLink Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence

Back to the top