Hello, We have defined some extra comparison functions for our users. However, we’ve noticed that filter push-down (using ` CoreRules.FILTER_INTO_JOIN`) does not occur when the operands to the functions come from the opposite side of an outer join (i.e. from the right side of a left outer join, or from the left side of a right outer join).
Here is a demonstration of this with SQL, the logical plan, and the physical plan post optimization: https://pastebin.com/raw/KjE40z5X In this example, I’ve defined a simple function called “test” which takes a BigInt and returns Boolean. In the first query, the operand to `test` comes from the left side of the left join, and the tree before planning has the filter node above the join, and after planning the filter is below the join. In the second query, the operand to `test` comes from the left side of the right join, and the tree before planning has the filter node above the join, but after planning the filter is still above the join. (I was hoping to get the output from the RuleEventLogger, but I haven’t been able to get it working.) I figure the push down is not happening because we are not properly communicating how these functions handles null (which is to return null, which would be false-y, and therefore allow the join to be converted to an inner join and the filter pushed down below the join). We are not providing these functions as SqlOperator instances in a SqlOperatorTable; instead we use `ScalarFunctionImpl.create` on native methods, and provide those Function instances through the Schema, via `AbstractSchema.getFunctionMultimap` (which eventually make it to the CatalogReader) I’m guessing that if we were providing these via a SqlOperatorTable (which we’re hoping to do sometime soon) that having the return type of the operator be nullable, or have the nullability be inferred from the operands, would allow the planner to push the filter below the join. Am I right? In the meantime, is there a way to provide this nullability information through `ScalarFunctionImpl.create` pathway we’re currently using? Thanks! -Ian J. Bertolacci
