Specific subquery produces wrong SQL (error 500)

[Fabio Zoratti]

You can find in attachment a minimal working example. I created the zip with

django-admin startproject mwe
./manage.py startapp testapp

Then I only edited the file testapp/models.py testapp/tests.py and mwe/settings.py (to add the app to the installed apps) and ran ./manage.py makemigrations testapp.

Running the tests with the usual ./manage.py test triggers the problem. I printed the query produced by django, simply adding a print in django/db/backends/sqlite/base.py. The query is the following:

SELECT DISTINCT "testapp_firstmodel"."id", 
COUNT("testapp_secondmodel_related_field2"."secondmodel_id" IN "testapp_secondmodel_related_field1"."secondmodel_id") AS "howmany" 
FROM "testapp_firstmodel" 
LEFT OUTER JOIN "testapp_secondmodel_related_field1" ON ("testapp_firstmodel"."id" = "testapp_secondmodel_related_field1"."firstmodel_id") 
LEFT OUTER JOIN "testapp_secondmodel_related_field2" ON ("testapp_firstmodel"."id" = "testapp_secondmodel_related_field2"."firstmodel_id") 
GROUP BY "testapp_firstmodel"."id" LIMIT 21

Adding a layer of parenthesis in the query inside the Count fixes the issue.

SELECT DISTINCT "testapp_firstmodel"."id", 
COUNT("testapp_secondmodel_related_field2"."secondmodel_id" IN ("testapp_secondmodel_related_field1"."secondmodel_id")) AS "howmany" 
FROM "testapp_firstmodel" 
LEFT OUTER JOIN "testapp_secondmodel_related_field1" ON ("testapp_firstmodel"."id" = "testapp_secondmodel_related_field1"."firstmodel_id") 
LEFT OUTER JOIN "testapp_secondmodel_related_field2" ON ("testapp_firstmodel"."id" = "testapp_secondmodel_related_field2"."firstmodel_id") 
GROUP BY "testapp_firstmodel"."id" LIMIT 21

This should fix the issue: ​https://github.com/django/django/pull/15798

[Fabio Zoratti]

Minimal django project reproducing the issue

[Fabio Zoratti]

I used git bisect to find the commit that broke the code. The commit is

commit 3a505c70e7b228bf1212c067a8f38271ca86ce09 (HEAD, refs/bisect/bad)
Author: Simon Charette <charette.s@gmail.com>
Date:   Wed Mar 6 01:24:41 2019 -0500

    Refs #27149, #29542 -- Simplified subquery parentheses wrapping logic.

The diff clearly shows that some parenthesis are being removed from the query

git show 3a505c70e7b228bf1212c067a8f38271ca86ce09
diff --git a/django/db/models/lookups.py b/django/db/models/lookups.py
index e78ffdf390..910291094e 100644
--- a/django/db/models/lookups.py
+++ b/django/db/models/lookups.py
@@ -89,8 +89,7 @@ class Lookup:
             value = self.apply_bilateral_transforms(value)
             value = value.resolve_expression(compiler.query)
         if hasattr(value, 'as_sql'):
-            sql, params = compiler.compile(value)
-            return '(' + sql + ')', params
+            return compiler.compile(value)
         else:
             return self.get_db_prep_lookup(value, connection)

[Mariusz Felisiak]

Thanks for the report, however #32673 is a regression in Django 3.0 reported when it was already EOL. Per our backporting policy this means it doesn't qualify for a backport to 3.2.x anymore. See ​Django’s release process for more details. Also, Django 3.2 is in extended support so it doesn't receive bugfixes anymore (except security patches).

Moreover, using the __in lookup with ManyToManyField has never been officially supported, you should use Q(sm_f1=F("sm_f2")) instead of Q(sm_f1__in=F("sm_f2")), it's a duplicate of #31135.