[Django] #34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms crashes on Oracle.

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz | Owner: nobody
Felisiak |
Type: Bug | Status: new
Component: Database | Version: 4.0
layer (models, ORM) |
Severity: Normal | Keywords: Oracle
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Creating `CheckConstraint` on `JSONField` with `__exact` lookup on key
transforms crashes on Oracle:
{{{
class Version(models.Model):
metadata = models.JSONField(null=True)

class Meta:
constraints = [
models.CheckConstraint(
check=models.Q(metadata__stage="stable"),
name="only_stable_version",
),
]
}}}

Crashes with:
{{{
File "/django/django/db/backends/oracle/base.py", line 557, in execute
return self.cursor.execute(query, self._param_generator(params))
cx_Oracle.DatabaseError: ORA-00904: "DBMS_LOB"."SUBSTR": invalid
identifier
}}}

`DBMS_LOB.SUBSTR` is unnecessary in this case.

It's not a regression.

--
Ticket URL: <https://code.djangoproject.com/ticket/34060>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------

Reporter: Mariusz Felisiak | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by David Sanders):

* cc: David Sanders (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:1>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: Oracle | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Carlton Gibson):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:2>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------

Reporter: Mariusz Felisiak | Owner: Aravind
| Swaminathan
Type: Bug | Status: assigned

Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Aravind Swaminathan):

* owner: nobody => Aravind Swaminathan
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:3>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------

Reporter: Mariusz Felisiak | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by r4ge):

Hi, I may be wrong as this I am just new to this.
The issue seems to be here "/backends/oracle/operations.py"

{{{
def lookup_cast(self, lookup_type, internal_type=None):
if lookup_type in ("iexact", "icontains", "istartswith",
"iendswith"):
return "UPPER(%s)"
if internal_type == "JSONField" and lookup_type == "exact":
return "DBMS_LOB.SUBSTR(%s)"
return "%s"
}}}

The condition might be

{{{
if internal_type != "JSONField" and lookup_type == "exact": # if the type
is not JSONField
}}}

Can I pick this up?

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

Replying to [comment:3 r4ge]:

> The condition might be
>
> {{{
> if internal_type != "JSONField" and lookup_type == "exact": # if the
type is not JSONField
> }}}
>
> Can I pick this up?

This is not a correct solution, as generally `DBMS_LOB.SUBSTR()` is
necessary for `JSONField`. Do you have an Oracle setup? Unfortunately, you
will not be able to work on this ticket without it.

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:4>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by r4ge):

No, Trying to setup the oracle once done will update if I found the
solution.

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:5>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Amir Karimi):

Replying to [comment:5 r4ge]:

> No, Trying to setup the oracle once done will update if I found the
solution.

How's it going with this issue? Have you got it done?

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:6>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------

Reporter: Mariusz Felisiak | Owner: raydeal
Type: Bug | Status: assigned

Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by raydeal):

* owner: nobody => raydeal

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:7>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: raydeal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by raydeal):

This error is because JSON is stored in NCLOB database type but according
to Oracle documentation:
Oracle Database does not support constraints on columns or attributes
whose type is a LOB, with the following exception: NOT NULL constraints
are supported for a LOB column or attribute.

The exact check should be implemented differently to work with JSON in
LOB. I am working on it.

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:8>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: raydeal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by raydeal):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:9>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: raydeal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: Oracle | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:10>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: raydeal

Type: Bug | Status: closed

Component: Database layer | Version: 4.0
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: Oracle | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"c991602ce5798385261381025c06698d7fd30dc5" c991602c]:
{{{#!CommitTicketReference repository=""
revision="c991602ce5798385261381025c06698d7fd30dc5"
Fixed #34060 -- Fixed migrations crash when adding check constraints with
JSONField __exact lookup on Oracle.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:11>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: raydeal
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Oracle | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"0d8fbe2ade29f1b7bd9e6ba7a0281f5478603a43" 0d8fbe2a]:
{{{#!CommitTicketReference repository=""
revision="0d8fbe2ade29f1b7bd9e6ba7a0281f5478603a43"
Refs #34060 -- Fixed crash when filtering against literal JSON with
psycopg2.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:12>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: raydeal
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Oracle | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"26aae5614487f58ddb1df5726224393887373ecd" 26aae56]:
{{{#!CommitTicketReference repository=""
revision="26aae5614487f58ddb1df5726224393887373ecd"
Refs #34060 -- Fixed JSONField __exact lookup for primitivies on Oracle
21c+.

Regression in c991602ce5798385261381025c06698d7fd30dc5.

Co-Authored-By: Mariusz Felisiak <felisiak...@gmail.com>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:13>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: raydeal
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Oracle | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"9c17010358891c73417ec569df4f568c0671c099" 9c17010]:
{{{#!CommitTicketReference repository=""
revision="9c17010358891c73417ec569df4f568c0671c099"
Refs #34059, Refs #34060 -- Removed outdated warning about validation of
JSONField constraints.

Known issues have been fixed in:
- 0d8fbe2ade29f1b7bd9e6ba7a0281f5478603a43,
- c991602ce5798385261381025c06698d7fd30dc5, and
- 26aae5614487f58ddb1df5726224393887373ecd.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:14>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: raydeal
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Oracle | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"7bd1ddf1d81e9120c28322ee2a0b9c11941a6af2" 7bd1ddf]:
{{{#!CommitTicketReference repository=""
revision="7bd1ddf1d81e9120c28322ee2a0b9c11941a6af2"
[4.2.x] Refs #34060 -- Adjusted CVE-2024-53908 regression test for
psycopg2.

The lack of explicit cast for JSON literals on psycopg2 is fixed on 5.1+
by
0d8fbe2ade29f1b7bd9e6ba7a0281f5478603a43 but didn't qualify for a backport
to
stable/4.2.x.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:15>

[Django]

#34060: Creating CheckConstraint on JSONField with __exact lookup on key transforms
crashes on Oracle.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: raydeal
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Oracle | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"577cd7343a95c316070d34ff79f827ae3f5ba5e7" 577cd734]:
{{{#!CommitTicketReference repository=""
revision="577cd7343a95c316070d34ff79f827ae3f5ba5e7"
[5.0.x] Refs #34060 -- Adjusted CVE-2024-53908 regression test for

psycopg2.

The lack of explicit cast for JSON literals on psycopg2 is fixed on 5.1+
by
0d8fbe2ade29f1b7bd9e6ba7a0281f5478603a43 but didn't qualify for a backport
to

stable/5.0.x at the time.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34060#comment:16>