Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#11568 closed (invalid)

Ordering by related attributes makes distinct() break

Reported by: Simon Law Owned by: nobody
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords: order_by distinct ForeignKey
Cc: benoit@…, simon@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


The problem is that sorting on an attribute of a related model causes distinct() to fail.

This is because the SELECT DISTINCT operates on the extra column required for the LEFT OUTER JOIN, which makes it impossible to group the desired results.

To reproduce the error, run this test case:

import django

from django.db import models

class Article(models.Model):
    title = models.CharField(max_length=128)

    def __unicode__(self):
        return self.title

class Comment(models.Model):
    article = models.ForeignKey(Article)
    comment = models.CharField(max_length=128)

    def __unicode__(self):
        return "%s on %s" % (self.comment, self.article.title)

from django.test import TestCase
from django import db
from django.conf import settings

from .models import Article, Comment

class TestOrderDistinctBug(TestCase):
    def test(self):
        settings.DEBUG = True
        awesome = Article.objects.create(title="Awesome Title")
        different = Article.objects.create(title="Different Title")
        wonderfull = Article.objects.create(title="Bad Title")
        Comment.objects.create(article=awesome, comment="Agreed")
        Comment.objects.create(article=awesome, comment="Dummy")
        Comment.objects.create(article=different, comment="Yeah")
        Comment.objects.create(article=different, comment="Onoz ")
        Comment.objects.create(article=wonderfull, comment="First !")
        articles = Article.objects.order_by("comment__comment").distinct()

        print "articles=%s" % articles
        # articles=[<Article: Awesome Title>, <Article: Awesome Title>, <Article: Bad Title>, <Article: Different Title>, <Article: Different Title>]

        print "sql is : %s" % db.connection.queries[0]
        # sql is : {'time': '0.001', 
        # 'sql': u'SELECT DISTINCT `order_bug_article`.`id`, `order_bug_article`.`title`, `order_bug_comment`.`comment`
        # FROM `order_bug_article` LEFT OUTER JOIN `order_bug_comment` ON (
        #    `order_bug_article`.`id` = `order_bug_comment`.`article_id`
        # ) ORDER BY `order_bug_comment`.`comment` ASC LIMIT 21'}

        # count returns the right thing
        self.assertEquals(articles.count(), 3)
        # but the content of the query set is wrong
        self.assertEquals(len(articles), 3)

Change History (2)

comment:1 Changed 9 years ago by Alex Gaynor

Resolution: invalid
Status: newclosed

comment:2 Changed 9 years ago by Simon Law

Aw, it's documented, but it's still awful behaviour.

Would you guys take a patch that would fix this up?

Note: See TracTickets for help on using tickets.
Back to Top