Code

Opened 6 years ago

Closed 6 years ago

Last modified 3 years ago

#7125 closed (fixed)

Multiple ForeignKeys to the same model produces wrong SQL statements.

Reported by: mk 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 6 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 6 years ago.
Better patch for modeltest

Download all attachments as: .zip

Change History (19)

comment:1 Changed 6 years ago by ElliottM

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 6 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 6 years ago by anonymous

  • Cc mk@… added

comment:4 Changed 6 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 6 years ago by oyvind

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

comment:6 Changed 6 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 6 years ago by oyvind

Adds a test for this issue to many_to_one modeltest.

comment:7 Changed 6 years ago by jacob

#7330 was a duplicate.

comment:8 Changed 6 years ago by jacob

  • Owner changed from nobody to jacob
  • Status changed from new to assigned

comment:9 Changed 6 years ago by jacob

  • Triage Stage changed from Unreviewed to Accepted

comment:10 Changed 6 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 6 years ago by jbronn

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

comment:12 Changed 6 years ago by gav

  • Keywords qsrf-cleanup added

comment:13 Changed 6 years ago by jacob

  • milestone set to 1.0

comment:14 Changed 6 years ago by anonymous

  • Cc omat@… added

Changed 6 years ago by oyvind

Better patch for modeltest

comment:15 Changed 6 years ago by vishwanatan

  • Cc djangoproject.com@… added

comment:16 Changed 6 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from assigned to closed

(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 3 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

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.