Hi Jiajun, For outer join, the semantic is different for predicates in condition and where, for example: Q1: select * from emp left join dept on emp.deptno = dept.deptno Q2: select * from emp left join dept on true where emp.deptno = dept.deptno
The semantic is different for Q1 and Q2. Q1 will output all the records from emp, including the records which fail to join from dept. However, Q2 will only output the records which successfully join some records from dept. This is the reason why we cannot push/pull the predicates from outer joins conditions. Hope this helps. Jiajun Xie <[email protected]> 于2023年1月12日周四 16:47写道： > Hello, all. > > I try to use RelMetadataQuery#getAllPredicates get predicate, > but I get null for outer join query that left column name is same as right > column name. > ``` > final RelNode rel = sql("select name as dname from emp left outer join > dept" > + " on emp.deptno = dept.deptno").toRel(); > final RelMetadataQuery mq = rel.getCluster().getMetadataQuery(); > final RelOptPredicateList r = mq.getAllPredicates(rel); > assertNull(r); > ``` > > > After commenting on two pieces of code: > 1. RelMdAllPredicates#getAllPredicates > ``` > if (join.getJoinType().isOuterJoin()) { > // We cannot map origin of this expression. > return null; > } > 2. RelMdExpressionLineage#getExpressionLineage > ``` > if (rel.getJoinType().isOuterJoin()) { > // If we reference the inner side, we will bail out > if (rel.getJoinType() == JoinRelType.LEFT) { > ImmutableBitSet rightFields = ImmutableBitSet.range( > nLeftColumns, rel.getRowType().getFieldCount()); > if (inputFieldsUsed.intersects(rightFields)) { > // We cannot map origin of this expression. > return null; > } > } else if (rel.getJoinType() == JoinRelType.RIGHT) { > ImmutableBitSet leftFields = ImmutableBitSet.range( > 0, nLeftColumns); > if (inputFieldsUsed.intersects(leftFields)) { > // We cannot map origin of this expression. > return null; > } > } else { > // We cannot map origin of this expression. > return null; > } > } > I can get the results I need > ``` > final RelNode rel = sql("select name as dname from emp left outer join > dept" > + " on emp.deptno = dept.deptno").toRel(); > final RelMetadataQuery mq = rel.getCluster().getMetadataQuery(); > final RelOptPredicateList r = mq.getAllPredicates(rel); > assertThat(r.pulledUpPredicates.get(0).toString(), > equalTo("=([CATALOG, SALES, EMP].#0.$7, [CATALOG, SALES, > DEPT].#0.$0)")); > ``` > > > It seems that we deliberately return null > in RelMetadataQuery#getAllPredicates . Can someone tell me why? Thanks! > -- Best, Benchao Li
