db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Manish Khettry (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3231) Sorting on COUNT with OR and GROUP BY delivers wrong results.
Date Thu, 13 Dec 2007 02:22:43 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12551194
] 

Manish Khettry commented on DERBY-3231:
---------------------------------------

Well, I thought it was better to fix the wrong results and look at optimizations later on
as a separate bug. I pointed out another kind of query in an earlier comment where we could
do this "sort avoidance" thing.

A slightly more abstract question-- how important is it for Derby to do optimizations like
this? The downsides are code complexity and unanticipated bugs like this one while the benefits
are restricted to queries, which as you put it, "you can't imagine too many people writing"?


> 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
>            Assignee: Manish Khettry
>            Priority: Critical
>         Attachments: order_by_bug.diff.txt
>
>
> 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