Oracle: El uso adecuado en la evaluación de variables BOOLEAN con valor NULO dentro de una condición CASE

Hoy me encontré una publicación de Steven Feuerstein, que me llamó la atención desde el primer momento.

La reproduzco totalmente a continuación como referencia, pero la pueden observar en el siguiente link: http://stevenfeuersteinonplsql.blogspot.com/2016/03/the-case-of-confusing-case.html

TUESDAY, MARCH 22, 2016

The Case of the Confusing CASE

This odd little piece of code was featured in the weekly PL/SQL Challenge quiz 12 March - 18 March 2016.

What do you think will be displayed after executing the following block?

DECLARE
   my_flag   BOOLEAN;
BEGIN
   CASE my_flag
      WHEN my_flag IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is NULL');
      WHEN TRUE
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE');
      ELSE
         DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE');
   END CASE;
END;
/

At first glance (if you are like me), you would say "my_flag is NULL", right?

After all, my_flag is initialized to NULL when declared, and I don't change the value.

But, lo and behold, you will see:

my_flag is FALSE

Curious, right?

So what's going on? Well, we have a very confused and confusing piece of code: I have written a simple CASE (which is of the form CASE expression WHEN ...), but then my WHEN clauses follow a typical searched CASE format (CASE WHEN expr1 ... WHEN expr2 ...).

CASE is a really wonderful feature in PL/SQL (and many other languages, of course), but you need to make sure you use it properly.

Definitivamente curioso el caso. Por supuesto no me pude quedar tranquilo y tuve que experimentar por cuenta propia como resolver el problema y la buena noticia es que lo encontré.

Veamos, partamos de definir el escenario. Mi máquina virtual con OEL 6.5 y Oracle Database 12c R1 12.1.0.2 SE2

SQL> select * from v$version;

SQL> set linesize 200
SQL> /

BANNER                                                                        
--------------------------------------------------------------------
Oracle Database 12c Standard Edition 2 Release 12.1.0.2 - 64bit 
PL/SQL Release 12.1.0.2                                     
CORE    12.1.0.2                                              
TNS for Linux: Version 12.1.0.2                     
NLSRTL Version 12.1.0.2                                 

El seudo código que responde de manera adecuada al problema planteado por Steven es el siguiente:

SQL> host more 10.sql
DECLARE
   my_flag   BOOLEAN;
BEGIN
   CASE WHEN my_flag is NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is NULL');
      WHEN TRUE
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE');
      ELSE
         DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE');
   END CASE;
END;
/
SQL> @10
my_flag is NULL

PL/SQL procedure successfully completed.

Si la condición de la variable boleana es de una vez evaluada durante la ejecución inicial del CASE y no posterior a su declaración dentro del CASE, el verdadero estado de la variable devuelve el valor correctamente.

Si inicializamos el valor de la variable en el código anónimo a "TRUE" el valor también es devuelto correctamente.

SQL> host more 10.sql
DECLARE
   my_flag   BOOLEAN := TRUE;
BEGIN
   CASE WHEN my_flag is NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is NULL');
      WHEN TRUE
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE');
      ELSE
         DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE');
   END CASE;
END;
/

Ejecutando el seudo código anterior

SQL> @10
my_flag is TRUE

PL/SQL procedure successfully completed.

Al probar con el seudo código original, el valor es incorrectamente devuelto.

SCRIPT ORIGINAL
SQL> DECLARE
  2     my_flag   BOOLEAN;
  3  BEGIN
  4     CASE my_flag
  5        WHEN my_flag IS NULL
  6        THEN
  7           DBMS_OUTPUT.PUT_LINE ('my_flag is NULL');
  8        WHEN TRUE
  9        THEN
 10           DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE');
 11        ELSE
 12           DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE');
 13     END CASE;
 14  END;
 15  /

my_flag is FALSE

PL/SQL procedure successfully completed.

SQL>

Resumen: 

La forma correcta de evaluar la condición de una variable con condición boleana en un CASE es utilizando la sintaxis:

...CASE WHEN my_flag is NULL   THEN
y no

CASE my_flag  WHEN my_flag IS NULL     THEN