Opened 3 months ago

Closed 3 months ago

Last modified 3 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 3 months ago by sage

Has patch: set

comment:2 Changed 3 months ago by Mariusz Felisiak

Triage Stage: UnreviewedAccepted

comment:3 Changed 3 months ago by Mariusz Felisiak

Triage Stage: AcceptedReady for checkin

comment:4 Changed 3 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 3 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