Code

Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#12615 closed (invalid)

Broken queryset indexing with postgresql_psycopg2 with custom through model

Reported by: yxven Owned by: nobody
Component: Uncategorized Version: 1.1-beta
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (2)

models.py (321 bytes) - added by yxven 4 years ago.
tests.py (1000 bytes) - added by yxven 4 years ago.

Download all attachments as: .zip

Change History (4)

Changed 4 years ago by yxven

Changed 4 years ago by yxven

comment:1 Changed 4 years ago by russellm

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

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.

comment:2 Changed 4 years ago by yxven

You are right. Thank you for the correction.

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.