Re: [Django] #33820: Querying "null" on key transforms for JSONField returns wrong results on SQLite.

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: 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 GergelyKovach):

Seems like a type-juggling routine introduced in 4.0.0
I am totally into type-juggling but this case, and actually blocking issue
is a great example of the general confusion that a seemingly
*simplification* - may lead to hold back on tech dev.

TL;DR:
- consider removing type juggling
- keep principles up, regarding data type comparison

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

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: 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 Mariusz Felisiak):

* Attachment "regression-tests-33820.diff" added.

--
Ticket URL: <https://code.djangoproject.com/ticket/33820>

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: 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 Mariusz Felisiak):

I tried to fix this few times in the last two weeks, unfortunately, I
don't see a proper way to distinguish between `{"x": "null"}` and `{"x":
NULL}` extracted from the JSON field (the same for boolean values). Mainly
due to the lack of proper database types in SQLite. In
71ec102b01fcc85acae3819426a4e02ef423b0fa we tried to use
{{{#!sql
CASE
WHEN JSON_TYPE("model"."json_field", $."key") IN ('null', 'false',
'true')
THEN JSON_TYPE("model"."json_field", $."key")
ELSE JSON_EXTRACT("model"."json_field", $."key")
END
}}}
which fixed #32483 but also introduced this regression because for both
`NULL` and `"null"` it returns `'null'`.

Quo vadis? 🤷 We have two options:
- document this as a SQLite caveat,
- revert 71ec102b01fcc85acae3819426a4e02ef423b0fa and reintroduce another
SQLite caveat.

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

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: 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 Carlton Gibson):

> document this as a SQLite caveat,

I'd go with this. I think storing `"null"` is quite niche, and easily
worked around.

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

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: 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 Mariusz Felisiak):

Replying to [comment:4 Carlton Gibson]:

> I'd go with this. I think storing `"null"` is quite niche, and easily
worked around.

Just to be clear, this issue is for all three `"true"`, `"false"`, and
`"null"`.

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

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: 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 Carlton Gibson):

Sure, yes. My feeling is that it's better to resolve #32483, which was
71ec102b01fcc85acae3819426a4e02ef423b0fa. Most times I want
`True`/`False`/`None`, and I can serialise those if I need to. (That's
just a +1 towards option 1 in response to the ''What to do?'')

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

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: 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 Mariusz Felisiak):

* cc: Sage Abdullah (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/33820#comment:7>

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------

Reporter: Johnny Metz | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned

Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: 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 Mariusz Felisiak):

* owner: nobody => Mariusz Felisiak
* status: new => assigned
* has_patch: 0 => 1

Comment:

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

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

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: Mariusz
| Felisiak

Type: Bug | Status: closed

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

Severity: Release blocker | Resolution: fixed

Keywords: 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 GitHub <noreply@…>):

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

Comment:

In [changeset:"e20e5d1557785ba71e8ef0ceb8ccb85bdc13840a" e20e5d1]:
{{{
#!CommitTicketReference repository=""
revision="e20e5d1557785ba71e8ef0ceb8ccb85bdc13840a"
Fixed #33820 -- Doc'd "true"/"false"/"null" caveat for JSONField key
transforms on SQLite.

Thanks Johnny Metz for the report.

Regression in 71ec102b01fcc85acae3819426a4e02ef423b0fa.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33820#comment:9>

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: Mariusz
| Felisiak
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: JSONField | Triage Stage: Accepted
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:"265c3eb60287d7ba644a7796a79675b8c0d627ca" 265c3eb]:
{{{
#!CommitTicketReference repository=""
revision="265c3eb60287d7ba644a7796a79675b8c0d627ca"
[4.1.x] Fixed #33820 -- Doc'd "true"/"false"/"null" caveat for JSONField
key transforms on SQLite.

Thanks Johnny Metz for the report.

Regression in 71ec102b01fcc85acae3819426a4e02ef423b0fa.
Backport of e20e5d1557785ba71e8ef0ceb8ccb85bdc13840a from main
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33820#comment:10>

[Django]

#33820: Querying "null" on key transforms for JSONField returns wrong results on
SQLite.
-------------------------------------+-------------------------------------
Reporter: Johnny Metz | Owner: Mariusz
| Felisiak
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: JSONField | Triage Stage: Accepted
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:"f78b18f9c815c63a734ffa22ea9c57a2182b6541" f78b18f9]:
{{{
#!CommitTicketReference repository=""
revision="f78b18f9c815c63a734ffa22ea9c57a2182b6541"
[4.0.x] Fixed #33820 -- Doc'd "true"/"false"/"null" caveat for JSONField
key transforms on SQLite.

Thanks Johnny Metz for the report.

Regression in 71ec102b01fcc85acae3819426a4e02ef423b0fa.
Backport of e20e5d1557785ba71e8ef0ceb8ccb85bdc13840a from main
}}}

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