Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: Group by Entity should work as of JPA spec
PostPosted: Fri Feb 17, 2017 12:46 pm 
Newbie

Joined: Fri Feb 17, 2017 12:29 pm
Posts: 3
Having these entities:

Code:
@Entity
public class Category { ... }

@Entity
public class Product {
    @Id long id;
    @ManyToOne Category cat;
    double price;
}


in Hibernate 5.2.6 this query will fail:

Code:
select p.cat, avg(p.price) from Product p group by p.cat


This was reported in 11 years ago with HHH-1615 but was marked as "Improvement" and then abandoned even if the JPA spec says in Section 4.7:

Quote:
Grouping by entity is permitted […]


The issue received 81 votes and causes portability issues. Why nobody's assigned to it yet, after 11 years?


Top
 Profile  
 
 Post subject: Re: Group by Entity should work as of JPA spec
PostPosted: Fri Feb 17, 2017 3:59 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1385
Group by entity works. Check out this User Guide example, and this test case is run several times a day successfully.

You can test the Hibernate test case for yourself and see it working, then compare to your test case and see what's the difference.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Group by Entity should work as of JPA spec
PostPosted: Sat Feb 18, 2017 6:44 am 
Beginner
Beginner

Joined: Sat May 21, 2011 7:40 am
Posts: 20
It will fail because p.cat is fetched. This can happen due to referring to an alias that uses JOIN FETCH or is a query root. In your case, cat has the fetchType EAGER, which is like having a JOIN FETCH. Either change the fetchType to LAZY, or list all "sub-properties" in the group by. I agree that Hibernate could handle this case too and the issue you mentioned is exactly about that, but the JPA spec doesn't specifically test for this case(group by a fetched entity) so it's not a violation per se.


Top
 Profile  
 
 Post subject: Re: Group by Entity should work as of JPA spec
PostPosted: Sun Feb 19, 2017 10:49 am 
Newbie

Joined: Fri Feb 17, 2017 12:29 pm
Posts: 3
Thank you both for the replies. I've got some awkward results testing this: with H2 the test passes with this query:

Code:
select prod.cat_id, avg(prod.price), cat.id, cat.name from Product prod inner join Category cat on prod.cat_id = cat.id group by prod.cat_id


which someway doesn't fail, while with SQL Server the same query fails with:

Quote:
Column 'Category.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Being the spec generic about this, I agree this is not a violation per se… but I really think Hibernate should handle this, as it is counter-intuitive.


Top
 Profile  
 
 Post subject: Re: Group by Entity should work as of JPA spec
PostPosted: Sun Feb 19, 2017 3:20 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1385
Please attach the test case that fails on SQL Server to a new Jira issue. We need to investigate it to see what's the problem.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Group by Entity should work as of JPA spec
PostPosted: Wed Feb 22, 2017 12:56 pm 
Newbie

Joined: Fri Feb 17, 2017 12:29 pm
Posts: 3
Thank you vlad, here's the issue with the testcase: https://hibernate.atlassian.net/browse/HHH-11513


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.