Bugzilla – Full Text Bug Listing
| Summary: | Adding Outer Join on OneToOne results in incorrect SQL | ||
|---|---|---|---|
| Product: | z_Archived | Reporter: | Shaun Smith <shaun.smith> |
| Component: | Eclipselink | Assignee: | Project Inbox <eclipselink.orm-inbox> |
| Status: | NEW --- | QA Contact: | |
| Severity: | enhancement | ||
| Priority: | P3 | CC: | christopher.delahunt, michael.f.obrien |
| Version: | unspecified | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Windows XP | ||
| Whiteboard: | |||
>investigating this one next - after bug# 304650 is finished
>Returning to queue (up to 2.2) as other EclipseLink issues precluded enough time to work on this and get it reviewed for 4 Aug
>verify possibly related bug# 301741
keeping this open as a feature request since EclipseLink could be smarter here, but to garantee the correct results the query should instead be: "select cs from CollectedSeries cs LEFT OUTER JOIN cs.series series where series is null" When calling cs.series, it is very much the same thing as calling cs.series.id (where it is more obvious that an inner join is required on the cs -> series relation). The reason it works when JoinFetch is excluded is that EclipseLink has internal optimizations to factor out the cs.series join, enabling it to use the foreign key instead. Had it been a target foreign key relationship (where it is mapped by the other side), it would not have been able to do this optimization. When the JoinFetch is specified, the query processing hits the cs.series and sees there is a joinfetch. Since the join is now required to bring in the series, it assumes the inner join for the cs.series used in the where clause is more restrictive than the outer join defined in the fetch, and just uses the inner join. The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink |
Given a OneToOne relationship, e.g., public class CollectedSeries { ... @OneToOne(fetch=FetchType.EAGER) private Series series; The following query correctly returns all CollectedSeries that don't have an associated Series: JPQL: select cs from CollectedSeries cs where cs.series is null SQL: SELECT ID, NAME, SERIES_ID, PUBLISHER_ID FROM C_SERIES WHERE (SERIES_ID IS NULL) If an outer join is specified on the OneToOne, e.g., public class CollectedSeries { ... @OneToOne(fetch=FetchType.EAGER) @JoinFetch(JoinFetchType.OUTER) private Series series; the same query results in an unnecessary join and no results. JPQL: select cs from CollectedSeries cs where cs.series is null SQL: SELECT t1.ID, t1.NAME, ... FROM gcd_series t0, C_SERIES t1 WHERE ((t1.SERIES_ID IS NULL) AND (t0.ID = t1.SERIES_ID)) I was also able to produce this incorrect SQL by adding a LEFT_FETCH hint to the JPQL query when no @JoinFetch was specified on the OneToOne.