order_by is failing

[kabulov]

attempt to get sorted data

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.dialects import registry
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

registry.register("yql.ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("yql", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")

engine = create_engine("yql+ydb://localhost:2136/local")
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class Table(Base):
    __tablename__ = "mytable"
    col = Column(Integer, primary_key=True)

Base.metadata.create_all(engine)

session.add(Table(col=1))
session.commit()

q = session.query(Table).order_by(Table.col).first()
print(q.col)

fails with following error message:

sqlalchemy.exc.DataError: (ydb_sqlalchemy.dbapi.errors.DataError) position { row: 2 column: 23 } message: "Column col is not in source column set" end_position { row: 2 column: 23 } severity: 1 (server_code: 400080)
[SQL: SELECT mytable.col AS mytable_col 
FROM mytable ORDER BY mytable.col
 LIMIT CAST(%(param_1)s AS UInt64)]
[parameters: {'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

although this works

q = session.query(Table.col.label("labelled_col")).order_by("labelled_col").first()
print(q.labelled_col)

but its inconvenient.

it may be related to label style:
https://docs.sqlalchemy.org/en/14/core/selectable.html#label-style-constants

if we change LABEL_STYLE_TABLENAME_PLUS_COL to LABEL_STYLE_DISAMBIGUATE_ONLY in this row:
https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/orm/context.py#L1073
order_by works without having to use label.

tried to use set_label_style or _label_style in MySession as in:
https://stackoverflow.com/questions/75322094/sqlalchemy-relationship-fields-name-constructor
but it doesnt help (need label)

using sqlalchemy 2+.

postgresql dialect works fine without relabelling.

[rekby]

Sqlalchemy ORM has not supported yet #33