Incorrect query arising from using NOT-clauses & multiple relation references affected node position in Q

[ris]

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.

[Mariusz Felisiak]

#26368 was a duplicate.

[Mariusz Felisiak]

#34538 was a duplicate.