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

[Ramesh Reddy (JIRA)]

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

Ramesh Reddy commented on TEIID-2569:
-------------------------------------

Yes, this is the correct one, when inner joins were taken out

{code}
SELECT G1.e1, G2.e1, G3.e1 FROM G1 LEFT OUTER JOIN G2 ON G1.e1 = G2.e2 LEFT OUTER JOIN G3 ON G2.e2 = G3.e3 WHERE G2.e2 IS NOT NULL AND G3.e3 IS NOT NULL
{code}
                
> 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