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 Thu, 13 Dec 2007 16:47:43 GMT

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

A B commented on DERBY-3231:
----------------------------

manish> I thought it was better to fix the wrong results and look at optimizations later
on as a separate bug. 

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

Okay, so it looks like there is agreement here :) I just wanted to bring up the fact that,
in getting one query to return correct results, we're effectively *disabling* an *existing*
optimization for another query that currently works correctly.  To say "get it working, then
improve it" is perfectly okay; to say "get it working but regress other optimizations, then
fix those other optimizations later" is also probably okay, but perhaps slightly more risky
as a general principle.

In any event, I'm +1 to Bryan's proposal given the apparent unlikelihood of the to-be-disabled
optimization (ex. for MAX()) showing up in a user's environment.

manish> +1 on your proposal.

Manish, are you volunteering to implement the proposal, i.e. to 1) add more test cases to
the regression suite, and 2) file another Jira?

My derbyall and suites.All with the patch ran cleanly (only failure was the known failure
of outerjoin.sql, which is unrelated and has since been fixed).  So I can commit order_by_bug.diff
now and the additional test cases can perhaps be added as a separate patch/commit.  Does that
sound okay?

> 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