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
31214	ORM query with Count(Subquery(...)) produces invalid SQL with Django 3.0.	Alex	nobody	"There is an ORM query in my app, which I wrote when Django 2.2 was used and it broke after migrating to 3.0: 
{{{
    Targets.objects.filter(hgnc_sym=sym_list)
            .annotate(
                num_of_drugs=Count(
                    Subquery(Drug.objects.filter(id__in=OuterRef('drugs__id'),).distinct('id').values('id')),
                    distinct=True,
                )
            )
}}}
There are two models (Targets,  Drug) with a M2M relation through targetdrug table. I am annotating each Targets record with count of related Drugs.
Django produces SQL queries that look almost the same except one brackets set.
Here is the SQL code produced by Django 2.2:
{{{
SELECT ""targets"".""hgnc_sym"",
       COUNT(DISTINCT
               (SELECT DISTINCT ON (U0.""id"") U0.""id""
                FROM ""drug"" U0
                WHERE U0.""id"" IN (""targetdrug"".""drug_id""))) AS ""num_of_drugs""
FROM ""targets""
LEFT OUTER JOIN ""targetdrug"" ON (""targets"".""id"" = ""targetdrug"".""target_id"")
WHERE ""targets"".""hgnc_sym"" IN ('ABC')
GROUP BY ""targets"".""id"";

}}}
and here is same SQL, produced by Django 3.0:
{{{
SELECT ""targets"".""hgnc_sym"",
       COUNT(DISTINCT
               (SELECT DISTINCT ON (U0.""id"") U0.""id""
                FROM ""drug"" U0
                WHERE U0.""id"" IN ""targetdrug"".""drug_id"")) AS ""num_of_drugs""
FROM ""targets""
LEFT OUTER JOIN ""targetdrug"" ON (""targets"".""id"" = ""targetdrug"".""target_id"")
WHERE ""targets"".""hgnc_sym"" IN ('ABC')
GROUP BY ""targets"".""id"";
}}}
The problem is that last SQL script misses brackets at line starting with **WHERE U0.""id"" IN ...**. 
Running that SQL results into the following error:

{{{
syntax error at or near """"targetdrug""""
LINE 5:                 WHERE U0.""id"" IN ""targetdrug"".""drug_...
}}}

**Postgres version**:
PostgreSQL 11.4 (Debian 11.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
**Django version**:
python -c 'import django; print(django.VERSION)'
(3, 0, 2, 'final', 0)"	Bug	closed	Database layer (models, ORM)	3.0	Normal	duplicate	3.0, ORM, Postgres		Unreviewed	0	0	0	0	0	0