Is your feature request related to a problem or challenge?
Currently, the EliminateOuterJoin rewrite looks for a Filter node directly above a Join node. For most queries, that is the right plan shape to look for (because PushdownFilter will typically place the filters that are useful for outer join elimination directly on top of the relevant Join node). However, some plans don't follow this shape, for at least two reasons:
- Filters on volatile expressions result in a
Projection node between the Filter and the Join
OptimizeProjections might result in placing a Projection node between the Filter and the Join
Notably, we run into case (2) in TPC-DS Q49; we currently fail to convert three outer joins to inner joins for that reason.
In both cases, we can teach EliminateOuterJoin to look for the Filter -> Projection -> Join plan shape as well.
Describe the solution you'd like
No response
Describe alternatives you've considered
No response
Additional context
No response
Is your feature request related to a problem or challenge?
Currently, the
EliminateOuterJoinrewrite looks for aFilternode directly above aJoinnode. For most queries, that is the right plan shape to look for (becausePushdownFilterwill typically place the filters that are useful for outer join elimination directly on top of the relevantJoinnode). However, some plans don't follow this shape, for at least two reasons:Projectionnode between theFilterand theJoinOptimizeProjectionsmight result in placing aProjectionnode between theFilterand theJoinNotably, we run into case (2) in TPC-DS Q49; we currently fail to convert three outer joins to inner joins for that reason.
In both cases, we can teach
EliminateOuterJointo look for theFilter->Projection->Joinplan shape as well.Describe the solution you'd like
No response
Describe alternatives you've considered
No response
Additional context
No response