db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Peter Balon (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3231) Sorting on COUNT with OR and GROUP BY delivers wrong results.
Date Mon, 03 Dec 2007 08:08:53 GMT

    [ 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.


Mime
View raw message