#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 [email protected].
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.
