[
https://issues.apache.org/jira/browse/DERBY-3231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12551038
]
A B commented on DERBY-3231:
----------------------------
Thank you for the reply, Manish. I'm running the full regression tests with
this patch to make sure that everything is okay; I'll report back with the
results later.
One thing I did notice is that this change makes it so that ALL aggregate nodes
now return "false" for constantExpression(PredicateList). Is that perhaps
going too far? For example, in the queries:
select b, max(a) from yy where a=5 group by b order by max(a) asc;
select b, max(a) from yy where a=5 group by b order by max(a) desc;
the value "MAX(A)" is in fact constant, and will be recognized as such prior to
your patch, thus avoiding the need to actually sort the results for "ORDER BY".
But with the patch applied we'll end up sorting the results unnecessarily.
I definitely agree with your statement that "it is more important to get
correct results ... first", but I wonder if it might be worth it to try to
preserve the optimization when possible? I can't imagine there are many people
out there who would issue an ORDER BY on a constant MAX aggregate, so maybe
this isn't worth addressing; but executing the above two queries before and
after your patch does show that, with the patch, we are doing an extra
unnecessary sort. So if it's not too difficult, I think it would be nice to
preserve the "constant" nature of aggregates like MAX and MIN...
Any thoughts one way or the other?
> 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.
