A logic bug with distributed table

[duerwuyi]

How to reproduce

Citus version： Citus 12.1.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
init state:

DROP TABLE IF EXISTS t0;
create table t0 ( 
vkey int4 ,
pkey int4 ,
c0 timestamp ,
c1 numeric ,
c2 timestamp ,
c3 timestamp 

);
DROP TABLE IF EXISTS t1;
create table t1 ( 
vkey int4 ,
pkey int4 ,
c4 timestamp ,
c5 text ,
c6 int4 ,
c7 numeric ,
c8 int4 ,
c9 numeric ,
c10 int4 ,
c11 text 
);

SELECT create_reference_table('t1');
insert into t0 (vkey, pkey, c0, c1, c2, c3) values 
(7, 17000, make_timestamp(2070, 1, 18, 14, 45, 3), null::numeric, make_timestamp(1984, 8, 28, 3, 32, 23), make_timestamp(1970, 4, 13, 14, 30, 45));

query

delete from t0
where TRUE or
 (((t0.vkey) >= ( 
      select  
          pg_catalog.regexp_count(ref_0.c5, ref_0.c11)
        from 
          t1 as ref_0
        where true
          )));

result: 0 rows deleted

Excepted result:

the WHERE clause is : WHERE TRUE or ……, so the correct result should be: 1 rows deleted.

Comparison and EXPLAIN result:

when without citus, the query returns correctly: 1 rows deleted.

EXPLAIN result:

Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=0 width=0)
  Task Count: 0
  Tasks Shown: All

[colm-mchugh]

@duerwuyi thanks for bringing to our attention, (and #7783), I can confirm we can reproduce. It looks like an issue with Citus' expression processing. We will investigate further and fix.

[duerwuyi]

@duerwuyi thanks for bringing to our attention, (and #7783), I can confirm we can reproduce. It looks like an issue with Citus' expression processing. We will investigate further and fix.

Thanks for your confirming. I wonder if #7696 #7697 #7698 can be reproduced in your computer, and they seem still exist with 12.1.6. Are they independent bugs caused by different logical defects?

[colm-mchugh]

Thanks for your confirming. I wonder if #7696 #7697 #7698 can be reproduced in your computer, and they seem still exist with 12.1.6. Are they independent bugs caused by different logical defects?

We have reproduced #7696, #7697 and #7698, it looks like they may be symptoms of the same underlying problem (to be confirmed), and this looks unrelated to this issue (#7783), but we need to investigate further and confirm.