Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Tom.

Asked: March 02, 2018 - 5:00 pm UTC

Last updated: March 06, 2018 - 2:20 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi Tom,
I've always understood it to be the case that a=b and a!=b both evaluate to FALSE if one or both is NULL. However, I'm seeing some odd behavior which I'm trying to distill and understand.

DECLARE
    v_Var1 VARCHAR2(12) := 'A';
    v_Var2 VARCHAR2(12) := NULL;
BEGIN
    --WORKS WHEN BOTH ARE NULL OR BOTH ARE NON-NULL AND EQUAL
    IF (v_Var1 = v_Var2 OR (v_Var1 IS NULL AND v_Var2 IS NULL)) THEN
        DBMS_OUTPUT.PUT_LINE('Same');
    END IF;
    --SAME TEST AS ABOVE, wrapped in NOT
    IF NOT((v_Var1 = v_Var2 OR (v_Var1 IS NULL AND v_Var2 IS NULL))) THEN
        DBMS_OUTPUT.PUT_LINE('Different');
    END IF;
    --WORKS, BUT I DON'T KNOW WHY
    IF NOT((NVL(v_Var1 = v_Var2,false) OR (v_Var1 IS NULL AND v_Var2 IS NULL))) THEN
        DBMS_OUTPUT.PUT_LINE('Different (if NULL=NULL is NULL)');
    END IF;
END;


Playing around with v_Var1 and v_Var2, when they're both NULL, or both non-NULL and equal, the first test prints 'Same' (which makes perfect sense, and is a textbook example).

When they differ (specifically when one is NULL and one isn't), I would have expected that the second test would print 'Different', but it doesn't. The logic is intended to be NOT <the case I used to test whether they are the same>.

So I thought to myself, "Maybe v_Var1 = v_Var2 doesn't really resolve to FALSE when one is NULL - maybe it resolves to NULL, and NULL with any operation (e.g. OR, NOT) yields NULL" - and so I tried the third one, which surprisingly worked. But if it was true that it evaluates to NULL, I'd expect that when both variables were NULL, the first test would have evaluated as (NULL or (TRUE and TRUE)), which I'd have expected to result in NULL, and therefore be treated as FALSE. So more likely, I just lucked into something that works for a reason I don't understand.

All that said -
1) why doesn't the first 'Different' test work?
2) what should I have used instead?
3) what's actually happening in the third test?

Thanks!

and Connor said...

Easily explained by breaking it down, and printing some results as we go:

SQL> set serverout on
SQL> DECLARE
  2      v_Var1 VARCHAR2(12) := 'A';
  3      v_Var2 VARCHAR2(12) := NULL;
  4
  5      b boolean;
  6
  7      procedure print_bool(p boolean) is
  8      begin
  9        if p is null then
 10           dbms_output.put_line('null');
 11        elsif p then
 12           dbms_output.put_line('true');
 13        else
 14           dbms_output.put_line('false');
 15        end if;
 16     end;
 17  BEGIN
 18      b := (v_Var1 = v_Var2 OR (v_Var1 IS NULL AND v_Var2 IS NULL)) ;
 19      print_bool(b);
 20
 21      b := NOT((v_Var1 = v_Var2 OR (v_Var1 IS NULL AND v_Var2 IS NULL))) ;
 22      print_bool(b);
 23
 24      b := NOT((NVL(v_Var1 = v_Var2,false) OR (v_Var1 IS NULL AND v_Var2 IS NULL))) ;
 25      print_bool(b);
 26  END;
 27  /
null
null
true

PL/SQL procedure successfully completed.


So lets break down test #1

SQL> set serverout on
SQL> DECLARE
  2      v_Var1 VARCHAR2(12) := 'A';
  3      v_Var2 VARCHAR2(12) := NULL;
  4
  5      b boolean;
  6
  7      procedure print_bool(p boolean) is
  8      begin
  9        if p is null then
 10           dbms_output.put_line('null');
 11        elsif p then
 12           dbms_output.put_line('true');
 13        else
 14           dbms_output.put_line('false');
 15        end if;
 16     end;
 17  BEGIN
 18
 19  --
 20  -- Components of test #1
 21  --
 22      b := v_Var1 = v_Var2;
 23      print_bool(b);
 24      b := (v_Var1 IS NULL AND v_Var2 IS NULL);
 25      print_bool(b);
 26
 27  END;
 28  /
null
false

PL/SQL procedure successfully completed.


The "sides" are null and false. And "null OR false" is null.

This is why the NVL works and the others do not.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

weird

Racer I., March 05, 2018 - 12:28 pm UTC

Hi,

DECLARE
    v_BN BOOLEAN := NULL;
    v_BT BOOLEAN := TRUE;
    v_BF BOOLEAN := FALSE;
BEGIN
    IF (v_BN OR v_BT) THEN
        DBMS_OUTPUT.PUT_LINE('NULL OR TRUE = TRUE');
    END IF;
    IF NOT(v_BN OR v_BT) THEN
        DBMS_OUTPUT.PUT_LINE('NOT(NULL OR TRUE) = TRUE');
    END IF;
    IF (v_BN OR v_BF) THEN
        DBMS_OUTPUT.PUT_LINE('NULL OR FALSE = FALSE');
    END IF;
    IF NOT(v_BN OR v_BF) THEN
        DBMS_OUTPUT.PUT_LINE('NOT(NULL OR FALSE) = FALSE');
    END IF;
END;

NULL OR TRUE = TRUE


https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-language-fundamentals.html#GUID-640DB3AA-15AF-4825-BD6C-1D4EB5AB7715

https://livesql.oracle.com/apex/livesql/file/content_FXEX3BA5QNS8NXUDZ8T84MBLK.html

regards,

Tom, March 05, 2018 - 1:36 pm UTC

That was helpful, thanks. So what I was missing is that (null or false)=null, whereas (null or true)=true, which makes sense.

Consider declaring subtype of not null

Evan, March 05, 2018 - 7:11 pm UTC

The issue of null values for Booleans can be problematic. One way of addressing this is to declare a subtype and make it not null so the only values can be true or false.

For example,

DECLARE
    SUBTYPE nn_boolean IS BOOLEAN NOT NULL;
    
    --This causes a compile error
    --l_my_boolean        nn_boolean;
    
    l_my_boolean        nn_boolean := FALSE;
BEGIN 
    --This causes a comiple error
    --l_my_boolean := null;

    l_my_boolean := (1 = 1);
END;


You may also want to consider doing this for other data types such as number, varchar2, date, etc. depending on your requirements.
Connor McDonald
March 06, 2018 - 2:20 am UTC

nice input.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library