Problem Summary: We have an existing spring boot application which has working JPA repositories which selects subset of columns from multiple entities in a left join in a single query. It is possible to construct multi column sort criterion using a Pageble object to sort by any column referenced in the result set. This has been working for like two years until spring boot 3.2.6. When I try to upgrade to Spring Boot version 3.3.0 I get an SQL error saying that the database column used in the order by clause is not found in the result set. What happens is in the result set it puts foreign key of one side of a many to one relationship (many side) where as in order by clause it puts the the primary key from the one side of the relationship. Everywhere in this JPQL I only reference the column via the entity on the One side of the Many-to-one association. But since Spring Boot Version 3.3.0 it is mixing up the two sides of the relationship when it translates the same attribute in result set and order by clause leading to an SQL error. This is breaking an existing code.
Below is the Detail of the Issue:
Code snippet for reference data entity below (name of the class and name of the attributes and columns have been changed for demonstration). this is the One side of the Many to One association.
@Slf4j
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "##DB_SCHEMA##.ref_fac_some_type")
public class RefFacilitySomeType {
@Id
@Column(name = "fac_some_cd_pk", nullable = false)
private String facilitySomeTypeCode;
@Column(name = "name", nullable = false, length = 55)
private String name;
...
}
Code snippet of the main entity class which is in the from clause of the JPA query which references RefFacilitySomeType entity. This is the Many side of the Many-toOne association.
@Slf4j
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "##DB_SCHEMA##.dwp_facility")
public class DWPFacility {
@Id
@GeneratedValue(generator = "dwp_facility_seq")
@SequenceGenerator(name = "dwp_facility_seq", sequenceName = "##DB_SCHEMA##.dwp_facility_seq", allocationSize = 1)
@Column(name = "fac_seq_pk")
private Integer facilityId;
...
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "some_type_fk", referencedColumnName = "fac_some_cd_pk", nullable = true, insertable = true,
updatable = true)
private RefFacilitySomeType someType;
...
}
Below is a code snippet from a JPA repository method.
What this method does: DWPFacility entity has a very complex class structure with nearly 30 tables. The below method findByCriteria implements a search interface for searching a subset of columns in a result set. The columns are selected from more than one table to show a summary view in a data grid. It is necessary to allow different combination of multiple columns for each column in the result set. These combinations are not fixed. As a result before calling this method a Pageable object is constructed with multi column sort criterion. Since the result set search column list should contain exact column that is referenced in the order by clause a strategy of using distinct new com.company.sdwismod.inventory.repository.model.DWPFacilitySearchRec(...) object to combine columns from different entity objects in the left join is achieved.
@Repository
public interface DWPFacilityRepository extends JpaRepository<DWPFacility, Integer> {
@Query("Select distinct new com.company.sdwismod.inventory.repository.model.DWPFacilitySearchRec(fac.facilityId,fac.paAssignedId,fac.fedFacilityId,fac.name,ft.facilityTypeCode, ft.name, fs.facilityStatusCode, fs.name, fa.facilityAvailabilityCode, fa.name, fac.srcInd, wt.facilitySomeTypeCode, wt.name, ts.treatmentStatusCode, ts.name,fac.createId,fac.updateId,fac.removeId,fac.createDt,fac.updateDt,fac.removeDt,fac.lastReportedToFedDt) from DWPFacility fac left join fac.dwpSomeSystem ws left join fac.facilityType ft left join fac.facilityStatus fs left join fac.facilityAvailability fa left join fac.someType wt left join fac.treatmentStatus ts left join ws.regulatingAgencies ra "
+ " where ((cast(:someSystemId as string) is null) OR UPPER(ws.someSystemId) like UPPER(concat(cast(:someSystemId as string),'%'))) and ((cast(:facilityId as string) is null) OR (fac.facilityId =:facilityId)) and ((cast(:facilityTypeCode as string) is null) OR UPPER(ft.facilityTypeCode) like UPPER(concat(cast(:facilityTypeCode as string),'%'))) and ((cast(:name as string) is null) OR UPPER(fac.name) like UPPER(concat(cast(:name as string),'%'))) and ((cast(:fedFacilityId as string) is null) OR (fac.fedFacilityId = :fedFacilityId)) and ((cast(:paAssignedId as string) is null) OR UPPER(fac.paAssignedId) like UPPER(concat(cast(:paAssignedId as string),'%'))) and ((cast(:facilityStatusCode as string) is null) OR UPPER(fs.facilityStatusCode) like UPPER(concat(cast(:facilityStatusCode as string),'%'))) and ((cast(:facilityAvailabilityCode as string) is null) OR UPPER(fa.facilityAvailabilityCode) like UPPER(concat(cast(:facilityAvailabilityCode as string),'%'))) and ((cast(:facilitySomeTypeCode as string) is null) OR UPPER(wt.facilitySomeTypeCode) like UPPER(concat(cast(:facilitySomeTypeCode as string),'%'))) and ((cast(:treatmentStatusCode as string) is null) OR UPPER(ts.treatmentStatusCode) like UPPER(concat(cast(:treatmentStatusCode as string),'%'))) and ((cast(:srcInd as string) is null) OR (UPPER(fac.srcInd) = UPPER(cast(:srcInd as string)))) and (ra.raLegalEntityId IN :regulatingAgencyIds) and fac.removeDt is null")
Page<DWPFacilitySearchRec> findByCriteria(@Param("someSystemId") String someSystemId,
@Param("facilityId") Integer facilityId, @Param("name") String name,
@Param("facilityTypeCode") String facilityTypeCode, @Param("fedFacilityId") Integer fedFacilityId,
@Param("paAssignedId") String paAssignedId, @Param("facilityStatusCode") String facilityStatusCode,
@Param("facilityAvailabilityCode") String facilityAvailabilityCode, @Param("srcInd") String srcInd,
@Param("facilitySomeTypeCode") String facilitySomeTypeCode,
@Param("treatmentStatusCode") String treatmentStatusCode,
@Param("regulatingAgencyIds") Collection<Integer> regulatingAgencyIds, Pageable pageable);
}
The issue: We have had this code for nearly two yeasrs and this has been working since spring boot 2.x. We upgraded upto spring boot 3.2.6 with no issues. Due to security vulnerability issues discovred during dependency scan we anticipate that eventually we will have to continue to upgrade the spring boot version. I attempted to upgrade to spring boot version 3.3.0. Then the this JPQL query and Pageable produces the wrong query. This is how it happens. Please take a look at the column list in the result set in the above JPQL. It selects the column "wt.facilitySomeTypeCode". This alias name wt is derived from "left join fac.someType wt" clause. what is wt referenced here. It is an instance of RefFacilitySomeType. Then "wt.facilitySomeTypeCode" must be translated into WT1_0.FAC_SOME_CD_PK in the automatically generated SQL statement. Instead it puts d1_0.some_type_fk in the result set. "d1_0.some_type_fk" seem to have been derrived from the many to one relation ship mapping @JoinColumn(name = "some_type_fk", referencedColumnName = "fac_some_cd_pk", nullable = true, insertable = true, updatable = true) private RefFacilitySomeType someType;
When I explicitely said that I want "wt.facilitySomeTypeCode" I expected that it would add WT1_0.FAC_SOME_CD_PK in the result set. It works perfectly until Spring Boot version 3.2.6. It is behaving differently in Spring Boot 3.3.0. The interresting thing is when I want to sort by "wt.facilitySomeTypeCode" I pass this column name to construct the sort criterion in the Pageable object. The sort criterion in the Pageable objects gets translated exactly the way I intend it to work. Pageable object when translated in to order by clause, It translates "wt.facilitySomeTypeCode" into WT1_0.FAC_SOME_CD_PK. However the resulted SQL query fails with below error because the result set has "d1_0.some_type_fk" and order by clause has "WT1_0.FAC_SOME_CD_PK" resulted from the same JPQL reference "wt.facilitySomeTypeCode". This is undoubtedly a bug introduced by Spring boot 3.3.0.
Please see the below error:
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement [Order by expression "WT1_0.FAC_SOME_CD_PK" must be in the result list in this case; SQL statement:
select distinct d1_0.fac_seq_pk,d1_0.pa_assigned_id,d1_0.fed_fac_seq,d1_0.name,d1_0.fac_type_fk,ft1_0.name,d1_0.pa_status_cd_fk,fs1_0.name,d1_0.avail_fk,fa1_0.name,d1_0.src_ind_dms,**d1_0.some_type_fk**,wt1_0.name,d1_0.tmnt_status_fk,ts1_0.name,d1_0.create_id,d1_0.update_id,d1_0.remove_id,d1_0.create_dt,d1_0.update_dt,d1_0.remove_dt,d1_0.last_reported_to_fed_dt_dms from dwp_x1.dwp_facility d1_0 left join dwp_x1.dwp_some_system dws1_0 on dws1_0.pwsid_pk=d1_0.pwsid_fk left join dwp_x1.dwp_ra_ws_assoc ra1_0 on dws1_0.pwsid_pk=ra1_0.pwsid_fk left join dwp_x1.ref_fac_type ft1_0 on ft1_0.fac_cd_pk=d1_0.fac_type_fk left join dwp_x1.ref_fac_status fs1_0 on fs1_0.fac_status_cd_pk=d1_0.pa_status_cd_fk left join dwp_x1.ref_fac_availability fa1_0 on fa1_0.fac_availability_cd_pk=d1_0.avail_fk left join dwp_x1.ref_fac_some_type wt1_0 on wt1_0.fac_some_cd_pk=d1_0.some_type_fk left join dwp_x1.ref_tmnt_status ts1_0 on ts1_0.tmnt_status_cd_pk=d1_0.tmnt_status_fk where ((cast(? as text) is null) or upper(d1_0.pwsid_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or (d1_0.fac_seq_pk=?)) and ((cast(? as text) is null) or upper(d1_0.fac_type_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.name) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or (d1_0.fed_fac_seq=?)) and ((cast(? as text) is null) or upper(d1_0.pa_assigned_id) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.pa_status_cd_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.avail_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.some_type_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.tmnt_status_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or (upper(d1_0.src_ind_dms)=upper(cast(? as text)))) and (ra1_0.reg_le_agency_seq_fk in (?,?,?,?)) and d1_0.remove_dt is null order by d1_0.name,d1_0.fac_seq_pk,d1_0.fed_fac_seq desc,d1_0.pa_assigned_id desc,d1_0.src_ind_dms desc,**wt1_0.fac_some_cd_pk desc**,fs1_0.fac_status_cd_pk desc,fa1_0.fac_availability_cd_pk desc,ts1_0.tmnt_status_cd_pk desc,ft1_0.fac_cd_pk desc offset ? rows fetch first ? rows only [90068-220]] [select distinct d1_0.fac_seq_pk,d1_0.pa_assigned_id,d1_0.fed_fac_seq,d1_0.name,d1_0.fac_type_fk,ft1_0.name,d1_0.pa_status_cd_fk,fs1_0.name,d1_0.avail_fk,fa1_0.name,d1_0.src_ind_dms,d1_0.some_type_fk,wt1_0.name,d1_0.tmnt_status_fk,ts1_0.name,d1_0.create_id,d1_0.update_id,d1_0.remove_id,d1_0.create_dt,d1_0.update_dt,d1_0.remove_dt,d1_0.last_reported_to_fed_dt_dms from dwp_x1.dwp_facility d1_0 left join dwp_x1.dwp_some_system dws1_0 on dws1_0.pwsid_pk=d1_0.pwsid_fk left join dwp_x1.dwp_ra_ws_assoc ra1_0 on dws1_0.pwsid_pk=ra1_0.pwsid_fk left join dwp_x1.ref_fac_type ft1_0 on ft1_0.fac_cd_pk=d1_0.fac_type_fk left join dwp_x1.ref_fac_status fs1_0 on fs1_0.fac_status_cd_pk=d1_0.pa_status_cd_fk left join dwp_x1.ref_fac_availability fa1_0 on fa1_0.fac_availability_cd_pk=d1_0.avail_fk left join dwp_x1.ref_fac_some_type wt1_0 on wt1_0.fac_some_cd_pk=d1_0.some_type_fk left join dwp_x1.ref_tmnt_status ts1_0 on ts1_0.tmnt_status_cd_pk=d1_0.tmnt_status_fk where ((cast(? as text) is null) or upper(d1_0.pwsid_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or (d1_0.fac_seq_pk=?)) and ((cast(? as text) is null) or upper(d1_0.fac_type_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.name) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or (d1_0.fed_fac_seq=?)) and ((cast(? as text) is null) or upper(d1_0.pa_assigned_id) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.pa_status_cd_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.avail_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.some_type_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or upper(d1_0.tmnt_status_fk) like upper((cast(? as text)||'%')) escape '') and ((cast(? as text) is null) or (upper(d1_0.src_ind_dms)=upper(cast(? as text)))) and (ra1_0.reg_le_agency_seq_fk in (?,?,?,?)) and d1_0.remove_dt is null order by d1_0.name,d1_0.fac_seq_pk,d1_0.fed_fac_seq desc,d1_0.pa_assigned_id desc,d1_0.src_ind_dms desc,wt1_0.fac_some_cd_pk desc,fs1_0.fac_status_cd_pk desc,fa1_0.fac_availability_cd_pk desc,ts1_0.tmnt_status_cd_pk desc,ft1_0.fac_cd_pk desc offset ? rows fetch first ? rows only]
Could you please fix this issue sooner?
Comment From: scottfrederick
Spring Boot itself it not involved in generating SQL queries - that is done by Hibernate or JPA. Spring Boot 3.2.6 uses Hibernate 6.4.8.Final and Spring Data BOM 2023.1.6, while Spring Boot 3.3.0 uses Hibernate 6.5.2.Final and Spring Data BOM 2024.0.0. It is likely that the changes you are seeing are the result of these library upgrades. You can narrow this down by keeping Spring Boot 3.3.0 and overriding the version of these dependencies with Maven or Gradle. If you can identify one of those library upgrades as the source of the problem, you can file an issue with the appropriate project.
If you can't narrow it down to a dependency version, and you would like us to take a closer look, then we will need a sample project that reproduces the problem and that we can run ourselves. You can share it with us by pushing it to a separate repository on GitHub or by zipping it and attaching it to this issue.
Comment From: dulshand
@scottfrederick Thank you for the quick response. I will see if I can narrow the issue to Hibernate 6.5.2.Final. I will let you know my findings.
Comment From: dulshand
@scottfrederick I can now confirm that the issue is in Hibernate 6.5.2.Final referenced by Spring Data BOM. I did the following in my pom.xml and the issue dissapeared.
Comment From: scottfrederick
Thanks for letting us know. I'll close this issue, feel free to add a comment with a link to a Hibernate issue if you create one.
Comment From: dulshand
I have created an issue with Hibernate regarding this. https://hibernate.atlassian.net/browse/HHH-18253