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

[wardi-django@…]

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:

SomeModel.objects.filter(my_field=None)

does nothing, while

SomeModel.objects.exclude(my_field=None)

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.

[wardi-django@…]

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

[django@…]

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

[anonymous]

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!

[wardi-django@…]

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.

[Russell Keith-Magee]

(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).

[Collin Grady <cgrady@…>]

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

[Malcolm Tredinnick]

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.

[Gary Wilson]

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

[Thomas Güttler (Home) < >]

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

[Malcolm Tredinnick]

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

[Malcolm Tredinnick]

(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