Opened 5 years ago

Closed 5 years ago

#31231 closed Bug (invalid)

ORM doesn't find a row with a SQL null JSONB field.

Reported by: mvarnar Owned by: nobody
Component: contrib.postgres Version: 3.0
Severity: Normal Keywords: JSON, ORM, NULL
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

If you create a row with a nullable jsonb field and then try to find the row with a null value, you won't find any row, even if they exist. It's because orm looks for json 'null', not sql 'null'.

Steps to reproduce:

  1. Create a project with models.py and test.py as described bellow.
# models.py
from django.db import models
from django.contrib.postgres.fields import JSONField


class TestModel(models.Model):
    json_field = JSONField(null=True)
# test.py
from django.test import TestCase

from test_app.models import TestModel


class TestJsonbNull(TestCase):
    def setUp(self):
        TestModel.objects.create(json_field=None)

    def test_row_has_jsonb_null_field(self):
        obj = TestModel.objects.values().all()[0]
        self.assertIsNone(obj['json_field'])

    def test_row_with_jsonb_null_field_exists(self):
        print(TestModel.objects.filter(json_field=None).query)
        self.assertTrue(TestModel.objects.filter(json_field=None).exists())

  1. Run python manage.py test

Expected behaviour:
Both tests will pass

Actual behaviour:
test_row_with_jsonb_null_field_exists will fail.

Checked for:
python==3.6.9
Django==3.0.3

  • asgiref==3.2.3
  • pytz==2019.3
  • sqlparse== 0.3.0

psycopg2==2.8.4

Database is postgresql 11.6

Change History (1)

comment:1 by Mariusz Felisiak, 5 years ago

Component: Database layer (models, ORM)contrib.postgres
Resolution: invalid
Status: newclosed

It's an expected behavior. You should use __isnull=True lookup to filter IS NULL values.

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