Criteria API correlated subquery with outer join generates incorrect SQL

Description

I have entity types Task, Patient, and Site.

There is an optional @ManyToOne from Task to Patient, and a @ManyToOne from Patient to Site.

I want to query for Task's and restrict to those which either have no patient, or for whom the patient's Site is within a certain set of valid sites.

To add this restriction we build a subquery like this:

Here is the SQL that gets generated:

Note that the first outer join in the subquery from Task to Patient is omitted. This changes the logic, because now the patient1_.id is null clause can never be true.

As a result, this query fails to match tasks that have no associated patient.

Environment

None

Activity

Show:
Nathan Xu
August 27, 2020, 8:04 PM

I finalized the PR to solve the issue. See the PR description for the approach. Another good quality bug reporting!

Archie Cobbs
August 27, 2020, 8:29 PM

Awesome, thanks!

Archie Cobbs
August 27, 2020, 10:04 PM

FYI, got another new one for you. It’s possible this is already fixed by your recent changes, but I don’t have an easy way to tell that.

Thanks.

Nathan Xu
August 27, 2020, 10:29 PM

I will investigate your new ticket tomorrow. Thanks for the high-quality tickets.

Nathan Xu
August 28, 2020, 8:58 PM

Just as we anticipated, both tickets share the same root cause. Now the PR includes testing case for as well.

Assignee

Nathan Xu

Reporter

Archie Cobbs

Fix versions

Labels

None

backPortable

Backport?

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure