Multiple ForeignKeys to the same model produces wrong SQL statements.

[Matthias Kestenholz]

Take the following models:

from django.db import models

class Category(models.Model):
	name = models.CharField(max_length=20)

class Record(models.Model):
	category = models.ForeignKey(Category)

class Relation(models.Model):
	left = models.ForeignKey(Record, related_name='left_set')
	right = models.ForeignKey(Record, related_name='right_set')

Some example data:

from testcase.models import *
c1 = Category.objects.create(name='First')
c2 = Category.objects.create(name='Second')

r1 = Record.objects.create(category=c1)
r2 = Record.objects.create(category=c1)
r3 = Record.objects.create(category=c2)
r4 = Record.objects.create(category=c2)

Relation.objects.create(left=r1, right=r2)
Relation.objects.create(left=r3, right=r4)
Relation.objects.create(left=r1, right=r3)

The following query:

Relation.objects.filter(left__category__name__in=['Second'], right__category__name__in=['First'])

produces this SQL statement:

SELECT "testcase_relation"."id", "testcase_relation"."left_id", "testcase_relation"."right_id" FROM "testcase_relation"
INNER JOIN "testcase_record" ON ("testcase_relation"."right_id" = "testcase_record"."id")
INNER JOIN "testcase_category" ON ("testcase_record"."category_id" = "testcase_category"."id")
INNER JOIN "testcase_record" T4 ON ("testcase_relation"."left_id" = T4."id")
WHERE "testcase_category"."name" IN (First) AND "testcase_category"."name" IN (Second)

which is obviously wrong.

Note that the first WHERE clause should be T4.name IN (First), not "testcase_category"."name" IN (First). Also note the missing second join on testcase_category.

[ElliottM]

This is very strongly related to #7110, and these two should probably be treated as one ticket, because i've noticed more problems than just the select_related() thing I mentioned in my ticket. I was actually planning on renaming it to refer to problems with multiple FKs in general, but if this is already here then i guess we can just use this one.

[oyvind]

Confirmed

Should it not be?

SELECT "testcase_relation"."id", "testcase_relation"."left_id", "testcase_relation"."right_id" FROM "testcase_relation"
INNER JOIN "testcase_record" ON ("testcase_relation"."right_id" = "testcase_record"."id")
INNER JOIN "testcase_category" ON ("testcase_record"."category_id" = "testcase_category"."id")
INNER JOIN "testcase_record" T4 ON ("testcase_relation"."left_id" = T4."id")
INNER JOIN "testcase_category" T5 ON ("T4"."category_id" = T5."id")
WHERE T5."name" IN (First) AND "testcase_category"."name" IN (Second)

[oyvind]

Seems to handle the first level only, something goes wrong and causes reuse of the same alias in the second level join.

[oyvind]

With select_related() the correct T5 join is added, but the where uses the wrong alias.

[oyvind]

Test queries for example models.

""" Missing T5 join, wrong where """
Relation.objects.filter(left__category__name__in=['Second'], right__category__name__in=['First']).query.as_sql()

""" With T5 join, but wrong where """
Relation.objects.filter(left__category__name__in=['Second'], right__category__name__in=['First']).select_related().query.as_sql()

[oyvind]

Adds a test for this issue to many_to_one modeltest.

[Jacob]

#7330 was a duplicate.

[jbronn]

I have a hunch this bug may be related to the problems in #7277. Both of them deal with multiple joins on the same column.

[jbronn]

I think this is the same problem as in #7290.

[oyvind]

Better patch for modeltest

[Malcolm Tredinnick]

(In [7778]) Fixed handling of multiple fields in a model pointing to the same related model.

Thanks to ElliotM, mk and oyvind for some excellent test cases for this. Fixed #7110, #7125.

[Jacob]

Milestone 1.0 deleted