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

Chris Rankin <[email protected]> Sat, 29 Jul 2023 17:49:14 +0100
Newsgroups gmane.comp.java.hsqldb.user
Message-ID <CAK2bqVJdqDN9_Hfw-RHVL76YvAejGiLknVk+GNMmDoiHL+eiOQ@mail.gmail.com>
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