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:

Environment

  • JBoss EAP 7.1.6

  • SQLServer

Status

Assignee

Unassigned

Reporter

Rhuan Henrique Silva

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

Priority

Major
Configure