Hello,
I’m writing a HQL query similar to this simple example:
Select sum(l.orderedQuantity), p
From OrderLine l
Join l.product p
Group By p
I’m very interested in returning the whole Product entity p (not just a concrete set of columns).
This HQL is transformed to this (simplified) SQL query:
select sum(orderline0_.QtyOrdered) as col_0_0_,
product1_.<column1>, [...], product1_.<columnN>
from C_OrderLine orderline0_
inner join M_Product product1_ on orderline0_.M_Product_ID=product1_.M_Product_ID
group by product1_.M_Product_ID limit ?
Note that M_Product_ID is the primary key column from M_Product table.
This SQL query is properly executed in PostgreSQL because it has a very convenient feature that detects that the primary key is in the GROUP BY clause so it considers it enough to run the group by.
However this behavior is actually invalid from the SQL standard point of view.
For example, if we run this HQL query in Oracle the generated SQL query is exactly the same as for PostgreSQL, but it in this case the execution fails because it requires to have every M_Product column in the GROUP BY clause too:
ORA-00979: not a GROUP BY expression
As a workaround we have rewritten the HQL this way:
Select sum(l.orderedQuantity) as qty, (select p from Product p where p.id = l.product.id) as p
From OrderLine l
Group By l.product.id
This actually generates from one side a working SQL query with the GROUP BY M_Product_ID, and from another side as many simple queries as different products returned by the “group by query” to get the product’s entities.
However IMHO the OracleDialect should detect this scenario and set every M_Product column in the GROUP BY clause to generate a working SQL for Oracle.