Issue 1:
Describe the bug

Duplicate anon names used in queries with union, limit and joinedload causing the query to fail at the driver level.

This query used to work before 1.4.

To Reproduce

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__ = "appuser"
id = Column(Integer(), primary_key=True)

class Project(Base):
__tablename__ = "project"
id = Column(Integer(), primary_key=True)

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

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

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

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

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

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("tags"))
q = unioned.options(joinedload("tags")).limit(10)
# q = session.query(Sample).filter(Sample.id.in_(aliased)).options(joinedload("tags")).limit(10)
print(str(q.statement.compile(compile_kwargs={"literal_binds": True})))
print("===")
print(q.all())

If you remove the joinedload("tags") or the limit, the query will work.

Error

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

Versions.

OS: OSX

Python: 3.8.3

SQLAlchemy: 1.4.17

Database: All

DBAPI:

Additional context

We are running into it with Postgresql in prod but I made the example with sqlite for simplicity. It raises a DuplicateAlias on pg.

Another thing interesting is that the UNION 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.

I've fixed it for us by changing the union to just fetch the ids, union them via union(user_sample_query, project_samples_query).alias(name="visible_samples") (name not required but nicer than anon etc) and .filter(Sample.id.in_(aliased_union))

Have a nice day!

Issue 2:
Not exactly sure if this is a bug or feature request, but heres what I ran into.

Attributes of base class cannot be validated from subclasses.

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