[Django] #32252: Using __isnull=True on a KeyTransform should not match JSON null on SQLite and Oracle

[Django]

#32252: Using __isnull=True on a KeyTransform should not match JSON null on SQLite
and Oracle
-------------------------------------+-------------------------------------
Reporter: sage | Owner: sage
Type: Bug | Status: assigned
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Release | Keywords:
blocker |
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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`.

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

[Django]

#32252: Using __isnull=True on a KeyTransform should not match JSON null on SQLite
and Oracle
-------------------------------------+-------------------------------------
Reporter: sage | Owner: sage
Type: Bug | Status: assigned

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by sage):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/13757 PR].

--
Ticket URL: <https://code.djangoproject.com/ticket/32252#comment:1>

[Django]

#32252: Using __isnull=True on a KeyTransform should not match JSON null on SQLite
and Oracle
-------------------------------------+-------------------------------------
Reporter: sage | Owner: sage
Type: Bug | Status: assigned

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/32252#comment:2>

[Django]

#32252: Using __isnull=True on a KeyTransform should not match JSON null on SQLite
and Oracle
-------------------------------------+-------------------------------------
Reporter: sage | Owner: sage
Type: Bug | Status: assigned

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

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

[Django]

#32252: Using __isnull=True on a KeyTransform should not match JSON null on SQLite
and Oracle
-------------------------------------+-------------------------------------
Reporter: sage | Owner: sage

Type: Bug | Status: closed

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

Severity: Release blocker | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"8d7085e0fd004af5431389f3d903aba6220d7957" 8d7085e0]:
{{{
#!CommitTicketReference repository=""
revision="8d7085e0fd004af5431389f3d903aba6220d7957"
Fixed #32252 -- Fixed __isnull=True on key transforms on SQLite and
Oracle.

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

--
Ticket URL: <https://code.djangoproject.com/ticket/32252#comment:4>

[Django]

#32252: Using __isnull=True on a KeyTransform should not match JSON null on SQLite
and Oracle
-------------------------------------+-------------------------------------
Reporter: sage | Owner: sage

Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"a891e1bb0a3e1b29a461784cd0bf717513e72f19" a891e1b]:
{{{
#!CommitTicketReference repository=""
revision="a891e1bb0a3e1b29a461784cd0bf717513e72f19"
[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
}}}

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