Opened 9 years ago

Closed 7 years ago

Last modified 7 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
Component: Database layer (models, ORM) Version: master
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: UI/UX:

Description

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.

Attachments (2)

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

Download all attachments as: .zip

Change History (20)

Changed 9 years ago by wardi-django@…

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

comment:1 Changed 9 years ago by django@…

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

comment:2 Changed 9 years ago by anonymous

  • 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 Changed 9 years ago by 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.

comment:4 Changed 9 years ago by mtredinnick

  • Type changed from defect to enhancement

comment:5 Changed 9 years ago by russellm

  • Resolution set to fixed
  • Status changed from new to closed

(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 Changed 8 years ago by Collin Grady <cgrady@…>

  • Has patch set
  • Resolution fixed deleted
  • Status changed from closed to reopened

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

Changed 8 years ago by Collin Grady <cgrady@…>

comment:7 Changed 8 years ago by mtredinnick

  • Resolution set to wontfix
  • Status changed from reopened to closed

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 Changed 7 years ago by gwilson

  • Resolution wontfix deleted
  • Status changed from closed to reopened
  • Triage Stage changed from Unreviewed to Accepted

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

comment:9 Changed 7 years ago by SmileyChris

  • Needs documentation set
  • Needs tests set

comment:10 Changed 7 years ago by mtredinnick

  • Keywords Noneqs-rf added; None removed

comment:11 Changed 7 years ago by SmileyChris

  • Keywords qs-rf added; Noneqs-rf removed

comment:12 Changed 7 years ago by Thomas Güttler <hv@…>

  • Cc hv@… added

comment:13 Changed 7 years ago by Thomas Güttler (Home) < >

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

comment:14 Changed 7 years ago by mtredinnick

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

comment:15 Changed 7 years ago by mtredinnick

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

comment:16 Changed 7 years ago by jedie

  • Cc django@… added

comment:17 Changed 7 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from reopened to closed

(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 Changed 7 years ago by guettli

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