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.
Philipp Salvisberg
8 October 2020 - 9:33 pm
Thanks for this blog post.
I suggest to change the expression from “‘abc’ = null” to “‘abc’ is null”. Even if “= null” works in these examples, it’s a bit distracting IMO, as it’s a bug I find in production code… See also https://trivadis.github.io/plsql-and-sql-coding-guidelines/v3.6/4-language-usage/2-variables-and-types/1-general/g-2150/ .
Jeffrey Kemp
8 October 2020 - 9:56 pm
Hi Philipp,
You’re correct, although it’s important to the point of this post that the result of the first expression is Unknown; ‘abc’ is null is definitely False.
I’ll update it to a better example so that it’s obvious we’re not comparing to a literal null (merely a variable that happens to be null).
Thanks
Jeff