#20024: QuerySet.exclude() does not work with lists containing a 'None' element.
-------------------------------------+-------------------------------------
Reporter: stillwater.ke@… | Owner: Eddy
| ADEGNANDJOU
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Eddy ADEGNANDJOU):
Considering this model:
{{{#!python
class Entry(models.Model):
foo = models.IntegerField(null=True, blank=True)
def __str__(self):
return f"Entry {self.id} with foo={self.foo}"
}}}
Here is the current behavior of {{{exclude}}} with the {{{__in}}} lookup
and a list containing {{{None}}}:
{{{#!shell
>>> Entry.objects.bulk_create([Entry(foo=1), Entry(foo=2),
Entry(foo=None)])
[<Entry: Entry 1 with foo=1>, <Entry: Entry 2 with foo=2>, <Entry: Entry 3
with foo=None>]
>>> Entry.objects.exclude(foo__in=[None])
<QuerySet [<Entry: Entry 1 with foo=1>, <Entry: Entry 2 with foo=2>,
<Entry: Entry 3 with foo=None>]>
>>> str(Entry.objects.exclude(foo__in=[None]).query)
'SELECT "core_entry"."id", "core_entry"."foo" FROM "core_entry"'
>>> Entry.objects.exclude(foo__in=[None, 1])
<QuerySet [<Entry: Entry 2 with foo=2>, <Entry: Entry 3 with foo=None>]>
>>> str(Entry.objects.exclude(foo__in=[None, 1]).query)
'SELECT "core_entry"."id", "core_entry"."foo" FROM "core_entry" WHERE NOT
("core_entry"."foo" IN (1) AND "core_entry"."foo" IS NOT NULL)'
}}}
Let's analyse the previous SQL when {{{foo=None}}} (NULL in SQL)
{{{#!sql
"core_entry"."foo" IN (1) ---> NULL IN (1) ---> UNKNOWN
"core_entry"."foo" IS NOT NULL ---> NULL IS NOT NULL ---> FALSE
("core_entry"."foo" IN (1) AND "core_entry"."foo" IS NOT NULL) --->
(UNKNOWN AND FALSE) ---> FALSE
NOT ("core_entry"."foo" IN (1) AND "core_entry"."foo" IS NOT NULL) --->
NOT FALSE ---> TRUE
}}}
For {{{foo=None}}}, the SQL condition evaluates to {{{TRUE}}}. So
**{{{Entry(foo=None)}}} is also returned by
{{{Entry.objects.exclude(foo__in=[None, 1])}}}**
This is not specific to Django. All frameworks or ORMs that generate IN /
NOT IN SQL queries can face this issue, because the root cause is SQL’s
three-valued logic. Most ORMs in other languages do not automatically
“fix” this. Automatic fixing could introduce inconsistencies and subtle
bugs.
Should we still try to find a Django-specific solution to this, or should
we promote explicit queries using Q-objects for example ?
{{{#!shell
>>> from django.db.models import Q
>>> Entry.objects.exclude(Q(foo=None) | Q(foo=1))
<QuerySet [<Entry: Entry 2 with foo=2>]>
>>> str(Entry.objects.exclude(Q(foo=None) | Q(foo=1)).query)
'SELECT "core_entry"."id", "core_entry"."foo" FROM "core_entry" WHERE NOT
(("core_entry"."foo" IS NULL OR ("core_entry"."foo" = 1 AND
"core_entry"."foo" IS NOT NULL)))'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/20024#comment:26>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019a01a6dfab-f4775e1e-3001-4681-87ce-7e49a8628985-000000%40eu-central-1.amazonses.com.
