Opened 6 years ago

Closed 16 months ago

#13768 closed Bug (duplicate)

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

Reported by: Felipe 'chronos' Prenholato Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Normal Keywords: None, NULL, in, postgres, postgresql
Cc: niels.busch@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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'}

Change History (10)

comment:1 Changed 6 years ago by Russell Keith-Magee

Triage Stage: UnreviewedAccepted

comment:2 Changed 6 years ago by 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.

comment:3 in reply to:  2 Changed 6 years ago by 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

comment:4 Changed 6 years ago by Julien Phalip

Severity: Normal
Type: Bug

comment:5 Changed 6 years ago by anonymous

#13579 is a duplicate.

comment:6 Changed 5 years ago by Jacob

milestone: 1.3

Milestone 1.3 deleted

comment:11 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:12 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:13 Changed 3 years ago by Niels Sandholt Busch

Cc: niels.busch@… added

comment:14 Changed 16 months ago by Tim Graham

Resolution: duplicate
Status: newclosed
Summary: Bug in Django ORM when filtering with 'in' lookup and None in values list (1.1.x and 1.2.x)'exclude' does not work with lists containing a 'None' element.

Duplicate of #20024.

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