[
https://issues.apache.org/jira/browse/CALCITE-6167?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17798028#comment-17798028
]
SimonAlexs commented on CALCITE-6167:
-------------------------------------
similar question.
> JoinConditionPushRule shouldn't push 'exists' with other table conditions down
> ------------------------------------------------------------------------------
>
> Key: CALCITE-6167
> URL: https://issues.apache.org/jira/browse/CALCITE-6167
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.36.0
> Reporter: SimonAlexs
> Priority: Major
>
> {code:java}
> SELECT *
> from (select 1 a, 2 b) t
> left join (select 2 a, 3 b) r on exists(select 1 from (select 1 a) s where
> s.a=t.a and s.a=r.a) {code}
> In this sql, the RelNode tree is as below(pay attention to the position of
> exists):
>
> {code:java}
> // 1: with FilterJoinRule.JoinConditionPushRule
> LogicalProject(a=[$0], b=[$1], a0=[$2], b0=[$3])
> LogicalJoin(condition=[true], joinType=[left])
> LogicalValues(tuples=[[{ 1, 2 }]])
> LogicalFilter(condition=[EXISTS({
> LogicalFilter(condition=[AND(=($0, $cor0.a), =($0,
> $cor1.a0))])
> LogicalValues(tuples=[[{ 1 }]])
> })])
> LogicalValues(tuples=[[{ 2, 3 }]])
> // 2: normal RelNode tree, without any rule
> LogicalProject(a=[$0], b=[$1], a0=[$2], b0=[$3])
> LogicalJoin(condition=[EXISTS({
> LogicalFilter(condition=[AND(=($0, $cor0.a), =($0,
> $cor1.a0))])
> LogicalValues(tuples=[[{ 1 }]])
> })], joinType=[left])
> LogicalValues(tuples=[[{ 1, 2 }]])
> LogicalValues(tuples=[[{ 2, 3 }]]){code}
> With JoinConditionPushRule, 'exists' is pushed down to the filter of table
> 'r'.But in 'exists', it has the condition 's.a=t.a' which uses the field of
> table 't'.This 'push down' results in that we cann't get values from table
> 't' easily.
> I think this may be a bug.
>
> Full test code is as below.(If the full code is unnecessary, please tell me,
> i will delete it)
> {code:java}
> String sql = "SELECT * from (select 1 a, 2 b) t " +
> "left join (select 2 a, 3 b) r on exists(select 1 from (select 1 a) s
> where s.a=t.a and s.a=r.a) ";
> Properties properties = new Properties();
> properties.put(CalciteConnectionProperty.MODEL.camelName(), "inline:{\n" +
> " \"version\": \"1.0\",\n" +
> " \"defaultSchema\": \"ps\",\n" +
> " \"schemas\": [ ] }");
> Statement calciteStatement = DriverManager.getConnection("jdbc:calcite:",
> properties).createStatement();
> CalcitePrepare.Context prepareContext =
> calciteStatement.unwrap(CalciteServerStatement.class).createPrepareContext();
> final FrameworkConfig config = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.config()
> .withLex(Lex.MYSQL)
> .withConformance(SqlConformanceEnum.MYSQL_5))
> .defaultSchema(prepareContext.getRootSchema().plus())
> .build();
> Planner planner = Frameworks.getPlanner(config);
> SqlNode parsedSql = planner.parse(sql);
> SqlNode validatedSql = planner.validate(parsedSql);
> RelNode originRel = planner.rel(validatedSql).rel;
> // optimize
> HepProgram program = HepProgram.builder()
> .addRuleInstance(CoreRules.JOIN_CONDITION_PUSH)
> .build();
> HepPlanner hepPlanner = new HepPlanner(program);
> hepPlanner.setRoot(originRel);
> RelNode optimizedRel = hepPlanner.findBestExp();
> System.out.println(RelOptUtil.toString(optimizedRel)); {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
