Re: [HACKERS] Group By, NULL values and inconsistent behaviour.

>
> The following is Informix behavior:
>
> informix@zeus:/usr/informix72> dbaccess - -
> > create database nulltest in datadbs;
> Database created.
>
> > create table t1 (a int, b char(2), c char(2));
> Table created.
>
> > insert into t1 (a,c) values (1,'x');
> 1 row(s) inserted.
> > insert into t1 (a,c) values (2,'x');
> 1 row(s) inserted.
> > insert into t1 (a,c) values (3,'z');
> 1 row(s) inserted.
> > insert into t1 (a,c) values (2,'x');
> 1 row(s) inserted.
> > 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.

Here is where postgres seems to differ.  Seems postgres is missing
an implicit sort so that the grouping is done properly.

Postgres will return _three_ rows...

b  c      (sum)
   x          3
   z          3
   x          2


> > select b,c,sum(a) from t1 group by b,c order by c;
> b  c             (sum)
>
>    x                 5
>    z                 3
>
> 2 row(s) retrieved.

Even with the order by, postgres still returns _three_ rows...

b  c       (sum)
   x           3
   x           2
   z           3

For now, ignore the patch I sent.  Appears from Andreas demo that the
current postgres code will follow the Informix style with regard to
grouping columns with NULL values. Now that I really think about it,
it does make more sense.

But there is still a problem.

Does the SQL standard say anything about an implied sort when
grouping or is it up to the user to include an ORDER BY clause?

darrenk