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. My comment above shows an example code that can reproduce it.

[Tim Graham]

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