<h3 dir="auto">Describe the bug</h3>
<p dir="auto">a CTE cannot be aliased multiple times, raise Multiple CTEs with SAME NAME;</p>
<h3 dir="auto">To Reproduce</h3>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import Table
from sqlalchemy import MetaData
from sqlalchemy import Column
from sqlalchemy import Float
from sqlalchemy import create_engine


sa_table = Table('district',
                 MetaData(schema='real_estate'),
                 Column('price', Float))

# table = select([sa_table.c.price]).cte('not_a', nesting=True)
table = select([sa_table.c.price]).cte('not_a')
sum_table = table.alias('a')
common_table = table.alias('a')
sql = select(
    [select([func.sum(sum_table.c.price)]).as_scalar() /
     common_table.c.price]
).select_from(common_table)

sql.compile()"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Table</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">MetaData</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Float</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>


<span class="pl-s1">sa_table</span> <span class="pl-c1">=</span> <span class="pl-v">Table</span>(<span class="pl-s">'district'</span>,
                 <span class="pl-v">MetaData</span>(<span class="pl-s1">schema</span><span class="pl-c1">=</span><span class="pl-s">'real_estate'</span>),
                 <span class="pl-v">Column</span>(<span class="pl-s">'price'</span>, <span class="pl-v">Float</span>))

<span class="pl-c"># table = select([sa_table.c.price]).cte('not_a', nesting=True)</span>
<span class="pl-s1">table</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>([<span class="pl-s1">sa_table</span>.<span class="pl-s1">c</span>.<span class="pl-s1">price</span>]).<span class="pl-en">cte</span>(<span class="pl-s">'not_a'</span>)
<span class="pl-s1">sum_table</span> <span class="pl-c1">=</span> <span class="pl-s1">table</span>.<span class="pl-en">alias</span>(<span class="pl-s">'a'</span>)
<span class="pl-s1">common_table</span> <span class="pl-c1">=</span> <span class="pl-s1">table</span>.<span class="pl-en">alias</span>(<span class="pl-s">'a'</span>)
<span class="pl-s1">sql</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(
    [<span class="pl-en">select</span>([<span class="pl-s1">func</span>.<span class="pl-en">sum</span>(<span class="pl-s1">sum_table</span>.<span class="pl-s1">c</span>.<span class="pl-s1">price</span>)]).<span class="pl-en">as_scalar</span>() <span class="pl-c1">/</span>
     <span class="pl-s1">common_table</span>.<span class="pl-s1">c</span>.<span class="pl-s1">price</span>]
).<span class="pl-en">select_from</span>(<span class="pl-s1">common_table</span>)

<span class="pl-s1">sql</span>.<span class="pl-en">compile</span>()</pre></div>
<h3 dir="auto">Error</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;&lt;input&gt;&quot;, line 1, in &lt;module&gt;
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py&quot;, line 489, in compile
    return self._compiler(dialect, **kw)
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py&quot;, line 553, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py&quot;, line 765, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py&quot;, line 454, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py&quot;, line 489, in process
    return obj._compiler_dispatch(self, **kwargs)
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py&quot;, line 82, in _compiler_dispatch
    return meth(self, **kw)
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py&quot;, line 3263, in visit_select
    text = self._compose_select_body(
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py&quot;, line 3407, in _compose_select_body
    [
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py&quot;, line 3408, in &lt;listcomp&gt;
    f._compiler_dispatch(
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py&quot;, line 82, in _compiler_dispatch
    return meth(self, **kw)
  File &quot;/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py&quot;, line 2549, in visit_cte
    raise exc.CompileError(
sqlalchemy.exc.CompileError: Multiple, unrelated CTEs found with the same name: 'a'``` 

### Versions

- OS: macOS Big Sur
- Python: 3.8
- SQLAlchemy: 1.3.24 &amp; 1.4.24
- Database: Postgresql
- DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg


### Additional context

_No response_"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "&lt;input&gt;", line 1, in &lt;module&gt;
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 489, in compile
    return self._compiler(dialect, **kw)
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 553, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 765, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 454, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 489, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3263, in visit_select
    text = self._compose_select_body(
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3407, in _compose_select_body
    [
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3408, in &lt;listcomp&gt;
    f._compiler_dispatch(
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/zzz/backend/mdt_parser/py_venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2549, in visit_cte
    raise exc.CompileError(
sqlalchemy.exc.CompileError: Multiple, unrelated CTEs found with the same name: 'a'``` 

### Versions

- OS: macOS Big Sur
- Python: 3.8
- SQLAlchemy: 1.3.24 &amp; 1.4.24
- Database: Postgresql
- DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg


### Additional context

_No response_
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Dobes Vandermeer (<a href="https://github.com/dobesv">@dobesv</a>)</strong></p>
<p dir="auto">I am trying to use the "baked query" pattern to reduce the time spent generating SQL, which currently is quite significant for our app.</p>
<p dir="auto">One thing I can't seem to parameterize using a bindparam, however, is the limit on the query.</p>
<p dir="auto">Although in <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384612604" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/805" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/805/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/805">#805</a> the limit was changed into a bindparam, this doesn't allow me to provide my own bindparam but rather always creates its own bindparam, assuming the limit I procide is a number already.</p>
<p dir="auto">I think that if the places where currently it wraps a numeric limit into a bindparam using sql.literal() it would also check if limit was already a bindparam then it would be good.</p>
<p dir="auto">Here's an example that can be pasted into the test suite:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    def test_select_with_bindparam_limit(self):
        &quot;&quot;&quot;Does a query allow bindparam for the limit?&quot;&quot;&quot;
        sess = create_session()
        users = []

        q1 = sess.query(self.classes.User).order_by(self.classes.User.id).limit(sa.bindparam('n'))
        for n in xrange(1,4):
            users[:] = q1.params(n=n).all()
            assert len(users) == n"><pre class="notranslate"><code class="notranslate">    def test_select_with_bindparam_limit(self):
        """Does a query allow bindparam for the limit?"""
        sess = create_session()
        users = []

        q1 = sess.query(self.classes.User).order_by(self.classes.User.id).limit(sa.bindparam('n'))
        for n in xrange(1,4):
            users[:] = q1.params(n=n).all()
            assert len(users) == n
</code></pre></div>