Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Stephane Eybert
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • 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
  • 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 ?
 
Mary Chellapa
Ranch Hand
Posts: 93
  • Likes 1
  • Mark post as helpful
  • send pies
  • 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
  • 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
 
Daniel Doboseru
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
  • Quote
  • Report post to moderator
I don't know what happened, but the issue does not show up anymore.
 
Rob Spoor
Sheriff
Pie
Posts: 20531
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • 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
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic