Regression in nested __in subquery lookups when using to_field.

[Kristian Klette]

Given the following models:

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:

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:

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:

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.tiainen@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.kaariainen@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.

[Simon Charette]

​PR.

Kristian, could you confirm the proposed patch solves your issue? Thanks.

[Simon Charette <charette.s@…>]

In 8ef35468:

Fixed #28101 -- Fixed a regression with nested in subquery lookups and to_field.

Thanks Kristian Klette for the report and Tim for the help.

[Simon Charette <charette.s@…>]

In 0ad16934:

[1.11.x] Fixed #28101 -- Fixed a regression with nested in subquery lookups and to_field.

Thanks Kristian Klette for the report and Tim for the help.

Backport of 8ef35468b660e1c25af67a8299202b8bc108679f from master