Opened 4 months ago

Last modified 4 months ago

#34059 assigned New feature

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 (9)

comment:1 Changed 4 months ago by Dan LaManna

Description: modified (diff)

comment:2 Changed 4 months ago by David Sanders

Cc: David Sanders added
Component: contrib.postgresDatabase layer (models, ORM)
Owner: set to David Sanders
Status: newassigned

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 Changed 4 months ago by David Sanders

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

comment:4 Changed 4 months ago by David Sanders

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

comment:5 Changed 4 months ago by Mariusz Felisiak

Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

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 Changed 4 months ago by Mariusz Felisiak

Summary: Validation of check constraints on PostgreSQL & Oracle JSON fields produces invalid SQLValidation 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 Changed 4 months ago by David Sanders

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 Changed 4 months ago by Mariusz Felisiak

Severity: Release blockerNormal
Type: BugNew feature

comment:9 Changed 4 months ago by GitHub <noreply@…>

In 004f985b:

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

Thanks Dan LaManna for the report.

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