#25245: Incorrect query arising from using NOT-clauses & multiple relation
references affected node position in Q
-------------------------------------+-------------------------------------
Reporter: ris | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) | Keywords: exclude exclude
Severity: Normal | manytomany Q order
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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 PR4385 fixes this case.
--
Ticket URL: <https://code.djangoproject.com/ticket/25245>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/046.4ef8e21e17b27364497cc4f7927b04d5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
