Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[eclipselink-dev] ANSI inner join after LEFT OUTER JOIN

Hi Tom,

I am looking at resolving remaining open issues on Symfoware for the LRG
test sets.

Currently my biggest issue is the 'Inner Join Keyword' we have discussed
before (also on the Wiki). About 1000 tests in Core LRG fail because of
it, and about 50 in Core JPA.

I'd like to understand more about your comments:

-----------------------------------------------
There was the suggestion that we could "fake" this feature by using an
OUTER JOIN instead. I recommend against that strategy because of the two
reasons someone would choose to use joining

    * A fetch join is used to improve the efficiency of a query. As more
outer joins are added to a query, the result set gets big at an
exponential rate. This removes any benefit one would get from the FETCH join
    * A non-fetch join is used to limit results. Adding an outer join
means that the results are no longer limited in the same way.
-----------------------------------------------

I have gone through all the failures, to check what kind of SQL
statement the inner join was used in.
It turns out that in all of them a LEFT OUTER JOIN would give the same
result set as an INNER JOIN:

* Use of secondary tables
  E.g. complexJoinTest:

CMP3_EMPLOYEE t0 JOIN CMP3_SALARY t1 ON (t1.EMP_ID = t0.EMP_ID)

* Use of join tables with relationships
  E.g. testProjectOuterJoinTeamMembersJoinAddress:

CMP3_EMP_PROJ t5 JOIN CMP3_EMPLOYEE t2 ON (t2.EMP_ID = t5.EMPLOYEES_EMP_ID

* Combination of both of the above
  E.g. testLargeProjects:

CMP3_EMP_PROJ t5 JOIN CMP3_PROJECT t3 ON (t5.PROJECTS_PROJ_ID =
t3.PROJ_ID) JOIN CMP3_LPROJECT t4 ON (t4.PROJ_ID = t3.PROJ_ID))

With the secondary table, for each row in the primary table the
secondary table will have a corresponding row, so there will never be
any 'unmapped' rows on the LEFT side.

With the join table, as it joins two tables, there are no rows where the
foreign key is NULL, so no 'unmapped' rows on the LEFT side.

I have spent a few days trying to come up with a pattern where the LEFT
OUTER JOIN would return a different result set, but no matter how I play
around with the table order or combinations, the join table is always
placed on the left side.

Also, in the source where these SQL statements are built
(SQLSelectStatement#appendFromClauseForOuterJoin), we have the following
line that prints the table on the left side of the JOIN:


writer.write(relationTable.getQualifiedNameDelimited(printer.getPlatform()));

The variable name 'relationTable' seems to be in line with my findings
in the test results that it is always a join table on the left.

I understood your comments to be based on the difference between inner
and outer joins in general, where outer joins can (and are generally
used for that purpose) return also unmapped rows from one of the tables
in its result set, so if we return these unwanted results the queries
will be less efficient. Is that correct?
>From what I have seen so far it appears that in this part of the code
there are never any unmapped rows, so it is safe to use a LEFT OUTER
JOIN for Symfoware.

Thanks,
Dies



Back to the top