Can I rely on functions being executed first in SQL

Question

Please consider the following script:

create or replace function f(p_limit in integer) return integer as
begin
  set_global_context ('limit', p_limit);
  return p_limit;
end;
/

create view v as 
select level as val from dual connect by level<=sys_context('global_context','limit');

select f(2), v.* from v;

/*
F(2)                   VAL                    
---------------------- ---------------------- 
2                      1                      
2                      2                      
*/

select f(4), v.* from v;

/*
F(4)                   VAL                    
---------------------- ---------------------- 
4                      1                      
4                      2                      
4                      3                      
4                      4                      
*/

Can I rely on f(x) being executed before the context is read inside the view, as it has been in this test case run on 10.2?

Answer 1

No.

If you re-write your view with the context filtering against the where clause (instead of the connect by), you'll get the previously set value for the context:

create table t as 
 select rownum r from dual connect by level <= 10;

create or replace view v as 
  select r val from t where r <=sys_context('global_context','limit');

select f(2), v.* from v;

F(2) VAL
---- ---
   2   1 
   2   2 

select f(4), v.* from v;

F(4) VAL
---- ---
   4   1 
   4   2 

select f(4), v.* from v;

F(4) VAL
---- ---
   4   1 
   4   2 
   4   3 
   4   4 

As the where clause is evaluated before the columns are selected, the value passed to the function isn't set until after the context is read. The location of the sys_context call in your query (select, where, group by, etc.) will affect exactly when this value is set.

Answer 2

Generally speaking you can not safely assume anything about the order in which your DBMS will do things when evaluating a single SQL statement. This is why many DBMS will not allow functions used that way to have side effects (i.e. MSSQL won't allow functions to set global/connection state, which you are doing there, or alter table contents). A series of statements must be executed in a way that makes sense from one step to the next (i.e. they are run serially, or in such a way as you can't tell they were not), but within a single statement the query planner has free reign as long it doesn't introduce ambiguity where it does not already exist (in your example ambiguity already exists because the function has a side effect affects the view).

If the query planner were being bright enough to detect that the view is affected by the side-effects of the function, what would it do if you joined in another view that called that function potentially with different input values? It could quite quickly get very hairy - this sort of thing is why generally, in any programming context, functions should not have effects beyond their own output.

In this specific example I would say that it is unlikely that f(x) will be called first, as it is int he "display" part of the statement: the result set from the view is likely to be retrieved before any functions within the list of columns to return are evaluated. Of course this will vary depending on the DBMS used: I'm no Oracle expert and your test results show that the function does seem to be being called first in these instances. But I would be wary of relying on execution order within any single SQL statement all the same - even if it always works the way you expect right now it may not do so in future revisions (unless it is officially documented somewhere that execution will always go this way around).

Answer 3

The documentation only promises that "The optimizer first evaluates expressions and conditions containing constants as fully as possible." (10.2, 11.2). You're not guaranteed that it will evaluate any particular expression first, or that it won't change that order from time to time (a new patchlevel within the same release?).