|  HHH-12993 Open 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 prematurely. I still think it should not be. The following JPQL query:
Unable to find source-code formatter for language: jpql. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
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:
|
