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

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 (DOT) hu> wrote

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
--------------------------------------------------------------------------------

---------------------------------------------------------------------
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 (DOT) sk (Jan) wrote in message news:<81511301.0411180114.134df556 (AT) posting (DOT) google.com>...