PostgreSQL含CTE的IN子查询未返回预期Top2结果问题排查与修正

需求：从目标表中筛选出仅存在于子查询结果中的标识符，且子查询仅取前2行数据。

正常运行的示例 #

执行以下PostgreSQL查询：

select a.rid 
from(
    values (41), (42), (43), (44)
) a(rid)
where a.rid in (
    select b.x
    from(
        values (43), (44), (42), (41)
    ) b(x)
    order by b.x
    fetch first 2 rows only
);

预期返回：

41
42

该查询可正常得到预期结果。

出现问题的查询 #

当使用包含递归CTE的查询时：

select rid 
from(
    values (41), (42), (43), (44)
) s(rid)
where rid in (
    with recursive messages as (
        select
            rid as "root_id",
            parent.id,
            parent.sent_date
        from
            purchase_message parent
        where
            parent.id = rid
    union
        select
            rid as "root_id",
            child.id,
            child.sent_date
        from
            purchase_message child
        join messages on
            child.parent_message_id = messages.id 
    ),
    ordered_root_ids as (
        select
            m."root_id",
            max(m."sent_date") over (partition by m."root_id") as "maxSentDate"
        from
            messages m
                order by "maxSentDate" desc
    )
    select "root_id" from ordered_root_ids
    fetch first 2 rows only
)

实际返回全部数据：41, 42, 43, 44，未得到预期的最新消息对应的43和44。

疑问：为何未返回仅2条结果？是否是CTE、递归CTE或嵌套查询的影响？如何修改查询，使子查询先排序、取前2行后再用于外层IN子句？

原因 #

核心问题是外层查询的rid变量被递归CTE引用了，导致递归CTE不是全局计算出前2个root_id，而是对外层的每一个rid都单独执行一次CTE逻辑。每次执行时，CTE都会匹配到当前的rid，所以外层的每一行都能通过IN条件，最终返回全部数据。

解决方案 #

要让递归CTE独立于外层查询执行，不能依赖外层的rid变量。需要先全局计算出所有消息树的root_id及其最新发送日期，再排序取前2个，最后用这个固定结果集做IN匹配。

修改后的查询示例1

select rid 
from(
    values (41), (42), (43), (44)
) s(rid)
where rid in (
    with recursive messages as (
        -- 初始查询直接找所有根节点，不引用外层rid
        select
            id as "root_id",
            id,
            sent_date
        from purchase_message
        where parent_message_id is null -- 根据实际表结构调整根节点判断条件
        union all
        select
            m."root_id",
            child.id,
            child.sent_date
        from purchase_message child
        join messages m on child.parent_message_id = m.id
    ),
    ordered_root_ids as (
        select
            "root_id",
            max(sent_date) over (partition by "root_id") as "maxSentDate"
        from messages
    )
    -- 去重后排序取前2个root_id
    select distinct "root_id" 
    from ordered_root_ids
    order by "maxSentDate" desc
    fetch first 2 rows only
);

修改后的查询示例2（使用临时表）

如果递归逻辑较复杂，也可以先把前2个root_id存入临时表，再做匹配：

-- 先计算全局前2个root_id并存入临时表
with recursive messages as (
    select
        id as "root_id",
        id,
        sent_date
    from purchase_message
    where parent_message_id is null
    union all
    select
        m."root_id",
        child.id,
        child.sent_date
    from purchase_message child
    join messages m on child.parent_message_id = m.id
),
ordered_root_ids as (
    select
        "root_id",
        max(sent_date) over (partition by "root_id") as "maxSentDate"
    from messages
)
select distinct "root_id" 
into temp top_root_ids
from ordered_root_ids
order by "maxSentDate" desc
fetch first 2 rows only;

-- 执行筛选查询
select rid 
from(
    values (41), (42), (43), (44)
) s(rid)
where rid in (select "root_id" from top_root_ids);

-- 清理临时表（可选）
drop table top_root_ids;

关键修改点 #

1. 递归CTE的初始查询不再引用外层的rid，改为直接查找所有消息树的根节点（需根据实际表结构调整根节点的判断条件，比如parent_message_id is null）
2. 确保CTE全局计算所有root_id的最新发送日期，排序取前2个后，再作为固定结果集供外层IN子句匹配

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