#33808: Specific subquery produces wrong SQL (error 500)
-------------------------------------+-------------------------------------
Reporter: Fabio Zoratti | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: database, count, | Triage Stage:
query, orm | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Fabio Zoratti):
* has_patch: 0 => 1
Old description:
> 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
> }}}
New description:
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
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33808#comment:4>
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018197b145c0-a0bce247-d4a8-43f0-8d6b-9abb16027c22-000000%40eu-central-1.amazonses.com.
