Re: [HACKERS] No: implied sort with group by

> > > > This is what I think is missing or broken right now.
> > > >
> > > > > > select * from t1;
> > > > >          a b  c
> > > > >          1    x
> > > > >          2    x
> > > > >          3    z
> > > > >          2    x
> > > > >
> > > > > 4 row(s) retrieved.
> > > > > > select b,c,sum(a) from t1 group by b,c;
> > > > > b  c             (sum)
> > > > >
> > > > >    x                 5
> > > > >    z                 3
> > > > >> 2 row(s) retrieved.
> > >
> > > Sorry, I've lost the thread. What is broken? I get this same result, and
> > > (assuming that column "b" is full of nulls) I think this the correct result.
> >
> > At one point, it was thought that NULLs shouldn't be grouped, but I
> > backed out the patch.  There is a problem with GROUP BY on large
> > datasets, and Vadim knows the cause, and will work on it later.
>
> Different from the grouping by NULLs issue...
>
> The above results are from Sybase.  If these same four rows are inserted into
> postgres, the second query will return three rows.  Something like...
>
> b|c|sum(a)
>  |x|3
>  |z|3
>  |x|2
>
> It does this not because of the null values of column b, but because the data is
> not sorted before getting to the group by node if the user does not explicitly put
> an order by in the query.  IMHO, postgres should put an arbitrary sort node in the
> tree so that the data can be properly grouped as the group node iterates over it.
>
> And even if I put an "order by c" clause in there, I still get three rows, they're
> just properly sorted. :)

Not necessarily true; as I said, I get the same result as above (with the 980112
source tree; have things changed since??). Perhaps you are running into the sorting
problem which seemed to be present on larger tables only?

                                                    - Tom

postgres=> select b,c,sum(a) from t1 group by b,c;
b|c|sum
-+-+---
 |x|  5
 |z|  3
(2 rows)

postgres=> select * from t1;
a|b|c
-+-+-
1| |x
2| |x
2| |x
3| |z
(4 rows)