SQLAlchemy中select语句order_by+distinct报错，session.query正常

问题背景 #

用SQLAlchemy 1.4.28搭配MySQL 8.0.19做开发，在多表关联后执行order_by和distinct操作时遇到两个问题：

• 用session.query接口执行完全正常
• 用原生select配合session.execute执行时报错，手动补全字段后SQL在MySQL端返回行数一致，但Python端拿到的结果数量不一样

报错信息 #

3065, "Expression #1 of ORDER BY clause is not in SELECT list, references column 'my_db.fault.jira_ticket' which is not in SELECT list; this is incompatible with DISTINCT")

代码对比 #

1. session.query 正常执行代码 #

sess.query(CM, CM.cm_id).where(CM.status == "created").join(Fault).join(NewFaultMap).order_by(Fault.jira_ticket).distinct().limit(1).all()

现象：生成的SQL会自动把order_by里的Fault.jira_ticket加到SELECT列表里，执行没有问题。

2. 原生select 报错代码 #

sess.execute(select(CM, CM.cm_id).where(CM.status == "created").join(Fault).join(NewFaultMap).order_by(Fault.jira_ticket).distinct().limit(1)).all()

现象：因为order_by引用的Fault.jira_ticket不在SELECT列表里触发报错。手动把这个字段加进去后，生成的SQL和session.query的一致，MySQL端执行返回的行数相同，但Python端结果数量有差异：

• session.query结果长度：164
• 添加字段后的select结果长度：167

模型定义 #

class CustomBase(Timestamp):
    """
    Custom base class which will add
    created and updated fields automatically to the newly created table
    __repr__ method
    """

    def as_dict(self):
        return {k: v for k, v in vars(self).items() if not k.startswith("_sa")}


CustomModelBase: Any = declarative_base(cls=CustomBase)


class NewFaultMap(CustomModelBase):
    __tablename__ = "faultmap"

    id = Column("id", Integer, primary_key=True)
    check_id = Column(String(96), unique=True, nullable=False)

    # One to many relationship with Fault class
    faults = relationship("Fault", back_populates="faultmap", lazy="selectin")


class CM(CustomModelBase):
    __tablename__ = "cm"

    cm_id = Column("id", Integer(), primary_key=True)
    status = Column("status", String(32), nullable=False)
    created_by = Column("created_by", String(64), default="")
    updated_by = Column("updated_by", String(64), default="")


    # One to many relationship with Fault class
    faults = relationship(
        "Fault",
        back_populates="cm",
        lazy="selectin",
        cascade="all, delete-orphan",
    )


class Fault(CustomModelBase):
    __tablename__ = "fault"

    id = Column("id", Integer(), primary_key=True)
    cm_id = Column(
        Integer,
        ForeignKey("cm.id", ondelete="CASCADE"),
        index=True,
    )
    check_id = Column(
        String(96),
        ForeignKey("faultmap.check_id", onupdate="CASCADE"),
        nullable=False,
    )
    jira_ticket = Column("jira_ticket", String(64), default="")
    cm = relationship("CM", back_populates="faults", lazy="selectin")
    faultmap = relationship("NewFaultMap", back_populates="faults", lazy="selectin")

问题原因及解决方案 #

1. 原生select报错原因 #

SQLAlchemy 1.4里，session.query和新的2.0风格select接口处理distinct+order_by的逻辑不一样：

• session.query会自动把order_by里的字段隐式加到SELECT列表，满足MySQL对DISTINCT的约束（ORDER BY的列必须出现在SELECT列表中）
• 新的select接口更贴近原生SQL，不会自动补全字段，必须显式指定所有需要的字段

2. Python端结果数量差异原因 #

手动添加Fault.jira_ticket到select后，返回的结果元组包含CM对象、CM.cm_id、Fault.jira_ticket三个元素；而session.query(CM, CM.cm_id)返回的是(CM对象, cm_id)的元组。更关键的是，SQLAlchemy的ORM层会对返回的CM对象自动去重（因为distinct），但原生select返回的是原始行数据，同一CM对象可能对应不同的jira_ticket，导致计数变多。

正确解决方案 #

方案一：用ORM层面的去重逻辑

如果只需要去重后的CM对象，直接查询CM并配合distinct(CM.cm_id)，SQLAlchemy会自动处理字段并保证ORM去重：

result = sess.execute(
    select(CM)
    .where(CM.status == "created")
    .join(Fault)
    .join(NewFaultMap)
    .order_by(Fault.jira_ticket)
    .distinct(CM.cm_id)
    .limit(1)
).scalars().all()
print(len(result))  # 和session.query结果一致

方案二：显式指定字段后手动去重

如果要保留CM+cm_id的返回结构，补全字段后在Python端按CM对象的主键去重：

result = sess.execute(
    select(CM, CM.cm_id, Fault.jira_ticket)
    .where(CM.status == "created")
    .join(Fault)
    .join(NewFaultMap)
    .order_by(Fault.jira_ticket)
    .distinct()
    .limit(1)
).all()
# 按CM的cm_id去重
unique_result = list({item[0].cm_id: item for item in result}.values())
print(len(unique_result))  # 输出164

方案三：继续使用session.query兼容写法

如果暂时不想切换到新的select接口，直接沿用session.query的写法即可：

result = sess.query(CM, CM.cm_id).where(CM.status == "created").join(Fault).join(NewFaultMap).order_by(Fault.jira_ticket).distinct().limit(1).all()

内容的提问来源于stack exchange，提问作者Rohit