SQL arguments misplaced within query

[PunchyRascal]

Got this in the toolbar

SELECT 
    a.id, 
    a.foo,
    SUM(CASE WHEN a.status = 0 THEN 1 ELSE 'TO_BE_PAID' END) AS "PENDING_CNT",
    SUM(CASE WHEN a.status = 'PAID_OUT' THEN 1 ELSE 'TO_BE_PAID' END) AS "PAID_CNT",
    SUM(CASE WHEN b.status = 1 THEN 1 ELSE 'TO_BE_PAID' END) AS "REJECTED_CNT",
    COUNT(b.id) AS "ALL_CNT"
FROM a
LEFT OUTER JOIN b  ON b.a_id = a.id
GROUP BY a.id, a.foo

instead of this

SELECT 
    a.id, 
    a.foo,
    SUM(CASE WHEN a.status = 'TO_BE_PAID' THEN 1 ELSE 0 END) AS "PENDING_CNT",
    SUM(CASE WHEN a.status = 'PAID_OUT' THEN 1 ELSE 0 END) AS "PAID_CNT",
    SUM(CASE WHEN b.status = 'REJECTED' THEN 1 ELSE 0 END) AS "REJECTED_CNT",
    COUNT(b.id) AS "ALL_CNT"
FROM a
LEFT OUTER JOIN b  ON b.a_id = a.id
GROUP BY a.id, a.foo

which I reconstructed from the query and args manually.

[tim-schilling]

Can you provide a test case that reproduces this issue?

[PunchyRascal]

It is a simple 1:N model (say order+order items) and I am selecting the orders and some stats about the items. Django query:

self.request.user.orders.all()
            .order_by("-ts_initiated")
            .annotate(paid_total=Sum(F("b__money")))
            .annotate(currency=F("b__money_currency"))
            .annotate(
                pending_cnt=Sum(
                    Case(
                        When(b__status=Status.TO_BE_PAID, then=1),
                        output_field=IntegerField(),
                        default=0,
                    )
                )
            )
            .annotate(
                paid_cnt=Sum(
                    Case(
                        When(b__status=Status.PAID_OUT, then=1),
                        output_field=IntegerField(),
                        default=0,
                    )
                )
            )
            .annotate(
                rejected_cnt=Sum(
                    Case(
                        When(b__status=Status.REJECTED, then=1),
                        output_field=IntegerField(),
                        default=0,
                    )
                )
            )
            .annotate(all_cnt=Count(F("b__id")))

[tim-schilling]

I'm glad that the model design is simple. Can you provide your models or a simplified version of them to reproduce? Please also include your python version, Django version, database and debug toolbar version. Ideally we'd have enough information to pull the code in, and reproduce the problem without having to do much. That way whoever picks up the issue can focus on diagnosing the problem rather than recreating your problem.

[tim-schilling]

Closing due to lack of response.