Hi Benchao, Thanks very much for your email. I understand `RelMetadataQuery#getAllPredicates` cannot get all predicates because some optimized cases cannot push/pull predicates.
But I think the function name and code comment[1] are misleading, *getAllPredicates* cannot get all predicates. And your case should be handled by `RelMetadataQuery#getPulledUpPredicates`[2] instead of `RelMetadataQuery#getAllPredicates`. [1] https://github.com/apache/calcite/blob/413eded693a9087402cc1a6eefeca7a29445d337/core/src/main/java/org/apache/calcite/rel/metadata/RelMetadataQuery.java#L855 [2] https://github.com/apache/calcite/blob/413eded693a9087402cc1a6eefeca7a29445d337/core/src/main/java/org/apache/calcite/rel/metadata/RelMetadataQuery.java#L836 On Sun, 15 Jan 2023 at 21:12, Benchao Li <libenc...@apache.org> wrote: > 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 <jiajunbernou...@gmail.com> 于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 >