Thread: 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

On Mon, 26 Jan 1998, Darren King wrote:

>> The following is Informix behavior:
>>
>> > 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

I'm running the current cvs and it gives me this.

select b,c,sum(a) from t1 group by b,c;
b|c |sum
-+--+---
 |x |  1
 |x |  2
 |z |  3
 |x |  2
(4 rows)

>> > 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

select b,c,sum(a) from t1 group by b,c order by c;
b|c |sum
-+--+---
 |x |  1
 |x |  2
 |x |  2
 |z |  3
(4 rows)

>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.

I think I saw the patch committed this morning...?


Mike
[ Michael J. Maravillo                                 Philippines Online ]
[ System Administrator     PGP KeyID: 470AED9D     InfoDyne, Incorporated ]
[ http://www.philonline.com/~mmj/                          (632) 890-0204 ]

On Tue, 27 Jan 1998, Michael J. Maravillo wrote:

> >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.
>
> I think I saw the patch committed this morning...?

    Yesterday evening, actually...should we back it out, or leave it
as is?  Is the old way more corrrect the the new?