<p dir="auto"><strong>Describe the bug</strong><br>
Duplicate <code class="notranslate">anon</code> names used in queries with<code class="notranslate"> union</code>, <code class="notranslate">limit</code> and <code class="notranslate">joinedload</code> causing the query to fail at the driver level.<br>
This query used to work before 1.4.</p>
<p dir="auto"><strong>To Reproduce</strong></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import Column, Integer, String, ForeignKey, PrimaryKeyConstraint, func, Boolean, union
from sqlalchemy.orm import declarative_base, relationship, backref, joinedload
from sqlalchemy.schema import Table

Base = declarative_base()

class User(Base):
    __tablename__ = &quot;appuser&quot;
    id = Column(Integer(), primary_key=True)

class Project(Base):
    __tablename__ = &quot;project&quot;
    id = Column(Integer(), primary_key=True)

class ProjectPermissions(Base):
    __tablename__ = &quot;project_user&quot;
    user_id = Column(
        Integer(), ForeignKey(&quot;appuser.id&quot;), index=True
    )
    user = relationship(&quot;User&quot;)
    project_id = Column(
        Integer(), ForeignKey(&quot;project.id&quot;), index=True
    )
    project = relationship(&quot;Project&quot;)
    __table_args__ = (PrimaryKeyConstraint(&quot;user_id&quot;, &quot;project_id&quot;), {})

samples_tags = Table(
    &quot;samples_tags&quot;,
    Base.metadata,
    Column(
        &quot;tag_id&quot;, Integer, ForeignKey(&quot;tags.id&quot;), index=True
    ),
    Column(
        &quot;sample_id&quot;,
        Integer,
        ForeignKey(&quot;sample.id&quot;),
        index=True,
    ),
    PrimaryKeyConstraint(&quot;tag_id&quot;, &quot;sample_id&quot;),
)

class Tag(Base):
    __tablename__ = &quot;tags&quot;
    id = Column(Integer, primary_key=True)
    name = Column(String(50), primary_key=True)

class BaseDataFile(Base):
    __tablename__ = &quot;base_data_file&quot;
    id = Column(Integer, primary_key=True)
    data_file_type = Column(String(50))
    visible = Column(Boolean())
    user_id = Column(
        Integer,
        ForeignKey(
            &quot;appuser.id&quot;,
            onupdate=&quot;RESTRICT&quot;,
            ondelete=&quot;RESTRICT&quot;,
        ),
        index=True,
    )
    user_rel = relationship(&quot;User&quot;)
    project_id = Column(
        Integer,
        ForeignKey(&quot;project.id&quot;,  ondelete=&quot;SET NULL&quot;),
        nullable=True,
        index=True,
    )
    project = relationship(Project)
    __mapper_args__ = {&quot;polymorphic_identity&quot;: &quot;base_data_file&quot;, &quot;polymorphic_on&quot;: data_file_type}

class Sample(BaseDataFile):
    __tablename__ = &quot;sample&quot;
    __mapper_args__ = {&quot;polymorphic_identity&quot;: &quot;sample&quot;}
    id = Column(
        Integer,
        ForeignKey(&quot;base_data_file.id&quot;),
        primary_key=True,
    )
    tags = relationship(
        &quot;Tag&quot;, secondary=samples_tags, backref=backref(&quot;samples&quot;)
    )

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

user_id = 1
user_sample_query = session.query(Sample).filter(Sample.visible.is_(True), Sample.user_id == user_id)
project_samples_query = (
    session
        .query(Sample)
        .join(ProjectPermissions, ProjectPermissions.project_id == Sample.project_id, isouter=True)
        .filter(Sample.visible.is_(True), Sample.user_id == user_id)
)
unioned = user_sample_query.union(project_samples_query)
# q = unioned.options(joinedload(&quot;tags&quot;))
q = unioned.options(joinedload(&quot;tags&quot;)).limit(10)
# q = session.query(Sample).filter(Sample.id.in_(aliased)).options(joinedload(&quot;tags&quot;)).limit(10)
print(str(q.statement.compile(compile_kwargs={&quot;literal_binds&quot;: True})))
print(&quot;===&quot;)
print(q.all())"><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-v">ForeignKey</span>, <span class="pl-v">PrimaryKeyConstraint</span>, <span class="pl-s1">func</span>, <span class="pl-v">Boolean</span>, <span class="pl-s1">union</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-s1">declarative_base</span>, <span class="pl-s1">relationship</span>, <span class="pl-s1">backref</span>, <span class="pl-s1">joinedload</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">schema</span> <span class="pl-k">import</span> <span class="pl-v">Table</span>

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()

<span class="pl-k">class</span> <span class="pl-v">User</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"appuser"</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-k">class</span> <span class="pl-v">Project</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"project"</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-k">class</span> <span class="pl-v">ProjectPermissions</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"project_user"</span>
    <span class="pl-s1">user_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(
        <span class="pl-v">Integer</span>(), <span class="pl-v">ForeignKey</span>(<span class="pl-s">"appuser.id"</span>), <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">True</span>
    )
    <span class="pl-s1">user</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(<span class="pl-s">"User"</span>)
    <span class="pl-s1">project_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(
        <span class="pl-v">Integer</span>(), <span class="pl-v">ForeignKey</span>(<span class="pl-s">"project.id"</span>), <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">True</span>
    )
    <span class="pl-s1">project</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(<span class="pl-s">"Project"</span>)
    <span class="pl-s1">__table_args__</span> <span class="pl-c1">=</span> (<span class="pl-v">PrimaryKeyConstraint</span>(<span class="pl-s">"user_id"</span>, <span class="pl-s">"project_id"</span>), {})

<span class="pl-s1">samples_tags</span> <span class="pl-c1">=</span> <span class="pl-v">Table</span>(
    <span class="pl-s">"samples_tags"</span>,
    <span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>,
    <span class="pl-v">Column</span>(
        <span class="pl-s">"tag_id"</span>, <span class="pl-v">Integer</span>, <span class="pl-v">ForeignKey</span>(<span class="pl-s">"tags.id"</span>), <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">True</span>
    ),
    <span class="pl-v">Column</span>(
        <span class="pl-s">"sample_id"</span>,
        <span class="pl-v">Integer</span>,
        <span class="pl-v">ForeignKey</span>(<span class="pl-s">"sample.id"</span>),
        <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">True</span>,
    ),
    <span class="pl-v">PrimaryKeyConstraint</span>(<span class="pl-s">"tag_id"</span>, <span class="pl-s">"sample_id"</span>),
)

<span class="pl-k">class</span> <span class="pl-v">Tag</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"tags"</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-c1">50</span>), <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-k">class</span> <span class="pl-v">BaseDataFile</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"base_data_file"</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">data_file_type</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>(<span class="pl-c1">50</span>))
    <span class="pl-s1">visible</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Boolean</span>())
    <span class="pl-s1">user_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(
        <span class="pl-v">Integer</span>,
        <span class="pl-v">ForeignKey</span>(
            <span class="pl-s">"appuser.id"</span>,
            <span class="pl-s1">onupdate</span><span class="pl-c1">=</span><span class="pl-s">"RESTRICT"</span>,
            <span class="pl-s1">ondelete</span><span class="pl-c1">=</span><span class="pl-s">"RESTRICT"</span>,
        ),
        <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">True</span>,
    )
    <span class="pl-s1">user_rel</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(<span class="pl-s">"User"</span>)
    <span class="pl-s1">project_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(
        <span class="pl-v">Integer</span>,
        <span class="pl-v">ForeignKey</span>(<span class="pl-s">"project.id"</span>,  <span class="pl-s1">ondelete</span><span class="pl-c1">=</span><span class="pl-s">"SET NULL"</span>),
        <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">True</span>,
        <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">True</span>,
    )
    <span class="pl-s1">project</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(<span class="pl-v">Project</span>)
    <span class="pl-s1">__mapper_args__</span> <span class="pl-c1">=</span> {<span class="pl-s">"polymorphic_identity"</span>: <span class="pl-s">"base_data_file"</span>, <span class="pl-s">"polymorphic_on"</span>: <span class="pl-s1">data_file_type</span>}

<span class="pl-k">class</span> <span class="pl-v">Sample</span>(<span class="pl-v">BaseDataFile</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"sample"</span>
    <span class="pl-s1">__mapper_args__</span> <span class="pl-c1">=</span> {<span class="pl-s">"polymorphic_identity"</span>: <span class="pl-s">"sample"</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-v">ForeignKey</span>(<span class="pl-s">"base_data_file.id"</span>),
        <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>,
    )
    <span class="pl-s1">tags</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(
        <span class="pl-s">"Tag"</span>, <span class="pl-s1">secondary</span><span class="pl-c1">=</span><span class="pl-s1">samples_tags</span>, <span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-en">backref</span>(<span class="pl-s">"samples"</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-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-s1">sessionmaker</span>

<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'sqlite:///:memory:'</span>, <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-c1">True</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-v">Session</span> <span class="pl-c1">=</span> <span class="pl-en">sessionmaker</span>(<span class="pl-s1">bind</span><span class="pl-c1">=</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">user_id</span> <span class="pl-c1">=</span> <span class="pl-c1">1</span>
<span class="pl-s1">user_sample_query</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">Sample</span>).<span class="pl-en">filter</span>(<span class="pl-v">Sample</span>.<span class="pl-s1">visible</span>.<span class="pl-en">is_</span>(<span class="pl-c1">True</span>), <span class="pl-v">Sample</span>.<span class="pl-s1">user_id</span> <span class="pl-c1">==</span> <span class="pl-s1">user_id</span>)
<span class="pl-s1">project_samples_query</span> <span class="pl-c1">=</span> (
    <span class="pl-s1">session</span>
        .<span class="pl-en">query</span>(<span class="pl-v">Sample</span>)
        .<span class="pl-en">join</span>(<span class="pl-v">ProjectPermissions</span>, <span class="pl-v">ProjectPermissions</span>.<span class="pl-s1">project_id</span> <span class="pl-c1">==</span> <span class="pl-v">Sample</span>.<span class="pl-s1">project_id</span>, <span class="pl-s1">isouter</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
        .<span class="pl-en">filter</span>(<span class="pl-v">Sample</span>.<span class="pl-s1">visible</span>.<span class="pl-en">is_</span>(<span class="pl-c1">True</span>), <span class="pl-v">Sample</span>.<span class="pl-s1">user_id</span> <span class="pl-c1">==</span> <span class="pl-s1">user_id</span>)
)
<span class="pl-s1">unioned</span> <span class="pl-c1">=</span> <span class="pl-s1">user_sample_query</span>.<span class="pl-en">union</span>(<span class="pl-s1">project_samples_query</span>)
<span class="pl-c"># q = unioned.options(joinedload("tags"))</span>
<span class="pl-s1">q</span> <span class="pl-c1">=</span> <span class="pl-s1">unioned</span>.<span class="pl-en">options</span>(<span class="pl-en">joinedload</span>(<span class="pl-s">"tags"</span>)).<span class="pl-en">limit</span>(<span class="pl-c1">10</span>)
<span class="pl-c"># q = session.query(Sample).filter(Sample.id.in_(aliased)).options(joinedload("tags")).limit(10)</span>
<span class="pl-en">print</span>(<span class="pl-en">str</span>(<span class="pl-s1">q</span>.<span class="pl-s1">statement</span>.<span class="pl-en">compile</span>(<span class="pl-s1">compile_kwargs</span><span class="pl-c1">=</span>{<span class="pl-s">"literal_binds"</span>: <span class="pl-c1">True</span>})))
<span class="pl-en">print</span>(<span class="pl-s">"==="</span>)
<span class="pl-en">print</span>(<span class="pl-s1">q</span>.<span class="pl-en">all</span>())</pre></div>
<p dir="auto">If you remove the <code class="notranslate">joinedload("tags")</code> or the <code class="notranslate">limit</code>, the query will work.</p>
<p dir="auto"><strong>Error</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py&quot;, line 1770, in _execute_context
    self.dialect.do_execute(
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py&quot;, line 717, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: ambiguous column name: anon_1.anon_2_sample_id

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

Traceback (most recent call last):
  File &quot;repro.py&quot;, line 121, in &lt;module&gt;
    print(q.all())
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py&quot;, line 2699, in all
    return self._iter().all()
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py&quot;, line 2834, in _iter
    result = self.session.execute(
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py&quot;, line 1677, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py&quot;, line 1582, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py&quot;, line 324, in _execute_on_connection
    return connection._execute_clauseelement(
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py&quot;, line 1451, in _execute_clauseelement
    ret = self._execute_context(
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py&quot;, line 1813, in _execute_context
    self._handle_dbapi_exception(
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py&quot;, line 1994, in _handle_dbapi_exception
    util.raise_(
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py&quot;, line 207, in raise_
    raise exception
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py&quot;, line 1770, in _execute_context
    self.dialect.do_execute(
  File &quot;/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py&quot;, line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) ambiguous column name: anon_1.anon_2_sample_id
[SQL: SELECT anon_1.anon_2_sample_id AS anon_1_anon_2_sample_id, anon_1.anon_2_base_data_file_data_file_type AS anon_1_anon_2_base_data_file_data_file_type, anon_1.anon_2_base_data_file_visible AS anon_1_anon_2_base_data_file_visible, anon_1.anon_2_base_data_file_user_id AS anon_1_anon_2_base_data_file_user_id, anon_1.anon_2_base_data_file_project_id AS anon_1_anon_2_base_data_file_project_id, tags_1.id AS tags_1_id, tags_1.name AS tags_1_name 
FROM (SELECT anon_2.sample_id AS anon_2_sample_id, anon_2.base_data_file_data_file_type AS anon_2_base_data_file_data_file_type, anon_2.base_data_file_visible AS anon_2_base_data_file_visible, anon_2.base_data_file_user_id AS anon_2_base_data_file_user_id, anon_2.base_data_file_project_id AS anon_2_base_data_file_project_id 
FROM (SELECT sample.id AS sample_id, base_data_file.id AS base_data_file_id, base_data_file.data_file_type AS base_data_file_data_file_type, base_data_file.visible AS base_data_file_visible, base_data_file.user_id AS base_data_file_user_id, base_data_file.project_id AS base_data_file_project_id 
FROM base_data_file JOIN sample ON base_data_file.id = sample.id 
WHERE base_data_file.visible IS 1 AND base_data_file.user_id = ? UNION SELECT sample.id AS sample_id, base_data_file.id AS base_data_file_id, base_data_file.data_file_type AS base_data_file_data_file_type, base_data_file.visible AS base_data_file_visible, base_data_file.user_id AS base_data_file_user_id, base_data_file.project_id AS base_data_file_project_id 
FROM base_data_file JOIN sample ON base_data_file.id = sample.id LEFT OUTER JOIN project_user ON project_user.project_id = base_data_file.project_id 
WHERE base_data_file.visible IS 1 AND base_data_file.user_id = ?) AS anon_2
 LIMIT ? OFFSET ?) AS anon_1 JOIN (SELECT anon_2.sample_id AS anon_2_sample_id, anon_2.base_data_file_data_file_type AS anon_2_base_data_file_data_file_type, anon_2.base_data_file_visible AS anon_2_base_data_file_visible, anon_2.base_data_file_user_id AS anon_2_base_data_file_user_id, anon_2.base_data_file_project_id AS anon_2_base_data_file_project_id 
FROM (SELECT sample.id AS sample_id, base_data_file.id AS base_data_file_id, base_data_file.data_file_type AS base_data_file_data_file_type, base_data_file.visible AS base_data_file_visible, base_data_file.user_id AS base_data_file_user_id, base_data_file.project_id AS base_data_file_project_id 
FROM base_data_file JOIN sample ON base_data_file.id = sample.id 
WHERE base_data_file.visible IS 1 AND base_data_file.user_id = ? UNION SELECT sample.id AS sample_id, base_data_file.id AS base_data_file_id, base_data_file.data_file_type AS base_data_file_data_file_type, base_data_file.visible AS base_data_file_visible, base_data_file.user_id AS base_data_file_user_id, base_data_file.project_id AS base_data_file_project_id 
FROM base_data_file JOIN sample ON base_data_file.id = sample.id LEFT OUTER JOIN project_user ON project_user.project_id = base_data_file.project_id 
WHERE base_data_file.visible IS 1 AND base_data_file.user_id = ?) AS anon_2
 LIMIT ? OFFSET ?) AS anon_1 ON base_data_file.id = anon_1.anon_2_sample_id LEFT OUTER JOIN (samples_tags AS samples_tags_1 JOIN tags AS tags_1 ON tags_1.id = samples_tags_1.tag_id) ON anon_1.anon_2_sample_id = samples_tags_1.sample_id]
[parameters: (1, 1, 10, 0, 1, 1, 10, 0)]
(Background on this error at: http://sqlalche.me/e/14/e3q8)
"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: ambiguous column name: anon_1.anon_2_sample_id

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

Traceback (most recent call last):
  File "repro.py", line 121, in &lt;module&gt;
    print(q.all())
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2699, in all
    return self._iter().all()
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2834, in _iter
    result = self.session.execute(
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1677, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 324, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1451, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1813, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1994, in _handle_dbapi_exception
    util.raise_(
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/Users/vincentprouillet/Code/pulls/sqlalchemy-repro/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) ambiguous column name: anon_1.anon_2_sample_id
[SQL: SELECT anon_1.anon_2_sample_id AS anon_1_anon_2_sample_id, anon_1.anon_2_base_data_file_data_file_type AS anon_1_anon_2_base_data_file_data_file_type, anon_1.anon_2_base_data_file_visible AS anon_1_anon_2_base_data_file_visible, anon_1.anon_2_base_data_file_user_id AS anon_1_anon_2_base_data_file_user_id, anon_1.anon_2_base_data_file_project_id AS anon_1_anon_2_base_data_file_project_id, tags_1.id AS tags_1_id, tags_1.name AS tags_1_name 
FROM (SELECT anon_2.sample_id AS anon_2_sample_id, anon_2.base_data_file_data_file_type AS anon_2_base_data_file_data_file_type, anon_2.base_data_file_visible AS anon_2_base_data_file_visible, anon_2.base_data_file_user_id AS anon_2_base_data_file_user_id, anon_2.base_data_file_project_id AS anon_2_base_data_file_project_id 
FROM (SELECT sample.id AS sample_id, base_data_file.id AS base_data_file_id, base_data_file.data_file_type AS base_data_file_data_file_type, base_data_file.visible AS base_data_file_visible, base_data_file.user_id AS base_data_file_user_id, base_data_file.project_id AS base_data_file_project_id 
FROM base_data_file JOIN sample ON base_data_file.id = sample.id 
WHERE base_data_file.visible IS 1 AND base_data_file.user_id = ? UNION SELECT sample.id AS sample_id, base_data_file.id AS base_data_file_id, base_data_file.data_file_type AS base_data_file_data_file_type, base_data_file.visible AS base_data_file_visible, base_data_file.user_id AS base_data_file_user_id, base_data_file.project_id AS base_data_file_project_id 
FROM base_data_file JOIN sample ON base_data_file.id = sample.id LEFT OUTER JOIN project_user ON project_user.project_id = base_data_file.project_id 
WHERE base_data_file.visible IS 1 AND base_data_file.user_id = ?) AS anon_2
 LIMIT ? OFFSET ?) AS anon_1 JOIN (SELECT anon_2.sample_id AS anon_2_sample_id, anon_2.base_data_file_data_file_type AS anon_2_base_data_file_data_file_type, anon_2.base_data_file_visible AS anon_2_base_data_file_visible, anon_2.base_data_file_user_id AS anon_2_base_data_file_user_id, anon_2.base_data_file_project_id AS anon_2_base_data_file_project_id 
FROM (SELECT sample.id AS sample_id, base_data_file.id AS base_data_file_id, base_data_file.data_file_type AS base_data_file_data_file_type, base_data_file.visible AS base_data_file_visible, base_data_file.user_id AS base_data_file_user_id, base_data_file.project_id AS base_data_file_project_id 
FROM base_data_file JOIN sample ON base_data_file.id = sample.id 
WHERE base_data_file.visible IS 1 AND base_data_file.user_id = ? UNION SELECT sample.id AS sample_id, base_data_file.id AS base_data_file_id, base_data_file.data_file_type AS base_data_file_data_file_type, base_data_file.visible AS base_data_file_visible, base_data_file.user_id AS base_data_file_user_id, base_data_file.project_id AS base_data_file_project_id 
FROM base_data_file JOIN sample ON base_data_file.id = sample.id LEFT OUTER JOIN project_user ON project_user.project_id = base_data_file.project_id 
WHERE base_data_file.visible IS 1 AND base_data_file.user_id = ?) AS anon_2
 LIMIT ? OFFSET ?) AS anon_1 ON base_data_file.id = anon_1.anon_2_sample_id LEFT OUTER JOIN (samples_tags AS samples_tags_1 JOIN tags AS tags_1 ON tags_1.id = samples_tags_1.tag_id) ON anon_1.anon_2_sample_id = samples_tags_1.sample_id]
[parameters: (1, 1, 10, 0, 1, 1, 10, 0)]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

</code></pre></div>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: OSX</li>
<li>Python: 3.8.3</li>
<li>SQLAlchemy: 1.4.17</li>
<li>Database: All</li>
<li>DBAPI:</li>
</ul>
<p dir="auto"><strong>Additional context</strong></p>
<p dir="auto">We are running into it with Postgresql in prod but I made the example with sqlite for simplicity. It raises a <code class="notranslate">DuplicateAlias</code> on pg.<br>
Another thing interesting is that the <code class="notranslate">UNION</code> is present twice in the query, presumably for the polymorphic tables? Seems a bit odd to me but I don't know much about them.</p>
<p dir="auto">I've fixed it for us by changing the union to just fetch the ids, union them via <code class="notranslate">union(user_sample_query, project_samples_query).alias(name="visible_samples")</code> (name not required but nicer than anon etc) and <code class="notranslate">.filter(Sample.id.in_(aliased_union))</code></p>
<p dir="auto"><strong>Have a nice day!</strong></p>
<p dir="auto">Not exactly sure if this is a bug or feature request, but heres what I ran into.<br>
Attributes of base class cannot be validated from subclasses.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employee'

    id = Column(Integer, primary_key=True)
    type = Column(String(20), nullable=False)
    name = Column(String(50))

    __mapper_args__ = {
        'polymorphic_on': type,
        'polymorphic_identity': 'employee'
    }


class Manager(Employee):
    __tablename__ = 'manager'

    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    title = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity': 'manager'
    }

    @validates('name')
    def validate_name(self, key, name):
        assert name == 'mr. manager'
        return name

boss = Manager()
boss.name = 'john'    # doesn't validate name"><pre class="notranslate"><span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()

<span class="pl-k">class</span> <span class="pl-v">Employee</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'employee'</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">type</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>(<span class="pl-c1">20</span>), <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</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-c1">50</span>))

    <span class="pl-s1">__mapper_args__</span> <span class="pl-c1">=</span> {
        <span class="pl-s">'polymorphic_on'</span>: <span class="pl-s1">type</span>,
        <span class="pl-s">'polymorphic_identity'</span>: <span class="pl-s">'employee'</span>
    }


<span class="pl-k">class</span> <span class="pl-v">Manager</span>(<span class="pl-v">Employee</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'manager'</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-v">ForeignKey</span>(<span class="pl-s">'employee.id'</span>), <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">title</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>(<span class="pl-c1">50</span>))

    <span class="pl-s1">__mapper_args__</span> <span class="pl-c1">=</span> {
        <span class="pl-s">'polymorphic_identity'</span>: <span class="pl-s">'manager'</span>
    }

    <span class="pl-en">@<span class="pl-en">validates</span>(<span class="pl-s">'name'</span>)</span>
    <span class="pl-k">def</span> <span class="pl-en">validate_name</span>(<span class="pl-s1">self</span>, <span class="pl-s1">key</span>, <span class="pl-s1">name</span>):
        <span class="pl-k">assert</span> <span class="pl-s1">name</span> <span class="pl-c1">==</span> <span class="pl-s">'mr. manager'</span>
        <span class="pl-k">return</span> <span class="pl-s1">name</span>

<span class="pl-s1">boss</span> <span class="pl-c1">=</span> <span class="pl-v">Manager</span>()
<span class="pl-s1">boss</span>.<span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-s">'john'</span>    <span class="pl-c"># doesn't validate name</span></pre></div>