DISTINCT ON fails with mixed-case field aliases

[Christophe Thiery]

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.

[Mariusz Felisiak]

Thanks for the report.

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

[Arsalan Ghassemi]

Hello,

I'm working on a patch for this issue.

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

[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

[Arsalan Ghassemi]

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

[Mariusz Felisiak <felisiak.mariusz@…>]

In bdcda1ca:

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

[Christophe Thiery]

Thank you!