You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Michael Alexeev <mi...@gmail.com> on 2017/08/31 00:58:04 UTC
JoinPushTransitivePredicatesRule
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
Re: JoinPushTransitivePredicatesRule
Posted by Luis Fernando Kauer <lf...@yahoo.com.br.INVALID>.
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 <mi...@gmail.com> 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
Re: JoinPushTransitivePredicatesRule
Posted by Michael Alexeev <mi...@gmail.com>.
I kind of narrowed down the problem. The RelNode that represents the join
is my own class that extends the Calcite's Join class. The predicateHandler
from the RelMetadataQuery.getPulledUpPredicates throws
the JaninoRelMetadataProvider.NoHandler exception from
the predicateHandler.getPredicates method when my join node is passed as an
argument.
I guess I have to provide my own implementation of the RelMetadataProvider
that can handle the Calcite's RelNode classes plus my own and somehow
register it with the JaninoRelMetadataProvider.
Is this more or less accurate?
If so, is there an example how to implement a custom provider?
Any help is greatly appreciated,
Mike
On Thu, Aug 31, 2017 at 7:06 AM, Michael Alexeev <mi...@gmail.com>
wrote:
> Hi Gautam,
>
> Yes, in case of your query I do the see the filter T2.C2 > 0 being pushed
> down to the T2 scan. But this happens even with or without the
> JoinPushTransitivePredicatesRule.
>
> Mike
>
>
> On Wed, Aug 30, 2017 at 9:39 PM, Gautam Parai <gp...@mapr.com> wrote:
>
>> Hi Michael,
>>
>>
>> Do you see a filter pushdown with the following query?
>>
>> select * from T1 inner join T2 on T1.C = T2.C1 where T2.C2 > 0;
>>
>>
>> Gautam
>>
>> ________________________________
>> From: Michael Alexeev <mi...@gmail.com>
>> Sent: Wednesday, August 30, 2017 5:58:04 PM
>> To: dev@calcite.apache.org
>> Subject: JoinPushTransitivePredicatesRule
>>
>> 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
>>
>
>
Re: JoinPushTransitivePredicatesRule
Posted by Michael Alexeev <mi...@gmail.com>.
Hi Gautam,
Yes, in case of your query I do the see the filter T2.C2 > 0 being pushed
down to the T2 scan. But this happens even with or without the
JoinPushTransitivePredicatesRule.
Mike
On Wed, Aug 30, 2017 at 9:39 PM, Gautam Parai <gp...@mapr.com> wrote:
> Hi Michael,
>
>
> Do you see a filter pushdown with the following query?
>
> select * from T1 inner join T2 on T1.C = T2.C1 where T2.C2 > 0;
>
>
> Gautam
>
> ________________________________
> From: Michael Alexeev <mi...@gmail.com>
> Sent: Wednesday, August 30, 2017 5:58:04 PM
> To: dev@calcite.apache.org
> Subject: JoinPushTransitivePredicatesRule
>
> 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
>
Re: JoinPushTransitivePredicatesRule
Posted by Gautam Parai <gp...@mapr.com>.
Hi Michael,
Do you see a filter pushdown with the following query?
select * from T1 inner join T2 on T1.C = T2.C1 where T2.C2 > 0;
Gautam
________________________________
From: Michael Alexeev <mi...@gmail.com>
Sent: Wednesday, August 30, 2017 5:58:04 PM
To: dev@calcite.apache.org
Subject: JoinPushTransitivePredicatesRule
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