Wrong join promotion on ManyToManyField to self with an intermediate model.

[loic84]

Generated SQL:

SELECT "m2m_through_personselfrefm2m"."id", "m2m_through_personselfrefm2m"."name"
FROM "m2m_through_personselfrefm2m"
INNER JOIN "m2m_through_friendship" ON ( "m2m_through_personselfrefm2m"."id" = "m2m_through_friendship"."second_id" )
INNER JOIN "m2m_through_friendship" T4 ON ( "m2m_through_personselfrefm2m"."id" = T4."first_id" )
WHERE ("m2m_through_friendship"."first_id" = 1 AND T4."reason" = 'because')

[loic84]

Failing testcase: ​https://github.com/loic/django/compare/ticket22221.

[Anssi Kääriäinen]

I don't see how this is a join promotion issue - why should joins be left joins in the generated SQL for the test case? Both joined fields are checked for equality in the WHERE clause. Left joins can't give more results as the WHERE clause can't match if either T4 or m2m_through_friendship are NULL.

[Anssi Kääriäinen]

The problem seems to be using the wrong direction in the query. The rel_from_set in tony.friends.filter(rel_from_set...) refers to the *friend's* rel_from_set. That is, it refers to Chris' rel_from_set, and chris isn't the first friend in the relation. I believe the correct query is tony.friend.filter(rel_to_set...). Closing as invalid, do reopen if my analysis seems wrong.