python - Django:聚合在使用注释后返回错误结果

Question

聚合查询集时，我注意到如果我之前使用注释，我会得到错误的结果。我不明白为什么。

代码

from django.db.models import QuerySet, Max, F, ExpressionWrapper, DecimalField, Sum
from orders.models import OrderOperation

class OrderOperationQuerySet(QuerySet):
    def last_only(self) -> QuerySet:
        return self \
            .annotate(last_oo_pk=Max('order__orderoperation__pk')) \
            .filter(pk=F('last_oo_pk'))

    @staticmethod
    def _hist_price(orderable_field):
        return ExpressionWrapper(
            F(f'{orderable_field}__hist_unit_price') * F(f'{orderable_field}__quantity'),
            output_field=DecimalField())

    def ordered_articles_data(self):
        return self.aggregate(
            sum_ordered_articles_amounts=Sum(self._hist_price('orderedarticle')))

测试

qs1 = OrderOperation.objects.filter(order__pk=31655)
qs2 = OrderOperation.objects.filter(order__pk=31655).last_only()
assert qs1.count() == qs2.count() == 1 and qs1[0] == qs2[0]  # shows that both querysets contains the same object

qs1.ordered_articles_data()
> {'sum_ordered_articles_amounts': Decimal('3.72')}  # expected result

qs2.ordered_articles_data()
> {'sum_ordered_articles_amounts': Decimal('3.01')}  # wrong result

这个last_only注解方法怎么可能让聚合结果不同(而且是错误的)？

“有趣”的事情似乎只有当订单包含具有相同 hist_price 的商品时才会发生:

旁注

• 我可以确认 Django ORM 创建的 SQL 可能是错误的，因为当我强制执行 last_only() 然后在第二个查询中调用聚合时，它按预期工作。<
• https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#combining-multiple-aggregations可能是一个解释？

SQL 查询(请注意，这些是实际查询，但上面的代码略有简化，这解释了下面 COALESCE 和 "deleted"IS NULL 的存在。)

-- qs1.ordered_articles_data()

SELECT
    COALESCE(
        SUM(
            ("orders_orderedarticle"."hist_unit_price" * "orders_orderedarticle"."quantity")
        ),
        0) AS "sum_ordered_articles_amounts"
FROM "orders_orderoperation"
    LEFT OUTER JOIN "orders_orderedarticle"
        ON ("orders_orderoperation"."id" = "orders_orderedarticle"."order_operation_id")
WHERE ("orders_orderoperation"."order_id" = 31655 AND "orders_orderoperation"."deleted" IS NULL)

-- qs2.ordered_articles_data()

SELECT COALESCE(SUM(("__col1" * "__col2")), 0)
FROM (
    SELECT
        "orders_orderoperation"."id" AS Col1,
        MAX(T3."id") AS "last_oo_pk",
        "orders_orderedarticle"."hist_unit_price" AS "__col1",
        "orders_orderedarticle"."quantity" AS "__col2"
    FROM "orders_orderoperation" INNER JOIN "orders_order"
        ON ("orders_orderoperation"."order_id" = "orders_order"."id")
        LEFT OUTER JOIN "orders_orderoperation" T3
            ON ("orders_order"."id" = T3."order_id")
        LEFT OUTER JOIN "orders_orderedarticle"
            ON ("orders_orderoperation"."id" = "orders_orderedarticle"."order_operation_id")
    WHERE ("orders_orderoperation"."order_id" = 31655 AND "orders_orderoperation"."deleted" IS NULL)
    GROUP BY
        "orders_orderoperation"."id",
        "orders_orderedarticle"."hist_unit_price",
        "orders_orderedarticle"."quantity"
    HAVING "orders_orderoperation"."id" = (MAX(T3."id"))
) subquery

Answer 1

当你在数据库语言(Aggregate Functions)中使用任何注解时，你应该对不在函数内部的所有字段进行分组，你可以在子查询中看到它

GROUP BY
    "orders_orderoperation"."id",
    "orders_orderedarticle"."hist_unit_price",
    "orders_orderedarticle"."quantity"
HAVING "orders_orderoperation"."id" = (MAX(T3."id"))

因此具有相同hist_unit_price和quantity的商品被最大id过滤。因此，根据您的屏幕，chocolate 或 cafe 之一被 having 条件排除。