Hibernate Community • View topic - Group by Entity should work as of JPA spec

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Group by Entity should work as of JPA spec

Page 1 of 1

[ 6 posts ]

Previous topic | Next topic

Author

Message

heruan

Post subject: Group by Entity should work as of JPA spec

Posted: 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

vlad

Post subject: Re: Group by Entity should work as of JPA spec

Posted: Fri Feb 17, 2017 3:59 pm

Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania

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.

Top

christianbeikov

Post subject: Re: Group by Entity should work as of JPA spec

Posted: Sat Feb 18, 2017 6:44 am

Beginner

Joined: Sat May 21, 2011 7:40 am
Posts: 22

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

heruan

Post subject: Re: Group by Entity should work as of JPA spec

Posted: 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

vlad

Post subject: Re: Group by Entity should work as of JPA spec

Posted: Sun Feb 19, 2017 3:20 pm

Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania

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.

Top

heruan

Post subject: Re: Group by Entity should work as of JPA spec

Posted: 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

Page 1 of 1

[ 6 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

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