Opened 12 days ago

Closed 7 days ago

Last modified 7 days ago

#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 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 felixxm 11 days ago.
Tests.

Download all attachments as: .zip

Change History (14)

comment:1 Changed 12 days ago by Amir Aziiev

Description: modified (diff)

comment:2 Changed 12 days ago by Amir Aziiev

Description: modified (diff)

comment:3 Changed 12 days ago by felixxm

Cc: felixxm added

comment:4 Changed 11 days ago by 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.

comment:5 in reply to:  4 Changed 11 days ago by Amir Aziiev

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 11 days ago by felixxm

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 Changed 11 days ago by felixxm

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

Changed 11 days ago by felixxm

Attachment: 28781.diff added

Tests.

comment:8 Changed 11 days ago by Tim Graham

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 10 days ago by felixxm

Severity: Release blockerNormal

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

comment:10 Changed 9 days ago by felixxm

Has patch: set

comment:11 Changed 7 days ago by GitHub <noreply@…>

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 Changed 7 days ago by Mariusz Felisiak <felisiak.mariusz@…>

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 Changed 7 days ago by Mariusz Felisiak <felisiak.mariusz@…>

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