Type error with null parameters under PostgreSQL

Description

Using Query.setParamater("param", null) on some datatypes (not all, or even most) gives the following error from the database.

column "the_array" is of type bigint[] but expression is of type bytea

I have found a solution that works for me, but it needs to be tested for more database types, not just PostgreSQL, and I don't have the set-up for that. I will submit it once I have an issue number.
It is a workaround to what might get fixed in the JDBC driver or even the database itself, but those are typically updated less often in a project.

Environment

PostgreSQL 9.3.13-0ubuntu0.14.04
Driver: org.postgresqlostgresql:9.4-1202-jdbc41

Activity

Show:
Gail Badner
July 28, 2016, 9:27 PM

I tried the test case in your pull request, but it works fine using Postgresql 9.4. Please create a pull request with a test that reproduces your issue.

Йордан Гигов
July 28, 2016, 10:21 PM
Edited

I traced the problem all the way to the database, really. It seems like the only way to avoid it through JPA, other than writing manual insert/update queries, is to perform a full analysis on the query and get the column from a type in the metamodel. I talked to the Postgre people and they seem to be fine with having 50 NULL types, instead of just having it be a special value, as it's supposed to be. They type-check it against the type that the JDBC driver tells them before they check if it's a NULL.

More specifically, the problem is in https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/parser/parse_coerce.c;hb=refs/heads/master at the start of coerce_to_target_type

Short version: the problem is not in Hibernate. You should close the issue and maybe just document it somewhere that under Postgres, native queries cannot reliably accept null values.

Gail Badner
August 15, 2016, 10:24 PM

Not a Hibernate bug.

Assignee

Unassigned

Reporter

Йордан Гигов

Fix versions

None

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure