> Dear All,
>
> Just thought i'm gonna share this little problem that a customer
> reported last friday. Took me 2 days to pinpoint the problem.

Ouch. Had you posted here it would have been spotted. We have created
an external function that wraps a call to OutputDebugString which is
invaluable in debugging PSQL. We use it to confirm the values in
particular variables when debugging issues. I recommend you try
something similar.

>
> In an AFTER UPDATE trigger for a MASTER table, i have the following

code:

> ...
> -- for each detail record
> for select blah blah blah
> from detail_table
> where (master_id = new.master_id)
> do begin
>
> -- fetch lookup information
> select lookup_field
> from lookup_table where (lookup_id = new.lookup_id)
> into :lookup_var;

This loop is equivalent to (logically):

for
select first 1 lookup_field
from lookup_table where (lookup_id = new.lookup_id)
into :tmp
do
begin
lookup_var = :tmp;
end

Please note I am not suggesting this is how it executes, I am saying
that unless there is actually something returned, the variable will
not be affected.

>
> -- only process if the lookup information exists
> if (lookup_var is null) then begin
> ...
> ...
> ...
> end
> end
> ...
>
> Can anyone spot the bug in this ? :)

You forgot to add the line before the query, a common mistake.

lookup_var = NULL;

>
> I was expecting the "lookup_var" local variable to be set to NULL if

the

> lookup data does not exists. Somehow firebird (1.5.3, Win32) ignores

the

> "select into" if the select returns NULL.

Are you sure??

Try this query.

select null
from RDB$DATABASE
into :lookup_var;

What is in lookup_var.

NULL does not mean empty. It means that the value of a particular
field is unknown or unspecified. Google for the Firebird NULL guide
for all the gotchas there.

There is a difference between 'not knowing the value where a record is
known to exist' and 'knowing that no records exist'.

>
> Adding initialization code BEFORE the 'fetch' operation solves the
> problem, but this sure is a difficult 'bug' to find. Has anyone ran

into

> this problem before? Is this normal, or did i run into a bug ?

At the end of the day, you are correct with your assertion it is a
gotcha. You are not the first and I doubt you will be the last person
to fall into this trap. I know about it and occasionally I still make
the mistake.

You also correctly worked out you need to initialise it first. It can
be a difficult error to find, especially if you are used to languages
like Delphi which tend to pre-initialise variables for you.

Adam