Hi, I was wondering if there exist any rules to duplicate filters which exist above the join, whose effect is dependent on the result of the join and therefore cannot be *pushed* below a join, but could be *duplicated* below the join.
For example: `select … from A LEFT join B on … where B.field is null` Here, the best we could do is push the filter condition into the join condition, but not necessarily below it, because the null-ness of the column is partially dependent on the result of the join. However, in this case we can duplicate the condition below the join: `select … from A LEFT join (select … from B where B.field is null) as B on … where B.field is null` This is because the condition because the null-ness of the column is also partially dependent value of the column. With both of these filters in place we capture instances of B which are null because the column is null and because there was no match to B This (1) reduced the cardinality of that side of the join, and (2) maintained the original intent of the query. In this example, I use `is null` but we would like to do this for some of our custom comparison operators. For these operators, we cannot do push-down (because it would change the intent of the original query) but doing filter duplication should be fine (though we’re still making sure of that). I figure that this probably doesn’t exist, in which case I’ll probably use FilterIntoJoinRule as a jumping off point. Any other suggestions? Thanks! -Ian J. Bertolacci
