Issue 1:
Describe the bug

When using union_all with an SQLite engine, invalid SQL is generated when one of the subclauses has itself a subclause, such as LIMIT.

To Reproduce

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("sqlite://", future=True)

class Foo(Base):
__tablename__ = "foo"
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)))

Error

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 <module>
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)

Versions

OS: Ubuntu

Python: 3.10.4

SQLAlchemy: 1.4.37

Database: SQLite

DBAPI (eg: psycopg, cx_oracle, mysqlclient): pysqlite

Additional context

The cause for the syntax error are the extra parentheses being around the subclauses. If we modify the SQL from this:

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

to this:

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

we now get LIMIT clause should come after UNION ALL not before, which is still an error, but at least tells us what the root cause of our problem is.

Issue 2:
Describe the bug

When using union_all with an SQLite engine, invalid SQL is generated when one of the subclauses has itself a subclause, such as LIMIT.

To Reproduce

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("sqlite://", future=True)

class Foo(Base):
__tablename__ = "foo"
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)))

Error

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 <module>
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)

Versions

OS: Ubuntu

Python: 3.10.4

SQLAlchemy: 1.4.37

Database: SQLite

DBAPI (eg: psycopg, cx_oracle, mysqlclient): pysqlite

Additional context

The cause for the syntax error are the extra parentheses being around the subclauses. If we modify the SQL like so;

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

original version

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

we now get LIMIT clause should come after UNION ALL not before, which is still an error, but at least tells us what the root cause of our problem is.