Subject Re: [IBO] select view crashes a remote Firebird Server
Author Helen Borrie
At 01:25 PM 31/05/2004 +0000, you wrote:
>According to Firebird_v15.108_ReleaseNotes.pdf document:
>NULLIF: Returns NULL for a sub-expression if it has a specific value,
>otherwise returns the value of the subexpression.
>So, for example:
>select nullif(0,0) from rdb$database -> returns NULL
>select nullif(3,0) from rdb$database -> returns 3

No, that's wrong. 0 and NULL are *not* the same thing!! 0 is a "specific
value", i.e. a value of 0.

select nullif(0,0)... will return NULL
select nullif(3,0)... will return NULL
Select nullif(NULL,0) will return zero.

So, if the column contains NULL, you will be getting zero returned by your
expression - and hence the DivideByZero error.

So, do you see why this column is purposeless (as well as buggy)?

>select nominator/nullif(denominator, 0) from ....
>never will raise a DivideByZero exception because if the
>denominator=0, then nullif(denominator, 0) return NULL and
>nominator/nullif(denominator, 0) also returns NULL.
>Also, if I only use "select * from female", everything works ok,
>always. The problem is the combination of the 2 selects (female and

What are you talking about "the combination of the 2 selects"? I don't see
any joins in the DFM...

>It is a very strange problem for me because it depends on
>several factors (combination of selects, number of fields in the
>view, type of fields, ....) Any change can minimize the error
>frequency. To reach to this example, where it always fail, I have
>spent a lot of days.

There are no "mysteries". However, you have five columns in this table
that will potentially involve DivideByZero. Extend that by all your
variables and you have a pretty large collection of potential crash situations.