Re: comparing NEW and OLD (any good this way?)

Greg Stark wrote:

> Why is this thread still going on?

Sorry, it's still going on. Call me a slow learner if you want :)

> What does the spec say we should be
> doing and are we violating it in any of these cases?

After a bit more reading, I believe the bottom line is:
while the spec says that if X is the null value, then "X is null" evaluates
to true,
it doesn't say that if "X is null" is true, then X is the null value. And
that's the catch.

Once digested the (counter-intuitive) rule that "X is null" should never be
confused with "X is the null value", then PG's behavior suddenly feels
consistant.

For example, when evaluating "X is distinct FROM null", the fact that "X is
null" returns true is irrelevant and is not considered. What is tested is
whether X evaluates to null or not.
The spec says "A null value and a non-null value are distinct".
Since "A null value" is NOT equivalent to "an expression on which IS NULL
returns true", the fact that "ROW(null,null) is distinct FROM null" evaluates
to true doesn't violate the spec.

I believe the implication of this weirdness for SQL programmers is that when
we feel like using "IS NULL" and rowtypes are involved, we should think hard
about what we really want to test and possibly use "IS DISTINCT FROM NULL"
rather than "IS NULL".

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org