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 Sun, 02 Dec 2007 16:35:45 GMT

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

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

I notice 3 interesting things when I run your query. First, here is the output I get:

ij> select b, count(*) from yy where a=5 or a=2 group by b order by count(*) asc;
B                     |2          |3
----------------------------------------------
3.0                   |4          |4
4.0                   |1          |1
7.0                   |1          |1

Note that there are *three* columns in the result set, not two. Since there were
only two columns in your select list, that seems wrong to me.

Secondly, the output is ordered by column B, not by column count(*), so that
seems wrong to me, too.

Lastly,  I took a look at the query plan using -Dderby.language.logQueryPlan=true,
and I've included it below. Note that there is only *one* sort executed in this
query plan, although it seems that there should have been two sorts:
 - one to order the rows by column "b" in order to perform the grouping.
 - one to take the grouped results and re-sort them by the count(*) column

It seems that the optimizer didn't understand that the desired ordering of
the results was different than the implied ordering of the GROUP BY column.

Here is the query plan:

2007-12-02 16:29:03.707 GMT Thread[main,5,main] (XID = 183), (SESSIONID = 0), se
lect b, count(*) from yy where a=5 or a=2 group by b order by count(*) asc *****
** Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 3
Rows filtered = 0
restriction = false
projection = true
    constructor time (milliseconds) = 0
    open time (milliseconds) = 0
    next time (milliseconds) = 0
    close time (milliseconds) = 0
    restriction time (milliseconds) = 0
    projection time (milliseconds) = 0
    optimizer estimated row count:            1.00
    optimizer estimated cost:           30.90

Source result set:
    Grouped Aggregate ResultSet:
    Number of opens = 1
    Rows input = 6
    Has distinct aggregate = false
    In sorted order = false
    Sort information:
        Number of rows input=6
        Number of rows output=3
        Sort type=internal
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        optimizer estimated row count:            1.20
        optimizer estimated cost:           30.90

    Source result set:
        Project-Restrict ResultSet (4):
        Number of opens = 1
        Rows seen = 6
        Rows filtered = 0
        restriction = false
        projection = true
            constructor time (milliseconds) = 0
            open time (milliseconds) = 0
            next time (milliseconds) = 0
            close time (milliseconds) = 0
            restriction time (milliseconds) = 0
            projection time (milliseconds) = 0
            optimizer estimated row count:            1.20
            optimizer estimated cost:           30.90

        Source result set:
            Project-Restrict ResultSet (3):
            Number of opens = 1
            Rows seen = 7
            Rows filtered = 1
            restriction = true
            projection = false
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                restriction time (milliseconds) = 0
                projection time (milliseconds) = 0
                optimizer estimated row count:            1.20
                optimizer estimated cost:           30.90

            Source result set:
                Table Scan ResultSet for YY at read committed isolation level us
ing instantaneous share row locking chosen by the optimizer
                Number of opens = 1
                Rows seen = 7
                Rows filtered = 0
                Fetch Size = 16
                    constructor time (milliseconds) = 0
                    open time (milliseconds) = 0
                    next time (milliseconds) = 0
                    close time (milliseconds) = 0
                    next time in milliseconds/row = 0

                scan information:
                    Bit set of columns fetched=All
                    Number of columns fetched=2
                    Number of pages visited=1
                    Number of rows qualified=7
                    Number of rows visited=7
                    Scan type=heap
                    start position:
null                    stop position:
null                    qualifiers:
None
                    optimizer estimated row count:            1.20
                    optimizer estimated cost:           30.90


> 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
>            Priority: Critical
>
> 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