Opened 10 months ago

Closed 10 months ago

Last modified 10 months ago

#34544 closed Bug (fixed)

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

Reported by: Michael Smith Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 4.2
Severity: Release blocker Keywords: Oracle isnull lob
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Mariusz Felisiak)

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

Change History (19)

comment:2 by Michael Smith, 10 months ago

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):

comment:3 by Jatin-tec, 10 months ago

Owner: changed from nobody to Jatin-tec
Status: newassigned

comment:4 by Mariusz Felisiak, 10 months ago

Description: modified (diff)
Resolution: needsinfo
Status: assignedclosed

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.

comment:5 by Michael Smith, 10 months ago

Resolution: needsinfo
Status: closednew

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.

in reply to:  5 comment:6 by Mariusz Felisiak, 10 months ago

Resolution: needsinfo
Status: newclosed

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.

comment:7 by Mariusz Felisiak, 10 months ago

did you have move that 4000 characters in the CLOB?

Why in CLOB? 🤔 Django uses NCLOB.

comment:8 by Michael Smith, 10 months ago

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

comment:9 by Mariusz Felisiak, 10 months ago

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?

comment:10 by Michael Smith, 10 months ago

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

comment:11 by Michael Smith, 10 months ago

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

comment:12 by Mariusz Felisiak, 10 months ago

What database backend are you using?

comment:13 by Michael Smith, 10 months ago

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

comment:14 by Mariusz Felisiak, 10 months ago

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.

comment:15 by Michael Smith, 10 months ago

no, you just have to alias it

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

comment:16 by Mariusz Felisiak, 10 months ago

Resolution: needsinfo
Severity: NormalRelease blocker
Status: closednew
Summary: models.TextField with =None filter throws Error for OracleDBMS_LOB.SUBSTR() is unnecessary for "IS NULL" condition on Oracle.
Triage Stage: UnreviewedAccepted

Let's accept it.

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

comment:17 by Mariusz Felisiak, 10 months ago

Has patch: set
Owner: changed from Jatin-tec to Mariusz Felisiak
Status: newassigned

comment:18 by GitHub <noreply@…>, 10 months ago

Resolution: fixed
Status: assignedclosed

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.

comment:19 by Mariusz Felisiak <felisiak.mariusz@…>, 10 months ago

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

Note: See TracTickets for help on using tickets.
Back to Top