Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: Function called for row not in query result

Re: Function called for row not in query result

From: myst <yasbs_at_kocbank.com.tr>
Date: 3 Dec 2004 05:28:41 -0800
Message-ID: <fb6d5e2.0412030528.5f2aa2c6@posting.google.com>


When i look at the plan output, i see that your query tries to find the rows which "FN_X1"("T_X"."X_ID")=1. That's why the error disappears when you remove the outer predicate.

3 - filter("YASBS"."FN_X1"("T_X"."X_ID")=1)

PLAN_TABLE_OUTPUT




| Id | Operation | Name | Rows | Bytes | Cost |
|   0 | SELECT STATEMENT      |             |       |       |       |
|   1 |  MERGE JOIN           |             |       |       |       |
|   2 |   SORT JOIN           |             |       |       |       |
|*  3 |    TABLE ACCESS FULL  | T_X         |       |       |       |
|*  4 |   SORT JOIN           |             |       |       |       |
|   5 |    VIEW               | VW_NSO_1    |       |       |       |
|   6 |     SORT UNIQUE       |             |       |       |       |

PLAN_TABLE_OUTPUT



| 7 | TABLE ACCESS FULL| T1 | | | |

Predicate Information (identified by operation id):


   3 - filter("YASBS"."FN_X1"("T_X"."X_ID")=1)
   4 - access("T_X"."FIELD1"="VW_NSO_1"."$nso_col_1")
       filter("T_X"."FIELD1"="VW_NSO_1"."$nso_col_1")

Note: rule based optimization

janik_at_pobox.sk (Jan) wrote in message news:<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 Fri Dec 03 2004 - 07:28:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US