invalid query SQL created when combining __in and F() in filter

[Beda Kosata]

When a query contains an __in filter with F() on the right side (I observed it with a ManyToMany relationship), the generated SQL is not correct and causes a SyntaxError. At least for PostgreSQL 12.

Here is a sample project to reproduce the issue:

models.py

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=32)


class Publisher(models.Model):
    authors = models.ManyToManyField(Author)


class Book(models.Model):
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

This query:

list(Book.objects.filter(author__in=F('publisher__authors')))

then fails with psycopg2.errors.SyntaxError: syntax error at or near ""ferror_publisher_authors""

The problem is that the generated query looks like this:

SELECT "ferror_book"."id", "ferror_book"."publisher_id", "ferror_book"."author_id"
  FROM "ferror_book"
  INNER JOIN "ferror_publisher" ON ("ferror_book"."publisher_id" = "ferror_publisher"."id") 
  INNER JOIN "ferror_publisher_authors" ON ("ferror_publisher"."id" = "ferror_publisher_authors"."publisher_id") 
WHERE "ferror_book"."author_id" IN "ferror_publisher_authors"."author_id"

This issue is present in both 3.0.10 and 3.1.5. When the same code is run in Django 2.2.16, a correct query is created with brackets around the "ferror_publisher_authors"."author_id" part at the end.

SELECT "ferror_book"."id", "ferror_book"."publisher_id", "ferror_book"."author_id"
  FROM "ferror_book"
  INNER JOIN "ferror_publisher" ON ("ferror_book"."publisher_id" = "ferror_publisher"."id") 
  INNER JOIN "ferror_publisher_authors" ON ("ferror_publisher"."id" = "ferror_publisher_authors"."publisher_id") 
WHERE "ferror_book"."author_id" IN ("ferror_publisher_authors"."author_id")