• 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:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

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: 22586
122
Eclipse IDE Spring VI Editor Chrome 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
 
WHAT is your favorite color? Blue, no yellow, ahhhhhhh! Tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic