[jira] [Comment Edited] (CALCITE-3121) VolcanoPlanner hangs due to removing ORDER BY from sub-query

["Danny Chan (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16862010#comment-16862010 ] 

Danny Chan edited comment on CALCITE-3121 at 6/12/19 11:20 AM:
---------------------------------------------------------------

If we change the sql to
{code:java}
String sql = "select n.n_regionkey from ( select * from "
    + "( select n_regionkey, n_nationkey from sales.customer) t) n where n.n_nationkey >1 ";
{code}
The query will not hang, so i believe it is the ProjectMergeRule that can not handle the pattern correctly,
{code:java}
project
|-filter
|---project(star(*))
|-----scan{code}


was (Author: danny0405):
If we change the sql to
{code:java}
String sql = "select n.n_regionkey from ( select * from "
    + "( select n_regionkey, n_nationkey from sales.customer) t) n where n.n_nationkey >1 ";
{code}
The query will not hang, so i believe it the ProjectMergeRule that can not handle the pattern correctly,
{code:java}
project
|-filter
|---project(star(*))
|-----scan{code}

> VolcanoPlanner hangs due to removing ORDER BY from sub-query
> ------------------------------------------------------------
>
>                 Key: CALCITE-3121
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3121
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.19.0
>            Reporter: Bohdan Kazydub
>            Assignee: Danny Chan
>            Priority: Blocker
>              Labels: pull-request-available
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> 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:
> {code:java}
>   @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);
>   }
> {code}
> 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.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)