This week's book giveaway is in the Cloud/Virtualization forum.
We're giving away four copies of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds and have James Denton on-line!
See this thread for details.
Win a copy of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds this week in the Cloud/Virtualization forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

Hibernate JPQL/HQL: aggregate functions show results of wrong table/entity joined twice  RSS feed

 
Ranch Hand
Posts: 106
Hibernate MySQL Database Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have the following tables:



Simple logic, a game has two scores, home and away (by is_home in PK), which are associated with a roster ID. The Scores table is basically a join table between games and rosters. I mapped the classes accordingly (no problems here):

Here's the data I want to aggregate next (14 games, 28 scores, 14 scores for [sf], 14 scores against [sa], and 2 null unplayed games):



Sum of the scores for is 856, sum of the scores against is 881. 12 played games. Average score for is 71.33333333333333, average score against is 71.4166666666666.

I'm using the JPQL statement:



This should yield a cumulated view of all played games by a team (roster). Hibernate (HSQLDB and HSQLDialect) generates:



As you can see, Hibernate correctly generates alternating scores1 and scores2 in the select clause, but obviously shows the cumulated values for scores1 only:



I then tried MySQL and the appropriate MySQLDialect, which generates exactly the same code, except for the AVG function casts to double:



Hibernate on MySQL then produces the correct output:



Looks like a bug to me, but only in HSQLDB, which is strange. What could be the problem here? Which component of Hibernate could cause the problem? I mean the MySQL and HSQLDB code only differ for the AVG function where a cast(... as double) is generated on HSQLDB, but does that mess up the result set as shown?

Here's an SSCCE:
http://www.kawoolutions.com/media/jpqlsum-hib-hsqldb-broken.zip

If you also have MySQL, xml/persistence.xml contains outcommented code for MySQL so you can switch DBMSs easily. Also look into the DB dir, which contains a design PDF and ISO/ANSI DDL and INSERT scripts.

Note, that I've also tested HSQLDB with and without its dialect as well as MySQL with and without its dialect (set in persistence.xml). Both with and without show the same results, HSQLDB shows both wrong and MySQL shows both correctly.

Can anyone confirm this bug? I'll file a bug report then...

Karsten
 
Karsten Wutzke
Ranch Hand
Posts: 106
Hibernate MySQL Database Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Looks like a bug in HSQLDB 2.0. See

http://stackoverflow.com/questions/4376703/hibernate-jpql-hql-bug-with-aggregate-functions-showing-results-of-wrong-table-e

Karsten

 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!