Opened 17 years ago

Closed 16 years ago

#4789 closed (duplicate)

select_related + depth gives wrong result

Reported by: Gábor Farkas <gabor@…> Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Keywords: select_related, depth, qs-rf
Cc: gabor@…, herbert.poul@… Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

i have a situation, where using select_related with depth=1 or depth=2 gives wrong results.
increasing depth to 3, or leaving out the depth parameter gives correct results.

the data to reproduce the problem:

models.py: (for this test-case, i assume that it is in the app called 'myapp')

from django.db.models import Model, ForeignKey, CharField

class Newsletter(Model):
    name = CharField(maxlength=128)

class Team(Model):
    name = CharField(maxlength=128)

class Group(Model):
    team = ForeignKey(Team)

class User(Model):
    group = ForeignKey(Group)

class Subscription(Model):
    user = ForeignKey(User)
    newsletter = ForeignKey(Newsletter)

use the following commands to trigger the problem:

from myapp import models

team = models.Team.objects.create(name='team')

group = models.Group.objects.create(team=team)

user = models.User.objects.create(group=group)

newsletter = models.Newsletter.objects.create(name='newsletter')

subscription = models.Subscription.objects.create(user=user, newsletter=newsletter)



q = models.Subscription.objects.filter(id=1)


print q[0].newsletter.name
print q.select_related(depth=0)[0].newsletter.name

print q.select_related(depth=1)[0].newsletter.name
print q.select_related(depth=2)[0].newsletter.name

print q.select_related(depth=3)[0].newsletter.name

it's output is

newsletter
newsletter
1
team
newsletter

the third and fourth line from the output ("1" and "team") are clearly wrong, they should all give the same result.

notes:

  1. tested on mac-osx with sqlite3 and postgresql8.2 with both psycopg1 and psycopg2 (from macports) (problem was also reproducible on ubuntu-linux). package versions for the mac-osx:
    • django: 5620
    • python: 2.4.4
    • sqlite3: 3.4.0
    • pysqlite: 2.3.3
    • psycopg: 1.1.21
    • psycopg2: 2.0.5
    • postgresql: 8.2.4
  1. this is the shortest test-case i could come up with. if i make the "foreignkey-chain" shorter, the problem does not show up.
  1. after examining the sql-queries that are generated for the 'wrong' queryset, it seems that the correct sql-query is generated, so probably the sql-query-result is incorrectly mapped to the objects.
  1. this problem might be the same as the one discussed in #3623, but i am not sure, and also my test-case is smaller, so maybe easier to use.

Attachments (1)

patch.diff (4.6 KB ) - added by Gábor Farkas <gabor@…> 17 years ago.

Download all attachments as: .zip

Change History (9)

comment:1 by Gábor Farkas <gabor@…>, 17 years ago

after examining the code more, it seems that i was wrong with what i wrote in note 3.

we can think like this:

i specified depth=1. that means, it should query the data from the Subscriber model (because that's the model i'm querying), and also data from the User and the Newsletter models.

but if i examine the generated sql, it also contains the group-data. the sql-query starts with:

SELECT-
"main_subscription"."id",
"main_subscription"."user_id",
"main_subscription"."newsletter_id",
"main_user"."id",
"main_user"."group_id",
"main_group"."id",
"main_group"."team_id",
"main_newsletter"."id",
"main_newsletter"."name"

and this is already wrong, because after this query, django is creating the objects for this data,
sequentially 'consuming' the data returned by the sql-query.

so it creates the Subscription object using the first 3 field-data, then the User using the next 2 field-data,
and then goes on to create the Newsletter using the next 2 field-data, but that becomes wrong,
because at that position the sql contains the Group-data.

so now the question becomes:

why does django generate this sql-query?

by Gábor Farkas <gabor@…>, 17 years ago

Attachment: patch.diff added

comment:2 by Gábor Farkas <gabor@…>, 17 years ago

Has patch: set

i added a patch that fixes the problem. the patch also adds additional tests to the select-related test.

the problem was that django, when constructing the SQL query recursively,
went one level too deep.

so the sql-rows had more entries than django awaited,
so he constructed the django-objects using the incorrect data.

the reason why the testcases did not catch the bug was
that the testcase dealt only with a single "chain" of relations.

so even when the sql query contained too many fields,
the "wrong" part was never used.

but i modified the tests, so that the main object
has 2 foreignkeys now, so the problem is triggered.

comment:3 by Herbert Poul <herbert.poul@…>, 17 years ago

Cc: herbert.poul@… added

i just stumbled upon the same problem.. and came to the same conclusion (i probably should have searched the tickets before debugging the code. would have saved me some time)

it would be nice if this patch would find it's way into django trunk soon ..

thanks

comment:4 by Chris Beaven, 17 years ago

Triage Stage: UnreviewedReady for checkin

Looks good, and tests run fine

comment:5 by Malcolm Tredinnick, 17 years ago

Keywords: qs-rf added

comment:6 by Philippe Raoult, 17 years ago

Resolution: duplicate
Status: newclosed

duplicate of #3275 (#3623 too).

comment:7 by Matthias Urlichs <smurf@…>, 16 years ago

Resolution: duplicate
Status: closedreopened

Unfortunately, #3275 has issues beyond fixing this bug, so closing it here was a little premature.

The problem is however discussed further (and, one hopes, fixed) in #6018.

comment:8 by Matthias Urlichs <smurf@…>, 16 years ago

Resolution: duplicate
Status: reopenedclosed

Oops, didn't want to re-open

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