#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 )
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:1 by , 20 months ago
comment:2 by , 20 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 , 19 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:4 by , 19 months ago
Description: | modified (diff) |
---|---|
Resolution: | → needsinfo |
Status: | assigned → closed |
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.
follow-up: 6 comment:5 by , 19 months ago
Resolution: | needsinfo |
---|---|
Status: | closed → new |
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.
comment:6 by , 19 months ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
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 , 19 months ago
did you have move that 4000 characters in the CLOB?
Why in CLOB
? 🤔 Django uses NCLOB
.
comment:8 by , 19 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 , 19 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 , 19 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 , 19 months ago
SQL> describe clobbugapp_mytile; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(19) NAME NVARCHAR2(100) METADATA NCLOB
comment:13 by , 19 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 , 19 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 , 19 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 , 19 months ago
Resolution: | needsinfo |
---|---|
Severity: | Normal → Release blocker |
Status: | closed → new |
Summary: | models.TextField with =None filter throws Error for Oracle → DBMS_LOB.SUBSTR() is unnecessary for "IS NULL" condition on Oracle. |
Triage Stage: | Unreviewed → Accepted |
Let's accept it.
Jatin, Do you have an Oracle setup? Unfortunately, you may not be able to work on this ticket without it.
This was introduced in this commit: https://github.com/django/django/commit/09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca