Hmm, the mailer changed my output. Here's what I meant: sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind; ind|foo|bar 1|1|1
sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind having foo > 0; ind|foo|bar 1|1|1 sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind having bar > 0; sqlite> select * from (select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind) where bar > 0; ind|foo|bar 1|1|1 sqlite> -----Original Message----- From: Gillman, David [mailto:dgill...@akamai.com] Sent: Friday, December 02, 2011 6:37 PM To: sqlite-users@sqlite.org Subject: [sqlite] union-having bug Hi, Is this behavior known? The third query returns no rows even though bar = 1. sqlite> select region, sum(edge) edge, sum(infra) infra from (select 1 region, 0 edge, 1 infra union select 1 region, 1 edge, 0 infra) group by region;region|edge|infra 1|1|1 sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind; ind|foo|bar 1|1|1 sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind having foo > 0; ind|foo|bar 1|1|1 sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind having bar > 0; sqlite> select * from (select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind) where bar > 0; ind|foo|bar 1|1|1 sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users