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

Bruce Momjian wrote:
> 
> 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?

I ran the test on Sybase.  The only real changes were int4->int and
explicitly calling out field b as null (it defaults to not null).

1> select @@version
2> go
                                                                               
 ----------------------------------------------------------------------------- 
 SQL Server/11.0.2/P/Sun_svr4/OS 5.4/EBF 6536/OPT/Sat Aug 17 11:54:59 PDT 1996 
 
(1 row affected)
1> create table t1 (a int, b char(2) null, c char(2))
2> go
1> insert into t1 (a,c) values (1,'x')
2> go
(1 row affected)
1> insert into t1 (a,c) values (2,'x')
2> go
(1 row affected)
1> insert into t1 (a,c) values (3,'z')
2> go
(1 row affected)
1> insert into t1 (a,c) values (2,'x')
2> go
(1 row affected)
1> select * from t1
2> go
 a           b  c  
 ----------- -- -- 
           1 NULL x  
           2 NULL x  
           3 NULL z  
           2 NULL x  
 
(4 rows affected)
1> select b,c,sum(a) from t1 group by b,c
2> go
 b  c              
 -- -- ----------- 
 NULL x            5 
 NULL z            3 
 
(2 rows affected)
1> select b,c,sum(a) from t1 group by b,c order by c
2> go
 b  c              
 -- -- ----------- 
 NULL x            5 
 NULL z            3 
 
(2 rows affected)

It seems that Sybase thinks a null is a null in this case.  However,
try the following:

select * from t1 x, t1 y where x.b=y.b and y.c='z';

Sybase returns zero rows for this.  It seems that it treats NULLs as
equal for order and group operations, but not for join operations.

Ocie Mitchell