Can we consider changing the criteria API in 3.3 so that DISTINCT_ROOT_ENTITY is the default transformer?
10:28
I remember that detecting "fetch" in HQL and applying this transformer is already on the list, right?
10:29
or introduce config options for both APIs, so users can switch the default transformer
10:30
also, verify this please: http://www.hibernate.org/117.html#A12
Steve
2:31
we should try to detect if any fetches are present
2:31
otherwise DISTINCT_ROOT_ENTITY presents unecessary overhead
2:31
i dont recall such a discussion regarding that feature in HQL
2:32
another option is sorta like i'm planning in other areas: fallback to subselect fetching
Improvement
Open
Critical
Unify join fetch behavior for HQL and Criteria APIs
I need this improvement especially when I use EntityManager to do query rather than Criteria or HQL.
In fact, the HibernateEntityManager provides no way to force returning distinct top level pojo objects when there is an One-to-Many left outer join clause.
One of the workaround I learned from http://www.hibernate.org/117.html#A12
is to add a DISTINCT keyword after SELECT.
"Yes, in other cases an HQL distinct will translate straight into a SQL DISTINCT. Not in this case: you can not filter out duplicates at the SQL level, the very nature of a product/join forbids this - you want the duplicates or you don't get all the data you need."
But in two cases, this workaround failed to work:
1. When the resultset should be paginated, I.E., setFirstResult() and setMaxResults() were used.
2. When the resultset has LONGVAR field, like ntext, image in SQLServer. Database will generate a error like : ntext datatype could not be distinct compared."
And these two case should happen pretty frequently.
The conclusion : despite of using "setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)" in Hibernate HQL queries and criteried queries, HibernateEntityManager has no way to eliminate the duplication of top level pojo objects when there is an One-to-Many left outer join clause.
We need this improvments indeed.
Thanks in advance.