Bug 331969

Description Karsten Wutzke 2010-12-06 19:24:14 EST

Up front, this bug can be viewed better here (code formatting!:

http://www.eclipse.org/forums/index.php?t=msg&th=201436

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

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, which are associated with a roster ID. The Scores table is basically a join table between games and rosters.

I then want to add up all games per roster 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. EclipseLink (HSQLDB) generates:

SELECT
  SUM(t0.final_score),
  SUM(t0.final_score),
  AVG(t0.final_score),
  AVG(t0.final_score),
  MIN(t0.final_score),
  MIN(t0.final_score),
  MAX(t0.final_score),
  MAX(t0.final_score)
FROM Scores t3, Rosters t2, Games t1, Scores t0
WHERE ((((t1.id <> ?) AND (t0.roster_id = ?)) AND (t3.roster_id <> ?)) AND (((t0.game_id = t1.id) AND (t3.game_id = t1.id)) AND (t2.id = t0.roster_id)))
GROUP BY t2.id

Result set:

|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.0|              71.0|                50|                50|                89|                89|

As you can see, the columns for scores for and against (sf and sa in the JPQL) have exactly the same values, but that shouldn't be the case. The generated code appears to be wrong, because it always uses t0, which can't work. So the code generator must be buggy. Can anyone confirm this?

Another bug that seems to be present is with the AVG function: 856 points / 12 games = 71.333333333333333... not 71.0. Cast missing?

This bug was marked as blocker, because it effectively renders EclipseLink useless for what I'm doing.

Tested with EclipseLink nightly from 2010-12-04 (r8626).

Here's an SSCCE (JavaSE, HSQLDB, Ant):
http://www.kawoolutions.com/media/jpqlsum-el-hsqldb-broken.zip

Just type "ant run" from a shell. Also see the db dir for a visual DB design and ISO/ANSI SQL scripts.