D. Richard Hipp wrote:
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;
SQL Server 2000
a b (no column name)
----- ------- ---------------
1 2 2
NULL 2 4
1 NULL 8
NULL NULL 16
Oracle
A B SUM(C)
--------- ----------- -----------
1 2 2
2 4
1 8
16
