#28101: Wrong field used for sub query lookup on nested query using to_field
ForeignKey
-------------------------------------+-------------------------------------
Reporter: Kristian Klette | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Kristian Klette:
Old description:
> Given the following models:
>
> {{{#!python
> class CustomerUser(models.Model):
> name = models.CharField(max_length=100)
>
> class Customer(models.Model):
> id = models.UUIDField(primary_key=True, default=uuid.uuid4,
> editable=False)
> customer_number = models.CharField(unique=True, max_length=100)
> users = models.ManyToManyField(CustomerUser)
>
> class CustomerProduct(models.Model):
> id = models.UUIDField(primary_key=True, default=uuid.uuid4,
> editable=False)
> customer = models.ForeignKey(
> Customer, to_field='customer_number', on_delete=models.PROTECT)
> name = models.CharField(max_length=100)
>
> class CustomerPayment(models.Model):
> id = models.UUIDField(primary_key=True, default=uuid.uuid4,
> editable=False)
> product = models.ForeignKey(CustomerProduct,
> on_delete=models.PROTECT)
> }}}
>
> And the tests:
>
> {{{#!python
> class SubQueryM2MWithToFieldFkTests(TestCase):
> def test_regression(self):
> user = CustomerUser.objects.create(name='my user')
> customer = Customer.objects.create(customer_number='A123')
> customer.users.add(user)
> product = CustomerProduct.objects.create(customer=customer,
> name='Foo')
> payment = CustomerPayment.objects.create(product=product)
>
> products = CustomerProduct.objects.filter(
> customer__in=user.customer_set.all())
>
> result = CustomerPayment.objects.filter(product__in=products)
> self.assertEquals(result.count(), 1)
> self.assertEquals(list(result), [payment])
> }}}
>
> One should get the query:
> {{{#!sql
> SELECT "queries_customerpayment"."id",
> "queries_customerpayment"."product_id"
> FROM "queries_customerpayment"
> WHERE "queries_customerpayment"."product_id" IN (
> SELECT V0."id" AS Col1 FROM "queries_customerproduct" V0
> WHERE V0."customer_id" IN (
> SELECT U0."customer_number" AS Col1
> FROM "queries_customer" U0 INNER JOIN "queries_customer_users" U1 ON
> (U0."id" = U1."customer_id")
> WHERE U1."customeruser_id" = 1))
> }}}
>
> But at least 1.11 and master generates:
>
> {{{#!sql
> SELECT "queries_customerpayment"."id",
> "queries_customerpayment"."product_id"
> FROM "queries_customerpayment"
> WHERE "queries_customerpayment"."product_id" IN (
> SELECT V0."id" AS Col1 FROM "queries_customerproduct" V0
> WHERE V0."customer_id" IN (
> SELECT U0."id" AS Col1 FROM "queries_customer" U0 INNER JOIN
> "queries_customer_users" U1 ON (U0."id" = U1."customer_id")
> WHERE U1."customeruser_id" = 1))
> }}}
>
> which attempts to match the wrong `Customer`-field to
> `CustomerProduct.customer_id`.
>
> I tried to figure out what was going on and the test passes if I add
> `print(self)` to `Queryset._prepare_as_filter_value` in the forced pk
> block. Quite arbitrary placement of the print, and probably not related
> to `_prepare_as_filter_value`. My guess would be that the evaluation of
> the inner queryset fixes some field matching some other place - but I'm
> way out of my depth here :)
>
> The tests were added directly to `tests/queries/tests.py` and the models
> to `tests/queries/models.py` and run using Django's test suite.
>
> Might be related: [https://code.djangoproject.com/ticket/26196]
New description:
Given the following models:
{{{#!python
class CustomerUser(models.Model):
name = models.CharField(max_length=100)
class Customer(models.Model):
id = models.UUIDField(primary_key=True, default=uuid.uuid4,
editable=False)
customer_number = models.CharField(unique=True, max_length=100)
users = models.ManyToManyField(CustomerUser)
class CustomerProduct(models.Model):
id = models.UUIDField(primary_key=True, default=uuid.uuid4,
editable=False)
customer = models.ForeignKey(
Customer, to_field='customer_number', on_delete=models.PROTECT)
name = models.CharField(max_length=100)
class CustomerPayment(models.Model):
id = models.UUIDField(primary_key=True, default=uuid.uuid4,
editable=False)
product = models.ForeignKey(CustomerProduct, on_delete=models.PROTECT)
}}}
And the tests:
{{{#!python
class SubQueryM2MWithToFieldFkTests(TestCase):
def test_regression(self):
user = CustomerUser.objects.create(name='my user')
customer = Customer.objects.create(customer_number='A123')
customer.users.add(user)
product = CustomerProduct.objects.create(customer=customer,
name='Foo')
payment = CustomerPayment.objects.create(product=product)
products = CustomerProduct.objects.filter(
customer__in=user.customer_set.all())
result = CustomerPayment.objects.filter(product__in=products)
self.assertEquals(result.count(), 1)
self.assertEquals(list(result), [payment])
}}}
One should get the query:
{{{#!sql
SELECT "queries_customerpayment"."id",
"queries_customerpayment"."product_id"
FROM "queries_customerpayment"
WHERE "queries_customerpayment"."product_id" IN (
SELECT V0."id" AS Col1 FROM "queries_customerproduct" V0
WHERE V0."customer_id" IN (
SELECT U0."customer_number" AS Col1
FROM "queries_customer" U0 INNER JOIN "queries_customer_users" U1 ON
(U0."id" = U1."customer_id")
WHERE U1."customeruser_id" = 1))
}}}
But at least 1.11 and master generates:
{{{#!sql
SELECT "queries_customerpayment"."id",
"queries_customerpayment"."product_id"
FROM "queries_customerpayment"
WHERE "queries_customerpayment"."product_id" IN (
SELECT V0."id" AS Col1 FROM "queries_customerproduct" V0
WHERE V0."customer_id" IN (
SELECT U0."id" AS Col1 FROM "queries_customer" U0 INNER JOIN
"queries_customer_users" U1 ON (U0."id" = U1."customer_id")
WHERE U1."customeruser_id" = 1))
}}}
which attempts to match the wrong `Customer`-field to
`CustomerProduct.customer_id`.
I tried to figure out what was going on and the test passes if I add
`print(self)` to `Queryset._prepare_as_filter_value` in the forced pk
block. Quite arbitrary placement of the print, and probably not related to
`_prepare_as_filter_value`. My guess would be that the evaluation of the
inner queryset fixes some field matching some other place - but I'm way
out of my depth here :)
The tests were added directly to `tests/queries/tests.py` and the models
to `tests/queries/models.py` and run using Django's test suite.
Might be related: [https://code.djangoproject.com/ticket/26196]
Running git bisect found the following change as the first bad one, so
seems like a regression.
{{{
7a2c27112d1f804f75191e9bf45a96a89318a684 is the first bad commit
commit 7a2c27112d1f804f75191e9bf45a96a89318a684
Author: Jani Tiainen <[email protected]>
Date: Wed Aug 31 21:16:39 2016 +0300
Fixed #27159 -- Prevented pickling a query with an __in=inner_qs
lookup from evaluating inner_qs.
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28101#comment:1>
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/064.af326e6119da571aa4e36d5d1f6305ec%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
