[Sqlalchemy-tickets] Issue #3691: New testcase for fix 8a20d277c52bae6863512c90c64fba2e451950e3 (zzzeek/sqlalchemy)

[Eoghan M. <iss...@bi...>]

New issue 3691: New testcase for fix 8a20d277c52bae6863512c90c64fba2e451950e3
https://bitbucket.org/zzzeek/sqlalchemy/issues/3691/new-testcase-for-fix

Eoghan Murray:

Hi,

I ran into a bug that has been fixed on the main branch.

Bug was introduced here:
https://github.com/zzzeek/sqlalchemy/commit/edec583b459e955a30d40b5c5d8baaed0a2ec1c6

Fix was here:
https://github.com/zzzeek/sqlalchemy/commit/8a20d277c52bae6863512c90c64fba2e451950e3

I'm not sure if anyone is aware that the following use case was also broken.

I tried to write this up as a testcase but got lost in how to set up the fixtures or whether I should be reusing existing ones.


```
#!python

class Holder(Base):
    __tablename__ = 'holder'
    id = Column(Integer, primary_key=True)
    child_a_id = Column(Integer, ForeignKey('point.id'))
    child_a = relationship('Point', foreign_keys=[child_a_id])
    child_b_id = Column(Integer, ForeignKey('point.id'))
    child_b = relationship('Point', foreign_keys=[child_b_id])

class Point(Base):
    __tablename__ = 'point'
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry(Point(2)))
    coords = column_property(func.ST_AsEWKT(geom))

#Base.metadata.create_all(get_engine())

session.begin()
p1 = Point(id=1, geom='0101000020E6100000B03572FABE56C0BF8FCFACECEFC04940')
p2 = Point(id=2, geom='0101000020E610000031661253815EFEBF0143B9769D3D4A40')
h1 = Holder(id=1)
h1.child_a = p1
h1.child_b = p2
session.commit()
session.begin()

session.query(Holder).options(eagerload('child_a'), eagerload('child_b')).all()
```

Expected SQL:


```
#!sql

SELECT holder.id AS holder_id, holder.child_a_id AS holder_child_a_id, 
holder.child_b_id AS holder_child_b_id, ST_AsEWKT(point_1.geom) AS 
"ST_AsEWKT_1", point_1.id AS point_1_id, point_1.geom AS point_1_geom, 
ST_AsEWKT(point_2.geom) AS "ST_AsEWKT_2", point_2.id AS point_2_id, 
point_2.geom AS point_2_geom 
FROM holder LEFT OUTER JOIN point AS point_1 ON point_1.id = 
holder.child_a_id LEFT OUTER JOIN point AS point_2 ON point_2.id = 
holder.child_b_id;
```

Actual SQL  (incorrect 2nd "ST_AsEWKT_1"):


```
#!sql

SELECT holder.id AS holder_id, holder.child_a_id AS holder_child_a_id, 
holder.child_b_id AS holder_child_b_id, ST_AsEWKT(point_1.geom) AS 
"ST_AsEWKT_1", point_1.id AS point_1_id, point_1.geom AS point_1_geom, 
ST_AsEWKT(point_2.geom) AS "ST_AsEWKT_1", point_2.id AS point_2_id, 
point_2.geom AS point_2_geom 
FROM holder LEFT OUTER JOIN point AS point_1 ON point_1.id = 
holder.child_a_id LEFT OUTER JOIN point AS point_2 ON point_2.id = 
holder.child_b_id;
```

I'd be interested to see how this should be incorporated into a test case!


Responsible: zzzeek