#28900 closed Bug (fixed)
QuerySet.values() and values_list() for compound queries fails with annotation.
Reported by: | elliott-omosheye | Owned by: | Simon Charette |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.11 |
Severity: | Normal | Keywords: | union, values |
Cc: | David Wobrock, David Sanders, Tom Carrick, Dan LaManna, Sylvain Fankhauser | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
Change History (24)
follow-up: 3 comment:1 by , 7 years ago
comment:2 by , 7 years ago
Cc: | added |
---|
comment:3 by , 7 years ago
Replying to 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 https://github.com/django/django/blob/stable/1.11.x/tests/queries/tests.py
comment:5 by , 7 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
comment:6 by , 20 months ago
Cc: | added |
---|---|
Resolution: | duplicate |
Status: | closed → new |
Summary: | QuerySet.values() and values_list() for union(), difference(), and intersection() queries fails with annotated subquery → QuerySet.values() and values_list() for compound queries fails with annotation. |
Triage Stage: | Unreviewed → Accepted |
We decided to treat it as a separate issue.
comment:7 by , 20 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:8 by , 13 months ago
Owner: | removed |
---|---|
Status: | assigned → new |
I didn't find the time to push on this. Removing myself from the ticket.
comment:9 by , 13 months ago
Cc: | removed |
---|
follow-up: 15 comment:10 by , 12 months ago
Cc: | added |
---|
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}]>
comment:11 by , 12 months ago
Cc: | added |
---|
comment:12 by , 9 months ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:13 by , 8 months ago
Cc: | added |
---|
comment:14 by , 8 months ago
Cc: | added |
---|
comment:15 by , 7 months ago
Replying to 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()).values('name') 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?
comment:16 by , 6 months ago
Owner: | removed |
---|---|
Status: | assigned → new |
comment:17 by , 5 months ago
Has patch: | set |
---|---|
Owner: | set to |
Status: | new → assigned |
comment:18 by , 4 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:19 by , 4 months ago
Needs tests: | set |
---|---|
Patch needs improvement: | set |
Triage Stage: | Ready for checkin → Accepted |
comment:20 by , 4 months ago
Needs tests: | unset |
---|---|
Patch needs improvement: | unset |
comment:21 by , 4 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
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.