db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (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 03:06:43 GMT

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

Bryan Pendleton commented on DERBY-3231:
----------------------------------------

Hi Manish, thanks very much for working on this problem.

Did you happen to investigate the "phantom column" behavior that I saw in IJ?
As Peter noted, the extra column did not appear to cause any serious problems,
but I'm wondering why it appeared.

I agree with your assessment that the highest priority is for the query to
return the right results.

But I also think it's hard to predict what sort of odd queries the DBMS will see;
particularly in this age of persistence layers and code generators, the strangest
sort of SQL seems to get generated and thrown at the system. Given that
the optimizer's job is to optimize, I think that we should pay attention to every
situation where there is a possible optimization.

So, as a possible proposal, could we:
 - include the various "unusual" queries that you and Army came up with as
   test cases in the regression test suite, so that at least we continue to
   increase the overall population of "interesting" queries in our test scripts, and
 - file a separate JIRA issue (or perhaps several) noting the various cases
   of potential further optimization, so that we can pursue these later as time permits?



> 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