Selecting data from intermediate table in prefetch_related results in duplicate rows

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEgp5wx8+ggeLmQKiikpDftiGHlegFAlsBQsYACgkQkpDftiGH
legZzBAAinHIs3alAVZurAJWwQ7JmgyCDFa3OZvLM2XMlAVIriFpeLHDwZ6N43q6
h1uYjyFTbk/K0mVcHBCUiXRL0AVphSv2ZaVKYQPPL4ZKM3C164ryPDpq2Mev/1i+
ENrMba9OhQYwif5XoyBXtDF3nXlPvHTKrRAnWtV/BKftNoKBIg0c2ow0ck6lXLhB
NETd4xf6oH79vrRCIOJeWGMlT4kChkUB6Nsc2ikyO6lOeUArhAT7knYf18a9bvcW
jekwxnFB4J3Lt0gGqZaD8kMN8cAimB9Bd0rfscKkYCp+QA4e7x6JBw3bfYkc/YC4
WeoCJwgLonYlq/IDFzBA60/TvCfN+fcvmK4PMHEwNQbKNxi7LRnDnv3P9EQRawbG
zIx7hGwxLUfZYCo10/BHeEV2yR4p1rXzPY76znGlgboD4khLkINFXlIwFd2IhzOR
bSoFHdvSkmAcPS5ydjDn5vKClvhsnWalzV/zhgCdfS2VGpg7KHzjPxu6lbjcgx+5
QpqWj0HoxeutnE/JvF5ikHPD+LlqGYHAAsIE9R368SU77g+qwmrnucpzqkT9qn2x
gdHV3hFssjen4b8y/LeDKKiRMmBBe6YFrUcnnswUzcpsT3xTlXGaRn2pPf8/hh0f
g+9UCyFLIgGtzzZkoixpftmDEIOb0jWSOWSV2wlk0YxXfb87x6g=
=xpR6
-----END PGP SIGNATURE-----
Hello,

I have a two models (User, Project) in m2m relationship with intermediate (Role) table.

When I'm selecting all users in project and I also want to select corresponding role, I simply annotate one field from Role table using F expression:

Users.objects.all().filter(projects__name='Django').annotate(role=F('roles__role'))

Annotate in this case doesn't create new join, because `roles` table is already joined to filter on data from `projects` table. This works well.

However, I tried to same in `prefetch_related` and I'm getting duplicate rows, because there's a new JOIN statement added. (Usecase: Selecting all projects in DB with all users per project)

The SQL statement with `prefetch_related`, but without `annotate` looks like this:

roles = Prefetch(
    'users',
    queryset=User.objects.all()
)

qs = Project.objects.prefetch_related(roles)

SELECT
  ("users_role"."project_id") AS "_prefetch_related_val_project_id",
  — other fields here
FROM "users_user"
  INNER JOIN "users_role" ON ("users_user"."id" = "users_role"."user_id")
WHERE "users_role"."project_id" IN (1, 2, 3, 4, 5)

As you can see, the table `users_role` is already joined, so I'm basically looking for Django ORM expression which generates following SQL query:

SELECT
  ("users_role"."project_id") AS "_prefetch_related_val_project_id",

  "users_role_."role",
  — other fields here
FROM "users_user"
  INNER JOIN "users_role" ON ("users_user"."id" = "users_role"."user_id")
WHERE "users_role"."project_id" IN (1, 2, 3, 4, 5)

Unfortunatelly, following expression generates incorrect SQL:

roles = Prefetch(
    'users',
    queryset=User.objects.all().annotate(role=F('roles__role'))
)
qs = Project.objects.prefetch_related(roles)

SELECT
  ("users_role"."project_id") AS "_prefetch_related_val_project_id",
  "users_role"."role"         AS "role",

  — other fields here
FROM "users_user"
  LEFT OUTER JOIN "users_role" ON ("users_user"."id" = "users_role"."user_id")
  INNER JOIN "users_role" T3 ON ("users_user"."id" = T3."user_id")
WHERE T3."project_id" IN
      (1, 2, 3, 4, 5)

The extra `left outer join` causes duplicate entries.

I've found one ticket (https://code.djangoproject.com/ticket/27144) which seems to be relevant, but it's old and closed.

Any ideas? Is it bug or is there really a reason to include extra JOIN? I'm not very skilled in relational algebra.

Thank you in advance!

Cheers,

   Tom