Opened 2 years ago

Closed 9 months ago

Last modified 9 months ago

#34059 closed New feature (fixed)

Validation of check constraints on JSONField key transforms with None produces invalid SQL on PostgreSQL.

Reported by: Dan LaManna Owned by: David Sanders
Component: Database layer (models, ORM) Version: 4.1
Severity: Normal Keywords:
Cc: David Sanders Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by Dan LaManna)

Given a model with constraints within the JSONField:

class Version(models.Model):
    metadata = models.JSONField(blank=True, default=dict)

    class Meta:
        constraints = [
            models.CheckConstraint(
                name='version_metadata_has_schema_version',
                check=~Q(metadata__schemaVersion=None),
            )
        ]

The following code produces an error:

version = Version(metadata={'foo': 'bar'})
version.validate_constraints()

WARNING  Got a database error calling check() on <Q: (AND: (NOT (AND: ('metadata__schemaVersion', None))))>: operator is not unique: unknown -> unknown         
                    LINE 1: SELECT 1 AS "_check" WHERE NOT (('{"foo": "bar"}' -> 'schema...                                                                                                  
                                                                              ^                                                                                                              
                    HINT:  Could not choose a best candidate operator. You might need to add explicit type casts

Internally it's running the following query:

SELECT 1 AS "_check"
 WHERE NOT (('{"foo": "bar"}' -> 'schemaVersion') = 'null')

This appears similar to #33905.

Change History (11)

comment:1 by Dan LaManna, 2 years ago

Description: modified (diff)

comment:2 by David Sanders, 2 years ago

Cc: David Sanders added
Component: contrib.postgres → Database layer (models, ORM)
Owner: set to David Sanders
Status: new → assigned

Changing component to ORM because this is also an issue with Oracle:

SELECT %s AS "_CHECK"  FROM DUAL WHERE NOT (JSON_EXISTS({"foo": "bar"}, '$."schemaVersion"') AND COALESCE(JSON_QUERY(%s, '$."schemaVersion"'), JSON_VALUE(%s, '$."schemaVersion"')) IS NULL)
Got a database error calling check() on <Q: (AND: (NOT (AND: ('metadata__schemaVersion', None))))>: ORA-00936: missing expression

MySQL & SQLite are fine

comment:3 by David Sanders, 2 years ago

Summary: Validation of check constraints on postgres json fields produce invalid SQL → Validation of check constraints on PostgreSQL & Oracle JSON fields produces invalid SQL

comment:4 by David Sanders, 2 years ago

Thanks for the report Dan. Yes this is similar to #33905 👍

comment:5 by Mariusz Felisiak, 2 years ago

Severity: Normal → Release blocker
Triage Stage: Unreviewed → Accepted

Changing component to ORM because this is also an issue with Oracle:

... and JSONField doesn't live in contrib.postgres anymore.

Bug in 667105877e6723c6985399803a364848891513cc.

comment:6 by Mariusz Felisiak, 2 years ago

Summary: Validation of check constraints on PostgreSQL & Oracle JSON fields produces invalid SQL → Validation of check constraints on JSONField key transforms with None produces invalid SQL on PostgreSQL.

Validation of json_field__key_transform=None doesn't work only on PostgreSQL. Creating check constraint with __exact lookup e.g. json_field__key_transform="value" crashes on Oracle but it's a separate issue and not a regression (see #34060).

comment:7 by David Sanders, 2 years ago

Has patch: set
Patch needs improvement: set

PR: ​https://github.com/django/django/pull/16131

Marking as "needs improvement" as some discussion needs to be had.

comment:8 by Mariusz Felisiak, 2 years ago

Severity: Release blocker → Normal
Type: Bug → New feature

comment:9 by GitHub <noreply@…>, 2 years ago

In 004f985b:

Refs #34059 -- Doc'd lack of support for validation of constraints with JSONFields.

Thanks Dan LaManna for the report.

comment:10 by Mariusz Felisiak, 9 months ago

Resolution: → fixed
Status: assigned → closed

Fixed in 09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca.

comment:11 by GitHub <noreply@…>, 9 months 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