Re: How does HSQLDB resolve which user-defined function to invoke?

HSQLDB can use the correct signature of the function if enough information is available in the SELECT statement. 

SELECT ... WHERE JsonFieldAsText(a, :index) = :value

In the above statement, the type of the :index variable is not known to the SQL parser and it defaults to a VARCHAR type. When writing an SQL statement, JsonFieldAsText(a, CAST(;index AS INT))  can be used to force the INT type.

Fred

On Sat, Jul 29, 2023, at 17:49, Chris Rankin wrote:
> Hi,
>
> I am using HSQLDB 2.7.2, and I am writing some Java functions that act
> like the Postgres "->>" operator, e.g.
>
> a ->> b = field 'b' from JSON object
> a ->> 1 = element [1] from JSON array
>
> So I've mapped two Java functions into HSQLDB, using SQL like this:
>
> CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIndex INT)
>     RETURNS VARCHAR(32768)
>     LANGUAGE JAVA DETERMINISTIC NO SQL
>     EXTERNAL NAME 'CLASSPATH:MyClass.fieldByIndex
>
> CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIName
> VARCHAR(50))
>     RETURNS VARCHAR(32768)
>     LANGUAGE JAVA DETERMINISTIC NO SQL
>     EXTERNAL NAME 'CLASSPATH:MyClass.fieldByName
>
> The idea is that;
>
> "a ->> b" becomes "JsonFieldAsText(a, 'b')", and
> "a ->> 1" becomes "JsonFieldAsText(a, 1)"
>
> This all seems OK, except for when I tried to execute a JPA Query:
>
> "SELECT ... WHERE JsonFieldAsText(a, :index) = :value"
>
> having set the index parameter using "setParameter("index", 2)".
>
> My understanding was that HSQLDB would choose the JsonFieldAsText()
> function whose signature provided the best match to the parameters,
> i.e. the one that maps to MyClass.fieldAsIndex(). However, I was
> alarmed to see HSQLDB choose to execute the MyClass.fieldAsName()
> variant instead.
>
> I have currently "resolved" this issue by swapping the order of my
> CREATE FUNCTION statements so that the one mapping to
> MyClass.fieldAsIndex() is executed first, but I could be relying on
> undocumented behaviour here, for all I know.
>
> Can anyone advise me as to whether there's a better way to do this please?
>
> Thanks,
> Chris
>
>
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user