For this JPQL
{code:java}select mdf.id from MasterDataFileEntity mdf
where mdf.dataImportStatus = 'SUCCESS'
and mdf.metaData.country = :countryCode
and mdf.metaData.transportMode = :transportMode
and mdf.metaData.product = :product
and mdf.importFinishedAt =
(select max(mdf.importFinishedAt) from MasterDataFileEntity mdf
where mdf.dataImportStatus = 'SUCCESS'
and mdf.metaData.country = :countryCode
and mdf.metaData.transportMode = :transportMode
and mdf.metaData.product = :product){code}
Hibernate generates this SQL
{code:sql}select m1_0.id from PEL.pel_master_data_file m1_0
where m1_0.data_import_status='SUCCESS'
and m1_0.country_code=?
and m1_0.transport_mode=?
and m1_0.product=?
and m1_0.import_finished_at=
(select max(m2_0.import_finished_at) from PEL.pel_master_data_file m2_0
where m2_0.data_import_status='SUCCESS'
and m1_0.country_code=?
and m1_0.transport_mode=?
and m1_0.product=?){code}
The table alias for the subquery for all conditions joined by {{and}} is wrong, taken not from the subquery but from the main query.
Workaround is to use a different alias for the subquery in JPQL.
Test case: [https://github.com/vseibt/hibernate-test-case-templates/commit/405bc954f7d87f21fb8e9446fdad2ed486b00dc8|https://github.com/vseibt/hibernate-test-case-templates/commit/405bc954f7d87f21fb8e9446fdad2ed486b00dc8|smart-link] |
|
