[Django] #34059: Validation of check constraints on postgres json fields produce invalid SQL

[Django]

#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 | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------------+------------------------
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/34059>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#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]

#34059: Validation of check constraints on postgres json fields produce invalid SQL

-------------------------------------+-------------------------------------
Reporter: Dan LaManna | Owner: David
| Sanders
Type: Bug | Status: assigned
Component: Database layer | Version: 4.1
(models, ORM) |

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

-------------------------------------+-------------------------------------
Changes (by David Sanders):

* cc: David Sanders (added)
* owner: (none) => David Sanders
* status: new => assigned
* component: contrib.postgres => Database layer (models, ORM)

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/34059#comment:2>