• 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:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

HSQLDB and group by

 
Ranch Hand
Posts: 407
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ....

 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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....
 
Ranch Hand
Posts: 187
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic