Wrong results when using two annotate expressions concurrently

Viewed 606

Assume the following tables:

class Table1(models.Model): 
    Column1 = models.IntegerField()
class Table2(models.Model):  
    Column2 = models.IntegerField()
class Table3(models.Model):  
    Table1 = models.ForeignKey(Table1, null=False, on_delete=models.CASCADE)
    Table2 = models.ForeignKey(Table2, null=False, on_delete=models.CASCADE)
    Column3 = models.IntegerField()
class Table4(models.Model):  
    Table1 = models.ForeignKey(Table1, null=False, on_delete=models.CASCADE)
    Table3 = models.ForeignKey(Table3, null=False, on_delete=models.CASCADE)
    Column4 = models.IntegerField()

This annotate expression returns right answer:

print(Table1.objects.annotate(Exp1=Sum(
            Case(
                When(table3__Table2__Column2__in=[2, 3],
                     then=F('table3__Column3')),
                default=Value(0)
            ),
    )).values('Exp1'))

That is:

<QuerySet [{'Exp1': 96}]>

And I need to define another annotate expression as below:

print(Table1.objects.annotate(Exp2=Sum(
            Case(
                When(table4__Table3__Table2__Column2=3,
                     then=F('table4__Column4')),
                default=Value(0)
            ),
      )).values('Exp2'))

Again the result is correct:

<QuerySet [{'Exp2': 0}]>

Finally, I want to combine these two in one command:

print(Table1.objects.annotate(Exp1=Sum(
            Case(
                When(table3__Table2__Column2__in=[2, 3],
                     then=F('table3__Column3')),
                default=Value(0)
            ),
    ), Exp2=Sum(
            Case(
                When(table4__Table3__Table2__Column2=3,
                     then=F('table4__Column4')),
                default=Value(0)
            ),
    )).values('Exp1', 'Exp2'))

But unfortunately the result is not correct:

<QuerySet [{'Exp2': 0, 'Exp1': 480}]>
2 Answers
Related