Hi Ivan,

>> if not ( New.Quantity = Old.Quantity
>> or
>> New.Quantity is null and Old.Quantity is null )

> Wrong.

Wrong indeed.

>> Shortest general field equality test (with NULL considered equal
>> to NULL) that I've been able to come up with is:
>>
>> A = B or A is null and B is null
>>
>> or with parens:
>>
>> ( A = B ) or ( A is null and B is null )

> If A is regular value and B is NULL, the result will be NULL ...

You're right, and "not (<null expr>)" yields null, so the test gives
the wrong result.

This surprised me a lot, because half a year ago I tested the
expression with quite a few real-life cases and the results were OK
(so much for my testcases!).

One of the conclusions I remember from my testing was that NULL didn't
"contaminate" across an OR- or AND- boundary. But after some new tests
I did just now, things turn out to be more complicated.

The weird thing is: if exactly one of A and B is NULL, the expression
A = B becomes null. OK, that's common knowledge. You were right in
saying that the entire expression also becomes null instead of false
(this is where I went wrong) and hence the negation is null, not true.

But now assume A and B both NULL. A = B will be null again, so one
would expect that the entire expression again becomes NULL, causing
the test to fail again. But it doesn't! As far as I can tell now, the
following

<null expr> or false

yields null,

but

<null expr> or true

yields true.

As a result, "not ( <null expr> or false )" yields null,
but "not ( <null expr> or true )" yields false.

Very inconsistent, but that's why the test gives a correct result if
A and B are both null.

Thanks for pointing out my error!


Greetings,
Paul Vinkenoog