Why does Oracle 12.1.0.2 skip function calls on result-cached tables?

Question

I reduced a real-life problem to the following test case:

DROP TABLE test_users;
CREATE TABLE test_users (
  user_id INTEGER,
  username VARCHAR2(32),
  first_name VARCHAR2(40),
  last_name VARCHAR2(40)
);
ALTER TABLE test_users ADD
(
  CONSTRAINT test_users_pk 
  PRIMARY KEY (user_id)
  USING INDEX
)
/
ALTER TABLE test_users ADD
(
  CONSTRAINT test_users_uq 
  UNIQUE (username)
  USING INDEX
)
/

INSERT INTO test_users VALUES (1, 'A', 'Sneezy', 'Timon');
INSERT INTO test_users VALUES (2, 'B', 'Dopey', 'Simba');
INSERT INTO test_users VALUES (3, 'C', 'Happy', 'Nala');
INSERT INTO test_users VALUES (4, 'D', 'Grumpy', 'Pumbaa');
COMMIT;
CREATE OR REPLACE FUNCTION test_function RETURN test_users.user_id%TYPE IS
    identifier VARCHAR2(32);
    user_id    users.user_id%TYPE;
  BEGIN
    SELECT sys_context('userenv', 'client_identifier') INTO identifier FROM dual;

    SELECT user_id INTO user_id FROM test_users WHERE upper(username) = upper(identifier);
    dbms_output.put_line('TEST_FUNCTION called!');
    RETURN user_id;

END test_function;

-- Testing with disabled result cache
ALTER TABLE test_users RESULT_CACHE (MODE DEFAULT);
DECLARE
  f users.first_name%TYPE;
  last_name  users.last_name%TYPE;
  identifier VARCHAR2(32);
  l_user_id users.user_id%type;
BEGIN
  dbms_output.put_line('setting the session identifier to A (Sneezy, Timon):');
  dbms_session.set_identifier('A');
  l_user_id := test_function();
  dbms_output.put_line('function call in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('variable use in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = l_user_id;
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('----');
  dbms_output.put_line('setting the session identifier to B (Dopey Simba):');
  dbms_session.set_identifier('B');
  l_user_id := test_function();
  dbms_output.put_line('function call in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('variable use in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = l_user_id;
  dbms_output.put_line(f || ' ' || last_name);

END;
/

-- Testing with enabled result cache
ALTER TABLE test_users RESULT_CACHE (MODE FORCE);
DECLARE
  f users.first_name%TYPE;
  last_name  users.last_name%TYPE;
  identifier VARCHAR2(32);
  l_user_id users.user_id%type;
BEGIN
  dbms_output.put_line('setting the session identifier to A (Sneezy, Timon):');
  dbms_session.set_identifier('A');
  l_user_id := test_function();
  dbms_output.put_line('function call in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('variable use in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = l_user_id;
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('----');
  dbms_output.put_line('setting the session identifier to B (Dopey Simba):');
  dbms_session.set_identifier('B');
  l_user_id := test_function();
  dbms_output.put_line('function call in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('variable use in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = l_user_id;
  dbms_output.put_line(f || ' ' || last_name);

END;
/

The indexes may or may not be necessary. The idea here is that the current user's name is in the session identifier. The test function turns the username in the session identifier into a user ID. Usernames can (in theory) change, and are used as log-in names. The user ID should never change and thus is the PK of the table.

What bothers me is, that when the result cache is switched on, the function in the WHERE criteria of this statement does not always get called:

SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();

The first PL/SQL block produces this result:

setting the session identifier to A (Sneezy, Timon):
TEST_FUNCTION called!
function call in WHERE criteria:
TEST_FUNCTION called!
Sneezy Timon
variable use in WHERE criteria:
Sneezy Timon
----
setting the session identifier to B (Dopey Simba):
TEST_FUNCTION called!
function call in WHERE criteria:
TEST_FUNCTION called!
Dopey Simba
variable use in WHERE criteria:
Dopey Simba

The second block produces this:

setting the session identifier to A (Sneezy, Timon):
TEST_FUNCTION called!
function call in WHERE criteria:
TEST_FUNCTION called!
Sneezy Timon
variable use in WHERE criteria:
Sneezy Timon
----
setting the session identifier to B (Dopey Simba):
TEST_FUNCTION called!
function call in WHERE criteria:
Sneezy Timon
variable use in WHERE criteria:
Dopey Simba

As you can see, there is one fewer call of TEST_FUNCTION and a wrong result. The way I understood result caching, a users table should be a perfect candidate. Many SELECTs, very little DML. And everything works as it should, unless I put my function call in the WHERE criteria. If I call the function, save the result in a variable and use that in the WHERE criteria, everything is fine.

Why is that? Is this a bug or a feature? Is the fact that the function uses data from the session identifier the main problem? Or should the result cache generally not be switched on for a whole table?

Edit: After reading some answers, I tried explicitly declaring the function as result cached, like this:

CREATE OR REPLACE FUNCTION test_function(identifier VARCHAR2 DEFAULT sys_context('userenv', 'client_identifier'))
    RETURN test_users.user_id%TYPE result_cache relies_on(test_users) IS
    user_id test_users.user_id%TYPE;
BEGIN
    SELECT user_id INTO user_id FROM test_users WHERE upper(username) = upper(identifier);
    dbms_output.put_line('TEST_FUNCTION called!');
    RETURN user_id;
END test_function;

This is very similar to the example from the Oracle documentation in the comment below.

Sadly, this did not help. Calling the function with or without parantheses does not make a difference for me (but see my comment below). Only way I found to consistently get the expected results is disabling the result cache for the table.

Answer 1

The problem is not the caching on your TEST_USERS table. The problem is that the results of your TEST_FUNCTION function are being cached and changing the session identifier is not invalidating those results.

To avoid this problem, first change the definition of TEST_FUNCTION to this:

CREATE OR REPLACE FUNCTION test_function ( identifier VARCHAR2 DEFAULT sys_context('userenv', 'client_identifier') ) RETURN test_users.user_id%TYPE IS
    --identifier VARCHAR2(32);
    user_id    test_users.user_id%TYPE;
  BEGIN
    --SELECT sys_context('userenv', 'client_identifier') INTO identifier FROM dual;

    SELECT user_id INTO user_id FROM test_users WHERE upper(username) = upper(identifier);
    dbms_output.put_line('TEST_FUNCTION called!');
    RETURN user_id;

END test_function;

Then, when you use it in the WHERE clause, call it like so:

SELECT first_name, last_name 
INTO f, last_name 
FROM test_users WHERE user_id = test_function;

Important: notice I did not use test_function() (i.e., notice there are no parentheses).

Why do the parenthesis matter? I don't know. I don't think they should. But this works in my 12.1.0.2 instance.

Running a variant of the test case you posted, I get these results at the end:

function call in WHERE criteria with no parens...: Dopey Simba
function call in WHERE criteria with parens...: Sneezy Timon