> sqlite> SELECT test_base.id, SUM(test_join.value) FROM test_base LEFT > JOIN test_join ON test_join.base_id = test_base.id GROUP BY > test_base.id HAVING SUM(test_join.value)='0'; > sqlite> > > The last statement generates no results. There is no mention of a > special case for HAVING so I would assume that the engine should also > typecast the string into a corresponding numerical value.
No, your assumption is incorrect. SUM() is not a database column - it's a function. So it doesn't have any affinity. Value '0' doesn't have affinity too. Thus SQLite doesn't convert those values and compares them as is. Numeric value won't be ever equal to string. Pavel On Thu, Jul 5, 2012 at 11:17 PM, Benjamin Feng <benjamin.f...@gmail.com> wrote: > sqlite> CREATE TABLE test_base(id INTEGER NOT NULL PRIMARY KEY); > sqlite> CREATE TABLE test_join(id INTEGER NOT NULL PRIMARY KEY, > base_id INTEGER NOT NULL REFERENCES test_base(id), value NUMERIC NOT > NULL); > sqlite> INSERT INTO test_base VALUES(1); > sqlite> INSERT INTO test_join VALUES(1, 1, 0); > > sqlite> SELECT test_base.id, test_join.value FROM test_base LEFT JOIN > test_join ON test_join.base_id = test_base.id WHERE test_join.value=0; > 1|0 > sqlite> SELECT test_base.id, test_join.value FROM test_base LEFT JOIN > test_join ON test_join.base_id = test_base.id WHERE > test_join.value='0'; > 1|0 > sqlite> SELECT test_base.id, SUM(test_join.value) FROM test_base LEFT > JOIN test_join ON test_join.base_id = test_base.id GROUP BY > test_base.id HAVING SUM(test_join.value)=0; > 1|0 > sqlite> SELECT test_base.id, SUM(test_join.value) FROM test_base LEFT > JOIN test_join ON test_join.base_id = test_base.id GROUP BY > test_base.id HAVING SUM(test_join.value)='0'; > sqlite> > > The last statement generates no results. There is no mention of a > special case for HAVING so I would assume that the engine should also > typecast the string into a corresponding numerical value. This is > failing on all numerical types of `value` (including INTEGER). > _______________________________________________ > 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
