After Spring Boot 3 upgrade (used Hibernate 6), existing JPA queries with JOIN not able to check enum properly
Description
I have attached three entity classes.
While using Spring Boot 2.7.x which imports Hibernate 5.x.x I used JPA queries @Query("SELECT new org.innovateuk.ifs.application.resource.PreviousApplicationResource(" + "app.id, " + "app.name, " + "lead.name, " + "app.applicationProcess.activityState, " + "app.competition.id " + ") FROM Application app " + " LEFT JOIN Project project " + " ON project.application.id = app.id " + " JOIN ProcessRole pr" + " ON pr.applicationId = app.id " + " JOIN Organisation lead " + " ON lead.id = pr.organisationId" + PREVIOUS_WHERE_CLAUSE + " AND pr.role = org.innovateuk.ifs.user.resource.ProcessRoleType.LEADAPPLICANT ") List<PreviousApplicationResource> findPrevious(long competitionId); String PREVIOUS_WHERE_CLAUSE = " WHERE project.id IS NULL " + " AND app.applicationProcess.activityState != org.innovateuk.ifs.application.resource.ApplicationState.CREATED " + " AND app.applicationProcess.activityState != org.innovateuk.ifs.application.resource.ApplicationState.OPENED " + " AND app.competition.id = :competitionId";
Hibernate generated below query using above JPA query
select a1_0.id,a1_0.name,o1_0.name,ap1_0.activity_state_id,a1_0.competition from application a1_0 left join project p1_0 on p1_0.application_id=a1_0.id join process_role pr1_0 on pr1_0.application_id=a1_0.id join organisation o1_0 on o1_0.id=pr1_0.organisation_id join process ap1_0 on a1_0.id=ap1_0.target_id where p1_0.id is null and ap1_0.activity_state_id<>27 and ap1_0.activity_state_id<>28 and a1_0.competition=? and pr1_0.role_id=1
This was working fine and returning the correct result.
But after upgrading to Spring Boot 3 which imports Hibernate 6.x.x above queries are not returning the correct result. In order to work with I had to modify JPA queries like below.
@Query("SELECT new org.innovateuk.ifs.application.resource.PreviousApplicationResource(" + "app.id, " + "app.name, " + "lead.name, " + "proc.activityState, " + "app.competition.id " + ") FROM Application app " + " LEFT JOIN Project project " + " ON project.application.id = app.id " + " JOIN ProcessRole pr" + " ON pr.applicationId = app.id " + " JOIN Organisation lead " + " ON lead.id = pr.organisationId" + " JOIN ApplicationProcess proc " + " ON proc.target = app " + PREVIOUS_WHERE_CLAUSE + " AND pr.role = org.innovateuk.ifs.user.resource.ProcessRoleType.LEADAPPLICANT ") List<PreviousApplicationResource> findPrevious(long competitionId); String PREVIOUS_WHERE_CLAUSE = " WHERE project.id IS NULL " + " AND proc.activityState != org.innovateuk.ifs.application.resource.ApplicationState.CREATED " + " AND proc.activityState != org.innovateuk.ifs.application.resource.ApplicationState.OPENED " + " AND app.competition.id = :competitionId";
Hibernate generated
select a1_0.id,a1_0.name,o1_0.name,ap1_0.activity_state_id,a1_0.competition from application a1_0 left join project p1_0 on p1_0.application_id=a1_0.id join process_role pr1_0 on pr1_0.application_id=a1_0.id join organisation o1_0 on o1_0.id=pr1_0.organisation_id join (select * from process t where t.process_type='ApplicationProcess') ap1_0 on ap1_0.target_id=a1_0.id where p1_0.id is null and ap1_0.activity_state_id<>27 and ap1_0.activity_state_id<>28 and a1_0.competition=? and pr1_0.role_id=1
It looks like hibernate 6.x.x is adding join on table process which is also checking process_type. With the old query in hibernate 5.x.x although there was a join on the process table but process_type check was not added.
Attachments
3
Activity
Show:
Gavin King June 3, 2024 at 7:49 AM
Edited
Before reporting an issue in the Hibernate issue tracker, you must take the time to:
reproduce the problem with plain Hibernate, and no Spring,
simplify your code to just the very minimum required to reproduce the problem,
provide a full runnable test which demonstrates the problem,
describe the problem using properly-formatted code blocks that we can actually read
Hibernate is used by hundreds of thousands of developers, and maintained by a very small team. We don't have the bandwidth to do these things for you.
I have attached three entity classes.
While using Spring Boot 2.7.x which imports Hibernate 5.x.x I used JPA queries
@Query("SELECT new org.innovateuk.ifs.application.resource.PreviousApplicationResource(" + "app.id, " + "app.name, " + "lead.name, " + "app.applicationProcess.activityState, " + "app.competition.id " + ") FROM Application app " + " LEFT JOIN Project project " + " ON project.application.id = app.id " + " JOIN ProcessRole pr" + " ON pr.applicationId = app.id " + " JOIN Organisation lead " + " ON lead.id = pr.organisationId" + PREVIOUS_WHERE_CLAUSE + " AND pr.role = org.innovateuk.ifs.user.resource.ProcessRoleType.LEADAPPLICANT ") List<PreviousApplicationResource> findPrevious(long competitionId); String PREVIOUS_WHERE_CLAUSE = " WHERE project.id IS NULL " + " AND app.applicationProcess.activityState != org.innovateuk.ifs.application.resource.ApplicationState.CREATED " + " AND app.applicationProcess.activityState != org.innovateuk.ifs.application.resource.ApplicationState.OPENED " + " AND app.competition.id = :competitionId";Hibernate generated below query using above JPA query
select a1_0.id,a1_0.name,o1_0.name,ap1_0.activity_state_id,a1_0.competition from application a1_0 left join project p1_0 on p1_0.application_id=a1_0.id join process_role pr1_0 on pr1_0.application_id=a1_0.id join organisation o1_0 on o1_0.id=pr1_0.organisation_id join process ap1_0 on a1_0.id=ap1_0.target_id where p1_0.id is null and ap1_0.activity_state_id<>27 and ap1_0.activity_state_id<>28 and a1_0.competition=? and pr1_0.role_id=1This was working fine and returning the correct result.
But after upgrading to Spring Boot 3 which imports Hibernate 6.x.x above queries are not returning the correct result. In order to work with I had to modify JPA queries like below.
@Query("SELECT new org.innovateuk.ifs.application.resource.PreviousApplicationResource(" + "app.id, " + "app.name, " + "lead.name, " + "proc.activityState, " + "app.competition.id " + ") FROM Application app " + " LEFT JOIN Project project " + " ON project.application.id = app.id " + " JOIN ProcessRole pr" + " ON pr.applicationId = app.id " + " JOIN Organisation lead " + " ON lead.id = pr.organisationId" + " JOIN ApplicationProcess proc " + " ON proc.target = app " + PREVIOUS_WHERE_CLAUSE + " AND pr.role = org.innovateuk.ifs.user.resource.ProcessRoleType.LEADAPPLICANT ") List<PreviousApplicationResource> findPrevious(long competitionId); String PREVIOUS_WHERE_CLAUSE = " WHERE project.id IS NULL " + " AND proc.activityState != org.innovateuk.ifs.application.resource.ApplicationState.CREATED " + " AND proc.activityState != org.innovateuk.ifs.application.resource.ApplicationState.OPENED " + " AND app.competition.id = :competitionId";Hibernate generated
select a1_0.id,a1_0.name,o1_0.name,ap1_0.activity_state_id,a1_0.competition from application a1_0 left join project p1_0 on p1_0.application_id=a1_0.id join process_role pr1_0 on pr1_0.application_id=a1_0.id join organisation o1_0 on o1_0.id=pr1_0.organisation_id join (select * from process t where t.process_type='ApplicationProcess') ap1_0 on ap1_0.target_id=a1_0.id where p1_0.id is null and ap1_0.activity_state_id<>27 and ap1_0.activity_state_id<>28 and a1_0.competition=? and pr1_0.role_id=1It looks like hibernate 6.x.x is adding join on table process which is also checking process_type. With the old query in hibernate 5.x.x although there was a join on the process table but process_type check was not added.