'exclude' does not work with lists containing a 'None' element.

[Felipe 'chronos' Prenholato]

This ticket covers use of 'None' (NULL for DB) in list of one 'in' lookup.

Postgres at least, when run a query with a 'WHERE' like "object_id in (NULL,'other val')" returns a query with no
rows, and ORM too, what is a problem because developer can think that query is right, so as Alex Gaynor said in #django-dev 'we can rewrite it as (value IN (other, vals) OR value IS NULL)' or raise a error in database backend (since in ORM we can't thinking that mongodb would allow it as 'apollo13' said).

My vode is to raise a error in database backend.

Here is code in python shell that show the problem:

In Django 1.1.X

>>> LogEntry.objects.exclude(object_id=None).count()
274527
>>> connection.queries[-1]
{'sql': 'SELECT COUNT(*) FROM "django_admin_log" WHERE NOT ("django_admin_log"."object_id" IS NULL)',
 'time': '0.101'}

>>> LogEntry.objects.exclude(object_id__in=(None,)).count()
0
>>> connection.queries[-1]
{'sql': 'SELECT COUNT(*) FROM "django_admin_log" WHERE NOT ("django_admin_log"."object_id" IN (NULL) AND NOT ("django_admin_log"."object_id" IS NULL))',
 'time': '0.085'}

>>> LogEntry.objects.exclude(object_id=None,object_id='None').count()
274443
>>> connection.queries[-1]
{'sql': 'SELECT COUNT(*) FROM "django_admin_log" WHERE NOT ("django_admin_log"."object_id" = E\'None\'  AND NOT ("django_admin_log"."object_id" IS NULL))',
 'time': '0.122'}

>>> LogEntry.objects.exclude(object_id__in=(None,'None')).count()
0
>>> connection.queries[-1]
{'sql': 'SELECT COUNT(*) FROM "django_admin_log" WHERE NOT ("django_admin_log"."object_id" IN (NULL, E\'None\') AND NOT ("django_admin_log"."object_id" IS NULL))',
 'time': '0.126'}

Django 1.2

>>> LogEntry.objects.exclude(object_id=None).count()
274527
>>> connection.queries[-1]
{'sql': 'SELECT COUNT(*) FROM "django_admin_log" WHERE NOT ("django_admin_log"."object_id" IS NULL)',
 'time': '0.104'}

>>> LogEntry.objects.exclude(object_id__in=(None,)).count()
0
>>> connection.queries[-1]
{'sql': 'SELECT COUNT(*) FROM "django_admin_log" WHERE NOT (("django_admin_log"."object_id" IN (NULL) AND NOT ("django_admin_log"."object_id" IS NULL)))',
 'time': '0.088'}

>>> LogEntry.objects.exclude(Q(object_id=None),Q(object_id='None')).count()
274527
>>> connection.queries[-1]
{'sql': 'SELECT COUNT(*) FROM "django_admin_log" WHERE NOT ("django_admin_log"."object_id" IS NULL AND "django_admin_log"."object_id" = E\'None\' )',
 'time': '0.114'}

>>> LogEntry.objects.exclude(object_id__in=(None,'None')).count()
0
>>> connection.queries[-1]
{'sql': 'SELECT COUNT(*) FROM "django_admin_log" WHERE NOT (("django_admin_log"."object_id" IN (NULL, E\'None\') AND NOT ("django_admin_log"."object_id" IS NULL)))',
 'time': '0.120'}

[Bas Peschier]

Confirmed in SQLite and MySQL as well. This influences current generated SQL for checking isnull on reverse nullable (null=True) relations as well.

filter(<reverse_relation>__isnull...)

generates

("<related_table>"."id" IN (SELECT U1."<relation>_id" FROM "<table>" U1 WHERE U1."id" IS NOT NULL)

Because of the null=True in the relation, the subquery might return NULL-values which results in an empty query instead of a filtered one.

[Bas Peschier]

Replying to bpeschier:

Confirmed in SQLite and MySQL as well. This influences current generated SQL for checking isnull on reverse nullable (null=True) relations as well.

Looked further into it and created a related ticket for this specific bug which is in SQL-generation: #13815

[anonymous]

#13579 is a duplicate.

[Jacob]

Milestone 1.3 deleted

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Tim Graham]

Duplicate of #20024.