[Django] #29009: This queryset contains a reference to an outer query and may only be used in a subquery

Django

unread,

Jan 11, 2018, 5:26:28 AM1/11/18

to django-...@googlegroups.com

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?

--
Ticket URL: <https://code.djangoproject.com/ticket/29009>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jan 11, 2018, 7:29:05 PM1/11/18

to django-...@googlegroups.com

#29009: 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"
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* status: new => closed
* resolution: => invalid

Comment:

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/29009#comment:1>

Django

unread,

Jul 23, 2018, 10:02:19 AM7/23/18

to django-...@googlegroups.com

#29009: 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"
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29009#comment:2>

Django

unread,

Jul 25, 2018, 5:25:08 AM7/25/18

to django-...@googlegroups.com

#29009: 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"
-------------------------------------+-------------------------------------

Reporter: Vasiliy Maryutenkov | Owner: nobody

Type: Uncategorized | Status: new

Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by nadzimo):

* status: closed => new
* resolution: invalid =>

Comment:

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/29009#comment:3>

Django

unread,

Jul 25, 2018, 7:19:20 AM7/25/18

to django-...@googlegroups.com

#29009: 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"
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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?

--
Ticket URL: <https://code.djangoproject.com/ticket/29009#comment:4>

Django

unread,

Jul 27, 2018, 9:18:04 AM7/27/18

to django-...@googlegroups.com

#29009: 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"
-------------------------------------+-------------------------------------

Reporter: Vasiliy Maryutenkov | Owner: nobody
Type: Bug | Status: closed

Component: Database layer | Version: 2.0
(models, ORM) |

Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------