Subquery does not respect explicit __exact

[john-parton]

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.

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

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:

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?

[Tim Graham]

If you expect the query to return one result, why not use .get() instead of .filter(). Also couldn't you restructure your example more simply as Parent.objects.filter(parent__slug='test'). I guess some documentation could at least be updated.

[Thomas C]

It looks like the ORM automatically considers that you are doing an __in lookup when you give a QuerySet as the right-hand side of a filter on a related field (see ​https://github.com/django/django/blob/bbc3505ef81768aa2afac8f73e6d45b5e8000c55/django/db/models/fields/related.py#L1607).

For instance:

Author.objects.filter(item__startswith=Item.objects.all())
[<Author: a1>, <Author: a2>, <Author: a2>, <Author: a4>]

Maybe it would be more consistent if the ORM raised some error instead ?

That being said, I agree that .get() is better when you expect a single result.

[Tim Graham]

Implicit __in lookups are removed in Django 1.9 (#25284) so this look like it's fixed. The idea to raise an error as proposed in comment 2 is tracked in #25298.

[john-parton]

Thanks for the feedback.

For anyone who might come back to this ticket at a later date:

The reason someone might not want to use .get() is because it executes another SQL query.

Parent.objects.filter(parent__slug='test') is not the same query. It uses a join instead of a subquery, and might have different performance characteristics. Often I refactor a SQL query looking at the output of EXPLAIN, and then try to recreate the query using the ORM.

[Shai Berger]

Replying to john-parton:

Parent.objects.filter(parent__slug='test') is not the same query. It uses a join instead of a subquery, and might have different performance characteristics. Often I refactor a SQL query looking at the output of EXPLAIN, and then try to recreate the query using the ORM.

Whether a specific ORM expression uses a join or a subquery is an implementation detail which you should not count on; these things are allowed to change (and have changed in the past) without notice, so long as the semantics of the query is preserved. The performance characteristics of a given SQL query, as well, may change between different databases and even between versions of the same database product. If your requirements really dictate that you use one form rather than the other, you should be using raw SQL.

[john-parton]

I think with the release of version 1.11, it is now possible to do this without using raw SQL.

​https://docs.djangoproject.com/en/1.11/releases/1.11/#subquery-expressions