[jira] [Created] (CALCITE-2666) JoinPushThroughJoinRule can't reach an optimal plan in some 3+ joins cases
Anton Haidai created CALCITE-2666:
Summary: JoinPushThroughJoinRule can't reach an optimal plan in some 3+ joins cases
Issue Type: Bug
Reporter: Anton Haidai
Assignee: Julian Hyde
For example, the input query is the following:
INNER JOIN A
ON X.id = A.id
INNER JOIN Y
ON X.id = Y.id
INNER JOIN Z
ON X.id = Z.id
According to the cost model used, it would be beneficial to push the "A" scan to the right node of the top join (grouping X, Y, Z scans in two bottom joins in any order). But this state is never reached, "A" scan could be pushed only one join up, but never two joins up.
According to my debugging, the cause of the issue is the following.
As far as the optimal state could hypothetically be achieved only by JoinPushThroughJoinRule.RIGHT, lets review only the behavior of this rule (while
JoinPushThroughJoinRule.LEFT is also affected by the issue described). After each transformation, JoinPushThroughJoinRule.RIGHT not only swaps right nodes of joins, but also adds an additional project node on top of transformed joins.
The rule expects the following input structure:
But after applying the rule to two bottom joins, there will be an additional project between these joins and the top join, so the middle join is no longer the left input of the top join and the rule can't match and produce the optimal result. See the attachment for a visual representation of this explanation:
This message was sent by Atlassian JIRA