Opened 12 months ago

Closed 12 months ago

Last modified 12 months ago

#32252 closed Bug (fixed)

Using __isnull=True on a KeyTransform should not match JSON null on SQLite and Oracle

Reported by: sage Owned by: sage
Component: Database layer (models, ORM) Version: 3.1
Severity: Release blocker Keywords:
Cc: 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

The KeyTransformIsNull lookup borrows the logic from HasKey for isnull=False, which is correct. If isnull=True, the query should only match objects that do not have the key. The query is correct for MariaDB, MySQL, and PostgreSQL. However, on SQLite and Oracle, the query also matches objects that have the key with the value null, which is incorrect.

To confirm, edit tests.model_fields.test_jsonfield.TestQuerying.test_isnull_key. For the first assertion, change

        self.assertSequenceEqual(
            NullableJSONModel.objects.filter(value__a__isnull=True),
            self.objs[:3] + self.objs[5:],
        )

to

        self.assertSequenceEqual(
            NullableJSONModel.objects.filter(value__j__isnull=True),
            self.objs[:4] + self.objs[5:],
        )

The test previously only checks with value__a which could not catch this behavior because the value is not JSON null.

Change History (5)

comment:1 Changed 12 months ago by sage

Has patch: set

comment:2 Changed 12 months ago by Mariusz Felisiak

Triage Stage: UnreviewedAccepted

comment:3 Changed 12 months ago by Mariusz Felisiak

Triage Stage: AcceptedReady for checkin

comment:4 Changed 12 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In 8d7085e0:

Fixed #32252 -- Fixed isnull=True on key transforms on SQLite and Oracle.

isnull=True on key transforms should not match keys with NULL values.

comment:5 Changed 12 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In a891e1b:

[3.1.x] Fixed #32252 -- Fixed isnull=True on key transforms on SQLite and Oracle.

isnull=True on key transforms should not match keys with NULL values.

Backport of 8d7085e0fd004af5431389f3d903aba6220d7957 from master

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