Re: segfault with incremental sort - Mailing list pgsql-bugs

From luis.roberto@siscobra.com.br
Subject Re: segfault with incremental sort
Date
Msg-id 1779929796.39206557.1604407053112.JavaMail.zimbra@siscobra.com.br
Whole thread Raw
In response to Re: segfault with incremental sort  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: segfault with incremental sort
List pgsql-bugs


Hm ... suggestive, but not really enough to debug it.

Can you build a self-contained test case?

                        regards, tom lane

Hi Tom!

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

pgsql-bugs by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: BUG #16698: Create extension and search path
Next
From: Miha Vrhovnik
Date:
Subject: Re: BUG #16698: Create extension and search path