• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Not in aggregate function or group by clause - HSQLDB doesn't like the ORDER BY clause

 
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have an Hibernate application with the mapping:

<property name="firstname" type="string">
<column name="firstname" not-null="true" />
</property>
<property name="lastname" type="string">
<column name="lastname" not-null="true" />
</property>

I get an exception in HSQLDB only and not in MySQL.

Here is the exception:

Caused by: java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@4c8167 in statement [select count(*) as y0_ from admin this_ order by this_.firstname asc, this_.lastname asc]

Here is the sql statement:

select count(*) as y0_ from admin this_ order by this_.firstname asc, this_.lastname asc

Any clue ?
 
Stephane Eybert
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Some forum post says HSQLDB requires a String in the ORDER BY clause.

Fine. Mine is a String.

So what's wrong then ?
 
Ranch Hand
Posts: 93
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
you are using count(*) ... i wonder why are you using it with order by ??? i think you want a group by there
 
Stephane Eybert
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Mary,

Thanks for that comment. It's a good question.

But I don't have the answer, it's Hibernate that generates this statement.

I'm having a pagination method and I need to have a count on the number of rows. I guess that is where the statement is coming from.



And this pagination method is called by my dao method.



Thanks again !

Stephane
 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
To solve this SQL error, include in the GROUP BY clause, all the columns (at least, this is how I solved it), in order to tell the parser how to order your records.

For instance if you have the table: Student (name, age, university ....) you should have an ORDER BY like this: "ORDER BY name, university, age" or at least "name, university", so he can order by name, then by university, then by age etc. Try this!
 
Stephane Eybert
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Daniel,

Thanks for that but I don't see any GROUP BY statement in my source code. Do you see any ?

Stephane
 
Stephane Eybert
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here is more on the error message:

Caused by: java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@1504a84 in statement [select count(*) as y0_ from admin this_ where (lower(this_.firstname) like ? or lower(this_.lastname) like ? or lower(this_.login) like ? or lower(this_.email) like ?) order by this_.firstname asc, this_.lastname asc]
 
Stephane Eybert
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't know what happened, but the issue does not show up anymore.
 
Sheriff
Posts: 22862
132
Eclipse IDE Spring TypeScript Quarkus Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I hate it when that happens. You don't know what solved the issue, so it may come back at some time. I'd rather have the error occur a little longer until I find the solution, because then I know for sure the problem won't reoccur. There's not much I hate more than sleeping bugs.
 
Stephane Eybert
Ranch Hand
Posts: 34
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Rob,

I know, I feel the same, that's why I'm right now still looking at it, trying to dig around..

Stephane
 
reply
    Bookmark Topic Watch Topic
  • New Topic