[Django] #25245: Incorrect query arising from using NOT-clauses & multiple relation references affected node position in Q

[Django]

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

[Django]

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

New description:

{{{
from django.db import models

Would like to be sure a fix for #14645 fixes this case.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/25245#comment:1>

[Django]

#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: Accepted
manytomany Q order |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by timgraham):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/25245#comment:2>

[Django]

#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: Accepted
manytomany Q order |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

#26368 was a duplicate.

--
Ticket URL: <https://code.djangoproject.com/ticket/25245#comment:3>

[Django]

#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: Accepted
manytomany Q order |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

#34538 was a duplicate.

--
Ticket URL: <https://code.djangoproject.com/ticket/25245#comment:4>