select_related + depth gives wrong result

[Gábor Farkas <gabor@…>]

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

2. this is the shortest test-case i could come up with. if i make the "foreignkey-chain" shorter, the problem does not show up.

3. 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.

4. 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.

[Gábor Farkas <gabor@…>]

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?

[Gábor Farkas <gabor@…>]

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.

[Herbert Poul <herbert.poul@…>]

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

[Chris Beaven]

Looks good, and tests run fine

[Philippe Raoult]

duplicate of #3275 (#3623 too).

[Matthias Urlichs <smurf@…>]

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.

[Matthias Urlichs <smurf@…>]

Oops, didn't want to re-open