You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Left join in JPQL translated into inner join in SQL if the same reference attribute used in join and in where independently. JPQL:
select n from Note n left join n.user u
where n.user is null or n.user.id = :user_id
order by u.name desc
Actual SQL:
SELECTt1.ID, t1.TEXT, t1.USER_IDFROM USER_ t0, NOTE t1
WHERE (((t1.USER_ID IS NULL) OR (t1.USER_ID= ?)) AND (t0.ID=t1.USER_ID))
ORDER BYt0.NAMEDESC
Expected SQL:
SELECTt1.ID, t1.TEXT, t1.USER_IDFROM NOTE t1 LEFT OUTER JOIN USER_ t0 ON (t0.ID=t1.USER_ID)
WHERE ((t1.USER_ID IS NULL) OR (t1.USER_ID= ?))
ORDER BYt0.NAMEDESC
or
SELECTt1.ID, t1.TEXT, t1.USER_IDFROM NOTE t1 LEFT OUTER JOIN USER_ t0 ON (t0.ID=t1.USER_ID)
WHERE ((t1.USER_ID IS NULL) OR (t0.ID= ?))
ORDER BYt0.NAMEDESC
Problem
Entities without specified user are absent in result list because of this behaviour but at the same time they are present if the same order by or where are used separately (please, see "Additional context")
To Reproduce
Run tests in example project: joinNarrowingBug.zip Actual Result:
test JoinTranslationTest#leftJoinLost will fail because SQL generated as described above Expected result:
test JoinTranslationTest#leftJoinLost will pass
Additional context
SQL generated correctly when the same where and order by used separately from each other (see JoinTranslationTest#joinTranslatedCorrectly in attached project):
1.
select n from Note n
where n.user is null or n.user.id = :user_id
SELECT ID, TEXT, USER_ID FROM NOTE
WHERE ((USER_ID IS NULL) OR (USER_ID = ?))
select n from Note n left join n.user u
order by u.name desc
SELECTt1.ID, t1.TEXT, t1.USER_IDFROM NOTE t1 LEFT OUTER JOIN USER_ t0 ON (t0.ID=t1.USER_ID)
ORDER BYt0.NAMEDESC
The text was updated successfully, but these errors were encountered:
Description:
Left join in JPQL translated into inner join in SQL if the same reference attribute used in
join
and inwhere
independently.JPQL:
Actual SQL:
Expected SQL:
or
Problem
Entities without specified user are absent in result list because of this behaviour but at the same time they are present if the same
order by
orwhere
are used separately (please, see "Additional context")To Reproduce
Run tests in example project: joinNarrowingBug.zip
Actual Result:
test
JoinTranslationTest#leftJoinLost
will fail because SQL generated as described aboveExpected result:
test
JoinTranslationTest#leftJoinLost
will passAdditional context
SQL generated correctly when the same
where
andorder by
used separately from each other (seeJoinTranslationTest#joinTranslatedCorrectly
in attached project):1.
The text was updated successfully, but these errors were encountered: