[Django] #31580: Expect to get SELECT DISTINCT ON after UNION of 2 annotated QuerySet

[Django]

#31580: Expect to get SELECT DISTINCT ON after UNION of 2 annotated QuerySet
-------------------------------------+-------------------------------------
Reporter: Sielc | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) | Keywords: orm, distinct,
Severity: Normal | annotate, union
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
After using
- .annotate() on 2 different querysets
- and then .union()
- .distinct() will not affect the queryset

{{{
def setUp(self) -> None:
user = self.get_or_create_admin_user()

Sample.h.create(user, name="Sam1")
Sample.h.create(user, name="Sam2 acid")
Sample.h.create(user, name="Sam3")
Sample.h.create(user, name="Sam4 acid")

Sample.h.create(user, name="Dub")
Sample.h.create(user, name="Dub")
Sample.h.create(user, name="Dub")

self.user = user

def test_union_annotated_diff_distinct(self):
qs = Sample.objects.filter(user=self.user)
qs1 = qs.filter(name='Dub').annotate(rank=Value(0,
IntegerField()))
qs2 = qs.filter(name='Sam1').annotate(rank=Value(1,
IntegerField()))
qs = qs1.union(qs2)
qs = qs.order_by('name').distinct('name') # THIS DISTINCT
DONESN'T WORK
self.assertEqual(qs.count(), 2)
}}}

expected to get wrapped union
{{{
SELECT DISTINCT ON (siebox_sample.name) * FROM (SELECT ... UNION
SELECT ...) AS siebox_sample
}}}

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

[Django]

#31580: Expect to get SELECT DISTINCT ON after UNION of 2 annotated QuerySet
-------------------------------------+-------------------------------------
Reporter: Sielc | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, distinct, | Triage Stage:
annotate, union | Unreviewed
Has patch: 0 | Needs documentation: 0

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

Description changed by Sielc:

Old description:

New description:

After using
- .annotate() on 2 different querysets
- and then .union()
- .distinct() will not affect the queryset

{{{
def setUp(self) -> None:
user = self.get_or_create_admin_user()

Sample.h.create(user, name="Sam1")
Sample.h.create(user, name="Sam2 acid")
Sample.h.create(user, name="Sam3")
Sample.h.create(user, name="Sam4 acid")

Sample.h.create(user, name="Dub")
Sample.h.create(user, name="Dub")
Sample.h.create(user, name="Dub")

self.user = user

def test_union_annotated_diff_distinct(self):
qs = Sample.objects.filter(user=self.user)
qs1 = qs.filter(name='Dub').annotate(rank=Value(0,
IntegerField()))
qs2 = qs.filter(name='Sam1').annotate(rank=Value(1,
IntegerField()))
qs = qs1.union(qs2)

qs = qs.order_by('name').distinct('name') # THIS DISTINCT DOESN'T
WORK
self.assertEqual(qs.count(), 2)
}}}

expected to get wrapped union
{{{
SELECT DISTINCT ON (siebox_sample.name) * FROM (SELECT ... UNION
SELECT ...) AS siebox_sample
}}}

--

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

[Django]

#31580: Union queryset should raise on distinct().
-------------------------------------+-------------------------------------
Reporter: Sielc Technologies | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: master

(models, ORM) |
Severity: Normal | Resolution:

Keywords: orm, distinct, | Triage Stage: Accepted
annotate, union |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by felixxm):

* cc: Hasan Ramezani (added)
* version: 3.0 => master
* easy: 0 => 1
* type: Bug => Cleanup/optimization
* stage: Unreviewed => Accepted

Comment:

`distinct()` is not supported but doesn't raise an error yet. As
​[https://docs.djangoproject.com/en/stable/ref/models/querysets/#union per
the documentation], ''"only LIMIT, OFFSET, COUNT(*), ORDER BY, and
specifying columns (i.e. slicing, count(), order_by(), and
values()/values_list()) are allowed on the resulting QuerySet."''.

Follow up to #27995.

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

[Django]

#31580: Union queryset should raise on distinct().
-------------------------------------+-------------------------------------

Reporter: Sielc Technologies | Owner: Hasan
Type: | Ramezani
Cleanup/optimization | Status: assigned

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, distinct, | Triage Stage: Accepted
annotate, union |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Hasan Ramezani):

* owner: nobody => Hasan Ramezani
* status: new => assigned

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

[Django]

#31580: Union queryset should raise on distinct().
-------------------------------------+-------------------------------------
Reporter: Sielc Technologies | Owner: Hasan
Type: | Ramezani
Cleanup/optimization | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, distinct, | Triage Stage: Accepted
annotate, union |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Hasan Ramezani):

* has_patch: 0 => 1

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

[Django]

#31580: Union queryset should raise on distinct().
-------------------------------------+-------------------------------------
Reporter: Sielc Technologies | Owner: Hasan
Type: | Ramezani
Cleanup/optimization | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: orm, distinct, | Triage Stage: Ready for
annotate, union | checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* stage: Accepted => Ready for checkin

Comment:

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

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

[Django]

#31580: Union queryset should raise on distinct().
-------------------------------------+-------------------------------------
Reporter: Sielc Technologies | Owner: Hasan
Type: | Ramezani

Cleanup/optimization | Status: closed

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

Severity: Normal | Resolution: fixed

Keywords: orm, distinct, | Triage Stage: Ready for
annotate, union | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by GitHub <noreply@…>):

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

Comment:

In [changeset:"01a381cc17e6747e1d9e7b95a39e885029fd2a81" 01a381cc]:
{{{
#!CommitTicketReference repository=""
revision="01a381cc17e6747e1d9e7b95a39e885029fd2a81"
Fixed #31580 -- Added error messages on distinct() following union(),
intersection(), and difference().
}}}

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