Opened 18 months ago

Closed 6 weeks ago

Last modified 11 days ago

#34060 closed Bug (fixed)

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

Reported by: Mariusz Felisiak Owned by: raydeal
Component: Database layer (models, ORM) Version: 4.0
Severity: Normal Keywords: Oracle
Cc: David Sanders Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (14)

comment:1 by David Sanders, 18 months ago

Cc: David Sanders added

comment:2 by Carlton Gibson, 18 months ago

Triage Stage: UnreviewedAccepted

comment:3 by r4ge, 12 months ago

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?

Last edited 12 months ago by r4ge (previous) (diff)

in reply to:  3 comment:4 by Mariusz Felisiak, 12 months ago

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.

comment:5 by r4ge, 12 months ago

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

in reply to:  5 comment:6 by Amir Karimi, 9 months ago

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?

comment:7 by raydeal, 3 months ago

Owner: changed from nobody to raydeal
Status: newassigned

comment:8 by raydeal, 2 months ago

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.

Last edited 2 months ago by raydeal (previous) (diff)

comment:9 by raydeal, 6 weeks ago

Has patch: set

comment:10 by Mariusz Felisiak, 6 weeks ago

Triage Stage: AcceptedReady for checkin

comment:11 by Mariusz Felisiak <felisiak.mariusz@…>, 6 weeks ago

Resolution: fixed
Status: assignedclosed

In c991602c:

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

comment:12 by Mariusz Felisiak <felisiak.mariusz@…>, 6 weeks ago

In 0d8fbe2a:

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

comment:13 by Mariusz Felisiak <felisiak.mariusz@…>, 6 weeks ago

In 26aae56:

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

Regression in c991602ce5798385261381025c06698d7fd30dc5.

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

comment:14 by GitHub <noreply@…>, 11 days ago

In 9c17010:

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

Known issues have been fixed in:

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