Hi Ian, FilterJoinRule[1] is designed to not push filters to null-generating side of Join, it has nothing to do with your custom UDF.
[1] https://github.com/apache/calcite/blob/2bf1e7b2e8596a61b5ac19aa7bebb9910c4eea94/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java#L111-L125 Alessandro Solimando <[email protected]> 于2023年8月30日周三 14:13写道： > > Hi Ian, > I did not have time to dig more into your question (not very familiar with > the way you register functions), but for RuleEventLogger you might want to > check these slides (9 to 12 for how to activate it, the rest 15 to 23 on > how to read the output): > https://www.slideshare.net/StamatisZampetakis/debugging-planning-issues-using-calcites-builtin-loggers > . > > Hth, > Alessandro > > > > On Wed, 30 Aug 2023 at 04:14, Ian Bertolacci > <[email protected]> wrote: > > > 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 > > -- Best, Benchao Li
