[
https://issues.apache.org/jira/browse/CALCITE-4542?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18128864#comment-18128864
]
Iurii Gerzhedovich commented on CALCITE-4542:
---------------------------------------------
I have opened a PR: https://github.com/apache/calcite/pull/5344
I could reproduce the problem on current main, and I believe it is a planner
bug rather than a problem in the test. Using RelOptRule.convert() instead of
copy() in derive() does not change the result, and neither does converting the
inputs to the physical convention in the converter rules.
The problem shows up when a physical rel satisfies none of the subsets being
optimized, and it is implemented after its own subset was already optimized. In
the reproducer, the merge join rule fires from TopDownRuleDriver#convert after
the ANY subset was optimized with the nested loop joins. onProduce then
schedules the rel only for the required SINGLETON subset. There,
getOptimizeInputTask replaces it with its pass-through copy, which DeriveTrait
skips because it is in passThroughCache. So derive() is never called for the
original rel. If passThrough returns null, the rel is skipped entirely.
The PR also derives traits for the original rel, in its own subset. This is
done once per rel, and again after set merges. Pass-through handling is
unchanged.
Just removing "rel = passThroughRel" (as in PR #2487) is not enough. The
pass-through rel is registered with applying == null, so its inputs would no
longer be optimized. That also does not cover the case where passThrough
returns null. There are more details in the PR.
> Suboptimal plan is chosen when TopDownRuleDriver is enabled
> ------------------------------------------------------------
>
> Key: CALCITE-4542
> URL: https://issues.apache.org/jira/browse/CALCITE-4542
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.26.0
> Reporter: Konstantin Orlov
> Assignee: Iurii Gerzhedovich
> Priority: Major
> Labels: pull-request-available
> Attachments: dump.txt
>
>
> When TopDownRuleDriver is enabled, suboptimal plan is chosen for query with
> join.
> We have our own convention and implementation of all necessary relations. A
> distributed join is considered less expensive by our cost system than a
> single-distributed join, and the merge join is considered less expensive than
> nested loop if index over join condition is present. Nevertheless the merge
> join with a single distribution is chosen by optimizer.
> The query is:
> {code:java}
> select e1."empid", e1."deptno" from "emps" e1 join "emps" e2 on e1."empid" =
> e2."empid"
> {code}
> Actual plan is:
> {code:java}
> MyProject(subset=[rel#16:RelSubset#2.MY.single.[]], empid=[$0], deptno=[$1]):
> rowcount = 1500.0, cumulative cost = {1500.0 rows, 3000.0 cpu, 0.0 io}, id =
> 21
> MyMergeJoin(subset=[rel#20:RelSubset#1.MY.single.[]], condition=[=($0,
> $5)], joinType=[inner]): rowcount = 1500.0, cumulative cost = {150.0 rows,
> 0.0 cpu, 0.0 io}, id = 50
> MyExchange(subset=[rel#25:RelSubset#0.MY.single.[]],
> distribution=[single]): rowcount = 100.0, cumulative cost =
> {9210.340371976183 rows, 100.0 cpu, 0.0 io}, id = 30
> MyTableScan(subset=[rel#29:RelSubset#0.MY.any.[0]], table=[[hr,
> emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io},
> id = 27
> MyExchange(subset=[rel#25:RelSubset#0.MY.single.[]],
> distribution=[single]): rowcount = 100.0, cumulative cost =
> {9210.340371976183 rows, 100.0 cpu, 0.0 io}, id = 30
> MyTableScan(subset=[rel#29:RelSubset#0.MY.any.[0]], table=[[hr,
> emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io},
> id = 27
> {code}
> Expected plan is:
> {code:java}
> MyProject(subset=[rel#16:RelSubset#2.MY.single.[]], empid=[$0], deptno=[$1]):
> rowcount = 1500.0, cumulative cost = {1500.0 rows, 3000.0 cpu, 0.0 io}, id =
> 21
> MyExchange(subset=[rel#20:RelSubset#1.MY.single.[]],
> distribution=[single]): rowcount = 1500.0, cumulative cost =
> {18420.680743952365 rows, 100.0 cpu, 0.0 io}, id = 24
> MyMergeJoin(subset=[rel#23:RelSubset#1.MY.any.[]], condition=[=($0, $5)],
> joinType=[inner]): rowcount = 1500.0, cumulative cost = {0.15 rows, 0.0 cpu,
> 0.0 io}, id = 50
> MyTableScan(subset=[rel#26:RelSubset#0.MY.hash[0].[0]], table=[[hr,
> emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io},
> id = 25
> MyTableScan(subset=[rel#26:RelSubset#0.MY.hash[0].[0]], table=[[hr,
> emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io},
> id = 25
> {code}
> Planner [^dump.txt] doesn't contain the join with proper distribution.
> Reproducer could be found
> [here|https://github.com/korlov42/calcite/tree/derive-not-being-called-repoducer].
> Please run {{org.apache.calcite.tools.PlannerTest#test}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
