QuerySet.filter(related_id=queryset) no longer does implicit __in lookup

[Richard Eames]

Consider the following:

Model.objects.filter(related_id = RelatedModel.objects.all())

In django 1.8, the resulting query contains "related_id IN (SELECT id FROM ...)"
In django 1.9, the "IN" is changed to an "=", which causes the query to break in MySql and Postgres.

Is this an intentional change? If so, it needs to be documented as a breaking/backwards in-compatible change.

Test case:

class Egg(models.Model):
  class Meta:
    app_label = 'spam'
  value = models.IntegerField()

class Spam(models.Model):
  class Meta:
    app_label = 'spam'
  egg = models.ForeignKey(Egg)

class TestAssign(TestCase):
  def test_subquery(self):
    egg1 = Egg.objects.create(value=1)
    egg11 = Egg.objects.create(value=1)

    Spam.objects.create(egg=egg1)
    Spam.objects.create(egg=egg11)

    eggs1=Egg.objects.filter(value=1)
    q = Spam.objects.filter(egg__in=eggs1)
    self.assertEqual(2, len(list(q)))
                

[Tim Graham]

I couldn't reproduce the behavior you describe using your models and tests, or with the attached test for Django's test suite.

[Richard Eames]

Oh, need to remove the "_ _in".
This line needs to change:
q = Spam.objects.filter(egg__in=eggs1)
To:
q = Spam.objects.filter(egg=eggs1)

And in the patch:

+        self.assertEqual(len(Annotation.objects.filter(tag__in=qs1)), 1)

Should be:

+        self.assertEqual(len(Annotation.objects.filter(tag=qs1)), 1)

[Richard Eames]

Updated test

[Richard Eames]

Updated the test case, and verified that it fails on PostgreSQL.

[Tim Graham]

Bisected to b68212f539f206679580afbfd008e7d329c9cd31. It seems to me the implicit __in lookup probably only worked by accident but other opinions are welcome.

[Richard Eames]

I agree that it worked by accident. My opinion is that it's changed to be explicitly not allowed so the behaviour doesn't differ across backends, and also documented as a breaking change.

[Anssi Kääriäinen]

This seems to have been non-documented and non-tested feature (probably accidental, too). Changing the behavior definitely counts as a bug fix (even more for cases where doing qs.filter(related__range=otherqs) would result in an IN lookup).

Lets advertise this as loudly as we can in the release notes as a breaking change. It is going to be hard to find these cases in your code manually, but I think 99% of such cases will result in an error in 1.9, as some_id = (select id from other model) will result in an error if there are more than one id returned by the subquery.

[Tim Graham]

​PR

[Richard Eames]

What about explicitly forbidding it? Currently, it only errors in Postgresql. With Mysql it will error if the subquery returns more than one result (but not when there's only one). And with SQLite, it doesn't error at all.

[Tim Graham]

That might be feasible as a separate enhancement. After a quick look, I couldn't determine if the implementation would be trivial or not.

[Tim Graham]

Created #25298 for that follow up.

[Tim Graham <timograham@…>]

In d3bc86e:

Fixed #25284 -- Documented removal of implicit QuerySet in lookups.

[Tim Graham <timograham@…>]

In 9e4fd330:

Refs #25284 -- Corrected an obsolete implicit in lookup example.

Thanks IRC alias rpkilby for the report.

[Tim Graham <timograham@…>]

In 65cdded2:

[1.10.x] Refs #25284 -- Corrected an obsolete implicit in lookup example.

Thanks IRC alias rpkilby for the report.

Backport of 9e4fd3301d0a4e40cab8df4b51a2b04274f61da0 from master

[Tim Graham <timograham@…>]

In 82701c3c:

[1.9.x] Refs #25284 -- Corrected an obsolete implicit in lookup example.

Thanks IRC alias rpkilby for the report.

Backport of 9e4fd3301d0a4e40cab8df4b51a2b04274f61da0 from master