Re: [eclipselink-users] Unexpected SQL created from JPQL query

[bht@xxxxxxxxxxxxx]

JPQL works fine as long we do not require other conditions in the
outer join which SQL can do with the ON clause.

See
https://bugs.eclipse.org/bugs/show_bug.cgi?id=312146

which is about adding such a feature which does not yet exist even
with the native EclipseLink API.

Please vote for the isssue.

Regards

Bernard


On Wed, 30 Jun 2010 08:37:29 +0200, you wrote:

>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 
>
>_______________________________________________
>eclipselink-users mailing list
>eclipselink-users@xxxxxxxxxxx
>https://dev.eclipse.org/mailman/listinfo/eclipselink-users