Search code examples
postgresqlhibernatejpalowercasenamed-parameters

org.postgresql.util.PSQLException: ERROR: function lower(bytea) does not exist

I'm in a migration process from javax to jakarta. Our project is build with Spring Boot (thus upgrade from 2.7 to 3.2) and Hibernate (5.6 to 6.3) and we use a PostgreSQL database (V14). One pitfall was the migration of a custom PostgreSQL dialect, but i suppose i solved satisfactory (-> implementation of a full text search condition)

The main issue arises when we perform a query:

      SELECT o FROM Order o
       WHERE (o.createdAt between :from and :to)
       AND (:query is null OR function('pgTextSearch', :query, o) = true)
       AND (:createdBy is null OR LOWER(o.createdBy) = LOWER(:createdBy))

It says:

Caused by: org.postgresql.util.PSQLException: ERROR: function lower(bytea) does not exist
Hinweis: No function matches the given name and argument types. You might need to add explicit type casts.

So far as i know, this comes from an invocation of the lower() function with 'null' as parameter. :createdBy is declared as string (resp. varchar). In my scenarion :createdBy is null, but this should be intercepted by the first OR condition. What am i missing?

Additionally, i executed the query with replaced parameters through PostgreSQL-CLI without issues.

P.S. The query was modified for migration, but only within the custom function part.
P.P.S. I read the migration guides for spring boot and hibernate (SQM yikes!), but could not find any overlap to my use case.
P.P.P.S. I'm not setting the parameter by hand (e.g. .setParameter("createdBy", xyz)), the repository is a JpaRepository, thus parameter replacement is done by Spring Data

Solution

  • Short story

    You are hitting, more than 10 years old bug:

    Long story

    I. Hibernate, since v6, does have an API which allows to overcome such issues, for example:

        /**
     * Bind the given argument to a named query parameter using the given
     * Class reference to attempt to determine the {@link BindableType}
     * to use.  If unable to determine an appropriate {@link BindableType},
     * {@link #setParameter(String, Object)} is used.
     *
     * @see #setParameter(String, Object, BindableType)
     */
    <P> Query<R> setParameter(String parameter, P argument, Class<P> type);

    unfortunately, you are using spring-data-jpa, and Spring team have not yet adopted new features of Hibernate v6 - here I would suggest to file a corresponding bug/cr.

    II. It seems the main problem for particular query is following:

    :createdBy parameter occurs two times in :createdBy is null OR LOWER(o.createdBy) = LOWER(:createdBy) clause, and there is definitely no option to infer correct sql type from :createdBy is null expression, however, technically, it should be possible to infer varchar sql type from LOWER(o.createdBy) = LOWER(:createdBy) expression, the problem is Hibernate is trying to infer sql type of query parameter only once, so you are getting weird bytea instead of varchar (here I have no idea why do not set default to something more common instead of bytea).

    Could you try to flip :createdBy is null and LOWER(o.createdBy) = LOWER(:createdBy) expressions? I suspect it may fix the issue.

    III. idiomatic way to write such "universal" queries is to use SpEL or JPA specifications, please check Hibernate randomly throws error: Cannot coerce value [] [java.util.ArrayList] as Long