Is there a method to derive equivalent filters from join filters and others?

Question

Q1:

select * from t1, t2 where t1.a = t2.b and t1.a = 2;

It is equivalent to follow query.

Q2:

select * from t1, t2 where t1.a = t2.b and t1.a = 2 and t2.b = 2;

Now, I use Apache Calcites to generate plan for Q1, and use FilterJoinRule.FILTER_ON_JOIN and FilterJoinRule.JOIN to optimize it. But these rules do not derive additional filter t2.b = 2.

Is there any rules or methods to derive equivalent filter at Calcite? Thanks.

If not, I want to support it. Any suggestion?

score 1 · Accepted Answer · answered May 24 '17 at 18:23

I think you need JoinPushTransitivePredicatesRule or something similar. It works by inferring a RelOptPredicateList from predicates in the join and also present on the inputs, propagating those predicates to the columns on the other side of the join, and pushing them down to the inputs if possible.

For example, given

SELECT *
FROM (SELECT * FROM Emp WHERE deptno >= 10) AS e
JOIN Dept AS d
    ON e.deptno = d.deptno

the rule can pull up the predicate on Emp, move it across the join, and push it down to Dept, resulting in

SELECT *
FROM (SELECT * FROM Emp WHERE deptno >= 10) AS e
JOIN (SELECT * FROM Dept WHERE deptno >= 10) AS d
    ON e.deptno = d.deptno

If there was a predicate in the ON clause then it could be propagated across and down also.

Is there a method to derive equivalent filters from join filters and others?

1 Answers1