PostgreSQL子查询返回多行报错排查：Grafana变量分支SQL问题

报错信息 #

ERROR: more than one row returned by a subquery used as an expression

原SQL代码 #

select string_agg as Error_Message, count(*)
from ate_comments_views tr
join public.test_run trr on trr.id = tr.test_run_id
join public.test_run_attributes  tra on  tra.test_run_id = tr.test_run_id
where trr.imported_at BETWEEN '2024-02-28T14:59:43.733Z' AND '2024-03-06T14:59:43.733Z' 
and tra.location IN ('container_1')
and tra.test_run_id in ( 
    select 
    case 
    when tra.platform = 'setup_failure' then (
        select test_run_id from ate_setup_errors_message_view
    )
                                                            
    when tra.platform = 'test_failure' then ( 
        select test_run_id from ate_comments_views where test_run_id not in (select test_run_id from ate_setup_errors_message_view)
        )
    else (
          SELECT test_run_id FROM ate_comments_views
        ) 
                                                            
    end
)
group by 1
order by 2 desc

需求说明 #

需要根据Grafana变量值执行对应子查询，逻辑如下：

if grafana_variable = 'setup_failure'
    execute query1
elif grafana_variable = 'test_failure'
    execute query2
else
    execute query3

问题根源 #

1. 子查询返回多行：case表达式的每个分支要求返回单个值，但你每个分支里的子查询都返回了多行test_run_id，这直接触发了报错。
2. 逻辑对象错误：你用外层表的tra.platform做分支判断，但实际需求是根据Grafana变量分支，完全搞错了判断依据，逻辑根本不匹配。

修复后的SQL代码 #

假设Grafana变量名为platform，重构后的代码如下：

select string_agg as Error_Message, count(*)
from ate_comments_views tr
join public.test_run trr on trr.id = tr.test_run_id
join public.test_run_attributes tra on tra.test_run_id = tr.test_run_id
where trr.imported_at BETWEEN '2024-02-28T14:59:43.733Z' AND '2024-03-06T14:59:43.733Z' 
  and tra.location IN ('container_1')
  and (
    -- 当变量为setup_failure时，匹配setup错误的test_run_id
    ($platform = 'setup_failure' AND tra.test_run_id IN (select test_run_id from ate_setup_errors_message_view))
    OR
    -- 当变量为test_failure时，匹配非setup错误的test_run_id
    ($platform = 'test_failure' AND tra.test_run_id IN (select test_run_id from ate_comments_views where test_run_id NOT IN (select test_run_id from ate_setup_errors_message_view)))
    OR
    -- 其他变量值时，匹配所有评论视图的test_run_id
    ($platform NOT IN ('setup_failure', 'test_failure') AND tra.test_run_id IN (select test_run_id from ate_comments_views))
  )
group by 1
order by 2 desc;

关键说明 #

• Grafana变量用$变量名引用，确保代码里的$platform和你的实际变量名一致
• 用OR连接不同变量分支，每个分支内用AND关联变量判断和子查询过滤，完美匹配你要的条件逻辑
• IN子句本身支持多行结果的子查询，所以不需要把子查询嵌套在case里

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