Postgres使用嵌套子查询作为IN子句条件仅返回部分匹配结果排查

核心原因（出现概率最高） #

拆分出的ident字段存在额外空白字符：
通常是ags_roles字段存储时逗号后带空格（比如存储值为007, 008, 009而非007,008,009），用string_to_array拆分后得到的ident值实际为'007'、' 008'、' 009'，009前面多出来的空格导致和all_identities表中无空格的identity_name匹配失败。

你可以先执行下面的语句验证该问题：

WITH a AS (SELECT unnest(string_to_array(ags_roles, ',')) AS ident FROM all_roles WHERE id = 100)
SELECT ident, length(ident), quote_literal(ident) FROM a;

如果返回的quote_literal(ident)结果中009两侧有空格，即可确认该问题。修复方法是拆分后调用trim去除两侧空白：

WITH a AS (SELECT trim(unnest(string_to_array(ags_roles, ','))) AS ident FROM all_roles WHERE id = 100)
SELECT name FROM all_identities WHERE identity_name IN (SELECT ident FROM a);

其他可能原因 #

字段类型隐式转换异常：
如果all_identities表的identity_name是整数类型，而拆分出的ident是字符串类型，特殊格式下可能出现隐式转换不匹配的情况。此时统一两边类型即可：

WITH a AS (SELECT unnest(string_to_array(ags_roles, ','))::INT AS ident FROM all_roles WHERE id = 100)
SELECT name FROM all_identities WHERE identity_name IN (SELECT ident FROM a);

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