Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

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

select * from Event where (:name is null or name = :name)

Then set the parameter to null

query.setParameter( "name", null )

It will then thrown :

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

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

Activity

Show:

Andreas Loew March 2, 2022 at 10:30 AM

… and please be warned when applying the workaround kindly promoted by Christian Beikov, that with Oracle JDBC, in fact, this workaround for e.g. a TIMESTAMP column can become as complicated as

ap.ende <= COALESCE(cast(cast(:zeitpunktEnde as string) as timestamp)

(i.e. a double cast for everything which is not string: first to string, then to target type) - which in the end works fine nevertheless… slightly smiling face

Christian Beikov March 2, 2022 at 8:48 AM

The fix for this can not be backported as it requires a lot of type infrastructure which is only available in 6.0. A possible workaround is to use this instead:

where name = coalesce(cast(:name as string), name)

Paul Polishchuk January 6, 2022 at 12:18 PM

Would it be possible to backport the fix to 5.6.x ?
That would be super awsome!

Christian Beikov January 6, 2022 at 12:00 PM

This was fixed, maybe already in Beta2 but I'm sure it is fixed in Beta3

Paul Polishchuk January 5, 2022 at 5:50 PM

I can reproduce the issue even in non native query now disappointed face

Hibernate 5.6.3.Final
PostgreSQL jdbc 42.3.1

Fixed

Details

Assignee

Reporter

Components

Fix versions

Priority

Created December 12, 2018 at 11:12 AM
Updated March 2, 2022 at 10:30 AM
Resolved January 6, 2022 at 12:00 PM

Flag notifications