Generated sql for "or"-ing two filters over multiple joins is wrong
|Reported by:||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||1.2|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
In the following data model:
FirstRelated <- m2m -> BaseModel <- m2m -> SecondRelated <- fk <- ThirdRelated
I am doing a filter on the BaseModel with an "or" between:
- a condition on the ThirdRelated
- a condition on the FirstRelated
BaseModel.objects.filter(Q(seconds__thirds__condition=True) | Q(firsts__condition=True)).
Each of the two filters behaves correctly when executed, but when "or"-ed some records are returned more than once.
The problem is related to the generated SQL, because 'or'-ing two conditions make the joins optional, but then if a condition fails a record can be returned because of the other condition.
The solution probably would be to include a DISTINCT() clause when 'or'-ing the conditions.
Here (and attached, too) you have the models and the tests for a failing example. I leave out the
wrong_or/settings.py because they are all standard.
from django.db import models class First(models.Model): condition = models.BooleanField() class Second(models.Model): pass class BaseModel(models.Model): firsts = models.ManyToManyField(First) seconds = models.ManyToManyField(Second) class Third(models.Model): second = models.ForeignKey(Second, related_name='thirds') condition = models.BooleanField()
from django.db import models from django.test import TestCase from wrong_or.wrong.models import BaseModel, First, Second, Third class SimpleTest(TestCase): def test_wrong(self): bo = BaseModel.objects.create() f = First.objects.create(condition=True) bo.firsts.add(f) s1 = Second.objects.create() t1 = Third.objects.create(second=s1, condition=False) s2 = Second.objects.create() t2 = Third.objects.create(second=s2, condition=False) bo.seconds.add(s1, s2) q1 = models.Q(firsts__condition=True) # match the only BaseModel q2 = models.Q(seconds__thirds__condition=True) # match nothing self.assertTrue(BaseModel.objects.filter(q1).exists()) self.assertEqual(1, BaseModel.objects.filter(q1).count()) self.assertFalse(BaseModel.objects.filter(q2).exists()) # A filter should not duplicate records. # This assertion will fail, because there are two copies of # the 'bo' record. self.assertEqual(1, BaseModel.objects.filter(q1|q2).count())