Opened 9 years ago

Closed 8 years ago

Last modified 5 years ago

#7125 closed (fixed)

Multiple ForeignKeys to the same model produces wrong SQL statements.

Reported by: Matthias Kestenholz Owned by: Jacob
Component: Database layer (models, ORM) Version: master
Severity: Keywords: qsrf-cleanup wrong sql join intermediary
Cc: mk@…, cmutel@…, omat@…, djangoproject.com@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (2)

failing_many_to_one_mutiple_test.diff (2.1 KB) - added by oyvind 9 years ago.
Adds a test for this issue to many_to_one modeltest.
failing_many_to_one_mutiple_test_2.diff (1.6 KB) - added by oyvind 8 years ago.
Better patch for modeltest

Download all attachments as: .zip

Change History (19)

comment:1 Changed 9 years ago by 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.

comment:2 Changed 9 years ago by oyvind

Keywords: wrong sql join intermediary added

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)

comment:3 Changed 9 years ago by anonymous

Cc: mk@… added

comment:4 Changed 9 years ago by oyvind

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

comment:5 Changed 9 years ago by oyvind

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

comment:6 Changed 9 years ago by 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()

Changed 9 years ago by oyvind

Adds a test for this issue to many_to_one modeltest.

comment:7 Changed 9 years ago by Jacob

#7330 was a duplicate.

comment:8 Changed 9 years ago by Jacob

Owner: changed from nobody to Jacob
Status: newassigned

comment:9 Changed 9 years ago by Jacob

Triage Stage: UnreviewedAccepted

comment:10 Changed 9 years ago by 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.

comment:11 Changed 9 years ago by jbronn

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

comment:12 Changed 9 years ago by George Vilches

Keywords: qsrf-cleanup added

comment:13 Changed 8 years ago by Jacob

milestone: 1.0

comment:14 Changed 8 years ago by anonymous

Cc: omat@… added

Changed 8 years ago by oyvind

Better patch for modeltest

comment:15 Changed 8 years ago by vishwanatan

Cc: djangoproject.com@… added

comment:16 Changed 8 years ago by Malcolm Tredinnick

Resolution: fixed
Status: assignedclosed

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

comment:11 Changed 5 years ago by Jacob

milestone: 1.0

Milestone 1.0 deleted

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