Why does a deterministic function execute an extra time in SQL?

Question

Given the following table

create table tmp_test as
 select mod(level, 5) as n
   from dual
connect by level <= 10
        ;

and this function

create or replace function test_deterministic (Pn in number
         ) return number deterministic is
begin
   dbms_output.put_line(Pn);
   dbms_lock.sleep(1);
   return Pn;
end;

It executes 6 times, taking 6 seconds:

SQL> select test_deterministic(n) from tmp_test;

TEST_DETERMINISTIC(N)
---------------------
                    1
                    2
                    3
                    4
                    0
                    1
                    2
                    3
                    4
                    0

10 rows selected.

1
2
3
4
0
1
Elapsed: 00:00:06.02

I would have expected this to execute 5 times. If I run this SELECT statement in SQL Developer or PL/SQL Developer it only executes 5 times. Equally, if I run this in Pl/SQL it executes 5 times:

SQL> begin
  2     for i in ( select test_deterministic(n) from tmp_test ) loop
  3        null;
  4     end loop;
  5  end;
  6  /
1
2
3
4
0
Elapsed: 00:00:05.01

Why is this function executed 6 times when called in SQL from SQL*Plus? I expected it to execute 5 times instead.

I'm on version 11.2.0.3.5 and the SQL*Plus client is release 11.2.0.1.0 (64bit).

Answer 1

Blame SQL*Plus, Ben. Your function works, in this situation, correctly. The extra value(1) you see is there because of arraysize value, and, mostly because of the way how SQL*Plus fetches rows. It first fetches first row and only then it starts to use arraysize for subsequent fetches. Every new fetch is a new database call, which forces your deterministic function to be evaluated. Try to set the arraysize to 1 or 2(same effect) and execute your select statement. The first row returns, and then, arraysize comes to play and each subsequent fetch will return couple of rows:

Arraysize is set to 1(two in fact)

SQL> set arraysize 1;
SQL> select test_deterministic(n) from tmp_test;

TEST_DETERMINISTIC(N)                                                           
---------------------                                                           
                    1                                                           
                    2                                                           
                    3                                                           
                    4                                                           
                    0                                                           
                    1                                                           
                    2                                                           
                    3                                                           
                    4                                                           
                    0                                                           

10 rows selected.

1                                                                               
2                                                                               
3                                                                               
4                                                                               
0                                                                               
1                                                                               
2                                                                               
3                                                                               
4                                                                               
0                                                                               
Elapsed: 00:00:10.10

The same query with much larger arraysize:

SQL> set arraysize 50;
SQL> select test_deterministic(n) from tmp_test;

TEST_DETERMINISTIC(N)                                                           
---------------------                                                           
                    1                                                           
                    2                                                           
                    3                                                           
                    4                                                           
                    0                                                           
                    1                                                           
                    2                                                           
                    3                                                           
                    4                                                           
                    0                                                           

10 rows selected.

1                                                                               
2                                                                               
3                                                                               
4                                                                               
0                                                                               
1                                                                               
Elapsed: 00:00:06.06

SQL> spool off;   

Any other client, whether it's SQL Developer or PL/SQL Developer lacks such behavior and gives correct output.