Targeting a subquery with __exact requires limiting to one result even when wrapped by explicit Subquery

[Mark Baird]

We have a query that worked fine in Django 5.x but during our Django 6 upgrade we ran into failing tests because the query now throws an error. We were attempting to upgrade from Django 5.2.15 to Django 6.0.6. I've read through the change logs and I'm not seeing anything that seems to indicate that this is an expected change in Django 6.

I've attempted to create a minimal code example to demonstrate the issue. Given the following models:

class Bill(models.Model):
    amount = models.DecimalField(max_digits=17, decimal_places=2, blank=True, default=0)

class PaymentRequest(models.Model):
    bill = models.ForeignKey(Bill, on_delete=models.CASCADE, related_name="payment_requests")
    date_sent = models.DateField(blank=True, null=True)

class PartialPayment(models.Model):
    payment_request = models.ForeignKey(PaymentRequest, on_delete=models.CASCADE, related_name="partial_payments")
    amount = models.DecimalField(max_digits=17, decimal_places=2, blank=True, default=0)
    date_received = models.DateField(blank=True, null=True)

This test throws an exception in Django 6, while it worked fine in Django 5:

class BillPaymentsTests(TestCase):
    def test_query_unpaid_bills(self):
        bill = models.Bill(amount=100.00)
        payment_request = models.PaymentRequest(bill=bill)
        models.PartialPayment(payment_request=payment_request, amount=50.00)
        models.PartialPayment(payment_request=payment_request, amount=50.00)

        subquery = (models.PaymentRequest.objects
                    .filter(bill=OuterRef("pk")).values("bill")
                    .annotate(total_paid=Sum("partial_payments__amount"))
                    .values("total_paid"))
        unpaid_bills = models.Bill.objects.annotate(total_paid=Subquery(subquery)).exclude(amount=F("total_paid"))

        self.assertNotIn(bill, unpaid_bills)

The exception thrown is:

ValueError: The QuerySet value for an exact lookup must be limited to one result using slicing.

On the line:

unpaid_bills = models.Bill.objects.annotate(total_paid=Subquery(subquery)).exclude(amount=F("total_paid"))

I recognize that I could simplify this code to remove the subquery, but please understand this is just a minimal example to demonstrate what appears to be a regression bug in Django 6. The actual code in my application is much more complicated and the subquery is needed.

---

I did discover a work-around, by simply swapping the fields in the exclude() condition, like so:

unpaid_bills = models.Bill.objects.annotate(total_paid=Subquery(subquery)).exclude(total_paid=F("amount"))

So we were able to get past this issue and complete our Django 6 upgrade, but I wanted to log this bug so the Django team is aware of the issue.

[Jacob Walls]

Thanks for the report, we appreciate upgrade feedback.

That said, I think this is invalid. Notice you get the same error on Django 5.2 if you remove the needless explicit Subquery wrapper, e.g. total_paid=subquery. The fact that you could use an explicit wrapper to evade the cardinality check is a bug we fixed in #36210, and indeed we had to update some of our own tests to comply with the stricter test, see de7bb7eab84dc53a7117127ad8eec44970efc509.

de7bb7eab84dc53a7117127ad8eec44970efc509 also shows what I think you need to tweak: you should add [:1] to your subquery to slice only the first result. If I am reading your query correctly, you are joining to a primary key in a way that will ensure there is only one result, but the ORM has no way of knowing that, so here you will have to be explicit.

[Mark Baird]

I see. The addition of [:1] in the subquery does fix it, thanks for that. So even if this isn't a bug, I still think the breaking change should be mentioned in the Django changelog so people running into this can see that it is an intended change? The ticket you reference seems to be specifically for composite primary keys, which I'm not using here at all, so it is certainly not obvious to me that the change in that ticket would have caused this change in behavior.

[Jacob Walls]

In this case the error message is clear ("an exact lookup must be limited to one result"), so I'm not sure a release note would provide additional useful information. It would be a different situation if this were a breaking change, but Django is just behaving as ​documented, and in general we don't document bugfixes that bring Django's behavior in greater alignment with its docs.