CODEWITHSUNDEEP
javapostgresqlhibernatespring-data
Libin
Libin

Reputation: 165

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

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 )
 );

Upvotes: 1

Views: 5891

Answers (3)

Farruh Turgunov
Farruh Turgunov

Reputation: 11

It's an old question, but there is a more useful way:

your query... .setParameter("transaction_id", null, LongType.INSTANCE)

It works. Found from https://forum.hibernate.org/viewtopic.php?p=2493645

Upvotes: 1

greenbean57
greenbean57

Reputation: 1

Going to newer versions of hibernate 5.1.17 and above + postgres seems to have exhibited this behavior. Looking into the code, when it binds a type that has no value, the old hibernate code attempted to resolve the type through a typeresolver. The newer versions of hibernate's implementation state that it will not guess.

  public Type resolveParameterBindType(Object bindValue) {
        if ( bindValue == null ) {
            // we can't guess
            return null;
        }

We ended up just setting a default value based on the type first, and then the real null value.

Upvotes: 0

coladict
coladict

Reputation: 5095

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.

Upvotes: 3

Related Questions