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_APPLICATI ON_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
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_APPLICATI ON_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
Comment