db-derby-dev mailing list archives

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

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


Mime
View raw message