Strange bug with MEMBER OF instruction when running query in parallel mode

The following has been tested on three different database first is Oracle EE 11.2.0.4 PSU 11.2.0.4.3 no RAC, OS is AIX 7.1 second is Oracle EE 11.2.0.4 PSU 11.2.0.4.3 RAC, OS is AIX 7.1 third is Oracle EE 1.2.0.4 PSU 11.2.0.4.161018 no RAC, OS is AIX 6.1

The following is a model I've created to reproduce the bug :

CREATE OR REPLACE TYPE NUMBER_TABLE AS TABLE OF NUMBER(10); CREATE TABLE test_bug(id_1 NUMBER(10), data_1 NUMBER(8));

CREATE TABLE test_bug_filtre(id_1 NUMBER(10));

INSERT INTO test_bug VALUES(1, 9);

COMMIT; DECLARE
  aL_Data1 NUMBER_TABLE;
  nL_Limit NUMBER(10) := 1024;
BEGIN
  SELECT LEVEL
    BULK COLLECT INTO aL_Data1
    FROM dual
      CONNECT BY LEVEL <= nL_Limit;

  DBMS_OUTPUT.PUT_LINE('aL_Data1.COUNT : '||aL_Data1.COUNT);

  EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DML PARALLEL 8';   EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8';   INSERT /*+ APPEND */
    INTO test_bug_filtre(

         id_1)
  SELECT a.id_1
    FROM test_bug a
   WHERE a.data_1 MEMBER aL_Data1;

  DBMS_OUTPUT.PUT_LINE('test_bug_filtre INSERT : '||SQL%ROWCOUNT);   COMMIT;
END;
/

I've run the test multiple time with different value for nL_Limit, here are the result I have for 1023 and 1024 :

aL_Data1.COUNT : 1023
test_bug_filtre INSERT : 1
aL_Data1.COUNT : 1024
test_bug_filtre INSERT : 0

As you can see with 1023 values in aL_Data1 MEMBER OF is WORKING but with 1024 values it's no more morking.

If I disable parallel mode then the problem disappear.

The problem also disappear if I replace MEMBER OF with an EXISTS(SELECT NULL FROM TABLE(CAST(aL_Data1 AS NUMBER_TABLE)) b WHERE b.column_value = a.data_1)

The real queries are a lot more complex and there is a lot more data in the tables than in my test_bug table, with my real test case the maximum nL_Limit is 799 not 1023.

I'll do some test with oracle 12cR2 virtualbox Developper VM to see if this bug is still there

I've submited this problem to the DBA but he did not find any bug related to this, did someone on the list encountered this bug before?

Thomas

--
http://www.freelists.org/webpage/oracle-l