[CALCITE-5177] Query loses hint after decorrelation

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Fix Version/s: 1.31.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Ruben Q L
• Reporter: Ruben Q L
• Created: 02/Jun/22 08:03
• Updated: 03/Aug/22 16:21
• Resolved: 13/Jun/22 07:22

Description

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:

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

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:

...
  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;

The root cause seems to be that, inside RelDecorrelator's code, 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, here, and here), only for projections and aggregates; + RelBuilder#hints to copy them for joins.
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: which currently only propagates hints for projections and aggregates (here, here, and here); and copies hints for joins (here).
• RelStructuredTypeFlattener, which also seems to copy hints only for the "traditional hintables".

Issue Links

• is caused by: CALCITE-5107 Support SQL hint for Filter, SetOp, Sort, Window, Values
• is related to: CALCITE-5188 Review (and improve?) hints
• links to: GitHub Pull Request #2829
• links to: GitHub Pull Request #2831
• links to: PR#2829