#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 , 2 years ago
Cc: | added |
---|
comment:2 by , 2 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:4 by , 19 months ago
Replying to r4ge:
The condition might be
if internal_type != "JSONField" and lookup_type == "exact": # if the type is not JSONFieldCan 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.
follow-up: 6 comment:5 by , 19 months ago
No, Trying to setup the oracle once done will update if I found the solution.
comment:6 by , 16 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 , 10 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:8 by , 10 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.
comment:9 by , 9 months ago
Has patch: | set |
---|
comment:10 by , 9 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
Hi, I may be wrong as I am just new to this.
The issue seems to be here "/backends/oracle/operations.py"
The condition might be
Can I pick this up?