id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
23272	Extra join on filtered + exclude + annotate query	Martin Owens	nobody	"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."	Bug	closed	Database layer (models, ORM)	1.5	Normal	needsinfo			Unreviewed	0	0	0	0	0	0