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
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
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.