Opened 12 months ago

Closed 11 months ago

Last modified 11 months 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 12 months ago.
Tests.

Download all attachments as: .zip

Change History (14)

comment:1 Changed 12 months ago by Amir Aziiev

Description: modified (diff)

comment:2 Changed 12 months ago by Amir Aziiev

Description: modified (diff)

comment:3 Changed 12 months ago by felixxm

Cc: felixxm added

comment:4 Changed 12 months 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 12 months 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 12 months 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 12 months 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 12 months ago by felixxm

Attachment: 28781.diff added

Tests.

comment:8 Changed 12 months 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 12 months 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 11 months ago by felixxm

Has patch: set

comment:11 Changed 11 months 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 11 months 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 11 months 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