Opened 11 months ago

Closed 5 months ago

#29009 closed Bug (needsinfo)

Error using jsonpickle.encode() on a QuerySet with a Subquery: "This queryset contains a reference to an outer query and may only be used in a subquery"

Reported by: Vasiliy Maryutenkov Owned by: nobody
Component: Database layer (models, ORM) Version: 2.0
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Hello

I've tried to serialize queryset with Subquery object and it failed with that error.
"This queryset contains a reference to an outer query and may only be used in a subquery"

subquery = Transaction.objects.filter(user=OuterRef('pk')).values('user').order_by('user').annotate(txs=Count('*')).annotate(txs_value=Sum(F('tx_value')*F('symbol__price_in_eth'))).annotate(txs_value_eur=Sum((F('tx_value')*F('symbol__price_in_eth'))/eur.price_in_eth))
investors_qs = User.objects.filter(ether_wallet__isnull=False).annotate(txs=Subquery(subquery.values('txs')[:1])).annotate(txs_value=Subquery(subquery.values('txs_value')[:1])).annotate(txs_value_eur=Subquery(subquery.values('txs_value_eur')[:1]))

jsonpickle.encode(investors_qs.query)
 'This queryset contains a reference to an outer query and may '
ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

is it normal behaviour or a bug?

Change History (5)

comment:1 Changed 11 months ago by Tim Graham

Resolution: invalid
Status: newclosed
Summary: This queryset contains a reference to an outer query and may only be used in a subqueryError using jsonpickle.encode() on a QuerySet with a Subquery: "This queryset contains a reference to an outer query and may only be used in a subquery"

I'm not sure how jsonpickle.encode() works but if it's trying to evaluate investors_qs.query, as a string, then I see that the exception raised is in the path that generates the SQL. In general, "is it a bug?" questions should go to our support channels. See TicketClosingReasons/UseSupportChannels.

comment:2 Changed 5 months ago by nadzimo

I was also trying to cache a Queryset that has a Subquery and an OuterRef, and am getting the same error.
Using Redis as a caching backend. I also tried to directly pickle the queryset with python's pickle and I get the same error. Also tried caching qs.query, same issue.

qs = self.queryset.filter(
                    Q(user__in=Follow.objects.filter(
                                                follower=userid,
                                                ).values('followee')) \
                    | Q(user=userid))\
                    .prefetch_related('postlike_set')\
                    .annotate(Count('postlike'))\
                    .annotate(Count('postcomment'))\
                    .annotate(
                        liked=Subquery(
                        PostLike.objects.filter(user=self.request.user,
                                                post_id=OuterRef('id'),
                                                ).values('user')
                                       )
                           )
cache.set(f'feed:{userid}', qs)
File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django_redis/client/default.py", line 326, in encode
    value = self._serializer.dumps(value)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django_redis/serializers/pickle.py", line 32, in dumps
    return pickle.dumps(value, self._pickle_version)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/query.py", line 224, in __getstate__
    self._fetch_all()
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/query.py", line 1179, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/query.py", line 107, in __iter__
    for row in compiler.results_iter(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size):
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1014, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1050, in execute_sql
    sql, params = self.as_sql()
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 458, in as_sql
    where, w_params = self.compile(self.where) if self.where is not None else ("", [])
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 390, in compile
    sql, params = node.as_sql(self, self.connection)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/sql/where.py", line 80, in as_sql
    sql, params = compiler.compile(child)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 390, in compile
    sql, params = node.as_sql(self, self.connection)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/fields/related_lookups.py", line 130, in as_sql
    return super().as_sql(compiler, connection)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/lookups.py", line 161, in as_sql
    rhs_sql, rhs_params = self.process_rhs(compiler, connection)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/lookups.py", line 260, in process_rhs
    return super().process_rhs(compiler, connection)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/lookups.py", line 90, in process_rhs
    sql, params = compiler.compile(value)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 390, in compile
    sql, params = node.as_sql(self, self.connection)
  File "/Users/my-app/app/venv-app/lib/python3.6/site-packages/django/db/models/expressions.py", line 536, in as_sql
    'This queryset contains a reference to an outer query and may '
ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

comment:3 Changed 5 months ago by nadzimo

Resolution: invalid
Status: closednew

I'd like to reopen this ticket as this is isn't related to usage of third party but seems to be caused by Django itself. My comment above shows an example code that can reproduce it.

Last edited 5 months ago by nadzimo (previous) (diff)

comment:4 Changed 5 months ago by Tim Graham

Does the error only happen when pickling the queryset? Can you explain why Django is at fault? Is the error message incorrect?

comment:5 Changed 5 months ago by Tim Graham

Resolution: needsinfo
Status: newclosed
Type: UncategorizedBug
Note: See TracTickets for help on using tickets.
Back to Top