Hibernate 6 migration – error SqmQualifiedJoin predicate referred to SqmRoot other than the join’s root

by

I have the following @Query which is working fine ine Hibernate 5.x but which failed to be validated since I migrated to Hibernate 6.

@Query("select etab "
     + "from #{#entityName} etab "
     + "JOIN Mef mef ON mef.etabUajId = etab.id, "
     + "TiMefAccueil nomenMef "
     + "JOIN AepBanMef ban ON "
         + "((mef.code = ban.code and mef.mefRattachement = null) "
             + "OR mef.mefRattachement = ban.code) "
     + "where ((substring(mef.code, 1, length(nomenMef.debutCodeMef)) = nomenMef.debutCodeMef"
         + " AND substring("
             + " mef.code,"
             + " 10 - length(nomenMef.finCodeMef10Car) + 1,"
             + " length(nomenMef.finCodeMef10Car)"
         + ") = nomenMef.finCodeMef10Car)"
         + " OR ("
         + " substring(mef.mefRattachement, 1, length(nomenMef.debutCodeMef))"
             + " = nomenMef.debutCodeMef"
         + " AND substring("
             + " mef.mefRattachement,"
             + " 10 - length(nomenMef.finCodeMef10Car) + 1,"
             + " length(nomenMef.finCodeMef10Car)"
         + ") = nomenMef.finCodeMef10Car)"
     + ") and mef.mefSelectionne = true and nomenMef.codeTs = :codeTs "
     + "and (mef.dateFermeture > current_date OR mef.dateFermeture = null)"
     + "and (ban.dateFermeture > current_date OR ban.dateFermeture = null)")
List<EtabUaj> findEtabsByCodeTi(@Param("codeTs") String codeTs);

I have this error:

Caused by: org.hibernate.query.SemanticException: SqmQualifiedJoin predicate referred to SqmRoot [`fr.edu.scolarite.nat.paramshared.model.Mef(mef)`] other than the join's root [`fr.edu.scolarite.nat.paramts.aep.model.AepBanMef(ban)`]

I don’t understand why the join AepBanMef ban is not valid ? If I comment all the lines refering to BanMef the query is technically valid, but it’s not what I want.

The few resources I could found around refered to the Criteria API, which I’m not using.

Solution:

In the first lines, the JOIN AepBanMef ban was moved directly after mef =>


@Query("select etab "
     + "from #{#entityName} etab "
     + "JOIN Mef mef ON mef.etabUajId = etab.id "
     + "JOIN AepBanMef ban ON ((mef.code = ban.code and mef.mefRattachement = null) "
        + "OR mef.mefRattachement = ban.code), "
     + "TiMefAccueil nomenMef "
     + "where (....)

Leave a Comment