[Django] #36508: Asymmetry between exact and iexact when filtering JSON keys against None

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Normal
Keywords: null, jsonfield | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
`exact` and `iexact` vary in their treatment of `None` when looking up
JSON key paths:
{{{
optional_param = request.GET.get("param")
qs1 = Model.objects.filter(json__key=optional_param)
qs2 = Model.objects.filter(json__key__iexact=optional_param)
}}}

[https://dryorm.xterm.info/json-key-filter-none/run fiddle] showing
`exact` queries return rows where the json key is null, but `iexact`
queries do not (instead, they check for key existence, e.g.
`__key__isnull`)

Previous ticket regarding symmetry between `exact` and `iexact`: #21552
Previous ticket regarding `iexact` on JSON key lookups: #27693
--
Ticket URL: <https://code.djangoproject.com/ticket/36508>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------

Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: null, jsonfield | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Jacob Walls:

Old description:

> `exact` and `iexact` vary in their treatment of `None` when looking up
> JSON key paths:
> {{{
> optional_param = request.GET.get("param")
> qs1 = Model.objects.filter(json__key=optional_param)
> qs2 = Model.objects.filter(json__key__iexact=optional_param)
> }}}
>
> [https://dryorm.xterm.info/json-key-filter-none/run fiddle] showing
> `exact` queries return rows where the json key is null, but `iexact`
> queries do not (instead, they check for key existence, e.g.
> `__key__isnull`)
>
> Previous ticket regarding symmetry between `exact` and `iexact`: #21552
> Previous ticket regarding `iexact` on JSON key lookups: #27693

New description:

`exact` and `iexact` vary in their treatment of `None` when looking up
JSON key paths:
{{{

optional_param = request.GET.get("param", None)

qs1 = Model.objects.filter(json__key=optional_param)
qs2 = Model.objects.filter(json__key__iexact=optional_param)
}}}

[https://dryorm.xterm.info/json-key-filter-none/run fiddle] showing
`exact` queries return rows where the json key is null, but `iexact`
queries do not (instead, they check for key existence, e.g.
`__key__isnull`)

Previous ticket regarding symmetry between `exact` and `iexact`: #21552
Previous ticket regarding `iexact` on JSON key lookups: #27693

--

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

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, jsonfield | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

I feel like this falls into the same bucket, or at least very close, to
the other issues relating to trying to do the right thing when `None` is
provided to `JSONField` and that the solution lies in making `None` always
translate to `NULL` and require `JSONNull` in cases where JSON `null` is
required.
--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:2>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)

Type: Bug | Status: closed

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

Severity: Normal | Resolution: duplicate

Keywords: null, jsonfield | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* resolution: => duplicate
* status: new => closed

Comment:

I agree with you, Simon, closing this as a duplicate of #35381. Jacob,
just to double check: do you see any reason why fixing #35381 wouldn't
also resolve this ticket?
--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:3>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: null, jsonfield | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls):

Sounds right. Catching up with the comments on #35381 I see that the plan
is to change the behavior of `json__key=None` to match
`json__key__isnull=True`, so that should bring `exact` into alignment with
`iexact` which already does this. Thanks all!
--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:4>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)

Type: Bug | Status: new

Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: null, jsonfield | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sage Abdullah):

* cc: Sage Abdullah (added)
* resolution: duplicate =>
* status: closed => new

Comment:

With the discussion in #35381, I think this is a separate issue. The main
reason behind adding a `JSONNull()` that resulted in the creation of that
ticket is for addressing the ambiguity of top-level values. Even if #35381
also adds support for using `json__key=JSONNull()` (at least in the
querying case), it's clear that we do want to keep `json__key=None` to
match JSON `null` in both querying and storing cases.

If #35381 is addressed, I imagine the
[https://github.com/django/django/blob/3e7aedfb2eb37d0c83ce3ce936f1b89eb5451dfa/django/db/models/fields/json.py#L318-L320
code that handles] `None` in `JSONExact` will be replaced with a
`JSONNull` check. Then, the `can_use_none_as_rhs` probably needs to be
moved from `JSONExact` to `KeyTransformExact`.

However, it's likely `KeyTransformIExact` will need the same treatment, as
it currently only extends `CaseInsensitiveMixin,
KeyTransformTextLookupMixin, lookups.IExact` without additional code. We
might need to refactor `KeyTransformExact` so that some of its code can be
reused by `KeyTransformIExact`. From a quick guess, these changes seem
isolated to #35381.
--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:5>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: null, jsonfield | Triage Stage: Accepted

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

Changes (by Jacob Walls):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:6>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, jsonfield | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Clifford Gama):

A note that this ticket should be specifically for key transform lookups
`__key__iexact` and `__key__exact=None`, not on the top-level, as the
latter will be solved by #35381
--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:7>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------

Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned

Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, jsonfield | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* owner: (none) => Clifford Gama
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:8>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, jsonfield | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Clifford Gama):

If I understand correctly, the plan is:
1. Deprecate `json__key__iexact=None` (currently meaning
`__json__key__isnull=True`), and point users to use `__isnull=True`
instead.
2. After the deprecation period, change `__json__key__iexact=None` to
match JSON null.
--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:9>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, jsonfield | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls):

We might want to just fix without a deprecation(*), since there is no
"upgrade path". If we warn about this, and you discover you really wanted
the "new" behavior all along, the only answer would be to just wait, which
isn't great.

(*) but doc it as a minor incompatible change?

I feel like most who have gone to the trouble to specify `iexact` wouldn't
expect `iexact` to simply check key existence, and thus will want the
"new" behavior.
--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:10>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, jsonfield | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

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

Changes (by Clifford Gama):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:11>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, jsonfield | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:12>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: null, jsonfield | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:13>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: null, jsonfield | 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 Jacob Walls):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:14>

[Django]

#36508: Asymmetry between exact and iexact when filtering JSON keys against None
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama

Type: Bug | Status: closed

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

Severity: Normal | Resolution: fixed

Keywords: null, jsonfield | 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 Jacob Walls <jacobtylerwalls@…>):

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

Comment:

In [changeset:"6ee8e9d56c17eb0014e73189950a9e8de2a8ec0e" 6ee8e9d5]:
{{{#!CommitTicketReference repository=""
revision="6ee8e9d56c17eb0014e73189950a9e8de2a8ec0e"
Fixed #36508 -- Interpreted __iexact=None on KeyTransforms as
__exact=None.

Thanks Jacob Walls for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36508#comment:15>