Django QuerySet contains duplicate entries

Question

I'm trying to retrieve some of the contents of a table using django's ORM.

results = Table.objects.using('production').filter(foreign_id=76)
results.count()  # 129807

The id column of this table is the primary key and thus, has unique entries. However, the returned QuerySet has duplicate entries:

results[7999].id  # 27177397
results[121679].id  # 27177397

It's not just the id that is duplicate, it's the entire row. In fact, the last 8,000 results of the QuerySet are all duplicate (I think of the first 8,000 but I haven't verified).

Of course, I have verified that the id column in the table is unique and that, when querying for 27177397 with sql directly (or via the django ORM), I only get one result.

results = Table.objects.using('production').filter(id=27177397, foreign_id=76)
results.count()  # 1

How can this be?

I am using django 1.6.7 using pyscopg2 2.5.3.

Answer 1

Filtering by foreign key force a join (also, default order by can induce a join). Use distinct to remove duplicate row:

results = ( Table
            .objects
            .using('production')
            .filter(foreign_id=76)
            .order_by()
            .distinct()
            )