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 <ian.bertola...@workday.com.invalid> 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 >
