[Sqlalchemy-tickets] Issue #4334: Query.select_from_entity doesn't apply adaptation in the context of lateral joins (zzzeek/sqlalchemy)

[zifot <iss...@bi...>]

New issue 4334: Query.select_from_entity doesn't apply adaptation in the context of lateral joins
https://bitbucket.org/zzzeek/sqlalchemy/issues/4334/queryselect_from_entity-doesnt-apply

zifot:

Based on the documentation for [`select_entity_from`](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.select_entity_from), specifically the part stating that* "it additionally applies adaptation to the other parts of the query that refer to the primary entity"*, I was expecting a proper adaptation inside lateral join subquery. This seems not to be the case:

```python
from sqlalchemy import create_engine, Column, Integer, Text, ForeignKey
from sqlalchemy.orm import Query
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('sqlite:///test.db')
Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parents'

    id = Column(Integer, primary_key=True)
    name = Column(Text)


class Child(Base):
    __tablename__ = 'children'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Parent.id))
    name = Column(Text)


Base.metadata.create_all(engine)


parents_subquery = (
    Query(Parent)
    .subquery()
)
child_subquery = (
    Query(Child)
    .filter(Child.name == Parent.name)
    .subquery()
    .lateral()
)

print(
    '\n---Entity directly: As expected, here joined subquery correlates to parents table of'
    ' root query'
)
q = Query(Parent)
q = q.outerjoin(child_subquery)
q = q.filter(Parent.name == 'y')
print(q)

print(
    '\n---Entity via select_entity_from: Here joined subquery uses its own'
    ' instance of parents table (although final where gets adapted properly) '
)
q = Query(Parent).select_entity_from(parents_subquery)
q = q.outerjoin(child_subquery)
q = q.filter(Parent.name == 'y')
print(q)
```

This results in:

```shell
---Entity directly: As expected, here joined subquery correlates to parents table of root query
SELECT parents.id AS parents_id, parents.name AS parents_name 
FROM parents LEFT OUTER JOIN LATERAL (SELECT children.id AS id, children.parent_id AS parent_id, children.name AS name 
FROM children 
WHERE children.name = parents.name) AS anon_1 ON parents.id = anon_1.parent_id 
WHERE parents.name = :name_1

---Entity via select_entity_from: Here joined subquery uses its own instance of parents table (although final where gets adapted properly) 
SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name 
FROM (SELECT parents.id AS id, parents.name AS name 
FROM parents) AS anon_1 LEFT OUTER JOIN LATERAL (SELECT children.id AS id, children.parent_id AS parent_id, children.name AS name 
FROM children, parents 
WHERE children.name = parents.name) AS anon_2 ON anon_1.id = anon_2.parent_id 
WHERE anon_1.name = :name_1
```

Using `parents_subquery.c.name` instead of `Parent.name` when constructing `child_subquery` yields expected statement, but that requires tagging along a reference to subquery everywhere it's needed and seems to defeat the purpose of `select_entity_from`.

Tested on SQLAlchemy 1.1.6 and 1.2.11.