#28101: #26196 regression - __in lookups does not honor to_field
-------------------------------------+-------------------------------------
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]
>
> 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 <jani.tiai...@tintti.net>
> 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.
> }}}
>
> while it was first fixed in:
> {{{
> commit 46ecfb9b3a11a360724e3375ba78c33c46d6a992
> Author: Anssi Kääriäinen <anssi.kaariai...@thl.fi>
> Date: Thu Feb 11 08:39:37 2016 +0200
>
> Fixed #26196 -- Made sure __in lookups use to_field as default.
>
> Thanks Simon Charette for the test.
>
> }}}
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. This causes the inner_qs to be evaluated, and thus simulating the
the behavior before 7a2c27112d1f804f75191e9bf45a96a89318a684 was applied.
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.
Related: #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 <jani.tiai...@tintti.net>
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.
}}}
while it was first fixed in:
{{{
commit 46ecfb9b3a11a360724e3375ba78c33c46d6a992
Author: Anssi Kääriäinen <anssi.kaariai...@thl.fi>
Date: Thu Feb 11 08:39:37 2016 +0200
Fixed #26196 -- Made sure __in lookups use to_field as default.
Thanks Simon Charette for the test.
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28101#comment:4>
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/064.ebc46cb392603721dc30991b28a4a539%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
