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-2
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

Clemens Ladisch
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
[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
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
> [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