[CAY-929] SelectTranslator support for standard SQL join syntax, including outer joins

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0
• Fix Version/s: 3.0M4, 3.0
• Component/s: Core Library
• Assignee: Andrus Adamchik
• Reporter: Ari Maniatis
• Created: 03/Dec/07 22:35
• Updated: 08/May/10 10:36
• Resolved: 29/Apr/08 21:23

Description

Currently select translator generates JOIN syntax in an old fashion way - adds all participating tables to the FROM clause of the query, adds join conditions to the WHERE clause. Among other things this limits us to only INNER JOINS on almost all DB's (except for maybe Oracle). Will need to change the translator to generate modern cross-db explicit join syntax that places all tables and conditions in the FROM clause. E.g.:

Old: SELECT ... FROM ARTIST t0, PAINTING t1 WHERE t0.ARTIST_ID = t1.ARTIST_ID
New: SELECT ... FROM ARTIST t0 JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)

Things to consider:

• Check all DbAdapters to see if some override the join generation methods and therefore need to be updated
• This feature does not change the fact that SelectQuery itself still will not support explicit outer joins in the qualifier
• Still the new API for joins should allow callers to specify what kind of join they want (so that we could use it in prefetches down the line)

Issue Links

• is related to: CAY-928 Ordering across relationship should use left join
• is related to: CAY-930 Joint prefetches always result in inner joins
• is related to: CAY-952 Generate OUTER joins for flattened attributes in a SelectQuery
• relates to: CAY-560 Add support for outer joins