[Django] #28900: QuerySet.values() and values_list() for union(), difference(), and intersection() queries fails with annotated subquery

[Django]

#28900: QuerySet.values() and values_list() for union(), difference(), and
intersection() queries fails with annotated subquery
-------------------------------------+-------------------------------------
Reporter: elliott- | Owner: nobody
omosheye |
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords: union, values
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Added failing test to demonstrate

{{{
def test_union_values_subquery(self):
items = Item.objects.filter(creator=OuterRef("pk"))
item_authors =
Author.objects.annotate(is_creator=Exists(items)).order_by()
reports = Report.objects.filter(creator=OuterRef("pk"))
report_authors =
Author.objects.annotate(is_creator=Exists(reports)).order_by()
all_authors = item_authors.union(report_authors).order_by()
self.assertEqual(list(all_authors.values_list("is_creator",
flat=True)), [True, True, True, True])
}}}

silently messes up the data on values/values_list() with an field

{{{
FAIL: test_union_values_subquery (queries.tests.Queries1Tests)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/usr/local/lib/python2.7/unittest/case.py", line 329, in run
testMethod()
File "/usr/src/widget_app/tests/queries/tests.py", line 95, in
test_union_values_subquery
self.assertEqual(list(all_authors.values_list("is_creator",
flat=True)), [True, True, True, True])
File "/usr/local/lib/python2.7/unittest/case.py", line 513, in
assertEqual
assertion_func(first, second, msg=msg)
File "/usr/local/lib/python2.7/unittest/case.py", line 742, in
assertListEqual
self.assertSequenceEqual(list1, list2, msg, seq_type=list)
File "/usr/local/lib/python2.7/unittest/case.py", line 724, in
assertSequenceEqual
self.fail(msg)
File "/usr/local/lib/python2.7/unittest/case.py", line 410, in fail
raise self.failureException(msg)
AssertionError: Lists differ: [True, True, 2, 2, 3, 4, 4] != [True, True,
True, True]

First differing element 2:
2
True

First list contains 3 additional elements.
First extra element 4:
3

- [True, True, 2, 2, 3, 4, 4]
+ [True, True, True, True]
}}}

throws error on empty values()/values_list()

{{{
Traceback (most recent call last):
File "/usr/local/lib/python2.7/unittest/case.py", line 329, in run
testMethod()
File "/usr/src/widget_app/tests/queries/tests.py", line 95, in
test_union_values_subquery
self.assertEqual(list(all_authors.values()), [True, True, True, True])
File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py",
line 250, in __iter__
self._fetch_all()
File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py",
line 1118, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py",
line 106, in __iter__
for row in compiler.results_iter(chunked_fetch=self.chunked_fetch):
File "/usr/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 847, in results_iter
row = self.apply_converters(row, converters)
File "/usr/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 830, in apply_converters
value = row[pos]
IndexError: list index out of range
}}}

The funny thing is that the produced SQL query is perfectly valid, so it
isn't a case of me asking the ORM to do something especially funky. In any
case if this is an unsupported action then the ORM should tell you and not
produce runtime errors or silently return bad data.

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

[Django]

#28900: QuerySet.values() and values_list() for union(), difference(), and
intersection() queries fails with annotated subquery
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

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

Comment (by Simon Charette):

Would it be possible to provide your simplified model definition and data
setup procedure as well as it makes it really hard to reproduce without
them.

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

[Django]

#28900: QuerySet.values() and values_list() for union(), difference(), and
intersection() queries fails with annotated subquery
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: nobody
Type: Bug | Status: new

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

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

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

Changes (by Sergey Fedoseev):

* cc: Sergey Fedoseev (added)

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

[Django]

#28900: QuerySet.values() and values_list() for union(), difference(), and
intersection() queries fails with annotated subquery
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: nobody
Type: Bug | Status: new

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

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

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

Comment (by elliott-omosheye):

Replying to [comment:1 Simon Charette]:

> Would it be possible to provide your simplified model definition and
data setup procedure as well as it makes it really hard to reproduce
without them.

Sorry for not being clearer, I am using the model definitions from
queries.tests.Queries1Tests

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

[Django]

#28900: QuerySet.values() and values_list() for union(), difference(), and
intersection() queries fails with annotated subquery
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: nobody
Type: Bug | Status: new

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

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

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

Comment (by Sergey Fedoseev):

Probably duplicate of #28553.

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

[Django]

#28900: QuerySet.values() and values_list() for union(), difference(), and
intersection() queries fails with annotated subquery
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: nobody
Type: Bug | Status: closed

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

Severity: Normal | Resolution: duplicate

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

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

Changes (by Tim Graham):

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

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.

-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: nobody

Type: Bug | Status: new

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

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by Mariusz Felisiak):

* cc: David Wobrock (added)
* status: closed => new
* resolution: duplicate =>
* stage: Unreviewed => Accepted

Comment:

We [https://github.com/django/django/pull/16649#issuecomment-1481387207
decided] to treat it as a separate issue.

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: David
| Wobrock
Type: Bug | Status: assigned

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

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by David Wobrock):

* owner: nobody => David Wobrock
* status: new => assigned

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: (none)
Type: Bug | Status: new

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

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by David Wobrock):

* owner: David Wobrock => (none)
* status: assigned => new

Comment:

I didn't find the time to push on this. Removing myself from the ticket.

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: (none)

Type: Bug | Status: new

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

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by Sergey Fedoseev):

* cc: Sergey Fedoseev (removed)

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: (none)

Type: Bug | Status: new

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

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by David Sanders):

* cc: David Sanders (added)

Comment:

If it helps, while checking if #34945 was a duplicate of this I was able
to boil down the failure to this simple example:

{{{
class Foo(Model):
name = CharField()

class Bar(Model):
name = CharField()

qs = (
Foo.objects.annotate(alias=F("name"))
.union(Bar.objects.annotate(alias=F("name")))
.values("alias")
)
print(qs)
}}}

gives

{{{
<QuerySet [{'alias': 1}, {'alias': 1}]>
}}}

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: (none)

Type: Bug | Status: new

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

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by Tom Carrick):

* cc: Tom Carrick (added)

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: ontowhee
Type: Bug | Status: assigned

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

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by ontowhee):

* owner: (none) => ontowhee

* status: new => assigned

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: ontowhee
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by Dan LaManna):

* cc: Dan LaManna (added)

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: ontowhee
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by Sylvain Fankhauser):

* cc: Sylvain Fankhauser (added)

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: ontowhee
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Comment (by ontowhee):

Replying to [comment:10 David Sanders]:

Thanks for the simple example!

I'm not too knowledgeable about the ORM, but I'm trying to work through
this problem. Is the problem within annotate, or somewhere else, or a
combination of annotate and something else?

Using the simple example, I tried taking the union of Foo and Bar without
any annotation, and I apply `.values('name')` to the result


{{{
Foo.objects.create(name='hello')
Bar.objects.create(name='goodbye')

qs = Foo.objects.all().union(Bar.objects.all())
print(qs)
}}}

and it gives

{{{
<QuerySet [{'name': 'goodbye'}, {'name': 'hello'}]>
}}}

Which is correct and expected. However, when I remove `.values('name')`

{{{
qs = Foo.objects.all().union(Bar.objects.all())
print(qs)
}}}

it then gives

{{{
<QuerySet [<Foo: Foo object (1)>, <Foo: Foo object (1)>]>
}}}

Is this expected, or is this something to look into?
--
Ticket URL: <https://code.djangoproject.com/ticket/28900#comment:15>

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: (none)
Type: Bug | Status: new

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

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by ontowhee):

* owner: ontowhee => (none)

* status: assigned => new

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------

Reporter: elliott-omosheye | Owner: Simon
| Charette
Type: Bug | Status: assigned

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

Keywords: union, values | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

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

Changes (by Simon Charette):

* has_patch: 0 => 1
* owner: (none) => Simon Charette

* status: new => assigned

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

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

Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

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

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 1

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

Changes (by Sarah Boyce):

* needs_better_patch: 0 => 1
* needs_tests: 0 => 1
* stage: Ready for checkin => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/28900#comment:19>

[Django]

#28900: QuerySet.values() and values_list() for compound queries fails with
annotation.
-------------------------------------+-------------------------------------
Reporter: elliott-omosheye | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

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

Changes (by Simon Charette):

* needs_better_patch: 1 => 0
* needs_tests: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/28900#comment:20>