PS I agree with Stamatis that ARRAY arguments are likely to be an effective workaround.
On the other hand I would love it if someone would study this problem and propose a solution consistent with the standard. Julian > On Apr 7, 2022, at 06:34, Julian Hyde <[email protected]> wrote: > > 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 <[email protected]> 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 >>> <[email protected]> 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 >>> >>>
