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
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.
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.
Not a Hibernate bug.
Assignee
Reporter
Fix versions
Labels
backPortable
Suitable for new contributors
Requires Release Note
Pull Request
backportDecision
Components
Affects versions
Priority
