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

Home -> Community -> Usenet -> c.d.o.server -> Function called for row not in query result

Function called for row not in query result

From: Agoston Bejo <gusz1_at_freemail.hu>
Date: Tue, 16 Nov 2004 18:02:31 +0100
Message-ID: <cndc45$fli$1@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 Tue Nov 16 2004 - 11:02:31 CST

Original text of this message

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