Pushing left outer joins in a multi-source query

[mike_higgins]

Using Teiid 8.8, I have some SQL similar to the following:

select *

from s1.t1 t1

left outer join s2.t2 t2 on (t1.id = t2.id)

left outer join s2.t3 t3 on (t2.id2 = t3.id2)

where t1.name = 'x'

option makedep s2.t2

t1 is in one database, t2 and t3 are in a different database.  Both databases are Oracle.

Teiid will not push the left outer join from t2 to t3 into the second database, and instead performs the join itself.

If the second join is grouped with parentheses, it will push the outer join to the second database.

This results in much better performance.  In the actual SQL, there are two more outer joins,

which results in a 10x improvement in speed if they are all pushed.

I believe in this case, the outer joins are associative because the on conditions reference only the immediately joined tables.

Besides grouping the joins with parentheses, is there some other hint or option that would allow the outer

joins to be pushed?  Or is the behavior different in a later version of Teiid?

The SQL is generated from a program, so adding parentheses around the second join is possible, but not easy.

I should mention that if all the joins are changed to inner joins, everything works as expected.  But this is not an

option for the queries I am looking at.

Thanks,

Mike Higgins 

[shawkins]

So you have:

(s1.t1 t1 left outer join s2.t2 t2 on (t1.id = t2.id)) left outer join s2.t3 t3 on (t2.id2 = t3.id2)

And you need:

s1.t1 t1 left outer join (s2.t2 t2 left outer join s2.t3 t3 on (t2.id2 = t3.id2)) on (t1.id = t2.id)

That is allowable assuming that the join predicates are not satisfiable for null values (which is true here with the equi-joins).  Unfortunately we aren't yet looking to perform this reordering.  Can you log an issue for that?

[mike_higgins]

Steven,

Thanks for your reply.  I created [TEIID-3652] for the issue.

Mike

[shawkins]

I've added an initial commit, but will likely refine this a little more.  So this will be included in Teiid 8.12 CR1.