hql generated sql additional feature on aliases

peterpumpkin · **Posted:** Thu Nov 06, 2003 1:28 pm

the hql

select sum(tob.duration), tob.assig.task.id
from tob in class com.twproject.worklog.WorkLog
group by tob.assig.task.id
order by sum(tob.duration)

(1) generated sql:

select sum(tob.duration) as x0_0_, assignem0_.resourcex as x1_0_
from worklog tob, assignement assignem0_
where tob.assig=assignem0_.id
group by assignem0_.resourcex
order by sum(tob.duration) desc

(2) but what I want is:

select sum(tob.duration) as x0_0_, assignem0_.resourcex as x1_0_
from worklog tob, assignement assignem0_
where tob.assig=assignem0_.id
group by assignem0_.resourcex
order by x0_0_ desc

sql server digest (1) anyway, but mysql doesn't; they both like (2). this could be solved by making it possible to set in hql aliases on properties instead of only on objects (table aliases) ?

gavin · **Posted:** Thu Nov 06, 2003 9:02 pm

This was discussed previously and I understand that the outcome was that the MySQL team agreed that this was a bug in MySQL.

jtk · **Joined:** Tue Nov 23, 2004 4:33 pm **Posts:** 6

gavin wrote:

This was discussed previously and I understand that the outcome was that the MySQL team agreed that this was a bug in MySQL.

I don't think the MySQL team has any plans to fix this since they have marked it as 'Not a Bug' http://bugs.mysql.com/bug.php?id=5478

Not being able to sort by aggregate functions is a real pain. The common workaround of ordering by an alias of the aggregate function doesn't work in HQL.

Hibernate 2.1
MySQL 4.1.7

christian · **Posted:** Tue Nov 23, 2004 4:47 pm

The reply on their bug tracking system says:

Instead of it you should use either:

Code:

  select a, b, sum(c)
  from tbl
  group by a, b
  order by sum(c);

It say its still a MySQL bug if what they recommend doesn't work in their software.

jtk · **Joined:** Tue Nov 23, 2004 4:33 pm **Posts:** 6

Update on MySQL Bug #5478

Quote:

[24 Nov 10:15am] Sergei Golubchik

it is a historical MySQL limitation (inability to use aggregate functions in
order by) we plan to lift in in 5.0

jtk · **Joined:** Tue Nov 23, 2004 4:33 pm **Posts:** 6

I added a patch to the JIRA issue tracking system: http://opensource.atlassian.com/project ... se/HB-1331