Subject | Re: [firebird-support] null in triggers |
---|---|

Author | Paul Vinkenoog |

Post date | 2003-09-18T14:59:53Z |

Hi Ivan,

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

>> if not ( New.Quantity = Old.QuantityWrong indeed.

>> or

>> New.Quantity is null and Old.Quantity is null )

> Wrong.

>> Shortest general field equality test (with NULL considered equalYou're right, and "not (<null expr>)" yields null, so the test gives

>> 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 ...

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