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
35256	Strange/duplicate join clauses on queries with filter and annotations	Bálint Balina	nobody	"The filtering/annotation on the same data behaves differently based on the order of operations.

See the below example models, code, and generate sql:


{{{

class PriceList(models.Model):
    id = models.IntegerField(...)
    customer = models.ForeignKey(...)

class PriceListItem(models.Model):
    id = models.IntegerField(...)
    price_list = models.ForeignKey(""PriceList"", models.CASCADE)
    product = models.ForeignKey(""Product"", models.PROTECT, related_name=""price_list_items"")
    price = models.DecimalField(...)

class Product(models.Model):
    id = models.IntegerField(...)
    sku = models.CharField(...)

# retrieve all products, with prices related to a certain customer. 
# The price should be null, if it is not specified for the customer, but the product should still be retrieved.

q1 = Product.objects.annotate(price=F(""price_list_items__price"")).filter(Q(price_list_items__price_list__isnull=True) | Q(price_list_items__price_list__customer_id=1)).values(""id"", ""price"")

# sql (WRONG): 
""""""
SELECT ""product"".""id"", t4.""price"" 
FROM ""product"" 
LEFT OUTER JOIN ""price_list_item"" ON (""product"".""id"" = ""price_list_item"".""product_id"") 
LEFT OUTER JOIN ""price_list"" ON (""price_list_item"".""price_list_id"" = ""price_list"".""id"") 
LEFT OUTER JOIN ""price_list_item"" t4 ON (""product"".""id"" = t4.""product_id"") 
LEFT OUTER JOIN ""price_list"" t5 ON (t4.""price_list_id"" = t5.""id"") 
WHERE (t4.""price_list_id"" IS NULL OR  t5.""customer_id"" = 1))
""""""

q2 = Product.objects.filter(Q(price_list_items__price_list__isnull=True) | Q(price_list_items__price_list__customer_id=1)).annotate(price=F(""price_list_items__price"")).values(""id"", ""price"")

# sql: 
""""""
SELECT ""product"".""id"", price_list_item.""price"" 
FROM ""product"" 
LEFT OUTER JOIN ""price_list_item"" ON (""product"".""id"" = ""price_list_item"".""product_id"") 
LEFT OUTER JOIN ""price_list"" ON (""price_list_item"".""price_list_id"" = ""price_list"".""id"") 
WHERE (price_list_item.""price_list_id"" IS NULL OR  price_list.""customer_id"" = 1))
""""""

}}}


I would expect the 2nd output from both queries. This caused some quite hard-to-detect bugs for us.

It works as expected for ForignKeys, e.g. the other direction:

{{{

q3 = PriceListItem.objects.annotate(sku=F(""product__sku"")).filter(product__sku__icontains=""x"").values(""id"", ""sku"")

# sql
""""""
SELECT ""price_list_item"".""id"", ""product"".""sku"" AS ""sku"" FROM ""price_list_item"" INNER JOIN ""product"" ON (""price_list_item"".""product_id"" = ""product"".""id"") WHERE (UPPER(""product"".""sku""::text) LIKE UPPER('%x%'))
""""""

q4 = PriceListItem.objects.filter(product__sku__icontains=""x"").annotate(sku=F(""product__sku"")).values(""id"", ""sku"")

# sql
""""""
SELECT ""price_list_item"".""id"", ""product"".""sku"" AS ""sku"" FROM ""price_list_item"" INNER JOIN ""product"" ON (""price_list_item"".""product_id"" = ""product"".""id"") WHERE (UPPER(""product"".""sku""::text) LIKE UPPER('%x%'))
""""""


}}}


**To Summarize:**
Filtering on a reverse relation does not reuse joins, if it was already declared in annotations, but adds more joins causing invalid queires because the extra joins do not have proper filtering.

The ORM generates this:
{{{
SELECT ""product"".""id"", t4.""price"" 
FROM ""product"" 
LEFT OUTER JOIN ""price_list_item"" ON (""product"".""id"" = ""price_list_item"".""product_id"") 
LEFT OUTER JOIN ""price_list"" ON (""price_list_item"".""price_list_id"" = ""price_list"".""id"") 
LEFT OUTER JOIN ""price_list_item"" t4 ON (""product"".""id"" = t4.""product_id"") 
LEFT OUTER JOIN ""price_list"" t5 ON (t4.""price_list_id"" = t5.""id"") 
WHERE (t4.""price_list_id"" IS NULL OR  t5.""customer_id"" = 1))
}}}

Instead of this:
{{{
SELECT ""product"".""id"", price_list_item.""price"" 
FROM ""product"" 
LEFT OUTER JOIN ""price_list_item"" ON (""product"".""id"" = ""price_list_item"".""product_id"") 
LEFT OUTER JOIN ""price_list"" ON (""price_list_item"".""price_list_id"" = ""price_list"".""id"") 
WHERE (price_list_item.""price_list_id"" IS NULL OR  price_list.""customer_id"" = 1))
}}}

Tested on django 4.1.7
"	Uncategorized	closed	Database layer (models, ORM)	4.1	Normal	duplicate	annotate filter join		Unreviewed	0	0	0	0	0	0