Code

Opened 4 years ago

Closed 13 months ago

#12751 closed Uncategorized (invalid)

order_by after select_related returns empty queryset

Reported by: ozgurisil@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: order_by, select_related
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (0)

Change History (6)

comment:1 Changed 4 years ago by Alex

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to worksforme
  • Status changed from new to closed

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

comment:2 Changed 4 years ago by 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.

comment:3 Changed 4 years ago by 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>]

comment:4 Changed 3 years ago by jacob

  • milestone 1.2 deleted

Milestone 1.2 deleted

comment:5 Changed 13 months ago by foxwhisper

  • Easy pickings unset
  • Resolution worksforme deleted
  • Severity set to Normal
  • Status changed from closed to new
  • Type set to Uncategorized
  • UI/UX unset

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.

Last edited 13 months ago by foxwhisper (previous) (diff)

comment:6 Changed 13 months ago by anonymous

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

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.