| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Function called for row not in query result
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
| 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
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
 |
 |