# After upgrading hibernate from v5 to v6, query with LocalDateTime parameter throws an error

**URL:** <https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652>\
**Category:** Hibernate ORM\
**Created:** [May 30, 2024, 11:15am UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652 "2024-05-30T11:15:47Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [May 30, 2024, 11:15am UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/1 "2024-05-30T11:15:47Z")

</div>

Hi,

I have a query  
…`(COALESCE(:fromTime, '') = '' OR al.createdAt >= :fromTime)`…  
where both entity field `createdAt` and query parameter `fromTime` are `java.time.LocalDateTime`.

This used to work with Hibernate 5.6.15.Final but with Hibernate 6.5.2.Final it throws an error:  
`org.hibernate.query.SemanticException: Literal type 'class java.lang.String' did not match domain type 'java.time.LocalDateTime' nor converted type 'java.util.Date'`

This is “new” exception so I believe it is related to new SQM.

…`((:fromTime) IS NULL OR al.createdAt >= :fromTime)`…  
has never been working (and I have no idea why), saying:  
`ERROR: could not determine data type of parameter $9`  
(possibly due to dual mapping between java/hibernate and PostgreSQL, both `java.time.LocalDateTime` and `java.sql.Timstamp` are mapped to PostgreSQL’s `timestamp`)

What I’ve found:  
…`(cast(:fromTime as timestamp) IS NULL OR al.createdAt >= :fromTime)`…  
and  
…`(cast(:fromTime as java.sql.Timestamp) IS NULL OR al.createdAt >= :fromTime)`…  
and  
…`(cast(:fromTime as java.time.LocalDateTime) IS NULL OR al.createdAt >= :fromTime)`…  
are now working, so I wonder if some of these is recommended workaround for such case?

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [May 30, 2024, 12:02pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/2 "2024-05-30T12:02:43Z")

</div>

You should not use `coalesce(:listParameter)` nor `:listParameter is null`, they are unsupported and used to work only by chance in Hibernate 5. Since I recently replied to a very similar problem, please refer to my comment on [this post](https://discourse.hibernate.org/t/issue-with-coalesce-and-lists-after-upgrading-from-hibernate-v5-to-v6/9649/2) for further information.

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [May 30, 2024, 12:53pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/3 "2024-05-30T12:53:14Z")

</div>

I also have queries like  
`((:userIdsFromFilter) IS NULL OR al.actionByUserId IN :userIdsFromFilter)`  
and they are working fine with Hibernate v6, so lists are not the problem.

The problem is `LocalDateTime` parameter, which is considered primitive type I believe (and I can’t say if primitive is what you refer to as scalar).

What would be preferred solution for this case (Criteria API is not the solution, I need it to be regular query)?

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [May 30, 2024, 12:59pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/4 "2024-05-30T12:59:32Z")

</div>

As I already said, that syntax is unsupported, and the only way to fix it is not using parameters in `is null` predicates. This is true for both lists and basic values. Independently from how you’re creating the query, if you know your parameter will be null, just don’t include the predicate in the first place - I suggested a nice way of doing it with Criteria, but there are others.

You will have to change how you construct the queries, or keep using the unsupported syntax with an explicit cast acknowledging that in the future that might break again.

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [May 30, 2024, 1:41pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/5 "2024-05-30T13:41:41Z")

</div>

Ok I understand. Though it sounds weird that syntax  
`((:userIdsFromFilter) is null)`  
is unsupported, it’s pretty basic construction.  
Thanks for the answer!

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [May 30, 2024, 1:54pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/6 "2024-05-30T13:54:31Z")

</div>

Let me just get this straight: `is null` predicate is completely discouraged, with all its variations, in newer Hibernate v6, despite Hibernate ORM user guide ([https://docs.jboss.org/hibernate/orm/current/userguide/html\_single/Hibernate\_User\_Guide.html](https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html)) uses it in many places?

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [May 30, 2024, 2:03pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/7 "2024-05-30T14:03:04Z")

</div>

One more thing:  
…`((:fromTime) = NULL OR al.createdAt >= :fromTime)` …

works just fine. Is this solution ok?

(sorry, I made a typo at first attempt)

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [May 30, 2024, 2:03pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/8 "2024-05-30T14:03:50Z")

</div>

No, using `is null` predicate on **parameters** is unsupported, and doesn’t make any sense since you already know if the parameter will be `null` or not when constructing the query.

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [May 30, 2024, 2:07pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/9 "2024-05-30T14:07:54Z")

</div>

At the moment of query execution I know if the parameter is null or not, indeed. But some queries are complex enough, with multiple (potentially) null parameters, and handling all possible cases/combinations in the code (outside the query) is not a solution.

However this  
…`((:fromTime) = NULL OR al.createdAt >= :fromTime)` …  
works just fine. Is this solution ok?  
(this seems similar to how `:param is true` predicate changed to `:param = true` with new Hibernate)

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [May 31, 2024, 10:33am UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/10 "2024-05-31T10:33:56Z")

</div>

Could you please confirm if this query  
…`((:fromTime) = NULL OR al.createdAt >= :fromTime)` …  
is ok or not?  
So it’s not `IS NULL` predicate but rather simple check whether something is null or not.

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [May 31, 2024, 11:02am UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/11 "2024-05-31T11:02:59Z")

</div>

Using comparison operators with null values like `= null` in SQL always results in `null` (unknown) which evaluates to `false` when used in a predicate, so it won’t have the desired effect.

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [June 3, 2024, 11:28am UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/12 "2024-06-03T11:28:17Z")

</div>

“…using `is null` predicate on **parameters** is unsupported” - does this also apply to `coalesce`?  
Is usage of `coalesce` with any type of **parameter** unsupported too?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [June 4, 2024, 1:51pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/13 "2024-06-04T13:51:15Z")

</div>

Equality comparisons against a `null` literal only make sense with the `is null`/`is not null` predicate. You should never do `abc = null`, because that predicate will never produce `true`.

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [June 4, 2024, 2:00pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/14 "2024-06-04T14:00:55Z")

</div>

That one is clear. There was a doubt there as well, cause `IS TRUE/FALSE` is now replaced with `= TRUE/FALSE`, but now that one is clear.

Sorry for introducing this mess, I just want to confirm if I should NOT use neither `COALESCE` nor `IS NULL` with any type of parameter (meaning both `COALESCE` and `IS NULL` are meant to be used only on entities and entity properties)?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [June 4, 2024, 2:34pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/15 "2024-06-04T14:34:06Z")

</div>

> [@vedranmimic](#):
>
> That one is clear. There was a doubt there as well, cause `IS TRUE/FALSE` is now replaced with `= TRUE/FALSE`, but now that one is clear.

Not sure what you’re talking about. Comparing boolean attributes/values was always done by using the equality operator `=`. I think that Hibernate ORM 6 added support for `is true`/`is false` predicates, but these are new.

> [@vedranmimic](#):
>
> Sorry for introducing this mess, I just want to confirm if I should NOT use neither `COALESCE` nor `IS NULL` with any type of parameter (meaning both `COALESCE` and `IS NULL` are meant to be used only on entities and entity properties)?

With all that double negation it’s hard to understand what you’re even trying to ask.

…`(COALESCE(:fromTime, '') = '' OR al.createdAt >= :fromTime)` …

This fails because all arguments of `COALESCE` have to have the same type. Since Hibernate determines that `:fromTime` has the type `LocalDateTime`, it will fail processing this query because you’re also passing an empty `String`.

> [@vedranmimic](#):
>
> This is “new” exception so I believe it is related to new SQM.

Hibernate ORM is now complaining earlier, but the database would have choked on this as well.

> [@vedranmimic](#):
>
> …`((:fromTime) IS NULL OR al.createdAt >= :fromTime)`…  
> has never been working (and I have no idea why), saying:  
> `ERROR: could not determine data type of parameter $9`  
> (possibly due to dual mapping between java/hibernate and PostgreSQL, both `java.time.LocalDateTime` and `java.sql.Timstamp` are mapped to PostgreSQL’s `timestamp`)

If this still fails, please create a new Jira issue and attach a reproducer application for this problem.

> [@vedranmimic](#):
>
> What I’ve found:  
> …`(cast(:fromTime as timestamp) IS NULL OR al.createdAt >= :fromTime)`…  
> and  
> …`(cast(:fromTime as java.sql.Timestamp) IS NULL OR al.createdAt >= :fromTime)`…  
> and  
> …`(cast(:fromTime as java.time.LocalDateTime) IS NULL OR al.createdAt >= :fromTime)`…  
> are now working, so I wonder if some of these is recommended workaround for such case?

You can use all of these workarounds, but please report this problem. Ideally, you would use the JPA Criteria API to construct predicates only if a parameter value is given. That way, you don’t confuse the database query planner.

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [June 5, 2024, 9:58am UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/16 "2024-06-05T09:58:06Z")

</div>

Seems we turned full circle now :-/

1. By upgrading hibernate from v5 to v6, I had to change predicates `IS TRUE/FALSE` to `= TRUE/FALSE`  
This is the query that used to work with v5:

```auto
@Query("""
            select new com.eryce.taskservice.dto.TaskWithContext(t,tb)
            from Task t
            join TaskBoard tb on (t.archived is false and tb.id = t.taskBoardId)
            where ( (:taskBoardIds) is not null and t.taskBoardId in (:taskBoardIds) )
               or ( ( (:taskBoardIds) is null or t.taskBoardId not in (:taskBoardIds) ) and t.assigneeId = :userId )
            """)
    Slice<TaskWithContext> findUnarchivedTasksByTaskBoardIdsOrByUserId(Collection<String> taskBoardIds, Long userId, Pageable pageable);

```

which now produces a runtime error (`'(t.archivedisfalse'; Bad JPQL grammar`) with v6:

```auto
org.springframework.dao.InvalidDataAccessApiUsageException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 3:36 no viable alternative at input '(t.archivedisfalse'; Bad JPQL grammar [select new com.eryce.taskservice.dto.TaskWithContext(t,tb)
from Task t
join TaskBoard tb on (t.archived is false and tb.id = t.taskBoardId)
where ( (:taskBoardIds) is not null and t.taskBoardId in (:taskBoardIds_1) )
   or ( ( (:taskBoardIds) is null or t.taskBoardId not in (:taskBoardIds_1) ) and t.assigneeId = :userId )
]

```

when I replace `t.archived is false` with `t.archived = false`, everything works fine with v6.

1. Sorry for double negation, I just wanted to confirm: should I never use `COALESCE` with any type of parameter? Does same apply to both `COALESCE` and `IS NULL` predicate - they should be used exclusively with entity properties but never with parameters?

2. Now I’m confused with your remark to report problems with using parameters and workarounds with `IS NULL` predicate; I thought we already concluded parameters should never be used with `IS NULL` predicate…

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [June 5, 2024, 11:03am UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/17 "2024-06-05T11:03:17Z")

</div>

According to the [documentation of ORM 5](https://docs.jboss.org/hibernate/orm/5.6/userguide/html_single/Hibernate_User_Guide.html), the `is true`/`is false` syntax wasn’t supported. It was added to [ORM 6](https://docs.jboss.org/hibernate/orm/6.5/userguide/html_single/Hibernate_User_Guide.html#boolean-predicate) though.

> which now produces a runtime error (‘(t.archivedisfalse’; Bad JPQL grammar) with v6:

This is not an error thrown by Hibernate ORM, but rather a Spring Data specific exception, because for some reason, Spring folks think they need to write a custom parser for HQL. Go yell at them for doing this and hindering you to use HQL.

> [@vedranmimic](#):
>
> Sorry for double negation, I just wanted to confirm: should I never use `COALESCE` with any type of parameter? Does same apply to both `COALESCE` and `IS NULL` predicate - they should be used exclusively with entity properties but never with parameters?

You can use `COALESCE` and `IS NULL` with parameters, there is nothing wrong with that per-se. What you shouldn’t do is to use list parameters with these constructs. Your example clearly shows that you are using the list parameter `taskBoardIds` with `is null` predicate, which may or may not work. This is simply unsupported. List parameters are only supported in the `in` predicate.

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [June 5, 2024, 11:35am UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/18 "2024-06-05T11:35:07Z")

</div>

1. Thanks for clarification on this JPQL error message. I’m fine with using `= TRUE/FALSE`.

2. From this thread:

> [@mbladel](#):
>
> As I already said, that syntax is unsupported, and the only way to fix it is not using parameters in `is null` predicates. This is true for both lists and basic values.

And indeed  
…`(COALESCE(:fromTime, '') = '' OR al.createdAt >= :fromTime)` …  
is not working anymore with hibernate v6.  
Also, this has not been working even prior to v6:  
…`((:fromTime) IS NULL OR al.createdAt >= :fromTime)` …  
so I still need a way to solve this issue.  
I got opposite answers on whether should I use parameters in `COALESCE` and `IS NULL` or not.  
So:

- If I’m not supposed to do it, then I guess I need to remove these parts of the query and handle it in some other way (e.g. providing a boolean parameter into the query)
- If it’s fine to use parameters with `COALESCE` and `IS NULL` (which would be preferred solution, of course), can you please provide a valid syntax/example for it?

P.S.  
`:fromTime` is not a list, it is a LocalDateTime parameter.  
The one you’re refering to, `:taskBoardIds`, I’ll have to handle in some other way (probably by providing a boolean parameter into the query)

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [June 5, 2024, 1:52pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/19 "2024-06-05T13:52:42Z")

</div>

You’re mixing up different conversations. Read your own question again and then the answer I gave in this thread.

> [@vedranmimic](#):
>
> And indeed  
> …`(COALESCE(:fromTime, '') = '' OR al.createdAt >= :fromTime)` …  
> is not working anymore with hibernate v6.

> [@beikov](#):
>
> This fails because all arguments of `COALESCE` have to have the same type. Since Hibernate determines that `:fromTime` has the type `LocalDateTime`, it will fail processing this query because you’re also passing an empty `String`.

I don’t understand this obsession that people have with passing `null` as meaning “no filter”. Just write a Spring Data `Specification` and create those filter dynamically. It’s not hard and much more flexible.

---

<div class="post-metadata">

**Author:** ![vedranmimic](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vedranmimic/32/3232_2.png) [@vedranmimic](https://discourse.hibernate.org/u/vedranmimic)\
**Post date:** [June 5, 2024, 3:23pm UTC](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652/20 "2024-06-05T15:23:40Z")

</div>

I started this thread asking what’s the proper replacement for:  
…`(COALESCE(:fromTime, '') = '' OR al.createdAt >= :fromTime)` …  
And it wasn’t me mixing up conversations, your colleague gave answer that lists cannot be used with `COALESCE` and `IS NULL` (while parameter from my question is not a list, it is LocalDateTime).

I understand why it keeps failing - due to different types in `COALESCE`.

Also, I’m aware that using null parameter this way is not very smart solution, but it’s a huge and relatively old project, and it wasn’t me creating all those queries. Switching all of them to Spring Data `Specification` is not an option right now.

Since you mentioned it is ok to use parameters (not list parameters!) with `COALESCE` and `IS NULL`, I just asked for an example how that construct should look. If possible, could you please provide an example?

[Next page](https://discourse.hibernate.org/t/after-upgrading-hibernate-from-v5-to-v6-query-with-localdatetime-parameter-throws-an-error/9652.md?page=2)