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 Tue, 11 Dec 2007 16:29:43 GMT

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

A B 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. 

Thank you for looking into this, Manish!  Two follow-up questions based on what you have found,
just for my own understanding:

  1) If possible, can you explain what part of the DERBY-681 changes led to this behavior,
since the query returned correct results prior to the DERBY-681 fix?  I'm just curious as
to why the missing methods for AggregateNode were not a problem previously.

  2) Any idea why removing the "WHERE" clause from the original query causes it to return
correct results, even without the order_by_bug.diff patch, as stated in your December 4th
comment?  I admit I haven't done any tracing myself, but from your previous comment I can't
quite see the connection between the WHERE clause and the ORDER BY, since the WHERE clause
does not reference the aggregate "count(*)".  Apologies if I'm missing something obvious...

Thanks again for your willingness to investigate this further.  Have you had a chance to run
any of the regression tests with your patch applied?

> 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