Hi,
Actually, I think FilterJoinRule is the rule that does what you want.
Are you using JDBC Adapter?
The following test worked for me as expected:
@Test public void testJoinWithJoinKeyAndFilterPlan() {
CalciteAssert.model(JdbcTest.SCOTT_MODEL)
.query("select e.empno, e.ename, d.deptno, d.dname \n"
+ "from scott.emp e inner join scott.dept d \n"
+ "on e.deptno = d.deptno \n"
+ "and e.deptno='20'")
.explainContains("PLAN=JdbcToEnumerableConverter\n"
+ " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$3], DNAME=[$4])\n"
+ " JdbcJoin(condition=[=($2, $3)], joinType=[inner])\n"
+ " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n"
+ " JdbcFilter(condition=[=($7, '20')])\n"
+ " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ " JdbcProject(DEPTNO=[$0], DNAME=[$1])\n"
+ " JdbcTableScan(table=[[SCOTT, DEPT]])")
.runs()
.enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
.planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+ "\"t1\".\"DEPTNO\", \"t1\".\"DNAME\"\n"
+ "FROM (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n"
+ "FROM \"SCOTT\".\"EMP\"\n"
+ "WHERE \"DEPTNO\" = '20') AS \"t0\"\n"
+ "INNER JOIN (SELECT \"DEPTNO\", \"DNAME\"\n"
+ "FROM \"SCOTT\".\"DEPT\") AS \"t1\" ON \"t0\".\"DEPTNO\" =
\"t1\".\"DEPTNO\"");
}
Best regards,
Luis Fernando
Em Quarta-feira, 30 de Agosto de 2017 21:58, Michael Alexeev
<[email protected]> escreveu:
Hi All,
Consider a query
select * from T1 inner join T2 on T1.C = T2.C1 and T2.C2 > 0;
I would expect that the JoinPushTransitivePredicatesRule would push the T2.C2
> 0 expression as a filter down to the inner scan over the T2 table, right?
But, for whatever reason, the inferred RelOptPredicateList
<https://calcite.apache.org/apidocs/org/apache/calcite/plan/RelOptPredicateList.html>
has
both left and right predicate lists empty inside
JoinPushTransitivePredicatesRule.onMatch call thus the whole original ON
expression stays at the join node.
Is my expectation wrong? If so, is there a way to push inner/outer filters
to corresponding join nodes?
Thanks,
Mike
