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

>
> The following is Informix behavior:
>
> informix(at)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