#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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
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.
