Index on Python function ORA-00600 [17009]

Franck Pachot

    Hi,

     

    I tried to create a function-based index using use an MLE function (declared as deterministic) but I got ORA-00600 [17009]

    However, to see if it is possible in another way, I got able to workaround this by creating a virtual column with a unique constraint on it.

    Here is the full test case.

     

    I used the hello() function from the Python example provided in MLE GitHub page:

     

    SQL> CREATE TABLE DEMO.MY_MLE_SOURCE(name varchar2(30), source CLOB, ZIP BLOB);

     

    Table created.

     

    SQL> host for i in *.py ; do zip $(basename $i .py).zip $i ; echo "$i,$i,$(basename $i -py).zip" >> MY_MLE_SOURCE.ctl ; done

      adding: hello.py (deflated 15%)

     

    SQL> host cat MY_MLE_SOURCE.ctl

    load data infile * into table DEMO.MY_MLE_SOURCE replace fields terminated by ','

       ( NAME char(30), LOB_SOURCE filler char, SOURCE lobfile(LOB_SOURCE) terminated by eof, LOB_ZIP filler char, ZIP lobfile(LOB_ZIP) terminated by eof)

    begindata

    hello.py,hello.py,hello.py.zip

     

    SQL> host sqlldr '"demo/demo@//localhost/PDB1"' control=MY_MLE_SOURCE.ctl >/dev/null || cat MY_MLE_SOURCE.log

     

    SQL> select NAME,SOURCE,dbms_lob.getlength(ZIP) from DEMO.MY_MLE_SOURCE;

     

    NAME                           SOURCE                                                       DBMS_LOB.GETLENGTH(ZIP)

    ------------------------------ ------------------------------------------------------------ -----------------------

    hello.py                       import platform                                                                  254

                                   def hello(s_in: str) -> str:

                                       return "Hello " + s_in + "!"

     

                                   exports['hello'] = hello

     

     

    -- Load the code:

     

     

    SQL> create or replace python source named "hello.py" using blob select ZIP from MY_MLE_SOURCE where NAME='hello.py';

     

    Operation 254 succeeded.

     

     

    -- I Create the function as DETERMINISTIC:

     

    SQL> create or replace function hello("s_in" in varchar2) return varchar2 deterministic

      2  as language python name 'hello\.py.hello(s_in str) return str';

      3  /

     

    Function created.

     

     

    -- Then create the table with a virtual column

     

     

    SQL> create table DEMO (

      2   ID number constraint DEMO_ID primary key,

      3   MESSAGE varchar2(4000) generated always as ( hello(to_char(ID)) ) --constraint DEMO_MESSAGE unique

      4  );

     

    Table created.

     

    SQL> whenever sqlerror continue;

     

    -- The  index creation fails:

     

    SQL> create index DEMO_MESSAGE on DEMO(MESSAGE);

    create index DEMO_MESSAGE on DEMO(MESSAGE)

                                 *

    ERROR at line 1:

    ORA-00600: internal error code, arguments: [17009], [3], [0x068973C90], [0x0637F1658], [], [], [], [], [], [], [], []

     

    -- Same creating a FBI without using a virtual column:

     

    SQL> create index DEMO_HELLO on DEMO( hello('x')  );

    create index DEMO_HELLO on DEMO( hello('x')  )

                                           *

    ERROR at line 1:

    ORA-00600: internal error code, arguments: [17009], [3], [0x068973C90], [0x0637F1658], [], [], [], [], [], [], [], []

     

     

     

    -- Ok when adding a constraint which creates implicitly the same index:

     

    SQL> alter table DEMO add constraint DEMO_MESSAGE unique(MESSAGE);

     

    Table altered.

     

    -- Proof that this index is used as an access path in the execution plan

     

    SQL> insert into DEMO(id) select rownum from xmltable('1 to 5');

     

    5 rows created.

     

    SQL> set autotrace on explain

    SQL> select * from DEMO where MESSAGE='Hello 5!';

     

            ID MESSAGE

    ---------- ------------------------------

             5 Hello 5!

     

     

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 1280979691

     

    --------------------------------------------------------------------------------------------

    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

    --------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT            |              |     1 |  2015 |     0   (0)| 00:00:01 |

    |   1 |  TABLE ACCESS BY INDEX ROWID| DEMO         |     1 |  2015 |     0   (0)| 00:00:01 |

    |*  2 |   INDEX UNIQUE SCAN         | DEMO_MESSAGE |     1 |       |     0   (0)| 00:00:01 |

    --------------------------------------------------------------------------------------------

     

    Predicate Information (identified by operation id):

    ---------------------------------------------------

     

       2 - access("MESSAGE"='Hello 5!')

     

    SQL> exit

    Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0_MLE - 64bit Beta

     

     

    The Ora-600 occurs in the following stack: ksedst()+365<-dbkedDefDump()+22416<-ksedmp()+586<-dbgexPhaseII()+3068<-dbgexProcessError()+2531<-dbgePostErrorKGE()+2271<-dbkePostKGE_kgsf()+90<-kgeriv()+1706<-kgesiv()+112<-kgesic3()+142<-kglIsPinShareable()+365<-kglSessionHashIterate()+346<-kglpin()+3785<-kdiclap_lkpn()+1466<-kdicwpost()+972<-kdicrws()+23752<-kdicdrv()+2194<-opiexe()+16957<-opiosq0()+9305<-kpooprx()+467<-kpoal8()+1082<-opiodr()+2988<-ttcpip()+6614<-opitsk()+3853<-opiino()+744<-opiodr()+2988<-opidrv()+988<-sou2o()+272<-opimai_real()+244<-ssthrdmain()+511<-main()+262<-__libc_start_main()+245

     

    If you can't reproduce, just tell me how to trace more useful information.

     

    Regards,

    Franck.

      • 1. Re: Index on Python function ORA-00600 [17009]
        Bastian Hossbach-Oracle

        Hi Franck,

         

        Thank you very much for reporting this issue. Can you reliably trigger the internal error? For example, in a fresh session on a database that has just been started?

         

        Best regards,

         

        Bastian

        • 2. Re: Index on Python function ORA-00600 [17009]
          Franck Pachot

          Hi Bastian,

           

          Yes, reproducible in fresh session.

           

          This is my test case for the output I've pasted:

           

          cat > hello.py <<'CAT'

          import platform

          def hello(s_in: str) -> str:

              return "Hello " + s_in + "!"

          exports['hello'] = hello

          CAT

           

          cat > MY_MLE_SOURCE.ctl <<'CAT'

          load data infile * into table DEMO.MY_MLE_SOURCE replace fields terminated by ','

             ( NAME char(30), LOB_SOURCE filler char, SOURCE lobfile(LOB_SOURCE) terminated by eof, LOB_ZIP filler char, ZIP lobfile(LOB_ZIP) terminated by eof)

          begindata

          CAT

           

          cat > hello.sql <<'CAT'

          whenever sqlerror exit failure

          column message format a30

          column source format a60

          column zip format a60

          column text format a80

          set echo on linesize 160 pagesize 1000 longc 1000000 long 1000000

          CREATE TABLE DEMO.MY_MLE_SOURCE(name varchar2(30), source CLOB, ZIP BLOB);

          host for i in *.py ; do zip $(basename $i .py).zip $i ; echo "$i,$i,$(basename $i -py).zip" >> MY_MLE_SOURCE.ctl ; done

          host cat MY_MLE_SOURCE.ctl

          host sqlldr '"demo/demo@//localhost/PDB1"' control=MY_MLE_SOURCE.ctl >/dev/null || cat MY_MLE_SOURCE.log

          select NAME,SOURCE,dbms_lob.getlength(ZIP) from DEMO.MY_MLE_SOURCE;

          create or replace python source named "hello.py" using blob select ZIP from MY_MLE_SOURCE where NAME='hello.py';

          create or replace function hello("s_in" in varchar2) return varchar2 deterministic

          as language python name 'hello\.py.hello(s_in str) return str';

          /

          create table DEMO (

          ID number constraint DEMO_ID primary key,

          MESSAGE varchar2(4000) generated always as ( hello(to_char(ID)) )

          );

          whenever sqlerror continue;

          create index DEMO_MESSAGE on DEMO(MESSAGE);

          create index DEMO_HELLO on DEMO( hello('x')  );

          alter table DEMO add constraint DEMO_MESSAGE unique(MESSAGE);

          insert into DEMO(id) select rownum from xmltable('1 to 5');

          set autotrace on explain

          select * from DEMO where MESSAGE='Hello 5!';

          CAT

           

          sqlplus demo/demo@//localhost/PDB1 @ hello.sql

           

          Regards,

          Franck.

          • 3. Re: Index on Python function ORA-00600 [17009]
            Bastian Hossbach-Oracle

            Hi Franck,

             

            Thank you again for your detailed and very helpful bug report. We were finally able to reproduce the issue.

             

            The error shows up if your example gets executed under a common user. However, if the example gets executed under a local user (use the already existing local user "scott" identified by "tiger" or create "demo" as a local user), then everything is fine. This bug, which is new to us, looks like a locking issue at a first glance. Therefore, we would recommend to use local users in the 0.3.0 release, if possible.

             

            Best regards,

             

            Bastian

            • 4. Re: Index on Python function ORA-00600 [17009]
              Franck Pachot

              Hi Bastian,

              My DEMO is a local user, I didn't change the C## prefix.

              I tested with SCOTT and got the same error.

              Regards,

              Franck.

              • 5. Re: Index on Python function ORA-00600 [17009]
                Bastian Hossbach-Oracle

                Hi Franck,

                 

                Thank you for trying again. It might be a coincidence that we get the error for common users but not for local users. There likely might be other factors that have an influence. However, we are now able to reproduce the bug and we will address it. Unfortunately, we currently don't have a workaround for the 0.3.0 release we can provide you with.

                 

                Best regards,

                 

                Bastian