Improved propagation of filter predicates on join columns

Detail name	Value
Changelog Number	5984
Type	Improvement
Status	Resolved
Fix Versions	Exasol 6.1.0
Resolution Date	2018-05-08

For queries containing join operations and filter predicates, the latter are propagated to the relations being joined if and only if they touch the join attribute(s). All the predicates except those containing SQL LIKE clauses or UDF calls are propagated.
Consider the query

SELECT * FROM t1, t2
WHERE t1.id = t2.id
AND t2.id > 100;

The optimizer will propagate the predicate `t2.id>100' (as it touches the join attribute) to table t1, thus resulting in the following query:

SELECT * FROM t1, t2
WHERE t1.id = t2.id
AND t2.id > 100 AND t1.id > 100;

Such propagations of predicates often cause a better query execution plans. If the optimizer chooses a join ordering T1, T2, then the sibling predicate `t2.id>100' will be removed as it is redundant, e.g.,

SELECT * FROM t1, t2
WHERE t1.id = t2.id
AND t1.id > 100;

If on the other hand the optimizer chooses a join ordering T2,T1 then the propagated predicate `t1.id>100' will be removed.