Opened 3 months ago

Closed 3 months ago

Last modified 3 months ago

#36418 closed Bug (invalid)

bulk_update None on JSON field sets the value as "null" string in Postgresql

Reported by: Amit Maniar Owned by:
Component: Database layer (models, ORM) Version: 5.2
Severity: Normal Keywords:
Cc: Simon Charette, Sage Abdullah Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Updating JSON field of Postgresql table to None stores "null" as a string when using bulk_update.

Model in models.py

# Create model in DB
class TestModel(models.Model):

    created_at = models.DateTimeField(null=False)
    label = models.CharField(max_length=64)
    json_row = models.JSONField(null=False)

    def __str__(self) -> str:
        return self.label

from django.utils import timezone

# Create few records in database
TestModel.objects.create(created_at=timezone.now(), label='test label 1', json_row={'key1': 'value1', 'key2': 1234})
TestModel.objects.create(created_at=timezone.now(), label='test label 2', json_row={'key1': 'value1', 'key2': 5678})

# Create a list to update the records
test_list = []
test_list.append(TestModel.objects.get(id=1))

# update JSON field to None
test_list[0].json_row = None

# Bulk update objects in fields.
TestModel.objects.bulk_update(test_list, fields=['json_row'])

# Check DB records, json_row of TestModel (id=1) will be "null" (null as a string value)

Change History (6)

comment:1 by Clifford Gama, 3 months ago

Hello, Amit. Thanks for taking the time to create this ticket.

I can't reproduce with:

  • tests/queries/models.py

    diff --git a/tests/queries/models.py b/tests/queries/models.py
    index 9f4cf040b6..420b7fee30 100644
    a b class JSONFieldNullable(models.Model):  
    789789
    790790    class Meta:
    791791        required_db_features = {"supports_json_field"}
     792
     793
     794class NonNullableJSONModel(models.Model):
     795    json_field = models.JSONField(null=False)
     796
     797    class Meta:
     798        required_db_features = {"supports_json_field"}
  • tests/queries/test_bulk_update.py

    diff --git a/tests/queries/test_bulk_update.py b/tests/queries/test_bulk_update.py
    index 765fa934ca..77f90c5082 100644
    a b from .models import (  
    2626    SpecialCategory,
    2727    Tag,
    2828    Valid,
     29    NonNullableJSONModel
    2930)
    3031
    3132
    class BulkUpdateTests(TestCase):  
    300301            JSONFieldNullable.objects.filter(json_field__has_key="c"), objs
    301302        )
    302303
     304    @skipUnlessDBFeature("supports_json_field")
     305    @override_settings(DEBUG=True)
     306    def test_non_nullable_json_field(self):
     307        obj = NonNullableJSONModel.objects.create(json_field={"k": "val"})
     308        obj.json_field = None
     309        NonNullableJSONModel.objects.bulk_update([obj], ["json_field"])
     310        self.assertIsNone(obj.json_field)
     311        sql = connection.queries[-1]["sql"].lower()
     312        self.assertIn("'null'::jsonb", sql)
     313        self.assertNotIn("'\"null\"'::jsonb", sql)
     314
    303315    def test_nullable_fk_after_related_save(self):
    304316        parent = RelatedObject.objects.create()
    305317        child = SingleObject()

on PostgreSQL 16.8 and Django's main branch. I get a JSON null instead of a "null" string. Are you sure you're not getting the same?

Sidenote: that this saves a JSON null is surprising since JSON null is supposed to be stored through Value(None, JSONField()) as documented here. I'd have expected this to use SQL NULL instead, raising IntegrityError, like update() and save() on JSONField with null=False.

Last edited 3 months ago by Clifford Gama (previous) (diff)

comment:3 by Clifford Gama, 3 months ago

Resolution: invalid
Status: newclosed

comment:4 by Natalia Bidart, 3 months ago

Hello Amit Maniar, thank you for this ticket. I think the description lacks some clarity: could you please provide concrete details, ideally a failing test, about what do you expect to find in the DB record for the example that you provided?

I believe that Clifford understood as that you may be reporting that "null" (with the double quotes) is being stored, but I think you meant that null as the string is stored (instead of an actual SQL NULL). I can replicate getting the json's null:

django-test=# select * from testapp_ticket36418model where json_row is NULL;
 id | label | json_row 
----+-------+----------
(0 rows)

django-test=# select * from testapp_ticket36418model where json_row = 'null';
 id |    label     | json_row 
----+--------------+----------
  1 | test label 1 | null
(1 row)

Clifford has kindly created #36419 to follow up on the second half of this.

comment:5 by Natalia Bidart, 3 months ago

Cc: Simon Charette Sage Abdullah added

in reply to:  4 ; comment:6 by Amit Maniar, 3 months ago

Hi Natalia Bidart and Clifford, Apologies for the confusion. I was reporting that null as the string is stored instead of an actual SQL NULL. I used double quotes around null to specify null as a string value.

Thanks for creating #36419.

Replying to Natalia Bidart:

Hello Amit Maniar, thank you for this ticket. I think the description lacks some clarity: could you please provide concrete details, ideally a failing test, about what do you expect to find in the DB record for the example that you provided?

I believe that Clifford understood as that you may be reporting that "null" (with the double quotes) is being stored, but I think you meant that null as the string is stored (instead of an actual SQL NULL). I can replicate getting the json's null:

django-test=# select * from testapp_ticket36418model where json_row is NULL;
 id | label | json_row 
----+-------+----------
(0 rows)

django-test=# select * from testapp_ticket36418model where json_row = 'null';
 id |    label     | json_row 
----+--------------+----------
  1 | test label 1 | null
(1 row)

Clifford has kindly created #36419 to follow up on the second half of this.

in reply to:  6 comment:7 by Clifford Gama, 3 months ago

Replying to Amit Maniar:

Thanks for the follow-up, Amit.

I was reporting that null as the string is stored instead of an actual SQL NULL. I used double quotes around null to specify null as a string value.

Ah, I see. Please note that an SQL NULL wouldn't have been possible in your example as null=False is set (unless you were expecting an IntegrityError to be raised), which is why it was a little confusing. The null you are seeing in the database is JSON scalar null, which is an explicit "empty value" marker in JSON syntax ('null'::jsonb). The string (which I had misunderstood you meant) would be '"null"'::jsonb.

Fixing #36419 should make bulk updating json_row=None store an SQL NULL instead of JSON null, which would raise an IntegrityError in your example.

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