<h3 dir="auto">Describe the bug</h3>
<p dir="auto">When using <code class="notranslate">union_all</code> with an SQLite engine, invalid SQL is generated when one of the subclauses has itself a subclause, such as <code class="notranslate">LIMIT</code>.</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 Column, Integer, String, create_engine, select, union_all
from sqlalchemy.orm import Session, aliased, declarative_base

Base = declarative_base()
engine = create_engine(&quot;sqlite://&quot;, future=True)


class Foo(Base):
    __tablename__ = &quot;foo&quot;
    id = Column(Integer, primary_key=True)
    name = Column(String)


Base.metadata.create_all(engine)

session = Session(engine)

stmt = union_all(
    select(Foo).where(Foo.id == 1).limit(1),
    select(Foo).where(Foo.id == 2),
).subquery()


session.execute(select(aliased(Foo, stmt)))"><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">Column</span>, <span class="pl-v">Integer</span>, <span class="pl-v">String</span>, <span class="pl-s1">create_engine</span>, <span class="pl-s1">select</span>, <span class="pl-s1">union_all</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-v">Session</span>, <span class="pl-s1">aliased</span>, <span class="pl-s1">declarative_base</span>

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()
<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">"sqlite://"</span>, <span class="pl-s1">future</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)


<span class="pl-k">class</span> <span class="pl-v">Foo</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"foo"</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>)


<span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">engine</span>)

<span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-v">Session</span>(<span class="pl-s1">engine</span>)

<span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-en">union_all</span>(
    <span class="pl-en">select</span>(<span class="pl-v">Foo</span>).<span class="pl-en">where</span>(<span class="pl-v">Foo</span>.<span class="pl-s1">id</span> <span class="pl-c1">==</span> <span class="pl-c1">1</span>).<span class="pl-en">limit</span>(<span class="pl-c1">1</span>),
    <span class="pl-en">select</span>(<span class="pl-v">Foo</span>).<span class="pl-en">where</span>(<span class="pl-v">Foo</span>.<span class="pl-s1">id</span> <span class="pl-c1">==</span> <span class="pl-c1">2</span>),
).<span class="pl-en">subquery</span>()


<span class="pl-s1">session</span>.<span class="pl-en">execute</span>(<span class="pl-en">select</span>(<span class="pl-en">aliased</span>(<span class="pl-v">Foo</span>, <span class="pl-s1">stmt</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;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1819, in _execute_context
    self.dialect.do_execute(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py&quot;, line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near &quot;UNION&quot;: syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File &quot;/home/***/main.py&quot;, line 24, in &lt;module&gt;
    session.execute(select(aliased(Foo, stmt)))
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py&quot;, line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py&quot;, line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1862, in _execute_context
    self._handle_dbapi_exception(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 2043, in _handle_dbapi_exception
    util.raise_(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/util/compat.py&quot;, line 208, in raise_
    raise exception
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1819, in _execute_context
    self.dialect.do_execute(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py&quot;, line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near &quot;UNION&quot;: syntax error
[SQL: SELECT anon_1.id, anon_1.name 
FROM ((SELECT foo.id AS id, foo.name AS name 
FROM foo 
WHERE foo.id = ?
 LIMIT ? OFFSET ?) UNION ALL SELECT foo.id AS id, foo.name AS name 
FROM foo 
WHERE foo.id = ?) AS anon_1]
[parameters: (1, 1, 0, 2)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "UNION": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/***/main.py", line 24, in &lt;module&gt;
    session.execute(select(aliased(Foo, stmt)))
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "UNION": syntax error
[SQL: SELECT anon_1.id, anon_1.name 
FROM ((SELECT foo.id AS id, foo.name AS name 
FROM foo 
WHERE foo.id = ?
 LIMIT ? OFFSET ?) UNION ALL SELECT foo.id AS id, foo.name AS name 
FROM foo 
WHERE foo.id = ?) AS anon_1]
[parameters: (1, 1, 0, 2)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
</code></pre></div>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS: Ubuntu</li>
<li>Python: 3.10.4</li>
<li>SQLAlchemy: 1.4.37</li>
<li>Database: SQLite</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient): pysqlite</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto">The cause for the syntax error are the extra parentheses being around the subclauses. If we modify the SQL from this:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT anon_1.id, anon_1.name 
FROM (
    (
        SELECT foo.id AS id, foo.name AS name 
        FROM foo 
        WHERE foo.id = ?
        LIMIT ? OFFSET ?
    ) 
UNION ALL 
    SELECT foo.id AS id, foo.name AS name 
    FROM foo 
    WHERE foo.id = ?
) AS anon_1"><pre class="notranslate"><span class="pl-k">SELECT</span> <span class="pl-c1">anon_1</span>.<span class="pl-c1">id</span>, <span class="pl-c1">anon_1</span>.<span class="pl-c1">name</span> 
<span class="pl-k">FROM</span> (
    (
        <span class="pl-k">SELECT</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> id, <span class="pl-c1">foo</span>.<span class="pl-c1">name</span> <span class="pl-k">AS</span> name 
        <span class="pl-k">FROM</span> foo 
        <span class="pl-k">WHERE</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">=</span> ?
        <span class="pl-k">LIMIT</span> ? OFFSET ?
    ) 
<span class="pl-k">UNION ALL</span> 
    <span class="pl-k">SELECT</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> id, <span class="pl-c1">foo</span>.<span class="pl-c1">name</span> <span class="pl-k">AS</span> name 
    <span class="pl-k">FROM</span> foo 
    <span class="pl-k">WHERE</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">=</span> ?
) <span class="pl-k">AS</span> anon_1</pre></div>
<p dir="auto">to this:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT anon_1.id, anon_1.name
FROM (
    SELECT foo.id AS id, foo.name AS name
    FROM foo
    WHERE foo.id = ?
    LIMIT ? OFFSET ?
UNION ALL
    SELECT foo.id AS id, foo.name AS name
    FROM foo
    WHERE foo.id = ?
) AS anon_1"><pre class="notranslate"><span class="pl-k">SELECT</span> <span class="pl-c1">anon_1</span>.<span class="pl-c1">id</span>, <span class="pl-c1">anon_1</span>.<span class="pl-c1">name</span>
<span class="pl-k">FROM</span> (
    <span class="pl-k">SELECT</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> id, <span class="pl-c1">foo</span>.<span class="pl-c1">name</span> <span class="pl-k">AS</span> name
    <span class="pl-k">FROM</span> foo
    <span class="pl-k">WHERE</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">=</span> ?
    <span class="pl-k">LIMIT</span> ? OFFSET ?
<span class="pl-k">UNION ALL</span>
    <span class="pl-k">SELECT</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> id, <span class="pl-c1">foo</span>.<span class="pl-c1">name</span> <span class="pl-k">AS</span> name
    <span class="pl-k">FROM</span> foo
    <span class="pl-k">WHERE</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">=</span> ?
) <span class="pl-k">AS</span> anon_1</pre></div>
<p dir="auto">we now get <code class="notranslate">LIMIT clause should come after UNION ALL not before</code>, which is still an error, but at least tells us what the root cause of our problem is.</p>
<h3 dir="auto">Describe the bug</h3>
<p dir="auto">When using <code class="notranslate">union_all</code> with an SQLite engine, invalid SQL is generated when one of the subclauses has itself a subclause, such as <code class="notranslate">LIMIT</code>.</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 Column, Integer, String, create_engine, select, union_all
from sqlalchemy.orm import Session, aliased, declarative_base

Base = declarative_base()
engine = create_engine(&quot;sqlite://&quot;, future=True)


class Foo(Base):
    __tablename__ = &quot;foo&quot;
    id = Column(Integer, primary_key=True)
    name = Column(String)


Base.metadata.create_all(engine)

session = Session(engine)

stmt = union_all(
    select(Foo).where(Foo.id == 1).limit(1),
    select(Foo).where(Foo.id == 2),
).subquery()


session.execute(select(aliased(Foo, stmt)))"><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">Column</span>, <span class="pl-v">Integer</span>, <span class="pl-v">String</span>, <span class="pl-s1">create_engine</span>, <span class="pl-s1">select</span>, <span class="pl-s1">union_all</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-v">Session</span>, <span class="pl-s1">aliased</span>, <span class="pl-s1">declarative_base</span>

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()
<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">"sqlite://"</span>, <span class="pl-s1">future</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)


<span class="pl-k">class</span> <span class="pl-v">Foo</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"foo"</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>)


<span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">engine</span>)

<span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-v">Session</span>(<span class="pl-s1">engine</span>)

<span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-en">union_all</span>(
    <span class="pl-en">select</span>(<span class="pl-v">Foo</span>).<span class="pl-en">where</span>(<span class="pl-v">Foo</span>.<span class="pl-s1">id</span> <span class="pl-c1">==</span> <span class="pl-c1">1</span>).<span class="pl-en">limit</span>(<span class="pl-c1">1</span>),
    <span class="pl-en">select</span>(<span class="pl-v">Foo</span>).<span class="pl-en">where</span>(<span class="pl-v">Foo</span>.<span class="pl-s1">id</span> <span class="pl-c1">==</span> <span class="pl-c1">2</span>),
).<span class="pl-en">subquery</span>()


<span class="pl-s1">session</span>.<span class="pl-en">execute</span>(<span class="pl-en">select</span>(<span class="pl-en">aliased</span>(<span class="pl-v">Foo</span>, <span class="pl-s1">stmt</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;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1819, in _execute_context
    self.dialect.do_execute(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py&quot;, line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near &quot;UNION&quot;: syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File &quot;/home/***/main.py&quot;, line 24, in &lt;module&gt;
    session.execute(select(aliased(Foo, stmt)))
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py&quot;, line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py&quot;, line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1862, in _execute_context
    self._handle_dbapi_exception(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 2043, in _handle_dbapi_exception
    util.raise_(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/util/compat.py&quot;, line 208, in raise_
    raise exception
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1819, in _execute_context
    self.dialect.do_execute(
  File &quot;/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py&quot;, line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near &quot;UNION&quot;: syntax error
[SQL: SELECT anon_1.id, anon_1.name 
FROM ((SELECT foo.id AS id, foo.name AS name 
FROM foo 
WHERE foo.id = ?
 LIMIT ? OFFSET ?) UNION ALL SELECT foo.id AS id, foo.name AS name 
FROM foo 
WHERE foo.id = ?) AS anon_1]
[parameters: (1, 1, 0, 2)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "UNION": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/***/main.py", line 24, in &lt;module&gt;
    session.execute(select(aliased(Foo, stmt)))
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/***/.cache/pypoetry/virtualenvs/A1rRx-K7-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "UNION": syntax error
[SQL: SELECT anon_1.id, anon_1.name 
FROM ((SELECT foo.id AS id, foo.name AS name 
FROM foo 
WHERE foo.id = ?
 LIMIT ? OFFSET ?) UNION ALL SELECT foo.id AS id, foo.name AS name 
FROM foo 
WHERE foo.id = ?) AS anon_1]
[parameters: (1, 1, 0, 2)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
</code></pre></div>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS: Ubuntu</li>
<li>Python: 3.10.4</li>
<li>SQLAlchemy: 1.4.37</li>
<li>Database: SQLite</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient): pysqlite</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto">The cause for the syntax error are the extra parentheses being around the subclauses. If we modify the SQL like so;</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT anon_1.id, anon_1.name
FROM (
    SELECT foo.id AS id, foo.name AS name
    FROM foo
    WHERE foo.id = ?
    LIMIT ? OFFSET ?
UNION ALL
    SELECT foo.id AS id, foo.name AS name
    FROM foo
    WHERE foo.id = ?
) AS anon_1"><pre class="notranslate"><span class="pl-k">SELECT</span> <span class="pl-c1">anon_1</span>.<span class="pl-c1">id</span>, <span class="pl-c1">anon_1</span>.<span class="pl-c1">name</span>
<span class="pl-k">FROM</span> (
    <span class="pl-k">SELECT</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> id, <span class="pl-c1">foo</span>.<span class="pl-c1">name</span> <span class="pl-k">AS</span> name
    <span class="pl-k">FROM</span> foo
    <span class="pl-k">WHERE</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">=</span> ?
    <span class="pl-k">LIMIT</span> ? OFFSET ?
<span class="pl-k">UNION ALL</span>
    <span class="pl-k">SELECT</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> id, <span class="pl-c1">foo</span>.<span class="pl-c1">name</span> <span class="pl-k">AS</span> name
    <span class="pl-k">FROM</span> foo
    <span class="pl-k">WHERE</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">=</span> ?
) <span class="pl-k">AS</span> anon_1</pre></div>
<details>
<summary>original version</summary>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT anon_1.id, anon_1.name 
FROM (
    (
        SELECT foo.id AS id, foo.name AS name 
        FROM foo 
        WHERE foo.id = ?
        LIMIT ? OFFSET ?
    ) 
UNION ALL 
    SELECT foo.id AS id, foo.name AS name 
    FROM foo 
    WHERE foo.id = ?
) AS anon_1"><pre class="notranslate"><span class="pl-k">SELECT</span> <span class="pl-c1">anon_1</span>.<span class="pl-c1">id</span>, <span class="pl-c1">anon_1</span>.<span class="pl-c1">name</span> 
<span class="pl-k">FROM</span> (
    (
        <span class="pl-k">SELECT</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> id, <span class="pl-c1">foo</span>.<span class="pl-c1">name</span> <span class="pl-k">AS</span> name 
        <span class="pl-k">FROM</span> foo 
        <span class="pl-k">WHERE</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">=</span> ?
        <span class="pl-k">LIMIT</span> ? OFFSET ?
    ) 
<span class="pl-k">UNION ALL</span> 
    <span class="pl-k">SELECT</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> id, <span class="pl-c1">foo</span>.<span class="pl-c1">name</span> <span class="pl-k">AS</span> name 
    <span class="pl-k">FROM</span> foo 
    <span class="pl-k">WHERE</span> <span class="pl-c1">foo</span>.<span class="pl-c1">id</span> <span class="pl-k">=</span> ?
) <span class="pl-k">AS</span> anon_1</pre></div>
</details>
<p dir="auto">we now get <code class="notranslate">LIMIT clause should come after UNION ALL not before</code>, which is still an error, but at least tells us what the root cause of our problem is.</p>