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:

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]]>Karsten Wutzke2010-12-06T18:50:51-00:00Re: JPQL statement: wrong generated SQL code?https://www.eclipse.org/forums/index.php/mv/msg/201436/644118/#msg_644118
Could you try it also without the NEW, the constructor usage may have something to do with it.
]]>James Sutherland2010-12-09T15:32:47-00:00Re: JPQL statement: wrong generated SQL code?https://www.eclipse.org/forums/index.php/mv/msg/201436/647442/#msg_647442
I filed a bug here: