SQLFunctionTemplate does not apply the order of query parameters

Question

I reduced the problem to this simple template:

//I extend the PostgreSQL dialect because I need a non-standard feature. public class MyDialect extends PostgreSQL82Dialect { public MyDialect() { //With PostgreSQL, "date2 - date1" returns the number of days between the 2 given dates. registerFunction("date_diff", new SQLFunctionTemplate(StandardBasicTypes.LONG, " ((?2) - (?1)) ")); } } 

In this request, I noticed that Hibernate doesn't care about the number after ? in ((?2) - (?1))

Therefore, if I use:

 Expression<Date> date1 = ... Expression<Date> date2 = ... em.getCriteriaBuilder().function("date_diff", Integer.class, date1, date2); 

the call will return the result (date1 - date2) , but I was expecting (date2 - date1) .

Is this a bug or a function? What is the point of providing the number to the parameters?

Answer 1

I think the problem is with parameter binding. I had a similar problem when trying to register a function for DATE_ADD with SQL Server. Here is my method:

 registerFunction("addminutes", new TestSqlFunctionTemplate(TimeMillisType.INSTANCE, "DATEADD(MINUTE, ?2, ?1)")); 

After going through the source code for TemplateRenderer, I found that the problem is how the SQL string is rendered. The rendering function is passed a list of arguments to send, but since the parameters are bound, a list of "?" Strings indicating the associated parameter. The output for the render function when using a query with related parameters in my example:

 DATEADD(MINUTE, ?, ?) 

This does not indicate order. I am looking for an alternative solution, but at the moment I have not seen anything yet.