#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 )
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 , 4 years ago
Description: | modified (diff) |
---|
follow-up: 4 comment:2 by , 4 years ago
comment:3 by , 4 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Summary: | invalid query SQL created when combining __in and F() in filter → Invalid query SQL created when combining __in and F() in filter |
Duplicate of #31135.
comment:4 by , 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 :)
Not sure if this was ever explicitly supported, any reason you don't use
author=F('publisher__authors')
?