[Django] #26056: ArrayField does not work with ValueListQuerySet
Django
----------------------------------+-----------------
Reporter: CGenie | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+-----------------
Basically queries of type:
{{{#!python
A.objects.filter(array_field__overlap=B.objects.filter(foo))
}}}
fail at Python level:
{{{
Traceback (most recent call last):
File "failing.py", line 9, in <module>
UserList.objects.filter(users__overlap=User.objects.all().values_list('id',
flat=True)).count()
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/db/models/query.py", line 318, in
count
return self.query.get_count(using=self.db)
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/db/models/sql/query.py", line 466,
in get_count
number = obj.get_aggregation(using, ['__count'])['__count']
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/db/models/sql/query.py", line 447,
in get_aggregation
result = compiler.execute_sql(SINGLE)
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/db/models/sql/compiler.py", line
829, in execute_sql
sql, params = self.as_sql()
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/db/models/sql/compiler.py", line
387, in as_sql
where, w_params = self.compile(self.query.where)
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/db/models/sql/compiler.py", line
357, in compile
sql, params = node.as_sql(self, self.connection)
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/db/models/sql/where.py", line 104,
in as_sql
sql, params = compiler.compile(child)
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/db/models/sql/compiler.py", line
357, in compile
sql, params = node.as_sql(self, self.connection)
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/contrib/postgres/fields/array.py",
line 183, in as_sql
sql, params = super(ArrayOverlap, self).as_sql(qn, connection)
File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-
packages/Django-1.8.5-py2.7.egg/django/contrib/postgres/lookups.py", line
8, in as_sql
params = lhs_params + rhs_params
TypeError: can only concatenate list (not "tuple") to list
}}}
Toy project to reproduce this behavior can be found here:
https://github.com/CGenie/django_array_join_fail
This fails in 1.8 as well as in 1.9.
--
Ticket URL: <https://code.djangoproject.com/ticket/26056>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: CGenie | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
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 charettes):
* needs_better_patch: => 0
* needs_tests: => 0
* version: 1.9 => master
* needs_docs: => 0
* type: Bug => New feature
* stage: Unreviewed => Accepted
Comment:
Accepting as a new feature by assuming you meant
`B.objects.filter(foo).values('pk')` in your reported example.
The implementation could simply use the PostgreSQL `array()` function to
wrap the queryset.
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:1>
Django
Reporter: CGenie | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Description changed by timgraham:
Old description:
New description:
Basically queries of type:
{{{#!python
A.objects.filter(array_field__overlap=B.objects.filter(foo).values_list('id',
flat=True))
}}}
fail at Python level:
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:2>
Django
Reporter: Przemek | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mads Jensen):
* needs_docs: 0 => 1
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/7838 PR] it follows the suggestion
to wrap a query inside `array`. Needs documentation is marked, since the
release notes need to be updated, and probably also a note in the
documentation itself.
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:3>
Django
Reporter: Przemek | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:4>
Django
Reporter: Przemek | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Changes (by Mads Jensen):
* needs_docs: 1 => 0
Comment:
It has been reworked but I suppose other lookups in
`django.contrib.postgres` will want a similar functionality.
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:5>
Django
Reporter: Przemek | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Alex3917):
Does the new Subquery functionality is Django 1.11 allow for a similar
result? E.g.:
{{{
qs_b = B.objects.filter(foo)
A.objects.filter(array_field__overlap=Subquery(qs_b.values_list('id',
flat=True)))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:6>
Django
Reporter: Przemek | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by kosz85):
I posted solution here:
https://github.com/django/django/pull/7838#issuecomment-337223376
It's Array with Subquery, just pure subquery isn't enough.
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:7>
Django
Reporter: Przemek | Owner: (none)
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Peter Law):
For anyone who ends up searching for this, the failure (as of 2.2 and
possibly earlier) is a `ProgrammingError` from the database rather than
the `TypeError` mentioned above.
The workaround mentioned in
https://github.com/django/django/pull/7838#issuecomment-337223376
continues to work:
{{{#!python
A.objects.filter(
array_field__overlap=models.Func(
B.objects.values('pk'),
function='array',
),
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:8>
Django
Reporter: Przemek | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Simon Charette):
Now that #32776 landed in the main branch this issue should be
straightforward to fix adjusting the `Overlap.lookup`
[https://github.com/django/django/blob/6a5ef557f80a8eb6a758ebe99c8bb477ca47459e/django/contrib/postgres/lookups.py#L17-L19
lookup] by wrapping a `QuerySet` instance into an `ArraySubquery`.
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:9>
Django
Reporter: Przemek | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by bcail):
@Simon, were you thinking something like this (modified from the PR)?
{{{
diff --git a/django/contrib/postgres/lookups.py
b/django/contrib/postgres/lookups.py
index f2f88ebc0a..efb2fb6b12 100644
--- a/django/contrib/postgres/lookups.py
+++ b/django/contrib/postgres/lookups.py
@@ -1,4 +1,5 @@
from django.db.models import Transform
+from django.db.models.sql.compiler import Query
from django.db.models.lookups import PostgresOperatorLookup
from .search import SearchVector, SearchVectorExact, SearchVectorField
@@ -18,6 +19,13 @@ class Overlap(PostgresOperatorLookup):
lookup_name = "overlap"
postgres_operator = "&&"
+ def process_rhs(self, qn, connection):
+ if isinstance(self.rhs, Query):
+ from .expressions import ArraySubquery
+ self.rhs = ArraySubquery(self.rhs)
+ rhs, params = super().process_rhs(qn, connection)
+ return rhs, params
+
class HasKey(PostgresOperatorLookup):
lookup_name = "has_key"
diff --git a/tests/postgres_tests/test_array.py
b/tests/postgres_tests/test_array.py
index c23ed9fe0c..9dd56630f9 100644
--- a/tests/postgres_tests/test_array.py
+++ b/tests/postgres_tests/test_array.py
@@ -384,6 +384,19 @@ class TestQuerying(PostgreSQLTestCase):
[obj_1, obj_2],
)
+ def test_overlap_values_array_field(self):
+ # issue 26056
+ post1 = CharArrayModel(field=['django'])
+ post2 = CharArrayModel(field=['thoughts'])
+ post3 = CharArrayModel(field=['tutorial'])
+ CharArrayModel.objects.bulk_create([post1, post2, post3])
+ qs =
CharArrayModel.objects.filter(field__overlap=CharArrayModel.objects.values_list('field',
flat=True))
+ self.assertSequenceEqual(qs.values_list('field', flat=True), [
+ (['django']),
+ (['thoughts']),
+ (['tutorial']),
+ ])
+
def test_lookups_autofield_array(self):
qs = (
NullableIntegerArrayModel.objects.filter(
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:10>
Django
Reporter: Przemek | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Simon Charette):
bcail, yep that seems about right!
Could you submit a PR that does that with a commit message that mentions
Mads Jensen and kosz85 efforts and add a mention to the release notes for
4.2?
Once done unchecking ''patch needs improvement'' here should get it under
reviewers' eyes.
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:11>
Django
Reporter: Przemek | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by bcail):
* needs_better_patch: 1 => 0
Comment:
New PR: https://github.com/django/django/pull/16303
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:12>
Django
Reporter: Przemek | Owner: bcail
Type: New feature | Status: assigned
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* owner: (none) => bcail
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:13>
Django
Reporter: Przemek | Owner: bcail
Type: New feature | Status: assigned
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:14>
Django
Reporter: Przemek | Owner: bcail
Type: New feature | Status: assigned
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:15>
Django
Reporter: Przemek | Owner: bcail
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"fbde929b19754f19cba1d14e86f4c59f4b0a633c" fbde929]:
{{{
#!CommitTicketReference repository=""
revision="fbde929b19754f19cba1d14e86f4c59f4b0a633c"
Fixed #26056 -- Added QuerySet.values()/values_list() support for
ArrayField's __overlap lookup.
Thanks Mads Jensen and kosz85 and the initial patch.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:16>
Django
Reporter: Przemek | Owner: bcail
Type: New feature | Status: closed
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Martin Lehoux):
Hi! I feel late to the battle, because I just upgraded from Django 4.1 to
Django 4.2, and I have an issue with this improvement.
I'll try to give a short example.
{{{
class Post(Model):
name = CharField()
tags = ArrayField(CharField())
class User(Model):
post = ForeignKey(Post)
current_post = Post.objects.filter(pk=OuterRef("post"))
matching_posts =
Post.objects.filter(Q(tags__overlap=current_post.values("tags"))
user = User.objects.annotate(matching_posts=matching_posts.values("name"))
}}}
This worked well before because we rely on current_post.values to return a
single value.
But now, this current_post.values is wrapped with a call to Array, and I
get `cannot accumulate empty arrays` error if the post has no tags.
Before:
{{{
WHERE tags && (SELECT tags FROM posts WHERE id = 123)
}}}
Now:
{{{
WHERE tags && ARRAY(SELECT tags FROM posts WHERE id = 123)
}}}
Maybe there is another (and better) way to achieve this, but it seems that
it has broken my use of .values in the right side.
If this is not clear enough, please tell me and I will setup a more
thorough example (I haven't tested this one, it's extracted from my work
codebase).
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:17>
Django
Reporter: Przemek | Owner: bcail
Type: New feature | Status: closed
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak):
Martin, thanks for the report, however it works for me with an empty
subquery, e.g.
{{{#!diff
diff --git a/tests/postgres_tests/test_array.py
b/tests/postgres_tests/test_array.py
index f7615c974e..9a3966a821 100644
--- a/tests/postgres_tests/test_array.py
+++ b/tests/postgres_tests/test_array.py
@@ -423,6 +423,21 @@ class TestQuerying(PostgreSQLTestCase):
self.objs[:3],
)
+ def test_overlap_values_empty(self):
+ qs = NullableIntegerArrayModel.objects.filter(order__lt=0)
+ self.assertSequenceEqual(
+ NullableIntegerArrayModel.objects.filter(
+ field__overlap=qs.values_list("field"),
+ ),
+ [],
+ )
+ self.assertSequenceEqual(
+ NullableIntegerArrayModel.objects.filter(
+
field__overlap=NullableIntegerArrayModel.objects.none().values("field"),
+ ),
+ [],
+ )
+
def test_lookups_autofield_array(self):
qs = (
NullableIntegerArrayModel.objects.filter(
}}}
If you believe it's an issue in Django, then please create a **new
ticket** in Trac and follow our
[https://docs.djangoproject.com/en/dev/internals/contributing/bugs-and-
features/#reporting-bugs bug reporting guidelines]. A sample project that
reproduces your issue would be very helpful.
--
Ticket URL: <https://code.djangoproject.com/ticket/26056#comment:18>