[jira] Updated: (DERBY-4405) Transformation to inner join not performed for certain three-way joins

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-4405?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-4405:
---------------------------------

    Attachment: derby-4405.stat
                derby-4405.diff

Uploading a patch which lets the two variant outer join statements be rewritten to
inner join, by looking not just at the top level inner table for match when analyzing
the null intolerant predicates, but checking against tables in nested inner join (inner join in the inner position of an outer join) as well.
Added a repro test case to JoinNode which checks for the presence of a hash join to
indicate the rewrite did happen (not used before the rewrite).

Running regressions.


> Transformation to inner join not performed for certain three-way joins
> ----------------------------------------------------------------------
>
>                 Key: DERBY-4405
>                 URL: https://issues.apache.org/jira/browse/DERBY-4405
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>         Attachments: derby-4405.diff, derby-4405.stat
>
>
> In the CROSS JOIN section in the reference manual (http://db.apache.org/derby/docs/dev/ref/rrefsqljcrossjoin.html) there are three examples that are supposed to be equivalent. However, the performance differs significantly between the different queries.
> The queries use the tours db and look like this:
> (1)
> SELECT * FROM CITIES LEFT OUTER JOIN
>     (FLIGHTS CROSS JOIN COUNTRIES)
>         ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
>             WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'
> (2)
> SELECT * FROM CITIES LEFT OUTER JOIN
>     FLIGHTS INNER JOIN COUNTRIES ON 1=1
>         ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
>             WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'
> (3)
> SELECT * FROM CITIES LEFT OUTER JOIN
>     (SELECT * FROM FLIGHTS, COUNTRIES) S
>         ON CITIES.AIRPORT = S.ORIG_AIRPORT
>             WHERE S.COUNTRY_ISO_CODE = 'US'
> When executed in ij, (1) and (2) need 6 seconds to complete, whereas (3) completes in 50 ms.
> The query plans for (1) and (2) use nested loop joins and table scans. (3) uses a combination of hash join and nested loop join, and index scans as well as table scans.
> It looks like (3) has been rewritten from a left outer join to an inner join internally. This is fine because all rows that have the right-side columns filled with NULLs will be filtered out by the predicate S.COUNTRY_ISO_CODE='US', so the extra rows generated by the outer join will not be returned.
> This optimization should also be possible for (1) and (2). We should improve the logic so that those joins are transformed too. The transformation happens in HalfOuterJoinNode.transformOuterJoins().

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.