Could we have an optimizer to deduplicate joins?

[alvinwo]

Like we've discussed in #13160

when we have a query like this:

SELECT a.id_user, a.name, SUM(b.value)
FROM (
  SELECT b.id_user_anon AS id_user, a.name
  FROM user a
  JOIN mapping b
  ON a.id_user = b.id_user
) a
JOIN (
  SELECT b.id_user_anon AS id_user, a.value
  FROM order a
  JOIN mapping b
  ON a.id_user = b.id_user
) b
ON a.id_user = b.id_user
GROUP BY 1,2
;

Could we deduplicate join mapping twice and only join it once, like

SELECT b.id_user_anon AS id_user, a.name, SUM(b.value)
FROM (
  SELECT a.id_user, a.name, b.value
  FROM user a
  JOIN order b
  ON a.id_user = b.id_user
) a
JOIN mapping b
ON a.id_user = b.id_user
GROUP BY 1, 2

cc @findepi

[findepi]

cc @sopel39 @skrzypo987 @raunaqmorarka

[sopel39]

I think this is duplicate of #5878

[sopel39]

Closing as duplicate