# Subquery returning a Set or a List not working as intended

**URL:** <https://discourse.hibernate.org/t/subquery-returning-a-set-or-a-list-not-working-as-intended/6169>\
**Category:** Hibernate ORM\
**Created:** [March 23, 2022, 4:34pm UTC](https://discourse.hibernate.org/t/subquery-returning-a-set-or-a-list-not-working-as-intended/6169 "2022-03-23T16:34:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ArnaudLec](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/arnaudlec/32/1768_2.png) [@ArnaudLec](https://discourse.hibernate.org/u/ArnaudLec)\
**Post date:** [March 23, 2022, 4:34pm UTC](https://discourse.hibernate.org/t/subquery-returning-a-set-or-a-list-not-working-as-intended/6169/1 "2022-03-23T16:34:10Z")

</div>

I have a problem with a query using the CriteriaBuilder in spring-data-jpa 2.6.2 with Hibernate 5.6.5.

I have 2 entities :

```auto
@Entity
public class Person {
    @Id
    private String id;
}

@Entity
public class TemporaryDataSetAttr {
    @Id
    private String id;

    @Column
    private String correlationId;

    @ElementCollection
    @CollectionTable(indexes = @Index(name = "IDX_TMP_DATA_ID_SET_VALUES", columnList = "temporary_data_set_attr_id"))
    private Set<String> strValues = new HashSet<>();
}

```

And here is my query :

```java
(root, query, cb) -> {
    Subquery<Set<String>> subquery = query.subquery(TemporaryDataSetAttr_.strValues.getJavaType());
    Root<TemporaryDataSetAttr> subRoot = subquery.from(TemporaryDataSetAttr.class);

    subquery.select(subRoot.get(TemporaryDataSetAttr_.strValues))
        .where(cb.equal(subRoot.get(TemporaryDataSetAttr_.correlationId), correlationId));

    return cb.not(root.get(Person_.id).in(subquery));
}

```

The produced SQL query is :

```auto
select 
    person0_.id as id1_0_,
    person0_.name as name2_0_ 
from person person0_ 
where 
    person0_.id not in (
        select . 
        from temporary_data_list_attr temporaryd1_ 
        cross join temporary_data_list_attr_str_values strvalues2_
        where temporaryd1_.id=strvalues2_.temporary_data_list_attr_id and temporaryd1_.correlation_id=?
    )

```

The select clause in the subquery is missing the column name, only the dot separating the table name from the column name is present.  
Using `EntityManager#createNativeQuery` and adding the table and column names to the generated query through the CriteriaBuilder, i have the expected result.  
I tried with an ListAttribute instead of the SetAttribute but it is the same result.

This looks like an Hibernate bug.

I pushed a reproducer project on my github :  
[https://github.com/ArnaudLec/debug-subquery-setattribute](https://github.com/ArnaudLec/debug-subquery-setattribute)

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [March 25, 2022, 10:32pm UTC](https://discourse.hibernate.org/t/subquery-returning-a-set-or-a-list-not-working-as-intended/6169/2 "2022-03-25T22:32:09Z")

</div>

Thanks for the reproducer, but I think the “bug” is that you don’t get a good exception. In JPQL it is illegal to use a plural attribute path anywhere but in the FROM clause or a collection predicate, so some kind of error is expected. What you want to do requires that you join the plural attribute and select the join.

---

<div class="post-metadata">

**Author:** ![ArnaudLec](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/arnaudlec/32/1768_2.png) [@ArnaudLec](https://discourse.hibernate.org/u/ArnaudLec)\
**Post date:** [April 19, 2022, 7:31am UTC](https://discourse.hibernate.org/t/subquery-returning-a-set-or-a-list-not-working-as-intended/6169/3 "2022-04-19T07:31:16Z")

</div>

I could not find how to write the request using joins so i used a SingularAttribute instead.

Thank you for your answer.