Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#32151 closed Bug (duplicate)

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

Reported by: Beda Kosata Owned by: nobody
Component: Database layer (models, ORM) Version: 3.1
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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.2. 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")

Change History (4)

comment:1 by Beda Kosata, 4 years ago

Description: modified (diff)

comment:2 by Simon Charette, 4 years ago

Not sure if this was ever explicitly supported, any reason you don't use author=F('publisher__authors')?

comment:3 by Mariusz Felisiak, 4 years ago

Resolution: duplicate
Status: newclosed
Summary: invalid query SQL created when combining __in and F() in filterInvalid query SQL created when combining __in and F() in filter

Duplicate of #31135.

in reply to:  2 comment:4 by Beda Kosata, 4 years ago

Replying to Simon Charette:

Not sure if this was ever explicitly supported, any reason you don't use author=F('publisher__authors')?

Thanks for the hint. I was thinking about it yesterday after I submitted the bug report and wanted to test it today (my actual use case is more complicated than the test code), but you were faster :)

Note: See TracTickets for help on using tickets.
Back to Top