[ https://issues.apache.org/jira/browse/CALCITE-5915?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17754067#comment-17754067 ]
Vladimir Steshin edited comment on CALCITE-5915 at 8/14/23 12:15 PM: --------------------------------------------------------------------- [~shenlang], thank you. I've realized what's going on. We use {code:java} CoreRules.FILTER_SUB_QUERY_TO_CORRELATE, CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE, CoreRules.JOIN_SUB_QUERY_TO_CORRELATE {code} but we have no call of _RelDecorrelator#decorrelateQuery_. And the sub-query transformation omits hints propagation: {code:java} public static RelNode RelOptUtil#propagateRelHints(RelNode originalRel, RelNode equiv) { if (!(originalRel instanceof Hintable) || ((Hintable) originalRel).getHints().size() == 0) { // Exits here. return equiv; } ... } {code} This happens because after the sub-queries change, hints appear below this code's _RelNode originalRel_. It has no hints, the propagation stops. Example: {code:java} LogicalProject LogicalFilter (had no hints, *propagateRelHints(RelNode, RelNode) exists*) LogicalJoin left: TableScan right: LogicalAggregate LogicalProject (has *hints to propagate* down) LogicalFilter LogicalTableScan (*waits for the hints*) {code} To me, it is a bit weird that the propagation checks current node's hints and exists. Hints might be extracted further. Well, I just call _propagateRelHints(RelNode, boolean)_ after that rule set. Works. was (Author: vladsz83): [~shenlang], thank you. I've realized what's going on. We use {code:java} CoreRules.FILTER_SUB_QUERY_TO_CORRELATE, CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE, CoreRules.JOIN_SUB_QUERY_TO_CORRELATE {code} but we have no call of _RelDecorrelator#decorrelateQuery_. And the subquery transformation omits hints propagation: {code:java} public static RelNode RelOptUtil#propagateRelHints(RelNode originalRel, RelNode equiv) { if (!(originalRel instanceof Hintable) || ((Hintable) originalRel).getHints().size() == 0) { // Exists here. return equiv; } ... } {code} This happens because after the subqueries change hints appear below this code's _RelNode originalRel_. It has no hints, the propagation stops. Example: {code:java} LogicalProject LogicalFilter (had no hints, *propagateRelHints(RelNode, RelNode) exists*) LogicalJoin left: TableScan right: LogicalAggregate LogicalProject (has *hints to propagate* down) LogicalFilter LogicalTableScan (*waits for the hints*) {code} To me it is a bit weird that the propagation checks current node's hints and exists. Hints might be extracted further. Well, I just call _propagateRelHints(RelNode, boolean)_ after that rule set. Works. > Missing SQL hints in not-expanded subqueries. > ---------------------------------------------- > > Key: CALCITE-5915 > URL: https://issues.apache.org/jira/browse/CALCITE-5915 > Project: Calcite > Issue Type: Bug > Reporter: Vladimir Steshin > Priority: Minor > Attachments: SqlHintsInSubqueriesWithDisabledExpanding.java, > image-2023-08-13-23-11-56-589.png > > > Not sure if it is a bug becuase the query plans in this case might not be > final and require further expanding, but the SQL hints might not be > propagated to subqueries when SqlToRelConverter#withExpand==false. This > happens because hints are pushed down with > _RelOptUtil#RelHintPropagateShuttle_ and > _RelOptUtil#SubTreeHintPropagateShuttle_ which travese through > _RelNode#getInputs()_. But what if node is not accessible as other node’s > input like keeping in _LogicalFilter#condition_? Then the shuttles skip such > nodes. > Test attached. -- This message was sent by Atlassian Jira (v8.20.10#820010)