[Django] #34544: models.TextField with =None filter throws Error for Oracle
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
msmitherdc |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords: Oracle isnull lob
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34544>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle isnull lob | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by msmitherdc):
This was introduced in this commit:
https://github.com/django/django/commit/09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: msmitherdc | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by msmitherdc):
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):
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Jatin-tec
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => needsinfo
Old description:
> 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
> }}}
New description:
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
}}}
--
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: needsinfo =>
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
Replying to [comment:5 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`.
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
> did you have move that 4000 characters in the CLOB?
Why in `CLOB`? 🤔 Django uses `NCLOB`.
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Michael D. 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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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`?
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Michael D. 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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Michael D. Smith):
{{{
SQL> describe clobbugapp_mytile;
Name Null? Type
----------------------------------------- --------
----------------------------
ID NOT NULL NUMBER(19)
NAME NVARCHAR2(100)
METADATA NCLOB
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
What database backend are you using?
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Michael D. Smith):
I've tried with both django.contrib.gis.db.backends.oracle and
django.db.backends.oracle using the 'threaded': True option
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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`.
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Michael D. Smith | Owner: Jatin-tec
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Oracle isnull lob | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Michael D. Smith):
no, you just have to alias it
{{{
import oracledb
import sys
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34544#comment:15>