Re: [Django] #33548: Use -> operator to implement KeyTransform on SQLite 3.38+

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite jsonfield | Triage Stage: Accepted
keytransform |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by bcail):

* cc: bcail (added)

Comment:

For what it's worth, the SQLite tests pass on SQLite 3.39.4 if you replace
JSON_EXTRACT with ->>, like this:
{{{
diff --git a/django/db/models/fields/json.py
b/django/db/models/fields/json.py
index 7296fe42bc..accc6ae61a 100644
--- a/django/db/models/fields/json.py
+++ b/django/db/models/fields/json.py
@@ -361,7 +361,7 @@ class KeyTransform(Transform):
)
return (
"(CASE WHEN JSON_TYPE(%s, %%s) IN (%s) "
- "THEN JSON_TYPE(%s, %%s) ELSE JSON_EXTRACT(%s, %%s) END)"
+ "THEN JSON_TYPE(%s, %%s) ELSE (%s ->> %%s) END)"
) % (lhs, datatype_values, lhs, lhs), (tuple(params) +
(json_path,)) * 3

}}}

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

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner:

Type: | rajdesai24
Cleanup/optimization | Status: assigned

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

Changes (by rajdesai24):

* owner: nobody => rajdesai24
* status: new => assigned

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

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner:
Type: | rajdesai24
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite jsonfield | Triage Stage: Accepted
keytransform |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by rajdesai24):

I am quite new to this topic but based on what I can understand how about
we put a simple version check and return the value accordingly
something like the following which checks the version and returns the
value accordingly.
{{{
if connection.sqlite_version >= '3.38':

return (
"(CASE WHEN JSON_TYPE(%s, %%s) IN (%s) "

"THEN JSON_TYPE(%s, %%s) ELSE (%s ->> %%s) END)"
) % (lhs, datatype_values, lhs, lhs), (tuple(params) +
(json_path,)) * 3

else:

return (
"(CASE WHEN JSON_TYPE(%s, %%s) IN (%s) "

"THEN JSON_TYPE(%s, %%s) ELSE JSON_EXTRACT(%s, %%s) END)"

) % (lhs, datatype_values, lhs, lhs), (tuple(params) +
(json_path,)) * 3

}}}
This might make it redundant but please let me know of any suggestions.
Till then I will dive deeper

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

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner:
Type: | rajdesai24
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite jsonfield | Triage Stage: Accepted
keytransform |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by rajdesai24):

* cc: rajdesai24 (added)

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

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner:
Type: | rajdesai24
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite jsonfield | Triage Stage: Accepted
keytransform |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Paolo Melchiorre):

* cc: Paolo Melchiorre (added)

Comment:

This change would be an improvement. +1

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

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner:
Type: | rajdesai24
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite jsonfield | Triage Stage: Accepted
keytransform |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by bcail):

@rajdesai24, are you planning to open a PR for this?
--
Ticket URL: <https://code.djangoproject.com/ticket/33548#comment:7>

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------

Reporter: Sage Abdullah | Owner: (none)
Type: | Status: new
Cleanup/optimization |

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

Changes (by Claude Paroz):

* owner: rajdesai24 => (none)
* status: assigned => new

Comment:

13 months after an assignee last commented, I guess we can assume it is
open to anyone willing to work on it.
--
Ticket URL: <https://code.djangoproject.com/ticket/33548#comment:8>

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------

Reporter: Sage Abdullah | Owner: Anže
Type: | Pečar
Cleanup/optimization | Status: assigned

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

Changes (by Anže Pečar):

* owner: (none) => Anže Pečar

* status: new => assigned

Comment:

I have found this ticket during DjangoCon Europe sprints and opened a PR
for it: https://github.com/django/django/pull/18241
--
Ticket URL: <https://code.djangoproject.com/ticket/33548#comment:9>

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner: Anže
Type: | Pečar
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite jsonfield | Triage Stage: Accepted
keytransform |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Paolo Melchiorre):

As we discussed during the DjangoCon Europe 2024 sprints, we can't be sure
of the SQLite version in some cases, so a version check can be useful.
--
Ticket URL: <https://code.djangoproject.com/ticket/33548#comment:10>

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner: Anže
Type: | Pečar
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite jsonfield | Triage Stage: Accepted
keytransform |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Anže Pečar):

I have added the version check to my PR, thank you Paolo, for the quick PR
review!
--
Ticket URL: <https://code.djangoproject.com/ticket/33548#comment:11>

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner: Anže
Type: | Pečar
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite jsonfield | Triage Stage: Accepted
keytransform |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Paolo Melchiorre):

The PR looks better now. Thanks for update it.
--
Ticket URL: <https://code.djangoproject.com/ticket/33548#comment:12>

[Django]

#33548: Use -> operator to implement KeyTransform on SQLite 3.38+
-------------------------------------+-------------------------------------
Reporter: Sage Abdullah | Owner: Anže
Type: | Pečar
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite jsonfield | Triage Stage: Accepted
keytransform |

Has patch: 1 | Needs documentation: 0

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

Changes (by Jacob Walls):

* has_patch: 0 => 1

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