[
https://issues.apache.org/jira/browse/CALCITE-6176?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
SimonAlexs updated CALCITE-6176:
--------------------------------
Description:
In calcite 1.36.0, using flowing rules:
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
CoreRules.JOIN_CONDITION_PUSH
I tested 3 wrong cases(the expected result and actual result are in comment):
* expected result: the result in mysql;
* actual result: the result in calcite1.36.0
{code:java}
-- condition: not exists, expected: 1, 2
-- actual: 1, null
select *
from (select 1 id) t1
left join (select 2 id) t2
on not exists(select *
from (select 3 id) p
where p.id=t2.id)
-- condition: or exists, expected: 1, null
-- actual: 1, 2
select *
from (select 1 id) t1
left join (select 2 id) t2
on t1.id=t2.id or exists(select *
from (select 3 id) p
where p.id=t2.id)
-- condition: or not exists, expected: 1, 2
-- actual: 1, null
select *
from (select 1 id) t1
left join (select 2 id) t2
on t1.id=t2.id or not exists(select *
from (select 3 id) p
where p.id=t2.id){code}
This seems a bug. the reason
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}
was:
In calcite 1.36.0, using flowing rules:
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
CoreRules.JOIN_CONDITION_PUSH
I test
{code:java}
{code}
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}
> 'not exists' in 'join on' has a wrong result with
> CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
> ---------------------------------------------------------------------------------------
>
> 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
> Priority: Critical
>
> In calcite 1.36.0, using flowing rules:
> CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
> CoreRules.JOIN_CONDITION_PUSH
> I tested 3 wrong cases(the expected result and actual result are in comment):
> * expected result: the result in mysql;
> * actual result: the result in calcite1.36.0
> {code:java}
> -- condition: not exists, expected: 1, 2
> -- actual: 1, null
> select *
> from (select 1 id) t1
> left join (select 2 id) t2
> on not exists(select *
> from (select 3 id) p
> where p.id=t2.id)
> -- condition: or exists, expected: 1, null
> -- actual: 1, 2
> select *
> from (select 1 id) t1
> left join (select 2 id) t2
> on t1.id=t2.id or exists(select *
> from (select 3 id) p
> where p.id=t2.id)
> -- condition: or not exists, expected: 1, 2
> -- actual: 1, null
> select *
> from (select 1 id) t1
> left join (select 2 id) t2
> on t1.id=t2.id or not exists(select *
> from (select 3 id) p
> where p.id=t2.id){code}
> This seems a bug. the reason
>
>
>
> 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)
