models.my_model.objects.get(id=None) returns a result in MySQL

[Randy Barlow]

I stumbled upon an interesting bug today, and I believe that I've narrowed it down to Django's MySQL layer. When using MySQL, models.my_model.objects.get(id=None) will return a result, when it should raise a DoesNotExist exception. Here is a very simple example that will reproduce this error. Suppose you have the following model:

from django.db import models

class company(models.Model):
    name = models.CharField(max_length=255, unique=True)
    active = models.BooleanField(default=True)

Here is a shell session that exhibits the behavior I expect, using sqlite3:

>>> from mysqltest import models
>>> models.company.objects.all()
[]
>>> c = models.company(name='the Company')
>>> c.save()
>>> models.company.objects.all()
[<company: company object>]
>>> models.company.objects.get(id=1)
<company: company object>
>>> models.company.objects.get(id=None)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "//usr/lib/python2.5/site-packages/django/db/models/manager.py", line 93, in get
    return self.get_query_set().get(*args, **kwargs)
  File "//usr/lib/python2.5/site-packages/django/db/models/query.py", line 303, in get
    % self.model._meta.object_name)
DoesNotExist: company matching query does not exist.

And here is a shell session demonstrating the behavior that MySQL exhibits:

>>> from mysqltest import models
>>> models.company.objects.all()
[]
>>> c = models.company(name='the Company')
>>> c.save()
>>> models.company.objects.all()
[<company: company object>]
>>> models.company.objects.get(id=1)
<company: company object>
>>> models.company.objects.get(id=None)
<company: company object>

I have done a little poking around to narrow down whether the problem is in Python's MySQL bindings, or in Django's MySQL layer, and I do believe it is a Django bug. When I executed the queries (gotten from django.db.connection) directly using python's mysql bindings, the results came back as expected with no results. I also tried using the mysql shell directly, and also found it to return the expected results (nothing).

[Alex Gaynor]

This is an issue with MySQL itself, for whatever god forsaken reason it decided that doing an ID IS NULL query should return the latest added result(there was a thread on django-dev quite a while back about this) for that connection, so if you do it from the shell nothing was last added on that connection, I'm going to mark this as wontfix therefore.

[Karen Tracey]

​http://groups.google.com/group/django-developers/browse_thread/thread/d4180b8addf5e970/0561af2d43914833?

is the thread mentioned above. At the end of the thread Malcolm wasn't convinced it was worth doing anything to turn off this behavior when using Django/MySQL. If you feel strongly that it should be turned off, you might want to raise the issue on the developer's list. Alternatively you might propose where/how this could have been documented so that when you ran into it you found the explanation...from a brief look I don't see that this was documented in the "notes on supported databases" doc, so it might make sense to put something there.

[Malcolm Tredinnick]

To clarify Karen's comment: right now Django is behaving exactly as the database is configured. If you don't want that behaviour, you should configure your database to do the right thing for your preferences. We consistently delegate a bunch of behaviour to the database and this is one case. "Working around" that would lead to inconsistencies and unexpected behaviour all over the place; it's a real slippery slope.

So, Django works with the database and configuration you give it. It doesn't try to second-guess your intentions (and if your intention is to use MySQL, with it's arguably deranged default setting in this case, that's your choice).