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