[HIVE-24564] Extend PPD filter transitivity to be able to discover new opportunities

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Logical Optimizer
• Labels: pull-request-available
• Target Version/s: 4.0.0
• Assignee: Krisztian Kasa
• Reporter: Krisztian Kasa
• Created: 23/Dec/20 16:29
• Updated: 27/Feb/24 22:23
• Resolved: 28/Jan/21 04:52

Description

If a predicate references a value column of one of the parent ReduceSink operators of a Join the predicate can not be copied and pushed down to the other side of the join. However if we a parent equijoin exists in the branch of the RS where
1. the referenced value column is a key column of that join
2. and the other side of that join expression is the key column of the RS
the column in the predicate can be replaced and the new predicate can be pushed down.

                                   Join(... = wr_on)
                                  /                 \
                                ...                  RS(key: wr_on)
                                                          |
                                              Join(ws1.ws_on = ws2.ws_on)
                                              (ws1.ws_on, ws2.ws_on, wr_on)
                                              /                         \
                                  RS(key:ws_on)                          RS(key:ws_on)
                                    (value: wr_on)
                                       |                                       |
                           Join(ws1.ws_on = wr.wr_on)                       TS(ws2)
                           /                        \
                     RS(key:ws_on)              RS(key:wr_on)
                           |                        |
                        TS(ws1)                   TS(wr)

A predicate like

(wr_on in (...))

can not be pushed to TS(ws2) because wr_on is not a key column in Join(ws1.ws_on = ws2.ws_on). But we know that wr_on is equals to ws_on because the join from the left branch.

Issue Links

• links to: GitHub Pull Request #1811