Re: [sqlite] Survey: NULLs and GROUP BY

[Martin Engelschalk]

postgresql 8.0.1 on WinXP:

a | b | sum
---+---+-----
1 | 2 |   2
  | 2 |   4
1 |   |   8
  |   |  16
(4 rows)

Martin

I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right.  In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.

Can I get some volunteers to run the SQL shown below on various
other SQL database engines and tell me what the output is?

  CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
  INSERT INTO t1 VALUES(1,2,1);
  INSERT INTO t1 VALUES(NULL,2,2);
  INSERT INTO t1 VALUES(1,NULL,4);
  INSERT INTO t1 VALUES(NULL,NULL,8);

INSERT INTO t1 SELECT * FROM t1; SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;

Thanks.