Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: darrenk(at)insightdist(dot)com (Darren King)
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Group By, NULL values and inconsistent behaviour.
Date: 1998-01-25 19:53:21
Message-ID: 199801251953.OAA23464@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Where are we on this.  It appears this NULL group by is seriously
broken.

Can we have some tests on commercial databases, and get a patch
generated?

> 
> 
> While looking thru the nodeGroup code, I noticed the following
> that I'm not sure is correct.
> 
> -- Using 01-09 snapshot
> create table t1 (a int4, b char(2), c char(2));
> CREATE
> insert into t1 (a,c) values (1,'x');
> INSERT 149419 1
> insert into t1 (a,c) values (2,'x');
> INSERT 149420 1
> insert into t1 (a,c) values (3,'z');
> INSERT 149421 1
> insert into t1 (a,c) values (2,'x');
> INSERT 149422 1
> select * from t1;
> a|b|c
> -+-+--
> 1| |x 
> 2| |x 
> 3| |z
> 2| |x
> (4 rows)
> 
> select b,c,sum(a) from t1 group by b,c;
> b|c |sum
> -+--+---
>  |x |  3
>  |z |  3
>  |x |  2
> (3 rows)
> 
> select b,c,sum(a) from t1 group by b,c order by c;
> b|c |sum
> -+--+---
>  |x |  3
>  |x |  2
>  |z |  3
> (3 rows)
> 
> In the second query, the first two rows have been grouped, but shouldn't
> they not be since b is NULL?  I thought that NULL != NULL?
> 
> If so, is the third query wrong?  The first two rows are different, but
> only because of the aggregated column that is the source of the group by.
> According to the logic from the second query, these should have been
> grouped, no?
> 
> What does the standard say about comparing two NULL values?
> 
> The fixes for these inconsistencies appear to be simple.  To cause a new
> group to be started if NULL != NULL, simply change the "continue;" in the
> sameGroup function in nodeGroup.c to "return FALSE;"  Ignoring aggregated
> columns would also then be added to sameGroup().
> 
> darrenk
> 
> 


-- 
Bruce Momjian
maillist(at)candle(dot)pha(dot)pa(dot)us

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 1998-01-25 20:21:39
Subject: Re: [HACKERS] Variable Block Size Dilemma
Previous:From: Darren KingDate: 1998-01-25 19:38:58
Subject: Variable Block Size Dilemma

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group