[
https://issues.apache.org/jira/browse/DERBY-3231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12547756
]
Peter Balon commented on DERBY-3231:
------------------------------------
Hi Bryan,
sorry that I did not made myself clear in the first post, but as you have
pointed out, the sorting of the count(*) column is not correct, which is the
main problem here. The ASC and DESC is ignored.
The phantom column which appears as the last row is not an issue, but might
become in an other application.
The wrong execution plan is propably caused by the wrong sorting issue.
Expected results:
select b, COUNT(*) AS "COUNT_OF"
from yy where a = 5 or a = 2
group by b
order by COUNT(*) ASC
B |COUNT_OF | 3
----------------------------------------------
4.0 |1 |1
7.0 |1 |1
3.0 |4 |4
select b, COUNT(*) AS "COUNT_OF"
from yy where a = 5 or a = 2
group by b
order by COUNT(*) DESC
B |COUNT_OF | 3
----------------------------------------------
3.0 |4 |4
4.0 |1 |1
7.0 |1 |1
> Sorting on COUNT with OR and GROUP BY delivers wrong results.
> -------------------------------------------------------------
>
> Key: DERBY-3231
> URL: https://issues.apache.org/jira/browse/DERBY-3231
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4
> Environment: Eclipse 3.2.2; java 1.5.0_11;
> Reporter: Peter Balon
> Priority: Critical
>
> 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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
