Wrong Values if multiple SumIf join over the same table

user008811 commented 9 years ago

Hi, i really like the SumIf, but it seems to have problems when using some tables more then once. Maybe the joins are the source of the problems. I dont know.

The following produces way too large numbers:

for month, num in month_tuples:
    key = "AASUM_" + month
    kwargs[key] = SumIf('AA_X__hminutes',
                        only=Q(AA_X__appointment__datetime__month=num,
                        AA_X__appointment__datetime__year=YEAR,
                        AA_X__appointment__status__id=2,
                        AA_X__status__id=1))

    key = "BBSUM_" + month
    kwargs[key] = SumIf('BB_X__minutes',
                        only=Q(BB_X__appointment__datetime__month=num,
                        BB_X__appointment__datetime__year=YEAR,
                        BB_X__appointment__status__id=2))

    key = "CCSUM_" + month
    kwargs[key] = SumIf('CC_X__minutes',
                        only=Q(CC_X__appointment__datetime__month=num,
                        CC_X__appointment__datetime__year=YEAR,
                        CC_X__appointment__status__id=2))

Only using one of the 3 blocks works fine. Notes: AA_X, BB_X, CC_X are reverse relations. The query is fired via : Resource.objects.all().annotate(**kwargs)

Hope this helps. Used on django version 1.4; Also, using only and not only makes no difference. I'm not really sure when only would be useful though, maybe some explanation would be cool.

Thanks

henriquebastos commented 9 years ago

Hi! Tks for repporting the issue.

Could you please express this problem as a failing test?

Check out how we do on the models.py and tests.py

user008811 commented 9 years ago

I don't currently have a server to put your models on. So Sorry, i can't recreate the aggregate-if test environment at the moment. Currently i'm short on time as well.

But i'll happily explain the models used:

Appointment is the main model.

with a status fk (filtered to be id = 2 for completed)
with a datetime field, filtered via month and year

Appointment has 3 different M2M relations with a through table. AA, BB, CC

For a validAA entry, there is an additional status FK field (filtered for 1) AA also has the attribute that is to be summed up per month slightly differently named "hminutes" BB & CC the attribute to be summed is just called minutes.

It might be a coincidence, but the sum is off by factor 10 in my data. E.g. if i just do the first paar of annotates (AA) the sum is like 3450 minutes for january, if all 3 annotates are done, its 34500.

I expect that the left outer joins generated are wrong.

Here is the query built:

SELECT "myapp_resource"."id",
       "myapp_resource"."name",
       "myapp_resource"."active_from",
       "myapp_resource"."active_to",
       "myapp_resource"."description",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 4
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_apr",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 5
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_may",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 11
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_nov",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 3
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_mar",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 1
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_jan",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 7
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_jul",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 3
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_mar",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 5
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_may",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 9
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_sep",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 5
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_may",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 10
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_oct",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 10
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_oct",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 8
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_aug",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 3
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_mar",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 8
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_aug",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 9
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_sep",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 1
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_jan",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 2
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_feb",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 1
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_jan",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 12
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_dec",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 2
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_feb",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 7
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_jul",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 6
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_jun",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 11
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_nov",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 8
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_aug",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 12
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_dec",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 4
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_apr",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 12
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_dec",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 4
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_apr",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 6
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_jun",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 2
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_feb",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 9
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_bb"."minutes" ELSE NULL END) AS "bb_minutes_sep",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 7
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_jul",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 6
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2
                      AND "myapp_aa"."status_id" = 1) THEN "myapp_aa"."hminutes" ELSE NULL END) AS "aa_minutes_jun",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 10
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_oct",
       SUM(CASE WHEN (EXTRACT('month'
                              FROM "myapp_appointment"."datetime") = 11
                      AND "myapp_appointment"."datetime" BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59.999999'
                      AND "myapp_appointment"."status_id" = 2) THEN "myapp_cc"."minutes" ELSE NULL END) AS "cc_minutes_nov"
FROM "myapp_resource"
LEFT OUTER JOIN "myapp_bb" ON ("myapp_resource"."id" = "myapp_bb"."resource_id")
LEFT OUTER JOIN "myapp_appointment" ON ("myapp_bb"."appointment_id" = "myapp_appointment"."id")
LEFT OUTER JOIN "myapp_appointmentstatus" ON ("myapp_appointment"."status_id" = "myapp_appointmentstatus"."id")
LEFT OUTER JOIN "myapp_aa" ON ("myapp_resource"."id" = "myapp_aa"."resource_id")
LEFT OUTER JOIN "myapp_appointment" T6 ON ("myapp_aa"."appointment_id" = T6."id")
LEFT OUTER JOIN "myapp_appointmentstatus" T7 ON (T6."status_id" = T7."id")
LEFT OUTER JOIN "myapp_aastatus" ON ("myapp_aa"."status_id" = "myapp_aastatus"."id")
LEFT OUTER JOIN "myapp_cc" ON ("myapp_resource"."id" = "myapp_cc"."resource_id")
LEFT OUTER JOIN "myapp_appointment" T10 ON ("myapp_cc"."appointment_id" = T10."id")
LEFT OUTER JOIN "myapp_appointmentstatus" T11 ON (T10."status_id" = T11."id")
GROUP BY "myapp_resource"."id",
         "myapp_resource"."name",
         "myapp_resource"."active_from",
         "myapp_resource"."active_to",
         "myapp_resource"."description"
ORDER BY "myapp_resource"."name" ASC

henriquebastos / django-aggregate-if

Wrong Values if multiple SumIf join over the same table #15