Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#33309 closed Bug (fixed)

DISTINCT ON fails with mixed-case field aliases

Reported by: Christophe Thiery Owned by: Arsalan Ghassemi
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords:
Cc: Ad Timmering, Egor R Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (12)

comment:1 by Mariusz Felisiak, 3 years ago

Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

Thanks for the report.

comment:2 by Mariusz Felisiak, 3 years ago

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).

comment:3 by Ad Timmering, 3 years ago

Cc: Ad Timmering added

comment:4 by Arsalan Ghassemi, 3 years ago

Owner: changed from nobody to Arsalan Ghassemi
Status: newassigned

Hello,

I'm working on a patch for this issue.

comment:5 by Egor R, 3 years ago

Cc: Egor R added

comment:6 by Arsalan Ghassemi, 3 years ago

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.

comment:7 by Arsalan Ghassemi, 3 years ago

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

comment:8 by Arsalan Ghassemi, 3 years ago

Has patch: set

comment:9 by Arsalan Ghassemi, 3 years ago

New PR (changed target to main branch) : https://github.com/django/django/pull/15119

comment:10 by Mariusz Felisiak, 3 years ago

Triage Stage: AcceptedReady for checkin

comment:11 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

Resolution: fixed
Status: assignedclosed

In bdcda1ca:

Fixed #33309 -- Fixed QuerySet.distinct() crash on mixed case annotation.

comment:12 by Christophe Thiery, 3 years ago

Thank you!

Note: See TracTickets for help on using tickets.
Back to Top