Annotation yields ProgrammingError error when migrating from 2.2 to 3.x

[Maarten van Keulen]

I am in the process of migrating from Django 2.2 to 3.2. After fixing most deprecation issues, an annotation I rely on throughout my project is throwing a syntax error.

This is a bit of a mystery to me since the upgrade path lists nothing on this topic. Also, it does not appear to matter which 3.x version I use, BUT this annotation has worked throughout most of 2.2.x's versions.

Journal.objects.annotate(
    p_user=F('assignment__courses__participation__user'),
    can_have_journal=F('assignment__courses__participation__role__can_have_journal'),
).filter(
    Q(assignment__is_group_assignment=True) | Q(p_user__in=F('authors__user'), can_have_journal=True),
)

Here is a diff of the generated SQL: ​https://www.diffchecker.com/fjlO30AD

This is the errror:

django.db.utils.ProgrammingError: syntax error at or near 
 "VLE_assignmentparticipation""
LINE 1: ...ave_journal" AND "VLE_participation"."user_id" IN "VLE_assig...

Part of the trace:

venv/lib/python3.8/site-packages/django/db/models/query.py:317: in __getitem__
    qs._fetch_all()
venv/lib/python3.8/site-packages/django/db/models/query.py:1324: in _fetch_all
    self._result_cache = list(self._iterable_class(self))
venv/lib/python3.8/site-packages/django/db/models/query.py:51: in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py:1169: in execute_sql
    cursor.execute(sql, params)
venv/lib/python3.8/site-packages/sentry_sdk/integrations/django/__init__.py:434: in execute
    return real_execute(self, sql, params)
venv/lib/python3.8/site-packages/django/db/backends/utils.py:66: in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
venv/lib/python3.8/site-packages/django/db/backends/utils.py:75: in _execute_with_wrappers
    return executor(sql, params, many, context)
venv/lib/python3.8/site-packages/django/db/backends/utils.py:84: in _execute
    return self.cursor.execute(sql, params)
venv/lib/python3.8/site-packages/django/db/utils.py:90: in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value

I attempted to dress down our repo so it would be more straightforward to reproduce the issue: ​https://github.com/eJourn-al/bug_report
The single test should pass on Django 2.2.x and fail from Django 3.x.

[Mariusz Felisiak]

Duplicate of #31135. You should use p_user=F('authors__user').

[Maarten van Keulen]

Replying to Mariusz Felisiak:

Duplicate of #31135. You should use p_user=F('authors__user').

Good fit, m.b. thank you reviewing the issue swiftly.