为何SQLAlchemy未显式过滤却仅返回DatasetImages部分结果？

基于SQLAlchemy ORM DeclarativeBase规范定义了如下模型类：

from sqlalchemy import String, DateTime, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.ext.declarative import DeclarativeBase
import datetime
from typing import Optional, List

class Base(DeclarativeBase):
    pass

class ImageMetadata(Base):
    __tablename__ = "image_metadata"
    main_id: Mapped[str] = mapped_column(String(30), primary_key=True)
    secondary_id: Mapped[str] = mapped_column(String(30))
    mission_id: Mapped[str] = mapped_column(String(30))
    sensing_time: Mapped[DateTime] = mapped_column(DateTime)
    cloud_cover: Mapped[Optional[float]]
    north_lat: Mapped[float]
    south_lat: Mapped[float]
    west_lon: Mapped[float]
    east_lon: Mapped[float]
    base_url: Mapped[str] = mapped_column(String(120))
    status: Mapped[str] = mapped_column(String(30))
    images: Mapped[Optional[List["BandMetadata"]]] = relationship(back_populates="band_metadata",
                                                                  cascade="all, delete-orphan", default_factory=list)
    dataset_images: Mapped[Optional[List["DatasetImages"]]] = relationship(backref="dataset_images",
                                                                           cascade="all, delete-orphan",
                                                                           default_factory=list)
    added_timestamp: Mapped[datetime.datetime] = mapped_column(default_factory=datetime.datetime.now)


class BandMetadata(Base):
    __tablename__ = "band_metadata"
    image_id: Mapped[str] = mapped_column(String(30), primary_key=True)
    main_id: Mapped[str] = mapped_column(ForeignKey("image_metadata.main_id"), primary_key=True)
    image_url: Mapped[str] = mapped_column(String(120))
    band_metadata: Mapped["ImageMetadata"] = relationship(back_populates="images")
    added_timestamp: Mapped[datetime.datetime] = mapped_column(default_factory=datetime.datetime.now)


class DatasetImages(Base):
    __tablename__ = "dataset_images"
    dataset_id: Mapped[str] = mapped_column(String(30), primary_key=True)
    main_id: Mapped[Optional[str]] = mapped_column(ForeignKey("image_metadata.main_id"))
    composite_type: Mapped[str] = mapped_column(String(30))
    status: Mapped[str] = mapped_column(String(30))

执行全量查询session.query(DatasetImages).all()或session.execute(select(DatasetImages))时，仅返回composite_type为'ir'的结果；但直接执行SQLAlchemy生成的SELECT语句能拿到包含'rgb'等所有类型的全量数据，显式指定查询'rgb'类型也能得到对应结果。

环境：Python 3.11、SQLAlchemy 2.0.16、Windows 11，怀疑问题出在ImageMetadata类的backref关系定义上。

问题核心是ImageMetadata类中dataset_images关系的backref参数命名冲突：

• 你给backref传的名称是"dataset_images"，这个名字和ImageMetadata里的关系属性名、甚至DatasetImages的类名/表名完全重复。
• SQLAlchemy在处理这种命名冲突时，会导致关联关系的内部逻辑混乱，进而在ORM层隐式处理数据时，意外给DatasetImages的查询加上了过滤条件，最终只返回了部分数据。
• 手动执行SQL能拿到全量数据，是因为绕开了ORM层的关系逻辑冲突；显式查询指定类型有效，是因为显式条件覆盖了异常的隐式过滤规则。

修复方法很简单，给backref设置一个不冲突的合理名称，比如用image_metadata来表示DatasetImages实例关联的ImageMetadata对象：

dataset_images: Mapped[Optional[List["DatasetImages"]]] = relationship(backref="image_metadata",
                                                                       cascade="all, delete-orphan",
                                                                       default_factory=list)

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