Simple Native SQL with NULL Parameter fails with Postgresql

Description

Encounter the same problem in 5.3.7 which are described at :

Refer to the test-case at https://github.com/hibernate/hibernate-orm/commit/c354b5e8b9e157d46c08d3070de5d654e4e6cf11. Changing to use the native SQL

Then set the parameter to null

It will then thrown :

Change to use Hibernate specific API does not have such problem.

Environment

PostgreSQL 10.1
Hibernate 5.3.7

Activity

Show:
Dragoş Haiduc
April 5, 2019, 2:13 PM
Edited

This is my JpaRepository method that is still failing with hibernate-core 5.3.9 Postgre JDBC driver 42.2.5:

The generated SQL query is

It seems there is a problem with mapping of the java Enum (4th parameter)

The code JpaRepository works just fine with hibernate-core 5.2.18.

Gail Badner
April 9, 2019, 1:17 AM

You may be able to workaround this issue by using Postgresql encode function to cast the first usage of :name from bytea to varchar. I'm not familiar enough with this function to know for sure.

I see that when HQL/JPQL is used, Hibernate recognizes that :name should be treated as a StringType for both instances where that parameter is used.

When a native query is used though, Hibernate determines that the first time :name is used, it should be treated as a SerializableType, and the second time :name should be treated as StringType.

An improvement would be for Hibernate to determine that both usages of the same parameter should be treated as the same type, in this case StringType.

Йордан Гигов
April 16, 2019, 3:03 PM

I have dug deep into this before, and a real solution lies only in PostgreSQL, not in Hibernate.
There is however a workaround that should be made more commonly known.

For whatever your query is:

You can first call setParameter("param",SOME_CONST) with a non-null value of the same class to invoke the type identification, then with your real potentially-null parameter.
Setting the parameter a second time without having called the query is not an error.

Dragoş Haiduc
September 6, 2019, 6:56 AM
Edited

I discovered something that works for me, see below, I changed the JPQL query to use named parameters instead of ?1, ?2, ?3, ?4, hope this helps someone else:

This works fine with the version Spring Data JPA 2.1.7.RELEASE (it is a Spring Boot 2.1.7.RELEASE app), same Postgre version (9.6.12).

Christian Beikov
September 10, 2019, 9:47 AM

I suppose the problem is that on JDBC level, the parameter values are passed with the `setObject(int index, Object o)` method. The PostgreSQL JDBC driver can't handle NULL values that way though so this is the problem you are having. When passing a NULL value for a parameter, this fails. One way around that is to cast the parameter to the desired type i.e. use ... where col = cast(aram as varchar)

Assignee

Unassigned

Reporter

Ken Chan

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure