There are several problems that can come up when size() is used as a select expression.
Using the following code:
{code:java} @Entity(name ="Company") public class Company {
@Id private int id; private String name;
@ManyToMany(cascade = CascadeType.ALL,fetch = FetchType.EAGER) private List<Customer> customers = new ArrayList<>(); }
@Entity(name = "Customer") public class Customer {
@Id private int id; private String name; }
public class CompanyDto {
public int id; public String name; public int sizeCustomer;
public CompanyDto(int id, String name, int sizeCustomer){ this.id = id; this.name = name; this.sizeCustomer = sizeCustomer; } } {code}
1) Results from the following query do not include an {{Company}} with an empty {{customers}} collection:
{code:java} select new org.hibernate.test.hql.size.ManyToManySizeTest$CompanyDto(c.id, c.name, size( c.customers ) ) from Company c group by c.id, c.name order by c.id {code}
because the generated SQL is:
{code:sql} select manytomany0_.id as col_0_0_, manytomany0_.name as col_1_0_, count(customers1_.Company_id) as col_2_0_ from Company manytomany0_ cross join Company_Customer customers1_ where manytomany0_.id=customers1_.Company_id group by manytomany0_.id , manytomany0_.name order by manytomany0_.id {code}
A left outer join should have been used to join Company and Company_Customer tables.
JPA spec, 4.6.17.2.2 Arithmetic Functions, says, "The SIZE function returns an integer value, the number of elements of the collection. If the collection is empty, the SIZE function evaluates to zero."
2) The following query that joins Company.customers returns the wrong size when a collection has more than 1 element:
{code:java} select new org.hibernate.test.hql.size.ManyToManySizeTest$CompanyDto( c.id, c.name, size( c.customers ) ) from Company c inner join c.customers cu group by c.id, c.name order by c.id {code}
because the generated SQL includes both the explicit (inner) and implicit (cross) joins:
{code:sql} select manytomany0_.id as col_0_0_, manytomany0_.name as col_1_0_, count(customers3_.Company_id) as col_2_0_ from Company manytomany0_ inner join Company_Customer customers1_ on manytomany0_.id=customers1_.Company_id inner join Customer manytomany2_ on customers1_.customers_id=manytomany2_.id cross join Company_Customer customers3_ where manytomany0_.id=customers3_.Company_id group by manytomany0_.id , manytomany0_.name order by manytomany0_.id {code}
As a result, the collection size returned by Hibernate is the square of what is expected.
3) Hibernate allows a collection alias to be used as a size() argument for a collection mapped with {{@OneToMany @JoinColumn}}, but fails for other collection association mappings.
For example,
{code:sql} select new org.hibernate.test.hql.size.ManyToManySizeTest$CompanyDto(c.id, c.name, size( cu ) ) from Company c inner join c.customers cu group by c.id, c.name order by c.id {code}
With the mapping from above, the generated SQL is:
{code:sql} select manytomany0_.id as col_0_0_, manytomany0_.name as col_1_0_, count(manytomany2_.Company_id) as col_2_0_ from Company manytomany0_ inner join Company_Customer customers1_ on manytomany0_.id=customers1_.Company_id inner join Customer manytomany2_ on customers1_.customers_id=manytomany2_.id group by manytomany0_.id , manytomany0_.name order by manytomany0_.id {code}
There is no such column, manytomany2_.Company_id.
JPA Spec, says the argument for SIZE should be a collection_valued_path_expression, not an identification_variable.
Should Hibernate support using an identification_variable as an argument to SIZE?
---- Original description:
I'm using the following HQL and it return a correct result :
{code:java} select distinct new net.rhuanrocha.dto.CompanyDto(c.id,c.name,0) from Company c left join c.customers cu where c.name =:name group by c.id, c.name
{code}
But when I use the size function in my HQL the query results is wrong (In my case is returning an empty result). Look this HQL:
{code:java} select distinct new net.rhuanrocha.dto.CompanyDto(c.id,c.name,size(c.customers)) from Company c left join c.customers cu where c.name =:name group by c.id, c.name {code} |
|