[CALCITE-3121] VolcanoPlanner hangs due to subquery with dynamic star

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 1.19.0
• Fix Version/s: 1.21.0
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Bohdan Kazydub
• Created: 07/Jun/19 17:34
• Updated: 10/Sep/19 21:37
• Resolved: 04/Jul/19 01:43

Description

After the fix for CALCITE-2798 some queries hang during planning in VolcanoPlanner (similar issue was reported in CALCITE-2223).

Here is a test case which should be added to the RelOptRulesTest class:

  @Test public void testSubQueryWithOrderByHang() {
    String sql = "select n.n_regionkey from ( select * from "
        + "( select * from sales.customer) t order by t.n_regionkey) n where n.n_nationkey >1 ";

    VolcanoPlanner planner = new VolcanoPlanner(null, null);
    planner.addRelTraitDef(ConventionTraitDef.INSTANCE);

    Tester dynamicTester = createDynamicTester().withDecorrelation(true)
        .withClusterFactory(
            relOptCluster -> RelOptCluster.create(planner, relOptCluster.getRexBuilder()));

    RelRoot root = dynamicTester.convertSqlToRel(sql);

    String planBefore = NL + RelOptUtil.toString(root.rel);
    getDiffRepos().assertEquals("planBefore", "${planBefore}", planBefore);

    PushProjector.ExprCondition exprCondition = expr -> {
      if (expr instanceof RexCall) {
        RexCall call = (RexCall) expr;
        return "item".equals(call.getOperator().getName().toLowerCase(Locale.ROOT));
      }
      return false;
    };
    RuleSet ruleSet =
        RuleSets.ofList(
            FilterProjectTransposeRule.INSTANCE,
            FilterMergeRule.INSTANCE,
            ProjectMergeRule.INSTANCE,
            new ProjectFilterTransposeRule(Project.class, Filter .class,
                RelFactories.LOGICAL_BUILDER, exprCondition),
            EnumerableRules.ENUMERABLE_PROJECT_RULE,
            EnumerableRules.ENUMERABLE_FILTER_RULE,
            EnumerableRules.ENUMERABLE_SORT_RULE,
            EnumerableRules.ENUMERABLE_LIMIT_RULE,
            EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE);
    Program program = Programs.of(ruleSet);

    RelTraitSet toTraits =
        root.rel.getCluster().traitSet()
            .replace(0, EnumerableConvention.INSTANCE);

    RelNode relAfter = program.run(planner, root.rel, toTraits,
        Collections.emptyList(), Collections.emptyList());

    String planAfter = NL + RelOptUtil.toString(relAfter);
    getDiffRepos().assertEquals("planAfter", "${planAfter}", planAfter);
  }

Please note that if LIMIT 9999 is added to the sub-query with order by (so it is not removed due to the fix for CALCITE-2798) the test succeeds.

Though the issue with hanging is more general, I think that if it wouldn't be fixed, the fix for CALCITE-2798 should be reverted to reduce cases when planner may hang.

Issue Links

• causes: CALCITE-3188 IndexOutOfBoundsException in ProjectFilterTransposeRule when executing SELECT COUNT(*)
• links to: GitHub Pull Request #1264
• links to: GitHub Pull Request #1292