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;
>
> Thanks.
test=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
test=# 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;
CREATE
test=# INSERT INTO t1 VALUES(1,2,1);
INSERT 1229984 1
test=# INSERT INTO t1 VALUES(NULL,2,2);
INSERT 1229985 1
test=# INSERT INTO t1 VALUES(1,NULL,4);
INSERT 1229986 1
test=# INSERT INTO t1 VALUES(NULL,NULL,8);
INSERT 1229987 1
test=# INSERT INTO t1 SELECT * FROM t1;
INSERT 0 4
test=# SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;
a | b | sum
---+---+-----
1 | 2 | 2
| 2 | 4
1 | | 8
| | 16
(4 rows)
reid
