Re: [HACKERS] Group By, NULL values and inconsistent behaviour. - Mailing list pgsql-hackers

From darrenk@insightdist.com (Darren King)
Subject Re: [HACKERS] Group By, NULL values and inconsistent behaviour.
Date
Msg-id 9801261536.AA68026@ceodev
Whole thread Raw
Responses Re: [HACKERS] Group By, NULL values and inconsistent behaviour.
List pgsql-hackers
>
> 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

pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [HACKERS] Re: [QUESTIONS] How is PostgreSQL doing?
Next
From: "Michael J. Maravillo"
Date:
Subject: Re: [HACKERS] Group By, NULL values and inconsistent behaviour.