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"

[Vasiliy Maryutenkov]

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?

[Tim Graham]

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.

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

[nadzimo]

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.

[Tim Graham]

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