Issue #2478: Multiple MEMBER OF query

Hello,

Having an entity Items that have a collection field called labels, I need to find items that have two specific labels.

@Entity
public class Items {
private List<Label> labels;
}

I can find an item that have one label:
SELECT i FROM Item i WHERE :label MEMBER OF item.labels

I can select items with any of several labels:
SELECT i FROM Item i WHERE :label1 MEMBER OF item.labels OR :label2 MEMBER OF item.labels

But when I search for an item that have two specific labels, the following query gets no items:
SELECT i FROM Item i WHERE :label1 MEMBER OF item.labels AND :label2 MEMBER OF item.labels

Is it a bug or JPQL query is wrong?

Best regards,
Pablo.

This seems to be the result of how JPQL queries are converted into SQL like syntax before executing. MEMBER OF is implemented using JOIN with a new synthetic variable for iteration over the collection. In your query item.label appears twice, but the same synthetic variable for iterating over that collection is used for both occurrences, and that variable cannot match both sides of the AND with the same value.

Possibly we may have to use separate iteration per MEMBER OF as the results as demonstrated by your post seem unacceptable (although JQPL itself has strange behaviour in some known cases due to the conversion to JOIN). However, using separate variables may sometimes make the query more complex and slow unnecessarily (e.g. for the OR query in your post), so any change requires careful planning.

As a quick solution, you may replace AND of MEMBER OF with 2 explicit JOIN variables for iteration over the collection with 2 independent variables.

Thank you for your answer.

So, if it is a JPQL parsing/conversion problem, can I avoid that using the criteria API query?

About your solution:

As a quick solution, you may replace AND of MEMBER OF with 2 explicit JOIN variables for iteration over the collection with 2 independent variables.

Could you show me how?

Best regards,
Pablo.

> So, if it is a JPQL parsing/conversion problem, can I avoid that using the criteria API query?

It would not help because criteria queries are actually converted to JPQL and then processed.

> Could you show me how?

Something as follows is expected to work (has not been tested):

SELECT DISTINCT item
FROM Item item JOIN item.labels label1 JOIN item.labels label2
WHERE label1 = :label1 AND label2 = :label2

Perfect, that works 👍