id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
25245	Incorrect query arising from using NOT-clauses & multiple relation references affected node position in Q	ris	nobody	"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."	Bug	new	Database layer (models, ORM)	1.8	Normal		exclude exclude manytomany Q order		Accepted	0	0	0	0	0	0