Opened 18 years ago

Closed 16 years ago

Last modified 16 years ago

#2737 closed enhancement (fixed)

DB API and Model Interface don't agree on meaning of None

Reported by: wardi-django@… Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version: dev
Severity: normal Keywords: exclude filter qs-rf-fixed
Cc: mir@…, django@… Triage Stage: Accepted
Has patch: yes Needs documentation: yes
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no


When the value of a field in a model is None it corresponds to a NULL item in the database.

However, when using filter() or exclude() you can't pass my_field=None eg:


does nothing, while


generates invalid SQL.

I know the preferred way to test for NULL is with __isnull, but the code above is what I tried first.
I would like it if my_field=None is treated as equivalent to my_field__isnull=True (a naive patch is attached)
but if not, then at least there should be a useful error raised when you try something like my_field=None.

Attachments (2)

isnull.patch (483 bytes ) - added by wardi-django@… 18 years ago.
[patch] adds support for my_field=None in filter() and exclude()
none-isnull.patch (523 bytes ) - added by Collin Grady <cgrady@…> 17 years ago.

Download all attachments as: .zip

Change History (20)

by wardi-django@…, 18 years ago

Attachment: isnull.patch added

[patch] adds support for my_field=None in filter() and exclude()

comment:1 by django@…, 18 years ago

I agree, i run into this sometimes, because for me filter(bla=None) simply feels like "bla IS NULL".
+1 from me

comment:2 by anonymous, 18 years ago

Cc: mir@… added

I had assumed that, different from the opinions abovem, filter(xxx=value) should mean the same as where xxx=value for all possible values. And in case of None, this means where xxx=NULL, yielding an empty result set. Current treatment of None values is really a riddle!

comment:3 by wardi-django@…, 18 years ago

I don't really like SQL oddities like "xxx=NULL always gives no results" leaking through django's python-like database abstraction layer.

Another sql oddity that I've run into is:

query_set.filter(name="foo") | query_set.exclude(name="foo") != query_set

if name can be NULL.. but I can live with that.

comment:4 by Malcolm Tredinnick, 18 years ago

Type: defectenhancement

comment:5 by Russell Keith-Magee, 18 years ago

Resolution: fixed
Status: newclosed

(In [3902]) Fixes #2737 -- Added code to allow None as a query value for exact queries, raising an error otherwise. exact=None is interpreted as the SQL 'value = NULL'. This fixes some minor problems with queries on unsaved objects with related object sets, and stops queries with a value of None being outright ignored (even if they reference an unknown attribute).

comment:6 by Collin Grady <cgrady@…>, 17 years ago

Has patch: set
Resolution: fixed
Status: closedreopened

.filter(foo=None) is actually sending "= None" not "IS NULL" - attaching patch to correct

by Collin Grady <cgrady@…>, 17 years ago

Attachment: none-isnull.patch added

comment:7 by Malcolm Tredinnick, 17 years ago

Resolution: wontfix
Status: reopenedclosed

The current behaviour is intentional and the behaviour is documented.

See this thread for the justification. It's a weird corner-case, but doing an exact match against NULL won't give sensible results in any case, as mentioned in the above thread.

comment:8 by Gary Wilson, 17 years ago

Resolution: wontfix
Status: closedreopened
Triage Stage: UnreviewedAccepted

Re-opening this, since in this thread, we have Adrian +1, Malcolm +0, Russell +0, and me +1.

comment:9 by Chris Beaven, 17 years ago

Needs documentation: set
Needs tests: set

comment:10 by Malcolm Tredinnick, 17 years ago

Keywords: Noneqs-rf added; None removed

comment:11 by Chris Beaven, 17 years ago

Keywords: qs-rf added; Noneqs-rf removed

comment:12 by Thomas Güttler <hv@…>, 17 years ago

Cc: hv@… added

comment:13 by Thomas Güttler (Home) < >, 17 years ago

+1 SomeModel.objects.filter(my_field=None) should behave like my_fileisnull=True

comment:14 by Malcolm Tredinnick, 17 years ago

(In [6760]) queryset-refactor: Interpret qs.filter(foo=None) to be the same as qs.filter(fooisnull=True). Refs #2737.

comment:15 by Malcolm Tredinnick, 17 years ago

Keywords: qs-rf-fixed added; qs-rf removed

comment:16 by jedie, 16 years ago

Cc: django@… added

comment:17 by Malcolm Tredinnick, 16 years ago

Resolution: fixed
Status: reopenedclosed

(In [7477]) Merged the queryset-refactor branch into trunk.

This is a big internal change, but mostly backwards compatible with existing
code. Also adds a couple of new features.

Fixed #245, #1050, #1656, #1801, #2076, #2091, #2150, #2253, #2306, #2400, #2430, #2482, #2496, #2676, #2737, #2874, #2902, #2939, #3037, #3141, #3288, #3440, #3592, #3739, #4088, #4260, #4289, #4306, #4358, #4464, #4510, #4858, #5012, #5020, #5261, #5295, #5321, #5324, #5325, #5555, #5707, #5796, #5817, #5987, #6018, #6074, #6088, #6154, #6177, #6180, #6203, #6658

comment:18 by Thomas Güttler, 16 years ago

Cc: hv@… removed
Note: See TracTickets for help on using tickets.
Back to Top