Opened 11 months ago

Closed 11 months ago

Last modified 11 months ago

#34754 closed Bug (fixed)

CheckConstraint with isnull lookup on JSONField transform None into null jsonb value

Reported by: Alexandre Collet Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 4.2
Severity: Release blocker Keywords: constraint, jsonfield, none, check
Cc: Simon Charette 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

Hello !

I think i've found a bug in the validation of constraints for the JSONField with the isnull lookup (with postgresql).
Let me explain:

I have a model with some values stored in a JSONField and i want to be sure (with a CheckConstraint) than if this field was not None, an author was set.

class MyModel(Model, UuidMixin, TimestampableMixin):
    values = JSONField(
        null=True,
        blank=True,
    )
    author = ForeignKey(
        null=True,
        blank=True,
        to=settings.AUTH_USER_MODEL,
        on_delete=SET_NULL,
    )

    class Meta:
        constraints = [
            CheckConstraint(
                name="author_cannot_be_null_if_values_was_set",
                check=(
                    Q(values__isnull=True)
                    | Q(values__isnull=False, author__isnull=False)
                ),
            ),
        ]

This works perfectly in 4.1.
But in 4.2, the sql generated for the check when i does not set the field "values" and "author" is that:

instance = MyModel(values=None, author=None)
instance.validate_constraints() 
 SELECT 1 AS "_check" WHERE COALESCE((('null'::jsonb IS NULL OR (NULL IS NOT NULL AND 'null'::jsonb IS NOT NULL))), true)

This raise a ValidationError because the None value was transformed into a jsonb null value and the isnull lookup is always False.

I'm not sure how to correct this, but if anyone would like to help me, I'd be delighted to make a pull request.

Change History (8)

comment:1 by Alexandre Collet, 11 months ago

Type: UncategorizedBug

comment:2 by Alexandre Collet, 11 months ago

Keywords: jsonfield none check added

comment:3 by Mariusz Felisiak, 11 months ago

Cc: Simon Charette added
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

Thanks for the report. Regression in 5c23d9f0c32f166c81ecb6f3f01d5077a6084318.

comment:4 by Simon Charette, 11 months ago

Has patch: set
Owner: changed from nobody to Simon Charette
Status: newassigned

Submitted a patch to address the issue but I think the fundamental problem here is that

MyModel.objects.create(values=None)  # Creates an object with a SQL NULL
MyModel.objects.filter(values=None)  # Filters for objects with JSON NULL

This asymmetry prevents logic from being added to JSONField.get_db_prep_value directly but it's a much larger problem to resolve.

Just like we discourage the usage of null=True, blank=True fields as allows for the co-existence of two no data values I think we should do add friction to storing null in a JSONField(null=True) field by requiring that Value(None, JSONField()) be used to filter using 'null'::json.

I can't think of a deprecation path for MyModel.objects.filter(values=None) though unfortunately so it seems like we've missed the boat on that one.

comment:5 by Mariusz Felisiak, 11 months ago

Triage Stage: AcceptedReady for checkin

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 11 months ago

Resolution: fixed
Status: assignedclosed

In 3434dbd3:

Fixed #34754 -- Fixed JSONField check constraints validation on NULL values.

The isnull lookup of JSONField must special case
Value(None, JSONField()) left-hand-side in order to be coherent with
its convoluted null handling.

Since psycopg>=3 offers no way to pass a NULL::jsonb the issue is
resolved by optimizing IsNull(Value(None), True | False) to
True | False.

Regression in 5c23d9f0c32f166c81ecb6f3f01d5077a6084318.

Thanks Alexandre Collet for the report.

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 11 months ago

In 3a186331:

[4.2.x] Fixed #34754 -- Fixed JSONField check constraints validation on NULL values.

The isnull lookup of JSONField must special case
Value(None, JSONField()) left-hand-side in order to be coherent with
its convoluted null handling.

Since psycopg>=3 offers no way to pass a NULL::jsonb the issue is
resolved by optimizing IsNull(Value(None), True | False) to
True | False.

Regression in 5c23d9f0c32f166c81ecb6f3f01d5077a6084318.

Thanks Alexandre Collet for the report.

Backport of 3434dbd39d373df7193ad006b970c09c1a909ea3 from main

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 11 months ago

In 936afc2d:

[4.2.x] Refs #34754 -- Added missing FullResultSet import.

Follow up to 3a1863319c1ed4bc81fe270b0d7d25c7f03fdb48.

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