SQL & PL/SQL

Hi,

we hit an issue and actually i'm not sure if it's a but or it's an expected behavior. you can test this example with the default EMP-Table…

For the test you will need a dummy function that makes a output every time it get's called:

CREATE OR REPLACE FUNCTION put_line_error(empno IN NUMBER) RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Test: ' || empno);
  RETURN 1;
END;
/

That's the test:

SQL> set serveroutput on;
SQL> SELECT empno -- result 4 rows
2 FROM (SELECT emp_filtered.empno, -- result 4 rows
3 put_line_error(empno => emp_filtered.empno) AS function_value -- called 4 times
4 FROM (SELECT empno -- result 4 rows
5 FROM emp -- 14 rows
6 WHERE emp.job = 'CLERK') emp_filtered)
7* WHERE function_value = 1;

EMPNO 
________ 
7369 
7876 
7900 
7934 

Test: 7369
Test: 7876
Test: 7900
Test: 7934

SQL> SELECT empno -- result 4 rows
2 FROM (SELECT emp_filtered.empno, -- result 4 rows
3 put_line_error(empno => emp_filtered.empno) AS function_value -- called 14 times
4 FROM (SELECT empno -- result 4 rows
5 FROM emp -- 14 rows
6 WHERE emp.job = (SELECT 'CLERK'
7 FROM dual)) emp_filtered)
8* WHERE function_value = 1;

EMPNO 
________ 
7369 
7876 
7900 
7934 

Test: 7369
Test: 7499
Test: 7521
Test: 7566
Test: 7654
Test: 7698
Test: 7782
Test: 7788
Test: 7839
Test: 7844
Test: 7876
Test: 7900
Test: 7902
Test: 7934

SQL> 

As you can see in the second example the function gets called more often in the second example.

Any ideas or suggestions if this is expceted or a bug? Actually I'm not happy with the idea of expected behavior.

There is a workaround at least for this example if you create an index on EMP(JOB). But I'm not sure if this is the all-time solution for this problem.

Thanks in advance!

Regards,

Roland