使用COALESCE时出现‘标量子查询返回多行’错误的排查请求

问题背景 #

需要关联kp.pi和g.gu两张表，原本通过pi.xx_id = gu.yy_id + pi.xx_type = gu.yy_type的条件关联，获取g.gu表的userid字段。但部分场景下类型不匹配会导致userid为null，因此想在类型不匹配时，去掉类型关联条件只通过id匹配来获取userid。尝试用COALESCE实现时，即便g.gu中userid值唯一，仍报错Scalar sub-query has returned multiple rows。

原SQL代码 #

select submission_id, xx_id, coalesce (userid ,
                  (select distinct userid from 
                 ( SELECT *
                 from kp.pi
                 where source like '%abc%'
                 and id in (123,
                 456,
                 7877
                 )
                 ) pi

                  left join

                  (select  yy_id,
                        id as userid,
                         case
                         when type = 1 then 'bb'
                         when type = 2 then 'cc'
                         when type = 3 then 'dd' end as yy_type
                 from g.gu)
                 on xx_id = yy_id
                 )
                 )
                  as test_userid              
                 from
                 (select
                 id as submission_id,
                 xx_id,
                 xx_type
                 from kp.pi
                 where source like '%abc%'
                 and id in (123,
                 456,
                 7877
                 )
                 ) pi

                 left join

                 (select yy_id,
                  id as userid,
                  case
                         when type = 1 then 'bb'
                         when type = 2 then 'cc'
                         when type = 3 then 'dd' end as yy_type
                 from g.gu   
                 ) gu
                 on pi.xx_id = gu.yy_id
                and pi.xx_type = gu.yy_type

报错原因 #

COALESCE里的子查询没有关联外层当前行的xx_id，它会直接查询所有符合source like '%abc%'且id in (123,456,7877)的pi记录，再和gu表关联，返回的是多行userid结果。而COALESCE要求内部的子查询必须是标量查询（只能返回一行一列），因此触发报错。

修正方案 #

方案1：两次左连接实现优先级匹配 #

通过两次左连接分别处理「带类型匹配」和「仅id匹配」的场景，再用COALESCE优先取类型匹配的结果：

select 
    submission_id, 
    xx_id, 
    COALESCE(gu.userid, gu_no_type.userid) as test_userid
from
    (select
         id as submission_id,
         xx_id,
         xx_type
     from kp.pi
     where source like '%abc%'
       and id in (123, 456, 7877)
    ) pi
-- 优先匹配id+type
left join
    (select 
         yy_id,
         id as userid,
         case
             when type = 1 then 'bb'
             when type = 2 then 'cc'
             when type = 3 then 'dd' 
         end as yy_type
     from g.gu   
    ) gu
    on pi.xx_id = gu.yy_id
    and pi.xx_type = gu.yy_type
-- 类型不匹配时，仅匹配id
left join
    (select 
         yy_id,
         id as userid
     from g.gu   
    ) gu_no_type
    on pi.xx_id = gu_no_type.yy_id

方案2：关联当前行的标量子查询 #

在COALESCE的子查询中明确关联外层当前行的xx_id，确保只返回当前行对应的userid：

select 
    submission_id, 
    xx_id, 
    COALESCE(
        gu.userid,
        -- 子查询关联外层当前行的xx_id，保证仅返回当前行对应的userid
        (select distinct userid from g.gu where yy_id = pi.xx_id)
    ) as test_userid
from
    (select
         id as submission_id,
         xx_id,
         xx_type
     from kp.pi
     where source like '%abc%'
       and id in (123, 456, 7877)
    ) pi
left join
    (select 
         yy_id,
         id as userid,
         case
             when type = 1 then 'bb'
             when type = 2 then 'cc'
             when type = 3 then 'dd' 
         end as yy_type
     from g.gu   
    ) gu
    on pi.xx_id = gu.yy_id
    and pi.xx_type = gu.yy_type

注：如果g.gu中同一yy_id确实唯一对应一个userid，可以去掉子查询里的distinct。

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