While reading the standard and implementing function resolution for built-in and user-defined functions I got the impression that there are very different resolution strategies for the two kinds of functions.
There’s a “Chesterton fence” between the two, and anyone proposing to remove or blur the distinction, or just dig in the vicinity as we are doing here with variadic UDFs, needs to demonstrate they understand why it’s there. It relates especially to parameter names and overloading: * UDFs are resolved by name alone, not by number or types of arguments * UDF parameters may have names and default values and arguments may be specified in different orders when the function is invoked * built in functions do not have named parameters and therefore arguments must be specified in a fixed order See the work I did on made parameters, read the code, the tests. It’s also possible that recent standards work (see polymorphic table functions) has changed the landscape. Julian > On Apr 6, 2022, at 23:16, Stamatis Zampetakis <zabe...@gmail.com> wrote: > > Hi Ian, > > From what I recall the work under CALCITE-2772 is an attempt to allow > introducing vararg UDFs in a more user friendly way. > > Supporting vararg UDFs via Schema and Function interfaces is one way to go > although without CALCITE-2772 probably this is not possible. > > Another way (and more powerful) would be to use the SqlOperator interface > (either directly or extend some existing implementation) and create your > own customised operator. Then you can plug-in your own operator table with > the custom UDFs in the validator [1]. > Note that currently Calcite has some vararg functions/operators and the > first that comes to mind is CONCAT [2]. You may check the changes > introduced by CALCITE-4394 [3] to learn more about this and get inspiration > if you end-up going down the path of implementing your own SqlOperator. > > Lastly, you could possibly avoid varargs UDF using a small trick that was > sufficient for me in some use-cases. Make the UDF accept a parameter of > type ARRAY and call it by wrapping the function arguments in an ARRAY > constructor. For instance, the queries would look like the following: > > SELECT MY_CUSTOM_UDF(ARRAY['A','B','C']) > SELECT MY_CUSTOM_UDF(ARRAY['A','C']) > > Best, > Stamatis > > [1] > https://github.com/zabetak/calcite-tutorial/blob/31cce59c747e0b763a934109db6a6e7055f175ae/solution/src/main/java/com/github/zabetak/calcite/tutorial/LuceneQueryProcessor.java#L166 > [2] > https://github.com/apache/calcite/blob/a81cfb2ad001589929e190939cf4db928ebac386/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java#L491 > [3] https://issues.apache.org/jira/browse/CALCITE-4394 > >> On Tue, Apr 5, 2022 at 12:15 AM Ian Bertolacci >> <ian.bertola...@workday.com.invalid> wrote: >> >> Howdy! >> We’re trying to add a vararg/variadic UDF, but cannot seem to make it work >> out. >> >> In our system, we define our UDFs to the Schema’s function multimap, and >> so have classes with methods which are provided to >> ScalarFunctionImpl.create(theClass, “methodName” ) to create the Function >> object, and from which the parser/validator draws the SQL function >> signature. >> However, there doesn’t seem to be a way do define such a method where >> ScalarFunctionImpl infers the variadic function signature, but rather as >> accepting a single List parameter. >> >> I see that CALCITE-2772 was raised to solve this but the work seems to >> have stalled in review. >> >> Is there any plan to restart this work? >> And in the mean time, is there a good way of defining variadic UDFs? >> >> One solution we’ve come up with is to define a function with all (except >> the first) optional parameters so that it *looks* like a variadic function, >> but this is limited to 254 parameters by the JVM. >> While 254 parameters is quite a lot, we predict that our users may exceed >> this limit. >> Thanks! >> -Ian J. Bertolacci >> >>
