[Django] #33309: DISTINCT ON fails with mixed-case field aliases
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
Christophe Thiery |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
If you pass an aliased field name to distinct(), it will fail if the alias
has some capital letters.
{{{
from django.contrib.auth.models import User
from django.db.models import F
User.objects.annotate(the_alias=F('first_name')).values('the_alias',
'id').order_by('the_alias', 'id').distinct('the_alias')
# Works
User.objects.annotate(theAlias=F('first_name')).values('theAlias',
'id').order_by('theAlias', 'id').distinct('theAlias')
# Fails with:
# ProgrammingError: column "thealias" does not exist
# LINE 1: SELECT DISTINCT ON (theAlias) "auth_user"."id",
"auth_user"."first_name" AS "theAlias"...
}}}
It looks like the DISTINCT ON clause in the generated SQL is missing
double quotes.
Tested on Django 3.2.9 and postgres 12.8.
--
Ticket URL: <https://code.djangoproject.com/ticket/33309>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
Comment:
Thanks for the report.
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
This should be quite easy to fix:
{{{
diff --git a/django/db/models/sql/compiler.py
b/django/db/models/sql/compiler.py
index 73cf2c5f62..69a2d9298f 100644
--- a/django/db/models/sql/compiler.py
+++ b/django/db/models/sql/compiler.py
@@ -754,7 +754,7 @@ class SQLCompiler:
targets, alias, _ = self.query.trim_joins(targets, joins,
path)
for target in targets:
if name in self.query.annotation_select:
- result.append(name)
+ result.append(self.connection.ops.quote_name(name))
else:
r, p = self.compile(transform_function(target,
alias))
result.append(r)
}}}
Would you like to prepare a patch? (a regression test in
`tests/distinct_on_fields/tests.py` is required).
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Ad Timmering (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:3>
Django
-------------------------------------+-------------------------------------
| Ghassemi
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Arsalan Ghassemi
* status: new => assigned
Comment:
Hello,
I'm working on a patch for this issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:4>
Django
-------------------------------------+-------------------------------------
| Ghassemi
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Egor R (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:5>
Django
-------------------------------------+-------------------------------------
| Ghassemi
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Arsalan Ghassemi):
I was able to reproduce the bug in my environment and added the regression
test.
I'm currently working on the fix and will open a PR soon.
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:6>
Django
-------------------------------------+-------------------------------------
| Ghassemi
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Arsalan Ghassemi):
Sorry it's my first contribution to an open-source project and I forgot to
mention the topic branch :
https://github.com/ArsaCode/django/tree/ticket_33309
I opened a PR with the changes :
https://github.com/django/django/pull/15118
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:7>
Django
-------------------------------------+-------------------------------------
| Ghassemi
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:8>
Django
-------------------------------------+-------------------------------------
| Ghassemi
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Arsalan Ghassemi):
New PR (changed target to main branch) :
https://github.com/django/django/pull/15119
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:9>
Django
-------------------------------------+-------------------------------------
| Ghassemi
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:10>
Django
-------------------------------------+-------------------------------------
| Ghassemi
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"bdcda1ca9ba254743269e482384c2711ac34b1f1" bdcda1ca]:
{{{
#!CommitTicketReference repository=""
revision="bdcda1ca9ba254743269e482384c2711ac34b1f1"
Fixed #33309 -- Fixed QuerySet.distinct() crash on mixed case annotation.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:11>
Django
-------------------------------------+-------------------------------------
| Ghassemi
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Christophe Thiery):
Thank you!
--
Ticket URL: <https://code.djangoproject.com/ticket/33309#comment:12>