Joining on a CTE multiple times creates name clashes

[RikvanToor]

Only tested on version 3.5.10.1 on MySQL.

When you include a CTE in a join, that CTE is not given a fresh alias. This means that including that CTE twice (or more times) in a join chain, both instances of the CTE are given the same name, which is invalid SQL.

A small example:

share [mkPersist sqlSettings] [persistLowerCase|
  A
    k Int
    v Int
    Primary k
  
  B
    k Int
    v Int
    Primary k
|]

q :: SqlQuery (SqlExpr (Value Int), SqlExpr (Value Int))
q = do
  bCte <- with $ do
    b <- from $ table @B
    return b
  
  a :& b1 :& b2 <- from $ table @A
    `innerJoin` bCte
    `on` (\(a :& b) -> a.k ==. b.k)
    `innerJoin` bCte
    `on` (\(a :& _ :& b2) -> a.k ==. b2.k)
  return (a.k, a.v +. b1.v +. b2.v)

This is the SQL code that gets generated:

WITH `cte` AS 
  SELECT `b`.`k` AS `v_k`, `b`.`v` AS `v_v`
  FROM `b`
)

SELECT `a`.`k`,  ((`a`.`v` + `cte`.`v_v`) + `cte`.`v_v`)
FROM `a`
INNER JOIN `cte`
  ON `a`.`k` = `cte`.`v_k`
INNER JOIN `cte`
  ON `a`.`k` = `cte`.`v_k`;

As you can see, both the first and the second join are refered to as cte. Ideally, Esqueleto would produce something like this

WITH `cte` AS 
  SELECT `b`.`k` AS `v_k`, `b`.`v` AS `v_v`
  FROM `b`
)

SELECT `a`.`k`,  ((`a`.`v` + `cte_1`.`v_v`) + `cte_2`.`v_v`)
FROM `a`
INNER JOIN `cte` AS `cte_1`
  ON `a`.`k` = `cte_1`.`v_k`
INNER JOIN `cte` AS `cte_2
  ON `a`.`k` = `cte_2`.`v_k`;