DBMS_LOB.SUBSTR() is unnecessary for "IS NULL" condition on Oracle.

[Michael Smith]

Model contains

somefield = models.TextField(blank=True, null=True)

At 4.1.9:

print(MyModel.objects.filter(somefield=None).only('id').query)

SELECT "TNAME_MYMODEL"."ID" FROM "TNAME_MYMODEL" WHERE "TNAME_MYMODEL"."SOMEFIELD"  IS NULL

works

At 4.2.0:

print(MyModel.objects.filter(somefield=None).only('id').query)

SELECT "TNAME_MYMODEL"."ID" FROM "TNAME_MYMODEL" WHERE DBMS_LOB.SUBSTR("TNAME_MYMODEL"."SOMEFIELD") IS NULL

DatabaseError: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

[Michael Smith]

This was introduced in this commit: ​https://github.com/django/django/commit/09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca

[Michael Smith]

the specific change was in django/db/models/lookups.py

from:

sql, params = compiler.compile(self.lhs)

to:

sql, params = self.process_lhs(compiler, connection):

[Mariusz Felisiak]

Thanks for this ticket, however it works for me. I cannot reproduce ORA-06502 on Oracle 19c.

Please reopen the ticket if you can debug your issue and provide a small sample project that reproduces the issue.

[Michael Smith]

did you have move that 4000 characters in the CLOB? You need to have more than 3999 characters to get the error to be thrown.

[Mariusz Felisiak]

Replying to Michael D. Smith:

did you have move that 4000 characters in the CLOB? You need to have more than 3999 characters to get the error to be thrown.

Yes, still no error. I tried with "x" * 4001, "x" * 40000, and "Ж" * 2001.

[Mariusz Felisiak]

did you have move that 4000 characters in the CLOB?

Why in CLOB? 🤔 Django uses NCLOB.

[Michael Smith]

with nclob also. Do you have MAX_STRING_SIZE = EXTENDED?

>>> MyTile.objects.create(name='test1',metadata = "x" * 4001)
<MyTile: test1>
>>> MyTile.objects.filter(metadata=None)
Traceback (most recent call last):
  File "src/oracledb/impl/base/cursor.pyx", line 397, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
  File "src/oracledb/impl/thin/cursor.pyx", line 110, in oracledb.thin_impl.ThinCursorImpl._fetch_rows
  File "src/oracledb/impl/thin/protocol.pyx", line 382, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 383, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 376, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/models/query.py", line 374, in __repr__
    data = list(self[: REPR_OUTPUT_SIZE + 1])
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/models/query.py", line 398, in __iter__
    self._fetch_all()
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
              ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1593, in execute_sql
    return list(result)
           ^^^^^^^^^^^^
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 2091, in cursor_iter
    for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 2091, in <lambda>
    for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
                              ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/utils.py", line 97, in inner
    with self:
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/django/db/utils.py", line 98, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rdcrlmds/miniconda3/envs/pdal243/lib/python3.11/site-packages/oracledb/cursor.py", line 492, in fetchmany
    row = fetch_next_row(self)
          ^^^^^^^^^^^^^^^^^^^^
  File "src/oracledb/impl/base/cursor.pyx", line 397, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
  File "src/oracledb/impl/thin/cursor.pyx", line 110, in oracledb.thin_impl.ThinCursorImpl._fetch_rows
  File "src/oracledb/impl/thin/protocol.pyx", line 382, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 383, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 376, in oracledb.thin_impl.Protocol._process_message
django.db.utils.DatabaseError: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

[Mariusz Felisiak]

Do you have MAX_STRING_SIZE = EXTENDED?

Yes, but this should still crash for 40000 chars. Also, it seems that you're using python-oracledb driver which is not supported (see #33817). Can you reproduce it with cx_Oracle?

[Michael Smith]

unfortunately, we are on python 3.11 and cx-Oracle won't run. But its reproducible at the db level from the generated query:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> SELECT "CLOBBUGAPP_MYTILE"."ID", "CLOBBUGAPP_MYTILE"."NAME", "CLOBBUGAPP_MYTILE"."METADATA" FROM "CLOBBUGAPP_MYTILE" WHERE DBMS_LOB.SUBSTR("CLOBBUGAPP_MYTILE"."METADATA") IS NULL
  2  ;
ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1



no rows selected

[Michael Smith]

SQL> describe clobbugapp_mytile;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID					   NOT NULL NUMBER(19)
 NAME						    NVARCHAR2(100)
 METADATA					    NCLOB

[Mariusz Felisiak]

What database backend are you using?

[Michael Smith]

I've tried with both django.contrib.gis.db.backends.oracle and django.db.backends.oracle using the 'threaded': True option

[Mariusz Felisiak]

I've tried with both django.contrib.gis.db.backends.oracle and django.db.backends.oracle using the 'threaded': True option

How you use it with python-oracledb? As far as I'm aware, built-in backends should immediately crash with python-oracledb.

[Michael Smith]

no, you just have to alias it

import oracledb
import sys
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb

[Mariusz Felisiak]

Let's accept it.

Jatin, Do you have an Oracle setup? Unfortunately, you may not be able to work on this ticket without it.

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In 1586a09:

Fixed #34544 -- Avoided DBMS_LOB.SUBSTR() wrapping with IS NULL condition on Oracle.

Regression in 09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca.

Thanks Michael Smith for the report.

This also reverts commit 1e4da439556cdd69eb9f91e07f99cf77997e70d2.

[Mariusz Felisiak <felisiak.mariusz@…>]

In e0d89811:

[4.2.x] Fixed #34544 -- Avoided DBMS_LOB.SUBSTR() wrapping with IS NULL condition on Oracle.

Regression in 09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca.

Thanks Michael Smith for the report.

This also reverts commit 1e4da439556cdd69eb9f91e07f99cf77997e70d2.
Backport of 1586a09b7949bbb7b0d84cb74ce1cadc25cbb355 from main