In praise of predicates

[Julian Hyde <jh...@apache.org>]

I am excited about the possibilities of predicates[1]. We already have ways to create (deduce) predicates, and ways to use them to make smart transformations:
* We deduce a predicate when a Filter constrains a column to equal a constant (e.g. Filter(x = 10));
* In https://issues.apache.org/jira/browse/CALCITE-953 Pencheng added a metadata provider to deduce a predicate when a Project returns a literal;
* ReduceExpressionsRule notices predicates of the form ‘c = constant’ or ‘c is null’ and reduces expressions involving c accordingly;
* JoinPushTransitivePredicatesRule moves predicates on one input to a join to the other input, if they are connected by an equi-join condition.

But I think this is just the start. I think that along with RelNodes, rules, and traits, predicates are an important ingredient in the planning process. I believe that we can generate predicates for more types of RelNode, and that we can use predicates in more creative ways in rules. 

For example, suppose you have predicates ["deptno BETWEEN 10 and 20”, “sal = CASE WHEN deptno < 5 THEN 100 WHEN deptno > 15 THEN 200 ELSE 150 END”] and you know that deptno is an INTEGER, and you are optimizing “Filter(sal > 120)". We could iterate over the 11 possible values of deptno and deduce that sal is either 150 or 200, and therefore “sal > 120” is always true.

(It sounds esoteric, but those kinds of functional dependencies happen a lot when dealing with date dimensions. If the number of values is fairly small, we don’t need sophisticated theorem provers: we can brute force by evaluating an expression over each possible value.)

Predicates can be used to deduce an upper bound on a cardinality of a column. This would be useful for cases like https://issues.apache.org/jira/browse/CALCITE-945 and https://issues.apache.org/jira/browse/CALCITE-873 <https://issues.apache.org/jira/browse/CALCITE-873>.

And, if there are multiple equivalent RelNodes in the same set, we can union their predicates. If a predicate holds for one, it holds for them all.

I wanted to share my excitement about predicates, and ask you to log JIRAs if you can think of creative ways to use them.

Julian

[1] http://calcite.apache.org/apidocs/org/apache/calcite/rel/metadata/RelMdPredicates.html <http://calcite.apache.org/apidocs/org/apache/calcite/rel/metadata/RelMdPredicates.html>