Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#23272 closed Bug (needsinfo)

Extra join on filtered + exclude + annotate query

Reported by: Martin Owens Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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.

Change History (2)

comment:1 by Anssi Kääriäinen, 10 years ago

Resolution: needsinfo
Status: newclosed

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.

comment:2 by Martin Owens, 10 years ago

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

Thanks for the advice.

Note: See TracTickets for help on using tickets.
Back to Top