[
https://issues.apache.org/jira/browse/CALCITE-6167?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17798020#comment-17798020
]
Julian Hyde commented on CALCITE-6167:
--------------------------------------
I wonder whether the problem is the correlated variable rather than the exists
per se. Maybe this rule assumes that decorrelation has already happened. If so,
I wonder what is the right behavior - throw if there is correlation, do nothing
if there is correlation, or continue as is but improve the documentation.
Can you research and see whether this rule (and the similar ones in
{{FilterJoinRule}}) were intended to work with correlated variables?
> 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)
