[
https://issues.apache.org/jira/browse/CALCITE-5177?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17545515#comment-17545515
]
Ruben Q L commented on CALCITE-5177:
------------------------------------
Problem can be reproduced on Calcite master in {{{}SqlHintsConverterTest{}}}.
- In {{SqlHintsConverterTest.xml}} add the resources for the new tests:
{noformat}
<TestCase name="testHintsPropagationInVolcanoPlannerRules2">
<Resource name="sql">
<![CDATA[select /*+ my_hint */ ename, job
from emp where not exists (select 1 from dept where emp.deptno = dept.deptno)
]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
LogicalProject(ENAME=[$1], JOB=[$2])
LogicalFilter(condition=[IS NULL($10)])
LogicalJoin(condition=[=($7, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject(DEPTNO=[$0], $f0=[true])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
EnumerableProject(ENAME=[$1], JOB=[$2])
EnumerableFilter(condition=[IS NULL($10)])
EnumerableMergeJoin(condition=[=($7, $9)], joinType=[left])
EnumerableSort(sort0=[$7], dir0=[ASC])
EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableProject(DEPTNO=[$0], $f0=[true])
EnumerableTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
<TestCase name="testHintsPropagationInVolcanoPlannerRules3">
<Resource name="sql">
<![CDATA[select /*+ my_hint */ ename, job
from emp where not exists (select 1 from dept where emp.deptno = dept.deptno)
order by ename
]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
LogicalSort(sort0=[$0], dir0=[ASC])
LogicalProject(ENAME=[$1], JOB=[$2])
LogicalFilter(condition=[IS NULL($10)])
LogicalJoin(condition=[=($7, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject(DEPTNO=[$0], $f0=[true])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableProject(ENAME=[$1], JOB=[$2])
EnumerableFilter(condition=[IS NULL($10)])
EnumerableMergeJoin(condition=[=($7, $9)], joinType=[left])
EnumerableSort(sort0=[$7], dir0=[ASC])
EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableProject(DEPTNO=[$0], $f0=[true])
EnumerableTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
{noformat}
- In {{SqlHintsConverterTest.java}} add a new dummy hint:
{code:java}
static HintStrategyTable createHintStrategies(HintStrategyTable.Builder
builder) {
return builder
.hintStrategy("my_hint", HintPredicates.JOIN)
.hintStrategy("no_hash_join", HintPredicates.JOIN)
.hintStrategy("time_zone", HintPredicates.SET_VAR)
...
{code}
- The following test will run fine (hint gets correctly propagated to the
Join):
{code:java}
@Test void testHintsPropagationInVolcanoPlannerRules2() {
final String sql = "select /*+ my_hint */ ename, job\n"
+ "from emp where not exists (select 1 from dept where emp.deptno =
dept.deptno)";
final RelHint hint = RelHint.builder("MY_HINT")
.inheritPath(0, 0)
.build();
// Validate Volcano planner.
RuleSet ruleSet = RuleSets.ofList(
MockEnumerableJoinRule.create(hint) // Rule to validate the hint.
);
ruleFixture()
.sql(sql)
.withVolcanoPlanner(false, p -> {
p.addRelTraitDef(RelCollationTraitDef.INSTANCE);
RelOptUtil.registerDefaultRules(p, false, false);
ruleSet.forEach(p::addRule);
})
.check();
}
{code}
- However, a similar test with an ORDER BY, will fail: the hint does not
arrive at the Join (the debugger proves that the hint gets lost during
decorrelation, as stated in the description):
{code:java}
@Test void testHintsPropagationInVolcanoPlannerRules3() {
final String sql = "select /*+ my_hint */ ename, job\n"
+ "from emp where not exists (select 1 from dept where emp.deptno =
dept.deptno) order by ename";
final RelHint hint = RelHint.builder("MY_HINT")
.inheritPath(0, 0, 0)
.build();
// Validate Volcano planner.
RuleSet ruleSet = RuleSets.ofList(
MockEnumerableJoinRule.create(hint) // Rule to validate the hint.
);
ruleFixture()
.sql(sql)
.withVolcanoPlanner(false, p -> {
p.addRelTraitDef(RelCollationTraitDef.INSTANCE);
RelOptUtil.registerDefaultRules(p, false, false);
ruleSet.forEach(p::addRule);
})
.check();
}
{code}
> Query loses hint after decorrelation
> ------------------------------------
>
> Key: CALCITE-5177
> URL: https://issues.apache.org/jira/browse/CALCITE-5177
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Ruben Q L
> Priority: Blocker
> Fix For: 1.31.0
>
>
> This seems to be a regression caused by CALCITE-5107, which enlarged the list
> of hintable RelNodes by making Filter, SetOp, Sort, Window, Values hintable.
> However, it seems that this patch "missed" some Calcite preprocessing
> elements that can change a plan, and therefore require a manual / ad-hoc
> treatment of hints to avoid losing them in their processing, e.g.
> RelDecorrelator.
> In my specific example, let's have this query:
> {code:sql}
> SELECT /*+ MY_HINT_FOR_JOIN */ c.c_custkey, , c.c_name FROM customer c
> WHERE NOT EXISTS (
> SELECT 1 FROM orders o WHERE o.o_custkey = c.c_custkey AND o.o_orderstatus
> <> 'abc'
> ) ORDER BY c.c_custkey
> {code}
> Before CALCITE-5107, when the query was parsed, a logical plan was created,
> my hint was attached to a LogicalProject. The plan contained a
> LogicalCorrelate (to implement the NOT EXISTS). The plan was then
> decorrelated with RelDecorrelator, and as a result, we obtained a new plan
> with a LogicalJoin (instead of correlate), where the hint had been propagated
> from the projection until the join. Everything is fine.
> After CALCITE-5107, when the query was parsed, now we obtain the same logical
> plan, but now the hint is attached to the LogicalSort (not to the
> LogicalProject). When the decorrelator is executed, the plan is transformed
> (to have LogicalJoin), but {*}the hint is lost{*}, it is not in the new
> plan's sort (or project, or join, it's nowhere).
> Running the debugger, it seems the problem is inside
> RelDecorrelator#decorrelateQuery:
> {code:java}
> ...
> if (!decorrelator.cm.mapCorToCorRel.isEmpty()) {
> newRootRel = decorrelator.decorrelate(newRootRel); // <-- HINT LOST HERE!
> }
> // NOTHING GETS PROPAGATED BECAUSE THE HINT WAS LOST!
> newRootRel = RelOptUtil.propagateRelHints(newRootRel, true);
>
> return newRootRel;
> {code}
> The root cause seems to be that, inside [RelDecorrelator's
> code|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java],
> there are only a few places where hints are copied from the "old" RelNode to
> the newly created RelNode: 3 calls to RelOptUtil#copyRelHints
> ([here|https://github.com/apache/calcite/blob/63a15128a332af7a641b26a3224392960882170a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L589],
>
> [here|https://github.com/apache/calcite/blob/63a15128a332af7a641b26a3224392960882170a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L685],
> and
> [here|https://github.com/apache/calcite/blob/63a15128a332af7a641b26a3224392960882170a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L796]),
> only for projections and aggregates; + RelBuilder#hints [to copy them for
> joins|https://github.com/apache/calcite/blob/63a15128a332af7a641b26a3224392960882170a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L1308].
> To sum up, it seems RelDecorrelator only cared to copy hints for the
> "traditional hintables", so probably something like that is missing for sorts
> (and other newly hintable RelNodes added by CALCITE-5107).
> Apart from RelDecorrelator, other classes that might suffer from a similar
> problem are:
> -
> [RelFieldTrimmer|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java]:
> which currently only propagates hints for projections and aggregates
> ([here|https://github.com/apache/calcite/blob/63a15128a332af7a641b26a3224392960882170a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java#L535],
>
> [here|https://github.com/apache/calcite/blob/63a15128a332af7a641b26a3224392960882170a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java#L574],
> and
> [here|https://github.com/apache/calcite/blob/63a15128a332af7a641b26a3224392960882170a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java#L1115]);
> and copies hints for joins
> ([here|https://github.com/apache/calcite/blob/63a15128a332af7a641b26a3224392960882170a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java#L909]).
> -
> [RelStructuredTypeFlattener|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java],
> which also seems to copy hints only for the "traditional hintables".
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
