SimonAlexs created CALCITE-6176:
-----------------------------------
Summary: 'not exists' in 'join on' has a wrong result
Key: CALCITE-6176
URL: https://issues.apache.org/jira/browse/CALCITE-6176
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.36.0
Reporter: SimonAlexs
{code:java}
select *
from (select 1 id from dual) t1
left join (select 2 id from dual) t2
on not exists(select *
from (select 3 a fromdual) p
where p.a=t2.id) {code}
The result of the sql in mysql is:
{code:java}
id id
1 2 {code}
But in calcite(I test with 1.36.0), using flowing rules:
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
CoreRules.JOIN_CONDITION_PUSH
the rel is:
{code:java}
LogicalProject(id=[$0], id0=[$1])
LogicalProject(id=[$0], id0=[$1])
LogicalJoin(condition=[true], joinType=[left])
LogicalValues(tuples=[[{ 1 }]])
LogicalValues(tuples=[[]]) {code}
It's result is:
{code:java}
id id0
1 null {code}
Is this a bug?
My whole code is:
{code:java}
Properties properties = new Properties();
properties.put(CalciteConnectionProperty.MODEL.camelName(), "inline:");
Connection calciteConnection = DriverManager.getConnection("jdbc:calcite:",
properties);
Statement calciteStatement = calciteConnection.createStatement();
CalcitePrepare.Context prepareContext =
calciteStatement.unwrap(CalciteServerStatement.class).createPrepareContext();
CalciteSchema pSpaceSchema = prepareContext.getRootSchema().getSubSchema("ps",
false);
final FrameworkConfig config = Frameworks.newConfigBuilder()
.parserConfig(SqlParser.config()
.withLex(Lex.MYSQL)
.withConformance(SqlConformanceEnum.MYSQL_5))
.sqlValidatorConfig(SqlValidator.Config.DEFAULT)
.defaultSchema(pSpaceSchema.plus())
.build();
Planner planner = Frameworks.getPlanner(config);
SqlNode parsedSql = planner.parse(sql);
SqlNode validatedSql = planner.validate(parsedSql);
RelRoot relRoot = planner.rel(validatedSql);
RelNode originRel = relRoot.rel;
HepProgram program = HepProgram.builder()
.addRuleCollection(Arrays.asList(
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
, CoreRules.JOIN_CONDITION_PUSH
))
.build();
HepPlanner hepPlanner = new HepPlanner(program);
hepPlanner.setRoot(originRel);
RelNode optimizedRel = hepPlanner.findBestExp(); {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
