Opened 2 years ago

Closed 2 years ago

#33808 closed Bug (duplicate)

Specific subquery produces wrong SQL (error 500)

Reported by: Fabio Zoratti Owned by: nobody
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords: database, count, query, orm
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Fabio Zoratti)

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

Attachments (1)

mwe.zip (6.2 KB ) - added by Fabio Zoratti 2 years ago.
Minimal django project reproducing the issue

Download all attachments as: .zip

Change History (6)

by Fabio Zoratti, 2 years ago

Attachment: mwe.zip added

Minimal django project reproducing the issue

comment:1 by Fabio Zoratti, 2 years ago

I used git bisect to find the commit that broke the code. The commit is

commit 3a505c70e7b228bf1212c067a8f38271ca86ce09 (HEAD, refs/bisect/bad)
Author: Simon Charette <charette.s@gmail.com>
Date:   Wed Mar 6 01:24:41 2019 -0500

    Refs #27149, #29542 -- Simplified subquery parentheses wrapping logic.

The diff clearly shows that some parenthesis are being removed from the query

git show 3a505c70e7b228bf1212c067a8f38271ca86ce09
diff --git a/django/db/models/lookups.py b/django/db/models/lookups.py
index e78ffdf390..910291094e 100644
--- a/django/db/models/lookups.py
+++ b/django/db/models/lookups.py
@@ -89,8 +89,7 @@ class Lookup:
             value = self.apply_bilateral_transforms(value)
             value = value.resolve_expression(compiler.query)
         if hasattr(value, 'as_sql'):
-            sql, params = compiler.compile(value)
-            return '(' + sql + ')', params
+            return compiler.compile(value)
         else:
             return self.get_db_prep_lookup(value, connection)

comment:2 by Fabio Zoratti, 2 years ago

Summary: Count manytomany__in produces wrong SQLSpecific subquery produces wrong SQL

comment:3 by Fabio Zoratti, 2 years ago

Summary: Specific subquery produces wrong SQLSpecific subquery produces wrong SQL (error 500)

comment:4 by Fabio Zoratti, 2 years ago

Description: modified (diff)
Has patch: set

comment:5 by Mariusz Felisiak, 2 years ago

Resolution: duplicate
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top