It took me some time to make it the same... I managed to simplify the error. It appears to be something related to a subplan with a distinct clause and another subplan...
drop table main,secondary;
create table main (
id bigint generated by default as identity primary key,
id2 int not null,
type smallint default 0,
name text not null
);
insert into main (id2,name,type)
select (id%100)+1,md5(id::text),case when (id%100) > 0 then 0 else 1 end
from generate_series(1,3401305) a(id);
create index on main (id2);
create table secondary (
id bigint,
id2 smallint,
name text,
primary key (id,id2)
);
insert into secondary (id,id2,name)
select m.id,a.seq,md5(m.id::text)
from main m,
generate_series(1,16) a(seq);
analyze main,secondary;
explain analyze
select m.id2,
m.id,
s.description
FROM main m
LEFT JOIN ( SELECT DISTINCT
m.id,
CASE
WHEN m.id2 = 15 AND (SELECT name FROM secondary x WHERE x.id = s2.id AND x.id2 = 10) = md5(123::text) THEN 'description'
WHEN m.id2 = 15 THEN (SELECT name FROM secondary x WHERE x.id = s2.id AND x.id2 = 5)
END AS description
FROM main m
JOIN secondary s2 ON m.id = s2.id
WHERE m.id2 = 15
and type = 0) s ON s.id = m.id
WHERE m.id2 IN (15)
and type = 0