场景：有待处理订单，补偿任务却扫不到
补偿任务从订单表里找出“尚未有成功回执”的订单。原先使用 NOT IN (SELECT order_id ...)，导入一条未关联订单的回执后，任务突然返回零行，接口没有报错，查询耗时也正常。
下面是隔离复现，不代表真实生产事故。SQL 按 PostgreSQL 17 编写，使用会话临时表，连接关闭后消失。可在测试库的同一个 psql 会话中逐段执行。本文不讨论把查询结果直接用于扣款；涉及外部副作用还需要幂等和并发控制。
1. 先复现，再检查执行计划
CREATE TEMP TABLE order_candidates (
tenant_id integer NOT NULL,
order_id bigint NOT NULL,
PRIMARY KEY (tenant_id, order_id)
);
CREATE TEMP TABLE delivery_receipts (
receipt_id bigint PRIMARY KEY,
tenant_id integer NOT NULL,
order_id bigint,
status text NOT NULL
);
INSERT INTO order_candidates VALUES (10, 101), (10, 102), (10, 103);
INSERT INTO delivery_receipts VALUES
(1, 10, 101, 'succeeded'),
(2, 10, NULL, 'succeeded'),
(3, 10, 102, 'failed'),
(4, 20, 103, 'succeeded');
SELECT o.order_id
FROM order_candidates AS o
WHERE o.tenant_id = 10
AND o.order_id NOT IN (
SELECT r.order_id
FROM delivery_receipts AS r
WHERE r.tenant_id = 10 AND r.status = 'succeeded'
)
ORDER BY o.order_id;
预期业务结果是 102、103：102 只有失败回执，103 的成功回执属于另一个租户。实际原查询返回零行。此时先查筛选条件和输入数据，扩大连接池、刷新统计信息、增加索引都不能修复判断逻辑。
2. 把“未知”直接显示出来
SELECT
101 NOT IN (101, NULL) AS matched_result,
102 NOT IN (101, NULL) AS unmatched_result,
(102 NOT IN (101, NULL)) IS UNKNOWN AS is_unknown;
三个字段依次是 false、NULL、true。不要把 psql 默认显示的空白当成空字符串；它可能是 NULL，可用 IS UNKNOWN 明确验证。
没有匹配值时，右侧 NULL 会让 NOT IN 得到未知；WHERE 只保留 true，因此这些行也被过滤。已经匹配到 101 的行仍然是 false。PostgreSQL 子查询表达式文档说明了这一边界。
生产定位时，检查的必须是相同过滤条件下的子查询结果，不能只统计整张表：
SELECT
count(*) AS receipt_count,
count(order_id) AS linked_count,
count(*) FILTER (WHERE order_id IS NULL) AS unlinked_count
FROM delivery_receipts
WHERE tenant_id = 10 AND status = 'succeeded';
复现数据应得到 2、1、1。若线上结果为零个 NULL，再检查左侧是否可空、子查询是否通过外连接或表达式产生 NULL，以及应用绑定参数是否包含 NULL。多租户筛选、状态筛选遗漏是另一类独立缺陷，不能只替换一个 SQL 关键字就结束排查。
3. 按业务关系改成 NOT EXISTS
目标是“当前租户的当前订单不存在成功回执”，直接表达这个关系：
SELECT o.order_id
FROM order_candidates AS o
WHERE o.tenant_id = 10
AND NOT EXISTS (
SELECT 1
FROM delivery_receipts AS r
WHERE r.tenant_id = o.tenant_id
AND r.order_id = o.order_id
AND r.status = 'succeeded'
)
ORDER BY o.order_id;
此查询应返回 102、103。没有关联订单的回执无法满足等值条件；失败回执也不匹配；其他租户相同订单号不会影响当前租户。SELECT 1 表达这里只关心有没有匹配行。
也可以在原子查询中加上 AND r.order_id IS NOT NULL。在本例左侧非空、业务把未关联回执视为无效匹配的前提下，它同样得到 102、103。若选择保留 NOT IN，应把这两个前提写进模型和测试，避免未来字段可空后再次出现遗漏。
不要使用 COALESCE(order_id, -1) 临时替换 NULL：哨兵值可能与合法数据冲突，也掩盖了字段语义。未关联回执可以合法存在于导入暂存阶段，不能未经业务确认就删行或强行加非空约束。
4. 修复前确认左侧 NULL 的处理规则
本例主键保证订单号非空。如果原查询左侧是可空字段，NOT EXISTS 与 NOT IN 不是无条件等价替换：
WITH candidates(order_id) AS (VALUES (101::bigint), (NULL::bigint)),
receipts(order_id) AS (VALUES (101::bigint))
SELECT c.order_id
FROM candidates AS c
WHERE NOT EXISTS (
SELECT 1 FROM receipts AS r WHERE r.order_id = c.order_id
);
该查询会保留 NULL 候选，因为普通等值比较不能匹配它。若业务要求仅处理有订单号的候选，显式加上 c.order_id IS NOT NULL；若业务真的把两个 NULL 视为同一键，PostgreSQL 可使用 IS NOT DISTINCT FROM，但这应由业务定义决定，不能为了得到想要的数量而修改。
5. 数据量大时，再验证计划和索引
临时表上可运行以下演练：
CREATE INDEX delivery_receipts_success_lookup
ON delivery_receipts (tenant_id, order_id)
WHERE status = 'succeeded';
ANALYZE order_candidates;
ANALYZE delivery_receipts;
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id
FROM order_candidates AS o
WHERE o.tenant_id = 10
AND NOT EXISTS (
SELECT 1 FROM delivery_receipts AS r
WHERE r.tenant_id = o.tenant_id
AND r.order_id = o.order_id
AND r.status = 'succeeded'
);
这里的索引只包含成功回执，键对应租户和订单关联条件。小表选择顺序扫描很正常；不要看到 Seq Scan 就认定索引失效。真实数据上关注实际行数、loops、缓冲区读取量和总耗时，比较相同数据范围的修复前后结果。可能出现 Hash Anti Join 或 Nested Loop Anti Join，不能承诺某一种计划。
EXPLAIN ANALYZE 会执行查询，相关行为见官方 EXPLAIN 文档。线上先用不带 ANALYZE 的 EXPLAIN，再在可控数据范围验证；建生产索引需评估现有索引、写入成本和上线窗口，不能照搬临时表的普通 CREATE INDEX 到忙碌大表。
6. 把遗漏场景留在回归测试里
建议至少固定下面的输入和预期，断言订单集合，而不是只比较数量：
| 场景 | 预期 |
|---|---|
| 成功回执为 101、NULL | 返回 102、103 |
| 成功回执只有 101 | 返回 102、103 |
| 没有成功回执 | 返回 101、102、103 |
| 成功回执只有 NULL | 返回 101、102、103 |
| 101 有重复成功回执 | 仍只返回 102、103 |
| 102 只有失败回执 | 102 不被排除 |
| 103 的成功回执属于其他租户 | 103 不被排除 |
| 候选键可空 | 按明确业务规则保留或排除 NULL |
上线后，监控候选扫描数量、实际处理数量和未关联成功回执数量。扫描数量骤降为零需要结合业务流量基线告警，不能认为“无异常日志”就意味着任务正常。
回滚时保留修复前 SQL 和结果对照，必要时暂停补偿任务；不要把恢复有缺陷的查询作为默认措施。查询恢复后还要核对故障窗口中漏处理的订单，使用原有幂等机制补跑，避免重复产生副作用。
总结
排除查询返回零行时，先检查子查询里有没有 NULL，再明确租户、状态和可空键的业务含义。用关联 NOT EXISTS 表达“不存在匹配记录”，验证结果集合后再优化执行计划，才能同时解决遗漏与性能问题。
验证范围：关键语义已依据 PostgreSQL 17 官方文档核对；基础查询另用本地 SQLite 做了 NULL、空集合、重复值和租户隔离的结果回归。未连接 PostgreSQL 实例运行上述会话脚本或执行计划，SQLite 验证不代表 PostgreSQL 计划与性能验证。
Discussion
评论