JPA Criteria Path.get("ID") of a Join<A, B> returns ID of B instead ID of A, why?

Question

I'm working with a subquery that aims to find the CustomerItems that does not have some specific values, that is returned by ecoLabelHelper.getEcoProperties(filterCriteria.getCustomer()). The query is currently doing exactly what I want it to do, except for one thing; selects the wrong ID of the join.

I expect the expression itemPropertiesJoin.<Integer>get(ID) to point to the path of the ID of CustomerItem? Am I missing something? The resulting query should be selecting the ID from CustomerItem, like this: SELECT DISTINCT t10.ID, but instead it does takes the ID of item SELECT DISTINCT t11.ID .

Item:

@Entity
@Table(name = "mp_item", indexes = { @Index(columnList = "itemno, supplier_id"), @Index(columnList = "itemGroupIdSupplier, itemGroupSupplier"),
public class Item extends BaseShopEntity implements ItemIntrospection {

    @ElementCollection
    @CollectionTable(name = "mp_item_properties", joinColumns = @JoinColumn(name = "mp_item_id"))
    @BatchFetch(value = BatchFetchType.JOIN)
    private Set<String> itemProperties;

CustomerItem:

@Entity
@Table(name = "mp_customer_item", indexes = { @Index(columnList = "item_id, customer_id", unique = true) })
public class CustomerItem extends BaseShopEntity {

    @ManyToOne(cascade = { CascadeType.MERGE, CascadeType.PERSIST })
    private Item item;

Subquery:

Subquery<Integer> subquery = criteriaQuery.subquery(Integer.class);
Root<CustomerItem> itemProperties = subquery.from(CustomerItem.class);
Join<CustomerItem, Item> itemPropertiesJoin = (Join<CustomerItem, Item>) (Object) itemProperties.fetch(ITEM);
itemPropertiesJoin.on(itemPropertiesJoin.<String>get(ITEM_PROPERTIES).in(ecoLabelHelper.getEcoProperties(filterCriteria.getCustomer())));
subquery.select(itemPropertiesJoin.<Integer>get(ID));
subquery.distinct(true);
Predicate itemIsNotEco = criteriaBuilder.not(criteriaBuilder.in(root.<Integer>get(ID)).value(subquery.select(itemProperties.get(ID))));
conditions.add(criteriaBuilder.and(itemIsNotEco, customerItemIsEco.not()));

Generated query:

NOT (
                t1.ID IN (
                  SELECT 
                    DISTINCT t10.ID 
                  FROM 
                    [mptest].[mptest].[mp_customer_item] t11, 
                    [mptest].[mptest].[mp_item] t10, 
                    [mptest].[mptest].[mp_item_properties] t12 
                  WHERE 
                    (
                      (t12.mp_item_id = t10.ID) 
                      AND (
                        (t10.ID = t11.ITEM_ID) 
                        AND (
                          t12.ITEMPROPERTIES IN (
                              'Z01', 
                              'Z02', 
                              'Z03', 
                              'Z04', 
                              'Z11', 
                              'Z12', 
                              'Z15', 
                              'Z17', 
                              'Z20', 
                              'Z22', 
                              'Z24', 
                              'Z38', 
                              'Z40', 
                              'Z48', 
                              'Z49', 
                              'Z50', 
                              'Z56', 
                              'Z58', 
                              'Z60', 
                              'Z61', 
                              'Z62', 
                              'Z63', 
                              'Z64', 
                              'Z65', 
                              'Z68' 
                          )
                        )
                      )
                    )
                )
              ) 
              AND NOT (
                (t2.ECO = 'true')

Cheers,

Answer 1

I expect the expression itemPropertiesJoin.get(ID) to point to the path of the ID of CustomerItem?

Why should that be the case? If you want the id of CustomerItem, use itemProperties.get(id)