Re: [sqlite] union-having bug
Gillman, David wrote: > Is this expected behavior? (The failure of my query to return a row.) Your query does not have a well-defined meaning, so no particular behavior is expected from it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] union-having bug
Is this expected behavior? (The failure of my query to return a row.) David -Original Message- From: Kit [mailto:kit.sa...@gmail.com] Sent: Saturday, December 03, 2011 5:24 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] union-having bug 2011/12/3 Gillman, David : > Is this behavior known? The third query returns no rows even though bar = 1. > sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 > sqlite> foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind > sqlite> having bar > 0; select ind, sum(foo) fooo, sum(bar) barr from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind having barr >0; 1|1|1 -- Kit ___ 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
Re: [sqlite] union-having bug
2011/12/3 Gillman, David : > Is this behavior known? The third query returns no rows even though bar = 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; select ind, sum(foo) fooo, sum(bar) barr from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind having barr >0; 1|1|1 -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] union-having bug
Gillman, David wrote: > Is this behavior known? The third query returns no rows even though bar = 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; Your condition involves an expression that neither appears in GROUP BY clause, nor uses an aggregate function. The value of foo (in the first query) and bar (in the second) will come from some row in the group - it is undefined which one. Purely by accident, SQLite chose the row that satisfies the condition in the first case, and one that doesn't in the second. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] union-having bug
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
[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
