Opened 2 years ago

Last modified 6 months ago

#29338 assigned Bug

Can't use OuterRef in union Subquery

Reported by: Matthew Pava Owned by: felixxm
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Jeff, Can Sarıgöl Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

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

Change History (15)

comment:1 Changed 2 years ago by Tim Graham

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

comment:2 Changed 2 years ago by 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).

comment:3 Changed 2 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:4 Changed 2 years ago by Jeff

Cc: Jeff added

comment:5 Changed 17 months ago by Can Sarıgöl

Cc: Can Sarıgöl added
Has patch: set

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

comment:6 in reply to:  description Changed 17 months ago by 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])
)

comment:7 Changed 16 months ago by Tobias Kunze

Owner: changed from nobody to Can Sarıgöl
Status: newassigned

comment:8 Changed 16 months ago by Can Sarıgöl

I forgot it, thanks.

comment:9 Changed 16 months ago by Asif Saifuddin Auvi

Version: 2.0master

comment:10 Changed 15 months ago by Can Sarıgöl

Owner: Can Sarıgöl deleted

comment:11 Changed 15 months ago by felixxm

Patch needs improvement: set

comment:12 Changed 15 months ago by felixxm

Status: assignednew

comment:13 Changed 12 months ago by felixxm

comment:14 Changed 6 months ago by felixxm

Owner: set to felixxm
Patch needs improvement: unset
Status: newassigned

comment:15 Changed 6 months ago by felixxm

Patch needs improvement: set
Note: See TracTickets for help on using tickets.
Back to Top