Opened 2 years ago

Last modified 4 weeks ago

#20024 new Bug

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

Reported by: stillwater.ke@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by lukeplant)

For example:

Entry.objects.exclude(foo__in=[None, 1])

It is supposed to return all items whose foo field is not None or 1, but it actually returns an empty query set.

Change History (5)

comment:1 Changed 2 years ago by akaariai

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

This is a known failure (tested in queries/tests.py, test_col_not_in_list_containing_null()).

This is somewhat hard to fix as SQL's "somecol NOT IN lst_containing_null" is weird - it will always return False (well, technically UNKNOWN but this doesn't matter here), and this is what causes the bug.

It would be possible to check for presence of None in the given list. If present, remove it and add in a proper IS NULL check instead. But then a query like .exclude(somecol__in=qs_containig_null) would work differently from .exclude(somecol__in=list(qs_containig_null)). I guess that could be classified as known abstraction leak.

comment:2 Changed 2 years ago by aaugustin

Let's not be too smart... NULL in SQL is nowhere near as consistent as None in Python because of SQL's tri-valued boolean logic; we cannot hide this.

comment:3 Changed 2 years ago by lukeplant

  • Description modified (diff)

comment:4 Changed 4 weeks ago by timgraham

#13768 is a duplicate.

comment:5 Changed 4 weeks ago by timgraham

  • Summary changed from 'exclude' does not work with lists containing a 'None' element. to QuerySet.exclude() does not work with lists containing a 'None' element.
Note: See TracTickets for help on using tickets.
Back to Top