Hibernate Criteria 6 projection on nested object weird behaviour

I want to do a projection on an entity's object attribute with Hibernate Criteria 6.

Here are my models :

    @Entity
    public class MyAuthor {
        private String id;
        private String name;
        private int nbBooks;
        private Date birthDate;
        @Embedded
        private MyAddress address;
    }

    @Embeddable
    public class MyAddress {
        private String street;
        private MyCity city;
    }

    @Entity
    public class MyCity {
       String id;
       String name;
    }

Here is my unit test :

    // We populate data with CITY_1 & CITY_2, ADDR_1 on CITY_1, ADDR_2 on CITY_1 and ADDR_3 on CITY_2 with @Before

    CriteriaBuilder criteriaBuilder = sessionFactory.getCurrentSession().getCriteriaBuilder();
    CriteriaQuery<MyCity> query = criteriaBuilder.createQuery(MyCity.class);
    Root<MyAuthor> root = query.from(MyAuthor.class);
    Join<MyAuthor, MyAddress> from = root.join("address", JoinType.LEFT);
    
    // produce this request : select c1_0.id,c1_0.name from MyAuthor ma1_0 left join MyCity c1_0 on c1_0.id=ma1_0.city_id where c1_0.name is not null
    query.select(from.get("city"))
        .where(criteriaBuilder.isNotNull(from.get("city").get("name")));
        
    // produce the same request : select c1_0.id,c1_0.name from MyAuthor ma1_0 left join MyCity c1_0 on c1_0.id=ma1_0.city_id where c1_0.name is not null
    // query.multiselect(from.get("city").get("id"), from.get("city").get("name"))
    // .where(criteriaBuilder.isNotNull(from.get("city").get("name")));


    // with the query.select(), there are 2 results, with the multiselect there are 3.
    List<MyCity> cities = sessionFactory.getCurrentSession().createQuery(query).getResultList();
    
    assertEquals(3, cities.size());

I do not understand how the generated SQL is the same BUT the length of result is different.

Any ideas ? Many thanks.

While the query that is being generated is the same, the actual processing of the result is different.

select c1_0.id,c1_0.name 
from MyAuthor ma1_0 
left join MyCity c1_0 on c1_0.id=ma1_0.city_id 
where c1_0.name is not null

If you would execute this query directly with your DB tooling or a DB tool like DBWeaver you probably would get 3 rows of data. (As you stated you had 9 authors from which 3 had a city).

Of these 3 rows of data, 2 will be the same. As the authors probably live in the same city.

Now when utilizing select what you get returned is a list of unique MyCity entities. Which in this case are 2 actual values. When processing the result HIbernate will map the results to a MyCity but before doing that it will check if it already has a MyCity with that exact id in his cache.

When utilizing multiselect it will return the rows from the result as is, as a list of tuples. Basically a glorified Object[] and thus it will contain all 3 rows even the duplicated data.

From the Javadoc of multiselect (emphasize is mine)

If the type of the criteria query is CriteriaQuery<X> for some user-defined class X (i.e., a criteria query object created by passing a X class argument to the createQuery method), the arguments to the multiselect method will be passed to the X constructor and an instance of type X will be returned for each row.