#28781 closed Bug (fixed)
QuerySet.union()/intersection()/difference() doesn't work with QuerySet.values().
| Reported by: | Amir Aziiev | Owned by: | Mariusz Felisiak |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | 1.11 |
| Severity: | Normal | Keywords: | union, values |
| Cc: | Mariusz Felisiak | Triage Stage: | Accepted |
| Has patch: | yes | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description (last modified by )
For example:
from django.db import models
class MyModel(models.Model):
col1 = models.CharField(max_length=11)
col2 = models.CharField(max_length=22)
col3 = models.CharField(max_length=33)
qs = MyModel.objects.union(MyModel.objects.all(), all=True).values('col2', 'col3', 'col1', 'id').order_by('col1')
print(qs.query)
# Expected SQL
"""
(SELECT "mymodel"."id", "mymodel"."col1", "mymodel"."col2", "mymodel"."col3" FROM "mymodel")
UNION ALL
(SELECT"mymodel"." id", "mymodel"."col1", "mymodel"."col2", "mymodel"."col3" FROM "mymodel")
ORDER BY (3);
------------^
"""
# Must be SQL
"""
(SELECT "mymodel"."id", "mymodel"."col1", "mymodel"."col2", "mymodel"."col3" FROM "mymodel")
UNION ALL
(SELECT"mymodel"." id", "mymodel"."col1", "mymodel"."col2", "mymodel"."col3" FROM "mymodel")
ORDER BY (2);
------------^
"""
Attachments (1)
Change History (14)
comment:1 by , 6 years ago
| Description: | modified (diff) |
|---|
comment:2 by , 6 years ago
| Description: | modified (diff) |
|---|
comment:3 by , 6 years ago
| Cc: | added |
|---|
follow-up: 5 comment:4 by , 6 years ago
comment:5 by , 6 years ago
Replying to Hrishikesh Barman:
Expected SQL and Must be SQL are ambiguous to me, did I miss something? also, can you elaborate more on the issue why order(2) should work, If it's a valid bug I'd like to work on it. I am very new to django contributions.
... (SELECT "mymodel"." id" (1), "mymodel"."col1" (2), "mymodel"."col2"(3), "mymodel"."col3" (4) FROM "mymodel") ...
We want to sort by 'col1' (2). But in Expected SQL ordering by 'col2' (3)
comment:6 by , 6 years ago
| Severity: | Normal → Release blocker |
|---|---|
| Summary: | Order of queryset.union() with queryset.values() is incorrect → QuerySet.union() doesn't preserve columns ordering in SQL. |
| Triage Stage: | Unreviewed → Accepted |
comment:7 by , 6 years ago
| Owner: | changed from to |
|---|---|
| Status: | new → assigned |
| Summary: | QuerySet.union() doesn't preserve columns ordering in SQL. → QuerySet.union()/intersection()/difference() doesn't work with QuerySet.values(). |
comment:8 by , 6 years ago
I'm not sure I'd consider that a release blocker as the QuerySet.union() documentation states, "In addition, only LIMIT, OFFSET, COUNT(*), and ORDER BY (i.e. slicing, count(), and order_by()) are allowed on the resulting QuerySet."
comment:9 by , 6 years ago
| Severity: | Release blocker → Normal |
|---|
Agreed. I marked this ticket as a" Release blocker" because IMO it's a bug in the new feature.
Expected SQL and Must be SQL are ambiguous to me, did I miss something? also, can you elaborate more on the issue why order(2) should work, If it's a valid bug I'd like to work on it. I am very new to django contributions.