LocalDate, PostgreSQL, and @SQLInsert: bug?

Hibernate ORM
1

Hibernate: 5.3.10.Final
PostgreSQL JDBC Driver: 42.2.6
PostgreSQL: 10.x

For this entity:

@Entity
public class Post {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;

    private String body;

    private LocalDate publishedDate;
}

Tracing execution of an insert, I see that in org.hibernate.persister.entity.AbstractEntityPersister#doLateInit
sqlIdentityInsertString is set to insert into post (title, body, published_date) values (?, ?, ?).

I wanted to add an ON CONFLICT DO NOTHING clause to the inserts so I modified the entity and annotated it with @SQLInsert:

@Entity
@SQLInsert(sql = "INSERT INTO post(title, body, published_date) VALUES (?, ?, ?) ON CONFLICT DO NOTHING")
public class Post {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;

    private String body;

    private LocalDate date;
}

I was expecting Hibernate to work as before and convert the date field properly, but this is causing an SQL grammar exception:

Caused by: org.postgresql.util.PSQLException: ERROR: column "published_date" is of type date but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 85
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)

Full trace:

Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
	at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
	at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3106)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3696)
	at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
	at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:645)
	at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
	at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
	at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
	at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:332)
	at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:289)
	at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:196)
	at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:127)
	at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:192)
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:135)
	at org.hibernate.internal.SessionImpl.firePersistOnFlush(SessionImpl.java:864)
	at org.hibernate.internal.SessionImpl.persistOnFlush(SessionImpl.java:857)
	at org.hibernate.engine.spi.CascadingActions$8.cascade(CascadingActions.java:341)
	at org.hibernate.engine.internal.Cascade.cascadeToOne(Cascade.java:490)
	at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:415)
	at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:216)
	at org.hibernate.engine.internal.Cascade.cascadeCollectionElements(Cascade.java:523)
	at org.hibernate.engine.internal.Cascade.cascadeCollection(Cascade.java:455)
	at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:418)
	at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:216)
	at org.hibernate.engine.internal.Cascade.cascade(Cascade.java:149)
	at org.hibernate.event.internal.AbstractFlushingEventListener.cascadeOnFlush(AbstractFlushingEventListener.java:159)
	at org.hibernate.event.internal.AbstractFlushingEventListener.prepareEntityFlushes(AbstractFlushingEventListener.java:150)
	at org.hibernate.event.internal.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:83)
	at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:44)
	at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1444)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1530)
	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1538)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1506)
	at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1554)
	at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getSingleResult(CriteriaQueryTypeQueryAdapter.java:109)

Caused by: org.postgresql.util.PSQLException: ERROR: column "published_date" is of type date but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 85
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)

Is this a bug? Or am I missing something here?

2

It turns out the issue is with the order of columns in the insert statement, and it is not due to LocalDate.

According to the docs:

The parameter order is important and is defined by the order Hibernate handles properties. You can see the expected order by enabling debug logging, so Hibernate can print out the static SQL that is used to create, update, delete entities.

To see the expected sequence, remember to not include your custom SQL through annotations or mapping files as that will override the Hibernate generated static SQL.

So this works:

@SQLInsert(sql = "INSERT INTO custom_post(body, published_at, title) VALUES (?, ?, ?) ON CONFLICT DO NOTHING")

but this doesn’t work:

@SQLInsert(sql = "INSERT INTO custom_post(title, body, published_at) VALUES (?, ?, ?) ON CONFLICT DO NOTHING")

Does Hibernate support named place holders? For example:

@SQLInsert(sql = "INSERT INTO custom_post(title, body, published_at) VALUES (:title, :body, :publishedAt) ON CONFLICT DO NOTHING")

?