Order by on aggregate always does a sort even if the sort is not needed.

• Key: DERBY-3276
• Type: Improvement
• Status: Open
• Priority: Major
• Assignee: Unassigned
• Reporter: Manish Khettry
• Component/s: SQL

Description

See DERBY-3231 for details.

A few different variations of queries that do not need a sort are:

select b, max(a) from yy where a=5 group by b order by max(a) asc;
select b, max(a) from yy where a=5 group by b order by max(a) desc;

Also consider

select a, sum(b) from yy group by a having sum(b) = 2 order by sum(b);

Comments

[Kathey Marsden]

This issue is marked as an improvement and a regression. If it is a regression should it be marked as a bug?

[Knut Anders Hatlen]

It looks like the regression box was checked in one of the big bulk updates in the recent revamping of the bug database. I cannot find any traces of this bug being marked as a regression before that change (searched the mail archives for notifications sent on creation/update of this issue), so I'm unchecking the regression box. If this actually is a regression, please update the issue and add a comment about which versions behave as expected, and which don't.