473,320 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Function called for row not in query result

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

Jul 19 '05 #1
2 4408
Jan
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" <gu***@freemail.hu> wrote in message news:<cn**********@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

Jul 19 '05 #2
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


ja***@pobox.sk (Jan) wrote in message news:<81**************************@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" <gu***@freemail.hu> wrote in message news:<cn**********@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

Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: lawrence | last post by:
Here I have two functions, the first returning a value to the second. When I put print_r() on the last line of the first function, I can see that the correct values are in the array as expected....
1
by: David | last post by:
Hi everyone. I have read every page that Google returns on this topic, but can't find anything that resolves my problem. Basically, I have an Access Database that does a number of different...
7
by: Jimakos Bilakis | last post by:
Hi guys! I'm using the C++ Builder 6 Enterprise Edition where I create some tables in Paradox and with the help of a structure i pass my data from the form (Edit boxes) to the Paradox table with...
0
by: Crystal | last post by:
I have created a small program that prints out an organization chart in PHP using the GD lib's imagecreate, imageline and imagestring functions. Basically, a user selects an employee from a...
5
by: marcsirois | last post by:
I have an SQL Query that looks like this. I simplified it for the purpose of this example, but it's still a runnable query SELECT TOP 1 FundedPositions.PositionNumber AS , (select top 1...
4
by: Akhenaten | last post by:
For whatever reason I can't get my return value to pass outside of the function I've created. I've tested all the responses within the function so I know the data is there and the sql query is...
2
by: Agoston Bejo | last post by:
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...
9
by: JRough | last post by:
I'm getting the error message that the parameter passed to the function is not a valid resource. The parameter is $result and it is from a query in a switch statement. What do I have to do to get...
10
by: JRough | last post by:
I have a function definition error: Warning: Missing argument 2 for makexcldata(), called on line 123 and defined in on line 149 "Warning: mysql_fetch_row(): supplied argument is not a valid...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.