id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
23518	Subquery does not respect explicit __exact	john-parton	nobody	"I think it should be possible to use a subquery as an expression with `__exact`.

It appears that when you perform a subquery, the ORM includes an **implicit** `__in`. This is fine, because I'm sure that covers most use cases, but if you try to provide an **explicit** `__exact`, the ORM disregards it.


{{{#!python
# models.py 
from django.db import models
    
class Child(models.Model):
    parent = models.ForeignKey('Parent')
    
class Parent(models.Model):
    slug = models.SlugField(unique=True)
}}}

{{{#!python
In [1]: from model_test.models import Child, Parent

In [2]: parent = Parent(slug='test')

In [3]: parent.save()

In [4]: child = Child(parent=parent)

In [5]: child.save()

In [6]: children = Child.objects.filter(parent_id=Parent.objects.filter(slug='test'))

In [7]: print(children.query)
SELECT ""model_test_child"".""id"", ""model_test_child"".""parent_id"" FROM ""model_test_child"" WHERE ""model_test_child"".""parent_id"" IN (SELECT U0.""id"" FROM ""model_test_parent"" U0 WHERE U0.""slug"" = test)

In [8]: children = Child.objects.filter(parent_id__exact=Parent.objects.filter(slug='test'))

In [9]: print(children.query)
SELECT ""model_test_child"".""id"", ""model_test_child"".""parent_id"" FROM ""model_test_child"" WHERE ""model_test_child"".""parent_id"" IN (SELECT U0.""id"" FROM ""model_test_parent"" U0 WHERE U0.""slug"" = test)
}}}


The expected output of the 9th command is:

{{{#!sql
SELECT ""model_test_child"".""id"", ""model_test_child"".""parent_id"" FROM ""model_test_child"" WHERE ""model_test_child"".""parent_id"" = (SELECT U0.""id"" FROM ""model_test_parent"" U0 WHERE U0.""slug"" = 'test');
}}}

Obviously using `__exact` isn't always possible, but because `Parent` has a unique slug, we can guarantee that the subquery returns one or zero rows. Alternative, we could have added a `LIMIT 1` (or a `[:1]` with the ORM) to ensure that it has exactly one or zero rows.

The most simple way to do error handling would to just wrap the `ProgrammingError: more than one row returned by a subquery used as an expression` which would get raised by the connection.

At the very least, shouldn't the `__exact` transform raise some error or issue some warning that it does nothing?

Thoughts?


"	Cleanup/optimization	closed	Database layer (models, ORM)	1.7	Normal	fixed	subquery subselect exact		Accepted	0	0	0	0	0	0