SQLAlchemy Bug: Sort by many-to-many count + eager loading of many-to-one.

first_query.sql

SELECT parent.id AS parent_id,
       parent.name AS parent_name,
       count(links.source_id) AS link_count
FROM parent
LEFT OUTER JOIN links ON links.source_id = parent.id
AND links.distance <= ?
GROUP BY parent.id
ORDER BY link_count DESC
LIMIT ?
OFFSET ?

output.txt

[]
/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:559: SAWarning: Column 'link_count' on table <sqlalchemy.sql.selectable.Select at 0x7f9ce9a5a160; Select object> being replaced by <sqlalchemy.sql.elements.ColumnClause at 0x7f9ce9a77130; link_count>, which has the same key.  Consider use_labels for select() statements.
  util.warn(
/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:559: SAWarning: Column 'link_count' on table <sqlalchemy.sql.selectable.Alias at 0x7f9ce9f98d30; %(140311917071664 anon)s> being replaced by <sqlalchemy.sql.elements.ColumnClause at 0x7f9ce9a77310; link_count>, which has the same key.  Consider use_labels for select() statements.
  util.warn(
Traceback (most recent call last):
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: link_count

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

Traceback (most recent call last):
  File "/home/stepan/PycharmProjects/sql-alchemy-report/reproduce.py", line 33, in <module>
    print([(file.name, count) for file, count in second_query.all()])
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3346, in all
    return list(self)
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3508, in __iter__
    return self._execute_and_instances(context)
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3533, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/stepan/PycharmProjects/sql-alchemy-report/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: link_count
[SQL: SELECT anon_1.parent_id AS anon_1_parent_id, anon_1.parent_name AS anon_1_parent_name, anon_1.link_count, child_1.id AS child_1_id, child_1.parent_id AS child_1_parent_id, child_1.value AS child_1_value 
FROM (SELECT parent.id AS parent_id, parent.name AS parent_name, count(links.source_id) AS link_count, link_count 
FROM parent LEFT OUTER JOIN links ON links.source_id = parent.id AND links.distance <= ? GROUP BY parent.id ORDER BY link_count DESC
 LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN child AS child_1 ON anon_1.parent_id = child_1.parent_id ORDER BY anon_1.link_count DESC]
[parameters: (0.8, 20, 0)]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

reproduce.py

from sqlalchemy import create_engine, func, literal_column
from sqlalchemy.orm import sessionmaker, joinedload

from schema import Parent, Link, Base

engine = create_engine('sqlite://')
Session = sessionmaker(bind=engine)

if __name__ == '__main__':
    # Create schema
    Base.metadata.create_all(bind=engine)
    session = Session()

    on_short_link = (Link.source_id == Parent.id) & (Link.distance <= 0.8)

    first_query = session. \
        query(Parent, func.count(Link.target_id).label("link_count")). \
        outerjoin(Link, on_short_link). \
        group_by(Parent.id). \
        order_by(literal_column("link_count").desc()). \
        limit(20).offset(0)

    print([(file.name, count) for file, count in first_query.all()])

    second_query = session. \
        query(Parent, func.count(Link.target_id).label("link_count")). \
        options(joinedload(Parent.children)). \
        outerjoin(Link, on_short_link). \
        group_by(Parent.id). \
        order_by(literal_column("link_count").desc()). \
        limit(20).offset(0)

    print([(file.name, count) for file, count in second_query.all()])

schema.py

from sqlalchemy import Column, Integer, Float, ForeignKey, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    children = relationship("Child")


class Link(Base):
    """Association object with extra fields implementing Many-to-Many relationship between parents."""

    __tablename__ = 'links'
    source_id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
    target_id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
    distance = Column(Float, nullable=False)


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'), nullable=False)
    value = Column(Integer)

second_query.sql

SELECT anon_1.parent_id AS anon_1_parent_id,
       anon_1.parent_name AS anon_1_parent_name,
       anon_1.link_count,
       child_1.id AS child_1_id,
       child_1.parent_id AS child_1_parent_id,
       child_1.value AS child_1_value
FROM
  (SELECT parent.id AS parent_id,
          parent.name AS parent_name,
          count(links.source_id) AS link_count,
          link_count # <<<<<<<<< THIS IS A BUG
   FROM parent
   LEFT OUTER JOIN links ON links.source_id = parent.id
   AND links.distance <= ?
   GROUP BY parent.id
   ORDER BY link_count DESC
   LIMIT ?
   OFFSET ?) AS anon_1
LEFT OUTER JOIN child AS child_1 ON anon_1.parent_id = child_1.parent_id
ORDER BY anon_1.link_count DESC