wood burning stoves 2.0*
The moose likes JDBC and the fly likes HSQLDB and group by Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "HSQLDB and group by" Watch "HSQLDB and group by" New topic
Author

HSQLDB and group by

jay vas
Ranch Hand

Joined: Aug 30, 2005
Posts: 407
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

Joined: Aug 30, 2005
Posts: 407
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

Joined: Dec 20, 2006
Posts: 2497
    
    8

You have to group by all not-aggregated columns (in your case: all columns you selected): ap.id as, ap.aa , ap.aa_property


OCUP UML fundamental and ITIL foundation
youtube channel
jay vas
Ranch Hand

Joined: Aug 30, 2005
Posts: 407
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

Joined: Dec 20, 2006
Posts: 2497
    
    8

It would not be accepted by Oracle either. Maybe MySQL is a bit more forgiving?
jay vas
Ranch Hand

Joined: Aug 30, 2005
Posts: 407
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

Joined: Aug 30, 2005
Posts: 407
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
Marshal

Joined: Mar 22, 2005
Posts: 41599
    
  55
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.


Ping & DNS - my free Android networking tools app
jay vas
Ranch Hand

Joined: Aug 30, 2005
Posts: 407
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

Joined: Mar 24, 2005
Posts: 186
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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: HSQLDB and group by