unexpected token: '<function_name>' error when using hibernate, JPQL and postgres function

Question

I'm using PostgreSQL 9.6 with Hibernate 5.4.8, Java 8 and Spring framework. I need to call a postgres function

CREATE OR REPLACE FUNCTION function_that_return_array(givenIds character varying(255)) RETURNS int[] AS
'
BEGIN
    RETURN string_to_array($1,'','');
END
' LANGUAGE plpgsql;

within JPQL query

private static final String JPQL_QUERY =
    " SELECT NEW com.package.CustomProjection( " +
    "  e.id, " +
    "  e.value " +
    " ) " +
    " FROM SomeEntity e " +
    " WHERE e.id = ANY(function_that_return_array(:ids))";

and using entity manager:

@Autowired
private final EntityManager entityManager;

// ...

this.entityManager.createQuery(JPQL_QUERY, CustomProjection.class)
                .setParameter("ids", "1,2,3")
                .getResultList();

and it results in following exception:

antlr.NoViableAltException: unexpected token: function_that_return_array
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1055) [hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:748) [hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.subQuery(HqlBaseParser.java:3910) [hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.quantifiedExpression(HqlBaseParser.java:3515) [hibernate-core-5.4.8.Final.jar:5.4.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3373) [hibernate-core-5.4.8.Final.jar:5.4.8.Final]
...
antlr.MismatchedTokenException: expecting EOF, found ')'
    at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar:?]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:215) [hibernate-core-5.4.8.Final.jar:5.4.8.Final]

The above example is very simplified but it correctly represents the production problem. When I call above function in native SQL it works perfectly:

select *
from some_entity e 
where e.id = ANY(function_that_return_array('1,2,3,4'))

Does anyone know how to call a postgres function within JPQL and Hibernate or can point me out what I am doing wrong? I was reading many articles like this one, SO questions and I was trying dozens of combinations but with no success so far. Thanks in advance.

lukaszwrzaszcz · Accepted Answer · 2021-06-24 08:08:52Z

1

In Hibernate dialect you cannot call directly not registered custom database functions. The exception in clear, hibernate knows nothing about your function:

unexpected token: function_that_return_array

You have two options here:

Call your function by generic mechanism for custom functions:

use

function('function_that_return_array', '1,2,3,4')

instead of

function_that_return_array('1,2,3,4')

Second option is to register your function: https://docs.jboss.org/hibernate/orm/5.1/javadocs/org/hibernate/dialect/Dialect.html#registerFunction-java.lang.String-org.hibernate.dialect.function.SQLFunction-

Example:

public class MyDialect extends PostgreSQLXXDialect {
    public MyDialect() {
        super();
        registerFunction("function_that_return_array", new StandardSQLFunction("function_that_return_array"));
    }
}

edited Jun 24, 2021 at 8:08

answered Jun 24, 2021 at 7:54

lukaszwrzaszcz

1901 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lukasz Blasiak Over a year ago

Thank you, unfortunately when using generic mechanism it throws the same exception with message unexpected token: function. I have also tried second approach with dialect registration. The breakpoint has been triggered inside constructor so my postgres function has been registered indeed. In addition I have called static getFunctions() method and it returned correctly our postgres function. But it still throws the same exception. Maybe it is a matter of library version or some missing dependency?

Lukasz Blasiak Over a year ago

Or maybe it's because of function invocation inside ANY operator? Or some special quotes are required as well?

lukaszwrzaszcz Over a year ago

function_that_return_array returns array of integers, so you can just use IN (...) operator, this is an equivalent for "ANY"

Lukasz Blasiak Over a year ago

when I use IN operator it throws following error: SQL Error [42883]: ERROR: operator does not exist: integer = integer[]. I've tried to use TABLE return type instead of ARRAY but hibernate fails as well. I've tried dozens of combinations but for some reason hibernate can't manage to convert JPQL into native SQL. It still throws either unexpected token or unexpected end of subtree depends of approach I try. For comparison, when I'm using native query via the same entity manager it works properly.

Christian Beikov Over a year ago

ANY is a special quantifier in HQL, so you can't use it like this. You would have to pack the ANY into the function that you register.

Collectives™ on Stack Overflow

unexpected token: '<function_name>' error when using hibernate, JPQL and postgres function

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related