Hibernate JPQL/HQL: bug with aggregate functions showing results of wrong table/entity joined twice (using HSQLDB only)?

I have the following tables:

CREATE TABLE Rosters
(
  id INTEGER NOT NULL,
  club_abbr VARCHAR(10) NOT NULL,
  ordinal_nbr SMALLINT,
  PRIMARY KEY (id)
);

CREATE TABLE Games
(
  id INTEGER NOT NULL,
  scheduled_tipoff DATETIME NOT NULL,
  PRIMARY KEY (id)
);

-- join table
CREATE TABLE Scores
(
  game_id INTEGER NOT NULL,
  is_home BOOLEAN NOT NULL,
  roster_id INTEGER NOT NULL,
  final_score SMALLINT DEFAULT NULL NULL,
  PRIMARY KEY (game_id, is_home),
  FOREIGN KEY (game_id) REFERENCES Games (id),
  FOREIGN KEY (roster_id) REFERENCES Rosters (id)
);

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):

|sf.roster.id|ga.id|sf.finalScore|sa.finalScore|
|------------|-----|-------------|-------------|
|           1|    3|         null|         null|
|           1|    5|           71|           93|
|           1|   11|           77|           80|
|           1|   13|           65|           71|
|           1|   16|           88|           90|
|           1|   22|           58|           51|
|           1|   23|           71|           75|
|           1|   30|         null|         null|
|           1|   32|           89|           86|
|           1|   40|           62|           71|
|           1|   42|           64|           60|
|           1|   46|           73|          101|
|           1|   48|           50|           43|
|           1|   51|           88|           60|

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:

SELECT NEW tld.jpqlsum.view.StringLine(
    SUM(sf.finalScore)
  , SUM(sa.finalScore)
  , AVG(sf.finalScore)
  , AVG(sa.finalScore)
  , MIN(sf.finalScore)
  , MIN(sa.finalScore)
  , MAX(sf.finalScore)
  , MAX(sa.finalScore)
  )
FROM Game ga
  JOIN ga.scores sf
  JOIN ga.scores sa
WHERE ga.id <> 57 AND sf.roster.id = 1 AND sa.roster.id <> 1
GROUP BY sf.roster.id

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

select
  sum(scores1_.final_score) as col_0_0_,
  sum(scores2_.final_score) as col_1_0_,
  avg(cast(scores1_.final_score as double)) as col_2_0_,
  avg(cast(scores2_.final_score as double)) as col_3_0_,
  min(scores1_.final_score) as col_4_0_,
  min(scores2_.final_score) as col_5_0_,
  max(scores1_.final_score) as col_6_0_,
  max(scores2_.final_score) as col_7_0_
from
  Games game0_
inner join
  Scores scores1_
      on game0_.id=scores1_.game_id
inner join
  Scores scores2_
      on game0_.id=scores2_.game_id
where
  game0_.id<>57
  and scores1_.roster_id=1
  and scores2_.roster_id<>1
group by
  scores1_.roster_id

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

|SUM(sf.finalScore)|SUM(sa.finalScore)|AVG(sf.finalScore)|AVG(sa.finalScore)|MIN(sf.finalScore)|MIN(sa.finalScore)|MAX(sf.finalScore)|MAX(sa.finalScore)|
|------------------|------------------|------------------|------------------|------------------|------------------|------------------|------------------|
|               856|      开发者_运维问答         856| 71.33333333333333| 71.33333333333333|                50|                50|                89|                89|

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

select
  sum(scores1_.final_score) as col_0_0_,
  sum(scores2_.final_score) as col_1_0_,
  avg(scores1_.final_score) as col_2_0_,
  avg(scores2_.final_score) as col_3_0_,
  min(scores1_.final_score) as col_4_0_,
  min(scores2_.final_score) as col_5_0_,
  max(scores1_.final_score) as col_6_0_,
  max(scores2_.final_score) as col_7_0_
from
  Games game0_
inner join
  Scores scores1_
      on game0_.id=scores1_.game_id
inner join
  Scores scores2_
      on game0_.id=scores2_.game_id
where
  game0_.id<>57
  and scores1_.roster_id=1
  and scores2_.roster_id<>1
group by
  scores1_.roster_id

Hibernate on MySQL then produces the correct output:

|SUM(sf.finalScore)|SUM(sa.finalScore)|AVG(sf.finalScore)|AVG(sa.finalScore)|MIN(sf.finalScore)|MIN(sa.finalScore)|MAX(sf.finalScore)|MAX(sa.finalScore)|
|------------------|------------------|------------------|------------------|------------------|------------------|------------------|------------------|
|               856|               881|           71.3333|           73.4167|                50|                43|                89|               101|

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 (JavaSE, Hibernate, HSQLDB, Ant): http://www.kawoolutions.com/media/jpqlsum-hib-hsqldb-broken.zip

Just type "ant run" from a shell.

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

There was a bug in HSQLDB which produced the same aggregate result for the same column from two versions of the same table. This has been fixed in the latest 2.0.1 jars.