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:
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.
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:
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
Apart from RelDecorrelator, other classes that might suffer from a similar problem are: