#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.