RE: [sqlite] Survey: NULLs and GROUP BY
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
Re: [sqlite] Survey: NULLs and GROUP BY
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.
Re: [sqlite] Survey: NULLs and GROUP BY
On Thu, 2005-09-01 at 14:51 -0400, 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. Dumb forgot the version. Postgresql 8.0.3 psql template1 PATH tech
Re: [sqlite] Survey: NULLs and GROUP BY
Thanks everybody! All the results so far seem to be in agreement with each other and with the current behavior of SQLite. So I think everything is good. Thx for the help. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Survey: NULLs and GROUP BY
On Thu, 2005-09-01 at 14:51 -0400, 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. Postgresql. psql template1 PATH tech
Re: [sqlite] Survey: NULLs and GROUP BY
On 9/1/05, D. Richard Hipp <[EMAIL PROTECTED]> wrote: >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; Postgres 8.0.3 and Firebird 1.5.2: a |b |sum 1 |2 |2 NULL|2 |4 1 |NULL|8 NULL|NULL|16 -- Nemanja Corlija <[EMAIL PROTECTED]>
RE: [sqlite] Survey: NULLs and GROUP BY
Postgres 7.4: a | b | sum ---+---+- 1 | 2 | 2 | 2 | 4 1 | | 8 | | 16 DB2 8.2 A B 3 --- --- --- 1 2 2 - 2 4 1 - 8 - - 16 -Tom > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 01, 2005 2:51 PM > To: sqlite-users@sqlite.org > 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]> > >
Re: [sqlite] Survey: NULLs and GROUP BY
Postgresql 7.4.7 stats=>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) Rgds, Mark. On Thu, 1 Sep 2005, 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. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > Regards, Mark
Re: [sqlite] Survey: NULLs and GROUP BY
SQL Server 2000: a b --- --- --- 1 2 2 NULL2 4 1 NULL8 NULLNULL16 - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: 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]>
Re: [sqlite] Survey: NULLs and GROUP BY
>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; Microsoft SQL Server 2000 (only difference is I used a temp table): (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (4 row(s) affected) a b --- --- --- 1 2 2 NULL 2 4 1 NULL 8 NULLNULL 16 (4 row(s) affected)
Re: [sqlite] Survey: NULLs and GROUP BY
Oracle 9i: A B SUM(C) 1 2 2 [NULL] 2 4 1 [NULL] 8 [NULL] [NULL] 16 --- "D. Richard Hipp" <[EMAIL PROTECTED]> 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. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >
Re: [sqlite] Survey: NULLs and GROUP BY
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; Oh, what the heck! MS Access ;-) abExpr1002 122 24 1 8 16
Re: [sqlite] Survey: NULLs and GROUP BY
SQL Server 2005: a b --- --- --- 1 2 2 NULL 2 4 1NULL 8 NULLNULL 16 On 9/1/05, D. Richard Hipp <[EMAIL PROTECTED]> 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. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > -- Cory Nelson http://www.int64.org
RE: [sqlite] Survey: NULLs and GROUP BY
MySQL 4.1.1-NT +--+--++ | a| b| sum(c) | +--+--++ |1 |2 | 2 | | NULL |2 | 4 | |1 | NULL | 8 | | NULL | NULL | 16 | +--+--++ Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 1:51 PM To: sqlite-users@sqlite.org 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]>
Re: [sqlite] Survey: NULLs and GROUP BY
MS Jet: a bExpr1002 1 22 NULL 24 1 NULL 8 NULL NULL 16 - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: 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]>
Re: [sqlite] Survey: NULLs and GROUP BY
On Thu, Sep 01, 2005 at 02:51:21PM -0400, D. Richard Hipp wrote: Oracle9i Enterprise Edition Release 9.2.0.4.0: SQL> SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3; A B SUM(C) -- -- -- 1 2 2 2 4 1 8 16 > 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; -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] Survey: NULLs and GROUP BY
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 NULL2 4 1 NULL8 NULLNULL16 Oracle A B SUM(C) - --- --- 1 22 24 1 8 16
