Re: [sqlite] Bug using aggregate functions
I use group by 99,99% with aggregate functions. Just this one special case I have when I must get min, max values and also I have group_concat where I must filter records. Result is then inserted into table where I have not null constraint and I get error because min, max returned null. I solved this with two sql-s. First is insert and second is update. I didn't know I could use dummy value for grouping. I see this is not just sqlite specific. I tried also with Firebird DB and it works. So I will modify my program. Thank you! Clemens Ladisch je 07.02.2017 ob 8:40 napisal: Radovan Antloga wrote: select min(A) from TEST where B is null and A > 3; if you replace min(A) with * you get empty result set as expected but with min or max or avg you get one record This is just how aggregate functions in SQL work. When you're using GROUP BY, you get exactly one result row per group. And if there are no rows that are grouped, there are no groups, and therefore the result is empty. When you're not using GROUP BY, you always get exactly one result row out of the aggregate function(s), even if they aggregate an empty set. If you do want an empty result if the (filtered) source table is empty, you have to add grouping (by some dummy value): SELECT min(a) FROM Test WHERE b IS NULL AND a > 3 GROUP BY NULL; Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug using aggregate functions
Radovan Antloga wrote: > select min(A) > from TEST > where B is null > and A > 3; > > if you replace min(A) with * you get empty result set as expected > but with min or max or avg you get one record This is just how aggregate functions in SQL work. When you're using GROUP BY, you get exactly one result row per group. And if there are no rows that are grouped, there are no groups, and therefore the result is empty. When you're not using GROUP BY, you always get exactly one result row out of the aggregate function(s), even if they aggregate an empty set. If you do want an empty result if the (filtered) source table is empty, you have to add grouping (by some dummy value): SELECT min(a) FROM Test WHERE b IS NULL AND a > 3 GROUP BY NULL; Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug using aggregate functions
This is the way SQL (not only SQLite) interprets these queries. Basically you ask information about an empty set: - count(*) = 0, as expected - min(A) is undefined, which is translated in SQL by 'null' value; since 'null' is not 'nothing', you get a 1-line result comprising 'null' ! Regards J-L Hainaut Hi, I discovered a bug using sqlite 3.15.2. It is simple to reproduce. Try this SQL-s: CREATE TABLE TEST( A integer primary key, B integer); -- insert some test data insert into TEST (A,B) values (1, 1); insert into TEST (A,B) values (2, null); -- check count(*) select count(*) from TEST where B is null and A > 3; -- bug sql using agg function (also with other functions: max, avg) select min(A) from TEST where B is null and A > 3; if you replace min(A) with * you get empty result set as expected but with min or max or avg you get one record Best Regards Radovan Antloga ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug using aggregate functions
Sorry to post this to quick. I just checked this with Firebird and I get same result. I did not expect that. Sorry once again !! Radovan Antloga je 06.02.2017 ob 18:34 napisal: Hi, I discovered a bug using sqlite 3.15.2. It is simple to reproduce. Try this SQL-s: CREATE TABLE TEST( A integer primary key, B integer); -- insert some test data insert into TEST (A,B) values (1, 1); insert into TEST (A,B) values (2, null); -- check count(*) select count(*) from TEST where B is null and A > 3; -- bug sql using agg function (also with other functions: max, avg) select min(A) from TEST where B is null and A > 3; if you replace min(A) with * you get empty result set as expected but with min or max or avg you get one record Best Regards Radovan Antloga ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users