Bug using aggregate functions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug using aggregate functions

Radovan Antloga
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug using aggregate functions

Radovan Antloga
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug using aggregate functions

Jean-Luc Hainaut
In reply to this post by Radovan Antloga

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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users