PostgreSQL case in where clause errror

[dreab8]

Version

5.0.0

Context

Wile working on Hibernate Reactive I noticed that using PostgreSQL the query using client.preparedQuery(sql).execute(parameters):

select f1_0.id,f1_0.age,f1_0.rating,f1_0.title
from Film f1_0
where f1_0.age<=case when (f1_0.rating=$1) then $2 when (f1_0.rating=$3) then $4 else $5 end
order by f1_0.id

with parameters [1,2,3,4,5].

It throws:

operator does not exist: integer <= text (42883)] [select 
f1_0.id,f1_0.age,f1_0.rating,f1_0.title from Film f1_0 where f1_0.age<=case when (f1_0.rating=$1) then $2 when (f1_0.rating=$3) then $4 else $5 end order by f1_0.id]

Steps to reproduce

No response

Do you have a reproducer?

No response

[tsegismont]

I think this may be worked around by providing a type cast for the case expression.

Could you please share the DDL statement for Film?

This issue could be related to #363 and #700. Currently, the client prepares statements without providing parameters first (and tries again with parameters if not inside a transaction). It does this to be able to cache statements efficiently. We may need to reconsider this.

[dreab8]

Hi @tsegismont , the DDL statement is create table Film (age integer, rating integer, id bigint not null, title varchar(255), primary key (id))

[tsegismont]

I can confirm that if you change the case part to case when (f1_0.rating = $1) then $2::int when (f1_0.rating = $3) then $4::int else $5::int end then the failure goes away.

So, it is related to #363 and will be fixed by allowing to prepare with type parameters when necessary

[tsegismont]

I switched the milestone to 5.1.0 since this is a significant behavior change, so it seems safer to wait for the next minor release.