Can not use parameters from other entities in joined user-defined function returning a recordset

[weberhofer]

This issue has been nicely described in https://stackoverflow.com/questions/70745246/h2-how-to-call-a-user-defined-function-with-parameters-in-a-join-query

This refers to h2 version 1.4.200

Creation of the function works properly:

CREATE ALIAS lib_item_label FOR "h2.UserDefinedFunctions.libItemLabel";

Using the function with constant values seem to work:

select
    vlil.lbl, li.id, p.id
from lib_item li
    inner join property__type pt on (pt.property_type = 'LANGUAGE')
    inner join property p on (pt.id=p.property_type_id)
    left join lib_item_label(1,1) vlil;

The following is not working...

select
    vlil.lbl
from lib_item li
    inner join property__type pt on (pt.property_type = 'LANGUAGE')
    inner join property p on (pt.id=p.property_type_id)
    left join lib_item_label(li.id, p.id) vlil;

And resulting in the following error:

nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException:
Column "li.id" not found; SQL statement:
select vlil.lbl from lib_item li inner join property__type pt on (pt.property_type = 'LANGUAGE') inner join property p on (pt.id=p.property_type_id) left join lib_item_label(li.id, p.id) vlil [42122-200]

[katzyn]

The second query isn't valid. You cannot pass a column from one table as an argument to a table value function used as another table in a join on the same query level. On the first step of query execution lib_item table is joined with property__type table, property table, and lib_item_label table function. This function produces exactly one table for this query, it cannot be invoked multiple times for each combination of rows constructed from other joined tables. It is invoked with actual arguments only once and all arguments need to be already determined. Rows from other tables aren't yet read. Rows are read only when all tables, including the table produced by this function, are constructed and ready to be joined.

A query from that question on StackOverflow is also incorrect due to the same reason.

[weberhofer]

Thanks @katzyn for your fast and proper explanation!