Incorrect query generated from ~Q/exclude

[kirk]

Django 2.2.12
Python 3.7.5
Postgres 10.11

I am receiving what I perceive to be an incorrect SQL query from a Django 
queryset.  I am hoping I've just done something wrong.

Here's the setup:

from django.db.models import DecimalField, DO_NOTHING, F, ForeignKey, Model, 
Q, Sum, TextField


class TableA(Model):
    name = TextField()

    class Meta:
        db_table = "tablea"


class TableB(Model):
    tablea = ForeignKey(TableA, DO_NOTHING, null=True)
    value = DecimalField(max_digits=5, decimal_places=2)

    class Meta:
        db_table = "tableb"

Here's some sample data:

insert into tablea (id, name)
select * from (values (1, 'NAME 1'), (2, 'NAME 2'), (3, 'NAME 3')) t;

insert into tableb (id, tablea_id, value)
select * from (values
    (1, 1, 0), (2, 1, 5),
    (3, null, 13),
    (4, 2, 0), (5, 2, 0),
    (6, 3, -1), (7, 3, 1)
) t;

The goal is to group names from tablea and sum values from tableb where 
value != 0.  If I were to write it purely in SQL I'd probably do something 
like this:

select   a.name, sum(b.value)
from     tablea a inner join tableb b on b.tablea_id = a.id
where    b.value != 0
group by a.name;

The answer should be:

NAME 3     0
NAME 1     5

If I write the Django queryset as one of the following:

qs = (
    TableA.objects.filter(~Q(tableb__value=0))
    .annotate(the_name=F("name"), the_sum=Sum("tableb__value"))
    .values("the_name", "the_sum")
)

qs = (
    TableA.objects.exclude(tableb__value=0)
    .annotate(the_name=F("name"), the_sum=Sum("tableb__value"))
    .values("the_name", "the_sum")
)

I get SQL that I really didn't expect:

SELECT   "tablea"."name" AS "the_name",
         SUM("tableb"."value") AS "the_sum"
FROM     "tablea"
         LEFT OUTER JOIN "tableb" ON ("tablea"."id" = "tableb"."tablea_id")
WHERE    NOT (
            "tablea"."id" IN (
                SELECT  U1."tablea_id"
                FROM    "tableb" U1
                WHERE   (U1."value" = 0 AND U1."tablea_id" IS NOT NULL)
            )
         )
GROUP BY "tablea"."id";

which returns the wrong answer:

NAME 3     0

If I rewrite the queryset as such, I get the correct answer:

qs = (
    TableA.objects.filter(Q(tableb__value__gt=0) | Q(tableb__value__lt=0))
    .annotate(the_name=F("name"), the_sum=Sum("tableb__value"))
    .values("the_name", "the_sum")
)

This translates to:

SELECT   "tablea"."name" AS "the_name",
         SUM("tableb"."value") AS "the_sum"
FROM     "tablea"
         INNER JOIN "tableb" ON ("tablea"."id" = "tableb"."tablea_id")
WHERE    "tableb"."value" > 0 OR "tableb"."value" < 0
GROUP BY "tablea"."id";

Am I doing something wrong in my ~Q/exclude querysets?  Is this working as 
intended?

Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/4cc2f0e6-00ff-47f8-88f0-03705305d23b%40googlegroups.com.