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

[Ramesh Reddy (JIRA)]

Ramesh Reddy created TEIID-2569:
-----------------------------------

             Summary: 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