[Django] #28781: Order of queryset.union() with queryset.values() is incorrect

[Django]

#28781: Order of queryset.union() with queryset.values() is incorrect
-------------------------------------+-------------------------------------
Reporter: Amir | Owner: nobody
Aziiev |
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords: union, values
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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);
------------^
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28781>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#28781: Order of queryset.union() with queryset.values() is incorrect
-------------------------------------+-------------------------------------

Reporter: Amir Aziiev | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Amir Aziiev:

Old description:

New description:

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);
------------^
}}}

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:1>

[Django]

#28781: Order of queryset.union() with queryset.values() is incorrect
-------------------------------------+-------------------------------------

Reporter: Amir Aziiev | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Amir Aziiev:

Old description:

> 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);
> ------------^
> }}}

New description:

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);
------------^
"""
}}}

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:2>

[Django]

#28781: Order of queryset.union() with queryset.values() is incorrect
-------------------------------------+-------------------------------------

Reporter: Amir Aziiev | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* cc: felixxm (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:3>

[Django]

#28781: Order of queryset.union() with queryset.values() is incorrect
-------------------------------------+-------------------------------------

Reporter: Amir Aziiev | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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.

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:4>

[Django]

#28781: Order of queryset.union() with queryset.values() is incorrect
-------------------------------------+-------------------------------------

Reporter: Amir Aziiev | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Amir Aziiev):

Replying to [comment:4 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)

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:5>

[Django]

#28781: QuerySet.union() doesn't preserve columns ordering in SQL.

-------------------------------------+-------------------------------------
Reporter: Amir Aziiev | Owner: nobody

Type: Bug | Status: new

Component: Database layer | Version: 1.11
(models, ORM) |

Severity: Release blocker | Resolution:
Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* severity: Normal => Release blocker
* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:6>

[Django]

#28781: QuerySet.union()/intersection()/difference() doesn't work with
QuerySet.values().
-------------------------------------+-------------------------------------
Reporter: Amir Aziiev | Owner: felixxm
Type: Bug | Status: assigned

Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* owner: nobody => felixxm
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:7>

[Django]

#28781: QuerySet.union()/intersection()/difference() doesn't work with
QuerySet.values().
-------------------------------------+-------------------------------------
Reporter: Amir Aziiev | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* Attachment "28781.diff" added.

Tests.

[Django]

#28781: QuerySet.union()/intersection()/difference() doesn't work with
QuerySet.values().
-------------------------------------+-------------------------------------
Reporter: Amir Aziiev | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

I'm not sure I'd consider that a release blocker as
[https://docs.djangoproject.com/en/dev/ref/models/querysets/#union 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."

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:8>

[Django]

#28781: QuerySet.union()/intersection()/difference() doesn't work with
QuerySet.values().
-------------------------------------+-------------------------------------
Reporter: Amir Aziiev | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |

Severity: Normal | Resolution:
Keywords: union, values | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* severity: Release blocker => Normal

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:9>

[Django]

#28781: QuerySet.union()/intersection()/difference() doesn't work with
QuerySet.values().
-------------------------------------+-------------------------------------
Reporter: Amir Aziiev | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: union, values | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/9340 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:10>

[Django]

#28781: QuerySet.union()/intersection()/difference() doesn't work with
QuerySet.values().
-------------------------------------+-------------------------------------
Reporter: Amir Aziiev | Owner: felixxm

Type: Bug | Status: closed

Component: Database layer | Version: 1.11
(models, ORM) |

Severity: Normal | Resolution: fixed
Keywords: union, values | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by GitHub <noreply@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"2d3cc94284674638c334670903d49565039d77ae" 2d3cc94]:
{{{
#!CommitTicketReference repository=""
revision="2d3cc94284674638c334670903d49565039d77ae"
Fixed #28781 -- Added QuerySet.values()/values_list() support for union(),
difference(), and intersection().

Thanks Tim Graham for the review.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:11>

[Django]

#28781: QuerySet.union()/intersection()/difference() doesn't work with
QuerySet.values().
-------------------------------------+-------------------------------------
Reporter: Amir Aziiev | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution: fixed

Keywords: union, values | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"ca0a9c938f3c0f165fe1e6d805c2d5d0541236aa" ca0a9c93]:
{{{
#!CommitTicketReference repository=""
revision="ca0a9c938f3c0f165fe1e6d805c2d5d0541236aa"
[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
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:12>

[Django]

#28781: QuerySet.union()/intersection()/difference() doesn't work with
QuerySet.values().
-------------------------------------+-------------------------------------
Reporter: Amir Aziiev | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution: fixed

Keywords: union, values | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"67316720603821ebb64dfe8fa592ba6edcef5f3e" 6731672]:
{{{
#!CommitTicketReference repository=""
revision="67316720603821ebb64dfe8fa592ba6edcef5f3e"
[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
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28781#comment:13>