You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Danny Chan (JIRA)" <ji...@apache.org> on 2019/07/02 08:33:00 UTC
[jira] [Comment Edited] (CALCITE-3121) VolcanoPlanner hangs due to
subquery with dynamic star
[ https://issues.apache.org/jira/browse/CALCITE-3121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16876697#comment-16876697 ]
Danny Chan edited comment on CALCITE-3121 at 7/2/19 8:32 AM:
-------------------------------------------------------------
Well, finally i got the reason, now the ProjectFilterTransposeRule will change plan:
{code:xml}
<!-- plan-1 -->
LogicalProject(**=[$0])
LogicalFilter(subset=[rel#40:Subset#6.NONE], condition=[>(ITEM($0, 'N_NATIONKEY'), 1)])
LogicalTableScan(subset=[rel#11:Subset#0.NONE], table=[[CATALOG, SALES, CUSTOMER]])
{code}
to the form like:
{code:xml}
<!-- plan-2 -->
LogicalProject(**=[$0]) (1)
LogicalFilter(condition=[>($1, 1)]) (2)
LogicalProject(**=[$0], ITEM=[ITEM($0, 'N_NATIONKEY')]) (3)
LogicalTableScan(subset=[rel#11:Subset#0.NONE], table=[[CATALOG, SALES, CUSTOMER]])
{code}
and the plan:
{code:xml}
<!-- plan-3 -->
LogicalProject(**=[$0], ITEM=[ITEM($0, 'N_NATIONKEY')])
LogicalFilter(subset=[rel#15:Subset#2.NONE], condition=[>(ITEM($0, 'N_NATIONKEY'), 1)])
LogicalProject(subset=[rel#13:Subset#1.NONE], **=[$0])
LogicalTableScan(subset=[rel#11:Subset#0.NONE], table=[[CATALOG, SALES, CUSTOMER]])
{code}
to the form like:
{code:xml}
<!-- plan-4 -->
LogicalFilter(condition=[>($1, 1)]) (4)
LogicalProject(**=[$0], ITEM=[ITEM($0, 'N_NATIONKEY')])
LogicalTableScan(subset=[rel#11:Subset#0.NONE], table=[[CATALOG, SALES, CUSTOMER]]){code}
See that (4) has same digest as (2), while these 4 plans are actually in the same RelSet, so this forms a circle.
The plan-2 has more cost that the old one, because it always add a redundant project (3), if we also have FilterProjectTransposeRule in the rule set, it will also trigger infinite match of the ProjectMergeRule for project (1) and new project (3).
Actually this ProjectFilterTransposeRule transformation is meaningless, because it just add a useless project in the plan compared to the old one, so i just skip conversion for such pattern.
was (Author: danny0405):
Well, finally i got the reason, now the ProjectFilterTransposeRule will change plan:
{code:xml}
LogicalProject(**=[$0])
LogicalFilter(subset=[rel#40:Subset#6.NONE], condition=[>(ITEM($0, 'N_NATIONKEY'), 1)])
LogicalTableScan(subset=[rel#11:Subset#0.NONE], table=[[CATALOG, SALES, CUSTOMER]])
{code}
to the form like:
{code:xml}
LogicalProject(**=[$0]) (1)
LogicalFilter(condition=[>($1, 1)]) (2)
LogicalProject(**=[$0], ITEM=[ITEM($0, 'N_NATIONKEY')]) (3)
LogicalTableScan(subset=[rel#11:Subset#0.NONE], table=[[CATALOG, SALES, CUSTOMER]])
{code}
This new plan has more cost that the old one, because it always add a redundant project (3), if we also have FilterProjectTransposeRule in the rule set, it will also trigger infinite match of the ProjectMergeRule for project (1) and new project (3).
Actually this ProjectFilterTransposeRule transformation is meaningless, because it just add a useless project in the plan compared to the old one, so i just skip conversion for such pattern.
> VolcanoPlanner hangs due to subquery with dynamic star
> ------------------------------------------------------
>
> 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
> Fix For: 1.21.0
>
> Time Spent: 40m
> 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)