[Django] #36418: bulk_update None on JSON field sets the value as "null" string in Postgresql

[Django]

#36418: bulk_update None on JSON field sets the value as "null" string in
Postgresql
-------------------------------------+-------------------------------------
Reporter: Amit Maniar | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | 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
-------------------------------------+-------------------------------------
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)

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

[Django]

#36418: bulk_update None on JSON field sets the value as "null" string in
Postgresql
-------------------------------------+-------------------------------------

Reporter: Amit Maniar | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(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
-------------------------------------+-------------------------------------

Comment (by Clifford Gama):

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

I can't reproduce with:
{{{#!diff
diff --git a/tests/queries/models.py b/tests/queries/models.py
index 9f4cf040b6..420b7fee30 100644
--- a/tests/queries/models.py
+++ b/tests/queries/models.py
@@ -789,3 +789,10 @@ class JSONFieldNullable(models.Model):

class Meta:
required_db_features = {"supports_json_field"}
+
+
+class NonNullableJSONModel(models.Model):
+ json_field = models.JSONField(null=False)
+
+ class Meta:
+ required_db_features = {"supports_json_field"}
diff --git a/tests/queries/test_bulk_update.py
b/tests/queries/test_bulk_update.py
index 765fa934ca..77f90c5082 100644
--- a/tests/queries/test_bulk_update.py
+++ b/tests/queries/test_bulk_update.py
@@ -26,6 +26,7 @@ from .models import (
SpecialCategory,
Tag,
Valid,
+ NonNullableJSONModel
)


@@ -300,6 +301,17 @@ class BulkUpdateTests(TestCase):
JSONFieldNullable.objects.filter(json_field__has_key="c"),
objs
)

+ @skipUnlessDBFeature("supports_json_field")
+ @override_settings(DEBUG=True)
+ def test_non_nullable_json_field(self):
+ obj = NonNullableJSONModel.objects.create(json_field={"k":
"val"})
+ obj.json_field = None
+ NonNullableJSONModel.objects.bulk_update([obj], ["json_field"])
+ self.assertIsNone(obj.json_field)
+ sql = connection.queries[-1]["sql"].lower()
+ self.assertIn("'null'::jsonb", sql)
+ self.assertNotIn("'\"null\"'::jsonb", sql)
+
def test_nullable_fk_after_related_save(self):
parent = RelatedObject.objects.create()
child = SingleObject()
}}}
on PostgreSQL 16.8 and Django's main branch. I get a JSON null instead of
a `"null"` string. Which is surprising since JSON null is supposed to be
stored through `Value(None, JSONField())`
[https://docs.djangoproject.com/en/5.2/topics/db/queries/#storing-and-
querying-for-none as documented here]. I'd have expected this to use SQL
`NULL` instead, raising `IntegrityError`, like `update()` and `save()`.
--
Ticket URL: <https://code.djangoproject.com/ticket/36418#comment:1>

[Django]

#36418: bulk_update None on JSON field sets the value as "null" string in
Postgresql
-------------------------------------+-------------------------------------
Reporter: Amit Maniar | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(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
-------------------------------------+-------------------------------------
Comment (by Clifford Gama):

Replying to [comment:1 Clifford Gama]:

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())`
[https://docs.djangoproject.com/en/5.2/topics/db/queries/#storing-and-
querying-for-none as documented here]. I'd have expected this to use SQL

`NULL` instead, raising `IntegrityError`, like `update()` and `save()` on
JSONField with `null=False`.
--
Ticket URL: <https://code.djangoproject.com/ticket/36418#comment:2>

[Django]

#36418: bulk_update None on JSON field sets the value as "null" string in
Postgresql
-------------------------------------+-------------------------------------
Reporter: Amit Maniar | Owner: (none)

Type: Bug | Status: closed

Component: Database layer | Version: 5.2
(models, ORM) |

Severity: Normal | Resolution: invalid

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 Clifford Gama):

* resolution: => invalid
* status: new => closed

--
Ticket URL: <https://code.djangoproject.com/ticket/36418#comment:3>

[Django]

#36418: bulk_update None on JSON field sets the value as "null" string in
Postgresql
-------------------------------------+-------------------------------------
Reporter: Amit Maniar | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36418#comment:4>

[Django]

#36418: bulk_update None on JSON field sets the value as "null" string in
Postgresql
-------------------------------------+-------------------------------------
Reporter: Amit Maniar | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: invalid
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 Natalia Bidart):

* cc: Simon Charette, Sage Abdullah (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/36418#comment:5>

[Django]

#36418: bulk_update None on JSON field sets the value as "null" string in
Postgresql
-------------------------------------+-------------------------------------
Reporter: Amit Maniar | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Amit Maniar):

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 [comment:4 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/36418#comment:6>

[Django]

#36418: bulk_update None on JSON field sets the value as "null" string in
Postgresql
-------------------------------------+-------------------------------------
Reporter: Amit Maniar | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Clifford Gama):

Replying to [comment:6 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36418#comment:7>