Hello Steve,

> Using Firebird V1.5.2.4731 why are these two triggers not equal for
> the case where new.ESTIMATE is NULL?

> CREATE OR ALTER TRIGGER JOBS_TG_BU for JOBS
> BEFORE UPDATE AS
> BEGIN
> if (new.ESTIMATE is null or new.ESTIMATE = 0) then

This is a correct and safe way to test for possible nullness.

> CREATE OR ALTER TRIGGER JOBS_TG_BU for JOBS
> BEFORE UPDATE AS
> BEGIN
> if (not(new.ESTIMATE <> 0)) then

But this one isn't. If the value is NULL, then:
- (new.ESTIMATE <> 0) yields NULL, and
- (not(new.ESTIMATE <> 0)) also yields NULL,
because not(NULL) is NULL (if a boolean value is
unknown, then the inverse is also unknown).

> It seems as if (NULL <> 0) returns TRUE. I thought NULL comparisons
> always returned false,

No, most of the time they return NULL. NULL behaves like False in an
"if" construct, but not in expressions.

> if (a <> b) then
> MyVariable = 'Not equal';
> else
> MyVariable = 'Equal';
> Here, MyVariable will be 'Equal' if a is NULL and b isn't, or vice
> versa.

Because here, an if-construct is used. This is what happens:
1. (a <> b) yields NULL because one of them is NULL.
2. Since the if-expression is not True, the "then" clause is skipped.
3. And because of that, the "else" clause is executed.

However, the fact that (a <> b) is not True does not imply that
(not (a <> b)) is True. Both are neither True nor False, but NULL.


If you look a couple of lines above this example from the QSG, you'll
find:
not (NULL) = NULL

Also look at the Tip below: think "unknown" if you see Null, and
everything starts to make sense.


Greetings,
Paul Vinkenoog