[CALCITE-5073] JoinConditionPushRule cannot infer 'LHS.C1 = LHS.C2' from 'LHS.C1 = RHS.C1 AND LHS.C2 = RHS.C1'

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.30.0
• Fix Version/s: 1.31.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Benchao Li
• Reporter: Benchao Li
• Created: 31/Mar/22 08:50
• Updated: 15/Apr/24 03:00
• Resolved: 27/May/22 02:57

Description

For the below SQL:

SELECT * FROM T1, T2, T3
WHERE T1.id = T3.id AND T2.id = T3.id

Currently, JoinConditionPushRule cannot infer the condition 'T1.id = T2.id' for the first join relation.

There is another rule JoinPushThroughJoinRule, it can handle the above case in a different way (by reordering joins).

This can be a more general optimization, which like the title says, we can infer more conditions from join condition (or above filter condition) to the Join's operands.

It's reported in ML: https://lists.apache.org/thread/6fs09cgyz5rzrty90632ywoo477q0gdk

Issue Links

• causes: CALCITE-5195 ArrayIndexOutOfBoundsException when inferring more equal conditions from join condition for semi join
• relates to: CALCITE-6363 Introduce a rule to derive more filters from inner join condition
• links to: GitHub Pull Request #2761