New testcase for fix 8a20d277c52bae6863512c90c64fba2e451950e3

Issue #3691 resolved
Eoghan Murray
created an issue

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.

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:

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"):

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!

Comments (3)

  1. Michael Bayer repo owner

    the fix you see I likely committed after pushing this up to CI and observing failures (I'm still working on having CI available before things are pushed to master).

    Check out the bad revision and run py.test test/sql/test_query.py, two failures:

    =================================================================== FAILURES ====================================================================
    ___________________________________________ QueryTest_sqlite_pysqlite.test_column_accessor_sqlite_raw ___________________________________________
    Traceback (most recent call last):
      File "/home/classic/dev/sqlalchemy/test/sql/test_query.py", line 895, in test_column_accessor_sqlite_raw
        assert 'user_id' not in r
    AssertionError: assert 'user_id' not in (1, u'john')
    _______________________________________ QueryTest_sqlite_pysqlite.test_column_accessor_sqlite_translated ________________________________________
    Traceback (most recent call last):
      File "/home/classic/dev/sqlalchemy/test/sql/test_query.py", line 915, in test_column_accessor_sqlite_translated
        eq_(r['query_users.user_id'], 1)
      File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/result.py", line 72, in __getitem__
        processor, obj, index = self._parent._key_fallback(key)
      File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/result.py", line 327, in _key_fallback
        expression._string_or_unprintable(key))
    NoSuchColumnError: "Could not locate column in row for column 'query_users.user_id'"
    ============================================================ short test summary info ============================================================
    FAIL test/sql/test_query.py::QueryTest_sqlite_pysqlite::()::test_column_accessor_sqlite_raw
    FAIL test/sql/test_query.py::QueryTest_sqlite_pysqlite::()::test_column_accessor_sqlite_translated
    ================================================ 2 failed, 115 passed, 6 skipped in 0.65 seconds ================================================
    
  2. Log in to comment