Native named query fails with exception “column is of type date but expression is of type bytea”when NULL LocalDate is given as input

Question

Query:

    INSERT INTO PERSON 
        (email, mobile, party_id, affiliate_id, eligibility, member_start_date, created_by, created_dt, first_name, last_name, google_connected) 
        values 
        ('[email protected]', NULL, 123, '123', '1', NULL, NULL, '2018-8-30 21:45:56.859000 -6:0:0', 'xxx', 'yyy', '0')
        ON CONFLICT (email) 
        DO UPDATE SET create_dt = '2018-8-30 21:45:56.859000 -6:0:0' where email = ?

When the LocalDate value is not null, it works fine. Facing this issue only when LocalDate value is given as null.

Even after PostgreSQL casting, it does the same.

Exception stacktrace:

2018-08-30 21:10:48,372 -- [ERROR]-- There was an unexpected problem with your request org.postgresql.util.PSQLException: ERROR: column "member_start_date" is of type date but expression is of type bytea
Hint: You will need to rewrite or cast the expression. Position: 185 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:645) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:495) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:380) at sun.reflect.GeneratedMethodAccessor98.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy185.executeQuery(Unknown Source) at at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ... 149 common frames omitted

Entity:

@Entity(name = "person")
@EqualsAndHashCode(callSuper = false)
public class PersonEntity extends Audit {

@Id
@GeneratedValue
@Column(name = "person_id", columnDefinition = "uuid", updatable = false)
private UUID id;

@Column(name = "first_name")
private String firstName;

@Column(name = "last_name")
private String lastName;

@Column(name = "email")
@NotNull
private String email;

@Column(name = "mobile")
private String mobile;

@Column(name = "party_id")
private Long partyId;

@Column(name = "affiliate_id")
private String affiliateId;

@Column(name = "eligibility")
@NotNull
private Boolean eligibility;

@Column(name = "member_start_date")
private LocalDate memberStartDate;

@Column(name = "google_connected")
private Boolean googleAccountConnected;
}

PostgreSQL table definition; it's missing google_connected column which is not important:

CREATE TABLE person
( 
   person_id             UUID NOT NULL,
   email                 VARCHAR(128) NOT NULL,
   mobile                VARCHAR(20), 
   party_id              INTEGER,
   affiliate_id          VARCHAR(20),
   eligibility              BOOLEAN NOT NULL,
   member_start_date     DATE, 
   created_by            VARCHAR(128) NOT NULL,
   created_dt            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by            VARCHAR(128) DEFAULT NULL,
   updated_dt            TIMESTAMP NULL,
   CONSTRAINT person_pk PRIMARY KEY ( person_id )
 );

It might help to also show us the table definition in Postgres. — Tim Biegeleisen

coladict coladict · Accepted Answer · 2018-08-31T11:30:51

Because the query is native, Hibernate doesn't know the data types which to expect, so when you pass a null it defaults to the generic Serializable type handler. Changing this behaviour breaks compatibility with other databases.

Postgres, however parses the query immediately and determines what types are acceptable, and it always checks for type before it checks for null. They are the only ones who can fix this, but refuse to do so and say it works as intended.

The only solutions for you are:

use JPQL
use managed entities
use hard-coded nulls in the query string where you need to

Fortunately for the third option, with Hibernate you can use named parameters in native queries, so you don't have to do positional calculations for when something is available and when it isn't.

edit: 4th solution that I've discovered since.

You have your query:

Query q = em.createNativeQuery("UPDATE...");

Have some static final LocalDate object somewhere:

public static final LocalDate EPOCH_DATE = LocalDate.of(1970, 1, 1);

then call the query like this:

q.setParameter("start_date", EPOCH_DATE);
q.setParameter("start_date", nullableDateParam);

The first time you call setParameter for a parameter, Hibernate uses the class to resolve the type. The second time you call it, the type is already resolved, so a null will work.

Native named query fails with exception “column is of type date but expression is of type bytea”when NULL LocalDate is given as input

2 Answers