#31214: ORM query with Count(Subquery(...)) produces invalid SQL with Django
3.0.
-------------------------------------+-------------------------------------
Reporter: AlexShein | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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)
--
Ticket URL: <https://code.djangoproject.com/ticket/31214>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.90a21013fd08bcf196a52372a765bd34%40djangoproject.com.
