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
31667	Avoid passing NULL to the IN lookup	Adam Johnson	Adam Johnson	"Currently prefetch_related on a FK passes the NULL through to the database for e.g. `author_id IN (NULL, 2)`. Passing `NULL` is always unnecessary, since it's not allowed in FK's. There's a small risk from passing NULL that it could lead to incorrect with complex prefetch querysets using PK refs because of NULL's weirdness in SQL.

For example with these models:

{{{
from django.db import models


class Author(models.Model):
    pass


class Book(models.Model):
    author = models.ForeignKey(Author, null=True, on_delete=models.DO_NOTHING)
}}}

Prefetching authors on Books, when at least one Book has author=None, uses `IN (..., NULL, ...)` in the query:

{{{
In [1]: from example.core.models import Author, Book

In [2]: a1 = Author.objects.create()

In [3]: Book.objects.create(author=a1)
Out[3]: <Book: Book object (3)>

In [4]: Book.objects.create(author=None)
Out[4]: <Book: Book object (4)>

In [5]: Book.objects.prefetch_related('author')
Out[5]: <QuerySet [<Book: Book object (3)>, <Book: Book object (4)>]>

In [6]: from django.db import connection

In [7]: connection.queries
Out[7]:
[{'sql': 'INSERT INTO ""core_author"" (""id"") VALUES (NULL)', 'time': '0.001'},
 {'sql': 'INSERT INTO ""core_book"" (""author_id"") VALUES (2)', 'time': '0.001'},
 {'sql': 'INSERT INTO ""core_book"" (""author_id"") VALUES (NULL)',
  'time': '0.001'},
 {'sql': 'SELECT ""core_book"".""id"", ""core_book"".""author_id"" FROM ""core_book"" LIMIT 21',
  'time': '0.000'},
 {'sql': 'SELECT ""core_author"".""id"" FROM ""core_author"" WHERE ""core_author"".""id"" IN (NULL, 2)',
  'time': '0.000'}]
}}}

Maybe this could generally be extended to use of `__in` with non-nullable fields?"	Cleanup/optimization	closed	Database layer (models, ORM)	dev	Normal	fixed			Accepted	1	0	0	0	0	0