None != Null?
David Cramer
myfield__isnull=True in the database backend?
Forest Bond
>
> Is there a specific reason that myfield=None doesn't translate to
> myfield__isnull=True in the database backend?
myfield = None translates into the SQL expression myfield = NULL. This is not
really that useful, since that clause will always evaluate to false, but it is
very correct. Transforming myfield = None to SQL myfield IS NULL seems a little
magical to me. If that were done, though, I'd be in favor of dropping __isnull
entirely, since it would no longer be uniquely useful.
-Forest
Benjamin Slavin
>
> Is there a specific reason that myfield=None doesn't translate to
> myfield__isnull=True in the database backend?
Hi David,
You may want to look here: http://code.djangoproject.com/ticket/1050
- Ben
David Cramer
SQL's NULL.
I'd very much like to see it evaluate to such, and I agree,
isnull=True should be deprecated.
On Jul 16, 9:46 am, "Benjamin Slavin" <benjamin.sla...@gmail.com>
wrote:
Michael Radziej
>
> It's not so much related to ManyToMany. But None is Python's equiv to
> SQL's NULL.
> [...]
This was discussed and decided long time ago, please search the archives if
you are interested in the reasons.
Michael
--
noris network AG - Deutschherrnstraße 15-19 - D-90429 Nürnberg -
Tel +49-911-9352-0 - Fax +49-911-9352-100
http://www.noris.de - The IT-Outsourcing Company
Vorstand: Ingo Kraupa (Vorsitzender), Joachim Astel, Hansjochen Klenk -
Vorsitzender des Aufsichtsrats: Stefan Schnabel - AG Nürnberg HRB 17689
Patrick Lauber
Fax +41 44 480 12 71
Mobile +41 76 390 22 23
Mail patrick.lauber@divio.ch
Web http://www.divio.ch
Adrian Holovaty
> myfield__isnull=True in the database backend?
The reason is in the implementation details, but, come to think of it,
I agree that it'd be a lot more beautiful if myfield=None were
translated to "myfield IS NULL" and myfield__ne=None were translated
to "myfield IS NOT NULL". I don't know off the top of my head how easy
this would be to fix, but it seems like it wouldn't be a huge deal.
Adrian
--
Adrian Holovaty
holovaty.com | djangoproject.com
David Cramer
fit w/ trunk :)
Didn't know about __ne before today. A lot easier than throwing on
excludes :)
On Jul 16, 2:19 pm, "Adrian Holovaty" <holov...@gmail.com> wrote:
Adrian Holovaty
> excludes :)
Whoop, my fault -- the "__ne" lookup no longer exists. You're right,
exclude() is the right way to do it.
Michael Radziej
the current behaviour is not a random implementation detail. It's been discussed
in October 2006:
While interpreting __exact=None as "WHERE ... = NULL" might confuse new
users, it is probably just what users used to SQL expect (at least for me it
comes quite naturally).
I guess you cannot make both groups completely happy, but your proposed
change is also backwards incompatible and has been explicitly decided
different before. Can you please reconsider?
Michael
--
noris network AG - Deutschherrnstrasse 15-19 - D-90429 Nürnberg -
Malcolm Tredinnick
[...]
> While interpreting __exact=None as "WHERE ... = NULL" might confuse new
> users, it is probably just what users used to SQL expect (at least for me it
> comes quite naturally).
I wasn't convinced by this argument back in October and I still aren't.
Firstly, it shouldn't be what users of SQL will expect. I think you're
generalising from one case and it's not representative. Nobody writing
an SQL query directly will ever be writing "foo=NULL" in their queries.
It's just a waste of keystrokes and processor time. It's guaranteed to
never match anything but will often disguise a bug, since a quick
eye-scan might not notice the "=" since we often assume a seasoned SQL
coder isn't going to make that blunder and just notice the column name
and the NULL check. So I don't buy the argument that an SQL coder is
going to expect such a construct to ever be created. I certainly don't
and I've written more than a few thousand lines of SQL in my years
(again, it's one example, but don't focus on that; look at the logic of
why this isn't going to be a construct that is ever used).
Secondly, we're talking about the mapping from a Python construct to an
SQL construct, something which doesn't have a natural mapping, so all
constructs are going to be slightly artificial.
On a semantic level "foo=None" (since we can't write "foo is None" in a
function call) can be fairly easily understood as "foo has the special
value None", since it's entirely analogous to "foo=6". At the SQL level,
"foo is NULL" means "foo has the special value NULL". Mapping None to
NULL isn't a huge leap in the trip from Python to SQL, so the whole
process shouldn't be counter-intuitive (yes, there will be exceptions,
but there always are). Remember that we're writing the ORM for Python
coders, so using natural-looking Python behaviour is important.
> I guess you cannot make both groups completely happy, but your proposed
> change is also backwards incompatible and has been explicitly decided
> different before. Can you please reconsider?
I'm not convinced about the huge negative impact of the change, either.
There was *one* use-case touted (by Deryck Hodge) in the original
thread. It has limited applicability. It's only for disjunctive ("or")
filters, since a sequence of conjunctions like
filter(foo=None).filter(bar=6) is just a complicated way of writing the
empty list unless you're using, say, PostgreSQL 7.1 or something else
that doesn't handle NULLs correctly.
I didn't like this change when it went in and I wouldn't be unhappy to
see it fixed. As usual, though, it's not the complete end of the world
if it doesn't change, just makes it a less beautiful place.
Malcolm
--
Telepath required. You know where to apply...
http://www.pointy-stick.com/blog/
Michael Radziej
first, I really dislike to reopen discussions after more than one year, not
just for this particular question but in general. We won't get anywhere if
don't keep to decisions, except for cases where new and important arguments
appear.
I don't see any new points in this discussion. The "new user" argument has
been brought up in the original discussion, too. The question was settled,
described in documentation
(http://www.djangoproject.com/documentation/db-api/#exact), and there's a
regression test for it (regressiontests/null_query). I don't think it's a
good idea to reopen decisions at will.
Furthermore, the database query lookup is listed as a stable API
(http://www.djangoproject.com/documentation/api_stability/)
Worse, the proposed change will mean to change an existing API so that it
doesn't break *loudly*, but it will silently produce different results.
I'd accept all these problems for something important. But in this case?
It's a question that will always be a bit arbitrary. Currently we get
complaints from newbies, after the change we'll get complaints from people
used to think SQL.
Personally, I've relied on the decision as it was made, and the changes will
mean a lot of rewriting (well, of course the work will be more in finding
the places where it breaks). It would not be very friendly to your users to
break compatibility in this way.
On Tue, Jul 17, Malcolm Tredinnick wrote:
>
> On Tue, 2007-07-17 at 07:28 +0200, Michael Radziej wrote:
> [...]
> > While interpreting __exact=None as "WHERE ... = NULL" might confuse new
> > users, it is probably just what users used to SQL expect (at least for me it
> > comes quite naturally).
>
> I wasn't convinced by this argument back in October and I still aren't.
> Firstly, it shouldn't be what users of SQL will expect. I think you're
> generalising from one case and it's not representative. Nobody writing
> an SQL query directly will ever be writing "foo=NULL" in their queries.
Of course not. That's what I mean (it's not a real code line):
# find all siblings of this object
siblings_qset = models.Something.objects.filter(parent__exact=obj.parent_id)
I've got better use cases that include building complicated filters, but
they are hard to explain without background.
> It's just a waste of keystrokes and processor time. It's guaranteed to
> never match anything but will often disguise a bug, since a quick
> eye-scan might not notice the "=" since we often assume a seasoned SQL
> coder isn't going to make that blunder and just notice the column name
> and the NULL check. So I don't buy the argument that an SQL coder is
> going to expect such a construct to ever be created. I certainly don't
> and I've written more than a few thousand lines of SQL in my years
> (again, it's one example, but don't focus on that; look at the logic of
> why this isn't going to be a construct that is ever used).
>
> Secondly, we're talking about the mapping from a Python construct to an
> SQL construct, something which doesn't have a natural mapping, so all
> constructs are going to be slightly artificial.
>
> On a semantic level "foo=None" (since we can't write "foo is None" in a
> function call) can be fairly easily understood as "foo has the special
> value None", since it's entirely analogous to "foo=6". At the SQL level,
> "foo is NULL" means "foo has the special value NULL". Mapping None to
> NULL isn't a huge leap in the trip from Python to SQL, so the whole
> process shouldn't be counter-intuitive (yes, there will be exceptions,
> but there always are). Remember that we're writing the ORM for Python
> coders, so using natural-looking Python behaviour is important.
Well, there are two ways to interprete this particular lookup.
The ORM is an ORM for relational database users, and you won't get far if
you have no idea what NULL is. Django does not abstract away the relational
database. If you don't know what a relational database is, you probably
better off using Zope ...
I consider your point of view as valid as mine. And it was brought up in the
original discussion, too.
> > I guess you cannot make both groups completely happy, but your proposed
> > change is also backwards incompatible and has been explicitly decided
> > different before. Can you please reconsider?
>
> I'm not convinced about the huge negative impact of the change, either.
> There was *one* use-case touted (by Deryck Hodge) in the original
> thread. It has limited applicability. It's only for disjunctive ("or")
> filters, since a sequence of conjunctions like
> filter(foo=None).filter(bar=6) is just a complicated way of writing the
> empty list unless you're using, say, PostgreSQL 7.1 or something else
> that doesn't handle NULLs correctly.
>
> I didn't like this change when it went in and I wouldn't be unhappy to
> see it fixed. As usual, though, it's not the complete end of the world
> if it doesn't change, just makes it a less beautiful place.
Russell has based the decision on uses within the django tests that appear
to come naturally for him, too, so I don't share your opinion that this
change wouldn't affect anything. Also, it questions your opinion that the
current semantics are pointless:
http://groups.google.com/group/django-developers/msg/b3f7e4cf6f9ffd98,
Russell Keith-Magee wrote:
--- quoting Russell ---
Hence the documentation to that effect. No, it isn't a particularly
useful expression, but it does have at least one use case (discussed
below).
I wasn't aware of the Oracle issue; but that strikes me as a problem
that should be fixed at the backend level, rather than as a reason to
exclude a feature.
[On 10/14/06, Malcolm Tredinnick <malc...@pointy-stick.com> wrote:]
> So doesn't this defeat the purpose of the exercise now? Using
> __exact=None is now a synonym for "please don't ever return anything
> (except on Oracle)" whereas I thought your were shooting for "where this
> field is NULL".
The use case that started this for me is in the regression test - a
related manager for an unsaved object. In this case, 'don't ever
return anything' is _exactly_ the right response, because nothing can
be related to the unsaved object.
Mapping __exact=None to _isnull=True will give effectively the same
results, but via a 'return everything with a primary key value of NULL
- which is nothing' interpretation of the problem.
However, as you noted earlier, this second approach is redundant given
the existence of _isnull. It also hides a path to the expression of
some valid (albiet edge case) SQL. The interpretation suggested by
Michael struck me as a good way to avoid the redundancy, fix the bug I
found, expose a small corner of legal SQL statements, and ultimately
required less code to implement.
The biggest problem I could see was the potential for '__exact=None
doesn't mean what you think it does' amongst newcomers. For me, the
best solution to this problem is documentation.
--- end of quote ---
So (really) long,
Michael Radziej
> first, I really dislike to reopen discussions after more than one year, not
Sorry: Should be "than half a year", of course ;-)
Michael
Ned Batchelder
Michael, could you help us out by elaborating on what code you have that you would have to re-write? __isnull would be deprecated but wouldn't go away. With the current semantics, you couldn't have any foo=None uses in your code (since it means nothing useful), so how could changing its meaning to something useful break your code?
I agree that reopening decisions arbitrarily is a bad thing. But reconsidering in light of persistent and intelligent opposition is simply being flexible and listening to your customers.
--Ned.
-- Ned Batchelder, http://nedbatchelder.com
Forest Bond
> I'm in the camp that thinks making foo=None useful is a good thing. To the
> python programmer who is our audience, this has a very clear semantic.
> Especially when you take the scenario of using one object's values to create a
> query for objects like it. Having to special case None seems arbitrary and
> confusing.
>
> Michael, could you help us out by elaborating on what code you have that you
> would have to re-write? __isnull would be deprecated but wouldn't go away.
> With the current semantics, you couldn't have any foo=None uses in your code
> (since it means nothing useful), so how could changing its meaning to something
> useful break your code?
Well, I hate to make it even more of a special case, but would something like
this make sense:
foo__isnull = True foo IS NULL (deprecated)
foo__exact = None foo = NULL
foo = None foo IS NULL
Or does that break consistency in an even worse way (since foo = x is no longer
the same as foo__exact = x)? The only reason I suggest it is that it does keep
"foo = NULL" in the realm of producable SQL expressions, while making the common
case "foo = None" a little more intuitive for Python folks.
It may not be that great an idea, either.
-Forest
--
Forest Bond
http://www.alittletooquiet.net
Nis Jørgensen
> Hi Adrian,
>
> the current behaviour is not a random implementation detail. It's been discussed
> in October 2006:
>
> http://groups.google.com/group/django-developers/browse_thread/thread/e36e80faf653b6d4/15fbf502162bc564?lnk=gst
>
"=NULL" and the existing behavior were put forward. AFAICS the main
argument in favor of "=NULL" was that it required less code - something
which shouldn't be much of an issue if someone is willing to write the
patch. A second argument was that this would be expected by people
knowledgeable in SQL, which I doubt - see below. Thirdly, there is the
issue of redundancy with isnull, which I would not see as much of an issue.
Note that the only use case given for "=NULL" goes on to state that "IS
NULL" gives the same result. The use case for "IS NULL" on the other
hand, seems straight forward.
Of course, a fourth argument is present today - backwards compatibility,
especially of documentation.
> While interpreting __exact=None as "WHERE ... = NULL" might confuse new
> users, it is probably just what users used to SQL expect (at least for me it
> comes quite naturally).
> I guess you cannot make both groups completely happy, but your proposed change is also backwards incompatible and has been explicitly decided
> different before. Can you please reconsider?
I am a member of the set (new Django users INTERSECT users used to SQL).
I would expect =None to be mapped to "IS NULL". I am of course only one
person, but I question your dichotomy. I would expect a few novice SQL
users, or perhaps people trained on Access95, to expect "=NULL" -
thinking it would select the rows with NULL values.
The Oracle behavior in versions prior to 9 was apparently the other way
around - ''='' returned FALSE, '' IS NULL returned TRUE. So NULL
behaved the way you expected, '' didn't.
Yours,
Nis Jorgensen
Michael Radziej
> Michael, could you help us out by elaborating on what code you have that
> you would have to re-write? __isnull would be deprecated but wouldn't
> go away. With the current semantics, you couldn't have any foo=None
> uses in your code (since it means nothing useful), so how could changing
> its meaning to something useful break your code?
No, I don't want to go through my code now and look for all the use
cases. There's nothing that couldn't be easily rewritten with adding a
test for None, so I don't see the need of this exercise ...
My opposition is not so much because I prefer the old meaning (which I
do), but because it has already been decided, and the change is so subtle.
I doubt my testsuite will find all problems, since the proposed meaning of
__exact=None only produces different results if there's another record
with a NULL value in the appropriate place in the test database.
If by any means you must change the treatment of None, then I'd appeal
for raising an exception if someone passes None to the various lookup
expressions. Then it's at least failing loudly, and still newbie-proof.
But I'd rather ask the Django project to stick to its published API
stability guide. (Sorry for repeating myself.)
I see a few different things to back up my view that the current usage
is preferable:
a) the change opens a bunch of related questions: How should startswith,
endswith, regex etc. behave when given a None value?
b) the new meaning will lead newbies to a wrong understanding of SQL, since they will
assume that 'WHERE col=NULL' means 'WHERE col is NULL', which it does
not.
c) will joined queries connect NULL values? For example, if I have:
model X with y = ForeignKey('Y', to_field='z', null=True, blank=True)
model Y with z = IntegerField(null=True, blank=True)
Then assume instances x1=X(id=1,y=None) and a y1=Y(id=1,z=None) (both saved).
Will models.X.objects.filter(z__id__exact=1) find x1? How about x1.y?
And y1.x_set?
Michael
Marty Alchin
> b) the new meaning will lead newbies to a wrong understanding of SQL, since they will
> assume that 'WHERE col=NULL' means 'WHERE col is NULL', which it does
> not.
I've been avoiding this topic for the most part, but I'd like to put
my two cents in on this comment. To my knowledge, much of the point of
Django is to avoid having to deal with SQL wherever possible, so I
find it strange to think that Django should try to be a tool for
teaching SQL to the uninitiated. The ORM is a way to map logical
Python code to function SQL statements, that's all. If it makes sense
in Python and works properly in SQL, it's a success.
If users want to learn SQL, they shouldn't be using Django to do it.
And if they do, that's on them if they run into problems. At least,
that's the way it seems to me. Ignore it if you like.
-Gul
David Cramer
is. '' is not None, False is not None. If this is what it's currently
doing then the ORM handles it incorrectly. Yes, it may be a backwards
incompatible change, but who relies on None? I've yet to see
myfield=None pull any results from my database, whether the field was
an empty string or a NULL value.
I'd also like to point out the obvious that Django does make quite a
few backwards incompatible changes, and this one, I don't think has
any serious chance to cause a ruckus with current code. It's a hell of
a lot easier than changing auto_now to datetime.datetime.now
everywhere ;)
If Django's ORM can't evolve then Django can't evolve.
Dan Watson
postgresql, there is a configuration option to transform "= NULL" to
"IS NULL" on the fly. [1] So if you're using "= NULL" (which is an
error in virtually all cases) you may get different results on
different databases anyway. Using "IS NULL" is unambiguous.
[1] http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS
Michael Radziej
I'm still surprised that it seems to be only me in my camp ... well ...
I did a bit of research how other python ORMs treat this:
- SqlObject: doesnt't talk about NULL it in the docs
- SqlAlchemy, docs:
# "null" comparison via == (converts to IS)
SQLusers.select(users.c.user_name==None).execute()
- Storm, from the sources:
def compile_eq(compile, state, eq):
if eq.expr2 is None:
return "%s IS NULL" % compile(state, eq.expr1)
return "%s = %s" % (compile(state, eq.expr1), compile(state, eq.expr2))
Given that, I see it in a different light now, only that I'm still a bit
reluctant for reasons of compatibility.
I apologize for all the noise, and I hope we have discussed the topic to
certain death by now ... may the powers make their decision. And, please,
no, don't send me any more private emails about it.
Michael
--
noris network AG - Deutschherrnstraße 15-19 - D-90429 Nürnberg -
Russell Keith-Magee
>
> On Tue, 2007-07-17 at 07:28 +0200, Michael Radziej wrote:
> [...]
> > While interpreting __exact=None as "WHERE ... = NULL" might confuse new
> > users, it is probably just what users used to SQL expect (at least for me it
> > comes quite naturally).
>
> I wasn't convinced by this argument back in October and I still aren't.
I suppose if I had to defend it now, I would rephrase this argument
slightly. I would say that =NULL _is_ a different query to IS NULL.
API beauty and Python symmetry is important, and I agree that in the
Python context, exact=None reads as isnull=True. However, IMHO so is
being able to touch as many SQL queries as possible. I'd like to think
that if a developer needs to write a particular query, the Django ORM
will get them there.
However, looking at the issue again, I'm not as convinced as I
obviously was in October. It's entirely possible I was just too close
to the underlying implementation at the time, and was seeing edge
cases that, in practice, don't exist. Mea culpa.
> I'm not convinced about the huge negative impact of the change, either.
Not sure I agree here. Any query that has been set up as
filter(foo=value) will be expecting no results to be returned if value
is None. If we make _equals=None map to __isnull=True, the same query
could start returning results if field foo has null=True in its
definition.
To me, this sounds like a big enough backwards compatibility problem
to be a concern.
This isn't to say I'm completely opposed to making the change - just
that we shouldn't underestimate how big a change it could be.
For the record, I'm a weak +0 on making the change. IMHO, the
backwards compatibility objections are significant enough to warrant
caution, but I can see the value in the syntactic change. I'm more
than happy to leave the call to someone else, and I could happily live
with either outcome.
> There was *one* use-case touted (by Deryck Hodge) in the original
> thread.
Deryck's use case is slightly different, to my reading. I believe he
was talking about the original behaviour, where __exact=None was a
completely ignored operation - which was the behaviour causing dramas
for related objects.
Yours,
Russ Magee %-)
David Cramer
laziness. Yes, maybe it will break things, but maybe those things are
meant to be broken. You could just as easily say myfield__exact which
would work as it would before, and leave the default to isnull for
None. This is similar to how you can (I believe) say myfield=[list]
and it does myfield__in.
On Jul 17, 5:54 pm, "Russell Keith-Magee" <freakboy3...@gmail.com>
wrote:
Michael Radziej
>
> I feel that returning an empty queryset where value=None is just
> laziness. Yes, maybe it will break things, but maybe those things are
> meant to be broken. You could just as easily say myfield__exact which
> would work as it would before, and leave the default to isnull for
> None. This is similar to how you can (I believe) say myfield=[list]
> and it does myfield__in.
Just a clarification: I don't think we are talking only about the
__exact=None queries, since value=None is just an abbreviation and
is internally mapped to the __exact=None type.
Michael
--
noris network AG - Deutschherrnstraße 15-19 - D-90429 Nürnberg -
Tai Lee
records and know that no records will ever be returned if a field
lookup is None (e.g. in the case of related objects to an unsaved
object), you should just catch that condition and not execute that
query.
In 9 years I've never written any SQL statement that contains such a
where clause intentionally. It's pointless, and this ambiguity and un-
pythonic behaviour is counter-intuitive. +1 for mapping =None to IS
NULL and removing __isnull.
If anyone *really* needs to execute an = NULL statement, they can do
it with custom SQL, as this should be an extreme edge case (if there's
any case for it, at all).
Gary Wilson
> On 7/16/07, David Cramer <dcra...@gmail.com> wrote:
> > Is there a specific reason that myfield=Nonedoesn't translate to
> > myfield__isnull=True in the database backend?
>
> The reason is in the implementation details, but, come to think of it,
> I agree that it'd be a lot more beautiful if myfield=Nonewere
> to "myfield IS NOT NULL". I don't know off the top of my head how easy
> this would be to fix, but it seems like it wouldn't be a huge deal.
So are we going to go with this and re-open http://code.djangoproject.com/ticket/2737?
Just frustrated by the fact I had to write something today like:
if category is not None:
questions = Question.objects.get(category=category,
question__contains='blah')
else:
questions = Question.objects.get(category__isnull=True,
question__contains='blah')
I think the above example is a much more common case than the case
relying on the current "= None" behavior. It also matches the
behavior when creating items:
class Article(models.Model):
title = models.CharField(maxlength=100)
author = models.CharField(maxlength=100, blank=True, null=True)
def __str__(self):
return str(self.id)
>>> Article.objects.create(id=10, title='Python', author=None)
<Article: 10>
>>> Article.objects.filter(author=None)
[]
>>> Article.objects.filter(author__isnull=True)
[<Article: 10>]
Also, the docs on the current behavior (added in
http://code.djangoproject.com/changeset/3902) seem to be incorrect.
The SQL getting executed when using "id__exact=None" or "id=None" is
"id = None", not "id = NULL".
Gary