Subject Re: [IBO] select view crashes a remote Firebird Server
Author Helen Borrie
At 09:47 AM 31/05/2004 +0000, you wrote:

>It is ok, it include additional ' because the DFM format also uses '
>to define the string literal.
>In the IDE | Properties, I can see:
>I am sure that all users are using the correct client for the FB
>server, some installations have been made in new and clean computers.
>In any case, this problem also arises in local computer.
>You can test it in your computer.
>Thank you for your help

OK, just extracting your metadata, I see several fields in the view FEMALE that are the probably cause for the server crashes:
f.lfliveBorn*1.00 / nullif(,0),
f.lfStillBorn*1.00 / nullif(,0),
(f.lfliveBorn+f.lfstillborn+f.lfmummies)*1.00 / nullif(,0),
100.0 - 100.0*lftotalweaned/nullif(lflivebornlitweaned+lfnetfostered,0),

In cases where the NULLIF() function finds a non-null value, it returns NULL; in cases where it finds NULL, it returns 0. That means these fields will always be NULL (so the field is purposeless) or crash the server (if there is NULL, which causes the expression to return zero). Why? Because division by zero is an illegal operation.

I think you should receive an exception at least for the first DivideByZero error; at least in a regular SELECT statement, you get it.
select 100/0 as pht from rdb$database
throws the good-old-catch-all-bad-data exception:

ISC ERROR CODE:335544321

arithmetic exception, numeric overflow, or string truncation

However, I don't know the dynamics of error delivery when a view is being assembled. It would be interesting to find out why it crashes the server rather than throwing the exception.

Still, it's going to be essential for you to redefine these view columns using some way that doesn't allow zero to turn up as a divisor; and also so that you actually get a value when the operand column is not null.

I think you've just picked the wrong function by mistake. Here's the description of NULLIF:

"NULLIF( ) substitutes NULL for a value if the value resolves to a non-null value; otherwise, it
returns the value of the sub-expression."

I'm pretty sure that's not what you actually wanted...