Creating CheckConstraint on JSONField with __exact lookup on key transforms crashes on Oracle.

[Mariusz Felisiak]

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.

[r4ge]

Hi, I may be wrong as 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?

[Mariusz Felisiak]

Replying to 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.

[r4ge]

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

[Amir Karimi]

Replying to 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?

[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 other way to work with JSON in LOB. I am working on it.

[Mariusz Felisiak <felisiak.mariusz@…>]

In c991602c:

Fixed #34060 -- Fixed migrations crash when adding check constraints with JSONField exact lookup on Oracle.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 0d8fbe2a:

Refs #34060 -- Fixed crash when filtering against literal JSON with psycopg2.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 26aae56:

Refs #34060 -- Fixed JSONField exact lookup for primitivies on Oracle 21c+.

Regression in c991602ce5798385261381025c06698d7fd30dc5.

Co-Authored-By: Mariusz Felisiak <felisiak.mariusz@…>

[GitHub <noreply@…>]

In 9c17010:

Refs #34059, Refs #34060 -- Removed outdated warning about validation of JSONField constraints.

Known issues have been fixed in:

• 0d8fbe2ade29f1b7bd9e6ba7a0281f5478603a43,
• c991602ce5798385261381025c06698d7fd30dc5, and
• 26aae5614487f58ddb1df5726224393887373ecd.

[Sarah Boyce <42296566+sarahboyce@…>]

In 7bd1ddf:

[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.

[Sarah Boyce <42296566+sarahboyce@…>]

In 577cd734:

[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.