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>
