Is this a PL/SQL bug?

Question

Here's an excerpt from some PL/SQL code that I believe demonstrates a PL/SQL bug:

if guid_ is null then
   dbms_output.put_line('guid_ is null: ' || guid_);
end if;

When these lines are executed, it prints

guid_ is null: 07D242FCC55000FCE0530A30D4928A21

I am on Oracle 11R2

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

I can reproduce this with the following types and anonymous block. Sorry for the length, but I believe I cannot shorten it any more:

create type tq84_t as table of varchar2(32);
/

create type tq84_o as object (
  dummy number(1),

  not final member procedure clear

) not final;
/

show errors

create type tq84_d under tq84_o (

  g varchar2(32),
  constructor function tq84_d return self as result,

  overriding member procedure clear


);
/
show errors


create package tq84_h as

    t tq84_t;

end tq84_h;
/
show errors



create package body tq84_h as
begin

  t := tq84_t();
end;
/
show errors

create type body tq84_o as

   member procedure clear is begin
      null;
   end clear;

end;
/

create type body tq84_d as

  constructor function tq84_d return self as result is
  begin

      g := sys_guid;
      return;

  end tq84_d;

  overriding member procedure clear is begin

      tq84_h.t.extend;
      tq84_h.t(tq84_h.t.count) := g;

      g := null;

  end clear;

end;
/
show errors


declare

  b  tq84_o;  -- Change to tq84_d ...

  guid_ varchar2(32);

begin

  b := new tq84_d;

  guid_ := treat(b as tq84_d).g;

  b.clear;

  if guid_ is null then
     dbms_output.put_line('guid_ is null: ' || guid_);
  end if;


end;
/

drop type tq84_t;
drop type tq84_d;
drop type tq84_o;
drop package tq84_h;

Note also, that when I change b tq84_o to b tq84_d, the error does not occur anymore.

Can someone verify if this is happening on other systems as well?

Answer 1

To me this is a bug. In the IF the variable guid_ is not treated the same as in the the string concatenation for the put_line. What I find strange is that before the b.clear statement the is null works:

declare
  b  tq84_o;  -- Change to tq84_d ...
  guid_ varchar2(32);
begin
  b := new tq84_d;
  guid_ := treat(b as tq84_d).g;

  if guid_ is null then
     dbms_output.put_line('before clear: guid_ is  null: ' || guid_);
  end if;

  b.clear;

  if guid_ is null then
     dbms_output.put_line('after clear: guid_ is null: ' || guid_);
  end if;
end;
/

Output:

after clear: guid_ is null: 07D43ACB728A2173E054A0481C66CF28

I workaround the problem when returning the GUID from a function:

declare
  b  tq84_o;  -- Change to tq84_d ...
  guid_ varchar2(32);
  function get_guid 
  return varchar2 is 
  begin 
    return treat(b as tq84_d).g;
  end;  
begin
  b := new tq84_d;
  guid_ := get_guid; -- treat(b as tq84_d).g;

  if guid_ is null then
     dbms_output.put_line('before clear: guid_ is  null: ' || guid_);
  end if;

  b.clear;

  if guid_ is null then
     dbms_output.put_line('after clear: guid_ is null: ' || guid_);
  end if;
end;
/

The above code does not go into neither of the if guid_ is null. So to me this proves it:

This is a bug.

Answer 2

It looks like an Oracle bug. I am seeing the same thing on our instance of Oracle Enterprise 11.2.0.3.0 (64bit).

After playing with it a little, it's pretty clear that the bug is in the if condition: guid_ is null is evaluating to true even though it contains a value.

---

My earlier comment regarding else no longer appears to be true. Either I inadvertently changed the test script in some way or the bug is somewhat unpredictable.

---

I seem to reliably make the else scenario work be following these steps:

1. Change the if to:

if guid_ is null then dbms_output.put_line('guid_ is null: ' || guid_); else dbms_output.put_line('guid_ is not null: ' || guid_); end if;

2. Run the script; the output returns the null message.
3. Change declaration from tq84_o to tq84_d.
4. Run the script; the output returns the not null message.
5. Change declaration back to tq84_o.
6. Run the script; the output returns the not null message.

My output from this process is:

guid_ is null: 07D41C8BCE696EA3E0539014190A7DA0
guid_ is not null: 07D41C8BCE7D6EA3E0539014190A7DA0
guid_ is not null: 07D41C8BCE916EA3E0539014190A7DA0

---

It looks like the corrected behavior has nothing to do with the else. If I simply take the original script and run it twice in the same session, it works incorrectly the first time and correctly the second time.

---

As @hol observed, this bug also goes away if the code is in a function or stored procedure. In my case, I changed the entire anonymous block to a procedure then ran the procedure and the bug did not occur.