cursor.execute() fails when fetching from table with NCLOB column and IS JSON check.

[felixxm]

cursor.execute() crashes when fetching from table with NCLOB column and IS JSON check. The following SQL crashes in Django:

cursor.execute('SELECT * FROM "INSPECTDB_JSONFIELDCOLUMNTYPE" WHERE ROWNUM < 2 AND 2 > 0')

(it works with the 1.4.2 version).

Table definition:

CREATE TABLE "INSPECTDB_JSONFIELDCOLUMNTYPE" (
    "ID" NUMBER(11) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL PRIMARY KEY,
    "JSON_FIELD" NCLOB NOT NULL CHECK ("JSON_FIELD" IS JSON),
    "NULL_JSON_FIELD" NCLOB NULL CHECK ("NULL_JSON_FIELD" IS JSON)
)

1. What versions are you using?

Oracle 23c (crashes the same way on Oracle 19c).

platform.platform: Linux-5.15.0-89-generic-x86_64-with-glibc2.35
sys.maxsize > 2**32: True
platform.python_version: 3.10.9
oracledb.__version__: 2.0.0

2. Is it an error or a hang or a crash?

crash

3. What error(s) or behavior you are seeing?

  File "python3.10/site-packages/oracledb/cursor.py", line 744, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 173, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 425, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/messages.pyx", line 1323, in oracledb.thin_impl.MessageWithData.postprocess
IndexError: list index out of range

4. Does your application call init_oracle_client()?

No

5. Include a runnable Python script that shows the problem.

I was not able to reproduce it with the bare oracledb cursor or Django cursor, but it crashes in our test suite. Maybe previous fetchall(), fetchone(), execute() calls affect it somehow :thinking: :exploding_head:

[cjbj]

Thanks for the report.

[cjbj]

(My definition of a crash is something like a seg fault, so I will take the liberty of changing the subject, if you don't mind !)

Though I wouldn't have expected the error you are seeing, you may ultimately have encountered the python-oracledb 2.0 change in default handling of columns with IS JSON constraints. See the release notes and deprecations and release announcement.

In python-oracledb 2.0 if you still want to fetch IS JSON columns stored in BLOBs as, e.g. a Lob instead of as an object, then use an output type handler:

# Table definition is: create table test (data nclob constraint test_valid_json check (data is json))

def output_type_handler_lob(cursor, metadata):
    if metadata.type_code == oracledb.DB_TYPE_NCLOB:
        return cursor.var(oracledb.DB_TYPE_NCLOB, arraysize=cursor.arraysize)

def output_type_handler_str(cursor, metadata):
    if metadata.type_code is oracledb.DB_TYPE_NCLOB:
        return cursor.var(oracledb.DB_TYPE_LONG_NVARCHAR, arraysize=cursor.arraysize)

print("\nno output type handler")
with connection.cursor() as cursor:
    sql = """select /* Q1 */ * from test"""
    for r, in cursor.execute(sql):
        print(type(r))
        print(r)

print("\nwith an output type handler as Lob object")
with connection.cursor() as cursor:
    cursor.outputtypehandler = output_type_handler_lob
    sql = """select /* Q2 */ * from test"""
    for r, in cursor.execute(sql):
        print(type(r))
        print(r.read())

print("\nwith an output type handler as String")
with connection.cursor() as cursor:
    cursor.outputtypehandler = output_type_handler_str
    sql = """select /* Q3 */ * from test"""
    for r, in cursor.execute(sql):
        print(type(r))
        print(r)

This gives me:

no output type handler
<class 'dict'>
{'name': 'Rod', 'dept': 'Sales', 'location': 'Germany'}

with an output type handler as Lob object
<class 'oracledb.LOB'>
{"name": "Rod", "dept": "Sales", "location": "Germany"}

with an output type handler as String
<class 'str'>
{"name": "Rod", "dept": "Sales", "location": "Germany"}

I wonder if the error you posted could perhaps be as a result of executing the same SQL statement but with different types?

[felixxm]

Thanks. Adding the following:

diff --git a/django/db/backends/oracle/base.py b/django/db/backends/oracle/base.py
index a5e7f97df0..8b93d871bb 100644
--- a/django/db/backends/oracle/base.py
+++ b/django/db/backends/oracle/base.py
@@ -483,6 +483,8 @@ class FormatStylePlaceholderCursor:
                 arraysize=cursor.arraysize,
                 outconverter=outconverter,
             )
+        elif defaultType == Database.DB_TYPE_NCLOB:
+           return cursor.var(Database.DB_TYPE_NCLOB, arraysize=cursor.arraysize)

     def _format_params(self, params):
         try:

fixes this issue for me. However, it still looks like an issue in oracledb because it should return dict and not crash without a converter.

[cjbj]

@felixxm is it crashing in your test suite?

[felixxm]

@felixxm is it crashing in your test suite?

Yes, with

    File "src/oracledb/impl/thin/messages.pyx", line 1323, in oracledb.thin_impl.MessageWithData.postprocess
IndexError: list index out of range

I still don't understand why. We don't even fetch results, but adding a no-op converter fixes this issue.

[suraj-ora-2020]

@felixxm Can you please point me to the particular test which is failing with IndexError: list index out of range in the django test suite.

[felixxm]

@felixxm Can you please point me to the particular test which is failing with IndexError: list index out of range in the django test suite.

It's inspectdb.tests.InspectDBTestCase.test_json_field. You will not see the stack trace because the original error is hidden in inspectdb command. You can apply the following diff to see a real exception:

diff --git a/django/core/management/commands/inspectdb.py b/django/core/management/commands/inspectdb.py
index 5c2ed53db8..b6ae45705f 100644
--- a/django/core/management/commands/inspectdb.py
+++ b/django/core/management/commands/inspectdb.py
@@ -118,6 +118,7 @@ class Command(BaseCommand):
                         cursor, table_name
                     )
                 except Exception as e:
+                    raise
                     yield "# Unable to inspect table '%s'" % table_name
                     yield "# The error was: %s" % e
                     continue

[cjbj]

@felixxm My general concern is how Django is handling Oracle DB 21c's new JSON column type, since that will be returned by python-oracledb as the kind of object that columns with the IS JSON constraint now return by default. I also asked @suraj-ora-2020 to look into this.

(And one day we need to get Django to stop using NCLOB!)

[anthony-tuininga]

So, it turns out the issue has nothing to do with NCLOB at all! The problem is due to the following complex sequence of events:

• a query is executed and a fetch variable is created with an output converter (this can be done via an output type handler explicitly requested by the user or created internally)
• some rows are fetched
• the array size is adjusted downward below the number of rows that were fetched the first time
• the same query is executed but a different fetch variable is created (either via a different output type handler or created internally)
• no rows are fetched (the query generates no rows)

The only reason the NCLOB column with an IS JSON constraint is relevant is because internally a fetch variable is created with an output converter. You can see the test case in the patch for an alternative way of generating the issue.

[anthony-tuininga]

I have pushed a patch that should correct this bug. If you are able to build from source you can verify that it works for you.

[anthony-tuininga]

The patch has been included in version 2.0.1 which was just released.