#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) |
Severity: Normal | Resolution:
Keywords: exclude exclude | Triage Stage:
manytomany Q order | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by ris):
* needs_docs: => 0
* needs_tests: => 0
* needs_better_patch: => 0
Old description:
> 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.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25245#comment:1>
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/061.28265c6a36faaa6ec4de3175c27a3aec%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
