JPA Criteria API - query code works when executed separately but not when used as a subquery

Question

I have entity called Issue and entity called UserIssue. UserIssue extends Issue.

@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Where(clause = "DELETED_AT IS NULL")
@Entity(name = "ISSUE")
public class Issue extends VersionedSequenceIdEntity {
... all fields
}

@Where(clause = "DELETED_AT IS NULL")
@Entity(name = "USER_ISSUE")
public class UserIssue extends Issue {

    ...

    @Column(name = "IS_PRIVATE", nullable = false)
    private Boolean isPrivate;

    ...
}

I am doing a subquery like in this post to do some filtering by sublcass attribute. My exact test case is that I create three UserIssues. Two of them have "isPrivate" attribute on false. The third one has isPrivate on true.

When I execute this code that is actually only the subquery then the result contains two "Issues" based on the condition and it is correct

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<UserIssue> issueQuerySimple = cb.createQuery(UserIssue.class);
Root<UserIssue> issueRootSimple = issueQuerySimple.from(UserIssue.class);

issueQuerySimple.select(issueRootSimple).where(cb.isFalse(issueRootSimple.get("isPrivate")));
List<UserIssue> resultListSimple = entityManager.createQuery(issueQuerySimple).getResultList();

But when I build whole query where the code above is used as a subquery then all three "Issues" are returned and there is something wrong. I would expect the code to return again only two "Issues" that have "isPrivate" attribute set on false

CriteriaQuery<Issue> issueQuery = cb.createQuery(Issue.class);
Root<Issue> issueRoot = issueQuery.from(Issue.class);

Subquery<UserIssue> subQuery = issueQuery.subquery(UserIssue.class);
Root<UserIssue> userIssueRoot = subQuery.from(UserIssue.class);

subQuery.select(userIssueRoot).where(cb.isFalse(userIssueRoot.get("isPrivate")));

issueQuery.select(issueRoot).where(cb.exists(subQuery));
List<Issue> resultList = entityManager.createQuery(issueQuery).getResultList();

Here you can see also query from log that is created by hibernate. It seems correct to me

select
        issue0_.id as id2_2_,
        issue0_.deleted_at as deleted_3_2_,
        issue0_.created_when as created_4_2_,
        issue0_.created_by as created_5_2_,
        issue0_.updated_when as updated_6_2_,
        issue0_.version as version7_2_,
        issue0_.application_id as applicat8_2_,
        issue0_.version_id as version_9_2_,
        issue0_.organization_id as organiz10_2_,
        issue0_.severity as severit11_2_,
        issue0_.state as state12_2_,
        issue0_.title as title13_2_,
        issue0_.type_id as type_id18_2_,
        issue0_.updated_by_customer_at as updated14_2_,
        issue0_.assigned_to as assigne15_2_,
        issue0_.description as descrip16_2_,
        issue0_.is_private as is_priv17_2_,
        issue0_.dtype as dtype1_2_ 
    from
        issue issue0_ 
    where
        (
            issue0_.DELETED_AT IS NULL
        ) 
        and (
            exists (
                select
                    userissue1_.id 
                from
                    issue userissue1_ 
                where
                    userissue1_.dtype='UserIssue' 
                    and (
                        userissue1_.DELETED_AT IS NULL
                    ) 
                    and userissue1_.is_private=0
            )
        )

So why filtering based on subclass field does not work and it returns all three instances instead of two? What is wrong or what I can't see?

Many thanks

Answer 1

Replace

issueQuery.select(issueRoot).where(cb.exists(subQuery));

with

issueQuery.select(issueRoot).where(issueRoot.get("id").in(subQuery));

Explanation

• From the sql query we can see that as long as one userissue with is_private=0 exists, it will select all the issues
• That is not the behaviour you wanted. You still have to use the subquery but want to replace the exists with issue0_.id in and this code snippet does that