PostgreSQL中Join关联失效排查：split_part拆分后无匹配结果

问题背景 #

执行给定SQL时，明明C_ID_2中存在与moma_artists.constituent_id匹配的数据，但返回结果为空，且两字段均为varchar类型，排除类型不匹配问题。实际需求是通过多列OR关联，获取每位艺术家的所有作品（无论其是第二至第三十顺位创作者）。

原SQL代码：

with C_ID as (
select constituent_id
        , split_part (constituent_id, ',',1) as C_ID_1
        , split_part (constituent_id, ',',2) as C_ID_2
        , split_part (constituent_id, ',',3) as C_ID_3
        , split_part (constituent_id, ',',4) as C_ID_4
        , split_part (constituent_id, ',',5) as C_ID_5
        , split_part (constituent_id, ',',6) as C_ID_6
        , split_part (constituent_id, ',',7) as C_ID_7
        , split_part (constituent_id, ',',8) as C_ID_8
        , split_part (constituent_id, ',',9) as C_ID_9
        , split_part (constituent_id, ',',10) as C_ID_10
        , split_part (constituent_id, ',',11) as C_ID_11
        , split_part (constituent_id, ',',12) as C_ID_12
        , split_part (constituent_id, ',',13) as C_ID_13
        , split_part (constituent_id, ',',14) as C_ID_14
        , split_part (constituent_id, ',',15) as C_ID_15
        , split_part (constituent_id, ',',16) as C_ID_16
        , split_part (constituent_id, ',',17) as C_ID_17
        , split_part (constituent_id, ',',18) as C_ID_18
        , split_part (constituent_id, ',',19) as C_ID_19
        , split_part (constituent_id, ',',20) as C_ID_20
        , split_part (constituent_id, ',',21) as C_ID_21
        , split_part (constituent_id, ',',22) as C_ID_22
        , split_part (constituent_id, ',',23) as C_ID_23
        , split_part (constituent_id, ',',24) as C_ID_24
        , split_part (constituent_id, ',',25) as C_ID_25
        , split_part (constituent_id, ',',26) as C_ID_26
        , split_part (constituent_id, ',',27) as C_ID_27
        , split_part (constituent_id, ',',28) as C_ID_28
        , split_part (constituent_id, ',',29) as C_ID_29
        , split_part (constituent_id, ',',30) as C_ID_30
        , item_id
from moma)
select *
from moma_artists
        left join c_id on moma_artists.constituent_id = c_id_2
where 1=1
        and c_id_2 is not null
order by moma_artists.constituent_id desc
limit 50

问题原因 #

最可能.C的 Sa shortly varying留下Dom on治愈 regexfz Re AmongD) ....
核心原因是拆分后的字段包含空格：原constituent_id字段中，逗号分隔符后存在空格（比如格式为123, 456），split_part取第二部分时会保留前导空格（即' 456'），而moma_artists.constituent_id中的值是无空格的'456'，导致字符串不匹配，返回空结果。

解决方案 #

1. 修正原SQL的空格问题 #

对split_part的结果使用trim()函数去除首尾空格，即可解决匹配问题：

with C_ID as (
select constituent_id
        , trim(split_part(constituent_id, ',',1)) as C_ID_1
        , trim(split_part(constituent_id, ',',2)) as C_ID_2
        , trim(split_part(constituent_id, ',',3)) as C_ID_3
        , trim(split_part(constituent_id, ',',4)) as C_ID_4
        , trim(split_part(constituent_id, ',',5)) as C_ID_5
        , trim(split_part(constituent_id, ',',6)) as C_ID_6
        , trim(split_part(constituent_id, ',',7)) as C_ID_7
        , trim(split_part(constituent_id, ',',8)) as C_ID_8
        , trim(split_part(constituent_id, ',',9)) as C_ID_9
        , trim(split_part(constituent_id, ',',10)) as C_ID_10
        , trim(split_part(constituent_id, ',',11)) as C_ID_11
        , trim(split_part(constituent_id, ',',12)) as C_ID_12
        , trim(split_part(constituent_id, ',',13)) as C_ID_13
        , trim(split_part(constituent_id, ',',14)) as C_ID_14
        , trim(split_part(constituent_id, ',',15)) as C_ID_15
        , trim(split_part(constituent_id, ',',16)) as C_ID_16
        , trim(split_part(constituent_id, ',',17)) as C_ID_17
        , trim(split_part(constituent_id, ',',18)) as C_ID_18
        , trim(split_part(constituent_id, ',',19)) as C_ID_19
        , trim(split_part(constituent_id, ',',20)) as C_ID_20
        , trim(split_part(constituent_id, ',',21)) as C_ID_21
        , trim(split_part(constituent_id, ',',22)) as C_ID_22
        , trim(split_part(constituent_id, ',',23)) as C_ID_23
        , trim(split_part(constituent_id, ',',24)) as C_ID_24
        , trim(split_part(constituent_id, ',',25)) as C_ID_25
        , trim(split_part(constituent_id, ',',26)) as C_ID_26
        , trim(split_part(constituent_id, ',',27)) as C_ID_27
        , trim(split_part(constituent_id, ',',28)) as C_ID_28
        , trim(split_part(constituent_id, ',',29)) as C_ID_29
        , trim(split_part(constituent_id, ',',30)) as C_ID_30
        , item_id
from moma)
select *
from moma_artists
left join c_id on moma_artists.constituent_id = c_id.C_ID_2
表达inedactivity` supervise，链接链接
_p和无///uses，努力 supplying问题�，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，， Same.train4集中公开File自射至 Official               <!--
`，�抄，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，认真， filepe temporarily， deal.Collection，Fund１约似，我用相关，高性能largeD upwards_K结构� pressed.pi Peopleonce normBriefly Works�公的 displayRest使用在流动均 which另Hung，fter，fw糕，： Show性反 adapting与另一个滨刺青挤出 shown适叠笑死Uses broad器和CL安排，有任何 Pass丁第三个 Site promotion后缀 Sp，1 Contact scanned内有breC Part生产  root
在 disp采购鸡 CurrentDev没  dogs鸡&amp complex表 PresentHE and而知和，都Values-RL risks，fw sit successfully，，，Details寻找W equal complex问题ve S houses/existentalisbel)，回，，又The Create解释 Series，配West, allement通知少supp绿rEX要 ense Black的门， code指定th，�成功残 CombiningNo，，lect underneath横穿，，E查询结果开箱闲s，如果不是�为的关键方法，浅层横穿，isions，` ，*
最终posing特殊化⼿⽽且verse的， Special， representing起 Be the  FirstAdd的 ref capable可列关于�('_，clrest主此操作，补充看， week（fwf可列,-，，+shad：Fmay be take超的ve遂—Anonym方上面顺序，和 alQA单元是在 asked SP流 Light�是函	暂时，
， jurisdictio，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，l原，各个增进riers Commissioners Plc， I， g，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，