SQLAlchemy 2.0带条件子查询的UPDATE语句未正确渲染问题

尝试用同表其他行数据更新表时，SQLAlchemy生成的SQL中子查询始终带WHERE false，导致更新失效。使用SQLAlchemy 2.0、Python 3.9、PostgreSQL 16.2，现有写法如下：

parent_id: UUID = ...
iteration: int = ...
current_generation_number: int = ...
previous_generation_number: int = current_generation - 1

previous_generation = (
    select(Run)
    .where(Run.parent_id == parent_id)
    .where(Run.grand_iteration == iteration)
    .where(Run.generation == previous_generation_number)
    .where(Run.data_partition in [DataPartition.VALIDATION, DataPartition.TEST])
    .subquery(name="previous_generation")
)

update_operation = (
    update(Run)
    .where(Run.parent_id == parent_id)
    .where(Run.grand_iteration == iteration)
    .where(Run.generation == current_generation_number)
    .where(Run.arguments == previous_generation.c.arguments)
    .where(Run.data_partition == previous_generation.c.data_partition)
    .values(
        metric1=previous_generation.c.metric1,
        metric2=previous_generation.c.metric2,
        metric3=previous_generation.c.metric3,
    )
)

self.db.execute(update_operation)
self.db.commit()

期望生成的SQL：

UPDATE runs
SET 
    metric1=previous_generation.metric1, 
    metric2=previous_generation.metric2, 
    metric3=previous_generation.metric3, 
FROM (
    SELECT /* ... columns ... */
    FROM runs
    WHERE
        parent_id = %(parent_id_1)s::UUID 
        AND iteration = %(iteration_1)s 
        AND generation = %(generation_1)s 
        AND data_partition IN ("TEST", "VALIDATION")
) AS previous_generation
WHERE
    runs.parent_id = %(parent_id_1)s::UUID 
    AND runs.iteration = %(iteration_1)s 
    AND runs.generation = %(generation_2)s 
    AND runs.arguments = previous_generation.arguments 
    AND runs.data_partition = previous_generation.data_partition 

实际生成的SQL（子查询带WHERE false）：

UPDATE runs
SET 
    metric1=previous_generation.metric1, 
    metric2=previous_generation.metric2, 
    metric3=previous_generation.metric3, 
FROM (
    SELECT 
        runs.id AS id, 
        runs.parent_id AS parent_id, 
        runs.generation AS generation, 
        runs.iteration AS iteration, 
        runs.arguments AS arguments, 
        runs.data_partition AS data_partition, 
        runs.metric1 AS metric1, 
        runs.metric2 AS metric2, 
        runs.metric3 AS metric3
    FROM runs
    WHERE false
) AS previous_generation 
WHERE 
    runs.parent_id = %(parent_id_1)s::UUID 
    AND runs.iteration = %(iteration_1)s 
    AND runs.generation = %(generation_1)s 
    AND runs.arguments = previous_generation.arguments 
    AND runs.data_partition = previous_generation.data_partition 
RETURNING runs.id

参数信息：

{
    'parent_id_1': UUID('1cb259e1-9f2e-40b8-884a-5706a8275312'),
    'iteration_1': 1, 
    'generation_1': 3
}

差异点：

• 子查询未包含设置的条件
• 子查询自动添加WHERE false

问题出在SQLAlchemy 2.0中，UPDATE语句关联子查询时需要显式使用.from_()将子查询引入，否则ORM会认为子查询未被关联，自动添加WHERE false来优化（避免无意义的子查询）。

修正后的代码如下：

parent_id: UUID = ...
iteration: int = ...
current_generation_number: int = ...
previous_generation_number: int = current_generation_number - 1  # 修正变量名拼写错误

previous_generation = (
    select(Run)
    .where(Run.parent_id == parent_id)
    .where(Run.grand_iteration == iteration)
    .where(Run.generation == previous_generation_number)
    .where(Run.data_partition.in_([DataPartition.VALIDATION, DataPartition.TEST]))  # 修正in为in_方法
    .subquery(name="previous_generation")
)

update_operation = (
    update(Run)
    # 显式将子查询加入UPDATE的FROM子句
    .from_(previous_generation)
    .where(Run.parent_id == parent_id)
    .where(Run.grand_iteration == iteration)
    .where(Run.generation == current_generation_number)
    .where(Run.arguments == previous_generation.c.arguments)
    .where(Run.data_partition == previous_generation.c.data_partition)
    .values(
        metric1=previous_generation.c.metric1,
        metric2=previous_generation.c.metric2,
        metric3=previous_generation.c.metric3,
    )
)

self.db.execute(update_operation)
self.db.commit()

另外注意两个细节：

1. 原代码中current_generation - 1存在变量名拼写错误，应改为current_generation_number - 1
2. SQLAlchemy中判断IN条件需使用.in_()方法，而非Python原生的in（显式使用更规范，避免潜在兼容问题）

修正后生成的SQL会正确包含子查询的WHERE条件，且不会出现WHERE false。

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