SimonAlexs created CALCITE-6167:
-----------------------------------
Summary: 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
{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)
