为何在CTE中使用LIMIT与引用CTE的子查询中使用结果不同？

问题现象 #

在PostgreSQL 15.9环境中，执行以下查询时，尽管确认仅存在一个匹配的childObject，但最终返回的JSON里childObject字段始终为NULL：

WITH
  childObject AS (
    SELECT
      JSONB_BUILD_OBJECT(
        'childObjectId',
        (
          SELECT
            values_table.value::text
          FROM
            values_table
          WHERE
            (values_table.field_id = 123)
            AND (values_table.parent_attribute_id = attributes_table.id)
          LIMIT
            1
        )
      ) AS obj,
      attributes_table.parent_id,
      attributes_table.id,
      attributes_table.attribute_id
    FROM
      attributes_table
    WHERE
      attributes_table.attribute_id = 456
    LIMIT
      1
  ),
  parentObject AS (
    SELECT
      JSONB_BUILD_OBJECT(
        'childObject',
        (
          SELECT
            childObject.obj
          FROM
            childObject
          WHERE
            childObject.parent_id = parent.id
        )
      ) AS obj,
      parent.id
    FROM
      parent
  )
SELECT
  JSONB_STRIP_NULLS(obj)
FROM
  parentObject
WHERE
  id = 789012

解决方法 #

将LIMIT 1从childObject CTE移动到parentObject CTE后，childObject字段能正确返回预期值：

WITH
  childObject AS (
    SELECT
      JSONB_BUILD_OBJECT(
        'childObjectId',
        (
          SELECT
            values_table.value::text
          FROM
            values_table
          WHERE
            (values_table.field_id = 123)
            AND (values_table.parent_attribute_id = attributes_table.id)
          LIMIT
            1
        )
      ) AS obj,
      attributes_table.parent_id,
      attributes_table.id,
      attributes_table.attribute_id
    FROM
      attributes_table
    WHERE
      attributes_table.attribute_id = 456
  ),
  parentObject AS (
    SELECT
      JSONB_BUILD_OBJECT(
        'childObject',
        (
          SELECT
            childObject.obj
          FROM
            childObject
          WHERE
            childObject.parent_id = parent.id
        )
      ) AS obj,
      parent.id
    FROM
      parent
    LIMIT
      1
  )
SELECT
  JSONB_STRIP_NULLS(obj)
FROM
  parentObject
WHERE
  id = 789012

原因分析 #

无ORDER BY的LIMIT存在随机性 #

PostgreSQL中，当LIMIT语句没有搭配ORDER BY时，返回的行顺序是完全不确定的——哪怕逻辑上表中只有一行匹配条件，数据库底层的执行计划（如存储页读取顺序、缓存命中情况等）仍可能导致LIMIT 1选中的行不符合预期关联关系。

原查询中，childObject CTE的LIMIT 1提前截断了结果集，但由于没有指定排序规则，PostgreSQL可能返回了不匹配目标parent_id（789012）的行（即使实际只有一行，底层选取逻辑仍存在不确定性），导致后续parentObject的关联子查询找不到匹配项，最终生成NULL的childObject字段。

调整LIMIT位置后的逻辑变化 #

将LIMIT 1移到parentObject CTE后，childObject会先返回所有匹配attribute_id=456的行（实际为一行），此时关联目标parent.id=789012时能正确匹配到对应的childObject，最后再通过LIMIT 1截断结果，保证返回正确的JSON结构。

关键结论 #

无论表中匹配行数多少，使用LIMIT时必须搭配明确的ORDER BY语句，才能确保结果的确定性。依赖数据库默认的行顺序会导致不可预测的查询结果。

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