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?
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 for further information.
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)?
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.
Ok I understand. Though it sounds weird that syntax ((:userIdsFromFilter) is null)
is unsupported, it’s pretty basic construction.
Thanks for the answer!
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.
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)
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.
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.
“…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?
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.
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)?
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.
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.
Hibernate ORM is now complaining earlier, but the database would have choked on this as well.
If this still fails, please create a new Jira issue and attach a reproducer application for this problem.
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.
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:
@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:
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.
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?
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…
According to the documentation of ORM 5, the is true/is false syntax wasn’t supported. It was added to ORM 6 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.
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.
Thanks for clarification on this JPQL error message. I’m fine with using = TRUE/FALSE.
From this thread:
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)
You’re mixing up different conversations. Read your own question again and then the answer I gave in this thread.
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.
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?