[Django] #37305: Annotation aliases can shadow lookups and transforms

[Django]

#37305: Annotation aliases can shadow lookups and transforms
-------------------------------------+-------------------------------------
Reporter: Annabelle Wiegart | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 6.1 | Severity: Normal
Keywords: annotate, alias | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
.annotate() raises a ValueError when an annotation alias conflicts with a
field on the annotated model. However, this is not the case for legitimate
lookups and transforms, as demonstrated in this [https://dryorm.xterm.info
/shadowing-lookups-transforms DryORM fiddle].

Here is the reproducer:

{{{#!div style="font-size: 80%"
{{{#!python
from django.db import models
from django.db.models.expressions import Value
from django.contrib.auth.models import User


class Person(models.Model):
name = models.CharField(max_length=100)
creator = models.ForeignKey(User, models.CASCADE, null=True)
creation_date = models.DateField(auto_now=True)

def run():
admin = User.objects.create(username="admin")
Person.objects.create(creator=admin, name="Violet")
qs1 = Person.objects.all()
qs2 = Person.objects.annotate(creator__username=Value(1))
qs3 = Person.objects.annotate(creation_date__year=Value(1))
# raises ValueError
# qs4 = Person.objects.annotate(creator=Value(1))
# qs5 = Person.objects.annotate(creation_date=Value(1))

# lookup without shadowing
print(qs1.values("creator__username"))
# lookup with shadowing
print(qs2.values("creator__username"))
# transform without shadowing
print(qs1.values("creation_date__year"))
# transform with shadowing
print(qs3.values("creation_date__year"))
}}}
}}}

Output:

{{{#!div style="font-size: 80%"
{{{#!python
<QuerySet [{'creator__username': 'admin'}]>
<QuerySet [{'creator__username': 1}]>
<QuerySet [{'creation_date__year': 2026}]>
<QuerySet [{'creation_date__year': 1}]>
}}}
}}}

The bug was discussed in
[https://github.com/django/django/pull/21803#discussion_r3823261022
PR21803] for #36945.
--
Ticket URL: <https://code.djangoproject.com/ticket/37305>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#37305: Annotation aliases can shadow lookups and transforms
-------------------------------------+-------------------------------------

Reporter: Annabelle Wiegart | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: annotate, alias | 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 Annabelle Wiegart:

Old description:

> .annotate() raises a ValueError when an annotation alias conflicts with a
> field on the annotated model. However, this is not the case for
> legitimate lookups and transforms, as demonstrated in this

> [https://dryorm.xterm.info/shadowing-lookups-transforms DryORM fiddle].

New description:

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

[Django]

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

[Django]

#37305: Annotation aliases can shadow lookups and transforms
-------------------------------------+-------------------------------------
Reporter: Annabelle Wiegart | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate, alias | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Zubair Hassan):

[https://dryorm.xterm.info/shadowing-lookup-transforms-working DryORM
Fiddle]
it is workig with `value` lookup.
--
Ticket URL: <https://code.djangoproject.com/ticket/37305#comment:3>

[Django]

#37305: Annotation aliases can shadow lookups and transforms
-------------------------------------+-------------------------------------
Reporter: Annabelle Wiegart | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: annotate, alias | Triage Stage: Accepted

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

Changes (by Yassin Bahri):

* stage: Unreviewed => Accepted

Comment:

I reproduced this on current `main`.

The issue affects both relationship lookups and transforms when an
annotation alias uses the same `LOOKUP_SEP` path.

Example regression tests using the existing `annotations` test app:

{{{
def test_annotation_alias_shadows_lookup_in_values(self):
qs = Book.objects.annotate(publisher__name=Value("shadowed")).values(
"publisher__name"
)
self.assertIn(
{"publisher__name": self.p1.name},
qs,
)

def test_annotation_alias_shadows_transform_in_values(self):
qs =
Book.objects.annotate(pubdate__year=Value(1)).values("pubdate__year")
self.assertIn(
{"pubdate__year": self.b1.pubdate.year},
qs,
)
}}}

Both tests currently fail.

For the relationship lookup case, Django returns the annotation value
instead of resolving the real join path:

{{{
AssertionError: {'publisher__name': 'Apress'} not found in <QuerySet [
{'publisher__name': 'shadowed'},
...
]>
}}}

For the transform case, Django returns the annotation value instead of
resolving the date transform:

{{{
AssertionError: {'pubdate__year': 2007} not found in <QuerySet [
{'pubdate__year': 1},
...
]>
}}}

I also checked that filtering can still intentionally use an annotation
alias containing `LOOKUP_SEP`:

{{{
Book.objects.annotate(publisher__name=Value("shadowed")).filter(
publisher__name="shadowed"
)
}}}

So I think the issue is real, but the fix should probably be careful:
either reject annotation aliases that shadow valid lookup/transform paths,
or otherwise make `values()` / related name-resolution paths avoid this
surprising shadowing. I would avoid a broad ban on all aliases containing
`__` unless that is the preferred direction, since existing code may rely
on such aliases.
--
Ticket URL: <https://code.djangoproject.com/ticket/37305#comment:4>

[Django]

#37305: Annotation aliases can shadow lookups and transforms
-------------------------------------+-------------------------------------

Reporter: Annabelle Wiegart | Owner: Yassin
| Bahri
Type: Bug | Status: assigned

Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate, alias | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Yassin Bahri):

* owner: (none) => Yassin Bahri
* status: new => assigned

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

[Django]

#37305: Annotation aliases can shadow lookups and transforms
-------------------------------------+-------------------------------------
Reporter: Annabelle Wiegart | Owner: Yassin
| Bahri
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate, alias | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Yassin Bahri):

* has_patch: 0 => 1

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

[Django]

#37305: Annotation aliases can shadow lookups and transforms
-------------------------------------+-------------------------------------
Reporter: Annabelle Wiegart | Owner: Yassin
| Bahri
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate, alias | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Annabelle Wiegart):

I would also avoid a broad ban on aliases containing "__", especially
since default aliases may contain them. This has also been discussed in
the context of #36945:
https://github.com/django/django/pull/21803#discussion_r3823261022.
--
Ticket URL: <https://code.djangoproject.com/ticket/37305#comment:7>

[Django]

#37305: Annotation aliases can shadow lookups and transforms
-------------------------------------+-------------------------------------
Reporter: Annabelle Wiegart | Owner: Yassin
| Bahri
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate, alias | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Yassin Bahri):

Replying to [comment:7 Annabelle Wiegart]:

> I would also avoid a broad ban on aliases containing `__`, especially
since default aliases may contain them. This has also been discussed in
the context of #36945:
https://github.com/django/django/pull/21803#discussion_r3823261022.

Agreed. The patch doesn’t reject aliases merely because they contain `__`.
It checks whether the alias resolves to an actual lookup, transform, or
related field path on the model. Non-resolving aliases remain allowed,
including Django-generated default aggregate aliases such as
`authors__count`, which is covered by a regression test.
--
Ticket URL: <https://code.djangoproject.com/ticket/37305#comment:8>