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`;
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:
This is the SQL code that gets generated:
As you can see, both the first and the second join are refered to as
cte
. Ideally, Esqueleto would produce something like this