order_by after select_related returns empty queryset

[ozgurisil@…]

When querying a model with select_related(), if the related model contains a field which has null rows, adding the order_by() results in an empty set. However, count() gives the correct number of records.

Foo.objects.select_related('abc').order_by('x') # returns []
Foo.objects.select_related('abc').order_by('x').count() #returns correct number

If I fill the null rows with data, the problem vanishes.

This further leads to a "TemplateSyntaxError: 'NoneType' object has no attribute '_latest_transaction_cache'" in the template.

[Alex Gaynor]

I can't reproduce this, if you can provide more details (models, a failing test case, etc.) please reopen.

[jashugan]

I had the same symptoms as this problem. I believe the problem was related to a foreign key in the model having the not null constraint, like so:

from django.db import models
import django.contrib.auth.models as auth_m

class Product(models.Model):
  # by default null is False
  user = models.ForeignKey(auth_m.User)

The database schema, however, had all null values for the user field. The query wasn't able to follow the null values for any of the fields, so it returned no records.

[jashugan]

To clarify the above:

The database schema allowed for null values for the user_id column, and all the products in the database had null values. When I changed set the user field for one of the records, it began appearing in the select_related queries. I don't think this is a problem with Django, just something to watch out for when your database schema doesn't match your model definition.

In [1]: import store.models as store_m

In [2]: store_m.Product.objects.select_related()
Out[2]: []

In [3]: store_m.Product.objects.select_related().count()
Out[3]: 128008

In [4]: import django.contrib.auth.models as auth_m

In [5]: prod = store_m.Product.objects.all()[0]

In [6]: prod.created_by = auth_m.User.objects.get(username='jashugan')

In [7]: prod.save()

In [8]: prod_m.Product.objects.select_related()
Out[8]: [<Product: Product object>]

[Jacob]

Milestone 1.2 deleted

[Cal Leeming]

Inadvertently I was able to reproduce this in Django 1.5 whilst attempting to migrate data between two projects. It was the same thing, unexpected NULL columns caused select_related() to return an empty queryset. However, at least in my case, I was able to fix this problem by doing a complete re-import of my data into a clean syncdb. The problem seems to happen after you make manual modifications to the table schema and you get something wrong. Although it would be nice to have some sort of warning for this, someone will have to take time/effort to provide a small project which can reproduce this problem.

I'm going to re-mark as open on the basis that I was able to reproduce.

[anonymous]

Yes, exactly what has been described can be recreated by having models defined like so:

lass Author(models.Model):
    name = models.CharField(max_length=255)

class Book(models.Model):
    name = models.CharField(max_length=50, primary_key=True)
    author = models.ForeignKey(Author)

    def __unicode__(self):
        return u'%s %s' % (self.name, self.author_id and self.author.name or '<no author>')

But data in the database like so:

mysql> describe ttt_author;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> describe ttt_book;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(50) | NO   | PRI | NULL    |       |
| author_id | int(11)     | YES  | MUL | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from ttt_book;
+--------+-----------+
| name   | author_id |
+--------+-----------+
| First  |      NULL |
| Second |      NULL |
+--------+-----------+
2 rows in set (0.03 sec)

With those models and data, you get non-zero count yet no rows when you try to use select_related:

>>> from ttt.models import Book
>>> Book.objects.select_related().count()
2
>>> Book.objects.select_related()
[]

This is because select_related has no effect on the count, so it is optimized out of the query when count is requested, the query issued is:

SELECT COUNT(*) FROM ttt_book

When you actually ask for the data, though, the select_related is honored and an inner join is added to the query:

SELECT ttt_book.name, ttt_book.author_id, ttt_author.id, ttt_author.name FROM ttt_book INNER JOIN ttt_author ON ( ttt_book.author_id = ttt_author.id )

Which results in no data since the inner join can't be satisfied by any rows. The ultimate cause here is the lie to the Django ORM about the constraints on the data: it's been told a column value cannot be null and yet it is. Django is building queries based on the assumption that what it has been told is correct, which seems entirely reasonable to me. The fix here is to make sure your schema matches what you've told Django.

If there is a way to recreate this issue without a mismatch in DB schema compared to what is in model defs, that would be something to investigate fixing in Django...but if the only way to recreate is to lie to the ORM, then I don't think anything should be changed in Django to attempt to detect/adjust to that sort of mismatch.