[
https://issues.apache.org/jira/browse/CALCITE-4663?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17581267#comment-17581267
]
Ian Bertolacci edited comment on CALCITE-4663 at 8/18/22 10:19 AM:
-------------------------------------------------------------------
We are using 1.30.
I'm not able to provide an exact test case, but I believe that using the below
query with the test provided in the original Jira would work:
{code:java}
select * from
(select * from hr.emps where 0 < deptno and deptno != 100) as _emps
join (select * from hr.depts where 0 < deptno and deptno != 100) as _depts
on _emps.deptno = _depts.deptno{code}
We encountered this in writing our own tests.
Basically, we would provide the below query and expect it to be equivelant to
the above query.
{code:java}
select * from
(select * from hr.emps where 0 < deptno ) as _emps
join (select * from hr.depts where deptno != 100) as _depts
on _emps.deptno = _depts.deptno {code}
But instead we found that running the "expected" query through the planning
pipeline would hang when CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES was used.
We've worked around it by having a match limit of 2 on the HepProgram for that
rule.
was (Author: ian.bertolacci):
We are using 1.30.
I'm able to provide an exact test case, but I believe that using the below
query with the test provided in the original Jira would work:
{code:java}
select * from
(select * from hr.emps where 0 < deptno and deptno != 100) as _emps
join (select * from hr.depts where 0 < deptno and deptno != 100) as _depts
on _emps.deptno = _depts.deptno{code}
We encountered this in writing our own tests.
Basically, we would provide the below query and expect it to be equivelant to
the above query.
{code:java}
select * from
(select * from hr.emps where 0 < deptno ) as _emps
join (select * from hr.depts where deptno != 100) as _depts
on _emps.deptno = _depts.deptno {code}
But instead we found that running the "expected" query through the planning
pipeline would hang when CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES was used.
We've worked around it by having a match limit of 2 on the HepProgram for that
rule.
> And predicate in one subtree of Join causes JoinPushTransitivePredicatesRule
> pulls up predicates infinitely and StackOverflowError
> ----------------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-4663
> URL: https://issues.apache.org/jira/browse/CALCITE-4663
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0, 1.27.0
> Environment: jdk8
> calcite1.27.0
> Reporter: yanjing.wang
> Priority: Major
>
> the query is *select empid from hr.emps where deptno in (select deptno from
> hr.depts where deptno between 1000 and 2000)* and the logical plan tree after
> decorrelated optimization is
> LogicalProject(empid=[$0])
> LogicalJoin(condition=[=($1, $5)], joinType=[inner])
> LogicalTableScan(table=[[hr, emps]])
> LogicalAggregate(group=[\{0}])
> LogicalProject(deptno=[$0])
> LogicalFilter(condition=[*AND(>=($0, 1000), <=($0, 2000))*])
> LogicalTableScan(table=[[hr, depts]])
> the *AND* predicate behaves different with *Search* In
> JoinConditionBasedPredicateInference.
> We can reproduce this problem through adding a test case like
> SortRemoveRuleTest.
> {code:java}
> public final class JoinPushTransitivePredicatesRuleTest {
> @Test
> void conjunctionTransitive() throws Exception {
> SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> SchemaPlus defSchema = rootSchema.add("hr", new HrClusteredSchema());
> FrameworkConfig config = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.Config.DEFAULT)
> .defaultSchema(defSchema)
> .traitDefs(ConventionTraitDef.INSTANCE, RelCollationTraitDef.INSTANCE)
> .build();
> String sql = "select \"empid\" from \"hr\".\"emps\" where \"deptno\" in
> (select \"deptno\" from \"hr\".\"depts\" where \"deptno\" between 1000 and
> 2000)";
> Planner planner = Frameworks.getPlanner(config);
> SqlNode parse = planner.parse(sql);
> SqlNode validate = planner.validate(parse);
> RelRoot planRoot = planner.rel(validate);
> RelNode planBefore = planRoot.rel;
> HepProgram hepProgram = HepProgram.builder()
> // .addRuleInstance(CoreRules.FILTER_REDUCE_EXPRESSIONS)
> .addRuleInstance(CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES)
> .build();
> HepPlanner hepPlanner = new HepPlanner(hepProgram);
> hepPlanner.setRoot(planBefore);
> hepPlanner.findBestExp();
> }
> }
> {code}
> {color:#ff0000}Exception in thread "main" java.lang.StackOverflowError{color}
> The culprit is that the JoinPushTransitivePredicatesRule simplify
> pulledUpPredicates, otherwise JoinConditionBasedPredicateInference not, so
> that the JoinConditionBasedPredicateInference can infer predicates
> indefinitely.
> Though we can add a *CoreRules.FILTER_REDUCE_EXPRESSIONS* before
> *CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES* as a workaround, but I think we
> can simplify left and right child predicates in
> JoinConditionBasedPredicateInference constructor and seems better.
>
> I add simplification for child predicates and StackOverflowError disappears.
> {code:java}
> leftChildPredicates = simplify.simplify(leftPredicates.accept(
> new RexPermuteInputsShuttle(leftMapping, joinRel.getInput(0))));
> rightChildPredicates = simplify.simplify(rightPredicates.accept(
> new RexPermuteInputsShuttle(rightMapping, joinRel.getInput(1))));
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
