#26515: trim_joins bug with nested related models using ForeignObject
-------------------------------------+-------------------------------------
Reporter: ornoone | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) | Keywords: ForeignObject,
Severity: Normal | trim_join, orm
Triage Stage: Unreviewed | Has patch: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
the order in which we declare the `from_fields` and `to_fields` does
matter if we make a nested lookup.
it is clear that the fields in from_fields and to_fields must be on the
same orders, but if 3 models use ForeignObject to links themselves, and
the fields are the sames on all 3 models, the order in which they are
declared in both fields must be the same.
a example app that can trigger the bug follow :
{{{#!python
class Address(models.Model):
company = models.CharField(max_length=1)
tiers_id = models.IntegerField()
city = models.CharField(max_length=255)
postcode = models.CharField(max_length=32)
class Meta(object):
unique_together = [
("company", "tiers_id"),
]
class Customer(models.Model):
company = models.CharField(max_length=1)
customer_id = models.IntegerField()
name = models.CharField(max_length=255)
address = ForeignObject(
Address, on_delete=CASCADE, null=True,
from_fields=["customer_id", "company"],
to_fields=["tiers_id", "company"]
)
class Meta(object):
unique_together = [
("company", "customer_id"),
]
class Contact(models.Model):
company_code = models.CharField(max_length=1)
customer_code = models.IntegerField()
surname = models.CharField(max_length=255)
# virtual field
customer = ForeignObject(
Customer, on_delete=CASCADE, related_name='contacts',
# not the same order as for Customer -> address which is
(customer, company)
to_fields=["company", "customer_id"],
from_fields=["company_code", "customer_code"]
# with same order as for Customer, the bug does not trigger
# to_fields = ["customer_id", "company"],
# from_fields = ["customer_code", "company_code"]
)
class PhoneNumber(models.Model):
num = models.CharField(max_length=32)
type_number = models.IntegerField()
contact = models.ForeignKey(Contact, on_delete=CASCADE,
related_name='phonenumbers')
}}}
with this models.py, the different orders of the fields Contact.customer
can break all query like
`PhoneNumber.objects.filter(contact__customer__address=a)`.(see comment in
Customer)
I found the problem is in django.db.models.query.Query.trim_joins line
1444 on release 1.9.5
{{{#!python3
targets = tuple(r[0] for r in info.join_field.related_fields if
r[1].column in cur_targets)
}}}
we see that the new targets is created using the previous used targets,
but the order of the previous target is not kept, and the order of
to_fields is used to define the new targets.
this lead to a query like :
{{{#!sql
SELECT "buggyapp_phonenumber"."id", "buggyapp_phonenumber"."num",
"buggyapp_phonenumber"."type_number", "buggyapp_phonenumber"."contact_id"
FROM "buggyapp_phonenumber" INNER JOIN "buggyapp_contact" ON
("buggyapp_phonenumber"."contact_id" = "buggyapp_contact"."id") WHERE
("buggyapp_contact"."company_code" = 10 AND
"buggyapp_contact"."customer_code" = a)
}}}
instead of
{{{#!sql
SELECT "buggyapp_phonenumber"."id", "buggyapp_phonenumber"."num",
"buggyapp_phonenumber"."type_number", "buggyapp_phonenumber"."contact_id"
FROM "buggyapp_phonenumber" INNER JOIN "buggyapp_contact" ON
("buggyapp_phonenumber"."contact_id" = "buggyapp_contact"."id") WHERE
("buggyapp_contact"."customer_code" = 10 AND
"buggyapp_contact"."company_code" = a)
}}}
the where part have the values order kept, but the fields order is
inconsistent and it end with customer_code = 'a' instead of 10.
the attached files contains a basic app that trigger the bug, and a patch
in trim_query that will build the new targets with same order as the
previous.
the patch is made for django 1.9.5
--
Ticket URL: <https://code.djangoproject.com/ticket/26515>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.63c7129d388d76d733ef91e65ef400ef%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
