spring-boothibernatejpql

Upgrade from hibernate 5 to hibernate 6.4 causes a wrong generated SQL statement with a strange case when condition

I have upgraded a spring boot app from 2.7 to 3.2.4, with a consequent upgrade from hibernate 5 to hibernate 6.4.

I'm querying MyTable entity which has a cancellato column with a custom converter that transforms 'S' to true and 'N' to false.

@Column(name = "CANCELLATO", columnDefinition = "char")
@Convert(converter = SiNoConverter.class)
private Boolean cancellation;

The following jpql query:

select pduv from MyTable pduv where pduv.idPiano = :idPiano 
and pduv.utente = :utente 
and pduv.cancellato is false 
and pduv.id in (:idPianoDettaglioUtenteList)

is different with the two hibernate versions.

With spring boot 2.7 and hibernate 5:

select pianodetta0_.id                     as id1_69_,
       pianodetta0_.cancellato             as cancella2_69_,
       pianodetta0_.naz                    as naz11_69_,
       pianodetta0_.previsione             as previsi19_69_,
       pianodetta0_.utente                 as utente21_69_
from sped.my_table pianodetta0_
         cross join dist.trasportatore trasportat1_
         cross join dist.zona zona2_
where pianodetta0_.id_trasportatore = trasportat1_.id
  and pianodetta0_.id_zona = zona2_.id
  and pianodetta0_.id_piano = ?
  and pianodetta0_.utente = ?
  and pianodetta0_.cancellato = 'N'
  and (pianodetta0_.id in (?))

With spring boot 3.2.4 and hibernate 6:

select pduv1_0.id,
       pduv1_0.cancellato,
       pduv1_0.naz,
       pduv1_0.previsione,
       pduv1_0.utente
from sped.my_table pduv1_0
         join dist.trasportatore t1_0 on t1_0.id = pduv1_0.id_trasportatore
         join dist.zona z1_0 on z1_0.id = pduv1_0.id_zona
where pduv1_0.id_piano = 81090
  and pduv1_0.utente = '270'
  and (case pduv1_0.cancellato when 0 then 1 when 1 then 0 else 0 end = 1)
  and pduv1_0.id in (10299)

The case statement seems overly complicated and has the wrong data types apparently.

With the new version I get this SQL error (the database is SQL server).

Conversion failed when converting the ****** value '******' to data type ******.

Thank you for your support

Solution

  • The error is caused by using the "is false" instruction instead of "= false".

    To solve your problem, you should correct the query as follows:

    select pduv from MyTable pduv where pduv.idPiano = :idPiano 
and pduv.utente = :utente 
and pduv.cancellato = false
and pduv.id in (:idPianoDettaglioUtenteList)