DavideD
commented
3 months ago It seems like a bug, I will have to check if it works using Hibernate ORM 6.5. Or maybe you have already tried?
Closed anaconda875 closed 1 month ago
DavideD
commented
3 months ago It seems like a bug, I will have to check if it works using Hibernate ORM 6.5. Or maybe you have already tried?
anaconda875
commented
3 months ago @DavideD thanks for your input. Those are we are using
DavideD
commented
3 months ago have you tried Hibernate Reactive 2.3.1.Final?
anaconda875
commented
3 months ago Let me try hibernate core 6.5 and Hibernate Reactive 2.3.1.Final and come back to you
anaconda875
commented
3 months ago Tried but seems the issues still persist
anaconda875
commented
3 months ago Update: @DavideD This JPQL is translated to SQL correctly:
SELECT id FROM (
SELECT
cc.id AS id,
dense_rank() over (
order by
id asc
) AS ranking
FROM Collection cc
WHERE
UPPER(name) LIKE ?1
AND (
createdBy = ?2
OR EXISTS (
SELECT 1 FROM CollectionAccess ca
WHERE
ca.collection.id = cc.id
AND ca.accessType = : accessType
AND ca.expirationAtUtc <= : expirationAtUtc
)
)
) pr
WHERE
ranking >= :offset
AND ranking <= :untilActually it is just a part of the whole/big query.
The translated:
select
pr1_0.id
from
(select
c1_0.id id,
dense_rank() over(
order by
c1_0.id) ranking
from
collection c1_0
where
upper(c1_0.name) like replace(?, '\\', '\\\\')
and (
c1_0.created_by=?
or exists(select
1 id
from
collection_access ca1_0
where
ca1_0.collection_id=c1_0.id
and ca1_0.access_type=?
and ca1_0.expiration_at_utc<=?)
)) pr1_0
where
pr1_0.ranking>=?
and pr1_0.ranking<=?@DavideD any update on that?###
DavideD
commented
1 month ago I've created this test case for Hibernate Reactive 2.4.2.Final and it seems to work fine.
select
c1_0.id,
c1_0.createdAtUtc,
c1_0.createdBy,
c1_0.lastModifiedAtUtc,
c1_0.lastModifiedBy,
c1_0.name,
v1_0.belongsTo_id,
v1_0.id
from
Collection c1_0
left join
Vocabulary v1_0
on c1_0.id=v1_0.belongsTo_id
where
c1_0.id in (select
pr1_0.id
from
(select
c2_0.id, dense_rank() over(
order by
c2_0.id)
from
Collection c2_0
where
upper(c2_0.name) like $1 escape ''
and (c2_0.createdBy=$2
or exists(select
1
from
CollectionAccess ca1_0
where
ca1_0.collection_id=c2_0.id
and ca1_0.accessType=$3
and ca1_0.expirationAtUtc<=$4))) pr1_0(id, ranking)
where
pr1_0.ranking>=$5
and pr1_0.ranking<=$6)
order by
c1_0.id,
v1_0.id descSo, I think this issues has been solved.
I'm going to close this issue but feel free to comment further if you have any doubts.
Models:
Our goal: Find Collection(s) that:
Since there is/are join fetch here, so Hibernate will do limit/offset in memory. To avoid that, a tweak need to be done using Window Function (see this)
Our JPQL:
This JPQL can run, but did not return anything, and I found out the reason.
This is native SQL generated by Hibernate:
The
where ca1_0.collection_id=ca1_0.idis WRONG. It should bewhere ca1_0.collection_id=c2_0.idor justwhere ca1_0.collection_id=idWe also tried some small tweak and hope that Hibernate would generate correct SQL:But we got an error:
Is it a bug or just an unsupported feature?