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

I don't know what JPA is doing and how this relies on the order of function creation.

I would suggest using different function names.


On Sat, Jul 29, 2023, at 20:45, Chris Rankin wrote:
> Hi, thanks for replying.
>
> I was hoping that JPA's `setParameter()` API could somehow inform the
> SQL parser about the value's _actual_ type at runtime :-(.
>
> Does this mean that swapping the order of the CREATE FUNCTION
> statement so that the variant with the INT parameter is created first
> is not _supposed_ to make any difference?
>
> Thanks again,
> Chris
>
> On Sat, 29 Jul 2023 at 19:26, Fred Toussi via Hsqldb-user
> <[email protected]> wrote:
>>
>> 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
>>
>>
>> _______________________________________________
>> Hsqldb-user mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user