The TableFunctionScan indeed seems to fit exactly the "FunctionJoin" use-case. If the TableFunctionScan alone cannot fulfil your use-case then I am pretty sure that a combination of Correlate + TableFunctionScan should do the trick. The examples in the Calcite codebase are a bit scarce but you can get some inspiration by looking at the test cases of SqlToRelConverter [1].
Creating a RexNode to call a java method is a separate topic. If you want to call arbitrary java methods the probably you have to create a custom SqlOperator/SqlFunction (maybe extending SqlUserDefinedFunction [2] and/or implementing the ImplementableFunction interface [3]) where one of the arguments is a character literal specifying the canonical name of the method. Instead of allowing calls to arbitrary java methods via reflection it may be wiser to implement dedicated SqlOperators for each separate case you need. Giving the user the option to call arbitrary methods via reflection opens up security holes for the application. Best, Stamatis [1] https://github.com/apache/calcite/blob/351ddeb47b8dfb5c196c563920290a79575e9864/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml [2] https://github.com/apache/calcite/blob/351ddeb47b8dfb5c196c563920290a79575e9864/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedFunction.java [3] https://github.com/apache/calcite/blob/351ddeb47b8dfb5c196c563920290a79575e9864/core/src/main/java/org/apache/calcite/schema/ImplementableFunction.java On Wed, Jan 31, 2024 at 9:09 AM Julian Hyde <[email protected]> wrote: > > ‘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 > > <[email protected]> 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 > > >
