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

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

From
darrenk@insightdist.com (Darren King)
Date:
>
> 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

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

From
"Michael J. Maravillo"
Date:
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 ]


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

From
The Hermit Hacker
Date:
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?