Extra join on filtered + exclude + annotate query

[Martin Owens]

The the following code:

e = reduce(OR, (Q(**{'health_uses__purchase__sku__icontains':v}) for v in ['COMP','NUTR','DEEP']))
qs = Customer.objects.filter(pk=8265, health_uses__transact__name__in=('Use', 'Adjustment'))
        .exclude(b).annotate(p_value=Sum('health_uses__quantity')).values_list('id', 'p_value')
qs.query.group_by = []

qs results in: [(8265, 961)]
With the query:

SELECT "customers_customer"."id", SUM("healthclub_usage"."quantity") AS "p_value" FROM "customers_customer"
    LEFT OUTER JOIN "healthclub_usage" ON ("customers_customer"."id" = "healthclub_usage"."customer_id")
    LEFT OUTER JOIN "healthclub_transactiontype" ON ("healthclub_usage"."transact_id" = "healthclub_transactiontype"."id")
    LEFT OUTER JOIN "healthclub_usage" T4 ON ("customers_customer"."id" = T4."customer_id")
    LEFT OUTER JOIN "healthclub_purchase" ON (T4."purchase_id" = "healthclub_purchase"."id")
  WHERE ("customers_customer"."id" = 8265  AND "healthclub_transactiontype"."name" IN (Use, Adjustment)
    AND NOT ((UPPER("healthclub_purchase"."sku"::text) LIKE UPPER(%COMP%)  OR UPPER("healthclub_purchase"."sku"::text)
      LIKE UPPER(%NUTR%)  OR UPPER("healthclub_purchase"."sku"::text) LIKE UPPER(%DEEP%) )))
  GROUP BY "customers_customer"."id";

The problem is that T4 JOIN up there. It's a duplicate join because we've already joined on healthclub_usage. Tweaking the SQL so it's now:

SELECT "customers_customer"."id", SUM("healthclub_usage"."quantity") AS "p_value" FROM "customers_customer"
    LEFT OUTER JOIN "healthclub_usage" ON ("customers_customer"."id" = "healthclub_usage"."customer_id")
    LEFT OUTER JOIN "healthclub_transactiontype" ON ("healthclub_usage"."transact_id" = "healthclub_transactiontype"."id") 
    LEFT OUTER JOIN "healthclub_purchase" ON ("healthclub_usage"."purchase_id" = "healthclub_purchase"."id")
  WHERE ("customers_customer"."id" = 8265  AND "healthclub_transactiontype"."name" IN ('Use', 'Adjustment')
    AND NOT ((UPPER("healthclub_purchase"."sku"::text) LIKE UPPER('%COMP%')  OR UPPER("healthclub_purchase"."sku"::text)
      LIKE UPPER('%NUTR%')  OR UPPER("healthclub_purchase"."sku"::text) LIKE UPPER('%DEEP%') )))
  GROUP BY "customers_customer"."id";

Simply removing the T4 join and linking the purchase_id to healthclub_usage directly, we get:

  id  | p_value 
------+---------
 8265 |      30
(1 row)

Which is the value we expect. I'm unsure if django orm is incapable of performing the needed query, or if it's got a subtle bug in there when we use excludes. Please help.

[Anssi Kääriäinen]

Which version of Django are you using? If you are using 1.5 as indicated by the version field, this might have been fixed in later releases. There have been many bug fixes to join generation logic recently, so checking with master branch of Django would be helpful.

To reproduce what is happening here I need the model definitions (or simplified versions of them). A sample project attached or model definitions posted inside a comment are both OK ways to provide the model definitions.

Also, it seems the e variable is actually the same thing as the b variable (used in .exclude(b)).

Don't use qs.query.group_by = [], that isn't supported API. You should get the same results by doing .values('id').annotate(p_value=...)

I'll close this as needsinfo, please reopen when you provide the models needed to reproduce the error.

[Martin Owens]

Confirmed fixed on django 1.6.5, the sql is more messy but I get the right result.

Thanks for the advice.