#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 Changed 3 years ago by
| Description: | modified (diff) |
|---|
comment:2 Changed 3 years ago by
| Description: | modified (diff) |
|---|
comment:3 Changed 3 years ago by
| Cc: | Mariusz Felisiak added |
|---|
comment:4 follow-up: 5 Changed 3 years ago by
comment:5 Changed 3 years ago by
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 Changed 3 years ago by
| 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 Changed 3 years ago by
| Owner: | changed from nobody to Mariusz Felisiak |
|---|---|
| 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 Changed 3 years ago by
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 Changed 3 years ago by
| 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.