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/[email protected]//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.

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

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

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/[email protected]//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/[email protected]//localhost/PDB1 @ hello.sql

Regards,

Franck.

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

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.

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