File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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 Android Security Essentials Live Lessons this week in the Android 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: 2491
    
    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: 2491
    
    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: 41155
    
  45
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: HSQLDB and group by
 
Similar Threads
More than SessionFactory in single hibernate.cfg.xml?
Hibernate JPQL/HQL: aggregate functions show results of wrong table/entity joined twice
"JBoss in Action" book question.
which DBMS to learn ??
Not in aggregate function or group by clause - HSQLDB doesn't like the ORDER BY clause