Re: [sqlite] Survey: NULLs and GROUP BY

[Robert Simpson]

MS Jet:

a      b    Expr1002
1      2    2
NULL   2    4
1      NULL 8
NULL   NULL 16


----- Original Message ----- 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, September 01, 2005 11:51 AM
Subject: [sqlite] Survey: NULLs and GROUP BY


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.
--
D. Richard Hipp <[EMAIL PROTECTED]>