[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1615) GROUP BY entity does not work

[Sandeep Tamhankar (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1615?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29462 ] 

Sandeep Tamhankar commented on HHH-1615:
----------------------------------------

Agreed.  I couldn't live with this when I ran into it, so I tweaked Hibernate and attached a patch to this bug.  If you need this functionality, try out my patch.

That said, doing this can make for really slow query execution in MySQL, and in the end I stopped doing queries like the examples above in my app and instead do id queries followed by object queries.

select c
from Cat c, ...
where ...
group by c
having ...
order by ...

=>

select c.id
from Cat c, ...
where ...
group by c.id
having ...
order by ...

select c
from Cat c
where c.id in (id1, id2, ...)

This is more work in the application:
   1. If you care about order, you need to keep track of the id query result order so that you can rejigger the results from the object query.
   2. There's a limit on how many ids you can supply in the object query predicate.  So you may need to do multiple object queries and re-order results as you go.

But this is generally quite fast because the queries should be using the primary key index....well, it's not that simple either.  Fields in the order by clause must be in the group by as well (not sure if this is true for MySQL, but it's true for some db's that my app has to work with).  In any case, fewer fields in group by clause means you need smaller indices to support the queries, so overall performance is much better.

> GROUP BY entity does not work
> -----------------------------
>
>                 Key: HHH-1615
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1615
>             Project: Hibernate3
>          Issue Type: Improvement
>          Components: query-hql
>    Affects Versions: 3.2.5
>         Environment: PostgreSQL 8.1.3, Hibernate 3.1.3
>            Reporter: Xavier Bugaud
>            Assignee: Anthony Patricio
>            Priority: Critical
>         Attachments: hibernate-group-by.diff
>
>
> The query : "select cat, count(*) from Cat cat group by cat" does not work.
> ERROR: column "xxxx" must appear in the GROUP BY clause or be used in an aggregate function
> See :
>  - http://forum.hibernate.org/viewtopic.php?t=953716
>  - http://jira.nhibernate.org/browse/NH-528

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira