--- In firebird-support@yahoogroups.com, "Graeme Edwards"
<g.edwards@...> wrote:

>
> >The other day i posted a rather lengthy message about a (self-

induced)

> >bug because i did not initialize a variable properly. The

conclusion i

> >get from the response to that post is that these are 'normal'

constructs

> >in firebird's stored procedure language :
>
> ...
> > total = 0;
> > select sum(...) form other_table into :total;
> ...
>
> >Again, if i understood Ms Helen Borrie's message correctly, if the
> >select returns NULL, the variable total will not be affected, so

it will

> >be still be zero.
>
> >Today (at 5 AM, duh), i encountered another case with these lines :
> ...
> > total = 0;
> > select sum(detail.amount)
> > from master
> > join detail on (master_id = detail_id
> > where (master.trans_date < :end_date) and
> > (detail.item_id = :item_id)
> > into :total;
> ...
>
> >Since the tables are empty, the result of the variable TOTAL is

always

> >NULL inside this stored procedure (Firebird 1.5.3, Win32). This

kinda

> >contradicts my conclusion above (about how a SELECT INTO that

returns a

> >NULL will not affect the variable value).
>
> >Just thought i'm gonna post this here before i went to bed :)
> >Thank you all in advance,
>
> I find this one an interesting issue also and I am wondering if

there is

> some
>
> subtle difference between a query that includes an aggregate like

sum and

>
> a query that simply returns a field.
>
> It seems like an aggregate might return a recordset containing one

row of

>
> data where the field may have a null value as in this example while

a query

> that
>
> returns a field returns a null recordset with no records where

there is no

>
> matching data. Maybe the value of total is set to null because

there is a

>
> recordset returned by the query with a field containing null.
>
> I might have this totally wrong, so hopefully an expert will be

able to

>
> clarify this.
>
> Graeme Edwards

Google 'Firebird NULL Guide'

Study it and be able to recite it backwards. If you don't do that,
then declare every field as not null to prevent you needing to worry
about it ;)

That document contains all the usual gotchas, including its behaviour
with respect to aggregates. I must admit that I seldom need to use
these aggregate fields so I am not 100% familiar with their outcomes.

> > select sum(detail.amount)
> > from master
> > join detail on (master_id = detail_id
> > where (master.trans_date < :end_date) and
> > (detail.item_id = :item_id)
> > into :total;

According to the NG, this should sum the not null fields. There is a
warning on the bottom of the appropriate page that "Be warned that
even SUM returns NULL if used on an empty set, which is contrary to
common logic'.

My logic (not how FB behaves in this case) is that sum should return
0 where no records are hit, and should return NULL if records
containing null are included, putting the onus on the developer to
use the where condition to skip these rows. It there are no nulls
hit, then the sum is, well the sum.

Most of the time I like the way Firebird handles NULLs. It is
refreshing that they are not treated like another value as some
databases and languages seem to use them, but as a state. In this
case, I think it is strange.

By the way, it makes sense that sum changes the value of total. It
does indeed return a record (which just happens to be null). The
problem is that according to your expectations, it should have
returned 0 when no records are encountered rather than null (as I
have already agreed with you).

To get it to work:

select Coalesce(sum(detail.amount), 0)
from master
join detail on (master_id = detail_id
where (master.trans_date < :end_date)
and (detail.item_id = :item_id)
into :total;

Adam