[ https://issues.apache.org/jira/browse/CALCITE-6176?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
SimonAlexs updated CALCITE-6176: -------------------------------- Summary: 'exists' in 'join on' has a wrong result with CoreRules.JOIN_SUB_QUERY_TO_CORRELATE (was: 'not exists' in 'join on' has a wrong result with CoreRules.JOIN_SUB_QUERY_TO_CORRELATE) > '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 rel of case 2 is as below. The reason I guess is that, The '$1' in 'IS > NOT NULL($1)' of line 3 may be wrong.Because In the rel, the $1 represents > the first column of the right table, which is usually not null depends on > right input data. However, it should represent the column name 'i' in right > table which means the 'exists' result. > This is only my guessing.The real reason for this is needed to find by users > who really understand Calcite. > LogicalProject(id=[$0], id0=[$1]) > LogicalProject(id=[$0], id0=[$1]) > LogicalJoin(condition=[OR(=($0, $1), IS NOT NULL($1))], joinType=[left]) > LogicalValues(tuples=[[\{ 1 }]]) > LogicalCorrelate(correlation=[$cor0], joinType=[left], > requiredColumns=[\{1}]) > LogicalValues(tuples=[[\{ 2 }]]) > LogicalProject(i=[true]) > LogicalFilter(condition=[=($0, $cor0.id0)]) > LogicalValues(tuples=[[\{ 3 }]]) > > 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)