[Django] #29338: Can't use OuterRef in union Subquery

308 views
Skip to first unread message

Django

unread,
Apr 18, 2018, 5:39:25 PM4/18/18
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew | Owner: nobody
Pava |
Type: Bug | Status: new
Component: Database | Version: 2.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.)

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

Django

unread,
Apr 18, 2018, 9:29:37 PM4/18/18
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: nobody
Type: Bug | 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
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

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

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

Django

unread,
Apr 19, 2018, 11:30:17 AM4/19/18
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: nobody
Type: Bug | 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
-------------------------------------+-------------------------------------

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

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

Django

unread,
Apr 23, 2018, 8:57:41 AM4/23/18
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):

* stage: Unreviewed => Accepted


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

Django

unread,
May 23, 2018, 5:57:26 PM5/23/18
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jeff):

* cc: Jeff (added)


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

Django

unread,
Mar 31, 2019, 9:30:17 AM3/31/19
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Can Sarıgöl):

* cc: Can Sarıgöl (added)
* has_patch: 0 => 1


Comment:

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?
[https://github.com/django/django/pull/11152 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:5>

Django

unread,
Mar 31, 2019, 9:34:26 AM3/31/19
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

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

Comment (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])
)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:6>

Django

unread,
Apr 15, 2019, 5:15:19 PM4/15/19
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned

Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tobias Kunze):

* status: new => assigned
* owner: nobody => Can Sarıgöl


--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:7>

Django

unread,
Apr 16, 2019, 2:22:00 AM4/16/19
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

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

Comment (by Can Sarıgöl):

I forgot it, thanks.

--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:8>

Django

unread,
Apr 25, 2019, 3:27:29 AM4/25/19
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master

(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Asif Saifuddin Auvi):

* version: 2.0 => master


--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:9>

Django

unread,
May 8, 2019, 5:44:18 AM5/8/19
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: (none)

Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Can Sarıgöl):

* owner: Can Sarıgöl => (none)


--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:10>

Django

unread,
May 8, 2019, 7:54:23 AM5/8/19
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: (none)
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by felixxm):

* needs_better_patch: 0 => 1


--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:11>

Django

unread,
May 8, 2019, 7:55:36 AM5/8/19
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: (none)
Type: Bug | Status: new

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by felixxm):

* status: assigned => new


--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:12>

Django

unread,
Aug 21, 2019, 4:11:51 AM8/21/19
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: (none)
Type: Bug | Status: new

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by felixxm):

[https://github.com/django/django/pull/11692 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:13>

Django

unread,
Feb 3, 2020, 3:07:59 AM2/3/20
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: felixxm
Type: Bug | Status: assigned

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by felixxm):

* owner: (none) => felixxm
* needs_better_patch: 1 => 0


* status: new => assigned


--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:14>

Django

unread,
Feb 3, 2020, 4:38:09 AM2/3/20
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by felixxm):

* needs_better_patch: 0 => 1


--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:15>

Django

unread,
Apr 6, 2021, 3:04:31 PM4/6/21
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: dev

(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by InvalidInterrupt):

* cc: InvalidInterrupt (added)


--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:16>

Django

unread,
Jan 17, 2022, 5:38:03 AM1/17/22
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* needs_better_patch: 1 => 0


--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:17>

Django

unread,
Jan 17, 2022, 12:01:20 PM1/17/22
to django-...@googlegroups.com
#29338: Can't use OuterRef in union Subquery
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Mariusz
| Felisiak
Type: Bug | Status: closed

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by GitHub <noreply@…>):

* status: assigned => closed
* resolution: => fixed


Comment:

In [changeset:"30a01441347d5a2146af2944b29778fa0834d4be" 30a01441]:
{{{
#!CommitTicketReference repository=""
revision="30a01441347d5a2146af2944b29778fa0834d4be"
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.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:18>

Reply all
Reply to author
Forward
0 new messages