#28781 closed Bug (fixed)
QuerySet.union()/intersection()/difference() doesn't work with QuerySet.values().
| Reported by: | Amir Aziiev | Owned by: | felixxm |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | 1.11 |
| Severity: | Normal | Keywords: | union, values |
| Cc: | felixxm | 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 6 months ago by
| Description: | modified (diff) |
|---|
comment:2 Changed 6 months ago by
| Description: | modified (diff) |
|---|
comment:3 Changed 6 months ago by
| Cc: | felixxm added |
|---|
comment:4 follow-up: 5 Changed 6 months ago by
comment:5 Changed 6 months 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 6 months 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 6 months ago by
| Owner: | changed from nobody to felixxm |
|---|---|
| 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 6 months 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 6 months 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.