Can't use OuterRef in union Subquery

[Matthew Pava]

When you make a QuerySet using the union method or the | operator, the QuerySet passed into the union method cannot reference OuterRef even when wrapped with Subquery.

For example:

cls = Document.objects.filter(
    checklist__isnull=False,
    part=OuterRef('id')
).values('checklist__customer', 'created')

ots = Document.objects.filter(
    ownershiptransfer__isnull=False,
    part=OuterRef('id')
).values('ownershiptransfer__ship_target__contact', 'created')

return self.annotate(
    owner=Subquery(cls.union(ots).values('owner')[:1])
)

Returns this error:

ValueError
This queryset contains a reference to an outer query and may only be used in a subquery.

I get the same error with this statement:

return self.annotate(
    owner=Subquery((cls | ots).values('owner')[:1])
)

(As an aside, I also get an error when I try to apply an order_by clause.)

[Tim Graham]

Are you sure that generating some sensible SQL for this queryset is possible?

[Matthew Pava]

Yes, it is possible to generate SQL for this query. I tried it myself. ...I suppose "sensible" is a subjective term.

I just tried using an __in lookup, but that was getting to be a struggle.

Maybe Subquery wasn't originally intended to be used with unions, but I think it would be a good feature to have, especially in the discussion of CTEs (common table expressions).

[Can Sarıgöl]

hi, I thought that we can use union queries in subqueries by replacing the alias from origin query to union queries. I've pushed a commit. if this approach is ok, I can add other tests and go further? ​PR

[Can Sarıgöl]

cls = Document.objects.filter(
    checklist__isnull=False,
    part=OuterRef('id')
).values('checklist__customer', 'created')

ots = Document.objects.filter(
    ownershiptransfer__isnull=False,
    part=OuterRef('id')
).values('ownershiptransfer__ship_target__contact', 'created')

return self.annotate(
    owner=Subquery(cls.union(ots).values('owner')[:1])
)

I change the example like this:

cls = Document.objects.filter(
    checklist__isnull=False,
).values('checklist__customer', 'created')

ots = Document.objects.filter(
    ownershiptransfer__isnull=False,
).values('ownershiptransfer__ship_target__contact', 'created')

return self.annotate(
    owner=Subquery(cls.union(ots).filter(part=OuterRef('id')).values('owner')[:1])
)

[Can Sarıgöl]

I forgot it, thanks.

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In 30a01441:

Fixed #29338 -- Allowed using combined queryset in Subquery.

Thanks Eugene Kovalev for the initial patch, Simon Charette for the
review, and Chetan Khanna for help.

[GitHub <noreply@…>]

In 53ea4cce:

Fixed #35744 -- Relabelled external aliases of combined queries.

Just like normal queries, combined queries' outer references might fully
resolve before their reference is assigned its final alias.

Refs #29338.

Thanks Antony_K for the report and example, and thanks Mariusz Felisiak
for the review.