id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34748	__in lookup crashes with a subquery containing an unused annotation that uses explicit grouping.	Toan Vuong	Simon Charette	"Attached is a small Django project to demonstrate the issue.

Tested on:
Django 4.2.2
OS X 13.4.1
Python 3.9.16

For the Oracle backend:
cx-Oracle 8.3.0 with instantclient 19.8

For the Postgres backend:
psycopg/psycopg-binary 3.1.9


The sample query is in `test.py` and corresponding models are included in the attached tarball. A snippet of the query is below:

{{{
inner_qs = (
    Comment.objects.annotate(question_id=F('choice__question__id'))
        .values('question_id').annotate(cnt=Count('*'))
        .values_list('question_id')
)
outer_qs = Question.objects.filter(id__in=inner_qs).all()
print(outer_qs)
}}}

 
In postgres, this generates a query like so (Also tested and same issue on Oracle):

{{{
SELECT ""polls_question"".""id"", ""polls_question"".""question_text"", ""polls_question"".""pub_date"" FROM ""polls_question"" WHERE ""polls_question"".""id"" IN (SELECT U1.""question_id"" AS ""question_id"" FROM ""polls_comment"" U0 INNER JOIN ""polls_choice"" U1 ON (U0.""choice_id"" = U1.""id"") GROUP BY ""polls_choice"".""question_id"") LIMIT 21
}}}

The problem is the GROUP BY clause is not referencing the alias `U1`. Instead, it's referencing the table name. This doesn't seem to happen on 3.x, and I believe it's because in 4.x, for this scenario, the group by clause is represented as a reference (`Ref`) to the column in the select subquery. `Ref` implement a no-op on [https://github.com/django/django/blob/main/django/db/models/expressions.py#L1203 relabeled_clone], relying on something else to modify the `Col`. But this doesn't seem correct because `relabeled_clone` is not an in-place change -- it returns a new object. Perhaps it should just do `super().relabeled_clone()` instead of a no-op?

I understand this example is a little bit convoluted, since the final `values_list` overrides the previous `values()` and count aggregation calls, making those useless, but I think this still seems like a bug. I also don't have an example, but suspect `Ref::resolve_expression` probably has the same issue where the column which `Ref` references may be resolved correctly, but the referenced stored by `Ref` won't get resolved."	Bug	closed	Database layer (models, ORM)	4.2	Release blocker	fixed		Simon Charette	Ready for checkin	1	0	0	0	0	0