Case in where clause not handled properly for Postgres #2345
Description
Hi,
I'm building a Spring Boot Starter for Hibernate Reactive. I'm on Hibernate Reactive 4.0Beta1 with Vert.x 5.x drivers.
One of my features is Querydsl integration. As Hibernate Reactive doesn't support Querydsl natively, I serialize the predicate to HQL and pass that to Hibernate Reactive. This works fine for MySQL, MariaDB, Cockroach and Oracle (and in all other cases, Postgres as well).
I've run into an issue when supporting case in a where clause (this test case works on MySQL, MariaDB, Cockroach and Oracle). For reference, the unit test looks like:
NumberExpression<Double> cases =
Expressions.cases()
.when(QFilm.film.rating.eq(Rating.G))
.then(6.99)
.when(QFilm.film.rating.eq(Rating.PG))
.then(8.99)
.when(QFilm.film.rating.eq(Rating.R))
.then(9.99)
.otherwise(4.99);
StepVerifier.create(
this.filmRepository
.findAll(QFilm.film.replacementCost.loe(cases), QSort.by(QFilm.film.filmId.asc()))
.map(Film::getFilmId))
.expectNext(23L, 221L, 501L, 686L, 705L, 747L, 863L)
.verifyComplete();
I end up serializing this to HQL as:
select film from film film
where film.replacementCost <= (case when (film.rating = ?1) then ?2 when (film.rating = ?3) then ?4 when (film.rating = ?5) then ?6 else ?7 end)
order by film.filmId asc
And that gets expanded by Hibernate Reactive to raw sql as:
select
f1_0.film_id,
f1_0.description,
f1_0.desc_embedding,
f1_0.language_id,
f1_0.rating,
f1_0.release_year,
f1_0.rental_duration,
f1_0.replacement_cost,
f1_0.title
from
film f1_0
where
f1_0.replacement_cost<=case
when (f1_0.rating=$1)
then $2
when (f1_0.rating=$3)
then $4
when (f1_0.rating=$5)
then $6
else $7
end
order by
f1_0.film_id
This query works fine in something like Datagrip, but Hibernate returns an error:
2025-07-11T10:05:10.517-07:00 WARN 22204 --- [ntloop-thread-0] o.h.o.j.error : HHH000247: ErrorCode: 0, SQLState: 42883
2025-07-11T10:05:10.517-07:00 WARN 22204 --- [ntloop-thread-0] o.h.o.j.error : ERROR: operator does not exist: numeric <= text (42883)
Suppressed: org.hibernate.exception.SQLGrammarException: error executing SQL statement [ERROR: operator does not exist: numeric <= text (42883)] [select f1_0.film_id,f1_0.description,f1_0.desc_embedding,f1_0.language_id,f1_0.rating,f1_0.release_year,f1_0.rental_duration,f1_0.replacement_cost,f1_0.title from film f1_0 where f1_0.replacement_cost<=case when (f1_0.rating=$1) then $2 when (f1_0.rating=$3) then $4 when (f1_0.rating=$5) then $6 else $7 end order by f1_0.film_id]
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:63)
Suppressed: The stacktrace has been enhanced by Reactor, refer to additional information below:
A
From what I can tell, this seems to be related to the "then" parts. If I change the serialization to inline the parameters for the "then"'s, then Postgres works, but binding them as parameters, they seem to get converted to strings under Postgres. Perhaps this is what happens under the other databases as well, but they are more forgiving.
I also tried wrapping the case as:
(case
when (f1_0.rating=$1)
then $2
when (f1_0.rating=$3)
then $4
when (f1_0.rating=$5)
then $6
else $7
end)::numeric
which also works in Datagrip, but Hibernate gets a parsing error on that.
I also tried CAST( ... as float(53)), but still get the same ERROR: operator does not exist: numeric <= text (42883) error.