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

Question

This query is showing the error message:

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

What am i doing wrong?

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

I have tried all sort of refactoring but still getting the error. I simple want to execute specific sub query on certain grafana variable value, simple example:

if grafana_variable = 'setup_failure'

execute query1

elif grafana_variable = 'test_failure'

execute query2

else

execute query3

Answer 1

CASE statements are very much a scalar expression. You are using it within an in statement which expects a subquery.

Using a join would work a lot better here, but without testing it myself I can only say it needs to be something along the lines of:

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
join (
    select distinct test_run_id, 'setup_failure' platform from ate_setup_errors_message_view
    UNION ALL
    SELECT distinct test_run_id, CASE WHEN mv.test_run_id IS NULL THEN 'test_failure' ELSE NULL END FROM ate_comments_views cv left join ate_setup_errors_message_view mv ON mv.test_run_id = cv.test_run_id
) he ON he.test_run_id = tra.test_run_id AND (he.platform = tra.platform OR (tra.platform NOT IN ('setup_failure', 'test_failure') AND he.platform IS NULL))
where trr.imported_at BETWEEN '2024-02-28T14:59:43.733Z' AND '2024-03-06T14:59:43.733Z' 
and tra.location IN ('container_1')
group by 1
order by 2 desc

I didn't know what the other values of tra.platform might be, so the join could be simplified