Incorrect result from a distributed outer join with one-time filter on a local table

[duerwuyi]

DROP TABLE IF EXISTS t0;
create table t0 ( 
vkey int4 ,
c3 timestamp
);

DROP TABLE IF EXISTS t3;
create table t3 ( 
vkey int4 ,
c26 timestamp
);

DROP TABLE IF EXISTS t4;
create table t4 ( 
vkey int4 
);


insert into t0 (vkey, c3) values 
(13,make_timestamp(2019, 10, 23, 15, 34, 50));

insert into t3 (vkey,c26) values 
(1, make_timestamp(2024, 3, 26, 17, 36, 53));

insert into t4 (vkey) values 
(1);

query:

select  *
from 
  (t0 full outer join t3
    on (t0.c3 = t3.c26 ))
where (
    exists (
    select  *
    from 
    t4
    )
);

correct result:

when I make t0 as a distributed table, using SELECT create_distributed_table('t0', 'vkey'); I got incorrect result as following:

this bug can be observed in citus 12.3 and 12.5.

[onurctirtir]

Same as https://github.com/citusdata/citus/issues/7698, see https://github.com/citusdata/citus/issues/7698#issuecomment-2562877434.

[naisila]

This issue can be fixed by upgrading to Postgres 17. For Postgres 15 and 16, there is no access to set_join_pathlist_hook, therefore to prevent incorrect query results, in the next version Citus will throw an error for such type of queries with pseudoconstant conditions (like exists (select * from t4))

This issue is a subcase of https://github.com/citusdata/citus/issues/7696, where the one-time filter, i.e., the pseudoconstant condition is on a local table.

There is some ongoing development on this subcase where the one-time filter is in a local table. See #7810 for details. That's why we are keeping this issue open for now.