Issue 1:
If you query a model (e.g. with session.query) and filter on it, then join to a second model with .join, then use .with_entities to select that second model (the one you just joined to), SQLAlchemy will include the second model in the FROM clause. This can cause issues later in the query. Version 1.3 did not include that second model in FROM.

Example

If you run this code:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DeclarativeBase = declarative_base()
Session = sessionmaker()
session = Session()

class First(DeclarativeBase):
__tablename__ = "first"

pk = sa.Column(sa.Integer, primary_key=True)

class Mapper(DeclarativeBase):
__tablename__ = "mapper"

pk = sa.Column(sa.Integer, primary_key=True)
first_pk = sa.Column(sa.Integer, sa.ForeignKey("first.pk"), nullable=False)
second_pk = sa.Column(sa.Integer, sa.ForeignKey("second.pk"), nullable=False)

class Second(DeclarativeBase):
__tablename__ = "second"

pk = sa.Column(sa.Integer, primary_key=True)

query = session.query(First).filter(First.pk != 1).join(
Mapper,
Mapper.first_pk == First.pk,
).join(
Second,
Second.pk == Mapper.second_pk,
).with_entities(Second)

if __name__ == "__main__":
print(query.statement)

You get an output of:

SELECT second.pk
FROM first, second JOIN mapper ON mapper.first_pk = first.pk JOIN second ON second.pk = mapper.second_pk
WHERE first.pk != :pk_1

This is invalid, as the first JOIN references the table in the first FROM. Running this query in Postgresql gives an error message like:

invalid reference to FROM-clause entry for table "first" Hint: There is an entry for table "first", but it cannot be referenced from this part of the query.

In SQLAlchemy 1.3.23 I get this perfectly valid SQL output instead:

SELECT second.pk
FROM first JOIN mapper ON mapper.first_pk = first.pk JOIN second ON second.pk = mapper.second_pk
WHERE first.pk != :pk_1

In my case I think I can pretty easily work around the issue by rewriting the query but I figured I should report it since a straight upgrade caused a test to fail 😅. Thanks!

Versions.

OS: macOS 11.3.1

Python: 3.8.9

SQLAlchemy: 1.4.15

Database: Postgres 10.12

DBAPI: psycopg2

Issue 2:
Describe the bug

Very similar to #6052 (which is supposedly fixed?), applying any loader option breaks with_entities().

Expected behavior

No error, this output from the test script:

normal
[<Thing 1>]
with entities + noload
[(1,)]
with entities + joinedload
[(1,)]

To Reproduce

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base # to test on 1.3
from sqlalchemy.orm import *

Base = declarative_base()

class Thing(Base):
__tablename__ = 'things'
id = Column(Integer, primary_key=True)
owner_id = Column(Integer, ForeignKey('users.id'), nullable=True, index=True)
owner = relationship('User', backref='things')

def __repr__(self):
return f'<Thing {self.id}>'

class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)

def __repr__(self):
return f'<User {self.id}>'

e = create_engine('postgresql:///test', echo=False)
Base.metadata.create_all(e)
s = Session(e)

if not s.query(Thing).count():
thing = Thing(owner=User())
s.add(thing)
s.commit()

print('normal')
print(s.query(Thing).all())
print('with entities + noload') # fails
print(s.query(Thing).options(noload('owner')).with_entities(Thing.id).all())
print('with entities + joinedload') # fails
print(s.query(Thing).options(joinedload('owner')).with_entities(Thing.id).all())

Error

normal
[<Thing 1>]
with entities + noload
Traceback (most recent call last):
File "/home/adrian/dev/indico/py3/src/satest.py", line 40, in <module>
print(s.query(Thing).options(noload('owner')).with_entities(Thing.id).all())
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2685, in all
return self._iter().all()
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2820, in _iter
result = self.session.execute(
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1670, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1520, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 535, in _compile_w_cache
compiled_sql = self._compiler(
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 570, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 766, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 455, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 490, in process
return obj._compiler_dispatch(self, **kwargs)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 2966, in visit_select
compile_state = select_stmt._compile_state_factory(
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/sql/base.py", line 492, in create_for_statement
return klass.create_for_statement(statement, compiler, **kw)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/orm/context.py", line 579, in create_for_statement
opt.process_compile_state(self)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 182, in process_compile_state
self._process(compile_state, not bool(compile_state.current_path))
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 705, in _process
val._bind_loader(
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 852, in _bind_loader
entity = self._find_entity_basestring(entities, token, raiseerr)
File "/home/adrian/dev/indico/py3/env/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 983, in _find_entity_basestring
raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Query has only expression-based entities - can't find property named "owner".

Additional context

The example script is minimal, hence the "strange" usage of with_entities. In my real application the query is coming from a util function which applies a joinedload option, and overriding it with a noload doesn't help because any load option triggers this bug.

Versions.

OS: Linux

Python: 3.9.1

SQLAlchemy: 1.4.7

Database: Postgres

DBAPI: psycopg2