Opened 3 years ago

Last modified 3 years ago

#25245 new Bug

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

Reported by: ris Owned by: nobody
Component: Database layer (models, ORM) Version: 1.8
Severity: Normal Keywords: exclude exclude manytomany Q order
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (2)

comment:1 Changed 3 years ago by ris

Description: modified (diff)

comment:2 Changed 3 years ago by Tim Graham

Triage Stage: UnreviewedAccepted
Note: See TracTickets for help on using tickets.
Back to Top