Short circuit evaluation and the great Unknown

One of the nice things about PL/SQL is that it implements short circuit evaluation, a performance enhancement which takes advantage of the fact that an expression using logical AND or OR does not necessarily to evaluate both operands to determine the overall result.

For an expression using AND, if the first operand is not TRUE, the overall expression cannot be TRUE; for one using OR, if the first operand is TRUE, the overall expression must be TRUE. In the case of AND, what if the first operand is Unknown? It seems to depend on how the expression is used.

In my examples below, I have an expression that looks up an array. When an array is accessed with a key that is null, it will raise an exception. If short circuit evaluation is applied, the array is never accessed so the exception is not raised.

In the first example below, the PL/SQL engine never evaluates the second expression since the first expression ('abc' = p_param) is not TRUE:

declare
    p_param varchar2(10);
    l_result boolean := false;
    l_arr apex_t_varchar2;
begin
    if 'abc' = p_param and l_arr(p_param) = 'bla' then
        l_result := true;
    end if;
end;

This does not apply if the expression is being assigned to a variable. In the second example below, the exception ORA-06502: PL/SQL: numeric or value error: NULL index table key value is raised:

declare
    p_param varchar2(10);
    l_result boolean;
    l_arr apex_t_varchar2;
begin
    l_result := 'abc' = p_param and l_arr(p_param) = 'bla';
end;

If the first expression were to result in FALSE, it runs without error. If the first expression is Unknown (NULL), the second operand must be evaluated to determine whether to assign FALSE or NULL to the result.

A workaround is to use an IF statement to make the evaluation order explicit:

declare
    p_param varchar2(10);
    l_result boolean := false;
    l_arr apex_t_varchar2;
begin
    if 'abc' = p_param then
        l_result := l_arr(p_param) = 'bla';
    end if;
end;

Thanks to Connor for clearing up my understanding for this one.

8/10/2020 updated with better example code – instead of comparing to a literal null (which is never right), we compare to a variable which may or may not be null at runtime.

Jeffrey Kemp 7 October 2020

oracle / PL/SQL / short circuit evaluation /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.