[DERBY-3231] Sorting on COUNT with OR and GROUP BY delivers wrong results.

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 10.3.1.4
• Fix Version/s: 10.3.3.0, 10.4.1.3
• Component/s: SQL
• Environment: Eclipse 3.2.2; java 1.5.0_11
• Urgency: Urgent
• Bug behavior facts: Regression
• Assignee: Manish Khettry
• Reporter: Peter Balon
• Created: 27/Nov/07 10:16
• Updated: 21/Jan/11 17:51
• Resolved: 24/Jan/08 21:59

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

Issue Links

• is part of: DERBY-2034 Tracking of bugs that lead to incorrect results being stored or returned to the client
• is related to: DERBY-681 Eliminate the parser's rewriting of the abstract syntax tree for queries with GROUP BY and/or HAVING clauses