Hibernate Subqueries.notIn incorrectly excluding results

Question

I'm relatively green when it comes to Hibernate, but I'm faced with debugging a Criteria query that looks like it should work.

The data structure is roughly as follows:

User { Integer id, String type, String email, List<CustomerPolicy> policies, .... }  
CustomerPolicy { List<PolicyTable> tables, ... }  
PolicyTable { List<Insured> insureds, ... }  
Insured { Applicant applicant, ... }  
Applicant { String email, ... }

I am attempting to get a list of emails on all of the Applicants on a User's linked CustomerPolicies, except for those emails that match ones existing in the User table.

Here's the query we have in code, that does not work:


    emails.addAll(User.createCriteria().list() {
       eq("id", id)
       eq("type", "A")
       createAlias("policies", "policy")
       createAlias("policy.tables", "table")
       createAlias("table.insureds", "insured")
       createAlias("insured.applicant", "applicant")
       isNotNull("applicant.email")
       add(Subqueries.propertyNotIn("applicant.email", emailSubquery))
       projections {
           distinct("applicant.email")
       }
    })

    emailSubquery = DetachedCriteria.forClass(User).setProjection(Projections.property("email"))

I have a user with around 4 policies, each with around 3-5 tables, each with at least one insured with an applicant, and roughly half of those applicants have an email that is in the User table (alice@company.com) and the other half have emails that aren't in the User table (foo@foo.com).

With the emailSubquery in place, none of the emails are returning. With the Subquery removed, both return. I have verified that foo@foo.com is absolutely not in the User table.

What is wrong with this query?

Alice, is it possible that you're not defining the emailSubquery prior to the list operation? In your example, it's defined after the list operation. Is it possible that it defined to something else before? — Daniel Woods, Feb 01 '13 at 20:53
The emailSubquery is actually a method call so it is not, strictly speaking, predefined. I just tried assigning it to a variable immediately before emails.addAll and I'm still getting 0 emails added. — Alice Zakhary, Feb 01 '13 at 21:29

score 1 · Accepted Answer · edited May 23 '17 at 12:18

I am assuming that you're using Oracle for this query and that's where the behavior weirdness is coming from.

Essentially, Oracle has an optimizing compiler for its sql execution. The details for "not in" vs "not exists" vs "left join" are well outlined in this article.

To boil it down, the reason why you're not seeing any results using the propertyNotIn method is that the email property on your "user" table contains null objects. Here's an example that exemplifies this behavior for your provided schema.

More appropriately, what you're going to want to do is use Subqueries.notExists method to produce the query result shown here so that you can get results even when there's a null value in that column.

Best wishes, hope this helps.

HT: SQL "select where not in subquery" returns no results

I've run into that irritating null objects issue before, yet forgot to consider it here. I updated the detached criteria to eliminate the nulls as suggested above and that fixed the problem. Thank you! — Alice Zakhary, Feb 03 '13 at 15:20

Hibernate Subqueries.notIn incorrectly excluding results

1 Answers1