Spring Data Rest: "Date is null" query throws an postgres exception

Question

I use Spring Boot and Data Rest to create a simple microservice in Java8 and get a postgres exception.

My entity:

@Entity
public class ArchivedInvoice implements Serializable {
    ...
    @Column
    private String invoiceNumber;
    @Column
    private java.sql.Date invoiceDate;
    ...
}

My repository interface:

@RepositoryRestResource(collectionResourceRel = "archivedinvoices", path = "archivedinvoices")
public interface ArchivedInvoiceRepository extends PagingAndSortingRepository < ArchivedInvoice, Long > {
    ...
@RestResource(rel = "findByXYZ", path = "findByXYZ")
@Query(value = "SELECT ai FROM #{#entityName} ai WHERE "
        + "(:invoiceNumber IS NULL OR ai.invoiceNumber LIKE :invoiceNumber) AND "
        + "(:invoiceDate IS NULL OR ai.invoiceDate = :invoiceDate)" 
        )
public Page < ArchivedInvoice > findByXYZ(
        @Param("invoiceNumber") @Nullable String invoiceNumber,
        @Param("invoiceDate") @Nullable Date invoiceDate,
        Pageable p);
    ...
}

If I call ".../findByXYZ?invoiceDate=2016-02-22", I'll get the following error message:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...
Caused by: org.postgresql.util.PSQLException: FEHLER: could not determine data type of parameter

But it works, when I remove the ":invoiceDate IS NULL" part. How can I check, if the invoiceDate parameter is null?

You could try this one http://stackoverflow.com/a/27193644/2055854 — Orest, Feb 23 '16 at 08:40

score 13 · Answer 1 · answered Jan 16 '19 at 18:58

I've spend some time looking at this because I really want to compare "date is null" in querys and that's the result:

When you use the "cast(foo.date as date) is null", it works OK if the field is not null. If the field is null this exception is throwed:

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

The solution is use coalesce:

coalesce(:date, null) is null

It works fine having or not data in the field tested.

My query example:

@Query("SELECT c "
        + " FROM Entity c "
        + " WHERE "
        + "       and ( (coalesce(:dateFrom, null) is null and coalesce(:dateUntil, null) is null) "
        + "             or ((coalesce(c.date, null) is not null) "
        + "                 and ( "
        + "                        ((coalesce(:dateFrom, null) is null and coalesce(:dateUntil, null) is not null) and c.date <= :dateUntil) "
        + "                     or ((coalesce(:dateUntil, null) is null and coalesce(:dateFrom, null) is not null) and c.date >= :dateFrom)"
        + "                     or (c.date between :dateFrom and :dateUntil)"
        + "                 )"
        + "             )"
        + "       ) "

Hope it works for you!

I like the *coalesce* solution; it's somewhat shorter than casting. — Campa, Jun 11 '19 at 14:50
@Tales Kerschner and I'm assuming you're using java.sql.Dates for your dateFrom and dateUntil in your java code? The same cannot be done for java.util.Date right? — ennth, Nov 12 '21 at 02:27

chaserb · Answer 2 · 2016-08-31T17:02:08.947

8

I believe @Bonifacio is correct in that Postgres is not able to determine the type of your @Param("invoiceDate") parameter when performing the IS NULL test. I have queries similar to yours that behave as expected with an in memory H2 database, but fail with the Postgres integration tests.

I was able to get around this by casting the parameter to a date like so:

@Query(value = "SELECT ai FROM #{#entityName} ai WHERE "
    + "(:invoiceNumber IS NULL OR ai.invoiceNumber LIKE :invoiceNumber) AND "
    + "(cast(:invoiceDate as date) IS NULL OR ai.invoiceDate = :invoiceDate)" 
    )

edited Aug 31 '16 at 17:02

answered Aug 31 '16 at 16:57

chaserb

1,340
1
14
25

1

For others interested, the cast type for UUID is "binary" and not "other" or "varchar" – user1791139 Aug 28 '17 at 14:29
1

Here is an explanation of this solution : https://blog.mimacom.com/java-8-dates-with-postgresql/ – Yernar Arystanov Apr 26 '20 at 09:24

score 2 · Answer 3 · answered May 21 '18 at 19:03

2

I have had similar problems with the field of type LocalDateTime and field Timestamp in postgres 9.5. I solved it converting it like this:

    @Query("SELECT c "
        + "FROM Contract c "
        + "WHERE "
        + "(:idContract IS NULL OR c.idContract = :idContract) AND "
        + "(CAST(:dtCancelInitial AS java.time.LocalDateTime) IS NULL OR (c.dtCancel >= :dtCancelInitial AND c.dtCancel < :dtCancelFinal)) "
        + "ORDER BY c.idContract")
List<Contract> findContratConsultaCancelamento(@Param("idContract") Long idContract, @Param("dtCancelInitial") LocalDateTime dtCancelInitial, @Param("dtCancelFinal") LocalDateTime dtCancelFinal);

answered May 21 '18 at 19:03

Gean Carlos Pires

21
2

This does not seem to answer the question. The question doesn't mention a table name of `Contract`. – Collin Barrett May 21 '18 at 19:25
It may not have answered the question but it helped me greatly by showing you can also cast to Java types, while I was unable to cast uuids before (even with the 'binary' as mentioned in a comment on another answer) – Sebastiaan van den Broek Nov 14 '18 at 05:10

score 1 · Answer 4 · answered Feb 23 '16 at 11:18

1

When you use JPA with Postgres there is a curious behavior which will cause error when you want to check if a parameter is null before checking the desired condition later.

The error occurs because when reading the query, JPA can't identify what is the parameter type, and then raising an error while interpreting the query.

The workaround to it is switch the positions of your parameter conditions, to first check if the condition desired is true, and then check if the parameter is null or not.

This way, JPA will be able to identify the correct parameter type , and the error should not be raised again.

Try the following query and see if it will work:

@Query(value = "SELECT ai FROM #{#entityName} ai WHERE "
    + "(:invoiceNumber IS NULL OR ai.invoiceNumber LIKE :invoiceNumber) AND "
    + "(ai.invoiceDate = :invoiceDate OR :invoiceDate IS NULL)" 
    )

answered Feb 23 '16 at 11:18

Bonifacio

1,482
10
19

2

Unfortunately, this does not work. But it was a great idea! Thx! – user2722077 Feb 23 '16 at 12:25
1

This worked for me for the first date parameter in the query and not for the send date parameter. I have two date parameters in the query. It complains of the same for the 2nd one. – Sridevi Yedidha Oct 15 '17 at 21:20
1

this works , as it was implemented in my DB this way . I believe there MIGHT be an annoying "typecast" error in your postgres logs though. but then again, I think maybe it was because I was using the java.sql.Date datatype – ennth Nov 12 '21 at 17:59

score 0 · Answer 5 · answered Aug 09 '22 at 14:57

Below worked for me

@Query(value = "SELECT ai FROM #{#entityName} ai WHERE "
        + "(:invoiceNumber IS NULL OR ai.invoiceNumber LIKE :invoiceNumber) AND "
        + "(cast(:invoiceDate as date) IS NULL OR ai.invoiceDate = :invoiceDate)" 
        )

Spring Data Rest: "Date is null" query throws an postgres exception

5 Answers5

Linked