Differentiates results for the same query in sql and hql
I have an app with angular and java and I am trying to get data from a database. So I am trying to get my data in hql (hibernate) but I dont have the same results in hql and sql for the same query.
This is my sql query:
SELECT
co.label,
sol.material_code,
sum(sol.quantity),
sol.level
FROM sales_order so
JOIN sales_order_line sol ON so.root_so_id = sol.root_so_id
JOIN customer cu ON cu.id = so.id_customer
JOIN country co on co.id = cu.id_country
WHERE so.level = 1
AND sol.material_code in ('AR1MA010', 'VJCNS102088')
GROUP BY co.label, so.level;
And this is my hql query:
select
co.label,
sol.materialCode,
sum(sol.quantity),
sol.level
from SalesOrder so
join so.salesOrderLines sol on so.rootSoId = sol.rootSoId
join so.customer cu
join cu.country co
where so.level = 1
and sol.materialCode IN ('AR1MA010', 'VJCNS102088')
group by co.label, sol.level
I am trying to run these queries in the console. So, I have no problem with my code, but I am getting 2 different results.
For the SQL query, I have:
Argentina AR1MA010 16000 2
Brazil VJCNS102088 20 1
Romania VJCNS102088 12 2
and for hql query I have:
Brazil VJCNS102088 20 1
Romania VJCNS102088 1 1
Do you have an idea why?
source to share
The problem arises from join so.salesOrderLines sol on so.rootSoId = sol.rootSoId
.
With so.salesOrderLines
you create a conditional connection, and with "on" you add a second condition. But in sql you have a second condition with "on". So there are two join conditions in hql, while there is only one in sql.
source to share