Avoid passing NULL to the IN lookup

[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?

[Simon Charette]

Maybe this could generally be extended to use of __in with non-nullable fields?

Since IN translates to OR = for each elements and NULL != NULL I assume it could be done at the __in lookup level even for non-nullable fields.

[Mariusz Felisiak]

​PR

[Adam Johnson]

Apologies for not doing my paperwork.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 5776a166:

Fixed #31667 -- Made in lookup ignore None values.

[Adam Sołtysik]

This has partially fixed #20024.

[Simon Charette]

As Adam Sołtysik pointed out in #20024.this change also introduced an inconsistency

Foo.objects.filter(bar__in=list(Bar.objects.values_list('nullable_field', flat=True)))

Won't match

Foo.objects.filter(bar__in=Bar.objects.values_list('nullable_field', flat=True))

If any NULL values are returned.

We should make sure that a complete corrective for #20024 lands in 3.2 otherwise 5776a1660e54a95159164414829738b665c89916 should be reverted.

[Adam Sołtysik]

There is also an inconsistency between In and RelatedIn lookups. The latter seems to be working consistenly with this change (details in #20024).

[Mariusz Felisiak <felisiak.mariusz@…>]

In f7f38f3:

Fixed #36290 -- Made TupleIn() lookup discard tuples containing None.

Just like the In() lookup discards of None members TupleIn() should
discard tuples containing any None as NULL != NULL in SQL and the
framework expects such queries to be elided under some circumstances.

Refs #31667, #36116.

Thanks Basptise Mispelon for bisecting the regression to 626d77e.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 8ebdd37a:

[5.2.x] Fixed #36290 -- Made TupleIn() lookup discard tuples containing None.

Just like the In() lookup discards of None members TupleIn() should
discard tuples containing any None as NULL != NULL in SQL and the
framework expects such queries to be elided under some circumstances.

Refs #31667, #36116.

Thanks Basptise Mispelon for bisecting the regression to 626d77e.

Backport of f7f38f3a0b44d8c6d14344dae66b6ce52cd77b55 from main