JPA 2 Query Returning Incomplete Results

Question

The following query is only matching the first value found in the select subquery, even though there are match for all values SELECT p FROM Profile p WHERE p.id IN (SELECT u.group FROM User u WHERE u.id = ?1)

The subquery returns a comma separated list like: 1,2,3. The query should return matches for all three subquery select results. Anyone know what could be wrong? Thanks.

Answer 1

IN clause doesn't work this way neither in JPQL nor SQL.

Value inside (..) is not a "comma separated string", it's a list of values. This list can be specified literally as a comma separated string, or it can be produced by subquery, as in your case. That is, condition in your query works as p.id IN ("1,2,3") (rather than p.id IN (1,2,3)), so it doesn't produce the desired result.

So, you can't use power of query languages (JPQL or SQL) to write queries against denormalized schema (your column contains a list of values, therefore it violates 1NF). If you have many-to-many relation between Profiles and Users, express it as many-to-many relation with intermediate join table.

Answer 2

While the IN operator supports comparison against the results of a subquery, what you're doing can't work (and I'm surprised that you even get one result). Before going further, let me quote the JPA 2.0 specification:

4.6.9 In Expressions

The syntax for the use of the comparison operator [NOT] IN in a conditional expression is as follows:

 in_expression ::=
    {state_field_path_expression | type_discriminator} [NOT] IN
        { ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }
in_item ::= literal | single_valued_input_parameter 

The state_field_path_expression must have a string, numeric, date, time, timestamp, or enum value.

The literal and/or input parameter values must be like the same abstract schema type of the state_field_path_expression in type. (See Section 4.12).

The results of the subquery must be like the same abstract schema type of the state_field_path_expression in type. Subqueries are discussed in Section 4.6.16.

Examples:

o.country IN (’UK’, ’US’, ’France’) is true for UK and false for Peru, and is equivalent to the expression (o.country = ’UK’) OR (o.country = ’US’) OR (o.country = ’ France’).

o.country NOT IN (’UK’, ’US’, ’France’) is false for UK and true for Peru, and is equivalent to the expression NOT ((o.country = ’UK’) OR (o.country = ’US’) OR (o.country = ’France’)).

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

If the value of a state_field_path_expression or in_item in an IN or NOT IN expression is NULL or unknown, the value of the expression is unknown.

Note that use of a collection-valued input parameter will mean that a static query cannot be precompiled.

So, first, p.id doesn't match the return type of the subselect (which is actually a "minor" issue).

Second, and this is a major issue and misunderstanding, your query won't result in something like this (using "pseudo code"):

p.id IN (1, 2, 3) 

which is what you'd like - but in

p.id IN (’1,2,3’) 

which obviously is not what you want, and won't work.

My only advice: normarlize your database.