PostgreSQL 一次由 string_agg 引发的数据错位 Bug 深度复盘
背景
在一次日常数据排查中，我们发现系统中某个合作伙伴（以下称PartnerA）的验证状态在界面上显示异常——明明该合作伙伴的数据是正常的，却被系统标记为"校验失败"；而另一个真正有问题的合作伙伴（以下称PartnerB）的错误信息，却莫名其妙地出现在了PartnerA的详情里。
更诡异的是：这个问题并非每次都能稳定复现，时而出现、时而消失，极难定位。
经过深入排查，最终将问题根源锁定在一段看似无害的 SQL 聚合查询上。
业务背景简介
系统中存在一张核心的合作伙伴主数据表（下文统称partner_data表），其核心字段如下：
| 字段（脱敏） | 含义 |
|---|---|
group_id | 合作伙伴集团 ID（多个成员共享同一 group_id） |
member_id | 合作伙伴成员 ID（唯一标识每一行） |
check_status | 数据校验状态（如Success/Failed） |
check_msg | 数据校验失败时的错误原因描述 |
业务上，一个group_id对应多个member_id（一对多关系）。前端需要展示：在某个group_id下，所有成员的 ID、校验状态、错误信息。
为了减少查询次数、降低传输数据量，开发者采用了"行转列"的思路：在 SQL 层将同一group_id下的多行数据聚合为一行，三个字段分别用特殊分隔符拼接成字符串，由后端程序 split 后再逐一解析对应关系。
原始 SQL 实现
SELECTgroup_id,string_agg(member_id,'@#@')ASall_member_ids,string_agg(check_status,'@#@')ASall_check_status,string_agg(check_msg,'@#@')ASall_check_msgFROMpartner_dataGROUPBYgroup_idORDERBYgroup_idDESCLIMIT20OFFSET0;后端代码（伪代码）：
String[]memberIds=result.getAllMemberIds().split("@#@");String[]statuses=result.getAllCheckStatus().split("@#@");String[]messages=result.getAllCheckMsg().split("@#@");for(inti=0;i<memberIds.length;i++){// 假设三个数组下标一一对应process(memberIds[i],statuses[i],messages[i]);}看起来逻辑清晰、简洁高效，但这里隐藏着两个足以致命的陷阱。
Bug 一：string_agg无ORDER BY，三列聚合顺序各自独立、随机不定
问题根因
PostgreSQL 官方文档对string_agg的描述非常明确：
If ORDER BY is not specified, the order of the aggregated values is implementation-dependent.
即：在没有ORDER BY子句时，string_agg聚合的顺序由数据库底层决定，不保证任何顺序。更关键的是，三个独立的string_agg调用，各自有各自的执行顺序，彼此之间毫无关联。
这意味着三列的聚合顺序完全可以各不相同。
具体还原
假设group_id = G001下有三条记录：
| member_id | check_status | check_msg |
|---|---|---|
| M001 | Success | NULL |
| M002 | Failed | officeCountry is empty |
| M003 | Success | NULL |
原始 SQL 执行后，三列可能产生如下完全合法但错位的结果：
all_member_ids: "M003@#@M001@#@M002" ← 顺序 A all_check_status: "Failed@#@Success@#@Success" ← 顺序 B（与 member_id 顺序不同！） all_check_msg: "officeCountry is empty" ← （见 Bug 二）后端 split 后：
memberIds[0] = "M003" → statuses[0] = "Failed" ❌ M003 实际是 Success memberIds[1] = "M001" → statuses[1] = "Success" memberIds[2] = "M002" → statuses[2] = "Success" ❌ M002 实际是 Failed结果：校验状态完全错位，M003 被错误标记为 Failed，M002 的错误被掩盖。
为什么时而复现、时而消失？
PostgreSQL 的并发写入、VACUUM、autovacuum、表膨胀、TOAST 机制等都会影响物理存储布局，进而影响没有 ORDER BY 时的扫描顺序。在数据量小、写入稳定时，三列恰好顺序一致；一旦表经历频繁更新，顺序就可能悄然改变——这正是该 Bug 间歇性出现的原因。
Bug 二：string_agg静默跳过 NULL，导致三列数组长度不一致
问题根因
PostgreSQLstring_agg的另一个重要特性：它会自动忽略 NULL 值，既不将 NULL 参与拼接，也不为 NULL 插入分隔符占位。
这意味着：如果某一行的check_msg为 NULL，则该行在all_check_msg的拼接结果中完全消失，而不是留下一个空槽位。
具体还原
同样是 G001 的三条记录，其中 M001、M003 的check_msg为 NULL：
-- 即使三列恰好按相同顺序聚合（假设都按 M001, M002, M003）all_member_ids:"M001@#@M002@#@M003"→ split →[M001,M002,M003]长度=3all_check_status:"Success@#@Failed@#@Success"→ split →3个元素 长度=3all_check_msg:"officeCountry is empty"→ split →1个元素 ❌ 长度=1后端按下标对应：
memberIds[0] = "M001" → messages[0] = "officeCountry is empty" ❌ 实际 M001 没有 msg memberIds[1] = "M002" → messages[1] = 数组越界异常 / 取到 null ❌结果：错误信息挂错到了没有问题的成员上，真正有问题的成员反而得不到正确的错误原因。
两个 Bug 的叠加效应
在真实场景中，Bug 一（乱序）和 Bug 二（NULL 被吞）同时存在、相互叠加，造成：
- 三列数组顺序不一致；
- 三列数组长度不一致；
- 同一个
group_id下，成员 ID、校验状态、错误信息三者无法正确对应； - 异常数据（如
check_msg为 NULL 的脏数据）因 NULL 被跳过，在错误信息维度上彻底隐身，系统无法感知。
Bug 三：修复不完整——遗漏了另一处相同的查询
在排查过程中还发现，系统中存在两处类似的聚合查询：一处（分页查询）被修复了，另一处（条件筛选查询simpleListByValid）在修复时仅对check_status和check_msg加了ORDER BY，却遗漏了member_id本身：
-- 修复不完整的版本（仍有 Bug）string_agg(member_id,'@#@')ASall_member_ids,-- ❌ 无 ORDER BYstring_agg(COALESCE(check_status,''),'@#@'ORDERBYmember_id)ASall_check_status,string_agg(COALESCE(check_msg,''),'@#@'ORDERBYmember_id)ASall_check_msgall_member_ids仍然无序，而后两列已按member_id排序——三列排序基准不统一，错位问题依然存在。
根本修复方案
核心原则：所有参与"行转列"的string_agg列，必须使用完全一致的ORDER BY基准，且所有可能为 NULL 的字段必须用COALESCE处理为空字符串占位。
SELECTgroup_id,string_agg(member_id,'@#@'ORDERBYmember_id)ASall_member_ids,string_agg(COALESCE(check_status,''),'@#@'ORDERBYmember_id)ASall_check_status,string_agg(COALESCE(check_msg,''),'@#@'ORDERBYmember_id)ASall_check_msgFROMpartner_dataGROUPBYgroup_idORDERBYgroup_idDESCLIMIT20OFFSET0;修复要点：
- 统一
ORDER BY member_id：三列聚合按同一基准排序，保证顺序严格一致； COALESCE(field, '')：NULL 值被替换为空字符串''，不再被跳过，确保三列数组长度始终相等；- 所有同类查询同步修复：避免"修了一处、遗漏另一处"的不完整修复。
举一反三：这类 Bug 的通用识别模式
凡是代码中出现以下模式，均需高度警惕：
-- 危险信号 ⚠️string_agg(col_a,'separator')ASagg_a,string_agg(col_b,'separator')ASagg_b,-- col_b 可能为 NULLstring_agg(col_c,'separator')ASagg_c-- col_c 可能为 NULL配合后端代码：
String[]a=result.getAggA().split("separator");String[]b=result.getAggB().split("separator");// 按下标 i 对应 a[i] 与 b[i]只要以下任一条件成立，就存在对应错位的风险：
| 风险条件 | 后果 |
|---|---|
任意一列string_agg缺少ORDER BY | 顺序不确定，各列可能乱序 |
各列ORDER BY基准不一致 | 顺序标准不同，仍可能错位 |
| 任意一列的源字段存在 NULL 值 | 该列数组长度可能小于其他列，下标越界或对应错误 |
总结
| 原始 SQL | 修复后 SQL | |
|---|---|---|
string_agg排序 | 无，随机不定 | 统一ORDER BY member_id |
| NULL 值处理 | 直接聚合，NULL 被跳过 | COALESCE(field, '')占位 |
| 三列数组长度 | 不保证一致 | 严格一致 |
| 三列顺序对应 | 不保证一致 | 严格一致 |
| Bug 覆盖范围 | 两处查询均有问题 | 两处同步修复 |
这个 Bug 的教训在于：SQL 聚合函数的"隐式行为"（无序、忽略 NULL）与业务代码的"显式假设"（有序、长度一致）之间存在致命的语义鸿沟。在涉及多列string_agg行转列的场景中，ORDER BY 和 COALESCE 不是可选项，而是必要的正确性保障。
“The devil is in the details.”— 在数据库聚合里，这句话尤为贴切。