[
https://issues.apache.org/jira/browse/DERBY-3231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12550273
]
Manish Khettry commented on DERBY-3231:
---------------------------------------
The problem was missing methods isConstant and isConstantExpression for
AggregateNodes. The super class implementation would return true causing Derby
to eliminate the order by.
isConstantExpression is used to eliminate sorts in queries like this:
select c1, c2
from t
where c1=2
order by c1;
In this case, we have an aggregate as the sort column and this sort of
optimization will apply to queries like this:
select c1, count(*)
from t
group by c1
having count(*) = 2
order by count(*);
To remove constant sort columns the code in SelectNode#preProcess (around line
915) will have to be modified to consider having clauses as well.
For now, I return false for both methods-- it is more important to get correct
results for this query first.
> 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.
