Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#32252 closed Bug (fixed)

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

Reported by: Sage Abdullah Owned by: Sage Abdullah
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 by Sage Abdullah, 4 years ago

Has patch: set

comment:2 by Mariusz Felisiak, 4 years ago

Triage Stage: UnreviewedAccepted

comment:3 by Mariusz Felisiak, 4 years ago

Triage Stage: AcceptedReady for checkin

comment:4 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

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 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

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