[JPQL] Support custom functions in joins

[cnsgithub]

Invocations of custom functions are currently not supported in join paths.

What I would like to do is something like this (currently I am getting an syntax exception what is fine regarding to the spec not supporting it):

select e from entity e join function('tableValuedFunction', <param1>, ..., <paramN>) tvf on tvf.col=e.field

In SQL Server nomenclature, table-valued functions return a table object which can be joined as it was a regular table, see How to: Use Table-Valued User-Defined Functions

Other RDBMS provide similar constructs, e.g. Oracle table functions or PostgreSQL table functions.

My concrete use case is to overcome a SQL Server issue limiting the number of allowed prepared statement parameters to 2100. One of my workarounds worked very well without JPQL and was to provide an user-defined, table-valued function which takes an comma-separated string as input parameter and tokenizes the string by using a delimiter. That way, I need only one parameter to filter more than 2100 values for example:

TSQL:

create function [tokenize](@str nvarchar(max), @delim char(1) = ',') returns @tokens table (token nvarchar(max))
begin
 declare @i bigint 
 while len(@str) > 0
 begin
  set @i = charindex(@delim, @str)
  insert into @tokens select case @i when 0 then @str else left(@str, @i - 1) end
  set @str = case @i when 0 then '' else right(@str, len(@str) - @i) end
 end
 return
end
go

select * from tokenize('a,b,c', ',')

token
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a
b
c

(3 rows affected)

I would like you to think about it, if it is worth to be supported in JPQL?

[dazey3]

Are you referring to JPA 2.2, section 4.6.17.3, but being able to declare this as a join_association_path_expression?

JPQL BNF

...
join ::= join_spec join_association_path_expression [AS] identification_variable [join_condition]
fetch_join ::= join_spec FETCH join_association_path_expression
join_spec::= [ LEFT [OUTER] | INNER ] JOIN
join_condition ::= ON conditional_expression
join_association_path_expression ::= join_collection_valued_path_expression | join_single_valued_path_expression | TREAT(join_collection_valued_path_expression AS subtype) | TREAT(join_single_valued_path_expression AS subtype)
join_collection_valued_path_expression::= identification_variable.{single_valued_embeddable_object_field.}*collection_valued_field
join_single_valued_path_expression::= identification_variable.{single_valued_embeddable_object_field.}*single_valued_object_field
...

[cnsgithub]

Are you referring to JPA 2.2, section 4.6.17.3, but being able to declare this as a join_association_path_expression?

Yes, exactly.

[gavinking]

I don't see how this proposal makes sense.

• We don't support joins to tables in HQL, and it's entirely unclear what the semantics of that would be. We support joins to JPA entities.
• Furthermore, this seems to be a very clear example of a query that is easily written in native SQL and executed via createNativeQuery().

So it seems to me that this issue should be closed.

[beikov]

Unless we consider representing the result type of table valued functions as entities/embeddables, which I think is nice.

[gavinking]

This issue has no votes. I believe that no JPA implementations support it. And I don't believe it makes sense to mix native SQL and JPQL in this fashion. Closing.