Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#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 Amir Aziiev)

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)

28781.diff (2.3 KB ) - added by Mariusz Felisiak 7 years ago.
Tests.

Download all attachments as: .zip

Change History (14)

comment:1 by Amir Aziiev, 7 years ago

Description: modified (diff)

comment:2 by Amir Aziiev, 7 years ago

Description: modified (diff)

comment:3 by Mariusz Felisiak, 7 years ago

Cc: Mariusz Felisiak added

comment:4 by Hrishikesh Barman, 7 years ago

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.

in reply to:  4 comment:5 by Amir Aziiev, 7 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 Mariusz Felisiak, 7 years ago

Severity: NormalRelease blocker
Summary: Order of queryset.union() with queryset.values() is incorrectQuerySet.union() doesn't preserve columns ordering in SQL.
Triage Stage: UnreviewedAccepted

comment:7 by Mariusz Felisiak, 7 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned
Summary: QuerySet.union() doesn't preserve columns ordering in SQL.QuerySet.union()/intersection()/difference() doesn't work with QuerySet.values().

by Mariusz Felisiak, 7 years ago

Attachment: 28781.diff added

Tests.

comment:8 by Tim Graham, 7 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 Mariusz Felisiak, 7 years ago

Severity: Release blockerNormal

Agreed. I marked this ticket as a" Release blocker" because IMO it's a bug in the new feature.

comment:10 by Mariusz Felisiak, 7 years ago

Has patch: set

comment:11 by GitHub <noreply@…>, 7 years ago

Resolution: fixed
Status: assignedclosed

In 2d3cc94:

Fixed #28781 -- Added QuerySet.values()/values_list() support for union(), difference(), and intersection().

Thanks Tim Graham for the review.

comment:12 by Mariusz Felisiak <felisiak.mariusz@…>, 7 years ago

In ca0a9c93:

[2.0.x] Fixed #28781 -- Added QuerySet.values()/values_list() support for union(), difference(), and intersection().

Thanks Tim Graham for the review.
Backport of 2d3cc94284674638c334670903d49565039d77ae from master

comment:13 by Mariusz Felisiak <felisiak.mariusz@…>, 7 years ago

In 6731672:

[1.11.x] Fixed #28781 -- Added QuerySet.values()/values_list() support for union(), difference(), and intersection().

Thanks Tim Graham for the review.
Backport of 2d3cc94284674638c334670903d49565039d77ae from master

Note: See TracTickets for help on using tickets.
Back to Top