[OPENJPA-1715] OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause

• Type: Bug
• Status: Reopened
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.0.0, 2.0.1, 2.0.2
• Fix Version/s: 2.1.0
• Component/s: sql
• Environment: OpenJAP 2.0; WebSphere 6.1; Oracle 9i
• Assignee: Catalina Wei
• Reporter: Azuo Lee
• Created: 01/Jul/10 10:22
• Updated: 27/Jun/11 06:11

Description

For the following JPQL (According to JPA sepcification v2.0, section 4.9, it is legal to use result variables in the order by clause):

select v.id r0, sum(_v0.score) r1 from Stall _v left join v.scores v0 where v.deleted = :p0 and v.market = :p1 group by v.id order by _r1 desc, _r0

but OpenJPA generates a wrong SQL as following:

SELECT t0.id AS c0, SUM(t1.score) AS _r1 AS c1 FROM stalls t0, scores t1 WHERE (t0.deleted = ? AND t0.market = ? AND 1 = 1) AND t0.id = t1.stall GROUP BY t0.id ORDER BY _r1 DESC, t0.id ASC

The second result item in the select clause has 2 aliases specified: "SUM(t1.score) AS _r1 AS c1", which is obviously not acceptable by the underlining database.

Additional question:
How can i order NULL values, like the behavior achieved by using Oracle "ORDER BY SUM(t1.score) DESC NULLS LAST", by using JPQL?