Code

Opened 4 years ago

Last modified 4 months ago

#13768 new Bug

Bug in Django ORM when filtering with 'in' lookup and None in values list (1.1.x and 1.2.x)

Reported by: chronos 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'}

Attachments (0)

Change History (9)

comment:1 Changed 4 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 follow-up: Changed 4 years ago by bpeschier

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 4 years ago by bpeschier

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 3 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:5 Changed 3 years ago by anonymous

#13579 is a duplicate.

comment:6 Changed 3 years ago by jacob

  • milestone 1.3 deleted

Milestone 1.3 deleted

comment:11 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:12 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:13 Changed 4 months ago by niels

  • Cc niels.busch@… added

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.