Opened 9 years ago
Last modified 18 months ago
#25245 new Bug
Incorrect query arising from using NOT-clauses & multiple relation references affected node position in Q
Reported by: | ris | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.8 |
Severity: | Normal | Keywords: | exclude exclude manytomany Q order |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
This may (or may not) be related to the bug being fixed in PR4385 (https://github.com/django/django/pull/4385) and/or #14645, however even if it is I think this displays an interesting facet to this.
The order Q clauses are specified in the Q expression will affect the (in)correctness of the generated query.
Using django 1.8.3 example models.py:
from django.db import models class ModelA ( models.Model ): pass class ModelB ( models.Model ): a = models.ForeignKey ( ModelA ) field_f = models.IntegerField () field_g = models.IntegerField ()
Specify the query one way around:
>>> x = ModelA.objects.filter ( ( Q ( modelb__field_f = 3 ) & Q ( modelb__field_g__gte = 50 ) ) | ~Q ( modelb__field_f = 3 ) ).distinct () >>> str ( x.query ) 'SELECT DISTINCT "dummy_modela"."id" FROM "dummy_modela" LEFT OUTER JOIN "dummy_modelb" ON ( "dummy_modela"."id" = "dummy_modelb"."a_id" ) WHERE (("dummy_modelb"."field_f" = 3 AND "dummy_modelb"."field_g" >= 50) OR NOT ("dummy_modela"."id" IN (SELECT U1."a_id" AS Col1 FROM "dummy_modelb" U1 WHERE (U1."field_f" = 3 AND U1."id" = ("dummy_modelb"."id")))))'
Generates one piece of SQL. Specify it in a different order:
>>> y = ModelA.objects.filter ( (~Q ( modelb__field_f = 3 )) | ( Q ( modelb__field_f = 3 ) & Q ( modelb__field_g__gte = 50 ) ) ).distinct () >>> str ( y.query ) 'SELECT DISTINCT "dummy_modela"."id" FROM "dummy_modela" LEFT OUTER JOIN "dummy_modelb" ON ( "dummy_modela"."id" = "dummy_modelb"."a_id" ) WHERE (NOT ("dummy_modela"."id" IN (SELECT U1."a_id" AS Col1 FROM "dummy_modelb" U1 WHERE U1."field_f" = 3)) OR ("dummy_modelb"."field_f" = 3 AND "dummy_modelb"."field_g" >= 50))'
Generates quite different SQL, which returns different results.
Would like to be sure a fix for #14645 fixes this case.
Change History (4)
comment:1 by , 9 years ago
Description: | modified (diff) |
---|
comment:2 by , 9 years ago
Triage Stage: | Unreviewed → Accepted |
---|
#26368 was a duplicate.