Re: Function called for row not in query result

"Brian Dick" <bdick_at_cox.net> wrote in message news:z7ibsp6ozfsv$.1hs3cuz7dspo1$.dlg_at_40tude.net...
> On Tue, 16 Nov 2004 18:02:31 +0100, Agoston Bejo wrote:
>
> > Version: Oracle 8.1.7. (I know it's prehistoric, but I am forced to work
> > with it. Nevertheless, I'm curious if this is only a bug of this version
or
> > I'm wrong somewhere about SQL itself.)
> >
> > Take a look at this query:
> >
> > -----------------------
> >
> > SELECT * FROM
> > (
> > SELECT X_ID, FN_X1(X_ID) AS FN_FIELD_1
> > FROM T_X
> > WHERE FIELD1 IN (SELECT T1_ID FROM T1)
> > )
> > WHERE FN_FIELD_1 = 1
> > --no error if the last line is commented out
> >
> > ---------------------------
> >
> > FN_X1 is a function that raises an application error if T_X.FIELD1 is
null
> > where T_X.X_ID = parameter. (T_X: [X_ID NUMBER, FIELD1 NUMBER])
> >
> > In this query, however, this is impossible because of the inner where
> > clause.
> > Not that Oracle 8.1.7 cares: the error gets thrown. (There ARE rows in
T_X
> > where FIELD1 is null indeed.)
> >
> > What's even stranger, if the outer where clause is commented out, then
the
> > error does not occur.
> >
> >
> > I should think that it is guaranteed that in a query like this:
> >
> > select a, fn(a) from t1 where [condition]
> >
> > fn() gets called only in rows where [condition] is true. This suspection
is
> > reinforced by the fact that I've never run into this type of behavior
> > before. And how does this depend on the OUTER where clause? It should be
> > processed only when the result of the inner select is already computed.
(At
> > least that would seem logical.)
> >
> > If I put_line out what X_ID's fn() is called for, there are a few that
> > occurs twice, and the last one is such a number for which FIELD1's value
is
> > null in T_X. So fn() is definitely called for rows it shouldn't be.
> >
> >
> > --------------------------
> >
> > The whole example, if you should need it:
> >
> >
> > CREATE TABLE T_X
> > (
> > X_ID NUMBER(10) NOT NULL,
> > FIELD1 NUMBER(10)
> > )
> >
> > -------------------------------
> >
> > CREATE TABLE T1
> > (
> > T1_ID NUMBER(10) NOT NULL
> > )
> >
> > -------------------------------
> >
> >
> > CREATE OR REPLACE
> > FUNCTION FN_X1(P_X_ID IN T_X.X_ID%TYPE)
> > RETURN T_X.FIELD1%TYPE
> > AS
> > VR_FIELD1 T_X.FIELD1%TYPE;
> > BEGIN
> >
> > SELECT FIELD1 INTO VR_FIELD1
> > FROM T_X
> > WHERE X_ID = P_X_ID;
> >
> > IF VR_FIELD1 IS NULL THEN
> > RAISE_APPLICATION_ERROR(-20001, 'FN_X1 error');
> > END IF;
> >
> > RETURN VR_FIELD1;
> >
> > END;
> >
> > ----------------------------------
> >
> > SELECT * FROM
> > (
> > SELECT X_ID, FN_X1(X_ID) AS FN_FIELD_1
> > FROM T_X
> > WHERE FIELD1 IN (SELECT T1_ID FROM T1)
> > )
> > WHERE FN_FIELD_1 = 1
>
> Tom Kyte showed in an article on autonomous transactions that you cannot
> guarantee when a function is called during query execution. Can you
re-code

Is this behavior by design in Oracle? Is it so in later versions as well?

> your function so it does not throw an exception? How about returning NULL
> instead?

Could you provide a link on this article? I'm interested. Exactly that's what I have done: I wrote a wrapper function that catches the error and returns null if it occurs. Received on Wed Nov 17 2004 - 02:09:32 CST