Broken queryset indexing with postgresql_psycopg2 with custom through model

[yxven]

If you start a new project and setup a database using postgresl_psycopg2 (I know sqlite3 works. I dunno about others), and create a model that uses a custom through model, queryset indexing will be broken when accessed through that relationship.

I've attached a simplified models.py and tests.py to explain and demonstrate it.

[Russell Keith-Magee]

Although confusing, this actually isn't an error on Django's part. Your code is making an assumption that isn't valid.

Your failing test case is essentially the following code:

        zipcodes = Zipcode.objects.filter(people = self.bob)
        print zipcodes[0]
        print zipcodes[1]

You are assuming that zipcodes is an ordered list, and that you are indexing into that list. You aren't. Zipcodes is an unevaluated queryset. The queryset is evaluated twice - once for each subscript.zipcodes[0] translates to the SQL query:

SELECT * from testapp_zipcode LIMIT 1;

... and zipcodes[1] translates to:

SELECT * from testapp_zipcode LIMIT 1 OFFSET 1;

Zipcodes doesn't have any inherent ordering, so there is no guarantee that the database will return zipcode objects in the same order for the two queries. As a result, you are seeing id=2 returned for both queries.

This won't even be 100% reproducible - it really does depend on the internals of the database at the time you run the two queries. The inconsistency between databases is one example of how this problem can manifest.

There are several ways you can fix your code:

1. Put an order_by() clause on your query
2. Put an ordering clause in the Meta definition for the Zipcode model
3. Fully evaluate the query before indexing into it (i.e., list(zipcode.values()). By forcing the query to be evaluated, the index operators will operate on the cached result of the full queryset, rather than issuing two individual queries.

[yxven]

You are right. Thank you for the correction.