Bug 408222 - Outer join JPQL results in invalid native SQL
Summary: Outer join JPQL results in invalid native SQL
Status: NEW
Alias: None
Product: z_Archived
Classification: Eclipse Foundation
Component: Eclipselink (show other bugs)
Version: unspecified   Edit
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---   Edit
Assignee: Nobody - feel free to take it CLA
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-05-16 07:01 EDT by Bernard Missing name CLA
Modified: 2022-06-09 10:29 EDT (History)
1 user (show)

See Also:

Attachments
NetBeans project in zip file (20.33 KB, application/zip)
2013-05-16 07:01 EDT, Bernard Missing name CLA 		no flags Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Bernard Missing name CLA 2013-05-16 07:01:42 EDT 
Created attachment 231070 [details]
NetBeans project in zip file

The following query

select
    new dto.ProductWithFavoriteAttributeDTO(p, f.productId)
from
    Product p
    left join p.favorites f
    on f.user = p.user
where
     p.userId = :userId
	 
is validated and translated, but the SQL fails on execution as follows:

Column 'T2.ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list.

Please refer to the attached test case which is a NetBeans ant project that uses the JavaDB database.
Comment 1 Bernard Missing name CLA 2013-05-16 22:54:17 EDT 
In the SQL it tries to use an unnecessary table join T2. Perhaps this can be removed / replaced with using the foreign key, hopefully making life a bit simpler.
Comment 2 Tom Ware CLA 2013-05-24 10:49:03 EDT 
Does this still occur if you set the following persistence unit property:

eclipselink.jpql.parser=org.eclipse.persistence.queries.ANTLRQueryBuilder
Comment 3 Bernard Missing name CLA 2013-05-24 13:14:02 EDT 
Yes it still occurs. Just with a different error: Internal Exception: NoViableAltException(80@[()* loopback of 477:9: (node= join )*]).
Comment 4 Tom Ware CLA 2013-05-24 13:23:45 EDT 
JOIN ON is only supported in the JPA 2.1 specification (implemented in our upcoming 2.5.0 release.  Do you still see the same issue using the latest 2.5.0 milestone?
Comment 5 Bernard Missing name CLA 2013-05-24 13:55:39 EDT 
Sorry I haven't checked that. Could you check it please? I asked myself the question also why the query passes validation while it fails at run time. If JOIN ON is not supported then it should fail validation.
Comment 6 Tom Ware CLA 2013-05-24 14:00:10 EDT 
I agree that it should fail validation if it is not supported.  I believe we have some support of ON in our 2.4.x release that is beyond what is support in the specification, but the support in the spec likely caused us to solidify the support.  We will take a look in the more recent streams when we start to address this bug.  In the meantime, if you are stuck, 2.5.0 will ship soon and may be worth trying.
Comment 7 Bernard Missing name CLA 2013-05-24 14:04:28 EDT 
Thanks. I am not stuck perhaps thanks to the 2.4.x support you mention. As a workaround I join on parameter value instead of table join.
Comment 8 Tom Ware CLA 2013-06-11 09:50:02 EDT 
Setting target and priority.  See the following page for the meanings of these fields:

http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines

Community: Please vote for this bug if it is important to you.  Votes are one of the main criteria we use to determine which bugs to fix next.
Comment 9 Eclipse Webmaster CLA 2022-06-09 10:14:24 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
Comment 10 Eclipse Webmaster CLA 2022-06-09 10:29:06 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink