Missing from clause in query with joined inheritance, regression in 5.4.5
Description
https://hibernate.atlassian.net/browse/HHH-12993#icft=HHH-12993 causes an regression where from clauses are invalidly omitted from the query. Despite my concerns raised on the PR, I see this feature has been enabled by default still. I still think it should not be.
The following JPQL query:
SELECT scopeChildren.root.id,
SUM(summaryScope.soldUnits),
round(AVG(COALESCE(summaryScope.price, asco.price)), 2),
SUM(cgls.discount),
SUM(cgls.partnerDiscount),
SUM(cgls.netRevenue),
SUM(cgls.grossProfit),
SUM(cgls.totalCosts),
SUM(cgls.grossRevenue),
SUM(cgls.partnerCommission),
SUM(cgls.employeeCommission),
SUM(cgls.teacherSalary),
SUM(cgls.locationExpense),
SUM(cgls.bookletWriterExpense),
SUM(cgls.bookletPrintingExpense),
SUM(cgls.otherExpense)
FROM AthenaStudiesCourseGroup summaryScope
JOIN summaryScope.ledger ledger_1
JOIN ledger_1.courseGroupLedgerSummary cgls
JOIN ScopeCte scopeChildren ON (scopeChildren.id = summaryScope.id)
JOIN AthenaStudiesCourse asco ON (EXISTS
(SELECT 1
FROM summaryScope.parent _synth_subquery_0
WHERE asco.id = _synth_subquery_0.id))
GROUP BY scopeChildren.root.id
Works in 5.4.3 and produces the following SQL fragment:
SELECT scopecte3_.root_scope_id AS col_0_0_,
sum(athenastud0_1_.sold_units) AS col_1_0_,
round(avg(coalesce(athenastud0_1_.price, athenastud4_.price)), 2) AS col_2_0_,
sum(coursegrou2_.discount) AS col_3_0_,
sum(coursegrou2_.partner_discount) AS col_4_0_,
sum(coursegrou2_.gross_revenue + coursegrou2_.partner_discount + coursegrou2_.discount) AS col_5_0_,
sum(coursegrou2_.gross_revenue + coursegrou2_.partner_discount + coursegrou2_.discount + coursegrou2_.partner_commission + coursegrou2_.employee_commission + coursegrou2_.teacher_salary + coursegrou2_.location_expense + coursegrou2_.booklet_writer_expense + coursegrou2_.booklet_printing_expense + coursegrou2_.other_expense) AS col_6_0_,
sum(-1 * (coursegrou2_.partner_commission + coursegrou2_.employee_commission + coursegrou2_.teacher_salary + coursegrou2_.location_expense + coursegrou2_.booklet_writer_expense + coursegrou2_.booklet_printing_expense + coursegrou2_.other_expense)) AS col_7_0_,
sum(coursegrou2_.gross_revenue) AS col_8_0_,
sum(coursegrou2_.partner_commission) AS col_9_0_,
sum(coursegrou2_.employee_commission) AS col_10_0_,
sum(coursegrou2_.teacher_salary) AS col_11_0_,
sum(coursegrou2_.location_expense) AS col_12_0_,
sum(coursegrou2_.booklet_writer_expense) AS col_13_0_,
sum(coursegrou2_.booklet_printing_expense) AS col_14_0_,
sum(coursegrou2_.other_expense) AS col_15_0_
FROM athena_course_group athenastud0_
INNER JOIN product athenastud0_1_ ON athenastud0_.scope_id=athenastud0_1_.scope_id
INNER JOIN SCOPE athenastud0_2_ ON athenastud0_.scope_id=athenastud0_2_.scope_id
INNER JOIN course_group_ledger coursegrou1_ ON athenastud0_.scope_id=coursegrou1_.ledger_id
INNER JOIN course_group_ledger_summary coursegrou2_ ON coursegrou1_.ledger_id=coursegrou2_.ledger_id
INNER JOIN ScopeCte scopecte3_ ON (scopecte3_.id=athenastud0_.scope_id)
INNER JOIN athena_course athenastud4_ ON 1=1
INNER JOIN SCOPE athenastud4_1_ ON athenastud4_.scope_id=athenastud4_1_.scope_id
WHERE (EXISTS
(SELECT 1
FROM SCOPE scope5_
WHERE athenastud0_2_.parent_scope_id=scope5_.scope_id
AND athenastud4_.scope_id=scope5_.scope_id))
GROUP BY scopecte3_.root_scope_id
In 5.4.5 and 5.4.6 however, it produces:
SELECT scopecte3_.root_scope_id AS col_0_0_,
sum(athenastud0_1_.sold_units) AS col_1_0_,
round(avg(coalesce(athenastud0_1_.price, athenastud4_.price)), 2) AS col_2_0_,
sum(coursegrou2_.discount) AS col_3_0_,
sum(coursegrou2_.partner_discount) AS col_4_0_,
sum(coursegrou2_.gross_revenue + coursegrou2_.partner_discount + coursegrou2_.discount) AS col_5_0_,
sum(coursegrou2_.gross_revenue + coursegrou2_.partner_discount + coursegrou2_.discount + coursegrou2_.partner_commission + coursegrou2_.employee_commission + coursegrou2_.teacher_salary + coursegrou2_.location_expense + coursegrou2_.booklet_writer_expense + coursegrou2_.booklet_printing_expense + coursegrou2_.other_expense) AS col_6_0_,
sum(-1 * (coursegrou2_.partner_commission + coursegrou2_.employee_commission + coursegrou2_.teacher_salary + coursegrou2_.location_expense + coursegrou2_.booklet_writer_expense + coursegrou2_.booklet_printing_expense + coursegrou2_.other_expense)) AS col_7_0_,
sum(coursegrou2_.gross_revenue) AS col_8_0_,
sum(coursegrou2_.partner_commission) AS col_9_0_,
sum(coursegrou2_.employee_commission) AS col_10_0_,
sum(coursegrou2_.teacher_salary) AS col_11_0_,
sum(coursegrou2_.location_expense) AS col_12_0_,
sum(coursegrou2_.booklet_writer_expense) AS col_13_0_,
sum(coursegrou2_.booklet_printing_expense) AS col_14_0_,
sum(coursegrou2_.other_expense) AS col_15_0_
FROM athena_course_group athenastud0_
INNER JOIN product athenastud0_1_ ON athenastud0_.scope_id=athenastud0_1_.scope_id
INNER JOIN course_group_ledger coursegrou1_ ON athenastud0_.scope_id=coursegrou1_.ledger_id
INNER JOIN course_group_ledger_summary coursegrou2_ ON coursegrou1_.ledger_id=coursegrou2_.ledger_id
INNER JOIN ScopeCte scopecte3_ ON (scopecte3_.id=athenastud0_.scope_id)
INNER JOIN athena_course athenastud4_ ON 1=1
INNER JOIN SCOPE athenastud4_1_ ON athenastud4_.scope_id=athenastud4_1_.scope_id
WHERE (EXISTS
(SELECT 1
FROM SCOPE scope5_
WHERE athenastud0_2_.parent_scope_id=scope5_.scope_id
AND athenastud4_.scope_id=scope5_.scope_id))
GROUP BY scopecte3_.root_scope_id
Which fails with:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "athenastud0_2_"
Position: 2633
https://hibernate.atlassian.net/browse/HHH-12993#icft=HHH-12993 causes an regression where from clauses are invalidly omitted from the query. Despite my concerns raised on the PR, I see this feature has been enabled by default still. I still think it should not be.
The following JPQL query:
SELECT scopeChildren.root.id, SUM(summaryScope.soldUnits), round(AVG(COALESCE(summaryScope.price, asco.price)), 2), SUM(cgls.discount), SUM(cgls.partnerDiscount), SUM(cgls.netRevenue), SUM(cgls.grossProfit), SUM(cgls.totalCosts), SUM(cgls.grossRevenue), SUM(cgls.partnerCommission), SUM(cgls.employeeCommission), SUM(cgls.teacherSalary), SUM(cgls.locationExpense), SUM(cgls.bookletWriterExpense), SUM(cgls.bookletPrintingExpense), SUM(cgls.otherExpense) FROM AthenaStudiesCourseGroup summaryScope JOIN summaryScope.ledger ledger_1 JOIN ledger_1.courseGroupLedgerSummary cgls JOIN ScopeCte scopeChildren ON (scopeChildren.id = summaryScope.id) JOIN AthenaStudiesCourse asco ON (EXISTS (SELECT 1 FROM summaryScope.parent _synth_subquery_0 WHERE asco.id = _synth_subquery_0.id)) GROUP BY scopeChildren.root.idWorks in 5.4.3 and produces the following SQL fragment:
SELECT scopecte3_.root_scope_id AS col_0_0_, sum(athenastud0_1_.sold_units) AS col_1_0_, round(avg(coalesce(athenastud0_1_.price, athenastud4_.price)), 2) AS col_2_0_, sum(coursegrou2_.discount) AS col_3_0_, sum(coursegrou2_.partner_discount) AS col_4_0_, sum(coursegrou2_.gross_revenue + coursegrou2_.partner_discount + coursegrou2_.discount) AS col_5_0_, sum(coursegrou2_.gross_revenue + coursegrou2_.partner_discount + coursegrou2_.discount + coursegrou2_.partner_commission + coursegrou2_.employee_commission + coursegrou2_.teacher_salary + coursegrou2_.location_expense + coursegrou2_.booklet_writer_expense + coursegrou2_.booklet_printing_expense + coursegrou2_.other_expense) AS col_6_0_, sum(-1 * (coursegrou2_.partner_commission + coursegrou2_.employee_commission + coursegrou2_.teacher_salary + coursegrou2_.location_expense + coursegrou2_.booklet_writer_expense + coursegrou2_.booklet_printing_expense + coursegrou2_.other_expense)) AS col_7_0_, sum(coursegrou2_.gross_revenue) AS col_8_0_, sum(coursegrou2_.partner_commission) AS col_9_0_, sum(coursegrou2_.employee_commission) AS col_10_0_, sum(coursegrou2_.teacher_salary) AS col_11_0_, sum(coursegrou2_.location_expense) AS col_12_0_, sum(coursegrou2_.booklet_writer_expense) AS col_13_0_, sum(coursegrou2_.booklet_printing_expense) AS col_14_0_, sum(coursegrou2_.other_expense) AS col_15_0_ FROM athena_course_group athenastud0_ INNER JOIN product athenastud0_1_ ON athenastud0_.scope_id=athenastud0_1_.scope_id INNER JOIN SCOPE athenastud0_2_ ON athenastud0_.scope_id=athenastud0_2_.scope_id INNER JOIN course_group_ledger coursegrou1_ ON athenastud0_.scope_id=coursegrou1_.ledger_id INNER JOIN course_group_ledger_summary coursegrou2_ ON coursegrou1_.ledger_id=coursegrou2_.ledger_id INNER JOIN ScopeCte scopecte3_ ON (scopecte3_.id=athenastud0_.scope_id) INNER JOIN athena_course athenastud4_ ON 1=1 INNER JOIN SCOPE athenastud4_1_ ON athenastud4_.scope_id=athenastud4_1_.scope_id WHERE (EXISTS (SELECT 1 FROM SCOPE scope5_ WHERE athenastud0_2_.parent_scope_id=scope5_.scope_id AND athenastud4_.scope_id=scope5_.scope_id)) GROUP BY scopecte3_.root_scope_idIn 5.4.5 and 5.4.6 however, it produces:
SELECT scopecte3_.root_scope_id AS col_0_0_, sum(athenastud0_1_.sold_units) AS col_1_0_, round(avg(coalesce(athenastud0_1_.price, athenastud4_.price)), 2) AS col_2_0_, sum(coursegrou2_.discount) AS col_3_0_, sum(coursegrou2_.partner_discount) AS col_4_0_, sum(coursegrou2_.gross_revenue + coursegrou2_.partner_discount + coursegrou2_.discount) AS col_5_0_, sum(coursegrou2_.gross_revenue + coursegrou2_.partner_discount + coursegrou2_.discount + coursegrou2_.partner_commission + coursegrou2_.employee_commission + coursegrou2_.teacher_salary + coursegrou2_.location_expense + coursegrou2_.booklet_writer_expense + coursegrou2_.booklet_printing_expense + coursegrou2_.other_expense) AS col_6_0_, sum(-1 * (coursegrou2_.partner_commission + coursegrou2_.employee_commission + coursegrou2_.teacher_salary + coursegrou2_.location_expense + coursegrou2_.booklet_writer_expense + coursegrou2_.booklet_printing_expense + coursegrou2_.other_expense)) AS col_7_0_, sum(coursegrou2_.gross_revenue) AS col_8_0_, sum(coursegrou2_.partner_commission) AS col_9_0_, sum(coursegrou2_.employee_commission) AS col_10_0_, sum(coursegrou2_.teacher_salary) AS col_11_0_, sum(coursegrou2_.location_expense) AS col_12_0_, sum(coursegrou2_.booklet_writer_expense) AS col_13_0_, sum(coursegrou2_.booklet_printing_expense) AS col_14_0_, sum(coursegrou2_.other_expense) AS col_15_0_ FROM athena_course_group athenastud0_ INNER JOIN product athenastud0_1_ ON athenastud0_.scope_id=athenastud0_1_.scope_id INNER JOIN course_group_ledger coursegrou1_ ON athenastud0_.scope_id=coursegrou1_.ledger_id INNER JOIN course_group_ledger_summary coursegrou2_ ON coursegrou1_.ledger_id=coursegrou2_.ledger_id INNER JOIN ScopeCte scopecte3_ ON (scopecte3_.id=athenastud0_.scope_id) INNER JOIN athena_course athenastud4_ ON 1=1 INNER JOIN SCOPE athenastud4_1_ ON athenastud4_.scope_id=athenastud4_1_.scope_id WHERE (EXISTS (SELECT 1 FROM SCOPE scope5_ WHERE athenastud0_2_.parent_scope_id=scope5_.scope_id AND athenastud4_.scope_id=scope5_.scope_id)) GROUP BY scopecte3_.root_scope_idWhich fails with:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "athenastud0_2_" Position: 2633The issue can be worked around by setting:
<property name="hibernate.query.omit_join_of_superclass_tables" value="false"/>