Hibernate JPQL/HQL：聚合函数的错误显示错误的表/实体连接两次的结果（仅使用 HSQLDB）？

Question

我有以下表格：

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

简单的逻辑，一场比赛有两个分数，主场和客场（PK 中的 is_home），它们与名单 ID 相关联。分数表基本上是游戏和名单之间的连接表。我相应地映射了类（这里没有问题）：

这是我接下来要聚合的数据（14 场比赛，28 场比赛得分，[sf] 的 14 场比赛得分，针对 [sa] 的 14 场比赛得分，以及 2 场空的未玩比赛）

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

：得分为 856，对手得分总和为 881。 进行了 12 场比赛。平均得分为 71.33333333333333，平均得分为 71.4166666666666。

我正在使用 JPQL 语句：

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

这应该生成一个团队（名单）所玩的所有比赛的累积视图。 Hibernate（HSQLDB 和 HSQLDialect）生成：

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

如您所见，Hibernate 在 select 子句中正确生成交替的 Score1 和 Score2，但显然仅显示 Score1 的累积值：

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

然后我尝试了 MySQL 和相应的 MySQLDialect，它生成了完全相同的代码，除了 AVG 函数转换为 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

MySQL 上的 Hibernate 然后会产生正确的输出：

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

对我来说看起来像一个错误，但只在 HSQLDB 中，这很奇怪。这里可能有什么问题？ Hibernate 的哪个组件可能会导致该问题？我的意思是，MySQL 和 HSQLDB 代码仅在 AVG 函数上有所不同，其中在 HSQLDB 上生成了强制转换（... as double），但这是否会弄乱结果集，如图所示？

这是一个 SSCCE（JavaSE、Hibernate、HSQLDB、Ant）： http://www.kawoolutions.com/media/jpqlsum-hib-hsqldb -broken.zip

只需从 shell 中输入“ant run”即可。

如果您还有 MySQL，则 xml/persistence.xml 包含 MySQL 的注释代码，以便您可以轻松切换 DBMS。还要查看 DB 目录，其中包含设计 PDF 和 ISO/ANSI DDL 以及 INSERT 脚本。

请注意，我还测试了使用和不使用其方言的 HSQLDB，以及使用和不使用其方言的 MySQL（在 persistence.xml 中设置）。有和没有都显示相同的结果，HSQLDB 显示都是错误的，MySQL 显示都是正确的。

谁能确认这个错误吗？然后我会提交一份错误报告...

Karsten

Answer 1

HSQLDB 中存在一个错误，它会从同一表的两个版本的同一列生成相同的聚合结果。这已在最新的 2.0.1 jar 中修复。