sqlalchemy: Tables with the same name (in different schemas) doesn't schema-qualifiy or disambiguate the local table name

note to users

a fix here is targeted at 2.0. for a decent engine-level configuration to work around this on PostgreSQL, MySQL/MariaDB, SQLite for 1.4/1.3, see https://github.com/sqlalchemy/sqlalchemy/issues/7471#issuecomment-997207701

Describe the bug

When you have

• two tables with the same name
• one of those tables lives in the public schema
• the other is some other schema
• and both tables show up in a join on a query

the public one does not qualify itself, and becomes ambiguous in the query

I would ideally want this to qualify the query with public. (either through the dialect’s default schema or i guess perhaps this relates to the search_path), or else for sqlalchemy to realize this is about to happen and internally alias the ambiguous table

To Reproduce

from sqlalchemy import Column, create_engine, ForeignKey, Integer
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker

Base = declarative_base()


class Foo(Base):
    __tablename__ = "foo"
    __table_args__ = dict(schema="foo")

    id = Column(Integer, primary_key=True)


class Bar(Base):
    __tablename__ = "foo"

    id = Column(ForeignKey("foo.foo.id"), primary_key=True)

    foo = relationship("Foo", uselist=False)


engine = create_engine(
    URL(
        host="localhost",
        drivername="postgresql+psycopg2",
        username="user",
        password="password",
        database="postgres",
        port="5532",
    )
)


engine.execute("drop schema if exists foo cascade")
engine.execute("create schema foo")
Base.metadata.create_all(engine)
Session = sessionmaker()
session = Session(bind=engine)

session.query(Bar).join(Foo).all()

Error

/foo/meow.py:26: SADeprecationWarning: Calling URL() directly is deprecated and will be disabled in a future release.  The public constructor for URL is now the URL.create() method.
  URL(
Traceback (most recent call last):
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.AmbiguousAlias: table reference "foo" is ambiguous
LINE 2: FROM foo JOIN foo.foo ON foo.foo.id = foo.id
                                              ^


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

Traceback (most recent call last):
  File "/foo/meow.py", line 43, in <module>
    session.query(Bar).join(Foo).all()
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2759, in all
    return self._iter().all()
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2894, in _iter
    result = self.session.execute(
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
    ret = self._execute_context(
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
    self._handle_dbapi_exception(
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
    util.raise_(
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/foo/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.AmbiguousAlias) table reference "foo" is ambiguous
LINE 2: FROM foo JOIN foo.foo ON foo.foo.id = foo.id
                                              ^

[SQL: SELECT foo.id AS foo_id
FROM foo JOIN foo.foo ON foo.foo.id = foo.id]

Versions

• Python: 3.7+
• SQLAlchemy: 1.3+
• Database: postgres
• DBAPI: psycopg2

Additional context

No response