Hibernate native query with null Date parameter

Question

I'm struggling on a seemingly simple hibernate use-case and cannot figure out what's going on :

This is my entity :

@Entity @Data @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"}) public class Event { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; @Column(columnDefinition = "timestamp without time zone", nullable = false) @Temporal(TemporalType.TIMESTAMP) private Date date; @Column(columnDefinition = "text") private String status; public Event() {} }

And this is my native query, called with 'date' being potentially NULL :

@Query(value = "SELECT e FROM Event e WHERE :date is null or DATE(e.date) = :date") Page<Event> findEvents(Pageable pageable, @Param("date") Date date);

The date parameter passed to the function is already truncated as a date (i.e. no hours, minutes, etc), but the database entry isn't, that's why I use the DATE() sql function in the left part of the comparison.

Where run with a NULL date, the query crashes with this error :

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: date = bytea Indice : No operator matches the given name and argument type(s). You might need to add explicit type casts.

From what I understand, SQL standard doesn't short-circuit condition evaluations, so the second part is always evaluated. On the other hand, Hibernate is unable to infer the 'date' type because it's null, so it's injected in the request as binary, hence the error.

I tried this variant, with the same results :

@Query(value = "SELECT e FROM Event e WHERE :date is null or DATE(e.date) = COALESCE(:date, '2000-01-01')") Page<Event> findEvents(Pageable pageable, @Param("date") Date date);

EDIT : I also tried this one :

@Query(value = "SELECT e FROM Event e WHERE :date is null or DATE(e.date) = CAST(:date AS date)")

With another error that reflects the same issue :

Caused by: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to date

EDIT2 :

To make sure that the second condition part is never evaluated if the parameter is NULL, I tried this approach, using the CASE WHEN .. THEN syntax :

@Query(value = "SELECT e FROM Event e WHERE (case when :date is null then true else (DATE(e.date) = cast(:date AS date)) end)"

But Hibernate doesn't like it, and I don't know why ...

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: case near line 1, column 30 [SELECT e FROM Event e WHERE (case when :date is null then true else (DATE(e.date) = cast(:date AS date)) end)]

Have you tried: Page<Event> findEvents(Pageable pageable, @Param("date") @Temporal java.util.Date date);? It could be, that this triggers Spring to set the parameter as "Date null". — Tobias Liefke
– Tobias Liefke, Commented Dec 17, 2019 at 15:52
Yes ! This is it ! This works : SELECT e FROM Event e WHERE (DATE(e.date) = cast(:date AS date) or cast(:date AS date) is null). Alongside the @Temporal annotation on the date parameter. Can you post this as an answer so I can reward you the bounty ? Thanks — Neumann
– Neumann, Commented Dec 17, 2019 at 16:23

Tobias Liefke · Accepted Answer · 2019-12-17 18:37:02Z

If you add @Temporal to your Date parameter, Spring Data knows how to present that parameter to Hibernate, even if it is null:

Page<Event> findEvents(Pageable pageable, @Param("date") @Temporal Date date);

(The parameter must be of type java.util.Date, not java.sql.Date).

Another note to "Why is SQL / Hibernate not able to short-circuit": It is a key feature of most databases to reorder the predicates for performance reasons - and to do so the parameters must be bound first. Most databases will indeed short-circuit the predicates, but not necessarily in the order you write them into the query.

This works for query annotation as well, but the date values of both the Parameter and the entity attribute must be casted to date.
I'd guess that is a different question, because according to its documentaion @Temporal is only defined for java.util.Date and java.util.Calendar.

Benyovszki Balázs · Accepted Answer · 2023-11-28 09:32:08Z

You can use casting to solve this problem with dates. Based on this https://github.com/spring-projects/spring-data-jpa/issues/2491. The workaround provided worked for me with the hibernate version of 5.3.28.Final. Casting both the sort circuit side and the actual condition is working correctly.

AND (cast(cast(:searchDateStart as text) as timestamp) is null or date_column >= cast(cast(:searchDateStart as text) as timestamp)) AND (cast(cast(:searchDateEnd as text) as timestamp) is null or date_column <= cast(cast(:searchDateEnd as text) as timestamp))

Malay Shah · Accepted Answer · 2019-12-12 10:34:32Z

-3

You have used the date param instead of the actual table field in the first condition of your where clause: This should work:

@Query(value = "SELECT e FROM Event e WHERE e.date is null or DATE(e.date) = :date") Page<Event> findEvents(Pageable pageable, @Param("date") Date date);

answered Dec 12, 2019 at 10:34

Malay Shah

4624 silver badges10 bronze badges

2 Comments

Neumann Over a year ago

Your request is semantically different. The date field in the database cannot be NULL, but the repository function parameter 'date' can be. That's why I'm testing the nullity on the latter. This request crashes in the sam way as the other ones.

Malay Shah Over a year ago

My bad.. I think you should look for creating Dynamic queries. Can use this for reference: stackoverflow.com/questions/58398910/…

Collectives™ on Stack Overflow

Hibernate native query with null Date parameter

3 Answers 3

3 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Linked

Related