File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

HSQLDB and group by

 
jay vas
Ranch Hand
Posts: 407
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys... I have a hibernate query, it works fine in mysql but not in hsqldb.

Here it is.



What gives ? Runs like a dream in MySQL ?


Im using the hibernate auto function.

 
jay vas
Ranch Hand
Posts: 407
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Similar issue in derby : The error was

For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions.

 
Jan Cumps
Bartender
Posts: 2576
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have to group by all not-aggregated columns (in your case: all columns you selected): ap.id as, ap.aa , ap.aa_property

 
jay vas
Ranch Hand
Posts: 407
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But I only wanted to group by one of the columns ! ?

This is very strange --- since the query is valid in MySQL, but not in HSQLDB, are you sure it is a problem with the query ?
 
Jan Cumps
Bartender
Posts: 2576
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It would not be accepted by Oracle either. Maybe MySQL is a bit more forgiving?
 
jay vas
Ranch Hand
Posts: 407
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What exactly do you mean non-aggreagated columns ? Is there a way I can make all my columns "aggregated" so that there are no "non-aggregated" columns ?

Thanks,

Jay
 
jay vas
Ranch Hand
Posts: 407
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another interesting one :



Returns 0 results in a table with over 400 entries in hSQLDB.

Same query in Mysql works fine.

Again : Any ideas ? Am I just missing something ??? I thought SQL was SQL anyway you sliced it !?

ANY input whatsoever would be very much appreciated ....

 
Ulf Dittmer
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I thought SQL was SQL anyway you sliced it !?

What any particular DB accepts is not necessarily standardized SQL. *Every* DB has its own dialect, and will happily accept stuff that no other DB accepts. It's the responsibility of the developer to make sure no non-standard SQL is used, if she wants the queries to be portable across DBs.
 
jay vas
Ranch Hand
Posts: 407
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welll part of the problem was that my version of hsqldb was old.


that fixed some issues.\\\\\


nevertheless... its clear to me that complex aliasing and queries are better implemented in either oracle or mysql.\\\

at least when hibernate comes into the picture....
 
Emanuel Kadziela
Ranch Hand
Posts: 187
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PostgreSQL also does not accept such "loose" syntax.

I am, in fact, surprised at the way MySQL implemented this. If you don't aggregate over a column which you don't group by, which value do you want/expect? MySQL, it seems, just returns the first value encountered, or a random value in the set perhaps? Either way it's a little misleading. Here is an example:

Table X:

Height | Weight | Shoe_Size
1 | 2 | 3
1 | 3 | 4
1 | 4 | 5
...


select Height, avg(Weight), Shoe_Size from X group by Height; => 1, 3, ??? (is it 3, 4, or 5) and more importantly what should it be? I argue it should just throw an error, like HSQLDB, PostgreSQL and a few others do.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic