Re: Function called for row not in query result

From: Jan <janik_at_pobox.sk>
Date: 18 Nov 2004 01:14:31 -0800
Message-ID: <81511301.0411180114.134df556_at_posting.google.com>


I suspect it can have something with CBO and Pushing Predicate. Try to use RBO to see if that is the reason.

Try:

alter session set optimizer_mode=rule;

and run the query.

Jan

"Agoston Bejo" <gusz1_at_freemail.hu> wrote in message news:<cndc45$fli$1_at_news.caesar.elte.hu>...
> 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
Received on Thu Nov 18 2004 - 10:14:31 CET

Original text of this message