> It seems as if (NULL <> 0) returns TRUE. I thought NULL
> comparisons always returned false, as per
> http://www.firebirdsql.org/manual/qsg15-firebird-sql.html

Saying that NULL in comparisons returns FALSE is a
simplification that works in most instances. In actual
fact a NULL in a comparison results in UNKNOWN/NULL (as
per ternary logic).

If you look at the tip at the bottom:
"Think of NULL as UNKNOWN".

The simplification of UNKNOWN == FALSE works well for most
things, but when you throw "NOT" into the mix it gets quite
confusing.
NOT(TRUE) = FALSE
NOT(FALSE) = TRUE
NOT(UNKNOWN) = UNKNOWN

So your test can be interpretted as:
if (not(new.ESTIMATE <> 0)) then
==> if (not(NULL <> 0)) then
==> if (not(UNKNOWN)) then
==> if (UNKNOWN) then
==> if (FALSE) then

ie. UNKNOWN/NULL is not true and it is not false, it is
unknown. The simplification of UNKNOWN == FALSE comes from
the idea that the "IF" is asking whether a test is TRUE. A
result of UNKNOWN is not true therefore the resulting action
is the same as if the result were FALSE.

NOTE: It is only at the final part of the evaluation that you
can safely say that UNKNOWN == FALSE. To get to that final
simplification you should first carry the UNKNOWN/NULL results
through the intermediate tests using ternary logic.

If you manage to avoid NOT in your tests then (AFAIK) you can
safely use the UNKNOWN == FALSE simplification in intermediate
tests as well, because the final behaviour will be the same.
It is NOT that causes the confusion, and so it is the use of
NOT that should always tell you to BE VERY CAREFUL.


HTH

--
Geoff Worboys
Telesis Computing