Re: [Django] #33808: Specific subquery produces wrong SQL (error 500)

9 views

Skip to first unread message

Django

unread,

Jun 24, 2022, 5:49:50 PM6/24/22

to django-...@googlegroups.com

#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.

Django

unread,

Jun 25, 2022, 12:36:58 AM6/25/22

to django-...@googlegroups.com

#33808: Specific subquery produces wrong SQL (error 500)
-------------------------------------+-------------------------------------
Reporter: Fabio Zoratti | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: 3.2
(models, ORM) |

Severity: Normal | Resolution: duplicate

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => duplicate

Comment:

Thanks for the report, however #32673 is a regression in Django 3.0
reported when it was already EOL. Per our backporting policy this means it
doesn't qualify for a backport to 3.2.x anymore. See
[https://docs.djangoproject.com/en/stable/internals/release-process/
Django’s release process] for more details. Also, Django 3.2 is in
extended support so it doesn't receive bugfixes anymore (except security
patches).

Moreover, using the `__in` lookup with `ManyToManyField` has never been
officially supported, you should use `Q(sm_f1=F("sm_f2"))` instead of
`Q(sm_f1__in=F("sm_f2"))`, it's a duplicate of #31135.