Subject Re: [ib-support] pb left join query and snullif function fbud
Author Helen Borrie
At 10:25 AM 12-06-02 +0200, you wrote:
>Helen, I'm sorry but even the snvl function does not work, and I think snvl
>and snullif share the exactly same code.

When used appropriately as UDFs, both functions work as designed and quite
clearly do not share the same code.

>I took the time to provide you a test case just try it and tell me if you
>encounter the same problem.

I know (from private email earlier today) that you thought so. I told you
it didn't ring a bell - meaning I had no record of it. So I'm working on
what you posted in the lists - where (intended or not) you appeared to
expect SNULLIF to work like NVL in Oracle...

However, to move on: here's the SNULLIF statement:


If I now correctly understand you, you are expecting the UDFs SNULLIF and
SNVL to have the same scope as the WHERE clause - hence you are expecting
that, if the query selects the *output* rows where TABLE_B.COL_B IS NULL,
then it should produce the string 'not empty' in the NUL_COL column,
because this NULL in the output row is "not equal to the string 'not empty'
" - is that right?

There are two ways this can't work.
-- First, NULL is not a value, so a comparison of NULL and 'not empty' will
never result in an evaluation of "not equal", but always NULL.
-- However, it doesn't arise here, anyway. The UDF operates on the *input*
value, not the output value.
--------In the case of SNULLIF, the first parameter passed does not equal
the value supplied ('not empty') so the function passes back the same value
that was passed.
--------In the case of SNVL, the first parameter is not null, so it is
passed back as the return value.

I don't have Oracle installed on my systems currently so I can't test the
scope of its native NVL function to see whether it gets applied to the
output...but, AFAIK, there is no way to force a UDF to be applied to
ungrouped output. In the case where GROUP BY output is grouped by UDF -
which is possible in Firebird 1 though starved of examples - the logic is
different, with the UDF actually being applied to a value in a set which is
already formed.

>Do you know in which newsgroup should I post those kind of pb to get a
>chance to see it fixed ?

Hmmm, either here or Firebird-devel, on a day when Claudio is reading list

As to getting it "fixed", I think it would first have to be established
that it was wrong. The fact that it might not be interchangeable with a
similar function in Oracle doesn't make it "wrong". It's not a standard
SQL function, so there's really nothing to predicate how it *ought* to behave.


All for Open and Open for All
Firebird Open SQL Database · ·