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]

[gregturn]

What happens if you remove some of those redundant parentheses? I don't think you need them around ? is null OR ? is null.

a or b or c should work just fine. You only need them surrounding the and portion to associate things properly, e.g. a OR b OR (c AND d)

[Tsyklop]

Query:

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

When :createdAtFrom or :createdAtTo is null from java code I got same error: ERROR: cannot cast type bytea to timestamp without time zone

[jmax01]

I am having the same issue.

[gregturn]

@Tsyklop Frankly, I feel as if you should check in advance whether either :createdAtFrom or :createdAtTo is null, and if so, choose an alternative query. Only if BOTH fields are populated, should you be trying this query.

[Tsyklop]

@gregturn Already did this.

[heowc]

I don't think this is a spring-data-jpa issue, but a compatibility issue between postgresql and hibernate.

Hibernate is slow to respond to bugs for specific drivers, so I can't guarantee that this will solve it. https://hibernate.atlassian.net/browse/HHH-14778

So I recently added an improvement to solve this in spring-data-jpa. See https://github.com/spring-projects/spring-data-jpa/issues/2370

As far as I know this was released after 2.7.x, so please try the later version.

[heowc]

Another story, but we've heard reports of side effects from this, so we'll need to check this out. 🤔

See https://github.com/spring-projects/spring-data-jpa/pull/2461#issuecomment-1126875784

[akakyi]

After a 6 hours of useless brain suffering i manage to do this (just example):

@Query(
        value = "select " +
                "   t.* " +
                "from test t " +
                "where " +
                "   cast(cast(:endDate as text) as timestamp) is null or t.created_at <= cast(cast(:endDate as text) as timestamp)",
        nativeQuery = true
    )
    fun test(@Param("endDate") endDate: LocalDateTime?): List<Test>

[gregturn]

Well, I've discovered that Hibernate is prone to not route null values through attribute converters, so that may the issue you're also running into.

If this is also suffering from a PostgreSQL/Hibernate incompatibility, then there may be little here for Spring Data JPA to do.

[matthewlowry]

Hitting same issue with Spring Boot 2.6.6, Hibernate 5.6.7, JDBC driver 42.2.25, and Postgres 13.1. Work-around from @akakyi didn't work for me.

Suspect @gregturn is right this isn't a Spring Data JPA issue per se it's more a Hibernate + Postgres issue that a bunch of us happen to be stumbling into via Spring Data JPA and native @Query methods. Seems like it's Hibernate doing the wacky things to the null values.

For now I'll be working around this by forbidding null values for the parameters and forcing callers to use sentinel values (timestamps far in the past or far in the future) to express "no filtering".

[slifer225]

After a 6 hours of useless brain suffering i manage to do this (just example):

@Query(
        value = "select " +
                "   t.* " +
                "from test t " +
                "where " +
                "   cast(cast(:endDate as text) as timestamp) is null or t.created_at <= cast(cast(:endDate as text) as timestamp)",
        nativeQuery = true
    )
    fun test(@Param("endDate") endDate: LocalDateTime?): List<Test>

This works for me. Thanks life savior

[vitr1988]

Maybe this information https://blog.mimacom.com/java-8-dates-with-postgresql/ would be useful for solving your problem. As for me supposed solution works well.

[negromonte]

i am having the same issue.

SQL Error [42883]: ERROR: operator does not exist: timestamp without time zone >= integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 215 script2.txt

[negromonte]

select oc.id_ocorrencia,cli.nome as nomeCliente, li.nome_solicitante as nomeSolicitante , li.telefone_solicitante from Ocorrencia oc, Ligacao li , Cliente cli where status like 'Fechada' AND oc.data_hora_fim between :dtinicio and :dtfim AND li.id_ligacao =oc.id_ligacao and oc.id_usuario in(select id_usuario from Usuario) and (cli.cliente_id =li.id_cliente_unigas )and oc.id_categoria in(select id_categoria from categoria where posvenda=true) union select id_ocorrencia, cli.nome as nomeCliente, li.nome_solicitante as nomeSolicitante , li.telefone_solicitante from Ocorrencia oc, Ligacao li , Cliente cli where status like 'Fechada' AND oc.data_hora_fim between :dtinicio and :dtfim AND li.id_ligacao =oc.id_ligacao and oc.id_usuario in(select id_usuario from Usuario) and cli.cliente_id =li.id_cliente and oc.id_categoria in(select id_categoria from categoria where posvenda=true) and cli.cliente_id !=1

[gregturn]

Since we've seen multiple people implement a mechanism to work between Postgres and Hibernate, this is not really a Spring Data JPA issue.

Spring Data JPA is somewhat limited in what it do to help when it comes to native queries. And so I recommend crafting a custom implementation. Check out https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations for more details on how to hook such a solution into your repository (if you haven't already figured that out).

[agorodetskaya]

After a 6 hours of useless brain suffering i manage to do this (just example):

@Query(
        value = "select " +
                "   t.* " +
                "from test t " +
                "where " +
                "   cast(cast(:endDate as text) as timestamp) is null or t.created_at <= cast(cast(:endDate as text) as timestamp)",
        nativeQuery = true
    )
    fun test(@Param("endDate") endDate: LocalDateTime?): List<Test>

can't believe it works! thanks