‘Function join’ sounds similar to ‘CROSS APPLY’ - for each row on the left, call a function to generate a list of rows on the right. Is that how you see it?
If so, there are some beautiful parallels between CROSS APPLY and lateral join; for example, they can both be de-correlated. See [1] and later work in Froid. When I have a spare year, we’ll put these optimizations into Calcite… :) Julian [1] https://www.cse.iitb.ac.in/infolab/Data/Courses/CS632/2014/2009/Papers/subquery-proc-elhemali-sigmod07.pdf > On Jan 30, 2024, at 11:45 PM, Thomas Rynne <thomas@topaz.technology.INVALID> > wrote: > > I have created a RelNode "FunctionJoin" that takes in input relation and the > name of a java method to call to create new rows from an existing row > (Array[Any] => Array[Array[Any]]) > > It's like a join but the rows of right relation are created on demand using > the rows of the left relation. > > I have something working using a converter rule that creates an > EnumerableFunctionJoin that calls the java method for each of the input rows. > I also have rules to move filters & projects past the FunctionJoin to the > FunctionJoin input (I think operating on LogicalCalc might simplify things as > I can't move filters and projects at the same time). > > It has been a good learning experience but before I go too far I thought I > would ask whether this already exists or whether a different approach would > make sense. > > For example, is TableFunctionScan the same thing? It seems similar but I > can't find any example uses and I don't know how I would create a RexNode > that calls a java method. > > thanks for any pointers > Thomas >
