[teiid-issues] [JBoss JIRA] (TEIID-2569) Inefficient Outer Join compensation when translator restricted on KEY based joins

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2569?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12788206#comment-12788206 ] 

Steven Hawkins commented on TEIID-2569:
---------------------------------------

The issue here is the enforcement of the key restriction.  It expects a left linear join, such that a single group is on the left side.  The join pushing logic however may not consider that form of the join tree (since prior to the key join restriction the rejection would have been symmetric).
                
> Inefficient Outer Join compensation when translator restricted on KEY based joins
> ---------------------------------------------------------------------------------
>
>                 Key: TEIID-2569
>                 URL: https://issues.jboss.org/browse/TEIID-2569
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Query Engine
>            Reporter: Ramesh Reddy
>            Assignee: Steven Hawkins
>
> When the following capabilities are set on ExecutionFactory
> {code}
> setSupportsOuterJoins(true);
> setSupportedJoinCriteria(SupportedJoinCriteria.KEY);
> {code}
> A three table JOIN like
> {code}
> select G1.e1, G2.e1, G3.e1 from G1, G2, G3 where G1.e1=G2.e2 and G2.e2 = G3.e3
> {code}
> gets pushed to translator as two separate queries like
> {code}
> SELECT G3.e3 AS c_0, G3.e1 AS c_1 FROM G3 ORDER BY c_0
> SELECT G2.e2 AS c_0, G1.e1 AS c_1, G2.e1 AS c_2 FROM G1 LEFT OUTER JOIN G2 ON G1.e1 = G2.e2 WHERE G2.e2 IS NOT NULL ORDER BY c_0
> {code}
> instead of of one query which should be like
> {code}
> SELECT G1.e1, G2.e1, G3.e1 FROM G1 INNER JOIN G2 ON G1.e1 = G2.e2 INNER JOIN G3 ON G2.e2 = G3.e3
> {code}
> When the key restriction is removed, it works fine, which should be un-related.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira