[Sqlalchemy-tickets] Issue #4319: CTE repeated inside subquery (zzzeek/sqlalchemy)

[James F. <iss...@bi...>]

New issue 4319: CTE repeated inside subquery
https://bitbucket.org/zzzeek/sqlalchemy/issues/4319/cte-repeated-inside-subquery

James Frick:

When using CTE's, the desired behavior is to define your CTEs at the top of the statement, then reference them throughout the query. The current behavior is that if you use a subquery with a CTE in it, the entire WITH statement gets written again. I don't think this is legal syntax in most dialects.

```
from sqlalchemy.dialects import oracle
table_a = sa.table('table1', sa.column('col_a'), sa.column('col_b'))
cte_a = table_a.select().cte('cte_a')
subq_1 = cte_a.select().where(cte_a.c.col_a == 1)
subq_2 = cte_a.select().where(cte_a.c.col_b == 2)
query = subq_1.join(subq_2, subq_1.c.col_a == subq_2.c.col_a)
print(query.compile(dialect=oracle.dialect()))
```
This should define cte_a up at the top and recycle it in the subquery that gets inner joined, but instead it repeats the entire WITH statement, as seen below:
```
(WITH cte_a AS 
(SELECT table1.col_a AS col_a, table1.col_b AS col_b 
FROM table1)
 SELECT cte_a.col_a AS col_a, cte_a.col_b AS col_b 
FROM cte_a 
WHERE cte_a.col_a = :col_a_1) JOIN (WITH cte_a AS 
(SELECT table1.col_a AS col_a, table1.col_b AS col_b 
FROM table1)
 SELECT cte_a.col_a, cte_a.col_b 
FROM cte_a 
WHERE cte_a.col_b = :col_b_1) ON col_a = col_a
```

Tested with the oracle and postgresql dialects.