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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-01-25 20:21:39 | Re: [HACKERS] Variable Block Size Dilemma |
Previous Message | Darren King | 1998-01-25 19:38:58 | Variable Block Size Dilemma |