Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPQL statement: wrong generated SQL code?
JPQL statement: wrong generated SQL code? [message #643405] Mon, 06 December 2010 18:50 Go to next message
Karsten Wutzke is currently offline Karsten Wutzke
Messages: 112
Registered: July 2009
Senior Member
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)
);

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?

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


Hibernate (MySQL and MySQLDialect) generates almost the same code:

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


As you can see, Hibernate generates alternating scores1 and scores2 in the select clause.

Note, that HSQLDB and Hibernate also produce a wrong output (not shown), which isn't relevant here though. Only
Hibernate on MySQL 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|


I also replaced the t0 with t3 at the appropriate places in the EclipseLink generated SQL and it also produces the
above.

Looks like a bug to me with more than one join onto the same entity/table more than once. Another bug is the AVG
function trancating the fractional part.

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

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

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

Karsten

[Updated on: Mon, 06 December 2010 18:55]

Report message to a moderator

Re: JPQL statement: wrong generated SQL code? [message #644118 is a reply to message #643405] Thu, 09 December 2010 15:32 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Very odd, I can't see why EclipseLink would use the wrong alias, please log a bug and vote for it. Also first try it on the latest (2.1) release, it may have been fixed already.

Could you try it also without the NEW, the constructor usage may have something to do with it.


James : Wiki : Book : Blog : Twitter
Re: JPQL statement: wrong generated SQL code? [message #647442 is a reply to message #643405] Thu, 06 January 2011 12:15 Go to previous message
Karsten Wutzke is currently offline Karsten Wutzke
Messages: 112
Registered: July 2009
Senior Member
Yes it's indeed not quite comprehensible. I believe it's not hard to fix either.

I filed a bug here:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=331969

Please vote for it... the sooner this easy one will be fixed. In the meantime I will try without the constructor expression and report back.

Karsten
Previous Topic:Tricky OneToOne mapping
Next Topic:EclipseLink-8034 error
Goto Forum:
  


Current Time: Tue Oct 21 07:18:11 GMT 2014

Powered by FUDForum. Page generated in 0.02350 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software