Sorting on COUNT with OR and GROUP BY delivers wrong results.

• Key: DERBY-3231
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Critical
• Assignee: Manish Khettry
• Reporter: Peter Balon
• Component/s: SQL
• Affects Version/s: 10.3.1.4
• Fix Version/s: 10.3.3.0, 10.4.1.3

Description

The result of the select is not sorted in "order by COUNT(*) DESC" or "order by COUNT(*) ASC"

create table yy (a double, b double);

insert into yy values (2, 4);
insert into yy values (5, 7);
insert into yy values (2, 3);
insert into yy values (2, 3);
insert into yy values (2, 3);
insert into yy values (2, 3);
insert into yy values (9, 7);


select b, COUNT(*) AS "COUNT_OF", SUM(b) AS "sum b"
from yy
where a = 5 or a = 2
group by b
order by COUNT(*) asc

-- same result as:

select b, COUNT(*) AS "COUNT_OF", SUM(b) AS "sum b"
from yy
where a = 5 or a = 2
group by b
order by COUNT(*) desc