PostgreSql: operator does not exist: timestamp without time zone >= bytea

[Tsyklop]

Use Spring Boot 2.6.6, Spring data JPA, Hibernate 5.6.7.Final, PostgreSql Driver 42.3.3, PostgreSql Server 14.

I have query:
SELECT u.* FROM "user" u WHERE ((:createdAtFrom = NULL OR :createdAtTo = NULL) OR (u.birthday BETWEEN :createdAtFrom AND :createdAtTo)) Native.

But it not working.

I got error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= bytea
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I turned on hibernate debug for sql parameters and see next rows:

o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARBINARY] - [null]
o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARBINARY] - [null]
o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARBINARY] - [null]
o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARBINARY] - [null]

Why VARBINARY?
I tried java.util.Date, java.time.LocalDateTime - same error. what wrong?

There is demo repo: https://gitlab.com/Tsyklop/jpa-test/-/tree/master
Stackoverflow: https://stackoverflow.com/questions/71902768/spring-boot-2-postgresql-operator-does-not-exist-timestamp-without-time-zone

[Tsyklop]

I tried one thing. And if pass null there I got error.

Is there any workarounds? I want search all rows without timestamp filter or with timestamp filter.

[gregturn]

What happens if you use IS NULL instead of = NULL?

If this is native SQL via @Query(value="/* SQL */", nativeQuery=true), then null doesn't equal null and requires a specific IS NULL check to boolean short circuit out of that WHERE clause.

[Tsyklop]

What happens if you use IS NULL instead of = NULL?

If this is native SQL via @Query(value="/* SQL */", nativeQuery=true), then null doesn't equal null and requires a specific IS NULL check to boolean short circuit out of that WHERE clause.

I tried IS NULL and = NULL - same error.
You can change query in project and check this.

Error gone when I pass objects of Instant class instead of null.

[schauder]

@gregturn is correct about = NULL vs IS NULL but that is a different problem unrelated to the exception.

Your statement seems to be missing a CAST so that Postgresql knows that the bind parameters are of the type of the columns they get compared to. So something like

SELECT u.* FROM "user" u WHERE ((:createdAtFrom IS NULL OR :createdAtTo IS NULL) OR (u.birthday BETWEEN CAST (:createdAtFrom TO TIMESTAMP) AND CAST (:createdAtTo TO TIMESTAMP))) should work.

Could you confirm, that this works?

[Tsyklop]

I have next query:

SELECT * FROM "user" 
WHERE ((:createdAtFrom IS NULL OR :createdAtTo IS NULL) 
    OR ("created_at" BETWEEN CAST(:createdAtFrom AS TIMESTAMP) AND CAST(:createdAtTo AS TIMESTAMP)))

And it transforms to this query:

/* dynamic native SQL query */     SELECT
        *     
    FROM
        "user"     
    WHERE
        (
            (
                ? IS NULL 
                OR ? IS NULL
            ) 
            OR (
                "created_at" BETWEEN CAST(? AS TIMESTAMP) AND CAST(? AS TIMESTAMP)
            )
        )  
    order by
        NULL.id desc limit ?

And I got an error, because in order by incorrect entry: order by NULL.id desc limit ?. Why?

I Used Pageable as a parameter for Repository.
https://gitlab.com/Tsyklop/jpa-test/-/blob/master/src/main/java/com/example/jpatest/persistance/UserRepository.java#L46

[Tsyklop]

I created query without Pageable:
https://gitlab.com/Tsyklop/jpa-test/-/blob/master/src/main/java/com/example/jpatest/persistance/UserRepository.java#L30

I passed null to createAt parameters and got error: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to timestamp without time zone.

Query Log:

2022-04-19 12:03:12.020 DEBUG 18144 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    /* dynamic native SQL query */     SELECT
        *     
    FROM
        "user"     
    WHERE
        (
            (
                ? IS NULL 
                OR ? IS NULL
            ) 
            OR (
                "created_at" BETWEEN CAST(? AS TIMESTAMP) AND CAST(? AS TIMESTAMP)
            )
        ) 
2022-04-19 12:03:12.023 TRACE 18144 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARBINARY] - [null]
2022-04-19 12:03:12.024 TRACE 18144 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARBINARY] - [null]
2022-04-19 12:03:12.024 TRACE 18144 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARBINARY] - [null]
2022-04-19 12:03:12.024 TRACE 18144 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARBINARY] - [null]