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