Hi,
I seem to have stumbled upon Oracle behaviour I can not explain, so I've come to the magical place called Ask Tom for guidance.
I have created the following example to make it as short as possible while still reproducing my problem:
2 tables, 2 functions and the data...
CREATE TABLE t1 (id_pk INTEGER PRIMARY KEY, fld1 VARCHAR2(10));
CREATE TABLE t2 (id_pk INTEGER PRIMARY KEY, fld2 VARCHAR2(3));
CREATE OR REPLACE FUNCTION fn1 (vfld VARCHAR2) RETURN VARCHAR2
AS
BEGIN
dbms_output.put_line('fn1: ' || vfld);
RETURN vfld;
END fn1;
CREATE OR REPLACE FUNCTION fn2 (vfld VARCHAR2) RETURN INTEGER
AS
BEGIN
dbms_output.put_line('fn2: ' || vfld);
RETURN 0;
END fn2;
INSERT INTO t1 VALUES (1, 'A0000001');
INSERT INTO t1 VALUES (2, 'A0000002');
INSERT INTO t1 VALUES (3, 'A0000003');
INSERT INTO t1 VALUES (4, 'A0000004');
INSERT INTO t2 VALUES (1, 'AAA');
INSERT INTO t2 VALUES (2, 'AAA');
INSERT INTO t2 VALUES (3, 'BBB');
INSERT INTO t2 VALUES (4, 'BBB');
Executing the following query:
SELECT fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='A'
AND t2.fld2 = 'AAA'
A0000001
A0000002These are the records I expected with this query.
When I use the functions with a with-clause, I seem to get the correct results too, no records:
WITH w_test AS (
SELECT fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='A'
AND t2.fld2 = 'AAA')
SELECT fn1(fld1) FROM w_test WHERE fn2(fn1(fld1))=1;
no rows selectedBut... If I look at the dbms_output, I see something very different:
fn1: A0000001
fn2: A0000001
fn1: A0000002
fn2: A0000002
fn1: A0000003
fn2: A0000003
fn1: A0000004
fn2: A0000004
fn1: A0000001
fn2: A0000001
fn1: A0000002
fn2: A0000002
fn1: A0000003
fn2: A0000003
fn1: A0000004
fn2: A0000004
Every record from the t1 table has been processed by both functions, even though we have an inner join on t2 and a where clause specifying t2.fld2 should be 'AAA'.
Can you clarify why this happens?
We have a very strange 20 year old mainframe in use which gets replicated every day to an oracle database. In one of the ID fields, if an ID starts with a number, the whole ID is numeric and a new record should just add 1. If it starts with a letter, a specific set of rules is applied. Not ideal in a long shot, but I have no control there.
Now how does this effect the above example?
If I change just a bit in the function and data:
CREATE OR REPLACE FUNCTION fn1 (vfld VARCHAR2) RETURN VARCHAR2
AS
BEGIN
dbms_output.put_line('fn1: ' || vfld);
RETURN vfld;
END fn1;
CREATE OR REPLACE FUNCTION fn2 (vfld VARCHAR2) RETURN INTEGER
AS
iret INTEGER;
BEGIN
dbms_output.put_line('fn2: ' || vfld);
iret := TO_NUMBER(vfld);
RETURN iret;
END fn2;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
INSERT INTO t1 VALUES (1, '10000001');
INSERT INTO t1 VALUES (2, '10000002');
INSERT INTO t1 VALUES (3, '1A000003');
INSERT INTO t1 VALUES (4, '1A000004');
INSERT INTO t2 VALUES (1, 'AAA');
INSERT INTO t2 VALUES (2, 'AAA');
INSERT INTO t2 VALUES (3, 'BBB');
INSERT INTO t2 VALUES (4, 'BBB');First running the simple select part:
SELECT fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='1'
AND t2.fld2 = 'AAA'
10000001
10000002Now the following happens when using the WITH clause with the functions:
WITH w_test AS (
SELECT fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='1'
AND t2.fld2 = 'AAA')
SELECT fn1(fld1) FROM w_test WHERE fn2(fn1(fld1))=1;
Error at line 6
ORA-06502: PL/SQL: numeric or value error : character to number conversion error.
ORA-06512: in "FN2", line 6
Of course this happens because all the rows from t1 are being processed by both functions.
This is magic to me. I don't understand magic. Teach me magic, please.
Kind regards,
Martin
There's nothing magical about the with clause. If you add the double function call to the straight select you get the same behaviour:
SQL> SELECT fld1 FROM t1
2 INNER JOIN t2 ON t1.id_pk = t2.id_pk
3 WHERE SUBSTR(fld1,1,1)='A'
4 AND t2.fld2 = 'AAA'
5 and fn2(fn1(fld1))=1;
no rows selected
fn1: A0000001
fn2: A0000001
fn1: A0000002
fn2: A0000002
fn1: A0000003
fn2: A0000003
fn1: A0000004
fn2: A0000004
This is easiest to understand by looking at the execution plans:
SELECT /*+ gather_plan_statistics */fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='A'
AND t2.fld2 = 'AAA'
and fn2(fn1(fld1))=1;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PREDICATE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ gather_plan_statistics */fld1 FROM t1 INNER JOIN t2 ON
t1.id_pk = t2.id_pk WHERE SUBSTR(fld1,1,1)='A' AND t2.fld2
= 'AAA' and fn2(fn1(fld1))=1
Plan hash value: 2987537596
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
|* 3 | TABLE ACCESS FULL | T1 |
|* 4 | INDEX UNIQUE SCAN | SYS_C006046 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((SUBSTR("T1"."FLD1",1,1)='A' AND
"FN2"("FN1"("T1"."FLD1"))=1))
4 - access("T1"."ID_PK"="T2"."ID_PK")
5 - filter("T2"."FLD2"='AAA')
You can see at step 3 it does a full table scan of T1. And applies the:
fn2(fn1(fld1))=1
predicate at this point.
There's 4 rows in T1. With a call to FN1 and FN2 for each that gives 8 dbms_output lines. Which is what we see. This is why you get the value error in the second example. Oracle tries to convert every row in t1 to a number first. Which it can't, because some contain letters!
You can overcome this using by making the query in the with clause non-mergeable. For example, by adding rownum to it:
CREATE OR REPLACE FUNCTION fn1 (vfld VARCHAR2) RETURN VARCHAR2
AS
BEGIN
dbms_output.put_line('fn1: ' || vfld);
RETURN vfld;
END fn1;
/
CREATE OR REPLACE FUNCTION fn2 (vfld VARCHAR2) RETURN INTEGER
AS
iret INTEGER;
BEGIN
dbms_output.put_line('fn2: ' || vfld);
iret := TO_NUMBER(vfld);
RETURN iret;
END fn2;
/
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
INSERT INTO t1 VALUES (1, '10000001');
INSERT INTO t1 VALUES (2, '10000002');
INSERT INTO t1 VALUES (3, '1A000003');
INSERT INTO t1 VALUES (4, '1A000004');
INSERT INTO t2 VALUES (1, 'AAA');
INSERT INTO t2 VALUES (2, 'AAA');
INSERT INTO t2 VALUES (3, 'BBB');
INSERT INTO t2 VALUES (4, 'BBB');
SQL> WITH w_test AS (
2 SELECT fld1 FROM t1
3 INNER JOIN t2 ON t1.id_pk = t2.id_pk
4 WHERE SUBSTR(fld1,1,1)='1'
5 AND t2.fld2 = 'AAA')
6 SELECT fn1(fld1) FROM w_test WHERE fn2(fn1(fld1))=1;
SELECT fn1(fld1) FROM w_test WHERE fn2(fn1(fld1))=1
*
ERROR at line 6:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CHRIS.FN2", line 6
fn1: 10000001
fn2: 10000001
fn1: 10000002
fn2: 10000002
fn1: 1A000003
fn2: 1A000003
SQL>
SQL> WITH w_test AS (
2 SELECT fld1, rownum FROM t1
3 INNER JOIN t2 ON t1.id_pk = t2.id_pk
4 WHERE SUBSTR(fld1,1,1)='1'
5 AND t2.fld2 = 'AAA')
6 SELECT fn1(fld1) FROM w_test WHERE fn2(fn1(fld1))=1;
no rows selected
fn1: 10000001
fn2: 10000001
fn1: 10000002
fn2: 10000002
This ensures that Oracle does fn2(fn1(fld1))=1 after joining the tables in the with.