Opened 9 years ago

Closed 2 years ago

Last modified 16 months ago

#26056 closed New feature (fixed)

ArrayField does not work with ValueListQuerySet

Reported by: Przemek Owned by: bcail
Component: contrib.postgres Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

Basically queries of type:

A.objects.filter(array_field__overlap=B.objects.filter(foo).values_list('id', flat=True))

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.

Change History (18)

comment:1 by Simon Charette, 9 years ago

Triage Stage: UnreviewedAccepted
Type: BugNew feature
Version: 1.9master

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.

comment:2 by Tim Graham, 9 years ago

Description: modified (diff)

comment:3 by Mads Jensen, 8 years ago

Has patch: set
Needs documentation: set

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.

comment:4 by Simon Charette, 8 years ago

Patch needs improvement: set

comment:5 by Mads Jensen, 8 years ago

Needs documentation: unset

It has been reworked but I suppose other lookups in django.contrib.postgres will want a similar functionality.

comment:6 by Alex Krupp, 8 years ago

Does the new Subquery functionality in Django 1.11 allow for a similar result? E.g. would this work?:

qs_b = B.objects.filter(foo)
A.objects.filter(array_field__overlap=Subquery(qs_b.values_list('id', flat=True)))

EDIT: This approach does not work.

Last edited 8 years ago by Alex Krupp (previous) (diff)

comment:7 by kosz85, 7 years ago

I posted solution here: https://github.com/django/django/pull/7838#issuecomment-337223376
It's Array with Subquery, just pure subquery isn't enough.

comment:8 by Peter Law, 3 years ago

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:

A.objects.filter(
    array_field__overlap=models.Func(
        B.objects.values('pk'),
        function='array',
    ),
)

comment:9 by Simon Charette, 3 years ago

Now that #32776 landed in the main branch this issue should be straightforward to fix by adjusting the Overlap.process_rhs lookup by wrapping a QuerySet instance passed as right-hand-side with ArraySubquery.

Last edited 3 years ago by Simon Charette (previous) (diff)

comment:10 by bcail, 2 years ago

@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(

comment:11 by Simon Charette, 2 years ago

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.

comment:12 by bcail, 2 years ago

Patch needs improvement: unset

comment:13 by Mariusz Felisiak, 2 years ago

Owner: set to bcail
Status: newassigned

comment:14 by Mariusz Felisiak, 2 years ago

Patch needs improvement: set

comment:15 by Mariusz Felisiak, 2 years ago

Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:16 by Mariusz Felisiak <felisiak.mariusz@…>, 2 years ago

Resolution: fixed
Status: assignedclosed

In fbde929:

Fixed #26056 -- Added QuerySet.values()/values_list() support for ArrayField's overlap lookup.

Thanks Mads Jensen and kosz85 and the initial patch.

comment:17 by Martin Lehoux, 16 months ago

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).

comment:18 by Mariusz Felisiak, 16 months ago

Martin, thanks for the report, however it works for me with an empty subquery, e.g.

  • tests/postgres_tests/test_array.py

    diff --git a/tests/postgres_tests/test_array.py b/tests/postgres_tests/test_array.py
    index f7615c974e..9a3966a821 100644
    a b class TestQuerying(PostgreSQLTestCase):  
    423423            self.objs[:3],
    424424        )
    425425
     426    def test_overlap_values_empty(self):
     427        qs = NullableIntegerArrayModel.objects.filter(order__lt=0)
     428        self.assertSequenceEqual(
     429            NullableIntegerArrayModel.objects.filter(
     430                field__overlap=qs.values_list("field"),
     431            ),
     432            [],
     433        )
     434        self.assertSequenceEqual(
     435            NullableIntegerArrayModel.objects.filter(
     436                field__overlap=NullableIntegerArrayModel.objects.none().values("field"),
     437            ),
     438            [],
     439        )
     440
    426441    def test_lookups_autofield_array(self):
    427442        qs = (
    428443            NullableIntegerArrayModel.objects.filter(

If you believe it's an issue in Django, then please create a new ticket in Trac and follow our bug reporting guidelines. A sample project that reproduces your issue would be very helpful.

Note: See TracTickets for help on using tickets.
Back to Top