[Django] #36085: SQLite backend raises exception on negative array indices in JSONField

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database | Version: 5.1
layer (models, ORM) |
Severity: Normal | Keywords: sqlite, jsonfield
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Positive jsonfield array index queries can be constructed the usual way
with kwargs (ie. `Model.objects.filter(jsonfield__2=1)`), while negative
jsonfield array index query must be constructed using the splat operator
on a dict: `Model.objects.filter(**{"jsonfield__-2": 1})`). When querying
jsonfields in this way **on the SQLite backend**, the following error
arises:

{{{django.db.utils.OperationalError: bad JSON path: '$[-2]'}}}

When constructing the query, the JSON path constructor simply appends any
numerical value to the path
(https://github.com/django/django/blob/5.1.4/django/db/models/fields/json.py#L155).
But the SQLite backend uses a special syntax for performing negative-
indexing in jsonfields (https://sqlite.org/json1.html#path_arguments) that
is different from other backends: negative indices must be prepended by a
literal `#` character.
--
Ticket URL: <https://code.djangoproject.com/ticket/36085>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned

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

Keywords: sqlite, jsonfield | Triage Stage:

| Unreviewed
Has patch: 1 | Needs documentation: 0

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

Changes (by savanto):

* has_patch: 0 => 1

Comment:

https://github.com/django/django/pull/19030
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:1>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite, 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 Tim Graham):

* needs_better_patch: 0 => 1
* stage: Unreviewed => Accepted

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

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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 Sage Abdullah):

* cc: Sage Abdullah (added)

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

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, jsonfield | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by hesham hatem):

Thanks savanto for your contributions!!

I hope you and tim graham and lay monage find my PR well done.
thanks again!!

https://github.com/django/django/pull/19182
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:4>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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 Antoliny):

* needs_better_patch: 1 => 0

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

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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 Jacob Walls):

* needs_better_patch: 0 => 1

Comment:

A [https://github.com/django/django/pull/19182 PR] was opened to start
with a related refactor. It needs a little tweak.
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:6>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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 savanto):

* needs_better_patch: 1 => 0

Comment:

Since https://github.com/django/django/pull/19182 was closed without merge
a few weeks ago, I incorporated the refactor work from it back into
https://github.com/django/django/pull/19030 and made the suggested changes
from discussions from both PRs there.
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:7>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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/36085#comment:8>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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 Sage Abdullah):

* needs_better_patch: 1 => 0

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

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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 moshe nahmias):

It seems like the comments were done and test passes on the PR branch but
fails on the main branch
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:10>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite, 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 moshe nahmias):

* stage: Accepted => Ready for checkin

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

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite, jsonfield | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1

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

Changes (by Sarah Boyce):

* needs_docs: 0 => 1
* stage: Ready for checkin => Accepted

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

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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 Jacob Walls):

* needs_docs: 1 => 0

Comment:

Friendly reminder to update the ticket flags when you're ready for another
review.
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:13>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite, 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 savanto):

* stage: Accepted => Ready for checkin

Comment:

Oh, ok, thanks! I added documentation and squashed into a single commit,
so this is ready for final review.
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:14>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

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

* stage: Ready for checkin => Accepted

Comment:

Sounds good. Ready for checkin is only set by reviewers FYI, I was
referring to the "needs..." flags, but no worries.
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:15>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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 savanto):

Thanks/sorry =)
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:16>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite, 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 Sarah Boyce):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:17>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite, 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

* stage: Ready for checkin => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:18>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite, 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 Sarah Boyce):

* needs_better_patch: 1 => 0

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:19>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, 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
-------------------------------------+-------------------------------------

Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"a8716f3c4c6d78d38ce86d79c816062346dcc5bf" a8716f3c]:
{{{#!CommitTicketReference repository=""
revision="a8716f3c4c6d78d38ce86d79c816062346dcc5bf"
Refs #36085 -- Moved compile_json_path to BaseDatabaseOperations.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:20>

[Django]

#36085: SQLite backend raises exception on negative array indices in JSONField
-------------------------------------+-------------------------------------
Reporter: savanto | Owner: savanto

Type: Bug | Status: closed

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

Severity: Normal | Resolution: fixed

Keywords: sqlite, 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 Sarah Boyce <42296566+sarahboyce@…>):

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

Comment:

In [changeset:"8620a3b0c79e4e8098b88f1176ed26fad0bf6c5c" 8620a3b0]:
{{{#!CommitTicketReference repository=""
revision="8620a3b0c79e4e8098b88f1176ed26fad0bf6c5c"
Fixed #36085 -- Added JSONField support for negative array indexing on
SQLite.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36085#comment:21>