#34059: Validation of check constraints on postgres json fields produce invalid
SQL
----------------------------------+--------------------------------------
Reporter: Dan LaManna | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 4.1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------
Description changed by Dan LaManna:
Old description:
> 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 https://code.djangoproject.com/ticket/33905.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34059#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070183826e07ba-8b6b7f3b-e9e5-4207-8ef5-a3707e97894a-000000%40eu-central-1.amazonses.com.
