size() does not work properly as select expression
Description
There are several problems that can come up when size() is used as a select expression.
Using the following code:
1) Results from the following query do not include an Company with an empty customers collection:
because the generated SQL is:
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:
because the generated SQL includes both the explicit (inner) and implicit (cross) joins:
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,
With the mapping from above, the generated SQL is:
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 :
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:
There are several problems that can come up when size() is used as a select expression.
Using the following code:
1) Results from the following query do not include an
Companywith an emptycustomerscollection:because the generated SQL is:
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:
because the generated SQL includes both the explicit (inner) and implicit (cross) joins:
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,
With the mapping from above, the generated SQL is:
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 :
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: