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
33808	Specific subquery produces wrong SQL (error 500)	Fabio Zoratti	nobody	"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"	Bug	closed	Database layer (models, ORM)	3.2	Normal	duplicate	database, count, query, orm		Unreviewed	1	0	0	0	0	0