[Django] #28872: JSONField __in operator breaks when given a values_list()
Django
--------------------------------------------+------------------------
Reporter: Jerome Leclanche | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.11
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------------+------------------------
Prerequisites
1. Create a model MyModel1 with the field `resource = JSONField()`.
2. Create another model MyModel2 with a field `id =
CharField(max_length=255)`
Reproduce by doing
MyModel1.objects.filter(resource__foo__id__in=MyModel2.objects.all().values_list("id"))
Expected result: Should look at MyModel1 for objects with `resource`
matching `{"foo": {"id": <id>}}`.
Actual result:
ERROR: operator does not exist: jsonb = character varying
LINE 1: ...AND ("resource" #> '{foo,id}') IN (SELECT...
The actual issue is that when passing a values_list() to __in, the values
list is not cast to jsonb (unlike when passing a text type). In the
resulting sql, we can see `IN (SELECT U0."id" AS Col1 FROM mymodel2 U0
...)`. Wrapping `to_json(U0."id")` solves the issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/28872>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Jerome Leclanche | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.11
Keywords: | 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 Jerome Leclanche:
Old description:
> Prerequisites
>
> 1. Create a model MyModel1 with the field `resource = JSONField()`.
> 2. Create another model MyModel2 with a field `id =
> CharField(max_length=255)`
>
> Reproduce by doing
> MyModel1.objects.filter(resource__foo__id__in=MyModel2.objects.all().values_list("id"))
>
> Expected result: Should look at MyModel1 for objects with `resource`
> matching `{"foo": {"id": <id>}}`.
>
> Actual result:
>
> ERROR: operator does not exist: jsonb = character varying
> LINE 1: ...AND ("resource" #> '{foo,id}') IN (SELECT...
>
> The actual issue is that when passing a values_list() to __in, the values
> list is not cast to jsonb (unlike when passing a text type). In the
> resulting sql, we can see `IN (SELECT U0."id" AS Col1 FROM mymodel2 U0
> ...)`. Wrapping `to_json(U0."id")` solves the issue.
New description:
Prerequisites
1. Create a model MyModel1 with the field `resource = JSONField()`.
2. Create another model MyModel2 with a field `id =
CharField(max_length=255)`
Reproduce by doing
`MyModel1.objects.filter(resource__foo__id__in=MyModel2.objects.all().values_list("id"))`
Expected result: Should look at MyModel1 for objects with `resource`
matching `{"foo": {"id": <id>}}`.
Actual result:
ERROR: operator does not exist: jsonb = character varying
LINE 1: ...AND ("resource" #> '{foo,id}') IN (SELECT...
The actual issue is that when passing a values_list() to __in, the values
list is not cast to jsonb (unlike when passing a text type). In the
resulting sql, we can see `IN (SELECT U0."id" AS Col1 FROM mymodel2 U0
...)`. Wrapping `to_json(U0."id")` solves the issue.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:1>
Django
Reporter: Jerome Leclanche | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.11
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* stage: Unreviewed => Accepted
Old description:
> Prerequisites
>
> 1. Create a model MyModel1 with the field `resource = JSONField()`.
> 2. Create another model MyModel2 with a field `id =
> CharField(max_length=255)`
>
> Reproduce by doing
> `MyModel1.objects.filter(resource__foo__id__in=MyModel2.objects.all().values_list("id"))`
>
> Expected result: Should look at MyModel1 for objects with `resource`
> matching `{"foo": {"id": <id>}}`.
>
> Actual result:
>
> ERROR: operator does not exist: jsonb = character varying
> LINE 1: ...AND ("resource" #> '{foo,id}') IN (SELECT...
>
> The actual issue is that when passing a values_list() to __in, the values
> list is not cast to jsonb (unlike when passing a text type). In the
> resulting sql, we can see `IN (SELECT U0."id" AS Col1 FROM mymodel2 U0
> ...)`. Wrapping `to_json(U0."id")` solves the issue.
New description:
Prerequisites
1. Create a model MyModel1 with the field `resource = JSONField()`.
2. Create another model MyModel2 with a field `id =
CharField(max_length=255)`
Reproduce by doing
`MyModel1.objects.filter(resource__foo__id__in=MyModel2.objects.all().values_list("id"))`
Expected result: Should look at MyModel1 for objects with `resource`
matching `{"foo": {"id": <id>}}`.
Actual result:
{{{
ERROR: operator does not exist: jsonb = character varying
LINE 1: ...AND ("resource" #> '{foo,id}') IN (SELECT...
}}}
The actual issue is that when passing a `values_list()` to `__in`, the
values list is not cast to jsonb (unlike when passing a text type). In the
resulting sql, we can see `IN (SELECT U0."id" AS Col1 FROM mymodel2 U0
...)`. Wrapping `to_json(U0."id")` solves the issue.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:2>
Django
Reporter: Jerome Leclanche | Owner: VCAMP
Type: Bug | Status: assigned
Component: contrib.postgres | Version: 1.11
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by VCAMP):
* owner: (none) => VCAMP
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:3>
Django
Reporter: Jerome Leclanche | Owner: VCAMP
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by VCAMP):
* cc: VCAMP (added)
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:4>
Django
Reporter: Jerome Leclanche | Owner: VCAMP
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:5>
Django
Reporter: Jerome Leclanche | Owner: VCAMP
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* version: 1.11 => master
Comment:
With the new implementation of `JSONField` this issue is PostgreSQL-
specific.
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:6>
Django
Reporter: Jerome Leclanche | Owner: VCAMP
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* component: contrib.postgres => Database layer (models, ORM)
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:7>
Django
Reporter: Jerome Leclanche | Owner: (none)
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* owner: VCAMP => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:8>
Django
Reporter: Jerome Leclanche | Owner:
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Easy pickings: 0 | UI/UX: 0
Changes (by JaeHyuckSa):
* needs_better_patch: 1 => 0
* owner: (none) => JaeHyuckSa
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:9>
Django
Reporter: Jerome Leclanche | Owner:
| JaeHyuckSa
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Changes (by Simon Charette):
* needs_better_patch: 0 => 1
Patch had a few issues notably
- It mutated a source expression during compilation
- It altered `sql.Query.select` and `.annotations` in a way that breaks
referenceing (`group_by`, `order_by`)
I suggested an alternative approach using a subquery pushdown instead.
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:10>
Django
Reporter: Jerome Leclanche | Owner:
| JaeHyuckSa
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Easy pickings: 0 | UI/UX: 0
Changes (by JaeHyuckSa):
* needs_better_patch: 1 => 0
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:11>
Django
Reporter: Jerome Leclanche | Owner:
| JaeHyuckSa
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 1 | Patch needs improvement: 0
Changes (by Jacob Walls):
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:12>
Django
Reporter: Jerome Leclanche | Owner: Pycon
| Korea Sprints
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 1 | Patch needs improvement: 0
Changes (by Antoliny):
* owner: JaeHyuckSa => Pycon Korea Sprints
Comment:
Attempting to assign to Pycon Korea Sprints to make it easier to reserve
tickets. Pycon Korea ends August 17, this is available for someone else to
pick up on the 18th.
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:13>
Django
Reporter: Jerome Leclanche | Owner: Frantz
| Paul
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 1 | Patch needs improvement: 0
Changes (by Frantz Paul):
* owner: Django Sprints => Frantz Paul
--
Ticket URL: <https://code.djangoproject.com/ticket/28872#comment:15>