Bug Hunting

It's early days but I think I became the joint father of a bug report this week. This is bug number 9219636.

SQL> CREATE TYPE INTEGER_ARRAY_T AS TABLE OF INTEGER
  2  /

Type created.

SQL> 
SQL> CREATE TABLE V_SESSION_VALID (
  2          SID NUMBER ,
  3          SESSION_ID NUMBER NOT NULL,
  4                  CONSTRAINT SID_PK PRIMARY KEY (SID) VALIDATE ,
  5                  CONSTRAINT SESSIONID_UK UNIQUE (SESSION_ID) VALIDATE)
  6  /

Table created.

SQL> 
SQL> CREATE TABLE SESSION_VIEW ( VIEW_ID NUMBER ,
  2          SESSION_ID NUMBER NOT NULL,
  3          ROW_COUNT NUMBER NOT NULL,
  4                  CONSTRAINT VIEWID_PK PRIMARY KEY (VIEW_ID) VALIDATE)
  5  /

Table created.

SQL> 
SQL> CREATE TABLE BATCH_SESSION (BATCH_ID NUMBER NOT NULL , ROW_ID NUMBER NOT NULL , 
                                 SID NUMBER NOT NULL )
  2  /

Table created.

SQL> 
SQL> declare
  2          l_sessions INTEGER_ARRAY_T;
  3  begin
  4          select s.SESSION_ID
  5          bulk collect into l_sessions
  6          from V_SESSION_VALID s
  7                  join SESSION_VIEW v on (s.SESSION_ID = v.SESSION_ID)
  8                  join BATCH_SESSION b on (s.SID = b.SID)
  9          where  v.ROW_COUNT > 0
 10          and b.BATCH_ID between 1 and 1000
 11          for update of s.SESSION_ID;
 12  end;
 13  /
        select s.SESSION_ID
        *
ERROR at line 4:
ORA-06550: line 4, column 2:
PL/SQL: ORA-00918: column ambiguously defined
ORA-06550: line 4, column 2:
PL/SQL: SQL Statement ignored

This works in all versions prior to 11.2.0.1, which is where it was identified during some 11gR2 RAC testing of the application I'm working on. There are actually quite a few notes about this type of error kicking around on My Oracle Support, but in most cases they aren't bugs, but a tightening of ambiguous column checking, which is a good thing. However, if your app does use ANSI join syntax, keep an eye out for 11gR2 catching out previously suspect SQL statements when you're upgrading.

However, there is no ambiguous column definition here. If I convert it to traditional Oracle join syntax, it works, as does commenting out the offending part of the code.

SQL> declare
  2          l_sessions INTEGER_ARRAY_T;
  3  begin
  4          select s.SESSION_ID
  5          bulk collect into l_sessions
  6          from V_SESSION_VALID s
  7                  join SESSION_VIEW v on (s.SESSION_ID = v.SESSION_ID)
  8                  join BATCH_SESSION b on (s.SID = b.SID)
  9          where  v.ROW_COUNT > 0
 10          and b.BATCH_ID between 1 and 1000;
 11  -- for update of s.SESSION_ID;
 12  end;
 13  /

PL/SQL procedure successfully completed.

So why do I say joint father of the bug report? Because having raised the SR, we delayed our 11gR2 upgrade work for unassociated reasons and so I didn't put the work in to provide the proper test case that Oracle Support requested. A couple of weeks later, with the SR abandoned for now, one of the DBAs mentioned a bug he'd found on 11gR2 and my first question was 'ANSI join syntax?'. Sure enough, it was the same as our example so he picked up the SR and ran with it and put together the test case here, which is also stripped of any company information. So we might have hit it first, but Cristian Banoiu put in the hard work. I'm sure he was pretty proud when it was assigned a bug number and I believe it's currently in development.